Python Data Persistence – Backup and Restore Database

by

Python Data Persistence – Backup and Restore Database

It is extremely important to secure an organization’s data with periodic backup so that the same can be used to fall back in case of any damage. The sqlite3 module provides iterdump ( ) function that returns an iterator of the entire data of a database in the form of SQL statements. This includes CREATE TABLE statements corresponding to each table in the database and INSERT statements corresponding to rows in each table.

Let us demonstrate the effect of iterdump ( ) with the following example. First, we create a database with one table and insert a record in it. After that, we create a dump of the database. Run the following script and open the resultant backup.sql file with an editor.

Example

import sqlite3
conn=sqlite3.connect('sample.db')
qry='create table names (name text (20), address
text(20));'
conn.execute(qry)
qry="insert into names values('Anurag', 'Mumbai');"
cur=conn.cursor()
try:
cur.execute(qry) print ('record added') conn.commit()
except:
print ('error in insert operation')
conn.rollback()
conn.close()
#creating dump
conn=sqlite3.connect('sample.db')
f=open('dump.sql','w')
for line in conn.iterdump():
f.write('{}\n'.format(line))
f.close()
conn.close ()

The dump file, created, will look like the following:

Example

BEGIN TRANSACTION; 
CREATE TABLE names (name text (20), address 
text(20)); 
INSERT INTO "names" VALUES('Anurag' ,'Mumbai'); 
COMMIT;

To restore the database from the dumped version in ‘newsample.db’, we have to read its contents and execute SQL statements in it with the help of executescript ( ) method of the cursor object.

>>> conn=sqlite3.connect('newsample.db')
>>> f=open('dump.sql1,1r')
>>> qry=f.read( )
>>> f.close ( )
>>> cur=conn.cursor ( )
>>> cur.executescript(qry)
>>> conn, close ( )

The new database gets constructed from the backup. To verify, run a select query on its names table and display the result.

>>> conn=sqlite3.connect('newsample.db')
>>> cur=conn.cursor()
>>> cur.execute('select * from names;')
>>> row=cur.fetchone( )
> > > row
('Anurag', 'Mumbai')

As you can see the result is the same data inserted in the original database. As mentioned earlier, SQLite recognizes NULL, INTEGER, REAL, TEXT, BLOB as native data types. They are mapped to respective Python data types as per the following table:(table 8.1)

Python Type SQLite type
None NULL
Int INTEGER
Float REAL
Str TEXT
Bytes BLOB

The type system of the sqliteT module can be extended to store additional Python types in the SQLite database via object adaptation. You can let the sqlite3 module convert SQLite types to different Python types via converters. Discussion on adapters and converters is kept outside the scope of this book.

Before we discuss other DB-API compatible modules, one more thing is worth mentioning here. We have used The execute () method – and its other variants execute any( ) and execute scripts) – as defined in the cursor class of the sqlite3 module. These methods are also available for use with the connection object. However, as mentioned in the official documentation of the sqlite3 module, they are non-standard methods. It simply means that DB API recommends these methods be defined in cursor class and the connection object as defined in other modules (pymysql or pyodbc module for example) may not be able to call these execute() methods.