Using peewee with mysql-connector-python

in Python

The problem

You already import mysql.connector in your project and you want to use peewee, only problem is:

peewee.ImproperlyConfigured: MySQL driver not installed!

It's a known issue since 2018: Why does not peewee support mysql-connector-python? · Issue #1501 · coleifer/peewee.

The advice is to:

Since mysql-connector-python (that provides us with mysql.connector) is compliant with DB-API 2.0 spec, the latter is actually an option.

The solution

Here's Database class that handles mysql.connector connection. It expects an environment variable named MYSQL_DSN that contains database credentials in DSN format. You can put it into db.py.

import os
import mysql.connector
import urllib.parse

class Database:
    _conn = None

    @classmethod
    def credentials(cls):
        mysql_dsn = os.getenv('MYSQL_DSN')
        if not mysql_dsn:
            raise RuntimeError('MYSQL_DSN environment variable not set')
        mysql_url = urllib.parse.urlparse(mysql_dsn)
        return {
            'host': mysql_url.hostname,
            'port': mysql_url.port or 3306,
            'user': mysql_url.username,
            'password': mysql_url.password,
            'database': mysql_url.path.lstrip('/'),
        }

    @classmethod
    def connection(cls):
        if cls._conn is None or not cls._conn.is_connected():
            credentials = cls.credentials()
            try:
                cls._conn = mysql.connector.connect(**credentials)
            except mysql.connector.Error as err:
                raise RuntimeError(f'Connection failed: {err}')
        return cls._conn

    @classmethod
    def cursor(cls):
        """
        For use with direct MySQL queries outside of peewee
        """
        conn = cls.connection()
        return conn.cursor()

Now base.py for peewee.

import peewee

from .db import Database

class MySQLConnectorDatabase(peewee.MySQLDatabase):
    """
    Reuse existing DB-API 2.0 compliant mysql.connector instead of relying on PyMySQL or MySQLdb
    - https://github.com/coleifer/peewee/issues/1501
    - https://docs.peewee-orm.com/en/latest/peewee/database.html#adding-a-new-database-driver
    """
    def __init__(self, database, **connect_params):
        # Prevent peewee.InterfaceError: Error, database must be initialized before opening a connection
        # Because parent constructor requires credentials, pass what we got at hand
        super().__init__(**Database.credentials())

    def _connect(self, **kwargs):
        return Database.connection()

class BaseModel(peewee.Model):
    class Meta:
        database = MySQLConnectorDatabase(None) # Point to our wrapper proxying to Database.connection()

Now you can build your peewee model classes:

import peewee

from .base import BaseModel

class User(BaseModel):
    id = peewee.AutoField()
    name = peewee.CharField(max_length=255, null=True)
    # ...

    class Meta:
        table_name = 'users'
#python #peewee