Integrate MySQL with python by importing suitable modules

In this post, we are going to integrate MySQL with python by importing suitable modules. There are many different libraries available for python to accomplish this task. In this, we will work with mysql connector library to integrate MySQL with python.

So before we start working with python mysql connector,we need to install it on computer. So first install mysql connector on computer by using pip command. After that,we can write python script using MySQL connector library .It can help to connect python to MySQL databases. In order to do this,we need to import mysql.connector package in python program.

For this,we simply use following command

import mysql.connector

After that we need to establish connection to MySQL database using connect() function of mysql.connector package. Then we need to create cursor instance which is control structure of database connectivity. Database cursor help to get s the access of all the record of database and allow you traverse the result-set that is your set of retrieved records row by row.

So once we have created a cursor,we can execute SQL queries using execute () function with cursor object. In this way, we can integrate MySQL with python by importing mysql.connector library.

Steps to create a database connectivity python application are :

• Step 1 : Start python
• Step 2 : Import package required for database programming ( ex : import mysql.connector)
• Step 3 : Open a connection
• Step 4 : Create a cursor instance (ex: cursor = connectionobject.cursor() )
• Step 5 : Execute a Query ( ex. cursor.execute( query ) )
• Step 6 : Extract data from result set
• Step 7 : Clean up environment

Let us now see following code examples how these functions work. For the following code example, we shall be connecting to database student_dbl,table student that has following data in it

1] Write a python program that display first 8 rows fetched from student table of MySQl database student_dbl

Output

Also read : Insert multiple rows with single INSERT statement in MySQL

2] Write a python database connectivity program that deletes record from student table of database that have name = Meena

import pandas as pd
import mysql.connector as sqltor
conn=sqltor.connect(host="localhost",user="root",passwd="123456",database="student_dbl")
if conn.is_connected():
    print("Successfully connected")
    cursor = conn.cursor()
    cursor.execute("delete from student where name = 'Meena'")
    data = cursor.fetchmany(10)
    count = cursor.rowcount
    print("Total no of records deleted from database :",count)
    conn.commit()
    conn.close()
else:
    print("MYSqL connection problem")

Output