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_tableonly 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.