Connection of MySQL and Python using PyMySql

Connection of MySQL and Python using PyMySql

1. Executing SQL query in Python Using PyMySql:

Execute the SQL query by using the Python Programming Language and pyMysql.
Here I have taken the Book database from the MySQL to display the result.

import pymysql
myConn=pymysql.connect(host=’localhost’,user=’root’,password=’root123′, db=’Db_py’)
print (“Connection Established!”)
sql = “Select B_id, B_Name, Price,Author from Book Where B_id = %s “
try :
            B_cur = myConn.cursor()
            B_cur.execute(sql, ( 3 ) )
            for B in B_cur:
                    print (” ———– “)
                    print(“Row: “, B)
                    print (“Book_No: “, B[0])
                    print (“Book_Name: “, B[1])
                    print (“Price: “, B[2] )
                    print(“Author:”, B[3] )

finally:
             myConn.close()

2. Executing Insert statement in Python Using PyMySql:

Execute the SQL insert statement by using the Python Programming Language and pyMysql. Here I have taken the Book database from the MySQL to display the result. In this program , I am trying to insert the data for the next row and Bood id will be automatically generated.

import pymysql
myConn=pymysql.connect(host=’localhost’,user=’root’,password=’root123′,db=’Db_py’)
print (“myConn Established!”)
try :
             B_cur = myConn.cursor()
             sql = “Select max(B_id) from Book “
             B_cur.execute(sql)
             B_id=0
             R1 = B_cur.fetchone()
             if R1 != None and R1[0] != None:
                         B_id = int(int(R1[0]) + 1)
             B_cur = myConn.cursor()
             sql = “Insert into Book (B_id, B_Name, price,author)  values (%s , %s, %s, %s) “
             print (“Insert Book: “, B_id)
             B_cur.execute(sql, (B_id,’Python’,’789′,’Rakesh’))
             myConn.commit()
finally:
             myConn.close()

3.Executing Update statement in Python Using PyMySql:

Execute the SQL update statement by using the Python Programming Language and pyMysql. Here I have taken the Book database from the MySQL to update the result. In this program , I am trying to update the data by taking input from the user:

import pymysql
myConn=pymysql.connect(host=’localhost’,user=’root’,password=’root123′, db=’Db_py’)
print (“myConn Established!”)
try :
              B_cur = myConn.cursor()
             B_id = int(input(“Enter the Book id:”))
             price= int(input(“Enter the Price:”))
             B_cur = myConn.cursor()
             sql = “Update Book set price = %s where B_id = %s”
             print (“Updated Book: “, B_id)
             B_cur.execute(sql, (price,B_id))
             myConn.commit()
             sql = “Select B_id, B_Name, Price,Author from Book Where B_id = %s “
             B_cur = myConn.cursor()
             B_cur.execute(sql, ( B_id ) )
             r1=B_cur.fetchone()
             print(r1)
finally:
myConn.close()

4. Delete Record from MySQL table by using Python:

import pymysql
myConn=pymysql.connect(host=’localhost’,user=’root’,password=’root123′,db=’Db_py’)
print (“myConn Established!”)
try :
            B_cur = myConn.cursor()
            B_id = int(input(“Enter the Book id:”))
            sql = “Delete from Book where B_id = %s”
            R1 = B_cur.execute(sql, ( B_id ) )
myConn.commit()
print (“Deleted! “, R1, ” rows”)
finally:
             myConn.close()  

Advertisements