DATABASE CONNECTIVITY FROM PYTHON TO
MYSQL
Database Access
• The Python standard for database interfaces is the Python DB-
API.
• The DB-API is a specification for a common interface to
relational databases.
• Relational databases are the most widely used type of
database, storing information as tables containing a number
of rows.
• Python Database API supports a wide range of database
servers such as: GadFly, mSQL, MySQL, PostgreSQL, Microsoft
SQL Server, Informix, Interbase, Oracle, Sybase, SQLite
Database Access
• The DB API provides a minimal standard for
working with databases using Python structures
and syntax wherever possible. The API includes
the following support: Importing the API
module.
• Acquiring a connection with the database.
• Issuing SQL statements and stored procedures.
• Closing the connection
INSTALLATION OF MYSQL CONNECTOR
• OPEN CMD PROMT WITH RUN AS
ADMINISTRATOR
• WRITE THE FOLLOWING COMMAND FOR
INSTALLING THE MYSQL CONNECTOR
C:\>python –m pip install mysql-connector
(Internet Connection is required for installation of
the Mysql Connector Module)
Importing and Using Mysql Connector
Module in Python Programme
Open the Python IDLE and Create New File
• Type the following statements
import mysql.connector
mydb=mysql.connector.connect(host="localhost",user="root",passwd="root",da
tabase="test")
mycursor=mydb.cursor()
mycursor.execute("show tables")
for x in mycursor:
print(x)
Save and run the Program you will get the List of tables in Database test
Create table Command
import mysql.connector
mydb=mysql.connector.connect(host="localhost",user=
"root",passwd="root",database="test")
mycursor=mydb.cursor()
mycursor.execute("Create table if not exists student
(RN int, Name Varchar(20), DOB date, Marks float)")
print("table Successfully Created")
Alter Table
import mysql.connector
mydb=mysql.connector.connect(host="localhost",user=
"root",passwd="root",database="test")
mycursor=mydb.cursor()
mycursor.execute("Alter table student add primary
key(RN)")
print("table Successfully Altered")
Inserting Row with Variables
import mysql.connector
mydb=mysql.connector.connect(host="localhost",user="root",passwd="root",database="test")
mycursor=mydb.cursor()
mycursor.execute("Desc Student")
data=mycursor.fetchall()
for r in data:
print(r)
rol=int(input("Enter the Roll number of Student"))
nam=input("Enter Name of Student")
DOB=input("Enter date of birth in YYYY-MM-DD format")
mark=float(input("Enter Marks of Student"))
mycursor.execute("insert into student values(%d,'%s','%s',%f)" %(rol,nam,DOB,mark))
mydb.commit()
print("Row Successfully Inserted")
(%d format is for integer , %s for string , %f for floating pointnumbers)
(Loop can be used if more than one rows are to be inserted)
Updating Column Data
import mysql.connector
#import pymysql
mydb=mysql.connector.connect(host="localhost",user="root",passwd="root",database="test")
#mydb=pymysql.connect("localhost","root","root","test")
mycursor=mydb.cursor()
#mycursor.execute("insert into empl values(4,'Raman',10,'1234565432')")
mycursor.execute("Select * from Empl")
records=mycursor.fetchall()
for x in records:
print(x)
en=int(input("Enter the Employee number you want to update the record"))
coltoupdate=int(input("Enter the Column Name to be Updated.\n 1 for Employee Name \n 2 For
Department \n 3 For Phone Number"))
if coltoupdate==1:
newname=input("Enter Correct Name of Employee")
mycursor.execute("update empl set ename='%s' where eno=%d" %(newname,en))
mydb.commit()
elif coltoupdate==2:
newdept=int(input("Enter New Departmen number"))
mycursor.execute("update empl set deptno=%d where eno=%d" %(newdept,en))
mydb.commit()
Deleting Record
import mysql.connector
#import pymysql
mydb=mysql.connector.connect(host="localhost",user="root",passwd="root",database
="test")
#mydb=pymysql.connect("localhost","root","root","test")
mycursor=mydb.cursor()
#mycursor.execute("insert into empl values(4,'Raman',10,'1234565432')")
mycursor.execute("Select * from Empl")
records=mycursor.fetchall()
for x in records:
print(x)
en=int(input("Enter the Employee number you want to delete the record"))
mycursor.execute("Delete from empl where eno=%d" %en)
mydb.commit()
mydb.close()
