Chapter 15:
Interface Python with
MySQL (A)
Introduction Program to check
01 04
connection b/w Python &
MySQL
05
Installing MySQL Program to create
02
driver database
Program to create table
03
Connecting MySQL with 06
Python : Steps
Python
Python needs a MySQL driver to access the MySQL database.
Use PIP to install "MySQL Connector".
PIP is most likely already installed in your Python environment.
Now type the following at command prompt:
python -m pip install mysql-connector
Step 1: # Start Python IDLE
Step 2: # Import mysql.connector Package
import mysql.connector
Step 3: # Establish a Connection to MySQL Database
# for this we will use connect() function of mysql.connector package
this function takes four parameter.
mycon = mysql.connector.connect(host="localhost", user="root",
passwd="aps", database="mydb1")
# we can also check for the successful connection as follows:
if mycon.is_connected():
print(“Successfully Connected to MySQL Database”)
# Connection Object : A database connection object controls the
connection to the database.
Step 4: Create a Cursor Instance
# A database cursor 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 the
query.
cursor=mycon.cursor()
Step 5: Execute SQL Query
#Once you have created a cursor, you need to execute SQL query using execute()
function with cursor object.
cursor.execute(“select * from emp”)
Step 6: Extract data from ResultSet
# A ResultSet refers to a logical set of records that are fetched from the database
by executing an SQL Query and made available to the application program.
We can extract data from ResultSet using any of the following fetch…() function.
1. fetchall() : it will return all the rows from the resultset in the form of tuple
containing the records.
data=cursor.fetchall()
count=data.rowcount() # it will count total number of rows/records
print(“Total number of records are “,count)
for row in data: # it will extract one row at a time and display it
print (row)
2. fetchone() : it will return one record at a time from resultset.
data= cursor.fetchone()
while(data):
print(data)
data= cursor.fetchone()
3. fetchmany(N): it will return specified number of rows at a time from resultset.
N=int(input(“Enter how many records you want to retrieve”))
data= cursor.fetchmany(N)
print(data)
or
for row in data:
print(row)
Step 7: Clean up the Environment
After you are through all the processing, in this final step you need to close the
environment.
mycon.close()
Ouput
Ouput
Before execution of the
program
After execution of the program
Before execution of the
program
After execution of the program
