Author name: Prasanna

Python Data Persistence – RDBMS Products

Relational Software Inc. (now Oracle Corp) developed its first SQL-based RDBMS software called Oracle V2. IBM introduced System-R as its RDBMS product in 1974 and followed it by a very successful DB2 product.
Microsoft released SQL Server for Windows NT in 1994. Newer versions of MS SQL server are integrated with Microsoft’s .NET Framework.
SAP is an enterprise-level RDBMS product targeted towards UNIX-based systems being marketed as Enterprise Resource Planning (ERP) products.

An open-source RDBMS product named MySQL, developed by a Swedish company MySQL AB was later acquired by Sun Microsystems, which in turn has, now, been acquired by Oracle Corporation. Being an open-source product, MySQL is a highly popular choice, after Oracle.
MS Access, shipped with Microsoft Office suite, is widely used in small-scale projects. The entire database is stored in a single file and, hence, is easily portable. It provides excellent GUI tools to design tables, queries, forms, and reports.

PostgreSQL is also an open-source object-oriented RDBMS, which has evolved from the Ingres project of the University of California, Berkley. It is available for use on diverse operating system platforms and SQL implementation is supposed to be closest to SQL standard.
SQLite is a very popular relational database used in a wide variety of applications. Unlike other databases like Oracle, MySQL, etc., SQLite is a transactional SQL database engine that is self-contained and serverless. As its official documentation describes, it is a self-contained, serverless, zero-configuration, transactional SQL database engine. The entire database is a single file that can be placed anywhere in the file system.

SQLite was developed by D. Richard Hipp in 2000. Its current version is 3.27.2. It is fully ACID compliant which ensures that transactions are atomic, consistent, isolated, and durable.
Because of its open-source nature, very small footprint, and zero-configuration, SQLite databases are popularly used in embedded devices, IoT, and mobile apps. Many web browsers and operating systems also use SQLite database for internal use. It is also used as prototyping and demo of larger enterprise RDBMS.

Despite being very lightweight, it is a full-featured SQL implementation with all the advanced capabilities. SQLite database can be interfaced with most of the mainstream languages like C/C++, Java, PHP, etc. Python’s standard library contains the sqlite3 module. It provides all the functionality for interfacing the Python program with the SQLite database.

7.4 SQLite installation

Installation of SQLite is simple and straightforward. It doesn’t need any elaborate installation. The entire application is a self-contained executable ‘sqlite3.exe’. Official website of SQLite, (https://sqIite.org/download.html) provides pre-compiled binaries for various operating system platforms containing the command line shell bundled with other utilities. All you have to do is download a zip’archive of SQLite command-line tools, unzip to a suitable location and invoke sqlite3.exe from DOS prompt by putting name of the database you want to open.
If already existing, the SqLite3 database engine will connect to it; otherwise, a new database will be created. If the name is omitted, an in memory transient database will open. Let us ask SQLite to open a new
mydatabase.sqlite3.

E:\SQLite>sqlite3 mydatabase.sqlite3
SQLite version 3.25.1 2018-09-18 20:20:44
Enter “.help” for usage hints.
sqlite>

In the command window a sqlite prompt appears before which any SQL query can be executed. In addition, there “dot commands” (beginning with a dot “.”) typically used to change the output format of queries, or to execute certain prepackaged query statements.
An existing database can also be opened using .open command.

E:\SQLite>sqlite3
SQLite version 3.25.1 2018-09-18 20:20:44
Enter “.help” for usage hints.
Connected to a transient in-memory database.
Use “.open FILENAME” to reopen on a persistent database.
sqlite> .open test.db

The first step is to create a table in the database. As mentioned above, we need to define its structure specifying name of the column and its data type.

SQUte Data Types

ANSI SQL defines generic data types, which are implemented by various RDBMS products with a few variations on their own. Most of the SQL database engines (Oracle, MySQL, SQL Server, etc.) use static typing. SQLite, on the other hand, uses a more general dynamic type system. Each value stored inSQLite database (or manipulated by the database engine) has one of the following storage classes:
• NULL
• INTEGER
• REAL ‘
• TEXT
• BLOB
A storage class is more general than a datatype. These storage classes are mapped to standard SQL data types. For example, INTEGER in SQLite has a type affinity with all integer types such as int, smallint, bigint, tinyint, etc. Similarly REAL in SQLite has a type affinity with float and double data type. Standard SQL data types such as varchar, char,nchar, etc. are equivalent to TEXT in SQLite.
SQL as a language consists of many declarative statements that perform various operations on databases and tables. These statements are popularly called queries. CREATE TABLE query defines table structure using the above data types.

CREATE TABLE

This statement is used to create a new table, specifying following details:
• Name of new table
• Names of columns (fields) in the desired table
• Type, width, and the default value of each column.
• Optional constraints on columns (PRIMARY KEY, NOT NULL, FOREIGN KEY)

Example

CREATE TABLE table_name (
column 1 datatype [width] [default] [constraint],
column2 ,
column3 …,
) ;

Python Data Persistence – Transaction Control

As mentioned above, SQLite is a transactional database and all transactions are ACID compliant. ACID stands for Atomic, Consistent, Isolated, and Durable. As a result, it ensures that the SQLite database doesn’t lose integrity, even if transaction such as INSERT, DELETE, or UPDATE, is interrupted because of any reason whatsoever.

A transaction is the propagation of changes to the database. The operation performed by INSERT, UPDATE, or DELETE statement results in a transaction.

Atomicity: When we say that a transaction should be atomic, it means that a change cannot be affected in parts. Either the entire transaction is applied or not applied.

Consistency: After any transaction is completed, the database should hold on to the changes in its state.
Isolation: It must be ensured that the transaction such as INSERT, UPDATE, or DELETE, performed by a client should only be visible to other clients after successful completion.

Durability: The result of successfully committed transactions must be permanent in the database regardless of the condition such as power failure or program crash.

SQLite provides two statements for transaction control. They are COMMIT and ROLLBACK. All CRUD (CREATE. RETRIEVE, UPDATE] and DELETE) operations first take effect in memory, and then they are permanently saved (committed) to the disk file. SQLite transactions are automatically committed without giving any chance to undo (roll back) the changes.
To control the commitment and rolling back manually, start transactions after issuing the directive BEGIN TRANSACTION. Whatever operations are done thereafter will not be confirmed, until COMMIT is issued and will be annulled if ROLLBACK is issued.

sqlite> select * from products;
productID      Name         price
----------     --------        -------
      1            Laptop       27500
      3           Router         2200
      4          Scanner        5500
      5          printer        11000
      6          Mobile        16500
sqlite> begin transaction;
sqlite> update products set price=2000 where name = 'Router';
sqlite> select * from products;
productID         Name         price
----------        --------        -------
      1               Laptop        27500
      3               Router        2200
      4              Scanner       5500
      5              printer         11000
     6               Mobile        16500
sqlite> rollback;
productID      Name        price
----------       --------     -------
      1             Laptop      27500
      3             Router       2200
      4             Scanner      5500
      5             printer        11000
      6             Mobile       16500

In the above example, the price of ‘ Router ‘ is initially 2200. It was changed to 2000 but rolled back. Hence its earlier value is restored. The following example shows the effect of the commit statement where the effect of the UPDATE statement is confirmed.

sqlite> begin transaction;
sqlite> update products set price=3000 where name='Router'; 
sqlite> commit;
sqlite> select * from products; 
productID        Name           price
----------          --------        -------
     1                 Laptop         27500
     3                 Router          2200
     4                 Scanner        5500
     5                 printer         11000
     6                Mobile          16500

Python Data Persistence – MySQL

So far we have learned how some basic SQL operations are performed over a relational database using SQLite console. Similar console-driven interaction is possible with other RDBMS products. MySQL console works more or less similar (barring certain syntactical differences) to the SQLite console we’ve used in this chapter. Following piece of code shows a sample MySQL console session:

Welcome to the MySQL monitor. Commands end with; or \g. 
Your MySQL connection id is 17
Server version: 5.7.23 MySQL Community Server (GPL) Copyright (c) 2000, 2018, 
Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective owners.
Type 'help;1 or '\h' for help. Type '\c' to clear
the current input statement. 
mysql> show databases;
+------------------------+
 | Database                     |
+------------------------+
 | information_schema   |
 |mydb                            | 
 |mysql                            |
 | performance_schema  |
 |I sys                              |
+------------------------+
5 rows in set (0.03 sec) 
mysql> use mydb;
Database changed 
mysql > CREATE TABLE products 
         -> ( productID varchar(5),
         -> productName varchar(20),
         -> price Numeric(7,2));
Query OK, 0 rows affected (0.17 sec)
mysql> insert into products values (1, 'TV', 40000);
Query OK, 1 row affected (0.06 sec) 
mysql> select * from products;
+-------------+---------------+---------+
 | productID    | productName | price    |
+-------------+---------------+---------+
|       1            |          TV         | 40000.00 |
+-------------+---------------+---------+
1 row in set (0.05 sec)

MS SQL Server also has a console-based frontend called SQLCMD which also works similarly. The command-line interface of Oracle is called SQL*Plus. As far as PostgreSQL is concerned, its primary command-line interface is psql program.

All the RDBMS products also provide GUI-based environments to perform various SQL-related operations instead of command-line actions. Oracle’s SQL Developer, Microsoft’s SQL Server management studio, pgAdmin for PostgreSQL, and Workbench for MySQL are respective examples. SQL Server client is integrated with Visual Studio which helps the user to perform database operations graphically. MySQL module is shipped with various web server software bundles (for example, LAMP, XAMPP, etc.), providing a web-based interface called PhpMyAdmin. (figure 7.2)

Although SQLite doesn’t provide its own GUI tool for database management, many third-party tools are available. One such utility is SQLiteStudio that is very popularly used.

Python Data Persistence – Python DB-API

The previous chapter was an overview of SQL fundamentals with the help of the SQLite database. However, as mentioned therein short, there are several equally popular RDBMS in use worldwide. Some of them are open-source and others for enterprise use. Although all of them use SQL underneath them, there are a lot of differences in the implementation of SQL standards by each of them. This also reflected in Python’s interface modules written by individuals for interaction with databases. Since each module defined its own functionality for interaction with the respective database product, its major fallout is a lack of compatibility. If for some reason, a user is required to switch to a different database product, almost the entire code that handles the back-end processing had to be rewritten.

To find a solution for this incompatibility issue, a ‘Special Interest Group’ was formed in 1996. This group (db-sig) recommended a set of specifications by raising ‘Python Enhancement Proposal (PEP 248)’ for a consistent interface to relational databases known as DB-API. The specifications have since been modified by subsequent enhancement proposals (PEP 249). Its recommendations are called DB-API Version 2.0.

As various Python database modules have been made DB-API compliant, most of the functionality required to interact with any database is uniform. Hence, even if the database itself is switched, only a couple of changes in the code should be sufficient.

Oracle, the world’s most popular relational database can be interface with Python with more than one module. cx_OracIe is a Python extension module that enables access to Oracle Database with some features of its owrt in addition to DB-API. It can be used with Oracle 11.2,12.1, and 12.2, and 18.3 client libraries. There is pyodbc module that acts as a Python- ODBC bridge driver that can be used for Python-Oracle interaction as well.

To use Microsoft’s SQL Server database with Python also, there are a couple of alternatives. The pymysql module is there in addition to pyodbc module.

As far as PostgreSQL is concerned, psycopg2 module is the most popular PostgreSQL adapter for the Python programming language.
MySQL is also a very popular relational database, especially in the open-source domain. MySQL Connector/Python is a standardized database driver provided by MySQL itself. There is a mysqldb module for the Python interface but is not still compatible with Python 3.x. You can use pymysql module as a drop-in replacement for mysqldb module while using the Python 3.x version.

As mentioned in the previous chapter, Python’s standard library consists of the sqlite3 module which is a DB-AP1 compliant module for handling the SQLite database through Python program. While other modules mentioned above should be installed in the current Python installation – either by pip utility or by using a customized installer (as in case of MySQL Connector/ Python), the sqlite3 module needs no such installation.

sqlite3 Module

SQLite is often used as a prototyping tool for larger databases. The fact that SQLite is extremely lightweight and doesn’t require any server but still is a fully-featured implementation of SQL, it is a common choice in the developmental stage of an application and is eventually replaced by enterprise RDBMS products such as Oracle. Likewise, you can think of the sqlite3 module as a prototype DB-API module. We shall explore the DB-API specifications with the help of the sqlite3 module. We will soon discover how easy it is to switch to other databases just by modifying a couple of statements.

Let us start by importing the sqlite3 module. Note that, its target SQLite library version may be different from the SQLite binary downloaded by you as shown in the previous chapter.

Example

>>> import sqlite3 
>>> sqlite3.sqlite_version 
'3.21..0'

Connection Object

The all-important connection object is set up by the module level connect ( ) function. The first positional argument to this function is a string representing path (relative or absolute) to an SQLite database file. The function returns a connection object referring to the database file – either existing or new. Assuming that, ‘newdb. sqlite’ doesn’t already exist, the following statement opens it:

>>> conn=sqlite3.connect('newdb.sqlite') 
>>> type(conn)
<class 'sqlite3.Connection'>

As we’ve seen in the previous chapter, SQLite supports an in-memory database. To open the same programmatically use “:memory: ” as its path.

>>> conn=sqlite3.connect(":memory:")

The connection object has access to various methods in the connection class. One of them is a cursor () method that returns a cursor object, which we shall know in the next section. Transaction control is achieved by commit () and rollback () methods of the connection object. Connection class has important methods to define custom functions and aggregates to be used in SQL queries. Later in this chapter create_ function () and create_aggregate () methods are explained.

cursor Object

After opening the database, the next step is to get a cursor object from it which is essential to perform any operation on the database. A database cursor enables traversal over the records in a database. It facilitates CRUD operations on a table. The database cursor can be considered similar to the concept of an iterator. The cursor ( ) method on the connection object returns the cursor object.

>>> cur=conn.cursor()
>>> type(cur)
<class 'sqlite3.Cursor'>

Once we get hold of the cursor object, we can perform all SQL query operations, primarily with the help of its execute () method. This method needs a strong argument which must be a valid SQL statement. String argument having incorrect SQL statement raises exceptions as defined in the sqlite3 module. Hence, it is recommended that a standard exception handling mechanism is used.

Updating Data

It is fairly straightforward to programmatically perform update operations on a table in the SQLite database. As pointed out in the previous chapter, the update query is normally a conditional operation unless you intend to update all rows of a certain table. Hence a parameterized query is ideal for this purpose.

Following script (1 updateqry.py!) asks the user to input the name of the product and new price and performs update operation accordingly.

Example

import sqlite3
conn=sqlite3.connect(1mydb.sqlite')
nm=input(1 enter name of product:')
p=int(input('new price:'))
qry='update Products set price=? where name=?'
cur=conn.cursor()
try;
cur.execute(qry, (p,nm))
print ('record updated')
conn.commit()
except:
print ('error in update operation')
conn.rollback()
conn.close( )

Run the above script from a command prompt:

E:\python37>python updateqry.py 
enter the name of the product: TV 
new price:32000 
record updated

The SQLite console can confirm the above action.

sqlite> select * from products where name='TV';
ProductID    Name          Price
------------  --------      --------
2                  TV            32000

Python Data Persistence – Creating Table

We shall now add a table in our newly created ‘mydb.sqlite’ database. In the following script, the first two steps are as illustrated above – setting up connection and cursor objects. Next, we call execute () method of cursor object, giving it a string with CREATE TABLE statement inside. We shall use the same ‘Products’ table that we created in the previous chapter. Save the following script as ‘createqry.py’ and execute it.

Example

import sqlite3
conn=sqlite3.connect('mydb.sqlite')
cur=conn.cursor()
qry=' ' '
CREATE TABLE Products (
ProductID INTEGER PRIMARY KEY AUTOINCREMENT,
Name     TEXT (20) ,
Price    INTEGER
) ;
' ' '
try:
        cur.execute(qry)
        print ('Table created successfully')
except:
        print ('error in creating table')
conn.close ( )

Products table will be created in our database. We can verify by listing out tables in this database in the SQLite console, as we did in the previous chapter.

sqlite> .open mydb.sqlite 
sqlite> .tables 
Products

Let us also create ‘Customers’ and ‘Invoices’ tables with the same structure as used in the previous chapter. Here, we use a convenience method to execute the script ( ) that is defined in the cursor class. With its help, it is possible to execute multiple execute statements at once.

Example

import sqlite3
conn=sqlite3.connect('mydb.sqlite')
cur=conn.cursor( )
qry= ' ' '
CREATE TABLE Customers (
          CustID INTEGER PRIMARY KEY AUTOINCREMENT,
          Name           TEXT (20),
          GSTIN           TEXT (15)
   ) ;
CREATE TABLE Invoices (
         InvID             INTEGER             PRIMARY KEY
AUTOINCREMENT,
       CustID               TEXT                REFERENCES Customers
(CustID),
      ProductID        INTEGER            REFERENCES Products
(ProductID),
     Quantity         INTEGER (5)
) ;
' ' '
try:
      cur.executescript(qry)
      print ('Table created successfully')
except:
     print ('error in creating table')
conn.close( )

You can go back to the SQLite console and refresh the table list to confirm that all three tables are created in mydb.sqlite database.

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 – Deleting Rows

Similarly, we can programmatically delete a certain record from a table. This operation is also more often than not conditional. Hence, the ‘WHERE’ clause appears in the parameterized DELETE query. Following script (deleteqry.py) deletes row belonging to the user-specified product.

Example

import sqlite3
conn=sqlite3.connect('mydb.sqlite')
nm=input('enter product to delete:1)
qry='delete from Products where name=?'
cur=conn.cursor( )
try:
cur.execute(qry, (nm,))
print ('record deleted')
conn.commit ( )
except:
print ('error in delete operation')
conn.rollback( )
conn.close( )

To delete the user input product, run the above script from the command prompt.

E:\python37>python deleteqry.py 
enter product to delete: Printer 
record deleted

Execute select query in SQLite console to verify that deleted product doesn’t appear in the list.

sqlite> select * from products;
ProductID      Name         Price
---------        ---------      --------
    1                Laptop        25000
    2                   TV           32000
    3              Router           2000
   4               Scanner        5000
   6               Mobile          15000

The next section explains how to programmatically retrieve records from a table.

Python Data Persistence – ResultSet Object

We need to call execute ( ) method on cursor object with a SELECT query string as its argument and a query result set is built which is similar to an iterator of rows returned in response to the query. The module provides the following methods to traverse the result set:

fenchone (): Next row in the query result set is retrieved and returned in the form of a tuple. The method returns None if there are no more rows to be fetched,

fetchall (): This method returns a list of all available rows in the result set. It contains a sequence corresponding to each row. You can employ a regular for loop to traverse the rows, as follows:

Example

import sqlite3
conn=sqlite3.connect('mydb.sqlite')
cur=conn.cursor()
qry="select * from Products;"
cur.execute(qry)
rows=cur.fetchall()
for row in rows:
        print (row) 
conn.close()

Run the above code (‘ selectqry. py’) from the command prompt.

E:\python37>python selectqry.py
(1,  'Laptop', 25000)
(2, ’TV', 40000)
(3, 'Router', 2000)
(4, 'Scanner' , 5000)
(5, 'Printer' , 9000)
(6, 'Mobile', 15000)

The fact that execute () method runs a parameterized query can be used to good effect to search for a certain condition in the table. The following code snippet accepts the product’s name as input and displays its price.

Example

import sqlite3
conn=sqlite3.connect('mydb.sqlite') nm=input ('Enter name of product:') cur=conn.cursor( )
qry="select * from Products where name=?";
cur.execute(qry, (nm,))
row=cur.fetchone( )
print (row)
conn.close( )

When the above script is run from the command prompt, then it above script shows the following output:

E:\python37>python selecttqry.py 
Enter name of product:TV 
(2, 'TV', 40000)

Individual items in the ‘row’ tuple can be accessed by index. The row can also be unpacked in separate variables as under:

Example

row=cur fetchone( ) 
print ( ' ID: ', row [ 0] , 'Name: 1, row[1], 'price:', row [2 ] ) 
id, nm, p=row 
print ( ID:', id, 'Name:', nm, 1 price: ' , p)

Python Data Persistence – User Defined Functions

The SQLite database engine by itself is equipped with several built-in functions for finding string length, changing case to upper/lower case, rounding a number, etc. However, it doesn’t have the provision to define a new function with customized functionality. The SQLite module, however, has the provision to do so’ with the help of the create_function () method available to the connection object.

In the following example, we try to represent the price of the product rounded to thousands and attach a ‘k‘ alphabet to it. In other words, 40000 is represented by 40k. First, we define a regular Python function (myfunction) that accepts a number, divides it by 1000, and appends ’k’ to its string conversion. The create_f unction () method has the following prototype:

Example

create_function(SQLFunction, parameters,PythonFunction)

In other words, it assigns a name to the Python function(a function in our case) that can be used as a function in the SQL query.

Example

import sqlite3
conn=sqlite3.connect('mydb.sqlite') def myfunction(num):
return str(round(num/1000))+"k" conn.create_function('priceinK' , 1,myfunction) cur=conn.cursor()
qry="select name, priceinK(price) from products;"
cur.execute(qry)
rows=cur.fetchall( )
print (rows)
conn.close ( )

Output of above code snippet is:

Example

[('Laptop', '25k'), ('TV', '40k'), ('Router', '2k'),
('Scanner', '5k'), ('Printer', '9k'), ('Mobile',
'15k') ]

SQLite also has several built-in aggregate functions such as SUM, AVG, COUNT, etc. to be applied to one or more columns in a table. For example, the query selects SUM (price) from Products’returns sum of values in the price column of all rows. Using the create_aggregate() method defined to be used with the cursor object, it is possible to define a customized aggregate function.

In the following script, a regular Python class named my class s is defined and it contains a step( ) method which is mandatory for the user-defined aggregate function. The step() method increments the count for each product name ending with ‘r\ The create_aggregate () method attaches a name that can be used in the SQL query. When this aggregate function is called, the value returned by finalize () method of the class is in fact the result of the SELECT statement.

Example

import sqlite3
conn=sqlite3.connect('mydb.sqlite')
class myclass:
def__init__(self):
self.count=0
def step(self, string):
if string.endswith('r'):
self.count=self.count+1
def finalize (self) :
return self.count
conn.create_aggregate(1MyF',1,myclass)
cur=conn.cursor()
qry="select MyF(name) from products;"
cur.execute(qry)
row=cur.fetchone()
print ('number of products with name ending with ' r1 : ' , (row) [0] )
conn.close()

The output of the above script is:

Example

number of products with the name ending with : 3

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)

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.