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

7 responses 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

  2. LV Handbags Sale November 30, 2011 at 8:13 am

    What i come across tough would be to locate a weblog that may seize me for a minute but your blog is diverse.

  3. Louis Vuitton Handbags For Sale December 2, 2011 at 10:35 am

    I wonder what is the lack of Google method that do not rank this type of informative sites in top of the list. Generally the top sites are full of garbage.

  4. Replica Louis Vuitton Hats December 2, 2011 at 11:43 am

    outstanding post. it’s so informative. i’d like to share this to my buddies.

  5. Discounted Designer Handbags December 5, 2011 at 8:28 am

    nice post! am looking forward to read your future post, great weekend ahead!

  6. Uggs For Cheap December 6, 2011 at 10:15 am

    hey there and thank you for your info ? I’ve definitely picked up something new from proper here. I did on the other hand expertise some technical points the use of this website, as I experienced to reload the web site a lot of instances prior to I may get it to load properly. I had been considering if your web hosting is OK? Not that I’m complaining, but slow loading circumstances instances will often have an effect on your placement in google and can damage your high quality score if advertising and marketing with Adwords. Anyway I am including this RSS to my email and can look out for a lot more of your respective fascinating content. Ensure that you update this again very soon..

  7. moncler December 16, 2011 at 10:54 am

    Hi just thought i would personally inform you of something.. It’s twice now i?ve landed inside of your blog inside final 3 weeks in search of totally unrelated things.

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: