Library Needed: We need any one of the following library to connect to the MySQL database with Python.
1) mysql.connector : It is a library provided by oracle
2) pymysql : It is pure Python library.
Steps of Connectivity:
1) Start Python
2) Import the required library.
3) Open a Connection
4) Create a cursor instance.
5) Execute the SQL query.
6) Extract data.
7) Clean up the Environment.
1) Start Python: Open a New File or stay at Python terminal.
2) Import the required library: We can import any one of the following library to connect to the MySQL database with Python
1) mysql.connector
2) pymysql
Syntax to import any library in python is
import libraryname
Examples:
1) import mysql.connector
2) import pymysql
Before importing, Ensure that the library is installed in your system, otherwise you will get an error as
>>> import pymysql
Traceback (most recent call last):
File "<pyshell#0>", line 1, in <module>
import pymysql
ModuleNotFoundError: No module named 'pymysql'
>>> import mysql.connector
Traceback (most recent call last):
File "<pyshell#1>", line 1, in <module>
import mysql.connector
ModuleNotFoundError: No module named 'mysql'
You can install the library as
1) Open command prompt by clicking on Run (Search on the windows) and then type cmd and click on OK
2) Type pip install pymysql to install pymysql
3) Type pip install mysql.connector to install mysql.connector
C:\Users\DELL>pip install pymysql
Collecting pymysql
Using cached PyMySQL-1.0.2-py3-none-any.whl (43 kB)
Installing collected packages: pymysql
Successfully installed pymysql-1.0.2
WARNING: You are using pip version 20.2.3; however, version 23.0 is available.
You should consider upgrading via the 'c:\users\dell\appdata\local\programs\python\python39\python.exe -m pip install --upgrade pip' command.
C:\Users\DELL>pip install mysql.connector
Collecting mysql.connector
Using cached mysql-connector-2.2.9.tar.gz (11.9 MB)
Using legacy 'setup.py install' for mysql.connector, since package 'wheel' is not installed.
Installing collected packages: mysql.connector
Running setup.py install for mysql.connector ... done
Successfully installed mysql.connector
WARNING: You are using pip version 20.2.3; however, version 23.0 is available.
You should consider upgrading via the 'c:\users\dell\appdata\local\programs\python\python39\python.exe -m pip install --upgrade pip' command.
C:\Users\DELL>
Now, you can import library as
>>> import pymysql >>> import mysql.connector
You can also give any other name (generally short name) ie alias name for this library as
>>> import pymysql as p >>> import mysql.connector as m
After giving the alias name for a library, you can refer to that library with the alias name. But you can’t refer to the library with its original name.
3) Open a Connection:
Database Connection Object: It controls the connection to the database. It represents a unique session with a database connected from within a program. We can create a connection as
1) connection_name = library_name.connect(host=”host name” , user=”user name” , passwd=”password” , database=”databse name” )
2) connection_name = library_name.connect(host=”host name” , user=”user name” , passwd=”password” )
We can skip the database perimeter from the above statements, as it is optional. Examples:
>>> import pymysql >>> con1=pymysql.connect(host="localhost" , user="root" , passwd="" , database="test" ) >>> con1=pymysql.connect(host="localhost" , user="root" , passwd="" ) >>> import pymysql as p >>> con2=p.connect(host="localhost" , user="root" , passwd="" , database="test" ) >>> con2=p.connect(host="localhost" , user="root" , passwd="" ) >>> import mysql.connector >>> con3=mysql.connector.connect(host="localhost" , user="root" , passwd="" , database="test" ) >>> con3=mysql.connector.connect(host="localhost" , user="root" , passwd="" ) >>> import mysql.connector as m >>> con4=m.connect(host="localhost" , user="root" , passwd="" , database="test" ) >>> con4=m.connect(host="localhost" , user="root" , passwd="")
If we have created a connection using mysql.connector library, then we can check, whether a connection is created successfully or not, using the is_connected() method, which returns True, is connection is created successfully.
>>> con3.is_connected() True >>> con4.is_connected() True
But if you have created the connection using pymysql , then it does not contain is_connected() method and python will give error as
>>> con1.is_connected()
Traceback (most recent call last):
File "<pyshell#2>", line 1, in <module>
con1.is_connected()
AttributeError: 'Connection' object has no attribute 'is_connected'
>>> con2.is_connected()
Traceback (most recent call last):
File "<pyshell#3>", line 1, in <module>
con2.is_connected()
AttributeError: 'Connection' object has no attribute 'is_connected'
4) Create a Cursor instance:
Database Cursor: It is a special control structure that facilitates the row by row processing of records in the result set i.e. the set of records retrieved as per query. We can create a cursor using the connection object as
cursor_name=connection_name.cursor()
Examples:
>>> cursor1=con1.cursor() >>> cursor2=con2.cursor() >>> cursor3=con3.cursor() >>> cursor4=con4.cursor() >>>
We can skip the database perimeter while creating the connection, as it is optional. But if no database is selected, we can’t perform any operation with any database objects like tables etc. So after creating cursor, we must execute following command to open a database
cursor_name.execute(“use databse_name”)
>>>cursor1.execute("use test")
5) Execute SQL Query: We can execute any valid SQL query
1) to create a table or
2) to retrieve (select) data from a SQL table or
3) to manipulate (insert, update, delete) data in a SQL table and save the changes permanently using commit() method.
4) any other valid SQL query.
Result Set: It is a logical set of records that are fetched from the database by executing a SQL query and made available to the application program. If we execute select query in MySQL, then a result set is retrieved.
Create a table from within the python :
>>> cursor1.execute("create table garg(rollno integer primary key, name varchar(20))")
0
Insert records in a table created in MySQL: After creating table, we can insert records as
>>> cursor1.execute("insert into garg values(1,'Sheetal')")
1
>>> cursor1.execute("insert into garg values(2,'Rajesh')")
1
>>> cursor1.execute("insert into garg values(3,'Ansh')")
1
>>> cursor1.execute("insert into garg values(4,'Saksham')")
1
# to permanently save all these inserted records in MySQL table
>>>con1.commit()
>>>
We can now retrieve the records as
>>> cursor1.execute("select * from garg")
4
6) Extract Data: We can extract the data using the following methods
1) fetchone() : to fetch 1 record at a time in a tuple form
2) fetchmany() : to fetch multiple records at a time in a tuple of tuples form
3) fetchall() : to fetch all records at a time in a tuple of tuples form
4) rowcount: It is a property to count the number of records retrieved so far. Initially when no record is fetched, it returns -1
>>> cursor1.execute("select * from garg")
4
>>># To count the number of records retrieved so far
>>> cursor1.rowcount
-1
>>># 1 record from starting position is fetched
>>> cursor1.fetchone()
(1, 'Sheetal')
>>># To count the number of records retrieved so far
>>> cursor1.rowcount
1
>>># 2 records from current position is fetched
>>> cursor1.fetchmany(2)
((2, 'Rajesh'), (3, 'Ansh'))
>>># To count the number of records retrieved so far
>>> cursor1.rowcount
3
>>># all records from current position is fetched
>>> cursor1.fetchall()
((4, 'Saksham'),)
>>># To count the number of records retrieved so far
>>> cursor1.rowcount
4
>>># No record retrieved as it reached end
>>> cursor1.fetchone()
>>># To count the number of records retrieved so far
>>> cursor1.rowcount
4
We can print records one by one as
>>> cursor1.execute("select * from garg")
4
>>> for record in records:
print(record)
(1, 'Sheetal')
(2, 'Rajesh')
(3, 'Ansh')
(4, 'Saksham')
7) Clean up the environment: We must close the connection after processing.
connection_name.close()
>>> con1.close()
Parameterized Queries: We can execute parametrized queries in Python with user inputted values as
1) f % v
2) using string.format
Using f % v
It is a standard form to format the strings where f is a formatted string containing %s at various place holders and v is a tuple of values to be replaced for various placeholders in place of %s in the formatted string f
>>> rollno=int(input("enter rollno "))
enter rollno 3
>>> name=input("enter name ")
enter name Rajesh
>>> query1="Select * from student where rollno=%s" %rollno
>>> cursor.execute(query1)
>>> cursor.fetchone()
(3, 'Ansh')
>>> query2="Select * from student where name='%s'" %name
>>> cursor.execute(query2)
>>> cursor.fetchone()
(2, 'Rajesh')
>>> query="Select * from student where rollno= %s or name='%s'" %(rollno,name)
>>> cursor.execute(query)
>>> cursor.fetchall()
[(2, 'Rajesh'), (3, 'Ansh')]
>>> query="Select * from student where rollno= %s or name='%s'"
>>> cursor.execute(query%(rollno,name))
>>> cursor.fetchall()
[(2, 'Rajesh'), (3, 'Ansh')]
Using string.format
string is a string having {} for various placeholders in the string and format is a function having arguments as the values of various placeholders for the string. Examples:
>>> rollno=int(input("enter rollno "))
enter rollno 3
>>> name=input("enter name ")
enter name Rajesh
>>> query1="Select * from student where rollno={}".format(rollno)
>>> cursor.execute(query1)
>>> cursor.fetchall()
[(3, 'Ansh')]
>>> query1="Select * from student where name='{}'".format(name)
>>> cursor.execute(query1)
>>> cursor.fetchall()
[(2, 'Rajesh')]
>>> query1="Select * from student where rollno={} or name='{}'".format(rollno, name)
>>> cursor.execute(query1)
>>> cursor.fetchall()
[(2, 'Rajesh'), (3, 'Ansh')]
>>> query1="Select * from student where rollno={} or name='{}'"
>>> cursor.execute(query1.format(rollno,name))
>>> cursor.fetchall()
[(2, 'Rajesh'), (3, 'Ansh')]
Program1: To Create a table in MySQL, insert a few records and display the records in Python
import mysql.connector as m
con=m.connect(host="localhost" , user="root" , passwd="")
if con.is_connected():
print("Successfully Connected to SQL")
cursor=con.cursor()
cursor.execute("use test")
cursor.execute("create table student(rollno integer primary key, name varchar(20))")
cursor.execute("insert into student values(1,'Sheetal')")
cursor.execute("insert into student values(2,'Rajesh')")
cursor.execute("insert into student values(3,'Ansh')")
cursor.execute("insert into student values(4,'Saksham')")
con.commit()
cursor.execute("select * from student")
records=cursor.fetchall()
print("Records presently available in the table are")
for record in records:
print(record)
con.close()
else:
print("Error in Connectivity")
Output is:
Successfully Connected to SQL Records presently available in the table are (1, 'Sheetal') (2, 'Rajesh') (3, 'Ansh') (4, 'Saksham')
Program2: Program to display the records of a already created table in MYSQL
import mysql.connector as m
con=m.connect(host="localhost" , user="root" , passwd="")
if con.is_connected():
print("Successfully Connected to SQL")
cursor=con.cursor()
cursor.execute("use test")
cursor.execute("select * from student")
records=cursor.fetchall()
print("Records presently available in the table are")
for record in records:
print(record)
con.close()
else:
print("Error in Connectivity")
Output is:
Successfully Connected to SQL Records presently available in the table are (1, 'Sheetal') (2, 'Rajesh') (3, 'Ansh') (4, 'Saksham')
Program: To insert a new record in MySQL table from Python with user inputted values.
import mysql.connector as m
con=m.connect(host="localhost" , user="root" , passwd="")
if con.is_connected():
print("Successfully Connected to SQL")
cursor=con.cursor()
cursor.execute("use test")
cursor.execute("select * from student")
records=cursor.fetchall()
print("Records presently available in the table are")
for record in records:
print(record)
rollno=int(input("enter rollno of new student "))
name=input("enter name of new student ")
query="insert into student values(%s,'%s')" %(rollno,name)
cursor.execute(query)
con.commit()
print("Record is inserted successfully")
cursor.execute("select * from student")
records=cursor.fetchall()
print("Records presently available in the table are")
for record in records:
print(record)
con.close()
else:
print("Error in Connectivity")
Output is:
Successfully Connected to SQL Records presently available in the table are (1, 'Sheetal') (2, 'Rajesh') (3, 'Ansh') (4, 'Saksham') enter rollno of new student 6 enter name of new student Nidhi Record is inserted successfully Records presently available in the table are (1, 'Sheetal') (2, 'Rajesh') (3, 'Ansh') (4, 'Saksham') (6, 'Nidhi')
Program: To update an already existing record in MySQL table from Python with user inputted values.
import mysql.connector as m
con=m.connect(host="localhost" , user="root" , passwd="")
if con.is_connected():
print("Successfully Connected to SQL")
cursor=con.cursor()
cursor.execute("use test")
oldrollno=int(input("enter rollno of student to be updated "))
found=False
cursor.execute("select * from student")
records=cursor.fetchall()
print("Records presently available in the table are")
for record in records:
print(record)
if record[0]==oldrollno:
found=True
if found==False:
print("Rollno does not exist in the table")
else:
newrollno=int(input("enter new rollno of student "))
newname=input("enter new name of student ")
query="update student set rollno=%s, name='%s' where rollno=%s" %(newrollno,newname,oldrollno)
cursor.execute(query)
con.commit()
print("Record is updated successfully")
cursor.execute("select * from student")
records=cursor.fetchall()
print("Records presently available in the table are")
for record in records:
print(record)
con.close()
else:
print("Error in Connectivity")
Output 1 :
Successfully Connected to SQL enter rollno of student to be updated 5 Records presently available in the table are (1, 'Sheetal') (2, 'Rajesh') (3, 'Ansh') (4, 'Saksham') (6, 'Nidhi') Rollno does not exist in the table
Output 2 :
Successfully Connected to SQL enter rollno of student to be updated 4 Records presently available in the table are (1, 'Sheetal') (2, 'Rajesh') (3, 'Ansh') (4, 'Saksham') (6, 'Nidhi') enter new rollno of student 40 enter new name of student Saksham Garg Record is updated successfully Records presently available in the table are (1, 'Sheetal') (2, 'Rajesh') (3, 'Ansh') (6, 'Nidhi') (40, 'Saksham Garg')
Program: To delete an already existing record in a MySQL table from Python with user inputted values.
import mysql.connector as m
con=m.connect(host="localhost" , user="root" , passwd="")
if con.is_connected():
print("Successfully Connected to SQL")
cursor=con.cursor()
cursor.execute("use test")
cursor.execute("select * from student")
records=cursor.fetchall()
print("Records presently available in the table are")
for record in records:
print(record)
rollno=int(input("enter rollno of student to be deleted "))
query="delete from student where rollno=%s" %rollno
cursor.execute(query)
con.commit()
print("Record is deleted successfully")
cursor.execute("select * from student")
records=cursor.fetchall()
print("Records presently available in the table are")
for record in records:
print(record)
con.close()
else:
print("Error in Connectivity")
Output is:
Successfully Connected to SQL Records presently available in the table are (1, 'Sheetal') (2, 'Rajesh') (3, 'Ansh') (4, 'Saksham') (5, 'amit') (6, 'Nidhi') enter rollno of student to be deleted 5 Record is deleted successfully Records presently available in the table are (1, 'Sheetal') (2, 'Rajesh') (3, 'Ansh') (4, 'Saksham') (6, 'Nidhi')