Expected URI to be a string to_sql()

Question

I am trying to upload a large dask dataframe to a sql server but i'm getting an error and can't see anything wrong with the connection string. I am able to connect to the DB using this connection string but I get the error when running the following:

import sqlalchemy as sa
import dask.dataframe as dd
from dask.diagnostics import ProgressBar
pbar = ProgressBar()
pbar.register()
#windows authentication + fast_executemany=True
to_sql_uri = sa.create_engine('mssql+pyodbc://TEST-BI/DB_TEST?driver=SQL Server?Trusted_Connection=yes', fast_executemany=True)
ddf.to_sql('test', uri=to_sql_uri, if_exists='replace', index=False)

ValueError: Expected URI to be a string, got <class 'sqlalchemy.engine.base.Engine'>.

i've tried forcing the conversion of to_sql_uri to string but still get an error.

i think you should replace the parameter ```uri``` with ```con``` — Yash, Sep 25 '20 at 12:50
tried changing to con but TypeError: to_sql() got an unexpected keyword argument 'con' — Sql_Pete_Belfast, Sep 25 '20 at 13:01

score 2 · Accepted Answer · answered Sep 25 '20 at 13:59

2

I'm sorry if the documentation is not clear enough (docs page, api reference). However, you did not pass a string in your call, you passed an engine instance, which is what the error message says.

You should do

ddf.to_sql('test', 
    uri='mssql+pyodbc://TEST-BI/DB_TEST?driver=SQL Server?Trusted_Connection=yes', 
    if_exists='replace', index=False)

answered Sep 25 '20 at 13:59

mdurant

27,272
5
45
74

But will that include the fast_executemany=True option? I didn't think it would? – Sql_Pete_Belfast Sep 25 '20 at 14:37
1

to_sql does not currently pass arbitrary arguments to the create_engine function. Feel free to propose or implement this. However, passing an engine will never work, because the instances cannot be serialised. – mdurant Sep 25 '20 at 15:33

Expected URI to be a string to_sql()

1 Answers1