44.python-and-mysql.md

Connecting Python to MySQL

In Python 3, we can use the third-party library mysqlclient or pymysql to connect to the MySQL database and do data persistence operations. The usage of the two is exactly the same, only the imported module name is different. We recommend everyone use the pure Python third-party library pymysql, because it is easier to install successfully.

Below, we still use the hrs database created earlier as an example, to show how to use a Python program to operate the MySQL database and do data persistence operations.

Connecting to MySQL

First, we can install pymysql with the command below in the command line or in PyCharm's terminal. If you need to connect to MySQL 8, you also need to install a third-party library named cryptography to support the MySQL 8 password authentication method.

pip install pymysql cryptography

The steps for using pymysql to operate MySQL are shown below:

1. Create a connection. After the MySQL server starts, it provides a network service based on TCP, the Transmission Control Protocol. We can connect to the MySQL server through the connect function of the pymysql module. When calling the connect function, we need to specify parameters such as host (host), port (port), username (user), password (password), database (database), and character set (charset). This function returns a Connection object.
2. Get a cursor. After successfully connecting to the MySQL server, the next thing to do is send SQL statements to the database server. MySQL will execute the received SQL and return the execution result through the network. To do this, we first need to get a cursor, that is, a Cursor object, through the cursor method of the connection object.
3. Send SQL. Through the execute method of the cursor object, we can send SQL statements to the database.
4. If an insert, delete, or update operation is executed, we need to commit or roll back the transaction according to the actual situation. Because when the connection is created, the transaction environment is turned on by default. After the operation is completed, we need to use the commit or rollback method of the connection object to commit or roll back the transaction. The rollback method is usually put in the exception handling block except. If a select operation is executed, we need to fetch the query result through the cursor object. There are three methods for this: fetchone, fetchmany, and fetchall. Among them, fetchone fetches one record and returns it as a tuple or dictionary; fetchmany and fetchall fetch multiple records and return them as nested tuples or a list of dictionaries.
5. Close the connection. After finishing the persistence operation, please do not forget to close the connection and release external resources. We usually use the close method of the connection object in the finally block to close the connection.

Below, we will use actual code to demonstrate the five steps above.

Code Examples

Insert Data

import pymysql

no = int(input('Department ID: '))
name = input('Department name: ')
location = input('Department location: ')

# 1. Create a connection
conn = pymysql.connect(host='127.0.0.1', port=3306,
                       user='guest', password='Guest.618',
                       database='hrs', charset='utf8mb4')
try:
    # 2. Get a cursor
    with conn.cursor() as cursor:
        # 3. Execute SQL
        affected_rows = cursor.execute(
            'insert into `tb_dept` values (%s, %s, %s)',
            (no, name, location)
        )
        if affected_rows == 1:
            print('Department inserted successfully!')
    # 4. Commit the transaction
    conn.commit()
except pymysql.MySQLError as err:
    conn.rollback()
    print(type(err), err)
finally:
    # 5. Close the connection
    conn.close()

Note: 127.0.0.1 above is called the loopback address. It means the local machine. The guest below is a user I created in advance. This user has insert, delete, update, and select permissions on the hrs database. We do not recommend that everyone directly use the root super administrator account to access the database in a project. Doing that is really too dangerous.

Example SQL to create that user:

create user 'guest'@'%' identified by 'Guest.618';
grant insert, delete, update, select on `hrs`.* to 'guest'@'%';

If you need to insert a large amount of data, it is recommended to use the executemany method of the cursor object to do batch processing, that is, one insert operation followed by many groups of data. Everyone can try inserting 10,000 records into a table, and then see the difference between inserting one by one without batch processing and inserting with batch processing. The first parameter of the executemany method is still an SQL statement, and the second parameter can be a list or tuple containing many groups of data.

Delete Data

import pymysql

no = int(input('Department ID: '))

conn = pymysql.connect(host='127.0.0.1', port=3306,
                       user='guest', password='Guest.618',
                       database='hrs', charset='utf8mb4',
                       autocommit=True)
try:
    with conn.cursor() as cursor:
        affected_rows = cursor.execute(
            'delete from `tb_dept` where `dno`=%s',
            (no, )
        )
        if affected_rows == 1:
            print('Department deleted successfully!')
finally:
    conn.close()

Note: If you do not want to manually commit or roll back the transaction after every SQL operation, you can add a parameter named autocommit in the connect function and set it to True, which means after each SQL is executed successfully, it is committed automatically. But we recommend everyone commit or roll back manually, so that the transaction environment can be built according to the actual business needs. If you do not want to catch and handle exceptions, you can directly put finally after the try block. Omitting except means that when an exception happens, the code will crash directly and show the exception stack in the terminal.

Update Data

import pymysql

no = int(input('Department ID: '))
name = input('Department name: ')
location = input('Department location: ')

conn = pymysql.connect(host='127.0.0.1', port=3306,
                       user='guest', password='Guest.618',
                       database='hrs', charset='utf8mb4')
try:
    with conn.cursor() as cursor:
        affected_rows = cursor.execute(
            'update `tb_dept` set `dname`=%s, `dloc`=%s where `dno`=%s',
            (name, location, no)
        )
        if affected_rows == 1:
            print('Department updated successfully!')
    conn.commit()
except pymysql.MySQLError as err:
    conn.rollback()
    print(type(err), err)
finally:
    conn.close()

Query Data

1. Query the department table:

import pymysql

conn = pymysql.connect(host='127.0.0.1', port=3306,
                       user='guest', password='Guest.618',
                       database='hrs', charset='utf8mb4')
try:
    with conn.cursor() as cursor:
        cursor.execute('select `dno`, `dname`, `dloc` from `tb_dept`')
        row = cursor.fetchone()
        while row:
            print(row)
            row = cursor.fetchone()
except pymysql.MySQLError as err:
    print(type(err), err)
finally:
    conn.close()

Note: In the code above, we use a while loop to fetch the query result row by row. This way is especially suitable when the query result has a very large number of rows. Because if fetchall is used to fetch all records into a nested tuple at one time, it will cause very large memory overhead, and in many scenarios this is not a good idea. If you do not want to use a while loop, you can also think about using the iter function to build an iterator and fetch data row by row. Interested readers can study it by themselves.

2. Paginated query on the employee table:

import pymysql

page = int(input('Page: '))
size = int(input('Page size: '))

con = pymysql.connect(host='127.0.0.1', port=3306,
                      user='guest', password='Guest.618',
                      database='hrs', charset='utf8')
try:
    with con.cursor(pymysql.cursors.DictCursor) as cursor:
        cursor.execute(
            'select `eno`, `ename`, `job`, `sal` from `tb_emp` order by `sal` desc limit %s,%s',
            ((page - 1) * size, size)
        )
        for emp_dict in cursor.fetchall():
            print(emp_dict)
finally:
    con.close()

Example: Export Database Data to Excel

Below we explain an example of exporting database table data to an Excel file. We first need to install the third-party library openpyxl. The command is shown below.

pip install openpyxl

Next, through the code below, we export all employees' ID, name, job, monthly salary, allowance, and department name from the hrs database to an Excel file.

import openpyxl
import pymysql

workbook = openpyxl.Workbook()
sheet = workbook.active
sheet.title = 'Employee Basic Info'
sheet.append(('Employee ID', 'Name', 'Job', 'Salary', 'Allowance', 'Department'))

conn = pymysql.connect(host='127.0.0.1', port=3306,
                       user='guest', password='Guest.618',
                       database='hrs', charset='utf8mb4')
try:
    with conn.cursor() as cursor:
        cursor.execute(
            'select `eno`, `ename`, `job`, `sal`, coalesce(`comm`, 0), `dname` '
            'from `tb_emp` natural join `tb_dept`'
        )
        row = cursor.fetchone()
        while row:
            sheet.append(row)
            row = cursor.fetchone()
    workbook.save('hrs.xlsx')
except pymysql.MySQLError as err:
    print(err)
finally:
    conn.close()

You can refer to the example above and try importing the data in an Excel file into the specified table of the specified database, and see whether it can be done successfully.