python Online Training In Andhra Pradesh and Telangana 
9010519704
Opening Hours :7AM to 9PM
Python Database Connectivity
Steps to conect python to mysql database
Step 1:import pymysql
Step 2:
Open database connection
Step 3:
prepare a cursor object using cursor() method
Step 4:
execute SQL query using execute() method.
Step 5:
Fetch a single row using fetchone() method.
Step 6:
disconnect from server
Database Connectivity Program
How To Connect The MYSQL Database
#!C:\Users\Varshini\AppData\Local\Programs\Python\Python37-32
import pymysql
# Open database connection
db = pymysql.connect(host="localhost",user="root",password = "",db="Rain",)
# prepare a cursor object using cursor() method
cursor = db.cursor()
# execute SQL query using execute() method.
cursor.execute("SELECT VERSION()")
# Fetch a single row using fetchone() method.
output = cursor.fetchone()
print ("Database version : %s " % output)
# disconnect from server
db.close()
Output:
Create Table Using Jdbc
How To Create Table Using MySql Database
#!C:\Users\Varshini\AppData\Local\Programs\Python\Python37-32
import pymysql
# Open database connection
db = pymysql.connect(host="localhost",user="root",password = "",db="Rain",)
# prepare a cursor object using cursor() method
cursor = db.cursor()
# Create table as per requirement
sql = """CREATE TABLE STUDENT (
FIRST_NAME CHAR(20) NOT NULL,
LAST_NAME CHAR(20),
AGE INT,
MARKS FLOAT )"""
cursor.execute(sql)
# disconnect from server
db.close()
Output:
Insert Data into the table Using Jdbc
How To Insert data into the Table Using MySql Database
#!C:\Users\Varshini\AppData\Local\Programs\Python\Python37-32
import pymysql
# Open database connection
db = pymysql.connect(host="localhost",user="root",password = "",db="Rain",)
# prepare a cursor object using cursor() method
cursor = db.cursor()
# Prepare SQL query to INSERT a record into the database.
sql = """INSERT INTO STUDENT(FIRST_NAME,
LAST_NAME, AGE, MARKS)
VALUES ('Varshini', 'm', 24, 24)"""
try:
# Execute the SQL command
cursor.execute(sql)
# Commit your changes in the database
db.commit()
except:
# Rollback in case there is any error
db.rollback()
# disconnect from server
db.close()
Output:
How To Insert data into the Table Using MySql Database
How To Insert data into the Table Using MySql Database
#!C:\Users\Varshini\AppData\Local\Programs\Python\Python37-32
import pymysql
# Open database connection
db = pymysql.connect(host="localhost",user="root",password = "",db="Rain",)
# prepare a cursor object using cursor() method
cursor = db.cursor()
# Prepare SQL query to INSERT a record into the database.
sql = "INSERT INTO STUDENT(FIRST_NAME, \
LAST_NAME, AGE, Marks) \
VALUES ('%s', '%s', '%d', '%d' )" % \
('Varshini', 'M', 24, 24)
try:
# Execute the SQL command
cursor.execute(sql)
# Commit your changes in the database
db.commit()
except:
# Rollback in case there is any error
db.rollback()
# disconnect from server
db.close()
Output:
How To Insert data into the Table Using MySql Database
How To Insert data into the Table Using MySql Database- User input
import pymysql
# Open database connection
db = pymysql.connect(host="localhost",user="root",password = "",db="Rain",)
# prepare a cursor object using cursor() method
cursor = db.cursor()
FIRST_NAME=input("Enter ffirstname")
LAST_NAME=input("Enter lastname")
AGE=int(input("enter age"))
Marks=int(input("Enter marks"))
sql="INSERT INTO student(FIRST_NAME, LAST_NAME, AGE, Marks) \
VALUES ('%s', '%s', '%d', '%d' )" % \
(FIRST_NAME, LAST_NAME, AGE,Marks)
try:
cursor.execute(sql)
db.commit()
except Exception as e:
db.rollback()
# disconnect from server
db.close()
Output:
Retrive The Data From The Table
Retrive The Data From The Table
#!C:\Users\Varshini\AppData\Local\Programs\Python\Python37-32
import pymysql
# Open database connection
db = pymysql.connect(host="localhost",user="root",password = "",db="Rain",)
# prepare a cursor object using cursor() method
cursor = db.cursor()
sql = "SELECT * FROM STUDENT"
try:
# Execute the SQL command
cursor.execute(sql)
# Fetch all the rows in a list of lists.
results = cursor.fetchall()
for row in results:
fname = row[0]
lname = row[1]
age = row[2]
marks = row[3]
# Now print fetched result
print ("fname=%s,lname=%s,age=%d,income=%d" % \
(fname, lname, age, marks ))
except Exception as e:
print ("unable to fecth data",e)
# disconnect from server
db.close()
Output:
Retrive The Data From The Table (Based on Student id)
Retrive The Data From The Table (Based on Student id)
#!C:\Users\Varshini\AppData\Local\Programs\Python\Python37-32
import pymysql
# Open database connection
db = pymysql.connect(host="localhost",user="root",password = "",db="Rain",)
# prepare a cursor object using cursor() method
cursor = db.cursor()
id=int(input("Enter id"))
sql = "SELECT * FROM STUDENT where id=%d " %(id)
try:
# Execute the SQL command
cursor.execute(sql)
# Fetch all the rows in a list of lists.
results = cursor.fetchall()
for row in results:
fname = row[0]
lname = row[1]
age = row[2]
marks = row[3]
# Now print fetched result
print ("fname=%s,lname=%s,age=%d,income=%d" % \
(fname, lname, age, marks ))
except Exception as e:
print ("unable to fecth data",e)
# disconnect from server
db.close()
Output:
Update The Data From The Table Using Jdbc
How To Update data from the Table Using MySql Database
#!C:\Users\Varshini\AppData\Local\Programs\Python\Python37-32
import pymysql
# Open database connection
db = pymysql.connect(host="localhost",user="root",password = "",db="Rain",)
# prepare a cursor object using cursor() method
cursor = db.cursor()
# Prepare SQL query to UPDATE required records
sql = "UPDATE STUDENT SET MARKS = MARKS + 10 WHERE Marks < '%d'" % (50)
try:
# Execute the SQL command
cursor.execute(sql)
# Commit your changes in the database
db.commit()
except:
# Rollback in case there is any error
db.rollback()
# disconnect from server
db.close()
Output:
Delete Data From The Table
Delete Data From The Table
#!C:\Users\Varshini\AppData\Local\Programs\Python\Python37-32
import pymysql
# Open database connection
db = pymysql.connect(host="localhost",user="root",password = "",db="Rain",)
# prepare a cursor object using cursor() method
cursor = db.cursor()
# Prepare SQL query to DELETE required records
sql = "DELETE FROM Student WHERE AGE > '%d'" % (20)
try:
# Execute the SQL command
cursor.execute(sql)
# Commit your changes in the database
db.commit()
except:
# Rollback in case there is any error
db.rollback()
# disconnect from server
db.close()