Python: Load Data from MySQL

To connect to MySQL in Python, there are various approaches:

• JDBC driver - JayDeBeApi python wrapper package.
• Native python MySQL packages, for example, MySQL connector, etc. 

This article provides an example of using MySQL connector package to connect to MySQL.

Prerequisites

• Python 3
• MySQL server. Refer to this article to configure a community version of MySQL if you don't have an instance to connect to: Apache Hive 3.1.2 Installation on Linux Guide (section Install MySQL) or Install MySQL on macOS.

Install Python MySQL Connector

Run the following command to install Python MySQL connector:

pip install mysql-connector-python

This package depends on Python protobuf package (>=3.0.0).

Example output:

PS F:\Projects\Python> pip install mysql-connector-python
Collecting mysql-connector-python
  Downloading mysql_connector_python-8.0.23-cp38-cp38-win_amd64.whl (854 kB)
     |████████████████████████████████| 854 kB 1.6 MB/s
Collecting protobuf>=3.0.0
  Downloading protobuf-3.14.0-py2.py3-none-any.whl (173 kB)
     |████████████████████████████████| 173 kB 6.4 MB/s
Requirement already satisfied: six>=1.9 in c:\users\fahao.000\appdata\roaming\python\python38\site-packages (from protobuf>=3.0.0->mysql-connector-python) (1.14.0)
Installing collected packages: protobuf, mysql-connector-python
Successfully installed mysql-connector-python-8.0.23 protobuf-3.14.0

Use the Python connector

Once the connector is installed, we can use it to connect to MySQL server.  The following examples connects to a test database created using mysql CLI:

mysql> create database test_db;
Query OK, 1 row affected (0.03 sec)

mysql> use test_db;
Database changed
mysql> show tables;
Empty set (0.01 sec)

mysql> create table test_table(id int, value varchar(10));
Query OK, 0 rows affected (0.29 sec)

Table test_table only has two columns. 

The server details are listed below:

• Server: localhost or 127.0.0.1
• MySQL service port: 10101 (for my environment, I used a customized port instead of the default 3306).
• User: hive
• Password: hive

Insert records into table

The following code snippet insert two records into the test table.

import mysql.connector

conn = mysql.connector.connect(user='hive', database='test_db',
                               password='hive',
                               host="localhost",
                               port=10101)
cursor = conn.cursor()

add_record = ("INSERT INTO test_table "
              "(id, value) "
              "VALUES (%s, %s)")

# Insert records
for i in range(5):
    cursor.execute(add_record, (i, "Record " + str(i)))

# Make sure data is committed to the database
conn.commit()

# Close cursor and connection
cursor.close()
conn.close()

mysql> select * from test_table;
+------+----------+
| id   | value    |
+------+----------+
|    0 | Record 0 |
|    1 | Record 1 |
|    2 | Record 2 |
|    3 | Record 3 |
|    4 | Record 4 |
+------+----------+
5 rows in set (0.00 sec)

Querying data

This example query data directly from the database using the connector:

import mysql.connector

conn = mysql.connector.connect(user='hive', database='test_db',
                               password='hive',
                               host="localhost",
                               port=10101)
cursor = conn.cursor()


query = "SELECT id, value FROM test_table"

cursor.execute(query)

for (id, value) in cursor:
    print("ID={}, Value={}".format(
        id, value))

cursor.close()
conn.close()

python .\mysql-example.py
ID=0, Value=Record 0
ID=1, Value=Record 1
ID=2, Value=Record 2
ID=3, Value=Record 3
ID=4, Value=Record 4

Create Pandas DataFrame

We can also use Pandas framework to create a DataFrame object. The performance will also be better instead of using cursor:

import mysql.connector
import pandas as pd 

conn = mysql.connector.connect(user='hive', database='test_db',
                               password='hive',
                               host="localhost",
                               port=10101)
cursor = conn.cursor()

query = "SELECT id, value FROM test_table"

df = pd.read_sql(query, con=conn)
print(df)
conn.close()

 python .\mysql-example.py
   id     value
0   0  Record 0
1   1  Record 1
2   2  Record 2
3   3  Record 3
4   4  Record 4

References

Connect to SQL Server via JayDeBeApi in Python

MySQL :: MySQL Connector/Python Developer Guide :: 5.1 Connecting to MySQL Using Connector/Python

As always, let me know if you have any questions.