× Python Introduction What is Python Python Features Python History Python Applications Python Install Python Path Python Example Execute Python Keywords Constant Variable Statements & Comments Python I/O and Import Operators UnaryBinaryTernary Unary Operators Unary Minus Binary Operators Arithmetic Operators Assignment Operators Relational Operators Logicaloperators Bitwise Operator Ternary Operators Control Statements in Python conditonal Statements IF if else Else If Nested if Switch For loop Nested For Loop While Loop Nested while Loop Unconditonal Statemets Continue Break Pass FUNCTIONS Python Function Function Argument Python Recursion Anonymous Function Python Modules NATIVE DATATYPES Python List Python Numbers Python Tuple Python String Python Set Python Dictionary OOPS PRINCIPALS Encapsulation Class Variable Method Object Or Instance CreationMethod Calling OOPS Syntax And Explanation DATA ABSTRACTION Constructor Inheritance 1.Single or simple Inheritance 2.Multilevel Inheritance 3.Hierarchical Inheritance 4.Multiple Inheritance 5.Hybrid Inheritance Operator Overloading File Operation Python Directory Python Exception Python - Multithreading Python - Database Access Python - CGI Python - Reg Exp Python - Date Python - XML Processing Python - GUI
  • iconpython Online Training In Andhra Pradesh and Telangana
  • icon9010519704

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

Key Points

Image
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()
Output: