CS – 33 : Programming in Advance Python – 2

B.C.A. Semester – 6th

Unit : 4
Interaction with Database
 Configuring database
 Defining model
 Basic data access
 Inserting and Updating data
 Selecting objects
 Deleting objects

Dr. Dhaval Kher Head of Department [BCA | M.Sc(IT&CA) ]

Dr. Virambhai R. Godhaniya I. T. College
B/h. Amar Polyfills, Rajkot- National Highway, Opp. New Airport, Porbandar - 360 578. (Gujarat).
Mobile : 9879870002| E-Mail : drvrgit@yahoo.in | www.drvrgit.com
 Unit-4 Interaction with Database
INDEX

1 MARKS QUESTIONS: ................................................................................................................................................. 2

3 MARKS QUESTIONS: ................................................................................................................................................. 2
5 MARKS QUESTIONS: ................................................................................................................................................. 2
PYTHON - DATABASE ACCESS ............................................................................................................................................. 3
THE SQLITE3 MODULE ................................................................................................................................................................. 4
THE CONNECTION OBJECT ............................................................................................................................................................ 4
THE CURSOR OBJECT.............................................................................................................................................................. 4
CREATING A DATABASE TABLE .............................................................................................................................................. 4
INSERT OPERATION .............................................................................................................................................................. 5
READ OPERATION ................................................................................................................................................................. 6
UPDATE OPERATION ............................................................................................................................................................... 6
DELETE OPERATION ............................................................................................................................................................. 7
GET() & FILTER() ................................................................................................................................................................... 7
PERFORMING TRANSACTIONS ................................................................................................................................................. 7
COMMIT OPERATION ............................................................................................................................................................ 8
ROLLBACK OPERATION....................................................................................................................................................... 8

By : Dr. Dhaval R. Kher Head of Department [BCA | M.Sc(IT&CA) ] | Dr. Virambhai R. Godhaniya I.T. College, Porbandar. Page | 1
 Unit-4 Interaction with Database

1 Marks Questions:
1. Which query is use to access data from the table?
2. Which query is use to delete the data from the table?
3. What is model?
4. Which query is use to display data from table?
5. _____ method is use to connect python application with MySQL.
6. _____ function is used to create database connection in mysql with .python.
7. _____ method id used to fetch single record from database table.
8. _____ method is used to close current cursor object.

3 Marks Questions:
1. Write down code to create data base.
2. Write down the code to create table in database.
3. Write down the code to fetch all data from table.
4. Write down the code to insert data in table.
5. Explain get( ) and filter( ) method for update operations.
6. Write down the code to insert 5 records in table. [table : Student]
7. Write steps to python program that interact with a MySQL based database.
8. How to create connection with database in python?
9. How to create Database from python?
10. How can we delete object from database in Python?

5 Marks Questions:
1. Write down the code to access data from emp table those who earn salary more than
average salary.
Table Name emp Columns empno, ename, deptno, salary
2. Explain database defining Model in Python.
3. Write down code to display records from the student table on the base of name field.
4. Write down code to update three records on the base of name of student table and
display all records.
5. Explain basic data access in detail.
6. Explain database with Tkinter in detail.
7. Write a code to create Database login page in Python using Tkinter.
8. Write a code to insert and update data.

By : Dr. Dhaval R. Kher Head of Department [BCA | M.Sc(IT&CA) ] | Dr. Virambhai R. Godhaniya I.T. College, Porbandar. Page | 2
 Unit-4 Interaction with Database

Python - Database Access

 Data input and generated during execution of a program is stored in RAM. If it is to be
stored persistently, it needs to be stored in database tables. There are various relational
database management systems (RDBMS) available.
o GadFly
o MySQL
o PostgreSQL
o Microsoft SQL Server
o Informix
o Oracle
o Sybase
o SQLite
 Relational databases use SQL (Structured Query Language) for performing
INSERT/DELETE/UPDATE operations on the database tables. However, implementation
of SQL varies from one type of database to other. This raises incompatibility issues. SQL
instructions for one database do not match with other.
 To overcome this incompatibility, a common interface was proposed in PEP (Python
Enhancement Proposal) 249. This proposal is called DB-API and requires that a database
driver program used to interact with Python should be DB-API compliant.

 Python's standard library includes sqlite3 module which is a DB_API compatible driver for
SQLite3 database, it is also a reference implementation of DB-API.
 Since the required DB-API interface is built-in, we can easily use SQLite database with a
Python application. For other types of databases, you will have to install the relevant Python
package.

Database Python Package

Oracle cx_oracle, pyodbc
SQL Server pymssql, pyodbc
PostgreSQL psycopg2
MySQL MySQL Connector/Python, pymysql

 A DB-API module such as sqlite3 contains connection and cursor classes. The connection
object is obtained with connect() method by providing required connection credentials such
as name of server and port number, and username and password if applicable. The
connection object handles opening and closing the database, and transaction control
mechanism of committing or rolling back a transaction.
By : Dr. Dhaval R. Kher Head of Department [BCA | M.Sc(IT&CA) ] | Dr. Virambhai R. Godhaniya I.T. College, Porbandar. Page | 3
 Unit-4 Interaction with Database

 The cursor object, obtained from the connection object, acts as the handle of the database
when performing all the CRUD operations.

The sqlite3 Module

 SQLite is a server-less, file-based lightweight transactional relational database. It doesn't
require any installation and no credentials such as username and password are needed to
access the database.
 Python's sqlite3 module contains DB-API implementation for SQLite database. It is written
by Gerhard Häring. Let us learn how to use sqlite3 module for database access with Python.
 Let us start by importing sqlite3 and check its version.
>>> import sqlite3
>>> sqlite3.sqlite_version
'3.39.4'

The Connection Object

 A connection object is set up by connect() function in sqlite3 module. First positional
argument to this function is a string representing path (relative or absolute) to a SQLite
database file. The function returns a connection object referring to the database.
>>> conn=sqlite3.connect('testdb.sqlite3')
>>> type(conn)
<class 'sqlite3.Connection'>
 Various methods are defined in connection class. One of them is cursor() method that returns
a cursor object, about which we shall know in next section. Transaction control is achieved
by commit() and rollback() methods of connection object. Connection class has important
methods to define custom functions and aggregates to be used in SQL queries.

The Cursor Object

 Next, we need to get the cursor object from the connection object. It is your handle to the
database when performing any CRUD operation on the database. The cursor() method on
connection object returns the cursor object.
>>> cur=conn.cursor()
>>> type(cur)
<class 'sqlite3.Cursor'>
 We can now perform all SQL query operations, with the help of its execute() method
available to cursor object. This method needs a string argument which must be a valid SQL
statement.

Creating a Database Table

 We shall now add Employee table in our newly created 'testdb.sqlite3' database. In following
script, we call execute() method of cursor object, giving it a string with CREATE TABLE
statement inside.

import sqlite3
conn=sqlite3.connect('testdb.sqlite3')
cur=conn.cursor()
qry='''
CREATE TABLE Employee (
EmpID INTEGER PRIMARY KEY AUTOINCREMENT,
FIRST_NAME TEXT (20),
LAST_NAME TEXT(20),
AGE INTEGER,

By : Dr. Dhaval R. Kher Head of Department [BCA | M.Sc(IT&CA) ] | Dr. Virambhai R. Godhaniya I.T. College, Porbandar. Page | 4
 Unit-4 Interaction with Database
SEX TEXT(1),
INCOME FLOAT
); '''

try:
cur.execute(qry)
print ('Table created successfully')
except:
print ('error in creating table')
conn.close()

 When the above program is run, the database with Employee table is created in the current
working directory.
 We can verify by listing out tables in this database in SQLite console.
sqlite> .open mydb.sqlite
sqlite> .tables
Employee

INSERT Operation
 The INSERT Operation is required when you want to create your records into a database
table.
Example
The following example, executes SQL INSERT statement to create a record in the EMPLOYEE table

import sqlite3
conn=sqlite3.connect('testdb.sqlite3')
cur=conn.cursor()
qry="""INSERT INTO EMPLOYEE(FIRST_NAME,
LAST_NAME, AGE, SEX, INCOME)
VALUES ('Mac', 'Mohan', 20, 'M', 2000)"""
try:
cur.execute(qry)
conn.commit()
print ('Record inserted successfully')
except:
conn.rollback()
print ('error in INSERT operation')
conn.close()

 You can also use the parameter substitution technique to execute the INSERT query as
follows −
import sqlite3
conn=sqlite3.connect('testdb.sqlite3')
cur=conn.cursor()
qry="""INSERT INTO EMPLOYEE(FIRST_NAME,
LAST_NAME, AGE, SEX, INCOME)
VALUES (?, ?, ?, ?, ?)"""
try:
cur.execute(qry, ('Makrand', 'Mohan', 21, 'M', 5000))
conn.commit()
print ('Record inserted successfully')
except Exception as e:
conn.rollback()
print ('error in INSERT operation')
conn.close()

By : Dr. Dhaval R. Kher Head of Department [BCA | M.Sc(IT&CA) ] | Dr. Virambhai R. Godhaniya I.T. College, Porbandar. Page | 5
 Unit-4 Interaction with Database

READ Operation
 READ Operation on any database means to fetch some useful information from the
database.
 Once the database connection is established, you are ready to make a query into this
database. You can use either fetchone() method to fetch a single record or fetchall() method
to fetch multiple values from a database table.
 fetchone() − It fetches the next row of a query result set. A result set is an object that is
returned when a cursor object is used to query a table.
 fetchall() − It fetches all the rows in a result set. If some rows have already been extracted
from the result set, then it retrieves the remaining rows from the result set.
 rowcount − This is a read-only attribute and returns the number of rows that were affected by
an execute() method.
Example
In the following code, the cursor object executes SELECT * FROM EMPLOYEE query. The
resultset is obtained with fetchall() method. We print all the records in the resultset with a for loop.
import sqlite3
conn=sqlite3.connect('testdb.sqlite3')
cur=conn.cursor()
qry="SELECT * FROM EMPLOYEE"
try:
# Execute the SQL command
cur.execute(qry)
# Fetch all the rows in a list of lists.
results = cur.fetchall()
for row in results:
fname = row[1]
lname = row[2]
age = row[3]
sex = row[4]
income = row[5]
# Now print fetched result
print ("fname={},lname={},age={},sex={},income={}".format(fname, lname, age, sex,
income ))
except Exception as e:
print (e)
print ("Error: unable to fecth data")
conn.close()

Update Operation
 UPDATE Operation on any database means to update one or more records, which are
already available in the database.
 The following procedure updates all the records having income=2000. Here, we increase
the income by 1000.

import sqlite3
conn=sqlite3.connect('testdb.sqlite3')
cur=conn.cursor()
qry="UPDATE EMPLOYEE SET INCOME = INCOME+1000 WHERE INCOME=?"

try:
# Execute the SQL command
cur.execute(qry, (1000,))
# Fetch all the rows in a list of lists.

By : Dr. Dhaval R. Kher Head of Department [BCA | M.Sc(IT&CA) ] | Dr. Virambhai R. Godhaniya I.T. College, Porbandar. Page | 6
 Unit-4 Interaction with Database
conn.commit()
print ("Records updated")
except Exception as e:
print ("Error: unable to update data")
conn.close()

DELETE Operation
 DELETE operation is required when you want to delete some records from your database.
Following is the procedure to delete all the records from EMPLOYEE where INCOME is
less than 2000.

import sqlite3
conn=sqlite3.connect('testdb.sqlite3')
cur=conn.cursor()
qry="DELETE FROM EMPLOYEE WHERE INCOME<?"
try:
# Execute the SQL command
cur.execute(qry, (2000,))
# Fetch all the rows in a list of lists.
conn.commit()
print ("Records deleted")
except Exception as e:
print ("Error: unable to delete data")
conn.close()

Get() & filter()

 Get() Method: The get method is used to retrieve a single object that matches the given
lookup parameters. If no object is found, it raises a DoesNotExist exception, and if multiple
objects are found, it raises a MultipleObjectsReturned exception. This method is ideal when
you are sure that only one object should match the query.
 Filter() Method:The filter method is used to retrieve a QuerySet of objects that match the
given lookup parameters. It does not raise exceptions if no objects are found; instead, it
returns an empty QuerySet. This method is useful when you need to update multiple objects
at once.

Key Differences:

 get: Retrieves a single object, raises exceptions if no or multiple objects are found.
 filter: Retrieves a QuerySet of objects, does not raise exceptions, and can be used for bulk
updates.

Summary:

 Use get when you need to update a single, specific object and are confident that only one
object matches the criteria.
 Use filter when you need to update multiple objects or when you are not sure how many
objects match the criteria.

Performing Transactions
 Transactions are a mechanism that ensure data consistency. Transactions have the following
four properties −
 Atomicity − Either a transaction completes or nothing happens at all.

By : Dr. Dhaval R. Kher Head of Department [BCA | M.Sc(IT&CA) ] | Dr. Virambhai R. Godhaniya I.T. College, Porbandar. Page | 7
 Unit-4 Interaction with Database
 Consistency − A transaction must start in a consistent state and leave the system in a
consistent state.
 Isolation − Intermediate results of a transaction are not visible outside the current
transaction.
 Durability − Once a transaction was committed, the effects are persistent, even after a
system failure.

The Python DB API 2.0 provides two methods to either commit or rollback a transaction.
Example
You already know how to implement transactions. Here is a similar example −
# Prepare SQL query to DELETE required records
sql = "DELETE FROM EMPLOYEE WHERE AGE > ?"
try:
# Execute the SQL command
cursor.execute(sql, (20,))
# Commit your changes in the database
db.commit()
except:
# Rollback in case there is any error
db.rollback()

COMMIT Operation
Commit is an operation, which gives a green signal to the database to finalize the changes, and after this
operation, no change can be reverted back.
Here is a simple example to call the commit method.
db.commit()

ROLLBACK Operation
If you are not satisfied with one or more of the changes and you want to revert back those changes
completely, then use the rollback() method.
Here is a simple example to call the rollback() method.
db.rollback()

By : Dr. Dhaval R. Kher Head of Department [BCA | M.Sc(IT&CA) ] | Dr. Virambhai R. Godhaniya I.T. College, Porbandar. Page | 8