Python Data Persistence - Inserting Rows

Python Data Persistence – Inserting Rows

The next step is to insert rows in the tables we have just created. We know the syntax of the INSERT statement and we have used it in console mode of sQLite in the previous chapter. To do it programmatically, declare an INSERT query string and use it as an argument to execute () method.

As noted in the previous chapter, the SQLite database engine is in auto-commit mode by default. To have better transaction control, we should commit the query operation only if it doesn’t encounter any exceptions.
Following code inserts a record in the Products table.

Example

import sqlite3
conn=sqlite3 . connect (' mydb. sqlite ' )
cur=conn.cursor( )
qry="insert into Products values (1,'Laptop', 25000);"
try: cur.execute(qry)
conn.commit( )
print ('Record inserted successfully')
except print ( 'error in insert conn.rollback ( ) operation')
conn.close ( )

In many cases, you may want to accept user input for field values to be inserted. You can form a query string by substituting the user inputs in the string with the help of string formatting technique, as shown below:

Example

>>> id=input('enter ProductID:1) enter ProductID:2
>>> nm=input('enter product name:') enter product name:Hard Disk
>>> p=int(input('enter price:')) enter price:5000
>>> qry="insert into products values ({ }, { }, { });"• format(id, nm, p)
> > > qry
'insert into products values (2, Hard Disk, 5000);'

You can very well use this query string as an argument of execute () method. However, query operations using Python’s string formatting is insecure as it makes the program vulnerable to SQL injection attacks. Hence., DB-API recommends the use of parameter substitution technique.

The execute () method of the sqlite3 module supports the use of question mark symbols (‘?’) as placeholders as well as named placeholders in which case the dictionary object of name and value pairs is given as the second argument to execute( ) method.

>>> #using '?' place holder
>>> cur.execute("insert into products values (?,?,?)",(id,nra,p))
>>> #using named place holder
>>> cur.execute("insert into products values
( :id, :nm, :p) ", {"id":id, "nm":nm, "p":p})

There is another useful variant of execute () method in the sqlite3 module. The first argument to execute the many( ) method is a query string with placeholders, and the second argument is a list of parameter sequences. The query gets executed for each sequence (itself may be a list or tuple) in the list. Following script (‘insertqry.py ) uses execute many () method to insert records in the Products table, as displayed in the previous chapter.

Example

import sqlite3
conn=sqlite3.connect('mydb.sqlite') cur=conn.cursor()
qry="insert into Products values (?,?,?)" pricelist=[(1, 'Laptop1 ,25000), (2, 'TV 1,40000) ,
(3,'Router',2000),(4,'Scanner',5000),
(5,'Printer',9000), (6,'Mobile',15000)]
try:
cur.executemany(qry, pricelist) conn.commit( )
print ('Records inserted successfully')
except:
print ('error in insert operation') conn.rollback( ) conn.close( )

You can check successful insertion on the SQLite console.

sqlite> select * from products;
ProductID Name Price
———- ——— ——-

1 Laptop 25000
2 TV 40000
3 Router 2000
4 Scanner 5000
5 Printer 9000
6 Mobile 15000

Python Data Persistence – Inserting Rows

Related