Quantcast
submit to reddit       
       

Accessing an SQLite database with Python

In the last tutorial, I set up an SQLite database, and performed a few basic operations on it using the SQLite shell. This time I'm going to use Python to manage my database.

Using Python to query a database

The following script connects to the database that I made in the last tutorial, and does a couple of queries:

#!/usr/bin/env python import sqlite3 conn=sqlite3.connect('mydatabase.db') curs=conn.cursor() print "\nEntire database contents:\n" for row in curs.execute("SELECT * FROM temps"): print row print "\nDatabase entries for the garage:\n" for row in curs.execute("SELECT * FROM temps WHERE zone='garage'"): print row conn.close()

Save this as database.py and make it executable with this command:

chmod +x database.py

The call to sqlite.connect() opens the database file, and creates a cursor. The cursor object is then used to execute SQL commands. In the example above, I have used the execute command to run a couple of queries, first to get all records in the table temps and then to get all records where the zone is 'garage'. In each case, the execute function returns a list of records, so we can use a for loop to iterate through each record. Finally, the database connection is closed.

The output from this code is as follows:

Entire database contents: (u'2013-09-04', u'01:40:36', u'kitchen', 20.6) (u'2013-09-04', u'01:40:51', u'greenhouse', 26.3) (u'2013-09-04', u'01:41:08', u'garage', 18.6) (u'2013-09-05', u'13:41:27', u'kitchen', 19.5) (u'2013-09-05', u'13:41:38', u'greenhouse', 15.1) (u'2013-09-05', u'13:41:52', u'garage', 18.1) (u'2013-09-05', u'01:42:09', u'kitchen', 21.2) (u'2013-09-05', u'01:42:26', u'greenhouse', 27.1) (u'2013-09-05', u'01:42:38', u'garage', 19.1) (u'2013-09-05', u'07:22:05', u'garage', 18.3) (u'2013-09-05', u'07:22:44', u'garage', 18.3) Database entries for the garage: (u'2013-09-04', u'01:41:08', u'garage', 18.6) (u'2013-09-05', u'13:41:52', u'garage', 18.1) (u'2013-09-05', u'01:42:38', u'garage', 19.1) (u'2013-09-05', u'07:22:05', u'garage', 18.3) (u'2013-09-05', u'07:22:44', u'garage', 18.3)

Each record is printed as a tuple of values, and text is in unicode. Each item in the tuple can be accessed using tuple notation (for example, to access the date from a row in the code above, you would use row[0]. You can convert from unicode using the Python str() function.

The example above simple returns database records in the order in they were found. Sometimes you might want to display the data in a particular order. This example selects all entries sorted in ascending order of temperature:

print "\nDatabase entries in order of temperature\n" for row in curs.execute("SELECT * FROM temps ORDER BY temperature ASC"): print row

This code's output is as follows:

Database entries in order of temperature (u'2013-09-05', u'13:41:38', u'greenhouse', 15.1) (u'2013-09-05', u'13:41:52', u'garage', 18.1) (u'2013-09-05', u'07:22:05', u'garage', 18.3) (u'2013-09-05', u'07:22:44', u'garage', 18.3) (u'2013-09-04', u'01:41:08', u'garage', 18.6) (u'2013-09-05', u'01:42:38', u'garage', 19.1) (u'2013-09-05', u'13:41:27', u'kitchen', 19.5) (u'2013-09-04', u'01:40:36', u'kitchen', 20.6) (u'2013-09-05', u'01:42:09', u'kitchen', 21.2) (u'2013-09-04', u'01:40:51', u'greenhouse', 26.3) (u'2013-09-05', u'01:42:26', u'greenhouse', 27.1)

To sort in descending order, replace ASC with DESC. ASC is used by default if neither is specified.

Subsituting SQL parameters

Sometimes it's useful to use the same query with different parameters. For example, we might want to select records where the temperature is above a certain value.

print "\nDatabase entries where the temperature is above x:\n" for row in curs.execute("SELECT * FROM temps WHERE temperature>19.0"): print row

Suppose we plan on using this query repeatedly, but we might want to check for different temperature thresholds. An alternative is to pass a variable to the execute function and let sqlite subsititute that variable in the query:

print "\nDatabase entries where the temperature is above x:\n" for row in curs.execute("SELECT * FROM temps WHERE temperature>(?)",(x,)): print row

Now the query will return any record where the temperature is greater than x. Note that x has to be passed to the execute command as a tuple, not a single variable. In this case we're using a tuple with a single entry, and x is equal to 19.0. Here's the output from this code:

Database entries where the temperature is above x: (u'2013-09-04', u'01:40:36', u'kitchen', 20.6) (u'2013-09-04', u'01:40:51', u'greenhouse', 26.3) (u'2013-09-05', u'13:41:27', u'kitchen', 19.5) (u'2013-09-05', u'01:42:09', u'kitchen', 21.2) (u'2013-09-05', u'01:42:26', u'greenhouse', 27.1) (u'2013-09-05', u'01:42:38', u'garage', 19.1)

Use python to insert data into a database

Using substitution is useful when writing code to insert a record into a database. In this function, the zone and temperature are passed to the query as variables, and the 'date' and 'time' SQL function is used to generate the current date and time:

def add_temp_reading (zonestr, temp): # I used triple quotes so that I could break this string into # two lines for formatting purposes curs.execute("""INSERT INTO temperatures values(date('now'), time('now'), (?), (?))""", (zonestr,temp)) # commit the changes conn.commit()

Selecting records by date

In many applications it's useful to select records by date. We can use less than/greater than operators to search for records before or after a specific date. The next examples select records from two days ago, and one month ago respecitvely:

print "\nDatabase entries for the garage:\n" for row in curs.execute("SELECT * FROM temperatures WHERE tdate>=date('now','-2 day')"): print row print "\nDatabase entries for the garage:\n" for row in curs.execute("SELECT * FROM temperatures WHERE tdate>=date('now','-1 month')"): print row
See also: http://docs.python.org/2/library/sqlite3.html
Comments

Comments

comments powered by Disqus

Follow me


This site is powered by Pyplate, a lightweight Python CMS for the Raspberry Pi.