1+ '''
2+ Created on Aug 8, 2017
3+
4+ @author: Aditya
5+
6+
7+ This program gives demo for Database useage in python
8+ '''
9+ import sqlite3 # import python library that supports sqlite3
10+
11+ def create_database (filename ):
12+ db = sqlite3 .connect (filename )# connects the file and creates the database if it did not exist
13+ return db
14+
15+ def readwrite_database ():
16+ filename = 'demo.db' # database filename
17+ db = create_database (filename )
18+ db .execute ('drop table if exists testtable' )# to remove table of name 'testtable' if existing
19+ db .execute ('create table testtable (t1 text, i1 int)' )# create table 'testtable' with columns (t1,i1)=(text,int)
20+ db .execute ('insert into testtable (t1, i1) values (?, ?)' , ('one' ,1 ))# insert values in columns (t1,i1) of database testtable
21+ db .execute ('insert into testtable (t1, i1) values (?, ?)' , ('two' ,2 ))
22+ db .execute ('insert into testtable (t1, i1) values (?, ?)' , ('three' ,3 ))
23+ db .execute ('insert into testtable (t1, i1) values (?, ?)' , ('four' ,4 ))
24+ db .execute ('insert into testtable (t1, i1) values (?, ?)' , ('five' ,5 ))
25+ db .execute ('insert into testtable (t1, i1) values (?, ?)' , ('six' ,6 ))
26+ db .commit ()# commit after changing any data in database
27+
28+ cursor = db .execute ('select * from testtable order by t1' )# select data from testtable in order of t1 column
29+ for row in cursor :
30+ print (row )
31+
32+ cursor = db .execute ('select i1, t1 from testtable order by i1' )# select data from testtable in order of i1 column and also change the order of display
33+ for row in cursor :
34+ print (row )
35+
36+ def using_rowfactory ():
37+ filename = 'demo.db' # database filename
38+ db = create_database (filename )
39+ db .row_factory = sqlite3 .Row
40+ db .execute ('drop table if exists testtable' )# to remove table of name 'testtable' if existing
41+ db .execute ('create table testtable (t1 text, i1 int)' )# create table 'testtable' with columns (t1,i1)=(text,int)
42+ db .execute ('insert into testtable (t1, i1) values (?, ?)' , ('one' ,1 ))# insert values in columns (t1,i1) of database testtable
43+ db .execute ('insert into testtable (t1, i1) values (?, ?)' , ('two' ,2 ))
44+ db .execute ('insert into testtable (t1, i1) values (?, ?)' , ('three' ,3 ))
45+ db .execute ('insert into testtable (t1, i1) values (?, ?)' , ('four' ,4 ))
46+ db .execute ('insert into testtable (t1, i1) values (?, ?)' , ('five' ,5 ))
47+ db .execute ('insert into testtable (t1, i1) values (?, ?)' , ('six' ,6 ))
48+ db .commit ()# commit after changing any data in database
49+
50+ cursor = db .execute ('select * from testtable order by t1' )# select data from testtable in order of t1 column
51+ for row in cursor :
52+ print (row )
53+ print ('Notice: the iterator returns row objects. Read these row objects as dictionaries.' )
54+
55+ cursor = db .execute ('select * from testtable order by t1' )# select data from testtable in order of t1 column
56+ for row in cursor :
57+ print (dict (row ))
58+
59+ cursor = db .execute ('select * from testtable order by t1' )# select data from testtable in order of t1 column
60+ for row in cursor :
61+ print (row ['t1' ])
62+ print (row ['t1' ],row ['i1' ])
63+
64+
65+ def main ():
66+ readwrite_database ()
67+ using_rowfactory ()
68+
69+ if __name__ == '__main__' :main ()