Python Interface with MySQL
Establishing connection with MySQL database.
Step – 1 Importing the MySQL connection API (Application Programming
Interface).
import [Link]
Step – 2 Creating the connection object.
# create the connection object
con=[Link](host="localhost",user="root",passwd="1234",database="sc
hooldb")
or
import [Link] as mc
con=[Link](host="localhost",user="root",passwd="1234",database="schooldb")
Arguments required for connecting MySQL database with Python:
> Username: This is the username that you use to work with MySQL Server. The
default username for the MySQL database is "root".
> Password: Password is given by the user at the time of installing the mysql
database.
> Host Name: This is server name or IP address on which MySQL is running. If you
are running on localhost, then you can use localhost, or its IP, i.e. [Link].
>Database Name: It is the name of the database to which connectivity is to be
established.
Step – 3 Testing the connection
# testing the connection
if con.is_connected():
print("Test connection succeeded")
What is a cursor object?
The cursor object is created from the connection object. It acts as a handle to execute
SQL queries and manage the context of fetch operations. It gives us the ability to
have multiple separate working environments through the same connection to the
database.
Step – 4 Creating the cursor object
# creating cursor object
mycursor=[Link]()
Step – 5 Creating the query string
# creating the query string
sql="select * from student"
Step-6 Executing the query string
# executing query string
[Link](sql)
Step – 7 Creating resultset and printing the record
# create the resultset (extract one record)
stu_set=[Link]()
while stu_set:
print(stu_set[0],"\t",stu_set[1],"\t",stu_set[2])
stu_set=[Link]()
Step-8 Closing the connection
# closing the connection
[Link]()
Program : Creating database and table programmatically
import [Link]
# create the connection object
con=[Link](host="localhost",user="root",passwd="ro
ot")
# testing the connection
if con.is_connected():
print("Test connection succeeded")
# creating cursor object
mycursor=[Link]()
# creating database
sql="create database if not exists bluebird"
[Link](sql)
[Link]()
print("Database 'bluebird' created successfully!")
# creating table courses
sql="create table if not exists course(cid char(3),cname
varchar(30),duration smallint,cfee int)"
[Link](sql)
[Link]()
print("Table 'course' created successfully!")
# closing the connection
[Link]()
Program to ‘show databases’
:
:
# show databases
sql="show databases"
[Link](sql)
alldb=[Link]()
print("All databases...")
for db in alldb:
print(db[0])
:
:
Program to ‘show tables’ in a database
:
:
# select database
sql="use schooldb"
[Link](sql)
# show tables
sql="show tables"
[Link](sql)
alltables=[Link]()
print("All tables within schooldb...")
for table in alltables:
print(table[0])
:
:
READ Operation
READ Operation on any database means to fetch some useful information from the
database. Once our database connection is established, we are ready to make a query
into this database.
We can use any of the following functions to fetch records into the result set:-
i. fetchone(): It fetches the next row of a query result set. A result set is an
object that is returned when a cursor object is used to query a table.
ii. fetchall(): It fetches all the rows in a result set. If some rows have already
been extracted from the result set, then it retrieves the remaining rows from
the result set.
iii. fetchmany(n): It fetches first/next n rows in a result set.
iv. rowcount: This is a read-only attribute and returns the number of rows that
were affected by an execute() method.
Program to fetch student records using ‘fetchone()’
method :-
:
:
# create the sql query string
sql="select * from student"
[Link](sql)
# create the resultset (extract one record)
stu_set=[Link]()
while stu_set:
print(stu_set[0],"\t",stu_set[1],"\t",stu_set[2])
stu_set=[Link]()
:
:
Program to fetch student records using ‘fetchall()’
method :-
:
:
# create the sql query string
sql="select * from student"
[Link](sql)
# create the resultset (extract all records)
stu_set=[Link]()
for rec in stu_set:
print(rec[0],"\t",rec[1],"\t",rec[2])
:
:
Program to fetch student records using ‘fetchmany()’
method :-
:
:
# create the sql query string
sql="select * from student"
[Link](sql)
# create the resultset (extract first 4 records)
stu_set=[Link](4)
for rec in stu_set:
print(rec[0],"\t",rec[1],"\t",rec[2])
:
:
Program to display the [Link] records fetched:-
:
:
# create the sql query string
sql="select * from student"
[Link](sql)
# create the resultset (extract one record)
stu_set=[Link]()
while stu_set:
print(stu_set[0],"\t",stu_set[1],"\t",stu_set[2])
print("Records fetched:",[Link])
stu_set=[Link]()
:
:
Output :-
Test connection succeeded
1 Suresh 45.50
Records fetched: 1
2 Dina 78.00
Records fetched: 2
3 Meena 65.50
Records fetched: 3
5 Ravi 65.50
Records fetched: 4
6 Velan 53.00
Records fetched: 5
7 Jenny 67.40
Records fetched: 6
8 Milind 70.50
Records fetched: 7
9 Neeru 67.00
Records fetched: 8
Parameterized queries
1. Traditional method
2. New method
1. Traditional method –
Syntax:-
<sql-query-string>="sql query+parameter" %(tuple of values)
Example :-
# print the records of those students who have scored more than x
marks. Ask for value of x from user.
import [Link]
# create the connection object
con=[Link](host="localhost",user="root",passwd="ro
ot",database="schooldb")
# creating cursor object
mycursor=[Link]()
# ask for x from user
x=float(input("Enter x:"))
# create the sql query string
sql="select * from student where marks>%s" %(x)
[Link](sql)
# create the resultset
stu_set=[Link]()
for rec in stu_set:
print(rec[0],"\t",rec[1],"\t",rec[2])
# closing the connection
[Link]()
2. New method –
Syntax:-
1. <sql-query-string>="sql query+{}".format(value)
2. <sql-query-string>="sql query+{1} and
{2}".format(1=<value1>,2=<value2>)
Example :-
Example :-
# print the records of those students who have scored more than x
marks. Ask for value of x from user.
:
:
# ask for x from user
x=float(input("Enter x:"))
# create the sql query string
sql="select * from student where marks>{}".format(x)
or
sql="select * from student where marks>{m}".format(m=x)
[Link](sql)
# create the resultset
stu_set=[Link]()
for rec in stu_set:
print(rec[0],"\t",rec[1],"\t",rec[2])
:
:
Program : Inserting records in the table
:
:
# reading records from user
for i in range(5):
print("Student #",i+1)
rollno=int(input("Enter rollno."))
sname=input("Enter name:")
marks=float(input("Enter marks:"))
sql="insert into student values(%s,'%s',%s)" %(rollno,sname,marks)
or
sql="insert into student values({},'{}',{})".format(rollno,sname,marks)
or
sql="insert into student
values({rn},'{snm}',{mr})".format(snm=sname,mr=marks,rn=rollno)
[Link](sql)
[Link]()
:
:
Program : Updating records
Q:Update the marks of any student whose rollno has been given. Ask for the
new marks from the user.
import [Link]
# create connection object
con=[Link](host="localhost",username="root",password="root",da
tabase="schooldb")
# create cursor object
stu_cursor=[Link]()
# ask for rollno and new marks
rno=int(input("What is the rollno?"))
new_marks=float(input("Enter the updated marks:"))
# sql query to update marks
sql="update student set marks={m} where rollno={r}".format(r=rno,m=new_marks)
or
sql="update student set marks={} where rollno={}".format(new_marks,rno)
or
sql="update student set marks=%s where rollno=%s" %(new_marks,rno)
stu_cursor.execute(sql)
[Link]()
# close the connection
[Link]()
The updated records :-
1 Suresh 45.50
2 Dina 78.00
3 Meena 65.50
5 Ravi 70.00 (old value = 65.5)
6 Velan 53.00
7 Jenny 67.40
8 Milind 70.50
9 Neeru 67.00
Program : Deleting records
Program to delete the record of a student whose rollno has been given.
import [Link]
# create connection object
con=[Link](host="localhost",username="root",password="root",da
tabase="schooldb")
# create cursor object
stu_cursor=[Link]()
# ask for rollno of the student
rno=int(input("What is the rollno?"))
# sql query to delete record of a student whose rollno is given
sql="delete from student where rollno={}".format(rno)
or
sql="delete from student where rollno={r}".format(r=rno)
or
sql="delete from student where rollno=%s" %(rno)
stu_cursor.execute(sql)
[Link]()
# close the connection
[Link]()
Records after deletion :-
1 Suresh 45.50
2 Dina 78.00
3 Meena 65.50
5 Ravi 70.00 (rollno 4 record is deleted)
6 Velan 53.00
7 Jenny 67.40
8 Milind 70.50
9 Neeru 67.00
----------xxxxxxxxxxxxx--------------