There are many options for connecting MySQL from Python, but let's use PyMySQL or mysql-connector-python for now.
Tags: python, mysql, database
Published: 2022-03-06 06:15:28

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](https://github.com/mysql/mysql-connector-python)
- Officially supported by Oracle
- Pure python
- A little slow
- Not compatible with MySQLdb

### [PyMySQL](https://github.com/PyMySQL/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
```python
import mysql.connector

# Connect to server
cnx = mysql.connector.connect(
    host="127.0.0.1",
    port=3306,
    user="mike",
    password="s3cre3t!")
```
### Insert/Select records
```python
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
```python
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
```python
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://dev.mysql.com/doc/connector-python/en/connector-python-examples.html
)
- [https://github.com/PyMySQL/PyMySQL](https://github.com/PyMySQL/PyMySQL)

## Advertisement
I am working on a web app called [spliito.com](https://bit.ly/3H7w3Zf) 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.
![screen](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/tzzttal5w02ljb2u2nmt.png)