MySQL Programming with Python using MySQL Python Connector

Introduction

This article is about programming MySQL with Python using MySQL Python Connector. MySQL is a popular Open Source Database that finds its way in the software stack of almost all newer billion dollar companies. Let’s try to do some basic operations like Insert, Read, Update and Delete. For creating the database schema we have used MySQL workbench, even though it is fully possible to do the same programmatically using Python and the MySQL Python Connector.

For our article I have considered this below database schema. You can read more about designing this schema here.

Image_1

The illustrated code has been tested on Python 3.5 with MySQL Python Connector.

MySQL Programming With Python Using MySQL Python Connector

Install the latest version of MySQL Python Connector as shown below

Image_2

In line 1, we import mysql.connector module. In line 2 we also import the errorcode class which can be used to various errors related to Database query.

In line 3-4 we import faker module and create fake object. The reason for using this awesome module is I wanted to have some real world values generated for me instead of writing the values manually. You can read more about faker module here.

You need to declare and provide values for user, password, host, database and raise_on_warnings. Here, config is declared as a dictionary. You need to pass this config dictionary to connect method as an argument (see ** in the connect method). In line 21 we create cursor object which can be used to execute the Database query. We write a query to insert a row to the contactperson table. The query is stored in the variable addContactPerson which is passed to the execute method as an argument. In this program we will use an for loop to insert ten rows. Once we have performed our operation we need to close the cnx and cursor objects. 

import mysql.connector
from mysql.connector import errorcode
from faker import Factory
fake = Factory.create()

config = {
'user': 'root',
'password': 'mysql123',
'host': 'localhost',
'database': 'contactmanagerapplication',
'raise_on_warnings': True,
}

#This is the most efficient way of toggling between the values
import itertools
toggle = itertools.cycle(['Male', 'Female'])
#next(toggle)

try:
    cnx = mysql.connector.connect(**config)
    cursor = cnx.cursor()
    #Let's insert ten rows to contactperson table
    for person in range(1,11):
        contactPersonID = person
        firstName = fake.first_name_male() #generate fake first name
        lastName = fake.last_name_male() #generate fake middle name
        middleName = fake.last_name_male() #generate fake last name
        dateOfBirth = fake.date() #generate fake DOB
        contactPersonType = next(toggle)

        #In SQL statement the values need to be enclosed within double quotes
        #I'm providing the same by explicity specifying double quotes and removing it's special meaning
        addContactPerson = "INSERT INTO contactmanagerapplication.contactperson(" \
                           "ContactPersonID, FirstName, MiddleName, LastName, DateOfBirth," \
                           "ContactPersonType)VALUES({},\"{}\",\"{}\",\"{}\",\"{}\",\"{}\")".\
            format(contactPersonID,firstName,lastName,middleName,dateOfBirth,contactPersonType)
        cursor.execute(addContactPerson)
        cnx.commit()
except mysql.connector.Error as err:
    if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
        print("Something is wrong with your user name or password")
    elif err.errno == errorcode.ER_BAD_DB_ERROR:
        print("Database does not exist")
    else:
        print(err)
else:
    cursor.close()
    cnx.close()

In the below code we select and display all the rows from the contactperson table. Once we have composed and executed the select query, we have to use the cursor object along with for loop to display all the rows.

In line 19, we specify variable names that correspond to the attributes in the table contactperson. We then use these variable names to display the values. 

import mysql.connector
from mysql.connector import errorcode

config = {
'user': 'root',
'password': 'mysql123',
'host': 'localhost',
'database': 'contactmanagerapplication',
'raise_on_warnings': True,
}

try:
    cnx = mysql.connector.connect(**config)
    cursor = cnx.cursor()
    #Let's read all the rows in the table
    readContactPerson = "select * from contactmanagerapplication.contactperson"
    cursor.execute(readContactPerson)
    #specify the attributes that you want to display
    for (contactPersonID, firstName, lastName, middleName, dateOfBirth,contactPersonType ) in cursor:
        print("{}, {}, {}, {}, {}, {}".format(contactPersonID,firstName,middleName,lastName,dateOfBirth,contactPersonID))
    cnx.commit()
except mysql.connector.Error as err:
    if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
        print("Something is wrong with your user name or password")
    elif err.errno == errorcode.ER_BAD_DB_ERROR:
        print("Database does not exist")
    else:
        print(err)
else:
    cursor.close()
    cnx.close()</pre>

Below code asks the user to enter the contactPersonID number which needs to be deleted. Based on the value that the user has entered the related row gets deleted in the table. 

import mysql.connector
from mysql.connector import errorcode

config = {
'user': 'root',
'password': 'mysql123',
'host': 'localhost',
'database': 'contactmanagerapplication',
'raise_on_warnings': True,
}

try:
    cnx = mysql.connector.connect(**config)
    cursor = cnx.cursor()
    contactPersonID = input("Enter the ContactPersonID to be deleted")
    #Let's remove a row in contactperson table by specifying the contactPersonID
    deleteContactPerson = "DELETE FROM contactmanagerapplication.contactperson where ContactPersonID = {}".format(contactPersonID)
    cursor.execute(deleteContactPerson)
    cnx.commit()
except mysql.connector.Error as err:
    if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
        print("Something is wrong with your user name or password")
    elif err.errno == errorcode.ER_BAD_DB_ERROR:
        print("Database does not exist")
    else:
        print(err)
else:
    cursor.close()
    cnx.close()

Below code is used to update an attribute of a particular row based on user input. 

import mysql.connector
from mysql.connector import errorcode
from faker import Factory
fake = Factory.create()

config = {
'user': 'root',
'password': 'mysql123',
'host': 'localhost',
'database': 'contactmanagerapplication',
'raise_on_warnings': True,
}

try:
    cnx = mysql.connector.connect(**config)
    cursor = cnx.cursor()
    contactPersonID = input('Enter the Contact Person ID whose records needs to be updated')
    firstName = fake.first_name_male() #generate fake first name
    #Let's update a row in contactperson table by specifying the contactPersonID
    updateContactPerson = "UPDATE contactmanagerapplication.contactperson SET FirstName = \"{}\" WHERE ContactPersonID = {}".format(firstName, contactPersonID)
    cursor.execute(updateContactPerson)
    cnx.commit()
except mysql.connector.Error as err:
    if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
        print("Something is wrong with your user name or password")
    elif err.errno == errorcode.ER_BAD_DB_ERROR:
        print("Database does not exist")
    else:
        print(err)
else:
    cursor.close()
    cnx.close()

Conclusion

As you can see with few lines of Python code we can perform various database operations.

You can find the source code for all the programs discussed @

https://github.com/gowrishankarnath/MySqlProgrammingWithPython

Comments

comments powered by Disqus