In real life scenario, user interact with applications whereas data is stored in
database. In order to connect a database from within Python, we need a library that
provides connectivity functionality. There are many different libraries available for
Python to accomplish this. We are going use mysql connector for the same.
Steps for creating Python and MySQL connectivity application:
1. Import the library [Link]
Syntax: import [Link] [as mc]
2. Create a connection
Syntax: <connection_object> =[Link] (host=”localhost”,
user=<username>, passwd= <password>,[ database =<database name>])
Example: mydb=[Link](host= “localhost”, user= “root”,
password = “root”, database= “Student”)
Host: Host can be the host name or the IP address (The host argument defaults to IP
address [Link])
Username: To know the user name, run select user(); command on your MySQL.
Password: It should be same as what we set during MySQL installation.
Example: Let us create a connection of python with MySQL and test it.
Is_connected() function checks whether connection string is able to connect python with
MySQL. It returns true if True otherwise False.
3. Create a cursor
Cursor: A database Cursor is a special control structure that facilitates the row-by-row
processing of records in the resultset. Whenever a SQL query runs, it gives the entire
result set in one go. We may not require the entire resultset at once. So, a cursor is
created and data from the entire resultset will be fetched row by row as per our
requirement.
Syntax: <cursor object> = <connection object>.cursor()
Example: mycursor = [Link]()
1|Page
4. Execute query
SQL queries are executed using the execute() method of the cursor object.
Syntax: <cursor object> . execute(<SQL query string>)
Example: [Link](“Select * from student_details;”)
The above program won’t print any output on the screen. But there is no error in the
program. We haven’t used any print statement in the above case.
5. Extract data from result set
As we know that, data from database is retrieved using select query, after running the
select query, we get the resultset. Now to fetch the data from resultset, following
functions are used:
a) fetchall(): It returns all the records from resultset. Each individual record will be in
the form of a tuple whereas the entire resultset will be in the form of a list.
Syntax: <variable name>=<cursor_object>.fetchall()
In the above program, if we add the following code, then we will get the tuples of the
resultset printed.
data=[Link]()
for x in data:
print(x)
Example:
b) fetchone():It returns one row from resultset in the form of a tuple. It returns None, if
no more records are there. To get multiple rows, we needs to run fetchone() multiple
times.
2|Page
Syntax: <variable name>=<cursor>.fetchone()
Example:
c) fetchmany([n]):It returns n number of records from resultset in the form of a list
where each individual record is in the form of a tuple. It returns empty tuple, if no more
records are there.
Syntax: <variable name>=<cursor>.fetchmany([n])
d) rowcount: It is cursor’s property to count the number of rows in resultset.
Syntax: <variable name>=<cursor>.rowcount
6. Close the connection
After doing all the processing, connection should be closed.
Syntax: <connection object>.close()
Example: [Link]()
PROGRAMS
Example 1: To create a database, activate database and create a table.
#TO CREATE A DATABASE AND A TABLE IN MYSQL FROM PYTHON
import m [Link]
mydb=[Link](host="localhost",user="root",passwd="pass")
mycursor=[Link]()
[Link]("CREATE DATABASE IF NOT EXISTS TRAIN")
[Link]("USE TRAIN")
[Link]("CREATE TABLE if not exists TRAIN_DETAILS (TRNO INT, TRNM
VARCHAR(30),SOURCE VARCHAR(30),DEST VARCHAR(30),CHARGES FLOAT);")
#[Link]("CREATE TABLE PASS_DET(TRNO INT, SOURCE VARCHAR(20),
DEST VARCHAR(20),NOP INT,CHARGES FLOAT);")
[Link]("SHOW TABLES;")
#print(mycursor)
for i in mycursor:
3|Page
print(i)
[Link]()
Example 2: To create a database by taking database name and connection parameters
as user input.
import [Link] as mc
host=input("Enter host name")
uname=input("Enter user name")
pwd=input("Enter password")
mydb=[Link](host=host, user=uname,passwd=pwd)
mycr=[Link]()
dbname=input("Enter database name:")
[Link]("create database if not exists {}".format(dbname))
print("database created")
[Link]()
Example 3: To select all data from a table and display.
Example 4: To select all the trains whose source is Chennai and charges is less than
1000.
Example 5: To add a primary key to an existing table
Example 6: To update the charges by 100 rupees, where destination is Bangalore
FORMAT SPECIFIER:
We need format specifier to write SQL query based on user input. For doing this we have
two ways:
1. String templates with % formatting (Old style):
Whenever we need to complete SQL query based on user input, we write a placeholder
%s in that place.
4|Page
Example:
2. String templates with {} formatting (New Style):
Whenever we need to complete SQL query based on user input, we write a placeholder {}
on that place. If we need to complete the SQL query based on multiple user input, we
write placeholder {}, {}, {} and so on at those places and pass user defined values in
format function in sequence. Here 1st value passed in format function will be passed to
1st {}, 2nd value passed to 2nd {}, 3rd value passed to 3rd {} and so on.
Example:
PERFORMING INSERT AND UPDATE QUERIES:
(i) INSERT query example:
Query= “insert into student values({}, ‘{}’, {})”.format(101, “Aravindhan”, 490)
[Link](Query)
[Link]() commit method is used for any DML commands
(Queries which make changes in the data in table)
(ii) UPDATE query example:
Query= “update student_details set total ={} where rollno={}.format(495,101)
5|Page
[Link](Query)
[Link]()
NOTE: Whenever we perform update, delete or insert query, commit() method must be
executed before closing the connection.
Syntax: <connection_object>.commit()
6|Page