Python Pocket Reference, 4th Edition

Python Portable SQL Database API

Python’s portable SQL database API provides script portability between different vendor-specific SQL database packages. For each vendor, install the vendor-specific extension module, but write your scripts according to the portable database API. Your database scripts will largely continue working unchanged after migrating to a different underlying vendor package.

Note that most database extension modules are not part of the Python standard library; they must be fetched and installed separately. The SQLite embedded in-process relational database package is included with Python as standard library module sqlite3, intended for program data storage and prototyping. See also the section Object Persistence Modules for simpler storage alternatives.

API Usage Example

The following uses the SQLite standard library module. Usage for enterprise-level database such as MySQL, PostgreSQL, and Oracle are similar, but require different connection parameters and installation of extension modules:

>>> from sqlite3 import connect >>> conn = connect(r'C:\users\mark\misc\temp.db') >>> curs = conn.cursor() >>> curs.execute('create table jobs (name, title, pay)') >>> prefix = 'insert into jobs values ' >>> curs.execute(prefix + "('Bob', 'dev', 100)") >>> curs.execute(prefix + "('Sue', 'dev', 120)") >>> curs.execute("select * from jobs where pay > 100") >>> for (name, title, pay) in curs.fetchall(): ... print(name, title, pay) ... Sue dev 120 >>> curs.execute("select name, ...