What is the Python equivalent of Perl's DBI?

Question

What is Python's equivalent of Perl's DBI and how do I use it? More specifically, what is the Python equivalent of the following Perl code?

use DBI;

# connect to a MySQL database
my $dbh = DBI->connect("dbi:mysql:database=$database; host=localhost; port=3306", $user, $pass);

# select and read a few rows
my $sth = $dbh->prepare("SELECT id, name FROM table WHERE id <= ?;");
$sth->execute(321);
while (my @row = $sth->fetchrow_array) {
  my $id = $row[0];
  my $name = $row[1];
  print "$id. $name\n";
}

# write to the database
$sth = $dbh->prepare("INSERT INTO table (id, name) VALUES (?, ?);");
$sth->execute(123, "foo");

Answer 1

Shylent's post meets the OP's request for equivalent code. However it does not adequately address the issue of what is Python's equivalent to the Perl DBI.

For those not familiar with Perl's DBI, it provides a common interface for all database systems. To add support for new storage backend, a database driver or DBD needs to be written. Drivers exist for many different database systems, and even non-database targets like CSV files and spreadsheets.

It looks like the Python DB-API is the closest thing to the Perl DBI. However it is a specification, and not an implementation. To what extent any database driver conforms to the specification up to the author.

Of course, database systems vary in what SQL commands and syntax they support. Databases vary quite a bit in what features they provide. Any system that attempts to standardize database interaction will have portability issues to address as all these differing systems provide distinct feature sets.

My experience with Perl DBI has been very positive. It is fairly easy to write portable code that works with many DBD drivers. I have successfully used 4 different database drivers (Postgres, MySQL, a CSV file driver, and SQLite) in a single application by simply changing the database connection string. For more complex apps which need to access more "incompatible" features of the database, there are a number of abstraction libraries that extend the DBI interface and further simplify portability.

I don't have enough Python experience to be able to say how PEP249 plays out in the real world. My hope is that database driver developers hew close to the spec, and portability is easy to obtain. Perhaps someone with a deeper knowledge of Python will be able to expand on this topic. There is some information on Python database access at the Python wiki.

Answer 2

import MySQLdb.cursors

db = MySQLdb.connect(db=database, host=localhost,
                     port=3306, user=user, passwd=pass,
                     cursorclass=MySQLdb.cursors.DictCursor)
cur = db.cursor()

#this is not string interpolation, everything is quoted for you automatically
cur.execute("select id, name from table where id = %s", (321,))

for row in cur.fetchall():
    print "%s. %s" % (row['id'], row['name'])

cur.execute("insert into table (id, name) values (%s, %s)", (123, 'foo'))
db.commit() # required, because autocommit is off by default

Python database API use a common convention, that is pretty much the same across different databases (but not quite!). You can read the MySQLdb documentation here.

There is also a more feature-rich interface to mysql, called oursql. It has real parametrization (not just glorified string interpolation), server-side cursors, data streaming and so on.