I'm trying to build an app with Flask+MySQL, but there are various packages for connecting to MySQL with Python3 and I wasn't sure which one is good and which one is bad.
I tried to use various packages and found PyMySQL or mysql-connector-python to be the simplest and easiest to use for me personally, so I'm going to write down how to use it.
Looking at StackOverflow and other sites, it appears that many responses recommend PyMySQL or mysql-connector-python.
Let's take a brief look at a brief overview of each and how to use them.
👀 Comparison of Overviews
mysql-connector-python
- Officially supported by Oracle
- Pure python
- A little slow
- Not compatible with MySQLdb
PyMySQL
- Pure python
- Faster than mysql-connector
- Almost completely compatible with MySQLdb, after calling pymysql.install_as_MySQLdb()
It is encouraging that mysql-connector-python is supported by Oracle. On the other hand, if you want speed, use PyMySQL.
✅ How to use mysql-connector-python
installation
pip install mysql-connector-python
Connect to the database
import mysql.connector
# Connect to server
cnx = mysql.connector.connect(
host="127.0.0.1",
port=3306,
user="mike",
password="s3cre3t!")
Insert/Select records
from __future__ import print_function
from datetime import date, datetime, timedelta
import mysql.connector
cnx = mysql.connector.connect(user='scott', database='employees')
cursor = cnx.cursor()
tomorrow = datetime.now().date() + timedelta(days=1)
# Insert new employee
add_employee = ("INSERT INTO employees "
"(first_name, last_name, hire_date, gender, birth_date) "
"VALUES (%s, %s, %s, %s, %s)")
data_employee = ('Geert', 'Vanderkelen', tomorrow, 'M', date(1977, 6, 14))
cursor.execute(add_employee, data_employee)
cnx.commit()
query = ("SELECT first_name, last_name, hire_date FROM employees "
"WHERE hire_date BETWEEN %s AND %s")
hire_start = datetime.date(1999, 1, 1)
hire_end = datetime.date(1999, 12, 31)
cursor.execute(query, (hire_start, hire_end))
for (first_name, last_name, hire_date) in cursor:
print("{}, {} was hired on {:%d %b %Y}".format(
last_name, first_name, hire_date))
cursor.close()
cnx.close()
✅ How to use PyMySQL
installation
pip install PyMySQL
Connect to the database
import pymysql.cursors
# Connect to the database
connection = pymysql.connect(host='localhost',
user='user',
password='passwd',
database='db',
cursorclass=pymysql.cursors.DictCursor)
Insert/Select records
with connection:
with connection.cursor() as cursor:
# Create a new record
sql = "INSERT INTO `users` (`email`, `password`) VALUES (%s, %s)"
cursor.execute(sql, ('webmaster@python.org', 'very-secret'))
# connection is not autocommit by default. So you must commit to save
# your changes.
connection.commit()
with connection.cursor() as cursor:
# Read a single record
sql = "SELECT `id`, `password` FROM `users` WHERE `email`=%s"
cursor.execute(sql, ('webmaster@python.org',))
result = cursor.fetchone()
print(result)
result
{'password': 'very-secret', 'id': 1}
Summary
I personally prefer PyMySQL. Which do you prefer? Or do you know a better package?
Reference page
- https://dev.mysql.com/doc/connector-python/en/connector-python-examples.html
- https://github.com/PyMySQL/PyMySQL
Advertisement
I am working on a web app called spliito.com that solves the tedious task of recording the money owed and borrowed that occurs when you go on a trip with friends in a spreadsheet and calculating who should pay back how much to whom at the end of the trip. I used PyMySQL for this app. It's pretty fast.
Top comments (1)
Hi, thanks for sharing your research.
There is also mysqlclient to consider. It's a wrapper around the C connector for MySQL.
But don't change in the middle of your developpement, they don't act all the same :(