MySQL

Supported drivers:

dbapi	default	driver	connection class
mysql-connector-python		mysql+mysql	mysql.connector.connection_cext.CMySQLConnection

mysql-connector-python

mysql-connector-python is the default dbapi driver for MySQL in pydapper. It is actually registered as mysql because that is the name of the actual package that is installed.

Note

Because of the build in behavior of mysql-connector-python, it is currently required to run cursor.fetchall() in the query_first implementation in order to flush the result set from the server When using query_first with MySQL, it is advisable to use LIMIT 1 in your query to prevent downloading unneeded rows.

Installation

pippoetry

pip install pydapper[mysql-connector-python]

poetry add pydapper -E mysql-connector-python

DSN format

TemplateExampleExample (Default Driver)

dsn = f"mysql+mysql://{user}:{password}@{host}:{port}/{dbname}"

dsn = "mysql+mysql://myuser:mypassword:3306@localhost/mydb"

dsn = "mysql://myuser:mypassword:3306@localhost/mydb"

Note

Databases and schemas are synonymous in MySQL.

Example - connect

The mysql-connector-python docs do not have clear examples of the behavior of the context manager. For the current version, the context manager simply closes the connection when it is finished. Handling transactions commits is up to you (see example).

import pydapper

# NOTE: setting autocommit to True will cause the transaction to commit immediately
with pydapper.connect("mysql+mysql://root:pydapper@localhost:3307/pydapper", autocommit=True) as commands:
    print(type(commands))
    # <class 'pydapper.mysql.mysql_connector_python.MySqlConnectorPythonCommands'>

    print(type(commands.connection))
    # <class 'mysql.connector.connection_cext.CMySQLConnection'>

    with commands.cursor() as raw_cursor:
        print(type(raw_cursor))
        # <class 'mysql.connector.cursor_cext.CMySQLCursor'>

    # you could alternatively commit your transaction all together at the end of the block
    commands.connection.commit()

Example - using

Use pydapper with a mysql-connector-python connection pool.

import mysql.connector

import pydapper

conn_from_pool = mysql.connector.connect(
    pool_name="pydapper", pool_size=5, port=3307, password="pydapper", user="root", autocommit=True
)

commands = pydapper.using(conn_from_pool)
print(type(commands))
# <class 'pydapper.mysql.mysql_connector_python.MySqlConnectorPythonCommands'>

print(type(commands.connection))
# <class 'mysql.connector.pooling.PooledMySQLConnection'>

conn_from_pool.close()  # doesn't actually close, but returns it to pool "pydapper"