Python has support for working with databases via a simple API. Modules included with Python include modules for SQLite and Berkeley DB. Modules for MySQL , PostgreSQL , FirebirdSQL and others are available as third-party modules. The latter have to be downloaded and installed before use. The package MySQLdb can be installed, for example, using the debian package "python-mysqldb".
An Example with MySQL would look like this:
import MySQLdb db = MySQLdb.connect("host machine", "dbuser", "password", "dbname") cursor = db.cursor() query = """SELECT * FROM sampletable""" lines = cursor.execute(query) data = cursor.fetchall() db.close()
On the first line, the Module MySQLdb is imported. Then a connection to the database is set up and on line 4, we save the actual SQL statement to be executed in the variable query. On line 5 we execute the query and on line 6 we fetch all the data. After the execution of this piece of code, lines contains the number of lines fetched (e.g. the number of rows in the table sampletable). The variable data contains all the actual data, e.g. the content of sampletable. In the end, the connection to the database would be closed again. If the number of lines are large, it is better to use row = cursor.fetchone() and process the rows individually:
#first 5 lines are the same as above while True: row = cursor.fetchone() if row == None: break #do something with this row of data db.close()
Obviously, some kind of data processing has to be used on row, otherwise the data will not be stored. The result of the fetchone() command is a Tuple.
In order to make the initialization of the connection easier, a configuration file can be used:
import MySQLdb db = MySQLdb.connect(read_default_file="~/.my.cnf") ...
Here, the file .my.cnf in the home directory contains the necessary configuration information for MySQL.
An example with sqlite is very similar to the one above and the cursor provides many of the same functionalities.
import sqlite3 db = sqlite3.connect("/path/to/file") cursor = db.cursor() query = """SELECT * FROM sampletable""" lines = cursor.execute(query) data = cursor.fetchall() db.close()
When writing to the db, one has to remember to call db.commit(), otherwise the changes are not saved:
import sqlite3 db = sqlite3.connect("/path/to/file") cursor = db.cursor() query = """INSERT INTO sampletable (value1, value2) VALUES (1,'test')""" cursor.execute(query) db.commit() db.close()
import psycopg2 conn = psycopg2.connect("dbname=test") cursor = conn.cursor() cursor.execute("select * from test"); for i in cursor.next(): print i conn.close()
import firebirdsql conn = firebirdsql.connect(dsn='localhost/3050:/var/lib/firebird/2.5/test.fdb', user='alice', password='wonderland') cur = conn.cursor() cur.execute("select * from baz") for c in cur.fetchall(): print(c) conn.close()
- SQLite documentation
- Psycopg2 (PostgreSQL module - newer)
- PyGreSQL (PostgreSQL module - older)
- MySQL module
- FirebirdSQL module