Roshan Book

My Tech Notebook

Python programming tutorial 15–Databases


Creating a database in sqlite 3

import sqlite3

def main():
    db=sqlite3.connect(‘test.db’)
    db.execute(‘drop table if exists test’)
    db.execute(‘create table test(t1 text,i1 int)’)
    db.execute(‘insert into test (t1,i1) values(?,?)’,(‘one’,1))
    db.execute(‘insert into test (t1,i1) values(?,?)’,(‘two’,2))
    db.execute(‘insert into test (t1,i1) values(?,?)’,(‘three’,3))
    db.execute(‘insert into test (t1,i1) values(?,?)’,(‘four’,4))
    db.commit()
    cursor=db.execute(‘select * from test order by t1’)
    for row in cursor:
        print(row)
   
if __name__ == “__main__”: main()

 

Using row factory in sqllite3. It returns rows in dictionary mode

import sqlite3

def main():
    db=sqlite3.connect(‘test.db’)
    db.row_factory=sqlite3.Row # It allows you to specify how rows will be returned
    db.execute(‘drop table if exists test’)
    db.execute(‘create table test(t1 text,i1 int)’)
    db.execute(‘insert into test (t1,i1) values(?,?)’,(‘one’,1))
    db.execute(‘insert into test (t1,i1) values(?,?)’,(‘two’,2))
    db.execute(‘insert into test (t1,i1) values(?,?)’,(‘three’,3))
    db.execute(‘insert into test (t1,i1) values(?,?)’,(‘four’,4))
    db.commit()
    cursor=db.execute(‘select * from test order by t1’)
    for row in cursor:
        print(dict(row))
        print(row[‘t1’], row[‘i1’])
   
if __name__ == “__main__”: main()

 

 

Output

{‘i1’: 4, ‘t1’: ‘four’}
four 4
{‘i1’: 1, ‘t1’: ‘one’}
one 1
{‘i1’: 3, ‘t1’: ‘three’}
three 3
{‘i1’: 2, ‘t1’: ‘two’}
two 2

Advertisements

One response to “Python programming tutorial 15–Databases

  1. Doyle Overstrom October 22, 2011 at 12:32 pm

    Good page… Just killing some time browsing searches and found your page. Great looking site. I will have to add your page to come back and see what’s new. Cheers! My Blog: Lethalia Blogs

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: