Working With SQLite Database in Python

Discussion in 'Python' started by pradeep, Jul 24, 2012.

  1. pradeep

    pradeep Team Leader

    Joined:
    Apr 4, 2005
    Messages:
    1,645
    Likes Received:
    87
    Trophy Points:
    0
    Occupation:
    Programmer
    Location:
    Kolkata, India
    Home Page:
    http://blog.pradeep.net.in
    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!
     

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice