INTERFACE PYTHON WITH
MYSQL
STEPS FOR CREATING DATABASE CONNECTIVITY
APPLICATIONS
STEP 1: Start Python
STEP 2: Import the package required for database programming
STEP 3: Open a connection to database
STEP 4: Create a cursor instance
STEP 5: Execute a query
STEP 6: Extract data from result set
STEP 7: Cleanup the environment
STEP 1:
import mysql.connector
or
import mysql.conector as ms 🡪 we can use any identifier of
our choice
STEP 3: OPEN A CONNECTION TO MYSQL Database:
SYNTAX:
<Connection-object> = mysql.connector.connect(host=<host-name>,user=<user-
name>,
passwd=<password>,[,database=<database>])
user 🡪 is the username on MySQL
password 🡪 is the password of the user
host-name 🡪 the database server hostname or IP address
database 🡪 is optional ( if need we have to give any one MYSQL database)
STEP 4: Create a cursor Instance
🡪 A database cursor is a special controlled structure that
facilitates the
row by row processing of records in the result set.
SYNTAX:
<cursorobject> =
<connectionobject>.cursor()
eg: cursor=x.cursor()
STEP 5: Executing SQl Query
🡪 After the cursor is created, the SQL query can be used with
the execute().
SYNTAX:
<cursor_object>.execute(‘SQL Query’)
eg:
<cursor.execute(‘Select * from emp’)
STEP 6: Extract Data from Resulset:
🡪 It refers a logical set of data that are fetched from the database by executing an
SQL Query
and made available to the application program
WAYS TO EXTRACT DATA FROM SQL:
⮚ fetchone()
⮚ fetchall()
⮚ fetchmany()
fetchone()
🡪 This function returns one row from the result set in the form of a tuple
containing a record
🡪 This function returns the records pointed to by the pointer
🡪 When one record is fetched , the pointer moves to the next record of the
result set
🡪 When we use the fetchone() again, it will return the next record of the
result set
🡪 SYNTAX:
<data> = <cursor>.fetchone()
fetchall()
🡪 This function is used to return all the rows from the result set in the form of a
tuple
containing all the records
SYNTAX:
<data>=<cursor>.fetchall()
eg:
data=cur.fetchall()
print(data)
fetchmany()
🡪 This function is used to return ‘n’
number of
rows from the result set in the
form
of a tuple containing the records
🡪 SYNTAX
<data>=<cursor>.fetchmany(n)
eg: data=cur.fetchmany(2)
🡪 it will return 2 rows from
the result set
Parameterized
queries
⮚ These are the queries that are used to run queries which are based on some parameters
or values that we provide from outside.
⮚ To execute these queries, we need to form SQL Query strings that includes value of
parameter
⮚ METHODS FOR FORMING PARAMETERIZED QUERIES:
1. String templates with % formatting.
2. String template using format()
a=100 a=int(input("enter a number"))
b=20 b =int(input("enter a number"))
print("c=",a*b) print("c=",a*b)
print("d=",a+b) print("d=",a+b)
print("e=",a-b) print("e=",a-b)
print("f=",a/b) print("f=",a/b)
String templates with % formatting
#update with the table by user input
import mysql.connector as ms
x=ms.connect(host='localhost',user='root',passwd=‘abcd123',database='d
b9')
if x.is_connected():
ans='y'
while ans == 'y' or ans == 'Y':
cur = x.cursor()
e_id=int(input("Enter employee id"))
e_name=input("Enter employee name")
cur.execute("Insert into emp values(%s,'%s')"%(e_id,e_name))
x.commit()
ans=input("Do you want to insert more data?(y/n)")
cur.execute("select * from emp")
data=cur.fetchall()
for i in data:
print(i)
x.close()
# search for a specific an employee details
import mysql.connector as ms
x=ms.connect(host='localhost',user='root',passwd=‘abcd123',database='db9
')
if x.is_connected():
ans='y'
while ans == 'y' or ans == 'Y':
cur = x.cursor()
e_id=int(input("Enter employee id"))
cur.execute("select * from emp where e_id=%s"%(e_id))
data=cur.fetchone()
print(data)
ans=input("Do you want to view more records?(y/n)")
x.close()
