[Go to site: main page, start]
Scribd
Upload
22 views9 pages

Connect Python to MySQL with PyMySQL

This document provides steps to interface Python with MySQL. It discusses two libraries, MySQL.connector and pymysql, that allow connecting Python to MySQL. It then outlines the basic steps to create a database connection from Python: import the library, open a connection, create a cursor, execute queries, extract result sets, and clean up the environment. Specific code examples are given to demonstrate connecting, retrieving data, inserting new records, and updating existing records in a MySQL database from Python.

Uploaded by

anubhav.srivastav0454
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
22 views9 pages

Connect Python to MySQL with PyMySQL

This document provides steps to interface Python with MySQL. It discusses two libraries, MySQL.connector and pymysql, that allow connecting Python to MySQL. It then outlines the basic steps to create a database connection from Python: import the library, open a connection, create a cursor, execute queries, extract result sets, and clean up the environment. Specific code examples are given to demonstrate connecting, retrieving data, inserting new records, and updating existing records in a MySQL database from Python.

Uploaded by

anubhav.srivastav0454
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Interface Python with

MySQL
CONNECTING TO MYSQL FROM
PYTHON
• [Link] should be installed
• After Installing this you have to include this
Library in your Python code.
• You can also use pymysql Library which is
purely provided by Python for connecting with
MySQL whereas [Link] is provided
by Oracle which owns MySQL.
Steps for Creating Database
Connectivity Applications
• Step 1 : Start Python
• Step 2 : Import the packages 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 : Clean up the environment.
• You can write –
import [Link]
or
import [Link] as sql
To include the package before starting connection. The 2nd
method can be used to provide a short name for package.

 To open a connection to MySQL database we can use the


connect() function of [Link] which requires 4
parameters –
<Connection-Object>=[Link](host=<host-
name>,user=<username>,passwd=<password>,database=<da
tabase>)
e.g. import [Link] as sql

mycon=[Link](host=“localhost”,user=“root”,passwd
=“MyPass”,database=“GNHSS”)
• After Connecting we have to create a CURSOR
instance.
• A Database Cursor is a special control structure that
facilitates the row by row processing of records in
the resultset i.e. the set of records retrieved as per
query.
cursor=[Link]()
• SQL query can be executed by –
[Link](“SELECT * FROM data”)
• The above code will execute the query and store
the resultset in a cursor object, which can be
extracted using any one of the following functions -
1. fetchall() – It will return all the records retrieved
as query in a tuple form
2. fetchone() – It will return one record at a time.
3. fetchmany(n) – It will return required no. of rows
from the resultset.
4. [Link] – is a property of cursor object
that returns the number of rows retrieved from the
cursor so far.
SAMPLE PROGRAM TO SHOW CONNECTION WITH
MYSQL –
Q. Program to display Records from table :-
import [Link] as sql
mycon=[Link](host="localhost",user="root",passwd="Giri9
753@@",database="gnhss")
if mycon.is_connected() :
print("CONNECTED")
cursor=[Link]()
[Link]("SELECT * FROM STUDENT")
data=[Link]()
for row in data :
print(row)
[Link]()

Note: is_connected() is used to check whether connection has


been established or not.
close() is used to clean up the environment.
INSERTING VALUES
import [Link] as sql
mycon=[Link](host="localhost",user="root",passwd="Giri9753@@",data
base="gnhss")
if mycon.is_connected() :
Roll=int(input("Enter Roll No."))
Name=input("Enter Name")
Phone=input("Enter Phone")
Class=input("Enter Class")
Sec=input("Enter Section")
DOB=input("Enter Date of Birth")
st="INSERT INTO STUDENT VALUES
({},'{}','{}','{}','{}','{}')".format(Roll,Name,Phone,Class,Sec,DOB)
cursor=[Link]()
[Link](st)
[Link]()
print("ONE RECORD INSERTED SUCCESSFULLY")
[Link]()
UPDATE VALUES
import [Link] as sql
mycon=[Link](host="localhost",user="root",passwd="Giri9753@
@",database="gnhss")
if mycon.is_connected() :
Roll=int(input("Enter Roll No. to UPDATE"))
Phone=input("Enter New Phone Number")
st="UPDATE STUDENT SET PHONE={} WHERE
ROLL={}".format(Phone,Roll)
cursor=[Link]()
[Link](st)
[Link]()
print("ONE RECORD UPDATED SUCCESSFULLY")
[Link]()

You might also like