CRUD operations in Python using MYSQL Connector

Python is a high level general purpose open source programming language. It is both object oriented and procedural. Like other programming languages, Python can also easily connect to different popular databases. In this article, you will learn to perform CRUD operations in Python using MySQL Connector. MySQL Connector Python is written in pure Python and compatible with Python 3. It is self-sufficient to execute database queries through Python.

Install MySQL Connector

Python MySQL installer available for different operating systems. You can download the MSI version from here -

Python MySQL Installer

The installer requires 'python.exe' in your system PATH, otherwise it will fail to install. So make sure to add the Python in your system environment.

You can also install MySQL Connector using pip command.

pip install mysql-connector

As, this connector is already installed in my system. It returns the following -

c:\python37\Scripts>pip install mysql-connector
Requirement already satisfied: mysql-connector in c:\python37\lib\site-packages (2.2.9)

This is also a way to check the successful installation of MySQL connector.

Python connecting to MySQL

First, we need to import the MySQL connector in Python file to connect to the database.

import MySQL.connector

Next, use the connect() constructor with all required parameters to create a connection to the MySQL server.

conn = MySQL.connector.connect(user='database_username', password='database_password', 
                    host='hostname', database='database_name')
conn.close()

Let's create a python file 'mscon.py' and check whether the database connection is successful or not -

import mysql.connector

conn = mysql.connector.connect(user='root', password='',
                              host='localhost',database='school')

if conn:
    print ("Connected Successfully")
else:
    print ("Connection Not Established")

Open 'cmd' and execute the above file -

c:\python37\Scripts>mscon.py
Connected Successfully

Python MySQL Create Table

In this section, you will learn to create MySQL table using Python. The mysql.connector.connect() method is used to connect to MySQL database. Next, we have used cursor() method to create a new cursor object. This cursor object is used further to execute the MySQL statement. At last, we have closed the cursor object and database connection object.

import mysql.connector

conn = mysql.connector.connect(user='root', password='',
                              host='localhost',database='school')

create_employee = """CREATE TABLE employee ( 
                    Id int(11) NOT NULL,
                    Name varchar(250) NOT NULL,
                    Dept varchar(50) NOT NULL,
                    Age int(11) NOT NULL,
                    Contact varchar(250) NOT NULL,
                    PRIMARY KEY (Id)) """
cursor = conn.cursor()
result = cursor.execute(create_employee)
print("Table created successfully")

if(conn.is_connected()):
    cursor.close()
    conn.close()
    print("MySQL connection is closed")

Here, we are executing the above file -

c:\python37\Scripts>employee.py
Table created successfully
MySQL connection is closed

Python MySQL Insert Table

This section demonstrates how to insert a new row into the 'employee' table. After executing the INSERT statement using execute() function you must have to commit it. The commit() method is used to commit the changes to the database.

import mysql.connector

conn = mysql.connector.connect(user='root', password='',
                              host='localhost',database='school')

insert_employee = """INSERT INTO employee (Id, Name, Dept, Age, Contact)
                    VALUES
                    (101,'Roza','Developer',30,9821212123),
                    (102,'Priska','Program Manager',32,6756098734)"""
cursor = conn.cursor()
result = cursor.execute(insert_employee)
conn.commit()
print("Data inserted successfully.")

if(conn.is_connected()):
    cursor.close()
    conn.close()
    print("MySQL connection is closed")

When we will execute the above program, it returns the following -

c:\python37\Scripts>insert_emp.py
Data inserted successfully.
MySQL connection is closed

Python MySQL Select Table

Here is the code to read data or select data from 'employee' table. After executing the SELECT statement using execute() function you can fetch data. We can use any of the fetchall(), fetchmany(), fetchone() method based on our need to return list data. In the given code, we have used the fetchall() method to fetch all records from the MySQL table.

import mysql.connector

conn = mysql.connector.connect(user='root', password='',
                              host='localhost',database='school')

select_employee = """SELECT * FROM employee"""
cursor = conn.cursor()
cursor.execute(select_employee)
result = cursor.fetchall()
print("Total number of rows selected : ", cursor.rowcount)

print("\nSelected data are -")

for row in result:
    print("Id : ",row[0])
    print("Name : ",row[1])
    print("Dept : ",row[2])
    print("Age : ",row[3])
    print("Contact : ",row[4])


if(conn.is_connected()):
    cursor.close()
    conn.close()
    print("MySQL connection is closed.")

The above code returns the following output -

c:\python37\Scripts>select_emp.py
Total number of rows selected :  4

Selected data are -
Id :  101
Name :  Roza
Dept :  Developer
Age :  30
Contact :  9821212123
Id :  102
Name :  Priska
Dept :  Program Manager
Age :  32
Contact :  6756098734
Id :  103
Name :  Loriya
Dept :  Software Testing
Age :  29
Contact :  2309678904
Id :  104
Name :  Somya
Dept :  Developer
Age :  33
Contact :  8976456789
MySQL connection is closed.

Python MySQL Update Record

Here is the code to update record in 'employee' table. We can update single row, multiple rows, single column and multiple columns using Python MySQL update operation. After executing the UPDATE statement using execute() function you must have to commit it using commit() method.

import mysql.connector

conn = mysql.connector.connect(user='root', password='',
                              host='localhost',database='school')
cursor = conn.cursor()

update_employee = """UPDATE employee SET Contact=9232894873 WHERE Id = 102"""
cursor.execute(update_employee)
conn.commit()
print("Data Updated Successfully.")

select_employee = """SELECT * FROM employee WHERE Id = 102"""
cursor.execute(select_employee)
result = cursor.fetchone()
print(result)

if(conn.is_connected()):
    cursor.close()
    conn.close()
    print("MySQL connection is closed.")

The above code returns the following output -

c:\python37\Scripts>update_emp.py
Data Updated Successfully.
(102, 'Priska', 'Program Manager', 32, '9232894873')
MySQL connection is closed.

Python MySQL Delete Record

These are the code to delete record from the 'employee' table. We prepare the DELETE statement query and then execute the DELETE query using cursor.execute() method to get numbers of rows affected. Once the record deleted successfully, we need to commit our changes to the database using commit() method.

import mysql.connector

conn = mysql.connector.connect(user='root', password='',
                              host='localhost',database='school')
cursor = conn.cursor()

delete_employee = """DELETE from employee WHERE Id = 103"""
cursor.execute(delete_employee)
conn.commit()
print("Data Deleted Successfully.")

select_employee = """SELECT * FROM employee"""
cursor.execute(select_employee)
result = cursor.fetchall()
print("Total number of rows selected : ", cursor.rowcount)

Output of the above code -

c:\python37\Scripts>delete_emp.py
Data Deleted Successfully.
Total number of rows selected :  3

Related Articles

Fibonacci Series Program in Python
Python File Handler - Create, Read, Write, Access, Lock File
Python convert XML to JSON
Python convert xml to dict
Python convert dict to xml
CRUD operations in Python using MYSQL Connector
Django create CSV file
How to get data from MySQL in Django View with Models
Windows commands to Create and Run first Django app
Django Send Mail on Contact form submission