Pandas .to_sql function throws ImportError (DLL Load Failed)

Question

When I'm calling .to_sql() on a Pandas series, I get an ImportError exception:

Exception has occurred: ImportError
DLL load failed: The specified module could not be found.

I imported pandas and pyodbc, and I am able to connect to the DB server and read data through .read_sql_query() as well as use pandas Dataframes elsewhere in the code. It only shows up when I try to call .to_sql()

Pandas version 0.23.4

import pandas as pd
import numpy as np
import pyodbc
conn = pyodbc.connect('Driver={SQL Server};Server=' + prm.DATASOURCE + ';DATABASE=' + prm.DATABASE + ';UID=' + prm.USER + ';PWD=' + prm.PASSWORD)
df.to_sql('table_name', conn, if_exists='append', index=True, index_label=['country_id','year_id'])

Thank you

Did you mean to have your second argument to df.to_sql be "self.conn" instead of "conn"? — pistolpete, Mar 08 '19 at 15:01
@Teddy, I don't think so. OP said they could use pd.read_sql_query() — pistolpete, Mar 08 '19 at 15:16
@pistolpete, thank you for pointing this out. I think @Nuno should explore this route first as a simple googling of `ImportError (DLL Load Failed)` show a lot of SO posts with similar issues marked as solved. :) — Teddy, Mar 08 '19 at 15:24
@Teddy, no pandas.DataFrame.to_sql wants a sqlalchemy engine, not a pyodbc connection. See my post below. — pistolpete, Mar 08 '19 at 15:25
Possible duplicate of [Get data from pandas into a SQL server with PYODBC](https://stackoverflow.com/questions/25661754/get-data-from-pandas-into-a-sql-server-with-pyodbc) — pistolpete, Mar 08 '19 at 15:27

pistolpete · Accepted Answer · 2019-03-08T15:33:34.507

From looking at this SO post and the pandas docs it seems like the problem is that DataFrame.to_sql is meant to be used with SQLAlchemy. Do you have SQLAlchemy installed? If so, maybe try passing in a SQLAlchemy engine instead of your pyodbc connection.

from sqlalchemy import create_engine

# Create your engine.
engine = create_engine('mssql+pyodbc://user:password@server/database')

# Use the engine instead of the pyodbc connection
df.to_sql('table_name', engine, if_exists='append', index=True, index_label=['country_id','year_id'])

From the Pandas docs linked above:

The pandas.io.sql module provides a collection of query wrappers to both facilitate data retrieval and to reduce dependency on DB-specific API...... If SQLAlchemy is not installed, a fallback is only provided for sqlite.

From the pandas.DataFrame.to_sql docs

con : sqlalchemy.engine.Engine or sqlite3.Connection

Using SQLAlchemy makes it possible to use any DB supported by that library. Legacy support is provided for sqlite3.Connection objects.

Changed to sqlalchemy by .create_engine and that solved the problem! Didn't know you could read but not write with pyodbc directly. Thank you — nunoapedro, Mar 08 '19 at 15:48
I didn't know that either until I looked into it, and honestly it's not intuitive at all. I wish they made it more explicit by renaming the "con" parameter to "engine". You're welcome. — pistolpete, Mar 08 '19 at 15:49

Pandas .to_sql function throws ImportError (DLL Load Failed)

1 Answers1