CBSE Curriculum
Interface of python with an SQL database:
▪ connecting SQL with Python
▪ performing insert, update, delete queries using cursor, display data by using
connect(), cursor(), execute(), commit(), fetchone(), fetchall(), rowcount,
▪ creating database connectivity applications
▪ use of %s format specifier or format() to perform queries
Python MySQL Data Connectivity
Front End pymysql or Back End
(User Interface) Mysqlclient or (Database Application)
mysql-connector-python
Python - MySQL connector / API
Three MySQL API for Python are :
1. pymysql : pure Python MySQL client
2. mysqlclient (MySQLdb) : fastest MySQL connector for Python. implements the Python
Database API v 2 0 and is built on top of the MySQL C API
3. mysql-connector-python : It is written in pure Python but is slower performance wise.
Installing connector from command prompt :
▪ pip install pymysql
▪ pip install mysqlclient
▪ pip install mysql-connector-python
Steps to Connect Python and MySQL
Case sensitive
1.
1 import module [ import pymysql / MySQLdb / [Link] ]
2.
2 establish connection [ .connection( ) ]
3.
3 create cursor using the established connection [ .cursor() ]
4.
4 execute SQL query using the cursor [ .execute() ]
5.
5 close connection [ .close() ]
1
Check if connector is installed
import statement connector
▪ pip install pymysql
▪ pip install mysql-connector-python
▪ pip install mysqlclient
No error after import statement at
Python prompt confirms the
successful installation of
connector.
Example : To create a database and display all the databases
2
3
4
4
Output
Note: Multiple SQL statements can be
executed with a single cursor multiple SQL
statements
2 Establishing connection using .connect()
Syntax:
<Connection Object> = <DB module>.connect( host = <hostname>,
user = <UserName>, password = <Password> ,
database = <DatabaseName> )
host – is the host name or IP address of the MySQL server. It can be either ‘localhost’ or ‘[Link]’
user – is the user name used to authenticate with the MySQL server
password - The password to authenticate the user with the MySQL server
database [optional] – is the database name to use
Example:
alias
3
Creating Cursor Object .cursor()
• Cursor objects interact with the MySQL server using a MySQLConnection.
• Cursor Object can execute SQL statements and store results of SQL statements which can be
traversed using fetchone( ), fetchall( ) , fetchmany( )
Syntax:
<Cursor Object> = <Connection Object>.cursor( )
Example:
Connection
Cursor object
object
Example : create a table student & display all tables in school database
SQL
Queries
INSERT record (constant values)
Output
<Connection>.commit() method sends a COMMIT statement to the MySQL server,
committing (permanently saving) the current transaction.
By default Connector/Python does not autocommit, it is important to call this method
after every DML transaction ( INSERT , UPDATE , DELETE )
INSERT multiple records (constant values)
SQL
query
Output
3 ways of formatting Python String
#1. ( old style ) using % operator
More than one variable need to be %s format specifier tells
enclosed in a tuple Python where to substitute the
value of variables v1 and v2 ,
output represented as a string.
#2. ( new style ) using [Link]()
The values are passed as
parameters into the format
function corresponding to the
placeholders { }.
Variables can be substituted by
position or by name.
#3. ( new style v3.6 onwards ) using string literals, f-string
prefix "f" before the string
literal. This method lets you
embedded Python expressions
inside string constants.
INSERT record ( user input ) using % (Modulo or Interpolation) Operator
Table : student
SQL
Query
Note: The quotes around
char/varchar and date fields in
insert SQL query
SQL query to INSERT record ( user input ) using [Link]()
SQL query to INSERT record ( user input ) using f-string
UPDATE records
Data after
update
[Link] Property : returns the number of rows
returned for SELECT statements, or the number of rows affected by
DML statements such as INSERT or UPDATE or DELETE.
DELETE records
delete
DISPLAY ALL records using [Link]( )
Output
[Link]() Method : fetches all (or all remaining)
rows of a query result set and returns a list of tuples. If no more
rows are available, it returns an empty list.
DISPLAY ALL records using [Link]( )
output
[Link]() Method retrieves
the next row of a query result set and
returns a single tuple, or None if no more
rows are available.
[Link]()
[Link]() Method
rows = [Link](size=1)
This method fetches the next set of rows ( specified in size argument, default is 1 ) of a query
result and returns a list of tuples. If no more rows are available, it returns an empty list.
Q : Consider Library database with a table Book having 20 records and answer the
questions that follow based on the code below:
I. What will be the output of the code ?
[Link] record(s) will the statement print(r4) display ?
Using [Link]() and [Link]()
Q1: Display the name Q2: Display the name
and percentage of class and percentage top two
XII topper. scores of class XII
Table: Student4
output
output