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

Live Session on Python Programming Language By Rakesh Roshan

Live Session on Python Programming Language By Rakesh Roshan. Here you will get the tutorial of Python Programming Language from scratch way . You can also ask questions related to python. In this video, I covered the below topics:

  1. Basics of Python
  2. Variables
  3. Type of Data Types
  4. Numbers
  5. String
  6. List
  7. Tuple
  8. Dictionary