Python Programming/Databases

From Wikibooks, the open-content textbooks collection

Python has some support for working with databases. Modules included with Python include modules for SQLite and Berkeley DB. Modules for MySQL and PostgreSQL and others are available as third-party modules. The latter have to be downloaded and installed before use.

[edit] MySQL

An Example with MySQL would look like this:

1  import MySQLdb
2  db = MySQLdb.connect("host machine", "dbuser", "password", "dbname")
3  cursor = db.cursor()
4  query = """SELECT * FROM sampletable"""
5  lines = cursor.execute(query)
6  data = cursor.fetchall()
7  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.

[edit] External links

• SQLite documentation
• Psycopg2 (PostgreSQL module - newer)
• PyGreSQL (PostgreSQL module - older)
• MySQL module