Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

pandas read_sql fails with Execution failed on sql ... sqlite_master on a MySQL connection

Errorpandas.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.

  • Applies to pandas 2.3, SQLAlchemy 2.0, PyMySQL 1.1, MySQL 8.4, Python 3.12
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 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__)"
  2. 2Match what you see, then jump to its fix

    1. 1The 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. 2pandas 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. 3The engine URL contains a password with @, : or / and the connection or the URL parse failsThe password has characters that break the URLFix

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

Did this fix it?

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 pymysql

2Fix

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

Did this fix it?

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

Did this fix it?

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:

Related fixes

Databases

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 fix

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.