Author name: Prasanna

Python Data Persistence – dbm Modules

6.3 dbm Modules

These modules in Python’s built-in library provide a generic dictionary-like interface to different variants of DBM style databases. These databases use binary encoded string objects as key, as well as value. The dbm. gnu module is an interface to the DBM library version as implemented by the GNU project. On the other hand, dbm.ndbm module provides an interface to UNIX nbdm implementation. Another module, dbm. dumb is also present which is used as a fallback option in the event, other dbm implementations are not found. This requires no external dependencies but is slower than others.

Example

>>> import dbm
> > > db=dbm.open(1mydbm.db' , 'n' )
>>> db[1 title']=1 Introduction to Python'
>>> db['publisher 1] = 'BPB'
>>> db[1 year'] = '2 019 1
>>> db.close( )

As in the case of shelve database, user-specified database name carries ‘.dir’ postfix. The dbm object’s whichdb( ) function tells which implementation of dbm is available on the current Python installation.

Example

>>> dbm.whichdb('mydbm.db')
'dbm.dumb'

The open() function allows mode these flags: ‘c’ to create a new database with reading/write permission, ‘r’ opens the database in read-only mode, ‘w’ opens an existing database for writing, and ‘n’ flag always create a new empty database with read/write permissions.
The dbm object is a dictionary-like object, just like a shelf object. Hence, all dictionary operations can be performed. The following code opens ‘mydbm.db’ with ‘r’ flag and iterates over the collection of key-value pairs.

Example

> > > db=dbm.open('mydbm.db', 'r')
>>> for k,v in db.items():
print (k,v)
b'title' : b'Introduction to Python'
b'publisher' : b'BPB'
b'year' : b'2019'

Python Data Persistence – shelve Module

Serialization and persistence affected by functionality in this module depend on the pickle storage format, although it is meant to deal with a dictionary-like object only and not with other Python objects. The shelve module defines an all-important open( ) function that returns the ‘shelf object representing the underlying disk file in which the ‘pickled’ dictionary object is persistently stored.

Example

>>> import shelve
>>> obj =shelve.open('shelvetest1)

In addition to the filename, the open( ) function has two more optional parameters. One is ‘flag’ which is by default set to ‘c’ indicating that the file has read/write access. Other accepted values for flag parameters are ‘w’ (write-only), ‘r’ (read-only), and ‘n’ (new with reading/write access). The second optional parameter is ‘writeback’ whose default value is False. If this parameter is set to True, any modification made to the shelf object will be cached in the memory and will only be written to file on calling sync () or close ( ) methods, which might result in the process becoming slow.

Once a shelf object is declared, you can store key-value pair data to it. However, the shelf object accepts only a string as the key. Value can be any valid Python object.

Example

>>> obj ['name'] = 'Virat Kohli'
>>> obj ['age']=29
>>> obj ['teams']=['India', 'IndiaU19', 'RCB', 'Delhi']
>>> obj.close( )

In the current working directory, a file named ‘shelveset.dir’ will store the above data. Since the shelf is a dictionary-like object, it can invoke familiar methods of built-in diet class. Using the get () method, one can fetch a value associated with a certain key. Similarly, the update () method can be used to add/modify k-v pairs in shelf objects.

Example

>>> obj.get('name')
'Virat Kohli'
>>> dct = { '100s' :64, '50s' :69}
>>> obj.update(dct)
>>> diet(obj)
{'name': 'Virat Kohli', 'age': 29, 'teams':
['India', 'IndiaU19', 'RCB', 'Delhi'], '100s': 64,' 50s' : 69}

The shelf object also returns views of keys, values, and items,same as the built-in dictionary object.

Example

>>> keys=list(obj.keys())
>>> keys
['name', 'age', 'teams', '100s', '50s']
>>> values=list(obj.values() )
>>> values
['Virat Kohli', 29, ['India' , 'IndiaU19' 'RCB', 'Delhi’], 64, 69]
>>> items=list(obj.items())
>>> items
[('name', 'Virat Kohli'), (' age 1, 29), ( teams',
['India', ’IndiaU19', 'RCB', 'Delhi']), ' 100s ' , 64), ( ' 50s 1 , 69)]

Python Data Persistence – pickle Module

The serialization format used by the pickle module, which is a part of Python’s built-in module library, is very Python-specific. While this fact can work as an advantage that it doesn’t face any restrictions by certain external standards such as JSON format, the major disadvantage is that non-Python applications may not be able to reconstruct ‘pickled’ objects. Also, the pickle module is not considered secure when it comes to unpickling data received from an unauthenticated or untrusted source.

The pickle module defines the module-level dumps ( ) function to obtain a byte string ‘pickled’ representation of any Python object. Its counterpart function loads () reconstructs (‘unpickles’) the byte string identical Python object.

Following code snippet demonstrates the use of dumps () and loads () functions:

Example

>>> import pickle
>>> numbers=[10,20,30,40]
>>> pickledata=pickle.dumps(numbers)
>>> pickledata
b'\x80\x03]q\x00(K\nK\xl4K\xleK(e.'
>>> #unpickled data
. . .
>>> unpickledata=pickle.loads(pickledata)
>>> unpickledata
[10, 20, 30, 40]
>>>

There are dump ( ) and load () functions that respectively write pickled data persistently to a file-like object (which may be a disk file, a memory buffer object, or a network socket object) having binary and write ‘wb’ mode enabled, and reconstruct identical object from a file-like object having ‘rb’ permission.

Example

>>> #pickle to file
. . .
>>> import pickle
>>> numbers=[ 10 , 20 , 30 , 40 ]
>>> file=open ('numbers .dat' , ' wb ')
>>> pickle . dump (numbers, file)
>>> file . close ()
>>> #unpickle from file
. . .
>>> file=open {1 numbers . dat' , ' rb' )
>>> unpickledata=pickle . load (file)
>>> unpickledata
[10, 20, 30, 40]
>>>

Almost any type of Python object can be pickled. This includes built-in types, built-in, and user-defined functions, and objects of user-defined classes.

The pickle module also provides object-oriented API as a substitute for module-level dumps () /loads () and dump () /load () functions. The module has a pickier class whose object can invoke dump () or dumps () method to ‘pickle’ an object. Conversely, the unpicker class defines load () and loads () methods.

Following script has a person class whose objects are pickled in a file using pickier class. Original objects are obtained by the load () method of unpicker class.

Example

from pickle import Pickier, Unpickler
class User:
def__init__(self,name, email, pw):
self.name=name
self.email=email
self.pw=pw
def__str__(self):
return ('Name: { } email: { } password: {}'. \ format(self.name, self.email, self.pw))
user1=User('Raj an', '[email protected]', 'rajanl23')
user2=User('Sudheer', '[email protected]', 's 11')
print ('before pickling..')
print (user1)
print (user2)
file=open (' users . dat' , ' wb' )
Pickier (file) .dump (userl)
Pickier (file) .dump(user2)
file.close ()
file=open ( ' users . dat' , ' rb ' )
obj 1=Unpickler (file) . load ()
print ('unpickled objects')
print (obj1)
obj2=Unpickler (file) . load()
print (obj2)

Output:

E:\python37>python pick1-example.py before pickling.
Name: Rajan email: rl23w-gmail.com password: rajanl23 
Name: Sudheer email: s.lKwgmail.com password: s_ll unpickled objects
Name: Rajan email: rl2 3wgmail.com password: rajanl23 
Name: Sudheer email: s.llwgmail.com password: s_ll E:\python3 7 >

Python Data Persistence – SELECT Statement

This is one of the most frequently used SQL statements. The purpose of the SELECT statement is to fetch data from a database table and return it in the form of a result set. In its simplest form SELECT statement is used as follows:

Example

SELECT coll, col2, .., coin FROM table_name;

SQLite console displays data from the named table for all rows in specified columns. SQLite console offers two useful ‘dot’ commands for a neat and formatted output of the SELECT statement. The ‘.header on’ command will display the column names as the header of output. The ‘.mode column’ command will force the left alignment of data in columns.

sqlite> .header on
sqlite> .mode column
sqlite> select name as name, price from products;
name       Price
---------    --------
Laptop   25000
TV          40000
Router   2000
Scanner 5000
Printer   9000
Mobile  15000

You. can use wild card character to indicate all columns in the table.

sqlite> .header on 
sqlite> .mode column 
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

The ORDER BY clause lists selected rows according to ascending order of data in the specified column. The following statement displays records in the Products table in ascending order of price.

sqlite> select * from products order by price;
ProductID          Name              Price
-----------          ---------         ---------     
      3                  Router             2000
      4                  Scanner           5000
      5                  Printer            9000
      6                  Mobile           15000
      1                  Laptop           25000
      2                  TV                  40000

To enforce descending order, attach ‘DESC’ to the ORDER BY clause.

sqlite> select * from products order by name desc;
ProductID             Name              Price
-----------             --------            --------      
       2                     TV                  40000
       4                     Scanner          5000
       3                     Router            2000
       5                     Printer            9000
       6                     Mobile           15000
       1                     Laptop           25000

You can apply the filter on the selection of rows by using the WHERE clause. The WHERE keyword is followed by a logical condition having logical operators (<, >, <=, >=, =, IN, LIKE, etc.). In the following example, only those rows will be selected for which value of the ‘price’ column is less than 10000.

sqlite> select * from products where price<10000;
ProductID            Name           Price
------------         ---------         --------      
      3                    Router          2000
      4                    Scanner        5000
      5                    Printer          9000

A big advantage of the relational model comes through when data from two related tables can be fetched. In our ‘Invoices’ table, we have ProductID as one of the columns that are a primary key of the ‘Products’ table. The following example uses the WHERE clause to join two tables – Invoices and Products – and fetch data from them in a single SELECT statement.

sqlite> select InvID, Products. name, Products.Price,
Quantity 
. . .> from invoices, , Products where invoices.
productID= Products.ProductID; 
InvID            Name           Price             Quantity
-------          --------       ----------         --------
 1                Laptop         25000                 2
  2                 TV               40000                 1
  3                Mobile         15000                 3
  4                Mobile         15000                 1
  5                Printer          9000                   3
  6                   TV              40000                5
  7                Laptop          25000                4
  8                Router          2000                  10
  9                Printer          9000                   2
 10               Scanner        5000                   3

It is also possible to generate a calculated column depending on some operation on other columns. Any column heading can also be given an alias name using AS keyword.
Following SELECT statement displays Total column which is Products. Price*Quantity. The column shows values of this expression is named AS Total.

sqlite > select InvID, Products.: name t Products.
Price, Quantity, Products.Price *Quantity as
Total 
> from invoices, Products where
invoices.productID=Products.ProductID;
InvID            Name            Price            Quantity           Total 
--------        ---------        ----------       -----------      --------
   1            Laptop             25000                2                  50000
   2               TV                 40000                1                  40000
   3            Mobile             15000                3                  45000
   4            Mobile             15000                1                  15000
   5            Printer              9000                 3                   27000
   6             TV                   40000               5                   200000
  7            Laptop              25000               4                   100000
  8            Router               2000                10                   20000
  9            Printer               9000                 2                   18000
 10           Scanner             5000                 3                   15000

Python Data Persistence – UPDATE Statement

It is possible to modify data of a certain field in a given table using the UPDATE statement. The usage syntax of the UPDATE query is as follows:

Example

UPDATE table_name SET coll=vall, col2=val2,.., colN=valN WHERE [expression] ;

Note that the WHERE clause is not mandatory when executing the UPDATE statement. However, you would normally want to modify only those records that satisfy ing a certain condition. If the WHERE clause is not specified, all records will be modified.

For example, the following statement changes the price of ‘Printer’ to 1000Q.

sqlite> update products set price=10000 where name='Printer';
sqlite> select * from products;
Product ID                Name                 Price
  ----------                 -----------          ------  
      1                         Laptop              25000
      2                          TV                    40000
      3                         Router               2000
      4                         Scanner             5000
      5                         Printer              10000
      6                         Mobile             15000

However, if you want to increase the price of each product by 10 percent, you don’t have to specify the WHERE clause.

sqlite> update products set price=price+price*10/100;
sqlite> select * from products;
ProductID             Name                Price
 ----------             ----------          -------  
      1                      Laptop              27500
      2                      TV                     44000
      3                      Router                2200
      4                     Scanner               5500
      5                     Printer                11000
     6                      Mobile                16500

Python Data Persistence – pyodbc Module

ODBC is a language and operating system independent API for accessing relational databases. The product module enables access to any RDBMS for which the respective ODBC driver is available on the operating system. Most of the established relational database products (Oracle, MySQL, PostgreSQL, SQL Server, etc.) have ODBC drivers developed by the vendors themselves or third-party developers.

In this section, we access ‘mydb’ database deployed on the MySQL server. First of all, verify if your OS has a corresponding ODBC driver installed. If not, download MYSQL/ODBC connector compatible with your OS, MySQL version, and hardware architecture from MySQL’s official download page: https://dev.mysql.com/downloads/connector/odbc/ and perform the installation as per instructions.

The following discussion pertains to MySQL ODBC on Windows OS. You need to open the ODBC Data Sources app in the Administrative Tools section of the control panel, add a newly installed MySQL driver, if it doesn’t have the same already, and configure it to identify by a DSN (Data Source Name) with the help of MySQL sever’s user ID and password, pointing towards ‘mydb’ database.(figure 8.1)

This ‘MySQLDSN’ is now available for use in any application including our Python interpreter. You need to install pyodbc module for that purpose.

Start the Python interpreter and import this module. Its connect () function takes the DSN and other login credentials as arguments.

Example

>>> con=pyodbc.connect("DSN=MYSQLDSN;UID=root")

Once we obtain the connection object, the rest of the operations are exactly similar to that described with reference to the sqlite3 module. You can try creating Customers and Invoices tables in mydb database using their earlier structure and sample data.
In conclusion, we can say that the DB-API specification has made database handling very easy and more importantly uniform. However, data in SQL tables is stored basically in primary data types only which are mapped to corresponding built-in data types of Python. Python’s user-defined objects can’t be persistently stored and retrieved to/from SQL tables. The next chapter deals with the mapping of Python classes to SQL tables.

Python Data Persistence – Python – SQLAIchemy

The concluding paragraph of the previous chapter briefly talked about the disparity between type systems of SQL and object-oriented programming languages such as Python. Apart from Python’s Number (that too int and float only, not complex) and string types (which are generally called scalar types), SQL doesn’t have an equivalent data type for others such as diet, tuple, list, or any user-defined class.

If you have to store such an object in a relational database, it must be deconstructed into SQL data types first, before performing INSERT operation. On the other hand, a Python object of the desired type will have to be constructed by using data retrieved from a SQL table, before a Python script is able to process it.

Let’s take the case of ‘Products’ table in the SQLite database used in the previous chapter. Its structure is as follows:

Example

CREATE TABLE Products (
ProductID    INTEGER     PRIMARY KEY AUTOINCREMENT,
Name     TEXT (20),
Price       INTEGER
) ;

On the other side, Python script has a Products class and its object is populated with data as below:

Example

class Product 
def __init__(self, id, name, price):
           self.id=id
           self.name=name
           self.price=price
p1=Product(1, Laptop 1,25000)

Following sqlite3 module syntax, the following statement will insert pi object in the Products table:

Example

cur.execute("insert into products values 
(?,?,?);",(self.id, self.name, self.price))

Similarly, following statements will store retrieved data in an object of Products class.

Example

cur.execute('select * from products where name=?',
(1 Laptop',)) row=cur.fetchone()
p1=Products(row[0], row[1],row[2])

As you can see, this involves a tedious and explicit packing and unpacking of Python objects in order to be compatible with SQL data types. This is where Object Relational Mappers are useful.

WhatisORM?

An Object Relation Mapper (ORM) library provides a seamless interface between a class and a SQL table. A class is mapped to a certain table in the database, so that cumbersome to and fro conversion between object and SQL types are automated. The products class in Python code can be mapped to the Products table in the database. As a result, all CRUD operations are done with the help of objects only, not requiring hard-coded SQL queries to be used in Python script.

ORMs thus provides an abstraction layer over the raw SQL queries, thus enabling rapid application development. Such ORM libraries are available for most programming languages including Python. SQLAlchemy is a popular database toolkit widely used by Python developers. SQL ALchemy’s ORM system transparently synchronizes all changes in the state of an object of a user-defined class with its related row in the database table.

SQLAlchemy interacts with a certain type of database in association with the respective DB-API compliant module. Its dialect system is able to establish interaction with a database through the latter’s DB-API driver. That means you should have a corresponding DB-API module also installed along with SQLAlchemy to be able to use a particular type of RDBMS.

As a matter of fact, SQLALchemy library also contains, in addition to ORM API, the SQL Expression Language (SQLAlchemy Core) that executes primitive constructs of the relational database directly. While our focus in this chapter is on SQLALChemy ORM, we shall also briefly SQL Expression language in the end. (figure 9.1)

In most cases, SQLAlchemy is installed with the help of a pip utility. As explained in —, a virtual environment with SQLAlchemy installed will be used for this chapter. We need to activate it and start a Python interpreter.

Example

E:\SQLAlchemyEnv>scripts\activate 
(SQLAlchemyEnv) E:\SQLAlchemyEnv>python 
Python 3.7.2 (tags/v3.7.2:9a3ffc0492, Dec 23 2018, 23:09:28) 
[MSC v.1916 64 bit (AMD64)] on Win32 
Type "help", "copyright", "credits" or "license" for more information.
>>>

ORM – Session object

Now that we have created the Products table in the database, the next step is to start the transaction session. A session object is a handle used to interact with the database. We define a Session class that will serve as a factory for new Session objects with the help of the session maker () function.

from sqlalchemy.orm import sessionmaker 
Session = sessionmaker(bind=engine)

Here the engine is the Engine object that represents a connection with our database. Whenever you need to have a conversation with the database, you instantiate a Session:

session  = Session ( )

The session remains in force till changes to the database are committed and/or the close () method is called on a session object.

Python Data Persistence – SQLAlchemy ORM

The first step is to connect to a database by using the create_engine () function in sqlalchemy module. This function should be provided with the URL of the database. The easiest way is to connect to an in-memory SQLite database.

Example

>>> from sqlalchemy import create_engine
>>> engine=create_engine('sqlite:///:memory:')

To connect to a SQLite database file use URL similar to following: engine =create_engine(‘sqlite:///mydb.sqlite’)

As you know, the Python library has in-built support for SQLite in the form of a DB-API compatible sqlite3 module. However, for other databases, its respective module needs to be installed. In order to connect to a different database (other than SQLite), its corresponding connection string includes the dialect and module. The general format of use of the create_engine () function is as follows:

dialect+driver://username:password®host:port/ database

Hence, to connect to a MySQL database using pymysql module, we need to use the following statement:

engine = create_engine('mysql+pymydsql://root@ localhost/mydb')

This assumes that the MySQL server’s username is ‘roof with no password set. The create_engine () function returns Engine object. It represents the interface to the database. The ORM doesn’t use the Engine directly once created but is used behind the scenes. This function can accept the optional ‘echo’ argument which is False by default. If set to True, it causes the generated SQL to be displayed by the Python interpreter.

>>> engine=create_
engine(1sqlite:///:memory:',echo=True)

Python Data Persistence – ORM – Table Object and Mapped Class

The next step is to describe the database tables and define the mapping classes. An object of a metaclass, called Declarative Base class that stores a catalog of user-defined classes and mapped tables is first obtained. This Declarative Base class is defined in sqlalchemy. ext.declarative sub-module.

>>> from sqlalchemy.ext.declarative import 
declarative_base
>>> base=declarative_base( )

Use this ‘base’ class to define mapped classes in terms of it. We define the Products class and map it to the Products table in the database. Its table name property defines this mapping. Other attributes are column names in the table.

Example

#myclasses.py
from sqlalchemy.ext.declarative import declarative_ 
base
from sqlalchemy import Column, Integer, String base=declarative_base( ) 
class Product(Base):
tablename = 'Products'

ProductID = Column(Integer, primary_key=True) 
name = Column(String) 
price = Column(Integer)

The column is a SQL Alchemy schema object that represents column in the database table. Its constructor defines name, data type, and constraint parameters. The Column data type can be any of the following generic data types that specify the type in which Python data can be read, written, and stored. SQLAlchemy will choose the best database column type available on the target database when issuing a CREATE TABLE statement.

• Biglnteger
• Boolean
• Date
• DateTime
• Float
• Integer
• Numeric
• Smalllnteger
• String
• Text
• Time

Even though this class defines mapping, it’s a normal Python class, in which there may be other ordinary attributes and methods as may be required by the application.

The Table object is created as per the specifications in the class and is associated with the class by constructing a Mapper object which remains behind the scene and we normally don’t need to deal with it directly.

The Table object created in the Declarative system is a member of the MetaData attribute of the declarative base class. The create_all ( ) method is called on metadata, passing in our Engine as a source of database connectivity. It will emit CREATE TABLE statements to the database for all tables that don’t yet exist.

base.metadata.create_all(engine)

Complete process explained above is stored as a script (addproducts.py) in the root folder of our virtual environment.

Example

from sqlalchemy import Column, Integer, String 
from sqlalchemy.ext.declarative import declarative_ base
from sqlalchemy import create_engine
from myclasses import Product, base
engine = create_engine('sqlite:///mydb.sqlite',echo=True)
base.metadata.create_all(engine)

We run this script from the command prompt (from within our virtual environment of course). The command window will show, apart from other logging information, the equivalent CREATE TABLE statement emitted by SQLALchemy. (figure 9.1)

(SQLAlchemyEnv) E:\SQLAlchemyEnv>python class-table-mapping . py
PRAGMA table_info("Products")
( )
CREATE TABLE "Products" (
"ProductID" INTEGER NOT NULL, 
name VARCHAR, 
price INTEGER,
PRIMARY KEY ("ProductID")
)
( )
COMMIT

Python Data Persistence – ORM – Add Data

To add data in the ‘Products’ table, first initialize an object of its mapped Products class, add it to the session and commit the changes.

Example

p1 = Products(name='Laptop 1, price = 25000) 
sessionobj.add(p1) 
sessionobj.commit( )

Add above code snippets to addproducts.py. It now looks like this:

from sqlalchemy import Column, Integer, String
from sqlalchemy import create_engine
from myclasses import Products,base
engine = create_engine('sqlite:///mydb.sqlite', echo=True)
base.metadata.create_all(engine) 
from sqlalchemy.orm import sessionmaker 
Session = sessionmaker(bind=engine) 
sessionobj = Session()
p1 = Product(name='Laptop', price=25000) 
sessionobj.add(p1) 
sessionobj.commit( )

Run the above script from the command prompt. SQLAlchemy will emit equivalent parameterized INSERT query that will be echoed on the terminal as shown below in figure 9.2:

(SQLAlchemyEnv) E:\SQLAlchemyEnv>addproducts.py 
PRAGMA table_info("Products")
( )
BEGIN (implicit)
INSERT INTO "Products" (name, price) VALUES (?, ?) ('Laptop', 25000)
COMMIT

If you want to confirm, open the database in SQLite console and view’ rows in Products table, (figure 9.3)

sqlite> .head on
sqlite> .mode column
sqlite> .open mydb.sqlite
sqlite> select * from products;
ProductID        name          price
———-         ——-         ——
1               Laptop         25000

To add multiple records at once, call the add_all() method on the session object. It requires a list of objects to be added.

Example

p2=Products(name='TV',price=40000) 
p3=Products(name=1 Router',price = 2 000) 
p4 = Products(name=1 Scanner 1,price = 5000) 
p5 = Products(name='Printer' ,price = 9000) 
p6=Products(name='Mobile',price=15000) 
sessionobj.add_all( [p2,p3,p4,p5,p6]) 
sessionobj.commit( )

Go ahead and add the ‘Customers’ class mapped to the ‘Customers’ table. Add data as per sample data given. (We shall add ‘Invoices’ class and ‘Invoices’ table a little later)

Example

class Customer(base):
table name ='Customers'
CustID=Column(Integer, primary_key=True) 
name=Column(String)
GSTIN=Column(String)

We have to add this table in the database schema by executing the following statement again:

base.metadata.create_all(engine)