4CP50: Python Programming
Chapter-9 MySQL Database
Access
Prepared By:
Dr. U. K. Jaliya,
Computer Engineering Department,
BVM.
9/30/2022 Python Programming 1
Introduction
The Python standard for database interfaces is the Python DB-
API. Most Python database interfaces adhere to this standard.
You can choose the right database for your application. Python
Database API supports a wide range of database servers such as
GadFly
mSQL
MySQL
PostgreSQL
Microsoft SQL Server 2000
Informix
Interbase
Oracle , Sybase , SQLite
9/30/2022 Python Programming 2
Cont…
You must download a separate DB API module for each
database you need to access.
For example, if you need to access an Oracle database as well
as a MySQL database, you must download both the Oracle and
the MySQL database modules.
The DB API provides a minimal standard for working with
databases using Python structures and syntax wherever possible.
This API includes the following:
Importing the API module.
Acquiring a connection with the database.
Issuing SQL statements and stored procedures.
Closing the connection
9/30/2022 Python Programming 3
What is PyMySQL ?
PyMySQL is an interface for connecting to a MySQL
database server from Python.
It implements the Python Database API v2.0 and contains a
pure-Python MySQL client library.
You have to install PyMySQL module on your machine.
import PyMySQL
If this statement generate the error means you have not
installed module.
Install it using following command:
pip install PyMySQL
9/30/2022 Python Programming 4
Database Connection
Before connecting to a MySQL database, make sure of the
following points:
You have created a database TESTDB.
You have created a table EMPLOYEE in TESTDB.
This table has fields FIRST_NAME, LAST_NAME, AGE,
SEX and INCOME.
User ID "testuser" and password "test123" are set to access
TESTDB.
Python module PyMySQL is installed properly on your
machine.
You have gone through MySQL tutorial to understand MySQL
Basics.
9/30/2022 Python Programming 5
Example
9/30/2022 Python Programming 6
Creating Database Table
Once a database connection is established, we are ready to
create tables or records into the database tables using execute
method of the created cursor.
Let us create a Database table EMPLOYEE in TESTDB
database using create table query.
9/30/2022 Python Programming 7
Example
9/30/2022 Python Programming 8
INSERT Operation
The INSERT Operation is required when you want to create
your records into a database table.
The following example, executes SQL INSERT statement to
create a record in the EMPLOYEE table:
9/30/2022 Python Programming 9
Example
9/30/2022 Python Programming 10
Example can be written as follows to create SQL queries dynamically
9/30/2022 Python Programming 11
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.
9/30/2022 Python Programming 12
Cont…
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
9/30/2022 Python Programming 13
Example
The following procedure queries all the records from EMPLOYEE table having
salary more than 1000.
9/30/2022 Python Programming 14
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
SEX as 'M'. Here, we increase the AGE of all the males by
one year.
9/30/2022 Python Programming 15
Example
9/30/2022 Python Programming 16
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 AGE is more than 20.
9/30/2022 Python Programming 17
Example
9/30/2022 Python Programming 18
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.
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.
9/30/2022 Python Programming 19
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.
[Link]()
9/30/2022 Python Programming 20
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.
[Link]()
Disconnecting Database
[Link]()
9/30/2022 Python Programming 21
Example
9/30/2022 Python Programming 22
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 cancelled
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.
9/30/2022 Python Programming 23
Cont…
9/30/2022 Python Programming 24
Cont…
9/30/2022 Python Programming 25
9/30/2022 Python Programming 26