Basically the process of transfer data between python programs and MySQL database is known as Python Database Connectivity. There few steps you have to follow to perform Python Database Connectivity. These steps are as follow:

1.Import the required packages

2.Establish a connection

3.Execute SQL command

4.Perform operations as per the requirements

Import the required packages

To perform the Python MySQL Database Connectivity you need to install mysql-connector-python or pymysql package using pip command. First of all check it is installed or not using pip list command.

Install python 3.12 version

open cmd and give command

pip install mysql connector python

After installation just write the import statement to import the package in python code.

import mysql.connector as msql

Establish a connection

To establish a connection you need to create a connection object in Python. Take a variable as a connection object and use connect() function with MySQL database specification like host name, username, passoword or passwd and database itself. For example cn. Observe the code:

import mysql.connector as msql

cn=msql.connect(host='localhost',user='root',passwd='root',database=' jnvchandigarh') ')

Execute SQL command and fetch rows

The next step after the successful connection is to write SQL command and fetch rows. The SQL commands are used to perform DML operations and fetch rows read data from table.

You have to create a cursor object for executing SQL command and fetch rows. Cursor object is a special kind of structure that processes the data row by row in database. You can create cursor object in the following manner.

c=con.cursor()

Performing DML operations (insert, update and delete)

To perform the DML operations like insert, update or delete follow these steps:

Create a cursor object

Write command as parameters for execute() function

Use commit() function to save the changes and reflect the data in the table.

insert command

import mysql.connector as msql

cn=msql.connect(host='localhost',user='root',passwd=‘ ',database=‘jnvchandigarh')

cur=cn.cursor()

cur.execute("insert into students values(1111,'Asmita',78.50,'B1')

cn.commit()

Select Command

As you know the select command is used retrieve records from the database. The result is available in the resultset or dataset. You can store the select the command in cursor object in python. Then for resultset you can use the fetch…() function. These are:

1.fetchall(): It will retrieve all data from a database table in form of record or tuple or a row.

2.fetchone(): It will retrieve one record from the resultset as a tuple or a list. It returns the records in a specific order like first record, the next time next record and so on. If records are not available then it will return None.

3.fetchmany(): It will retrieve n number of records from the database. If records are not available then it will return an empty tuple.

4.rowcount: It is one of the properties of cursor object that return number of rows fetched from the cursor object.

Using fetchall()

import mysql.connector as msql

cn=msql.connect(host='localhost',user='root',passwd=‘ ',database=‘jnvchandigarh')

cur=cn.cursor()

cur.execute("select * from students")

d=cursor.fetchall()

for r in d:

   print(r) 

Database Connectivity commands:

import mysql.connector as sql mydb=sql.connect(host="localhost",user="root",passwd="") mycursor=mydb.cursor()

qry=“show databases"

mycursor.execute(qry)

SELECT QUERY

qry="select * from student" mycursor.execute() records=mycursor.fetchall()

 for i in records:

print(i)

INSER QUERY

qry="insert into student values(2,'VIJAY',17,'CHANDIGARH') "

 mycursor.execute(qry)

mydb.commit() 

print(mycursor.rowcount,"record inserted")

UPDATE QUERY

qry="update student set age=25 where rollno=2 " 

mycursor.execute(qry)

mydb.commit() 

print(mycursor.rowcount,"Record updated")

DELETE QUERY

qry="delete from student where name='vinay' "

 mycursor.execute()

mydb.commit() 

print(mycursor.rowcount,"Record deleted")

Parametrize Queries

INSERT QUERY

rollno=int(input("enter your rollno")) 

nm=input("enter name of student") 

age=int(input("enter age")) 

cty=input("enter your city")

 data=(rollno,nm,age,cty)

qry="insert into student values(%s,%s,%s,%s)“

mycursor.execute(qry,data)

mydb.commit()

print(mycursor.rowcount,"Record updated")

UPDATE QUERY

roll=int(input("enter ROLLNO of student")) 

age=int(input("enter new age")) data=(age,roll)

qry="update student set age=%s where rollno=%s"

 mycursor.execute(qry,data)

mydb.commit() 

print(mycursor.rowcount,"Record updated")

DELETE QUERY

roll=input("enter rollno of student") data=(roll,)

qry="delete from student where rollno=%s" 

mycursor.execute(qry,data)

mydb.commit() 

print(mycursor.rowcount,"Record deleted")

SELECT QUERY

roll=input("enter rollno of student") data=(roll,)

qry="SELECT * FROM student WHERE rollno=%s" 

mycursor.execute(qry,data) 

records=mycursor.fetchall()

for i in records: 

print(i)

mydb.commit()

 print(mycursor.rowcount,"Records displayed")