User Tools

Site Tools


python:sqlitepy

SMALL TUTORIAL WITH SQLITE AND PYTHON IN ACTION

18.10.2008

SQLite is a C library that provides a lightweight disk-based database that doesn’t require a separate server process and allows accessing the database using a nonstandard variant of the SQL query language. Some applications can use SQLite for internal data storage. It’s also possible to prototype an application using SQLite and then port the code to a larger database such as PostgreSQL or Oracle.

First step in your python scripts it will be to import the library:

import sqlite3

If you get an error message, check for your python installation.

1. Create a connection and a cursor to work with db

conn = sqlite3.connect('/tmp/test.db')
cursor = conn.cursor()

2. Check if the table already exists in the db

If you want to check before create a table, you can use the very simple sql statement CREATE TABLE IF NOT EXISTS… This is supported by the latests sqlite versions, and it's a good way to create a table only if it's needed.

If you don't want to use this, or you just need a method to check for the table:

cursor.execute(""" SELECT COUNT(*) FROM sqlite_master WHERE name = ?  """, (tablename, ))
res = self.cursor.fetchone()
print bool(res[0]) # True if exists

3. Create a table inside the database (temp.db)

Let's suppose we want to create a table called customer:

cursor.execute("""
               CREATE TABLE customer(
	               id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
                    firstname VARCHAR(50),
                    lastname VARCHAR(50),
                    age INTEGER
                    )
               """)

4. Insert data (one customer case)

Populate the table using placeholders (to prevent sql injections):

q = """ 
	INSERT INTO customer(firstname, lastname, age)
	VALUES(?, ?, ?)
	"""
cursor.execute(q, ('John', 'Doe', 34))
conn.commit()

You need to explicitly commit; sqlite3 for python uses transactions by default and before every UPDATE/INSERT/DELETE/REPLACE statements, sqlite3 opens a transaction (it automatically commits to a nonquery statement as CREATE TABLE). If you don't want to use this feature (useful if something gets wrong along the way – your db will be unaffected), just open the connection as:

conn = sqlite3.connect('/tmp/test.db', isolation_level=None)

5. Insert a batch of data (multi customers case) using executemany

A very efficient way to insert data is by using executemany method. executemany - Executes an SQL command against all parameter sequences or mappings found in the sequence sql.

Create a list of tuples with desired data, let's say in our case:

multiple_customers = [
        ('Derek', 'Patrick', 24),
        ('Simoncelli', 'Rock', 53),
        ('Jesus', 'Christ', 25)
        ]

And then use executemany to fill up the table:

conn.executemany("INSERT INTO customer(firstname, lastname, age)
			  VALUES(?, ?, ?)", multiple_customers)
conn.commit()

6. Executing multiple sql statements at once

Using executescript - This is a nonstandard convenience method for executing multiple SQL statements at once. It issues a COMMIT statement first, then executes the SQL script it gets as a parameter.

script = 	""" 
		CREATE TABLE another_one(first, last, number);
		CREATE TABLE important_data(id, data);
		INSERT INTO important_data(id, data) VALUES(1, 'some string');
		"""
cursor.executescript(script)

7. Retrieving all the data (customers) from the table

cursor.execute(“SELECT * FROM customers”)
for cust in cursor.fetchall():
	print “ID %d NAME %s %s” % (cust[0], cust[1], cust[2])

8. Accessing columns by name

If you want to access column by name, rather than by index, you must set first the attribute row_factory to sqlite3.Row class.

conn = sqlite3.connect(“mydb”)
conn.row_factory = sqlite3.Row
 
curs = conn.cursor()
curs.execute(“SELECT firstname FROM customer WHERE 1)
for row in curs:
	print row['firstname']

9. Using adapters

As described before, SQLite supports only a limited set of types natively. To use other Python types with SQLite, you must adapt them to one of the sqlite3 module’s supported types for SQLite: one of None, int, long, float, str, unicode, buffer.

Let's suppose you want to store a tuple (eg. (1,2,3)) in a sqlite database. You need to transform it into a string. We'll use in our class a conform method who will return the accepted type (string in our case):

class tupleExample():
	def __init__(self, optuple):
		self.tup = optuple
 
	def __conform__(self, protcol):
		if protocol is sqlite3.PrepareProtocol:
			return self.tup.__str__()
 
con = sqlite3.connect(":memory:")
	 curs = con.cursor()
 
	  tu = tupleExample((1, 4, 5))
	  curs.execute("select ?", (tu,))
	  print curs.fetchone()[0]

Another way to achieve this is to create a function who make this transformation and then register it with register_adapter():

class tupleExample(object):
    def __init__(self, optuple):
        self.optuple = optuple
 
 
def adapt_tuple(tupleob):
    return tupleob.optuple.__str__()
 
 
sqlite3.register_adapter(tupleExample, adapt_tuple)
con = sqlite3.connect(":memory:")
curs = con.cursor()
 
tu = tupleExample((1, 4, 5))
curs.execute("select ?", (tu,))
print curs.fetchone()[0]

python/sqlitepy.txt · Last modified: 2013/03/16 17:41 (external edit)