There are many options for connecting MySQL from Python, but let's use PyMySQL or mysql-connector-python for now.

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, ('[e-mail]', '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, ('[e-mail]',))
        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.

Comments

[Honiix]

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 :(