Python

Python Data Persistence – Inheritance

Of all features of object-oriented programming methodology, inheritance is arguably the most important, and hence the most used feature in software development. Leaving aside its literary meaning, inheritance in the context of programming refers to the mechanism by which features of one class (attributes and methods) are made available to another class that has the option to define additional resources or modify the functionality of inherited methods.

Python makes extensive use of inheritance. As we have seen, each class is inherited from the built-in object class. Inheritance enables a template definition of properties in a parent class to be implemented by its subclass for a more specific purpose.

One such case in point is the relationship between built-in classes ‘int’ and ‘bool’. Inheritance comes into the picture whenever there is ‘IS A’ kind of relationship between classes. Here a ‘bool’ object IS A(n) ‘int’ right? Hence ‘int’ class is a superclass of the ‘bool’ class. As you know a bool object has only two possible values True and False. They are equivalent to 1 and 0 respectively which are integers by the way.

Let us establish the inheritance of two customized classes with the following example. Ellipse an elongated circle with two radii and one radius is larger than the other. The circle can be seen as a more specific case of ellipse where two radii are equal. So circle IS an A(n) ellipse! We are going to define an ellipse class with two instance attributes and two methods area( ) and perimeter( ).

Example

#inheritEllipse.py
import math
class ellipse:
      def__init__(self, r1, r2):
            self.radius1=r1
            self.radius2=r2
     def area(self):
           area=math.pi*self.radiusl*self.radius2
             return area
def perimeter(self):
            perimeter=2 *math.pi*math. 
sqrt((pow(self.radius1,2)+pow(self.radius2,2))/2)
           return perimeter

Note that formula for area of ellipse = π*rl *r2 and perimeter of ellipse =2π

Let us import this class in the interpreter and declare objects as follows:

Example

>>> from inheritEllipse import ellipse
>>> e1=ellipse (20,30)
>>> e1.area()
1884.9555921538758
>>> e1.perimeter()
160.19042244414092
>>> e1=ellipse(20,20)
>>> e1.area()
1256.6370614359173
>>> e1.perimeter()
125.66370614359172

Note that in the second case both radii are equal, hence the ellipse happens to be a circle. We now design a circle class using the ellipse class as its parent. Add following lines in inheritEllipse .py code.

Example

class circle(ellipse) :
           def__init___(self, r1, r2=None):
                    super ( ) .__init__(r1 , r2)
                             self.radius2=self.radius1

Just import this inherited circle class, declare an object with a radius of 20. The result shows that the perimeter and area match that of the ellipse object with identical radii.

Example

>>> from inheritEllipse import ellipse, circle
>>> c1=circle(20)
>>> c1.area()
1256.6370614359173
>>> c1.perimeter()
125.66370614359172
> > >

Python Data Persistence – Class Level Attributes and Methods

In the above example, MyClass defines two data variables __myname and __myage that are instance attributes. They are invariably initialized through __init__( ) constructor. Their values are different for each object. A class however may have an attribute such that its value is the same for all existing objects. In other words, such attribute is a shared or common resource and defined outside the __init__( ) method, for that matter outside any instance method.

In the following script, the total is a class variable of a player class. It is defined with the purpose of maintaining a running sum of runs scored by each player. The player class defines a name and runs as instance attributes initialized through __init__( ) method as usual which also keeps on adding runs of each object.

Example

#classattr.py
class player:
      __total=0
      def__init__(self, name, runs):
            self.__name=name
            self.___runs=runs
            player.__total+=self.__runs
            print ('Total runs so far:',player.___total)

Let us import the player class and set up a few objects. Following interpreter, activity shows that the ___total variable is being cumulatively updated by ___runs of each object.

Example

>>> from classattr import player
>>> p1=player('Virat', 60)
Total runs so far: 60
>>> p2=player('Rahul', 45)
Total runs so far: 105

Two things are to be noted here. First, the use of the += operator. It is an in¬place addition operator effectively assigning the addition of two operands back to the left operand. Hence player. total==+=self. runs actually becomes player. total=player. total+self.runs . In-place variations of other operators defined in Python are +=, -=, *=, /=, and so on.

Secondly, the value of __total is retrieved with the help of the name of the class (player.__total) rather than self. This is obvious because the total is a class variable and not an instance variable specific to any particular object.
In view of this feature, Python has a provision to define methods that can access such class attributes. A class method needs the name of the class to be passed to it as an argument (conventionally using ‘cls’ identifier). The class can also have a static method that doesn’t need an explicit reference to either class or object which means there’s no argument to it in the form of self or els.

Class method and static method is decorated by built-in @classmethod and @statiemethod directives.

Example

#classattr.py
class player:
       ___total = 0
       def__init__(self, name, runs):
               self.___name=name
               self.___runs=runs
               player.___total+=self.___runs
               print ('Total runs so far:',player.___total)
      @classmethod
      def printtotal(cls):
          print ('Total runs so far:',els.___total)
      @staticmethod
      def displaytotal( ):
                print ('Total runs so far:',player.__total)

Output

>>> from classattr import player 
>>> p1=player('Virat',60)
Total runs so far: 60 
>>> p2=player('Rahul',45)
Total runs so far: 105 
>>> player.printtotal( )
Total runs so far: 105 
>>> player.displaytotal( )
Total runs so far: 105

Python Data Persistence – Relational Database

The term ‘database’ refers to an organized collection of data so as to remove redundancy and inconsistency, and to ensure data integrity. Over the years, different database models have been in use. The early days of computing observed the use of hierarchical and network database models. Soon, they were replaced by the relational database model, which is still used very predominantly. The last 10-15 years have seen the emergence of NoSQL databases like MongoDB and Cassandra.

The relational database model, proposed by Edgar Codd in 1970, aims to arrange data according to the entities. Each entity is represented by a table (called a relation). You can think of the entity as a class. Just as a class, an entity is characterized by attributes (also called fields, in the database terminology) that form columns of the table. Each instance of the entity is described in subsequent rows, below the heading row. The entity table structure provides one attribute whose value is unique for each row. Such an attribute is called the ‘primary key’.

If we analyze the pricelist example above, it involves three entities, Customers, Products, and Invoices. We have prepared three tables representing them, as fol\o\\;s:(figure7.1)

The important aspect of relational database design is to establish a relationship between tables. In the three tables above, the attributes ‘prodlD’, ‘CustID’, and ‘InvNo’ are primary keys in products, customers, and invoices tables respectively.
Further, the structure of the ‘invoices’ table uses ‘CustID’ and ‘ProductID’ attributes which are the primary keys of the other two tables. When the primary key of one table appears in the structure of other tables, it is called ‘Foreign key’ and this forms the basis of the relationship between the two.

This approach of database design has two distinct advantages. Firstly, using the relationship between primary and foreign keys, details of the corresponding row can be fetched without repetition. For example, the ‘invoices’ table has the ‘ProdlD’ foreign key which is the primary key in the ‘Products’ table, hence the ‘name’ and ‘price’ attributes can be fetched using this relationship. The same is true about ‘CustID’ which appears as the foreign key in ‘invoices’ and is the primary key in the ‘customers’ table. We can thus reconstruct the original price list table by using relationships.

Secondly, you need not make any changes in the ‘invoices’ table, if either name of product or price changes, change in the ‘Products’ table will automatically reflect in all rows of invoices table because of the primary- foreign key relationship. Also, the database engine won’t allow deleting a certain row in the customers or products table, if its primary key is being used as foreign keys in the invoices table. This ensures data integrity.

Software products based on this relational model are popularly called Relational Database Systems (RDBMS). Some of the renowned RDBMS brands are Oracle, MySQL, MS SQL Server, Postgre SQL, DB2. SQLite, etc.

Python Data Persistence – INSERT Statement

Now that we have created tables in our database, let us add few records to them. SQL provides an INSERT statement for the purpose. Its standard syntax is as follows:

Example

INSERT INTO table name (coll, col2, ...) VALUES (vail, val2 , val3 , ...) ;

Name of the table in which a new record (row) is to be added, follows mandatory keywords INSERT INTO. The column list is given after the name in parentheses, which is followed by the VALUES clause. The data corresponding to each column is given in another set of parentheses. The following statement adds one record in the Products table:

sqlite> INSERT INTO Products (Name, Price) VALUES ('Laptop1, 40000);

We insert a row in the ‘Customers’ table by executing the following statement in the SQLite console:

sqlite> INSERT INTO Customers (Name, GSTIN) VALUES ('Ravikumar', 127AAJPL7103N1ZF');

Similarly, the following statement adds a record in the ‘Invoices’ table:

sqlite> INSERT INTO Invoices (CUSTID, PRODUCTID, Quantity) VALUES (1, 1, 2);

Note that, in the above INSERT statements, we have not included ProductID, CustID, and InvID columns in respective column list parentheses because they have been defined as autoincrement fields. The column list may be omitted altogether if you intend to provide values for all columns in the table (excluding autoincrement fields). They must be given in the VALUES list exactly in the same order in which their fields have been defined. You may add a few more records to these three tables. Sample data for these tables is given below: (table 7.3, table 7.4, and table 7.5)

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