Short answer
No — you should not assert exact df.dtypes against an in-memory SQLite engine and expect those assertions to be valid for PostgreSQL or MySQL. Additionally, read_sql has no built-in configuration to normalize dtype inference across different SQLAlchemy engines. The only reliable way to ensure consistent dtypes is to declare them explicitly: pass a dtype mapping to read_sql or use astype() immediately after loading to establish a deterministic contract.
Why the dtypes diverge
SQLite uses dynamic typing with type affinity. A column declared as INTEGER can store text, and values are returned to the driver based on the storage class of the actual value. pandas then infers dtypes from these observed Python values. This leads to two primary discrepancies:
- NULL Promotion: Columns that are entirely NULL or contain NULLs in SQLite often result in
object or float64 dtypes because pandas promotes integers-with-NaN to floats.
- Strict Mapping: Production backends like PostgreSQL and MySQL enforce declared types. SQLAlchemy maps these strict types to specific pandas categories (e.g.,
INTEGER to int64), regardless of whether the specific result set contains NULLs.
Consequently, a test asserting df["amount"].dtype == "float64" might pass on SQLite due to NaN promotion, while production returns object (Decimal) or int64, meaning the test is validating the surrogate engine's behavior rather than the application logic.
Recommended implementation
Treat the dtype requirement as a part of your application logic rather than a database property. Use one of the following patterns:
1. Explicit Type Declaration
DTYPES = {"amount": "float64", "quantity": "Int64", "name": "string"}
# Apply coercion immediately after loading
df = pd.read_sql(query, engine)
df = df.astype(DTYPES)
Using nullable types like Int64 or string ensures that NULLs are preserved without forcing the entire column to float64. This makes df.dtypes assertions meaningful across all environments.
2. Semantic Assertions
If you rely on inference, avoid asserting raw dtypes in SQLite tests. Instead, focus on backend-independent properties: row counts, value equality, and NULL presence. Use pd.testing.assert_frame_equal(..., check_dtype=False) for value verification, and reserve exact-dtype assertions for tests running against a production-equivalent container (e.g., PostgreSQL in CI).
Caveats and Verification
- Data Truncation: Forcing a dtype can cause errors or data loss if the specified type cannot accommodate the database values. These errors are useful as they highlight schema mismatches.
- Version Sensitivity: Type mapping can change between pandas and SQLAlchemy versions. Pin these dependencies in your test environment.
To verify the divergence, seed the same fixture into both engines and compare the output:
for url in ["sqlite:///:memory:", "postgresql+psycopg2://user:pass@host/db"]:
eng = create_engine(url)
seed(eng)
print(f"{url}: {pd.read_sql('select * from t', eng).dtypes}")
pandas
sqlite
sqlalchemy