pandas read_sql fails with Execution failed on sql ... sqlite_master on a MySQL connection
pandas.errors.DatabaseError: Execution failed on sql 'SELECT name FROM sqlite_master WHERE type='table' AND name=?;'Short answer
pandas supports a plain DBAPI connection only for sqlite3. When you pass a raw pymysql connection, it treats it as sqlite and sends sqlite-specific SQL that MySQL rejects. Create a SQLAlchemy engine with a mysql+pymysql URL and pass that to read_sql or to_sql instead. Install the SQLAlchemy extra if it is missing.
Fix it fast
1Find out which cause you have
Print the type of the object you pass as con. If it is a pymysql Connection, that is cause 1. If it is already an Engine and the error remains, check the installed packages.
python -c "import pandas, sqlalchemy, pymysql; print(pandas.__version__, sqlalchemy.__version__, pymysql.__version__)"2Match what you see, then jump to its fix
- 1
The code calls pymysql.connect(...) and passes the result to pd.read_sql, pd.read_sql_query or df.to_sqlcon is a raw pymysql connectionFix - 2
pandas works with sqlite but not with MySQL, or the packages are not installed in the interpreter that runs the scriptSQLAlchemy or PyMySQL is missing or too oldFix - 3
The engine URL contains a password with @, : or / and the connection or the URL parse failsThe password has characters that break the URLFix
- 1
What does the error look like?
pandas.errors.DatabaseError: Execution failed on sql 'SELECT name FROM sqlite_master WHERE type='table' AND name=?;'Execution failed on sql 'SELECT name FROM sqlite_master WHERE type='table' AND name=?;'
Cause 1 of 3
con is a raw pymysql connection
1Check
The read_sql documentation lists the accepted types for con: an ADBC connection, a SQLAlchemy connectable, a string, or a sqlite3 connection. For other DBAPI2 objects it states that only sqlite3 is supported, so MySQL through a bare pymysql connection falls outside it.
grep -rn "pymysql.connect" --include=*.py .2Fix
Build a SQLAlchemy engine for MySQL and pass it as con. You are responsible for disposing the engine when you finish.
import pandas as pd
from sqlalchemy import create_engine
engine = create_engine("mysql+pymysql://user:password@localhost:3306/mydb")
df = pd.read_sql("SELECT * FROM orders", engine)
engine.dispose()3Verify
The query returns a DataFrame.
python -c "import pandas as pd; from sqlalchemy import create_engine; e = create_engine('mysql+pymysql://user:password@localhost:3306/mydb'); print(pd.read_sql('SELECT 1 AS ok', e))"Confirmed by pandas.pydata.org
Cause 2 of 3
SQLAlchemy or PyMySQL is missing or too old
1Check
pandas lists SQLAlchemy and PyMySQL as optional dependencies, with minimum versions. Check what the interpreter that runs your script has.
python -m pip show sqlalchemy pymysql2Fix
Install pandas with the MySQL and SQL extras, which pull in versions pandas supports.
python -m pip install "pandas[mysql,sql-other]"3Verify
Print the versions again; SQLAlchemy is at the minimum or above.
python -c "import sqlalchemy, pymysql; print(sqlalchemy.__version__, pymysql.__version__)"Confirmed by pandas.pydata.org
Cause 3 of 3
The password has characters that break the URL
1Check
SQLAlchemy says special characters in a URL must be URL encoded, or you can build the URL with URL.create(), which takes the raw password.
grep -rn "mysql+pymysql://" --include=*.py .2Fix
Use URL.create so the password goes in as plain text.
from sqlalchemy import create_engine
from sqlalchemy.engine import URL
url = URL.create(
"mysql+pymysql",
username="user",
password="p@ss:word/1",
host="localhost",
port=3306,
database="mydb",
)
engine = create_engine(url)3Verify
Connect and run a trivial query.
python -c "from sqlalchemy import text; print(engine.connect().execute(text('select 1')).scalar())"Confirmed by docs.sqlalchemy.org
How do I stop it coming back?
- Use SQLAlchemy engines for every non-sqlite database in pandas, for reads and for to_sql.
- Install pandas with the extras it documents, instead of adding packages one by one.
- Build URLs with URL.create when credentials come from a secret store.
Sources
How was this page made?
I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.
Which stack is behind this page?
Projects of mine built on the same technologies:
- ShopGoAn e-commerce aggregator with a Python scraping engine behind its catalog, past 10K Google Play downloads.
- TaxiDexMy own multi-tenant taxi dispatch SaaS: API, dispatcher portal, driver and passenger apps, and the servers underneath.
- QistLockA NestJS and React SaaS driving an Android Device Owner agent that locks financed phones when payments lapse.
Related fixes
Databases
Installing the Airflow MySQL provider fails on pkg-config or mysqlclient
Building the mysqlclient wheel fails during the install
The Airflow MySQL provider depends on mysqlclient, which pip compiles against the MySQL C client library.
2 causes, each with a fixDatabases
Django cannot load MySQLdb and asks whether mysqlclient is installed
Error loading MySQLdb module: Did you install mysqlclient or MySQL-python?
Django's MySQL backend imports MySQLdb, which is provided by the mysqlclient package.
3 causes, each with a fixDatabases
python manage.py migrate finishes but the tables for my app are missing
relation "myapp_book" does not exist
migrate only applies migration files that exist.
3 causes, each with a fixDatabases
MySQL error 3159: connections using insecure transport are prohibited
ER_SECURE_TRANSPORT_REQUIRED
Error 3159 means the server has require_secure_transport turned on and your client connected over plain TCP.
3 causes, each with a fixDatabases
pip install mysqlclient fails on a Mac
The build output mentions pkg-config or the MySQL client library
mysqlclient is a C extension, so pip compiles it against the MySQL client library and finds that library with pkg-config.
3 causes, each with a fixDatabases
pip install mysqlclient fails to build on Windows, Linux or macOS
error: metadata-generation-failed
mysqlclient is a C extension.
3 causes, each with a fix