Python MySQL Database

Connection
Grade 12

Computer Science
 CBSE Syllabus:
• 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
 Goals of this lesson, in this lesson, you’ll learn:

• How to connect MySQL Server and create a table in MySQL from Python
• Different MySQL Connection arguments we can use to connect to MySQL
• How to change the MySQL connection timeout when connecting through
Python
Steps to connect MySQL database in Python using MySQL Connector Python

1. Install MySQL Connector Python using pip.

2. Use the mysql.connector.connect() method of MySQL Connector
Python with required parameters to connect MySQL.
3. Use the connection object returned by a connect() method to create
a cursor object to perform Database Operations.
4. The cursor.execute() to execute SQL queries from Python.
5. Close the Cursor object using a cursor.close() and MySQL database
connection using connection.close() after your work completes.
6. Catch Exception if any that may occur during this process
Prerequisites before installing MySQL Connector Python
• You need root or administrator privileges to perform the installation process.
• Python must be installed on your machine.
• Note: – MySQL Connector Python requires python to be in the system’s
PATH. Installation fails if it doesn’t find Python.
• On Windows, If Python doesn’t exist in the system’s PATH, please manually
add the directory containing python.exe yourself.
 Python MySQL Connector Versions
Please refer to the following table of MySQL connector python versions. You need
to install a module that is compatible with your python version.

Connector/Python Version MySQL Server Versions Supported Python Versions

8.0 8.0, 5.7, 5.6, 5.5 3.6, 3.5, 3.4, 2.7

2.2 5.7, 5.6, 5.5 3.5, 3.4, 2.7

2.1 5.7, 5.6, 5.5 3.5, 3.4, 2.7, 2.6

2.0 5.7, 5.6, 5.5 3.5, 3.4, 2.7, 2.6

1.2 5.7, 5.6, 5.5 (5.1, 5.0, 4.1) 3.4, 3.3, 3.2, 3.1, 2.7, 2.6
 Pip Command to install MySQL Connector python
It is always accessible and straightforward to install any module using pip in
python. MySQL Connector Python is available on pypi.org so you can install
using pip command.
Use the following pip command to install MySQL Connector python.
pip install mysql-connector-python
If you are facing any problem while installing, please mention the version of the
module and then try to install it again. Refer to the above table to install the
correct version.
pip install mysql-connector-python==8.0.11
An API (Application Programming Interface) is a set of
rules that are shared by a particular service. These rules
determine in which format and with which command set
your application can access the service, as well as what
data this service can return in the response. The API acts
as a layer between your application and external service.
You do not need to know the internal structure and
features of the service, you just send a certain simple
command and receive data in a predetermined format.
Arguments required to connect MySQL from Python
You need to know the following detail of the MySQL server to perform the
connection from Python.
Username – i.e., the username that you use to work with MySQL Server. The
default username for the MySQL database is a root (user="root" )
Password – Password is given by the user at the time of installing the MySQL
database. If you are using root then you won’t need the password. Here I have
used password as: passwd="yadav"
Host Name – is the server name or Ip address on which MySQL is running. if
you are running on localhost, then you can use host="localhost", or it’s IP, i.e.
127.0.0.0
Database Name – Database name to which you want to connect. Here we are
using Database named database="xii_b" because we have already created this
for our example.
After successful execution of above statements in python following out will be displayed
<mysql.connector.connection.MySQLConnection object at 0x022624F0>
Otherwise an error message will be shown

Output:
Can we change the order of the arguments in connect()
functions? Justify your answer?
Can we change the order of the arguments in connect()
functions? Justify your answer?

Ans.:
Yes. These are called named arguments. To reduce the necessity to know the
order of arguments in a function, Python allows named arguments. When you
call a function, you can precede some or all of the arguments by a name and an
equal sign, to let the function know exactly which argument you are passing to
the function. So if we invoke the connect() function by changing the order of
arguments.
Output:

Error Message when Database is not correct

 Understand the Python MySQL Database connection program
import mysql.connector
This line imports the MySQL Connector Python module in your program so you can
use this module’s API to connect MySQL.
from mysql.connector import Error
mysql connector Error object is used to show us an error when we failed to connect
Databases or if any other database error occurred while working with the database.
Example ACCESS DENIED ERROR when username
or password is wrong.
mysql.connector.connect()
Using this method we can connect the MySQL Database, this method accepts four
required parameters: Host, Database, User and Password that we already discussed.
connect() method established a connection to the MySQL database from Python
application and returned a MySQLConnection object. Then we can use
MySQLConnection object to perform various operations on the MySQL Database.
The Connect() method can throw an exception, i.e. Database error if one of the required
parameters is wrong. For example, if you provide a database name that is not present in
MySQL, then Python application throws an exception. So check the arguments that you are
passing to this method.
connection.is_connected()
is_connected() is the method of the MySQLConnection class through which we can verify is
our python application connected to MySQL.
connection.cursor()
This method returns a cursor object. Using a cursor object, we can execute SQL queries. The
MySQLCursor class instantiates objects that can execute operations such as SQL statements.
Cursor objects interact with the MySQL server using a MySQLConnection object.
cursor.close()
Using the cursor’s close method we can close the cursor object. Once we close the cursor
object, we can not execute any SQL statement.
connection.close()
At last, we are closing the MySQL database connection using a close() method of
MySQLConnection class.
Now you know how to connect to MySQL server from python let’s proceed with
creating a table from Python.
Steps for Creating Database CLUB and Table: MEMBER.

mysql> CREATE DATABASE CLUB;

mysql> USE CLUB;
mysql> CREATE TABLE MEMBER (M_ID CHAR(5) PRIMARY KEY, NAME
VARCHAR(10), ACTIVITY VARCHAR(10));
mysql> INSERT INTO MEMBER VALUES ('M1001','Amina','GYM');
mysql> INSERT INTO MEMBER VALUES ('M1002','Pratik','GYM');
mysql> INSERT INTO MEMBER VALUES ('M1003','Simon','SWIMMING');
mysql> INSERT INTO MEMBER VALUES ('M1004','Rakesh','GYM');
mysql> INSERT INTO MEMBER VALUES ('M1005','Avneet','SWIMMING');
 Steps for Creating Database CLUB and Table: MEMBER
mysql> select * from Member;
+------------+-----------+-----------------+
| M_ID | NAME | ACTIVITY |
+-----------+------------+-----------------+
| M1001 | Amina | GYM |
| M1002 | Pratik | GYM |
| M1003 | Simon | SWIMMING |
| M1004 | Rakesh | GYM |
| M1005 | Avneet | SWIMMING |
+--------- -+------------+-----------------+
Consider the following SQL table MEMBER in a SQL Database CLUB:
Table: Member
+------------+-----------+-----------------+
| M_ID | NAME | ACTIVITY |
+-----------+------------+-----------------+
| M1001 | Amina | GYM |
| M1002 | Pratik | GYM |
| M1003 | Simon | SWIMMING |
| M1004 | Rakesh | GYM |
| M1005 | Avneet | SWIMMING |
+--------- -+------------+-----------------+
Assume that the required library for establishing the connection between Python and MYSQL
is already imported in the given Python code. Also assume that DB is the name of the database
connection for table MEMBER stored in the database CLUB.
Predict the output of the following code-1:
Output:
Predict the output of the following code-2:
Output: Statement-1

Output: Statement-2
 Understand the Python MySQL Database connection program
import mysql.connector
This line imports the MySQL Connector Python module in your program so you can
use this module’s API to connect MySQL.
from mysql.connector import Error
mysql connector Error object is used to show us an error when we failed to connect
Databases or if any other database error occurred while working with the database.
Example ACCESS DENIED ERROR when username
or password is wrong.
mysql.connector.connect()
Using this method we can connect the MySQL Database, this method accepts four
required parameters: Host, Database, User and Password that we already discussed.
connect() method established a connection to the MySQL database from Python
application and returned a MySQLConnection object. Then we can use
MySQLConnection object to perform various operations on the MySQL Database.
The Connect() method can throw an exception, i.e. Database error if one of the required
parameters is wrong. For example, if you provide a database name that is not present in
MySQL, then Python application throws an exception. So check the arguments that you are
passing to this method.
connection.is_connected()
is_connected() is the method of the MySQLConnection class through which we can verify is
our python application connected to MySQL.
connection.cursor()
This method returns a cursor object. Using a cursor object, we can execute SQL queries. The
MySQLCursor class instantiates objects that can execute operations such as SQL statements.
Cursor objects interact with the MySQL server using a MySQLConnection object.
cursor.close()
Using the cursor’s close method we can close the cursor object. Once we close the cursor
object, we can not execute any SQL statement.
connection.close()
At last, we are closing the MySQL database connection using a close() method of
MySQLConnection class.
Now you know how to connect to MySQL server from python let’s proceed with
creating a table from Python.
Creating Database TRAVEL, Using Database TRAVEL, Creating Table PASSENGERS
and inserting records into Table PASSENGERS.
mysql> CREATE database TRAVEL;
mysql> USE TRAVEL;
mysql> CREATE TABLE PASSENGERS (TNO CHAR(3), NAME VARCHAR(20), START VARCHAR(10),
END VARCHAR(10));
mysql> INSERT INTO PASSENGERS VALUES ('T1', 'RAVI KUMAR', 'DELHI', 'MUMBAI' );
mysql> INSERT INTO PASSENGERS VALUES ('T2', 'NISHANT JAIN', 'DELHI', 'KOLKATA' );
mysql> INSERT INTO PASSENGERS VALUES ('T3', 'DEEPAK PRAKASH', 'MUMBAI', 'PUNE' );
mysql> SELECT * FROM PASSENGERS;
+--------+-------------------------+--------------+--------------+
| TNO | NAME | START | END |
+--------+-------------------------+--------------+--------------+
| T1 | RAVI KUMAR | DELHI | MUMBAI |
| T2 | NISHANT JAIN | DELHI | KOLKATA |
| T3 | DEEPAK PRAKASH | MUMBAI | PUNE |
+--------+-------------------------+--------------+--------------+
For the following SQL Table named PASSENGERS in a
database TRAVEL:

TNO NAME START END

T1 RAVI KUMAR DELHI MUMBAI
T2 NISHANT JAIN DELHI KOLKATA
T3 DEEPAK PRAKASH MUMBAI PUNE
A cursor named Cur is created in Python for a connection of a host which contains the
database TRAVEL. Write the output for the execution of the following Python statements for
the above SQL Table PASSENGERS:
Output: # Statement 1 Output: # Statement 2
If for loop is as follows:
If for loop is as follows: for R in Recs:
for R in Recs: print(R[0], R[1],R[2],sep="#")
print(R[1])

Output: # Statement 3
If for loop is as follows:
for R in Recs:
print(R)
 Cursor object :
The MySQLCursor class instantiates objects that can execute operations such as SQL statements. Cursor objects
interact with the MySQL server using a MySQLConnection object.
How to create cursor object and use it:

Use different database name which you

want to create instead of XII_B

We are creating a database named Books if it is already not created with the help of cursor object. Next line
executes the SQL query show databases and store result in MyCursor as collection, whose values are being
fetched in x variable one by one. On execution of above program Books database is created and a list of available
databases is shown.
Output:
 Please do the programs given in this power point
presentation and check Python-MySQL connectivity
on your laptop or computer.

More programs based on Python-MySQL connectivity will be

done when the school re-opens after the summer vacation.
Questions from CBSE Sample QP 2022-2023: Q. No. 32(b)
Create the Database: school and Table: Student

mysql> CREATE DATABASE school;

mysql> use school;
Database changed
mysql> CREATE TABLE STUDENT (RollNo INT, Name Varchar(15), Class
INT, Marks INT);
Query OK, 0 rows affected (0.95 sec)
After creating the table. Do the following program.
This program accepts the user input Roll Number, Name, Class and Marks of a
student and stores it in the table.
Sample input data and the contents of data stored in Table: student
Questions from CBSE Sample QP 2022-2023: Q. No. 32(b) OR Option
import mysql.connector as mysql
con1=mysql.connect(host="localhost",user="root",
password="yadav", database="school")
mycursor=_________________ #Statement 1
print("Students with marks greater than 75 are : ")
_________________________ #Statement 2
data=_____________________ #Statement 3
for i in data:
print(i)
print()
The Table: Student was created in previous
program. Having details of two students:
Completed program:
Output:
Modify the previous program to display all records form Table: Student.
Output:
 Cursor object :
The MySQLCursor class instantiates objects that can execute operations such as SQL statements. Cursor objects
interact with the MySQL server using a MySQLConnection object.
How to create cursor object and use it:

Use different database name which you

want to create instead of XII_B

We are creating a database named Books if it is already not created with the help of cursor object. Next line
executes the SQL query show databases and store result in MyCursor as collection, whose values are being
fetched in x variable one by one. On execution of above program Books database is created and a list of available
databases is shown.
Output:
 How to create table at run time
Table creation is very easy ,if we are already well versed in SQL table creation then we have to
just pass the create table query in execute() method of cursor object. But before table creation
we must open the database. Here we are opening database school(through connect() method)
before student table creation.

Output:
Check the table created in MySQL by the program:
How to change table structure/(add, edit, remove column of a table) at run time
To modify the structure of the table we just have to use alter table query. Below program will add
a column mark in the student table
Python code output and MySQL output:

Before adding column marks

After adding column marks

How to insert record in a table at run time
Python Code Output:
Output: (MySQL- Table: Student)
Output:

Before deletion record

After deletion record

Output:

Before updating marks

After updating marks of roll no 2

Output:
Output: Data stored in Table: STUD2
 How to search records of a table at run time
Below statement demonstrate the use of select query for searching specific record from a
table. The program will prompt a name from user, as user types the name, that name is
searched in the table student with the help of select query. The result will be shown with
the help of mycursor collection.
Output:
Output:
Output:
 How to fetch all records of a table at run time

Output:
 How to fetch all records of a table at run time

Output:
Output:
 MySQLCursor.fetchall() Method
MyCursor.fetchall()
The 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.
MySQLCursor.fetchone() Method
This method retrieves the next row of a query result set and returns a single
sequence, or None if no more rows are available. By default, the returned
tuple consists of data returned by the MySQL server, converted to Python
objects.

MySQLCursor.fetchmany() Method
rows = cursor.fetchmany(size=1)
This method fetches the next set of rows of a query result and returns a list of
tuples. If no more rows are available, it returns an empty list.
How to fetch one record of a table at run time

print(row[1], row[3]) # to display only name and marks

Output: print(row[1], row[3]) # to display only name and marks
Output:
Output:
buffered=True
We have used my_cursor as buffered cursor.
my_cursor = my_connect.cursor(buffered=True)
This type cursor fetches rows and buffers them after getting output from
MySQL database.

We can use such cursor as iterator. There is no point in using buffered cursor
for single fetching of rows. If we don’t use buffered cursor then we will get -1
as output from rowcount.
The reason is that without a buffered cursor, the results are "lazily" loaded,
meaning that "fetchone" actually only fetches one row from the full result set
of the query. When you will use the same cursor again, it will complain that
you still have n-1 results (where n is the result set amount) waiting to be
fetched. However, when you use a buffered cursor the connector fetches ALL
rows behind the scenes and you just take one from the connector so the mysql
db won't complain.
Output:
How to update record of a table at run time
How to update record of a table at run time
Output:
Before updating marks of rollno 20

After updating marks of rollno 20

 Manage Database Transaction
In the following program if update query is successfully executed then commit()
method will be executed otherwise exception error part will be executed where
revert of update query will be done due to error. At finally we are closing cursor
as well as connection.
To rollback or commit we have to set autocommit=False just like
conn.autocommit=False in the program otherwise rollback will not work.
Output:
Before updating marks of rollno 20

After updating marks of rollno 20

Students are advised to develop menu driven
programs using above concepts for better
understating of python MySql database interface.
Menu driven MySQL-Connectivity Programs to perform the following operations:
Database CBSE2022, Table Student
Student Table has: Roll No-Int, Name-Varchar, Stream-varchar, Total Marks-Int,
Grade-char, Class-int.
1. Create Database and Show Databases
2. Create Table
3. Show tables
4. Display structure of the table
5. Insert Data into Table
6. Fetch data
7. Update Data
8. Delete Data
9. Sort Data
10. Search Data
11. Exit
Structure of Table Student
Outputs: