SQLite is a very small RDBMS, unlike other popular RDBMS it does not have a client/server model, data is stored in a file. SQLite is very portable, and very popular for creating prototype systems before actual work is started. Here, we'll see how to access & modify SQLite database in Python. Python version >= 2.5 have the sqlite Python library shipped with core Python package, lower versions have to install the SQLite Python library pysqlite from http://code.google.com/p/pysqlite/ If you would like to manage the SQLite databases, you can install the sqlite3 command line tool, or a GUI in the form of a Firefox extension named SQLite Manager is also available. Basics Next, we'll go through a bit of Python code which does basic operations like listing version, schema info, creating tables etc. Follow the code it's quite easy. Code: ## load the module import sqlite3 as sdb ## create connection object conn = sdb.connect('/tmp/test.db') ## get a cursor object my_cursor = conn.cursor() ## execute query my_cursor.execute('SELECT SQLITE_VERSION()') ## fetch data sqlite_data = my_cursor.fetchone() print "SQLite version: %s" % sqlite_data ## create a simple table my_cursor.execute('CREATE TABLE IF NOT EXISTS test( name VARCHAR(12), age INTEGER)') ## insert data my_cursor.execute('insert into test values (?,?)', ('Sudipta',30) ) ## get tables' info from sqlite_master my_cursor.execute('SELECT * FROM sqlite_master') ## fetch data sqlite_data = my_cursor.fetchone() print sqlite_data ## get inserted data my_cursor.execute('SELECT * FROM test') ## fetch data sqlite_data = my_cursor.fetchall() print sqlite_data Output: Code: SQLite version: 3.3.6 [(u'table', u'test', u'test', 2, u'CREATE TABLE test( name VARCHAR(12), age INTEGER)')] [(u'Sudipta', 30)] Advanced Usage In the advanced usage we will be looking into executing the same query with many different values, the dictionary cursor, using placeholders in queries and exception handling. At times we might need to insert many rows at once: Code: ## load the module import sqlite3 as sdb ## create connection object conn = sdb.connect('/tmp/test.db') ## get a cursor object my_cursor = conn.cursor() ## create table my_cursor.execute('CREATE TABLE IF NOT EXISTS contacts(contact_id INTEGER PRIMARY KEY, contact_name TEXT, contact_email TEXT)') my_data = ( ('Anjan','anjan@anjan.net.in'), ('Jayanta','jayanta@gmail.com'), ('Ajay','ajayan@kerala.com'), ('Jac','jacann@yahoo.com.au') ) my_cursor.executemany('INSERT INTO contacts(contact_name,contact_email) VALUES(?,?)',my_data) conn.commit() Result: Code: $ sqlite3 /tmp/test.db SQLite version 3.3.6 Enter ".help" for instructions sqlite> select * from contacts; 1|Anjan|anjan@anjan.net.in 2|Jayanta|jayanta@gmail.com 3|Ajay|ajayan@kerala.com 4|Jac|jacann@yahoo.com.au sqlite> The dictionary cursor enables us to access fields using their names, follow the sample code: Code: ## load the module import sqlite3 as sdb ## create connection object conn = sdb.connect('/tmp/test.db') ## select the dictionary cursor conn.row_factory = sdb.Row ## get a cursor object my_cursor = conn.cursor() my_cursor.execute("SELECT * FROM contacts") rows = my_cursor.fetchall() for row in rows: print "%-2s %-10s %-30s" % (row["contact_id"], row["contact_name"], row["contact_email"]) Output: Code: $ ./test.py 1 Anjan anjan@anjan.net.in 2 Jayanta jayanta@gmail.com 3 Ajay ajayan@kerala.com 4 Jac jacann@yahoo.com.au Next, we would always want out code to exit gracefully, therefore, we would need to do exception handling: Code: ## load the module import sqlite3 as sdb import sys conn = None try: conn = sdb.connect('/tmp/test.db') my_cursor = conn.cursor() my_cursor.execute("INSERT INTO contacts(contact_name,contact_email) VALUES (?,?)", ('Bittu','bittu@redbull.com') ) ## try to insert a duplicate contact id my_cursor.execute("INSERT INTO contacts(contact_id,contact_name,contact_email) VALUES (?,?,?)", (1,'Farah','fk10@microsoft.com') ) conn.commit() except sdb.Error, e: ## rollback on error if conn: conn.rollback() print "Error %s:" % e.args[0] sys.exit(1) finally: if conn: conn.close() Output: Code: $ ./test.py Error PRIMARY KEY must be unique: That's all you'll need to start writing your portable application using SQLite. Enjoy!