Accessing MySQL from Python

In this blog, we learn how to connect to MySQL using mysql.connector package, which is an implementation of DB-API to access MySQL Database.

Getting MySQL ready
Connecting to MySQL Database
Executing Queries
Inserting Rows
Deleting Employee
Updating Employee
Getting Summaries
Update table from data in a file
Converting rows to JSON

Getting MySQL ready

I assume you have installed MySQL community edition (or some other edition).

Connect to MySQL server using root account (or some other account). By default MySQL community edition runs on localhost at port number 3306.

We use EMPLOYEES table in HR database. It is okay to use another database. In case you want to create database HR, use the following command either in MySQL Workbench or MySQL Shell.

create database hr

Alternatively, in MySQL Workdbench you can use Create a new schema tool to create a database in the connected server.

Here is the command to create EMPLOYEES table. Execute it either in MySQL Workbench or MySQL Shell.

CREATE TABLE employees (
    id       INTEGER      PRIMARY KEY AUTO_INCREMENT,
    fullname VARCHAR (30) NOT NULL,
    job      VARCHAR (20),
    salary   INTEGER
);

The following INSERT commands are used to load some data into EMPLOYEES table.

insert into employees(fullname,job,salary)  values('Andy Roberts','Programmer',1500000);
insert into employees(fullname,job,salary)  values('Scott Guthrie','Programmer',2000000);
insert into employees(fullname,job,salary)  values('Kevin Loney','DBA',2500000);
insert into employees(fullname,job,salary)  values('Joe Stagner','Team Lead',4500000);

Python Connector

Connector to connect to MySQL is automatically installed when you install MySQL using Windows installer. In case, you need to install it explicitly, use pip as follows:

>pip install mysql-connector-python

For more information about how to install Connector, please visit https://dev.mysql.com/doc/dev/connector-python/8.0/installation.html

Connecting to MySQL Database

We want to store connection details in a JSON file (connection.json) and read those details from a Python program.

{
    "host": "localhost",
    "port": 3306,
    "database": "hr",
    "user": "root",
    "password": "mysql"
}

The following program will connect to MySQL running on localhost at port 3306 and uses HR Database using username root and password mysql.

In case you are trying to use a different account or a different database then feel free to change the details and experiment.

import mysql.connector
import json

f = open("connection.json")    # Open connection properties
config = json.loads(f.read())  # Convert JSON to dict

try:
    con = mysql.connector.connect(**config)   # Unpack dict and pass to connect method
    print("Connected Successfully!")
except Exception as ex:
    print("Connection Error : ", ex)
finally:
    if con.is_connected():
        con.close()

Executing Query

It is possible to execute SELECT command and retrieve data from database in the form of list of tuples.

Method execute() of cursor object should use query to be executed and then use fetchall() (or some other fetch methods) to retrieve data in the form of tuples.

The following program displays all employees in ascending order of employee id.

import mysql.connector
import json

f = open("connection.json")  # Open connection properties
config = json.loads(f.read())  # Convert JSON to dict

try:
    con = mysql.connector.connect(**config)
    cur = con.cursor()
    cur.execute("select * from employees order by id")
    for id, name, job, salary in cur.fetchall():
        print(f"{id:2} {name:20} {job:10} {salary:10}")
    cur.close()
except Exception as ex:
    print("Error : ", ex)
finally:
    if con.is_connected():
        con.close()

Inserting Rows

The following program takes data from user and inserts a new row into EMPLOYEES table.

Parameters are represented by %s.

import mysql.connector
import json

f = open("connection.json")    # Open connection properties
config = json.loads(f.read())  # Convert JSON to dict

try:
    con = mysql.connector.connect(**config)
    cur = con.cursor()
    name = input("Enter name : ")
    job = input("Enter job : ")
    salary = int(input("Enter salary : "))
    try:
        cur.execute("insert into employees (fullname,job,salary) values (%s,%s,%s)",(name, job, salary))
        print("Added Employee Successfully!")
        con.commit()      # Must call commit() to commit insertion 
    except Exception as ex:
        print("Insertion Error : ", ex)
    finally:
        cur.close()
except Exception as ex:
    print("Connection Error : ", ex)
finally:
    if con.is_connected():
        con.close()

Updating Employee

The following program updates salary of the employee whose id is given. If id is not found then it displays error, otherwise it commits update.

import mysql.connector
import json

f = open("connection.json")    # Open connection properties
config = json.loads(f.read())  # Convert JSON to dict

try:
    con = mysql.connector.connect(**config)
    cur = con.cursor()
    id = input("Enter id : ")
    salary = input("Enter salary : ")
    try:
        cur.execute("update employees set salary = %s where id = %s", (salary, id,))
        if cur.rowcount == 1:   # if id found and row updated 
            print("Updated Employee Successfully!")
            con.commit()
        else:
            print("Sorry! Employee ID not found!")
    except Exception as ex:
        print("Update Error : ", ex)
    finally:
        cur.close()
except Exception as ex:
    print("Connection Error : ", ex)
finally:
    if con.is_connected():
        con.close()

Deleting Employee

The following program deletes an employee whose id is given. If id is not found then it displays error, otherwise it commits deletion.

import mysql.connector
import json

f = open("connection.json")  # Open connection properties
config = json.loads(f.read())  # Convert JSON to dict

try:
    con = mysql.connector.connect(**config)
    cur = con.cursor()
    id = input("Enter id : ")
    try:
        cur.execute("delete from employees where id = %s", (id,))
        if cur.rowcount == 1:
            print("Deleted Employee Successfully!")
            con.commit()
        else:
            print("Sorry! Employee ID not found!")
    except Exception as ex:
        print("Deletion Error : ", ex)
    finally:
        cur.close()

except Exception as ex:
    print("Connection Error : ", ex)
finally:
    if con.is_connected():
        con.close()

Getting Summaries

The following program uses group functions to get average salary and count of employees. It also uses fetchone() method as the result is always just one row with two columns.

import mysql.connector
import json

f = open("connection.json")  # Open connection properties
config = json.loads(f.read())  # Convert JSON to dict

try:
    con = mysql.connector.connect(**config)
    cur = con.cursor()
    cur.execute("select avg(salary), count(salary) from employees")
    summary = cur.fetchone()
    print(f"Average Salary :  {summary[0]:8.0f}")
    print(f"Employee Count :  {summary[1]:8}")
    cur.close()
except Exception as ex:
    print("Error : ", ex)
finally:
    if con.is_connected():
        con.close()

Update table using data from a file

In this example, we take data from employees.txt file, which contains employee id and new salary, and update EMPLOYEES table.

We ignore lines of the file in which we don't have two values (id and salary).

employees.txt

Here is the program to read data from employees.txt and update salary of EMPLOYEES table.

import mysql.connector
import json

f = open("connection.json")  # Open connection properties
config = json.loads(f.read())  # Convert JSON to dict

try:
    f = open('salaries.txt', 'rt')
    con = mysql.connector.connect(**config)
    cur = con.cursor()
    employees = []
    for line in f.readlines():
        parts = line.strip().split(",")
        if len(parts) != 2:
            continue  # Ignore line

        id, salary = parts
        employees.append((salary, id))

    f.close()

    try:
        cur.executemany("update employees set salary = %s where id = %s", employees)
        print(f"Updated {cur.rowcount} rows!")
        con.commit()
    except Exception as ex:
        print("Updation error : ", ex)
    finally:
        cur.close()

except Exception as ex:
    print("Connection error : ", ex)
finally:
    if con.is_connected():
        con.close()

Convert table to JSON

The following program converts EMPLOYEES table to JSON. It creates a dict for each row and a list of dict for entire table. Once a list of dict is created, it can be converted to array of JSON objects using dumps() method of json module.

import mysql.connector
import json

f = open("connection.json")  # Open connection properties
config = json.loads(f.read())  # Convert JSON to dict

try:
    con = mysql.connector.connect(**config)
    cur = con.cursor()
    cur.execute("select * from employees")
    employees = []
    for id, name, job, salary in cur.fetchall():
        empdict = {"id": id, "name": name, "job": job, "salary": salary}
        employees.append(empdict)

    print(json.dumps(employees))
    cur.close()
except Exception as ex:
    print("Error : ", ex)
finally:
    if con.is_connected():
        con.close()

MySQL provides a set of examples regarding how to use Python connector in folder C:\Program Files\MySQL\Connector Python 8.0\Examples.

For more information on MySQL Python Connector, visit https://dev.mysql.com/doc/dev/connector-python/8.0/

That's all for now. Keep learning!