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