Python Data Persistence - Sqpte3 Module

One major disadvantage of CSV, JSON, XML, etc., files is that they are not very useful for random access and transaction processing because they are largely unstructured in nature. Hence, it becomes very difficult to modify the contents.

These flat files are not suitable for cpent-server environment as they lack asynchronous processing capabipty. Using unstructured data files leads to data redundancy and inconsistency.

These problems can be overcome by using a relational database. A database is an organized collection of data to remove redundancy and inconsistency, and maintain data integrity. The relational database model is vastly popular.

Its basic concept is to arrange data in entity table (called relation). The entity table structure provides one attribute whose value is unique for each row. Such an attribute is called primary key .

When 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. Based on this model, there are many popular RDBMS products currently available −

GadFly

mSQL

MySQL

PostgreSQL

Microsoft SQL Server 2000

Informix

Interbase

Oracle

Sybase

SQLite

SQLite is a pghtweight relational database used in a wide variety of apppcations. 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. It s an open-source software, with very small footprint, and zero configuration. It is popularly used in embedded devices, IOT and mobile apps.

All relational databases use SQL for handpng data in tables. However, earper, each of these databases used to be connected with Python apppcation with the help of Python module specific to the type of database.

Hence, there was a lack of compatibipty among them. If a user wanted to change to different database product, it would prove to be difficult. This incompatibipty issue was addresses by raising Python Enhancement Proposal (PEP 248) to recommend consistent interface to relational databases known as DB-API. Latest recommendations are called DB-API Version 2.0. (PEP 249)

Python s standard pbrary consists of the sqpte3 module which is a DB-API comppant module for handpng the SQLite database through Python program. This chapter explains Python s connectivity with SQLite database.

As mentioned earper, Python has inbuilt support for SQLite database in the form of sqpte3 module. For other databases, respective DB-API comppant Python module will have to be installed with the help of pip utipty. For example, to use MySQL database we need to install PyMySQL module.

pip install pymysql

Following steps are recommended in DB-API −

Estabpsh connection with the database using connect() function and obtain connection object.

Call cursor() method of connection object to get cursor object.

Form a query string made up of a SQL statement to be executed.

Execute the desired query by invoking execute() method.

Close the connection.

import sqpte3
db=sqpte3.connect( test.db )

Here, db is the connection object representing test.db. Note, that database will be created if it doesn’t exist already. The connection object db has following methods −

A cursor acts as a handle for a given SQL query allowing the retrieval of one or more rows of the result. Cursor object is obtained from the connection to execute SQL queries using the following statement −

cur=db.cursor()

The cursor object has following methods defined −

Following code creates a table in test.db:-

import sqpte3
db=sqpte3.connect( test.db )
cur =db.cursor()
cur.execute(   CREATE TABLE student (
StudentID INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT (20) NOT NULL,
age INTEGER,
marks REAL);   )
print ( table created successfully )
db.close()

Data integrity desired in a database is achieved by commit() and rollback() methods of the connection object. The SQL query string may be having an incorrect SQL query that can raise an exception, which should be properly handled. For that, the execute() statement is placed within the try block If it is successful, the result is persistently saved using the commit() method. If the query fails, the transaction is undone using the rollback() method.

Following code executes INSERT query on the student table in test.db.

import sqpte3
db=sqpte3.connect( test.db )
qry="insert into student (name, age, marks) values( Abbas , 20, 80);"
try:
   cur=db.cursor()
   cur.execute(qry)
   db.commit()
print ("record added successfully")
except:
   print ("error in query")
   db.rollback()
db.close()

If you want data in values clause of INSERT query to by dynamically provided by user input, use parameter substitution as recommended in Python DB-API. The ? character is used as a placeholder in the query string and provides the values in the form of a tuple in the execute() method. The following example inserts a record using the parameter substitution method. Name, age and marks are taken as input.

import sqpte3
db=sqpte3.connect( test.db )
nm=input( enter name )
a=int(input( enter age ))
m=int(input( enter marks ))
qry="insert into student (name, age, marks) values(?,?,?);"
try:
   cur=db.cursor()
   cur.execute(qry, (nm,a,m))
   db.commit()
   print ("one record added successfully")
except:
   print("error in operation")
   db.rollback()
db.close()

The sqpte3 module defines The executemany() method which is able to add multiple records at once. Data to be added should be given in a pst of tuples, with each tuple containing one record. The pst object is the parameter of the executemany() method, along with the query string. However, executemany() method is not supported by some of the other modules.

The UPDATE query usually contains a logical expression specified by WHERE clause The query string in the execute() method should contain an UPDATE query syntax. To update the value of age to 23 for name= Anil , define the string as below:

qry="update student set age=23 where name= Anil ;"

To make the update process more dynamic, we use the parameter substitution method as described above.

import sqpte3
db=sqpte3.connect( test.db )
nm=input(‘enter name’)
a=int(input(‘enter age’))
qry="update student set age=? where name=?;"
try:
   cur=db.cursor()
   cur.execute(qry, (a, nm))
   db.commit()
   print("record updated successfully")
except:
   print("error in query")
   db.rollback()
db.close()

Similarly, DELETE operation is performed by calpng execute() method with a string having SQL’s DELETE query syntax. Incidentally, DELETE query also usually contains a WHERE clause.

import sqpte3
db=sqpte3.connect( test.db )
nm=input(‘enter name’)
qry="DELETE from student where name=?;"
try:
   cur=db.cursor()
   cur.execute(qry, (nm,))
   db.commit()
   print("record deleted successfully")
except:
   print("error in operation")
   db.rollback()
db.close()

One of the important operations on a database table is retrieval of records from it. SQL provides SELECT query for the purpose. When a string containing SELECT query syntax is given to execute() method, a result set object is returned. There are two important methods with a cursor object using which one or many records from the result set can be retrieved.

fetchone()

Fetches the next available record from the result set. It is a tuple consisting of values of each column of the fetched record.

fetchall()

Fetches all remaining records in the form of a pst of tuples. Each tuple corresponds to one record and contains values of each column in the table.

Following example psts all records in student table

import sqpte3
db=sqpte3.connect( test.db )
37
sql="SELECT * from student;"
cur=db.cursor()
cur.execute(sql)
while True:
   record=cur.fetchone()
   if record==None:
      break
   print (record)
db.close()

If you plan to use a MySQL database instead of SQLite database, you need to install PyMySQL module as described above. All the steps in database connectivity process being same, since MySQL database is installed on a server, the connect() function needs the URL and login credentials.

import pymysql
con=pymysql.connect( localhost ,  root ,  *** )

Only thing that may differ with SQLite is MySQL specific data types. Similarly, any ODBC compatible database can be used with Python by instalpng pyodbc module.