Python With MySQL Examples Using PyMysql

1. Python3 Code To Get The DataBase Version Of Mysql Using PyMYSQL Api.

import pymysql
#127.0.0.1   or local ipaddress or localhost
conn = pymysql.connect(host='localhost', port=3306, user='root',passwd='root',db='emp')
cursor = conn.cursor()
cursor.execute("SELECT VERSION()")
data = cursor.fetchone()
print ("Database version : %s " % data)
conn.close()

2. Python3 Code To Create Table In Mysql DB Using PyMYSQL Api.

import pymysql

db = pymysql.connect(host='localhost', port=3306, user='root',passwd='root', db='ecity')
cursor = db.cursor()
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")

sql = """CREATE TABLE EMPLOYEE (
   FIRST_NAME  CHAR(20) NOT NULL,
   LAST_NAME  CHAR(20),
   AGE INT,  
   SEX CHAR(1),
   INCOME FLOAT )"""

cursor.execute(sql)

db.close()

3. Python3 Code To Insert The Data Into Table In Mysql DB Using PyMYSQL Api.

import pymysql
conn = pymysql.connect(host='localhost', port=3306, user='root', 
passwd='root', db='demo')
cursor = conn.cursor()
sql = """INSERT INTO EMPLOYEE(FIRST_NAME,
   LAST_NAME, AGE, SEX, INCOME)
   VALUES ('Mac', 'Mohan', 20, 'M', 2000)"""
try:
   cursor.execute(sql)
   conn.commit()
except:
    conn.rollback()
conn.close()

4. Python3 Code To Insert The Data Dynamically Into Table In Mysql DB Using PyMYSQL Api.

from __future__ import print_function
import pymysql

conn = pymysql.connect(host='localhost', port=3306, user='root', passwd='root', db='adv')


cursor = conn.cursor()

name=input("enter a name")
lname=input("enter last name")
age=int(input("enter a age"))
s=input("enter sex")
income=int(input("enter income"))

sql = "INSERT INTO EMPLOYEE(FIRST_NAME, \
   LAST_NAME, AGE, SEX, INCOME) \
   VALUES ('%s', '%s', '%d', '%c', '%d' )" % \
   (name, lname, age, s, income)
try:

   cursor.execute(sql)

   conn.commit()
except:

    conn.rollback()


conn.close()

5. Python3 Code To Delete The Data From Mysql Table Using PyMYSQL Api.

from __future__ import print_function
import pymysql

conn = pymysql.connect(host='localhost', port=3306, user='root', passwd='root', db='adv')


cursor = conn.cursor()

sql = "DELETE FROM EMPLOYEE WHERE AGE > '%d'" % (20)
try:

   cursor.execute(sql)

   conn.commit()
except:

    conn.rollback()


conn.close()

6. Python3 Code To Update The Data In Mysql Table Using PyMYSQL Api.

from __future__ import print_function
import pymysql

conn = pymysql.connect(host='localhost', port=3306, user='root', passwd='root', db='adv')


cursor = conn.cursor()

sql = "UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = '%c'" % ('M')
try:

   cursor.execute(sql)

   conn.commit()
except:

    conn.rollback()


conn.close()

7. Python3 Code To Fetch All Records From Mysql Table Using PyMYSQL Api.

from __future__ import print_function
import pymysql

conn = pymysql.connect(host='localhost', port=3306, user='root', passwd='root', db='ecity')


cursor = conn.cursor()

sql = "SELECT * FROM EMPLOYEE WHERE INCOME > '%d'" % (1000)

try:

   cursor.execute(sql)
   results=cursor.fetchall()
   for row in results:
      fname = row[0]
      lname = row[1]
      age = row[2]
      sex = row[3]
      income = row[4]

      print ("fname = %s,lname = %s,age = %d,sex = %s,income = %d" %(fname, lname, age, sex, income ))
except:

    conn.rollback()


conn.close()