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🔗
pip install pydapper[mysql-connector-python]
poetry add pydapper -E mysql-connector-python
DSN format🔗
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"