# Python Programming/Database Programming

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\".

## DBMS Specifics

### MySQL

An Example with MySQL would look like this:

``` python
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:

``` python
  #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:

``` python
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.

### Sqlite

An example with sqlite is very similar to the one above and the cursor
provides many of the same functionalities.

``` python
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:

``` python
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()
```

### Postgres

``` python
import psycopg2
conn = psycopg2.connect("dbname=test")
cursor = conn.cursor()
cursor.execute("select * from test");
for i in cursor.next():
    print(i)
conn.close()
```

### Firebird

``` python
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()
```

## General Principles

### Parameter Quoting

You will frequently need to substitute dynamic data into a query string.
**It is important to ensure this is done correctly.**

``` python
# Do not do this!
result = db.execute("SELECT name FROM employees WHERE location = '" + location + "'")
```

This example is **wrong**, because it doesn't correctly deal with
special characters, like apostrophes, in the string being substituted.
If your code has to deal with potentially hostile users (like on a
public-facing Web server), this could leave you open to an **SQL
injection attack**.

For simple cases, use the automatic parameter substitution provided by
the `execute` method, e.g.

``` python
result = db.execute("SELECT name FROM employees WHERE location = ?", [location])
```

The DBMS interface itself will automatically convert the values you pass
into the correct SQL syntax.

For more complex cases, the DBMS module should provide a quoting
function that you can explicitly call. For example, MySQLdb provides the
`escape_string` method, while APSW (for SQLite3) provides
`format_sql_value`. This is necessary where the query structure takes a
more dynamic form:

``` python
criteria = [("company", company)] # list of tuples (fieldname, value)
if department != None :
    criteria.append(("department", department))
# ... append other optional criteria as appropriate ...

result = db.execute(
        "SELECT name FROM employees WHERE "
    +
        " and ".join(
            "%s = %s" % (criterion[0], MySQLdb.escape_string(criterion[1]))
            for criterion in criteria
          )
  )
```

This will dynamically construct queries like "select name from employees
where company = \'*some company*\'" or "select name from employees where
company = \'*some company*\' and department = \'*some department*\'",
depending on which fields have been filled in by the user.

### Use Iterators

Python iterators are a natural fit for the problem of iterating over
lots of database records. Here is an example of a function that performs
a database query and returns an iterator for the results, instead of
returning them all at once. It relies on the fact that, in APSW (the
Python 3 interface library for SQLite), the `cursor.execute` method
itself returns an iterator for the result records. The result is that
you can write very concise code for doing complex database queries in
Python.

``` python
def db_iter(db, cmd, mapfn = lambda x : x) :
    "executes cmd on a new cursor from connection db and yields the results in turn."
    cu = db.cursor()
    result = cu.execute(cmd)
    while True:
        yield mapfn(next(result))
```

Example uses of this function:

``` python
for artist, publisher in db_iter(
        db = db,
        cmd =
                "SELECT artist, publisher FROM artists WHERE location = %s"
            %
                 apsw.format_sql_value(location)
      ):
    print(artist, publisher)
```

and

``` python
for location in db_iter(
        db = db,
        cmd = "SELECT DISTINCT location FROM artists",
        mapfn = lambda x : x[0]
      ):
    print(location)
```

In the first example, since `db_iter` returns a tuple for each record,
this can be directly assigned to individual variables for the record
fields. In the second example, the tuple has only one element, so a
custom `mapfn` is used to extract this element and return it instead of
the tuple.

### Never Use "SELECT \*" in a Script

Database table definitions are frequently subject to change. As
application requirements evolve, fields and even entire tables are often
added, or sometimes removed. Consider a statement like

``` python
result = db.execute("select * from employees")
```

You may happen to know that the `employees` table currently contains,
say, 4 fields. But tomorrow someone may add a fifth field. Did you
remember to update your code to deal with this? If not, it's liable to
crash. Or even worse, produce an incorrect result!

Better to always list the **specific** fields you're interested in, no
matter how many there are:

``` python
result = db.execute("select name, address, department, location from employees")
```

That way, any extra fields added will simply be ignored. And if any of
the named fields are removed, the code will at least fail with a runtime
error, which is a good reminder that you forgot to update it!

### Looping on Field Breaks

Consider the following scenario: your sales company database has a table
of employees, and also a table of sales made by each employee. You want
to loop over these sale entries, and produce some per-employee
statistics. A naïve approach might be:

-   Query the database to get a list of employees
-   For each employee, do a database query to get the list of sales for
    each employee.

If you have a lot of employees, then the first query may produce a large
list, and the second step will involve a correspondingly large number of
database queries.

In fact, the entire processing loop can run off a *single database
query*, using the standard SQL construct called a `join`.

Here is what an example of such a loop could look like:

``` python
rows = db_iter \
  (
    db = db,
    cmd =
        "select employees.name, sales.amount, sales.date from"
        " employees left join sales on employees.id = sales.employee_id"
        " order by employees.name, sales.date"
  )
prev_employee_name = None
while True:
    row = next(rows, None)
    if row != None :
        employee_name, amount, date = row
    if row == None or employee_name != prev_employee_name :
         if prev_employee_name != None :
              # done stats for this employee
              report(prev_employee_name, employee_stats)
         if row == None :
              break
         # start stats for a new employee
         prev_employee_name = employee_name
         employee_stats = {"total_sales" : 0, "number_of_sales" : 0}
         if date != None :
               employee_stats["earliest_sale"] = date
    # another row of stats for this employee
    if amount != None :
         employee_stats["total_sales"] += amount
         employee_stats["number_of_sales"] += 1
    if date != None :
         employee_stats["latest_sale"] = date
```

Here the statistics are quite simple: earliest and latest sale, and
number and total amount of sales, and could be computed directly within
the SQL query. But the same loop could compute more complex statistics
(like standard deviation) that cannot be represented directly within a
simple SQL query.

Note how the statistics for each employee are written out under either
of two conditions:

-   The employee name of the next record is different from the previous
    one
-   The end of the query results has been reached.

Both conditions are tested with
`row == None or employee_name != prev_employee_name`; after writing out
the employee statistics, a separate check for the second condition
`row == None` is used to terminate the loop. If the loop doesn't
terminate, then processing is initialized for the new employee.

Note also the use of a `left join` in this case: if an employee has had
no sales, then the join will return a single row for that employee, with
SQL `null` values (represented by `None` in Python) for the fields from
the `sales` table. This is why we need checks for such `None` values
before processing those fields.

Alternatively, we could have used an `inner join`, which would have
returned *no* results for an employee with no sales. Whether you want to
omit such an employee from your report, or include them with totals of
zero, is really up to your application.

## See Also

-   Python Programming/Database
    Programming

## External links

-   APSW module, code.google.com ---
    SQLite3 for Python 2.*x* and 3.*x*
-   SQLite documentation
-   Psycopg2 (PostgreSQL module - newer), initd.org
-   PyGreSQL (PostgreSQL module - older),
    pygresql.org
-   MySQL module,
    sourceforge.net
-   FirebirdSQL module,
    github.com

