Python is an open-source general-purpose programming language and MySQL is also open-source cross-platform RDBMS (Relational database management system) Database.

In this article, we will learn how to connect to MySQL database and perform CRUD operations using Python.

We are assuming that you already have MySQL database and python installed in your machine and have a basic understanding of SQL.

First, you need to create a database “TestDB”

Install MySQL connector
To install MySQL connector we need to run following command in our in terminal

$ pip3.6  install mysql-connector

Make a connection

mysql-connect provide the connect class to make a connection with MySql database

conn = mysql.connector.connect(user='******', password='*****',
                  host='127.0.0.1', database='mydatabase',port:3306)

User: username of the database
Password: password of that user account
Host: Servername/Ipaddress of server
Database: A database which we want to connect
Port: port number on which database server is accessible.

Below is the working example of to connect MySQL with python

# importing Packages
from mysql.connector import MySQLConnection, Error

conn = mysql.connector.connect(user='******', password='*****',
                  host='127.0.0.1', database='mydatabase')

try:
  print('Connecting to MySQL database...')
    if conn.is_connected():
      ### Process Databse operations
      else:
          print('connection failed.')
except Error as error:
  print(error)
finally:
  conn.close()
  print('Connection closed.')

Cursors

Cursor plays an important part when we perform Transaction in the database. Cursor initialize an object which connects with MySql server to execute SQL Query.
A cursor object can be initialized by the following code:

conn = mysql.connector.connect(database='world')
cursor = conn.cursor()

In our below CURD operation, we will learn how we use cursors in python.

Create a table

In the below code we are creating a table Employee with fields like First_name, Last_name, Age etc.

Below is the sample example for creating the table:

# importing Packages
from mysql.connector import MySQLConnection, Error

conn = mysql.connector.connect(user='******', password='*****',
host='127.0.0.1', database='mydatabase')

try:
print('Connecting to MySQL database...')
if conn.is_connected():
cursor = conn.cursor()
sql = """CREATE TABLE EMPLOYEE (
FIRST_NAME CHAR(20) NOT NULL,
LAST_NAME CHAR(20),
AGE INT, SEX CHAR(1),
INCOME FLOAT )""";
cursor.execute(sql)
conn.commit()
else:
print('connection failed.')
except Error as error:
print(error)
finally:
cursor.close()
conn.close()
print('Connection closed.')

Insert data:

In the below example we are executing a SQL statement to  insert values in Employee table

# importing Packages
from mysql.connector import MySQLConnection, Error

conn = mysql.connector.connect(user='******', password='*****',
host='127.0.0.1', database='mydatabase')

try:
print('Connecting to MySQL database...')
if conn.is_connected():
cursor = conn.cursor()
sql = """INSERT INTO EMPLOYEE(FIRST_NAME,
LAST_NAME, AGE, SEX, INCOME)
VALUES ('Mac', 'Mohan', 20, 'M', 2000)"""
cursor.execute(sql)
conn.commit()
else:
print('connection failed.')
except Error as error:
conn.rollback()
finally:
cursor.close()
conn.close()
print('Connection closed.')

Read data from the database

In the below code we are using pandas to read data from MySQL database.

To fetch data we can use two main methods
1. fetchone()
2. fetchall()

fetchone read the next record from result set while fetchall reads the all rows from a result set
Below is the example

Example:

# importing Packages
from mysql.connector import MySQLConnection, Error

conn = mysql.connector.connect(user='******', password='*****',
host='127.0.0.1', database='mydatabase')

try:
print('Connecting to MySQL database...')
if conn.is_connected():
cursor = conn.cursor()
sql = "SELECT * FROM EMPLOYEE \
WHERE INCOME > '%d'" % (1000)
results = cursor.fetchall()
for row in results:
fname = row[0]
lname = row[1]
age = row[2]
sex = row[3]
income = row[4]
# Now print fetched result
print("fname= {0},lname={1},age={2},sex={3},income={4}".format(fname, lname, age, sex, income ))
else:
print('connection failed.')
except Error as error:
conn.rollback()
finally:
#cursor.close()
conn.close()
print('Connection closed.')

Update data in the database
To modify an existing record in the database we need the update query.
In the below example we are using the update Query to modify the record

Make a cursor Object

# importing Packages
from mysql.connector import MySQLConnection, Error

conn = mysql.connector.connect(user='******', password='*****',
host='127.0.0.1', database='mydatabase')

try:
print('Connecting to MySQL database...')
if conn.is_connected():
cursor = conn.cursor()
sql = "UPDATE EMPLOYEE SET AGE = AGE + 1  WHERE SEX = '%c'" % ('M')
cursor.execute(sql)
conn.commit()

else:
print('connection failed.')
except Error as error:
conn.rollback()
finally:
#cursor.close()
conn.close()
print('Connection closed.')

Delete record from the database

Delete operation is required to remove the record from the database.
In the below example we are removing the record from the database

# importing Packages
from mysql.connector import MySQLConnection, Error

conn = mysql.connector.connect(user='******', password='*****',
host='127.0.0.1', database='mydatabase')

try:
print('Connecting to MySQL database...')
if conn.is_connected():
cursor = conn.cursor()
sql = "DELETE FROM EMPLOYEE WHERE AGE > '%d'" % (20)
cursor.execute(sql)
conn.commit()

else:
print('connection failed.')
except Error as error:
conn.rollback()
finally:
#cursor.close()
conn.close()
print('Connection closed.')

Transaction in SQL

Now understand how the transaction will work, we have already implemented the transaction in the above example let understand it again

try:
print('Connecting to MySQL database...')
if conn.is_connected():
cursor = conn.cursor()
sql = """INSERT INTO EMPLOYEE(FIRST_NAME,
LAST_NAME, AGE, SEX, INCOME)
VALUES ('Mac', 'Mohan', 20, 'M', 2000)"""
cursor.execute(sql)
conn.commit()
else:
print('connection failed.')
except Error as error:
conn.rollback()
finally:
cursor.close()
conn.close()
print('Connection closed.')

In the above code block, we are using a commit and rollback statement

Commit by using commit statement are just sending the signal to the database to do the final changes.

Rollback if we face any issue with the transaction then we rollback all the changes by using the rollback command

Exception:

MySql provides mysql.connector.Error class to handle database errors.
This class inherits the python’s errors. Error class

Below is the example:

try:
conn = mysql.connector.connect(database = "baddb")
except mysql.connector.Error as e:

In the above example object e has the following property

e.errno         # error number
e.sqlstate   # SQLSTATE value
e.msg          # error message