Go4Expert

Go4Expert (http://www.go4expert.com/)
-   Python (http://www.go4expert.com/articles/python-tutorials/)
-   -   Working With SQLite Database in Python (http://www.go4expert.com/articles/sqlite-database-python-t28778/)

pradeep 24Jul2012 16:49

Working With SQLite Database in Python
 
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: Python

## 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: Python

## 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: Python

## 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!


All times are GMT +5.5. The time now is 14:18.