access_time 4 months ago languageEnglish
more_vert

Python: Load Data from MySQL

visibility 46 comment 0

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

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()
As many other programming languages, we first establish a database connection and then create a cursor using the connection object. SQL INSERT statement is used to insert data into the table.
The result can be verified via mysql CLI:
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()
The result looks like this:
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()
The above code snippet firstly creates a database connection and then use Pandas read_sql API to retrieve data. 
The output looks like the following:
 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. 

copyright This page is subject to Site terms.
Like this article?
Share on

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts

Follow Kontext

Get our latest updates on LinkedIn.

Want to contribute on Kontext to help others?

Learn more

More from Kontext

Load JSON File into BigQuery
visibility 101
thumb_up 0
access_time 2 months ago
visibility 168
thumb_up 1
access_time 2 years ago
visibility 99
thumb_up 0
access_time 4 months ago