1+ '''
2+ Created on Aug 9, 2017
3+
4+ @author: sayalik157
5+ '''
6+
7+ import sqlite3 # python lib for sqlite3 database handling
8+
9+ class database :
10+ def __init__ (self ,** kwargs ):
11+ # note: because of decorators _filename and _table are set from filename and file respectively
12+ self .filename = kwargs .get ('filename' )
13+ self .table = kwargs .get ('table' ,'test' )# default tablename set as 'test'
14+
15+ def sql_do (self ,sql ,* params ):
16+ # arbitrary number of *params are given incase more parameters are needed for 'sql' command
17+ self ._db .execute (sql ,params )# execute sql command on database
18+ self ._db .commit ()
19+
20+ def insert (self ,row ):# inserting a row in database
21+ self ._db .execute ('insert into {} (t1, i1) values (?, ?)' .format (self ._table ), (row ['t1' ],row ['i1' ]))
22+ self ._db .commit ()
23+
24+ def retrieve (self ,key ):# to retrieve a row from database
25+ cursor = self ._db .execute ('select * from {} where t1 = ?' .format (self ._table ), (key ,))
26+ return dict (cursor .fetchone ())# fetch only one in dictionary form
27+
28+ def update (self ,row ):# to update a row in database
29+ self ._db .execute ('update {} set i1 = ? where t1=?' .format (self ._table ), (row ['i1' ],row ['t1' ]))
30+ self ._db .commit ()
31+
32+ def delete (self ,key ):
33+ self ._db .execute ('delete from {} where t1 = ?' .format (self ._table ), (key ,))
34+
35+ def display_rows (self ):
36+ cursor = self ._db .execute ('select * from {} order by t1' .format (self ._table ))
37+ for row in cursor :
38+ print ('{}:{}' .format (row ['t1' ],row ['i1' ]))
39+
40+ def __iter__ (self ):
41+ cursor = self ._db .execute ('select * from {} order by t1' .format (self ._table ))
42+ for row in cursor :
43+ yield dict (row )
44+
45+ @property # filename getter decorator
46+ def filename (self ):return self ._filename
47+
48+ @filename .setter # filename setter decorator
49+ def filename (self ,filename ):
50+ self ._filename = filename
51+ self ._db = sqlite3 .connect (self ._filename )
52+ self ._db .row_factory = sqlite3 .Row
53+
54+ @filename .deleter # filename deleter decorator
55+ def filname (self ):self .close ()# close the database
56+
57+ @property #decorator to get tablename
58+ def table (self ):return self ._table
59+ @table .setter # decorator to set tablename
60+ def table (self ,t ):self ._table = t
61+ @table .deleter # decorator to delete tablename
62+ def table (self ):self ._table = 'test'
63+
64+ def close (self ):
65+ self ._db .close ()# close the database
66+ del self ._filename
67+
68+ def main ():
69+ db = database (filename = 'test.db' ,table = 'test' )
70+
71+ print ('Create table test' )
72+ db .sql_do ('drop table if exists test' )
73+ db .sql_do ('create table test ( t1 text, i1 int )' )
74+
75+ print ('Create rows' )
76+ db .insert (dict (t1 = 'one' ,i1 = 1 ))
77+ db .insert (dict (t1 = 'two' ,i1 = 2 ))
78+ db .insert (dict (t1 = 'three' ,i1 = 3 ))
79+ db .insert (dict (t1 = 'four' ,i1 = 4 ))
80+ for row in db :print (row )
81+
82+ print ('Retrieve rows' )
83+ print (db .retrieve ('one' ),db .retrieve ('two' ))
84+
85+ print ('Update rows' )
86+ db .update (dict (t1 = 'one' ,i1 = 101 ))
87+ db .update (dict (t1 = 'three' ,i1 = 103 ))
88+ for row in db :print (row )
89+
90+ print ('Delete rows' )
91+ db .delete ('one' )
92+ db .delete ('three' )
93+ for row in db :print (row )
94+
95+ print ('Done!!' )
96+ if __name__ == '__main__' :main ()