II B.

Tech I- Semester Python Programming (2022-23)

LECTURE NOTES

ON

PYTHON PROGRAMMING

UNIT V PART-I & II

(AI & ML)

II B. Tech I semester [2022-23]

R18 Regulation

Dr. N.Venkateswaran,
Associate Professor,

Department of Computer Science & Engineering

Prepared by Dr. N.Venkateswaran, Associate Professor, CSE Dept, JITS Page 1

II B.Tech I- Semester Python Programming (2022-23)

UNIT-V DATABASE PROGRAMMING

The Python programming language has powerful features for database programming. Python supports various
databases like MySQL, Oracle, Sybase, PostgreSQL, etc. Python also supports Data Definition Language
(DDL), Data Manipulation Language (DML) and Data Query Statements. For database programming, the
Python DB API is a widely used module that provides a database application programming interface.

Benefits of Python for database programming

There are many good reasons to use Python for programming database applications:

 Programming in Python is possibly more efficient and faster compared to other languages.
 Python is famous for its portability.
 It is platform independent.
 Python supports SQL cursors.
 In many programming languages, the application developer needs to take care of the open and closed
connections of the database, to avoid further exceptions and errors. In Python, these connections are
taken care of.
 Python supports relational database systems.
 Python database APIs are compatible with various databases, so it is very easy to migrate and port
database application interfaces.

DB-API (SQL-API) for Python

Python DB-API is independent of any database engine, which enables you to write Python scripts to access any
database engine. The Python DB API implementation for MySQL is MySQLdb. For PostgreSQL, it supports
psycopg, PyGresQL and pyPgSQL modules. DB-API implementations for Oracle are dc_oracle2 and cx_oracle.
Pydb2 is the DB-API implementation for DB2. Python’s DB-API consists of connection objects, cursor objects,
standard exceptions and some other module contents.

Environment Setup
To build the real world applications, connecting with the databases is the necessity for the programming
languages. However, python allows us to connect our application to the databases like MySQL, SQLite,
MongoDB, and many others.

Install mysql.connector

To connect the python application with the MySQL database, we must import the mysql.connector module in
the program.

The mysql.connector is not a built-in module that comes with the python installation. We need to install it to get
it working.

Prepared by Dr. N.Venkateswaran, Associate Professor, CSE Dept, JITS Page 2

II B.Tech I- Semester Python Programming (2022-23)

Database Connection
We will discuss the steps to connect the python application to the database.

There are the following steps to connect a python application to our database.

1. Import mysql.connector module

2. Create the connection object.
3. Create the cursor object
4. Execute the query

Creating the connection

To create a connection between the MySQL database and the python application, the connect() method of
mysql.connector module is used.

Pass the database details like HostName, username, and the database password in the method call. The method
returns the connection object.

The syntax to use the connect() is given below.

Connection-Object= mysql.connector.connect(host = <host-name> , user = <uname> , passwd = <password> )

Consider the following example.

Example
import mysql.connector

#Create the connection object

myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "")

#printing the connection object

print(myconn)

Output:
<mysql.connector.connection.MySQLConnection object at 0x7fb142edd780>

Here, we must notice that we can specify the database name in the connect() method if we want to connect to a
specific database.
Example
import mysql.connector

#Create the connection object

myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "", database = "jits")

#printing the connection object

print(myconn)

Output:
<mysql.connector.connection.MySQLConnection object at 0x7ff64aa3d7b8>

Prepared by Dr. N.Venkateswaran, Associate Professor, CSE Dept, JITS Page 3

II B.Tech I- Semester Python Programming (2022-23)

Creating a cursor object

The cursor object can be defined as an abstraction specified in the Python DB-API 2.0. It facilitates us to have
multiple separate working environments through the same connection to the database. We can create the cursor
object by calling the 'cursor' function of the connection object. The cursor object is an important aspect of
executing queries to the databases.

The syntax to create the cursor object is given below.

<my_cur> = conn.cursor()
Example
import mysql.connector
#Create the connection object
myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "", database = "jits")

#printing the connection object

print(myconn)

#creating the cursor object

cur = myconn.cursor()

print(cur)
Output:
<mysql.connector.connection.MySQLConnection object at 0x7faa17a15748>
MySQLCursor: (Nothing executed yet)

Creating new databases

We will create the new database JITSCSE.

Getting the list of existing databases

We can get the list of all the databases by using the following MySQL query.
show databases;

Example
import mysql.connector

#Create the connection object

myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "")

#creating the cursor object

cur = myconn.cursor()

try:
dbs = cur.execute("show databases")
except:
myconn.rollback()
for x in cur:
print(x)

Prepared by Dr. N.Venkateswaran, Associate Professor, CSE Dept, JITS Page 4

II B.Tech I- Semester Python Programming (2022-23)

myconn.close()

Output:
('information_schema',)
('cseaadb',)
('cseadb',)
('jits',)
('jitscloud',)
('joeldb',)
('mysql',)
('payload',)
('wt',)

Creating the new database

The new database can be created by using the following SQL query.

> create database <database-name>

Example
import mysql.connector

#Create the connection object

myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "")

#creating the cursor object

cur = myconn.cursor()

try:
#creating a new database
cur.execute("create database JITSCSE")

#getting the list of all the databases which will now include the new database JITSCSE
dbs = cur.execute("show databases")

except:
myconn.rollback()

for x in cur:
print(x)

myconn.close()

Output:
('information_schema',)
('cseaadb',)
('cseadb',)
('jits',)
(‘JITSCSE’)
('jitscloud',)
('joeldb',)
('mysql',)
('payload',)
('wt',)

Prepared by Dr. N.Venkateswaran, Associate Professor, CSE Dept, JITS Page 5

II B.Tech I- Semester Python Programming (2022-23)

Creating the table

We will create the new table Employee. We have to mention the database name while establishing the
connection object.

We can create the new table by using the CREATE TABLE statement of SQL. In our database JITSCSE, the
table Employee will have the four columns, i.e., name, id, salary, and department_id initially.

The following query is used to create the new table Employee.

create table Employee (name varchar(20) not null, id int primary key, salary float not null, Dept_Id int not null)
Example
import mysql.connector

#Create the connection object

myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "",database = "JITSCSE")

#creating the cursor object

cur = myconn.cursor()

try:
#Creating a table with name Employee having four columns i.e., name, id, salary, and department id
dbs = cur.execute("create table Employee(name varchar(20) not null, id int(20) not null primary key, salary
float not null, Dept_id int not null)")
except:
myconn.rollback()

myconn.close()

Now, we may check that the table Employee is present in the database.

Alter Table
Sometimes, we may forget to create some columns, or we may need to update the table schema. The alter
statement used to alter the table schema if required. Here, we will add the column branch_name to the table
Employee. The following SQL query is used for this purpose.

alter table Employee add branch_name varchar(20) not null

Consider the following example.

Example
import mysql.connector

#Create the connection object

myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "",database = "JITSCSE")

#creating the cursor object

cur = myconn.cursor()

try:
#adding a column branch name to the table Employee

Prepared by Dr. N.Venkateswaran, Associate Professor, CSE Dept, JITS Page 6

II B.Tech I- Semester Python Programming (2022-23)

cur.execute("alter table Employee add branch_name varchar(20) not null")

except:
myconn.rollback()

myconn.close()

Insert Operation
Adding a record to the table
The INSERT INTO statement is used to add a record to the table. In python, we can mention the format
specifier (%s) in place of values.

We provide the actual values in the form of tuple in the execute() method of the cursor.
Consider the following example.

Example
import mysql.connector

#Create the connection object

myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "",database = "JITSCSE")

#creating the cursor object

cur = myconn.cursor()
sql = "insert into Employee(name, id, salary, dept_id, branch_name) values (%s, %s, %s, %s, %s)"

#The row values are provided in the form of tuple

val = ("Joel", 171, 25000.00, 501, "Karimnagar")

try:
#inserting the values into the table
cur.execute(sql,val)

#commit the transaction

myconn.commit()

except:
myconn.rollback()

print(cur.rowcount,"record inserted!")
myconn.close()
Output:

1 record inserted!

Insert multiple rows

We can also insert multiple rows at once using the python script. The multiple rows are mentioned as the list of
various tuples.

Each element of the list is treated as one particular row, whereas each element of the tuple is treated as one
particular column value (attribute).

Prepared by Dr. N.Venkateswaran, Associate Professor, CSE Dept, JITS Page 7

II B.Tech I- Semester Python Programming (2022-23)

Consider the following example.

Example
import mysql.connector

#Create the connection object

myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "",database = "JITSCSE")

#creating the cursor object

cur = myconn.cursor()
sql = "insert into Employee(name, id, salary, dept_id, branch_name) values (%s, %s, %s, %s, %s)"
val = [("snigdha", 777, 45000.00, 577, "India"),("Joel",303,35000.00,202,"UK"),("Royal",404,90000.00,201,"US
A")]

try:
#inserting the values into the table
cur.executemany(sql,val)

#commit the transaction

myconn.commit()
print(cur.rowcount,"records inserted!")

except:
myconn.rollback()

myconn.close()

Output:
3 records inserted!

Row ID
In SQL, a particular row is represented by an insertion id which is known as row id. We can get the last inserted
row id by using the attribute lastrowid of the cursor object.

Consider the following example.

Example
import mysql.connector

#Create the connection object

myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "",database = "JITSCSE")
#creating the cursor object
cur = myconn.cursor()

sql = "insert into Employee(name, id, salary, dept_id, branch_name) values (%s, %s, %s, %s, %s)"

val = ("Shailaja",505,78000,602,"Delhi")

try:
#inserting the values into the table
cur.execute(sql,val)

#commit the transaction

myconn.commit()
Prepared by Dr. N.Venkateswaran, Associate Professor, CSE Dept, JITS Page 8
II B.Tech I- Semester Python Programming (2022-23)

#getting rowid
print(cur.rowcount,"record inserted! id:",cur.lastrowid)

except:
myconn.rollback()

myconn.close()

Output:
1 record inserted! Id: 0

Read Operation
The SELECT statement is used to read the values from the databases. We can restrict the output of a select
query by using various clause in SQL like where, limit, etc.
Python provides the fetchall() method returns the data stored inside the table in the form of rows. We
can iterate the result to get the individual rows.

In this section of the tutorial, we will extract the data from the database by using the python script. We will also
format the output to print it on the console.

Example
import mysql.connector

#Create the connection object

myconn = mysql.connector.connect(host = "localhost", user = "root", passwd = "", database =
"JITSCSE")

#creating the cursor object

cur = myconn.cursor()

try:
#Reading the Employee data
cur.execute("select * from Employee")

#fetching the rows from the cursor object

result = cur.fetchall()
#printing the result

for x in result:
print(x);
except:
myconn.rollback()

myconn.close()

Output:

(‘snigdha’, 777, 45000.00, 577, "India")

("Joel", 303, 35000.00, 202,"UK")
("Royal", 404, 90000.00, 201,"USA")
Prepared by Dr. N.Venkateswaran, Associate Professor, CSE Dept, JITS Page 9
II B.Tech I- Semester Python Programming (2022-23)

("Shailaja", 505, 78000, 602,"Delhi")

Reading specific columns

We can read the specific columns by mentioning their names instead of using star (*). In the following example,
we will read the name, id, and salary from the Employee table and print it on the console.

Example
import mysql.connector

#Create the connection object

myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "",database = "JITSCSE"
)

#creating the cursor object

cur = myconn.cursor()
try:
#Reading the Employee data
cur.execute("select name, id, salary from Employee")

#fetching the rows from the cursor object

result = cur.fetchall()
#printing the result
for x in result:
print(x);
except:
myconn.rollback()
myconn.close()

Output:
(‘snigdha’, 777, 45000.00)
("Joel", 303, 35000.00)
("Royal", 404, 90000.00)
("Shailaja", 505, 78000)

The fetchone() method

The fetchone() method is used to fetch only one row from the table. The fetchone() method returns the next row
of the result-set.

Consider the following example.

Example
import mysql.connector

#Create the connection object

myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "",database = "JITSCSE"
)

#creating the cursor object

cur = myconn.cursor()

Prepared by Dr. N.Venkateswaran, Associate Professor, CSE Dept, JITS Page 10

II B.Tech I- Semester Python Programming (2022-23)

try:
#Reading the Employee data
cur.execute("select name, id, salary from Employee")

#fetching the first row from the cursor object

result = cur.fetchone()

#printing the result

print(result)

except:
myconn.rollback()

myconn.close()

Output:
(‘snigdha’, 777, 45000.00)

Formatting the result

We can format the result by iterating over the result produced by the fetchall() or fetchone() method of cursor
object since the result exists as the tuple object which is not readable.

Consider the following example.

Example
import mysql.connector

#Create the connection object

myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "",database = JITSCSE")

#creating the cursor object

cur = myconn.cursor()

try:

#Reading the Employee data

cur.execute("select name, id, salary from Employee")

#fetching the rows from the cursor object

result = cur.fetchall()

print("Name id Salary");
for row in result:
print("%s %d %d"%(row[0],row[1],row[2]))
except:
myconn.rollback()

myconn.close()

Prepared by Dr. N.Venkateswaran, Associate Professor, CSE Dept, JITS Page 11

II B.Tech I- Semester Python Programming (2022-23)

Output:
Name id Salary
snigdha 777 45000.00
Joel 303 35000.00
Royal 404 90000.00
Shailaja 505 78000

Update Operation
The UPDATE-SET statement is used to update any column inside the table. The following SQL query is used
to update a column.
 update Employee set name = 'snigdhaAngel’ where id = 777

Consider the following example.

Example
import mysql.connector

#Create the connection object

myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "",database = "JITSCSE"
)

#creating the cursor object

cur = myconn.cursor()

try:
#updating the name of the employee whose id is 777
cur.execute("update Employee set name = 'snigdhaAngel' where id = 777")
myconn.commit()
except:

myconn.rollback()

myconn.close()

Delete Operation
The DELETE FROM statement is used to delete a specific record from the table. Here, we must impose a
condition using WHERE clause otherwise all the records from the table will be removed.

The following SQL query is used to delete the employee detail whose id is 404 from the table.
 delete from Employee where id = 404

Consider the following example.

Example
import mysql.connector

#Create the connection object

myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "",database = "JITSCSE"

Prepared by Dr. N.Venkateswaran, Associate Professor, CSE Dept, JITS Page 12

II B.Tech I- Semester Python Programming (2022-23)

#creating the cursor object

cur = myconn.cursor()

try:
#Deleting the employee details whose id is 404
cur.execute("delete from Employee where id = 404")
myconn.commit()
except:

myconn.rollback()

myconn.close()

Performing Transactions
Transactions are a mechanism that ensures data consistency. Transactions have the following four properties

 Atomicity − Either a transaction completes or nothing happens at all.

 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 again similar example −
# Prepare SQL query to DELETE required records

sql = "DELETE FROM EMPLOYEE WHERE AGE > '%d'" % (20)

try:
# Execute the SQL command
cursor.execute(sql)

# Commit your changes in the database

db.commit()

except:
# Rollback in case there is any error
db.rollback()

COMMIT Operation

Commit is the operation, which gives a green signal to database to finalize the changes, and after this operation,
no change can be reverted back.

Prepared by Dr. N.Venkateswaran, Associate Professor, CSE Dept, JITS Page 13

II B.Tech I- Semester Python Programming (2022-23)

Here is a simple example to call 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 rollback() method.

Here is a simple example to call rollback() method.

db.rollback()

Disconnecting Database
To disconnect Database connection, use close() method.
db.close()

If the connection to a database is closed by the user with the close() method, any outstanding transactions are
rolled back by the DB. However, instead of depending on any of DB lower level implementation details, your
application would be better off calling commit or rollback explicitly.

Handling Errors
There are many sources of errors. A few examples are a syntax error in an executed SQL statement, a
connection failure, or calling the fetch method for an already canceled or finished statement handle.

The DB API defines a number of errors that must exist in each database module. The following table lists these
exceptions.
Sr.No. Exception & Description
1 Warning :Used for non-fatal issues. Must subclass StandardError.
2 Error : Base class for errors. Must subclass StandardError.
InterfaceError
3
Used for errors in the database module, not the database itself. Must subclass Error.
4 DatabaseError :Used for errors in the database. Must subclass Error.
5 DataError :Subclass of DatabaseError that refers to errors in the data.
OperationalError
6 Subclass of DatabaseError that refers to errors such as the loss of a connection to the database.
These errors are generally outside of the control of the Python scripter.
IntegrityError :Subclass of DatabaseError for situations that would damage the relational integrity,
7
such as uniqueness constraints or foreign keys.
InternalError :Subclass of DatabaseError that refers to errors internal to the database module, such
8
as a cursor no longer being active.
ProgrammingError
9 Subclass of DatabaseError that refers to errors such as a bad table name and other things that can
safely be blamed on you.
NotSupportedError
10
Subclass of DatabaseError that refers to trying to call unsupported functionality.
Object-Relational Mappers (ORMs)
Prepared by Dr. N.Venkateswaran, Associate Professor, CSE Dept, JITS Page 14
II B.Tech I- Semester Python Programming (2022-23)

An object-relational mapper (ORM) is a code library that automates the transfer of data stored in relational
databases tables into objects that are more commonly used in application code.

Object Relational Mapping is a system of mapping objects to a database. ORM is a layer of abstraction that
some developers appreciate because they can work with databases in a language (Python in this case) besides
SQL.

I think of the mapping like this…

 Python Class == SQL Table

 Instance of the Class == Row in the Table

Database tables are magically converted to Python classes with columns and features as attributes and methods
responsible for database operations. Setting up your application to an ORM is somewhat similar to that of a
standard database adapter.

Some other Python ORMs include PyDO/PyDO2, PDO, Dejavu, PDO, Durus, QLime, and ForgetSQL. Larger
Web-based systems can also have their own ORM component, i.e., WebWare MiddleKit and Django's Database
API.

Why are ORMs useful?

ORMs provide a high-level abstraction upon a relational database that allows a developer to write Python code
instead of SQL to create, read, update and delete data and schemas in their database. Developers can use the
programming language they are comfortable with to work with a database instead of writing SQL statements or
stored procedures.

For example, without an ORM a developer would write the following SQL statement to retrieve every row in
the USERS table where the zip_code column is 94107:

SELECT * FROM USERS WHERE zip_code=94107;

The equivalent Django ORM query would instead look like the following Python code:

# obtain everyone in the 94107 zip code and assign to users variable
users = Users.objects.filter(zip_code=94107)

Prepared by Dr. N.Venkateswaran, Associate Professor, CSE Dept, JITS Page 15

II B.Tech I- Semester Python Programming (2022-23)

The ability to write Python code instead of SQL can speed up web application development, especially at the
beginning of a project. The potential development speed boost comes from not having to switch from Python
code into writing declarative paradigm SQL statements. While some software developers may not mind
switching back and forth between languages, it's typically easier to knock out a prototype or start a web
application using a single programming language.

ORMs also make it theoretically possible to switch an application between various relational databases. For
example, a developer could use SQLite for local development and MySQL in production. A production
application could be switched from MySQL to PostgreSQL with minimal code modifications.
In practice however, it's best to use the same database for local development as is used in production. Otherwise
unexpected errors could hit in production that were not seen in a local development environment. Also, it's rare
that a project would switch from one database in production to another one unless there was a pressing reason.

Do I have to use an ORM for my web application?

Python ORM libraries are not required for accessing relational databases. In fact, the low-level access is
typically provided by another library called a database connector, such as psycopg (for PostgreSQL) or
MySQL-python (for MySQL). Take a look at the table below which shows how ORMs can work with different
web frameworks and connectors and relational databases.

The above table shows for example that SQLAlchemy can work with varying web frameworks and database
connectors. Developers can also use ORMs without a web framework, such as when creating a data analysis
tool or a batch script without a user interface.

PyMySQL is a MySQL database connector for Python. It’s what SQL Alchemy uses as a driver to connect
with a database. There are other database APIs that connect to different databases, such as psycopg2 for
postgresql. This layer is known as a DBAPI in SQL Alchemy.

SQL Alchemy is a complete toolkit for working with databases in Python. It can connect with many different
databases (MySQL, postgres, Mongo, etc) and allows developers to work with databases in a Pythonic way.

Prepared by Dr. N.Venkateswaran, Associate Professor, CSE Dept, JITS Page 16

II B.Tech I- Semester Python Programming (2022-23)

Declare Mapping
First of all, create_engine() function is called to set up an engine object which is subsequently used to perform
SQL operations. The function has two arguments, one is the name of database and other is an echo parameter
when set to True will generate the activity log. If it doesn’t exist, the database will be created. In the following
example, a SQLite database is created.
from sqlalchemy import create_engine
engine = create_engine('sqlite:///sales.db', echo = True)

The Engine establishes a real DBAPI connection to the database when a method like Engine.execute() or
Engine.connect() is called. It is then used to emit the SQLORM which does not use the Engine directly; instead,
it is used behind the scenes by the ORM.

In case of ORM, the configurational process starts by describing the database tables and then by defining
classes which will be mapped to those tables. In SQLAlchemy, these two tasks are performed together. This is
done by using Declarative system; the classes created include directives to describe the actual database table
they are mapped to.

A base class stores a catlog of classes and mapped tables in the Declarative system. This is called as the
declarative base class. There will be usually just one instance of this base in a commonly imported module. The
declarative_base() function is used to create base class. This function is defined in sqlalchemy.ext.declarative
module.

from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

Once base classis declared, any number of mapped classes can be defined in terms of it. Following code defines
a Customer’s class. It contains the table to be mapped to, and names and datatypes of columns in it.

class Customers(Base):
__tablename__ = 'customers'

id = Column(Integer, primary_key = True)

name = Column(String)
address = Column(String)
email = Column(String)

A class in Declarative must have a __tablename__ attribute, and at least one Column which is part of a
primary key. Declarative replaces all the Column objects with special Python accessors known as descriptors.
This process is known as instrumentation which provides the means to refer to the table in a SQL context and
enables persisting and loading the values of columns from the database.

This mapped class like a normal Python class has attributes and methods as per the requirement.

The information about class in Declarative system, is called as table metadata. SQLAlchemy uses Table object
to represent this information for a specific table created by Declarative. The Table object is created according to
the specifications, and is associated with the class by constructing a Mapper object. This mapper object is not
directly used but is used internally as interface between mapped class and table.

Each Table object is a member of larger collection known as MetaData and this object is available using the
.metadata attribute of declarative base class. The MetaData.create_all() method is, passing in our Engine as a
source of database connectivity. For all tables that haven’t been created yet, it issues CREATE TABLE

Prepared by Dr. N.Venkateswaran, Associate Professor, CSE Dept, JITS Page 17

II B.Tech I- Semester Python Programming (2022-23)

statements to the database.

Base.metadata.create_all(engine)

The complete script to create a database and a table, and to map Python class is given below −

from sqlalchemy import Column, Integer, String

from sqlalchemy import create_engine
engine = create_engine('sqlite:///sales.db', echo = True)
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()

class Customers(Base):
__tablename__ = 'customers'
id = Column(Integer, primary_key=True)

name = Column(String)
address = Column(String)
email = Column(String)
Base.metadata.create_all(engine)

When executed, Python console will echo following SQL expression being executed −

CREATE TABLE customers (

id INTEGER NOT NULL,
name VARCHAR,
address VARCHAR,
email VARCHAR,
PRIMARY KEY (id)
)

If we open the Sales.db using SQLiteStudio graphic tool, it shows customers table inside it with above
mentioned structure.

Prepared by Dr. N.Venkateswaran, Associate Professor, CSE Dept, JITS Page 18

II B.Tech I- Semester Python Programming (2022-23)

Related Modules
The table lists most of the common databases out there along with working Python modules and packages that
serve as adapters to those database systems. Note that not all adapters are DB-API compliant.

Table Database-Related Modules and Websites

Databases

Gadfly http://gadfly.sf.net
MySQL http://mysql.com or http://mysql.org
MySQLdb a.k.a. MySQL- python http://sf.net/projects/mysql-python
PostgreSQL http://postgresql.org
psycopg http://initd.org/projects/psycopg1
psycopg2 http://initd.org/software/initd/psycopg/
PyPgSQL http://pypgsql.sf.net
PyGreSQL http://pygresql.org
PoPy Deprecated; merged into PyGreSQL project
SQLite http://sqlite.org
pysqlite http://initd.org/projects/pysqlite

APSW http://rogerbinns.com/apsw.html
MaxDB (SAP) http://mysql.com/products/maxdb
sdb http://dev.mysql.com/downloads/maxdb/7.6.00.html#Python
sapdb http://sapdb.org/sapdbPython.html
Firebird (InterBase) http://firebird.sf.net
KInterbasDB http://kinterbasdb.sf.net
SQL Server http://microsoft.com/sql
pymssql http://pymssql.sf.net (requires FreeTDS [http://freetds.org])

adodbapi http://adodbapi.sf.net
Sybase http://sybase.com
sybase http://object-craft.com.au/projects/sybase
Oracle http://oracle.com
cx_Oracle http://starship.python.net/crew/atuining/cx_Oracle
DCOracle2 http://zope.org/Members/matt/dco2 (older, for Oracle8 only)
Ingres http://ingres.com
Ingres DBI http://ingres.com/products/ Prod_Download_Python_DBI.html
ingmod http://www.informatik.uni-rostock.de/~hme/software/

ORMs

SQLObject http://sqlobject.org
SQLAlchemy http://sqlalchemy.org
PyDO/PyDO2 http://skunkweb.sf.net/pydo.html
[

Prepared by Dr. N.Venkateswaran, Associate Professor, CSE Dept, JITS Page 19