PYTHON / DATABASES WITH PYTHON
Connecting to MySQL from Python
Open, configure and close a MySQL connection from Python with a DB-API driver, and tell apart the errors a failed connect raises.
What you will learn
- Install and import a real MySQL driver such as mysql-connector-python
- Pass host, port, user, password, database and charset as a config dict
- Tell apart errno 1045, 1049 and 2003 when a connect attempt fails
- Reuse sockets with MySQLConnectionPool instead of connecting per query
Understanding Connecting to MySQL from Python
MySQL is a separate server process that speaks its own binary protocol over TCP port 3306 (or a Unix socket). Connecting is therefore not opening a file the way sqlite3 does: the driver performs a TCP handshake, then an authentication handshake against a user@host account, then a USE of the schema you named in the database= argument. Python has no MySQL support in the standard library, so you install a third-party driver: mysql-connector-python from Oracle (pure Python plus an optional C extension), PyMySQL (pure Python), or mysqlclient (a binding to libmysqlclient that needs a compiler and headers). All three implement PEP 249, so once connected the calling code looks nearly identical.
A connection object is expensive and stateful, and both facts drive how you should treat it. Expensive, because every connect() costs a round trip for TCP, several more for the authentication plugin, and one server thread out of max_connections. Stateful, because the session carries a current database, a character set, a time zone and an autocommit flag; mysql-connector-python turns autocommit off by default, which is the opposite of what the mysql command line client does. That means the settings you pass to connect() silently change how every later statement on that connection behaves.
Connect failures happen at distinct stages and MySQL numbers them, so branch on err.errno rather than on the message text. Error 2003 means the driver never reached a server: wrong host, wrong port, server down, firewall. Error 1045 means the server answered and rejected your credentials for that user@host combination. Error 1049 means you authenticated but the schema in database= does not exist, and 1046 means you never named one. Always pass connection_timeout so an unreachable host fails in seconds instead of hanging on the operating system's default TCP timeout.
import mysql.connector
from mysql.connector import errorcode
config = {
"host": "127.0.0.1",
"port": 3306,
"user": "app",
"password": "s3cret",
"database": "shopdb",
"charset": "utf8mb4",
"connection_timeout": 5,
}
try:
conn = mysql.connector.connect(**config)
except mysql.connector.Error as err:
if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
print("wrong user or password")
elif err.errno == errorcode.ER_BAD_DB_ERROR:
print("database does not exist")
else:
print("connect failed:", err.errno)
else:
print("connected:", conn.is_connected())
print("database:", conn.database)
print("autocommit:", conn.autocommit)
print("server:", conn.get_server_info())
conn.close()
print("connected:", conn.is_connected())A MySQL connection is an authenticated, stateful network session with its own session settings, not a file handle, so it must be configured, reused and closed deliberately.
Worked examples
Credentials from the environment
Builds the connection settings from environment variables and guarantees the socket is closed with try/finally.
import os
import mysql.connector
cfg = {
"host": os.environ.get("MYSQL_HOST", "127.0.0.1"),
"port": int(os.environ.get("MYSQL_PORT", "3306")),
"user": os.environ["MYSQL_USER"],
"password": os.environ["MYSQL_PASSWORD"],
"database": os.environ["MYSQL_DB"],
"autocommit": True,
"connection_timeout": 3,
}
print("connecting to", cfg["host"], "as", cfg["user"])
conn = mysql.connector.connect(**cfg)
try:
cur = conn.cursor()
cur.execute("SELECT DATABASE()")
print("database:", cur.fetchone()[0])
print("autocommit:", conn.autocommit)
cur.close()
finally:
conn.close()
print("socket closed")Example explained
Line 1os.environ["MYSQL_USER"] raises KeyError immediately if the variable is unset, which is a clearer failure than a later error 1045.
Line 2The port must be cast with int(): environment variables are strings and the driver expects a number.
Line 3autocommit=True is a session setting applied during connect, so it changes the behaviour of every statement on this connection.
Line 4The finally block sends COM_QUIT even if execute() raises, so the server thread is released instead of waiting for wait_timeout.
Reusing sockets with a pool
Shows that closing a pooled connection returns it to the pool rather than dropping the TCP connection.
from mysql.connector import pooling
pool = pooling.MySQLConnectionPool(
pool_name="shoppool",
pool_size=2,
host="127.0.0.1",
user="app",
password="s3cret",
database="shopdb",
)
for i in range(3):
conn = pool.get_connection()
cur = conn.cursor()
cur.execute("SELECT CONNECTION_ID()")
print(i, conn.pool_name, cur.fetchone()[0])
cur.close()
conn.close()Example explained
Line 1MySQLConnectionPool opens pool_size connections while it is being constructed, so the two handshakes are paid once up front.
Line 2get_connection() takes a PooledMySQLConnection off an internal queue; with two members the third pass gets the first one back.
Line 3CONNECTION_ID() is the server side thread id, so the repeated 41 proves the same socket was handed out again.
Line 4conn.close() on a pooled connection returns it to the queue instead of disconnecting, which is why the loop never exceeds two server threads.
Important notes
with conn: does not mean the same thing everywhere. In sqlite3 it wraps a transaction and leaves the connection open; in mysql-connector-python 8.x leaving the block closes the connection.
With mysqlclient and other libmysqlclient based drivers, host='localhost' selects a Unix socket and ignores port, so use '127.0.0.1' when you need TCP, for example against a container with a mapped port.
Common mistakes
Running pip install mysql instead of mysql-connector-python: that PyPI package is a stub, so import mysql.connector fails with ModuleNotFoundError even though 'mysql' appears to be installed.
Omitting the database= argument and only noticing at the first query, which fails with error 1046 'No database selected' rather than at connect time.
Calling connect() inside a loop and never closing, which leaves one server thread per iteration until MySQL refuses new clients with error 1040 'Too many connections'.
Try it yourself
Change, predict, then run
Write build_config(settings) that copies the given dict, fills in defaults of host '127.0.0.1', port 3306 and charset 'utf8mb4', and raises ValueError naming any missing key among user, password and database. Print the result for {'user': 'app', 'password': 'x', 'database': 'shopdb'} and for a dict with no password.
Open the Python workspaceCheck your understanding
A Python script using mysql-connector-python inserts rows and prints no errors, but the rows are missing when you look from the mysql command line client. Typing the same INSERT into that client persists it. What is the most likely explanation?
- The driver opened the connection with autocommit off, so the open transaction was discarded when the connection closed
- The script connected to a different database than the command line client
- The connection needed charset='utf8mb4' for writes to be stored
- Cursors buffer statements and cur.close() must be called to send them
Show answer
mysql-connector-python sets autocommit to False when it connects, so the INSERT lived in a transaction that was rolled back on close; passing autocommit=True or committing fixes it. A different database is tempting but the database= argument pins the schema, and a wrong name would have raised error 1049 at connect time instead of succeeding silently.