1

The create_engine() statement doesn't seem to work when using mssql+pyodbc, it throws an InterfaceError. The same happens when I try to use turbodbc. However, it works fine when using mssql+pymssql.

Mainly, I'm trying to speed up my df.to_sql() operation. To that end, I tried two options.

  1. using pyodbc and implementing fastexecutemany = True.
  2. using turbodbc.

Both options throw the same error when used in the create_engine() command.

I am using Windows 10 and Windows Authentication, Python 3.7.

The connection can be established using pyodbc. So, this works:

cnxn = pyodbc.connect("Driver={ODBC Driver 13 for SQL Server};"
                          "Server=server;"
                          "Database=database;"
                          "Trusted_Connection=yes;")

I CAN DO:

cnx = create_engine('mssql+pymssql://@server/database')
df.to_sql(name = name, con=cnx, if_exists = 'append', index=False)

I CANNOT DO:

cnx = create_engine('mssql+pyodbc://@server/database')
df.to_sql(name = name, con=cnx, if_exists = 'append', index=False)

I get the following error message:

InterfaceError: (pyodbc.InterfaceError) ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')(Background on this error at: http://sqlalche.me/e/rvf5)

Inerestingly, the same thing happens when I try 

cnx = create_engine('mssql+turbodbc://@server/database')

Clearly, something in the way I set up my engine and try to connect to the database fails.

corr
  • 73
  • 1
  • 7
  • The most common approach (preferred by SQLAlchemy) for `mssql+pyodbc` is to create an ODBC DSN and then use `mssql+pyodbc://@dsn_name` – Gord Thompson Jun 03 '19 at 11:35
  • Well, I don't have a dsn. I've been given access to the database by someone else and I wouldn't know how to create a dsn. Also, any idea why it's not working dsn-less? – corr Jun 03 '19 at 11:42
  • https://docs.sqlalchemy.org/en/13/dialects/mssql.html#hostname-connections – Gord Thompson Jun 03 '19 at 12:06
  • Thanks, this was actually really helpful (specifiying the driver solved my problem for both ```pyodbc``` and ```turbodbc```. Can't belive I was unable to find that myself! Thanks and best, – corr Jun 06 '19 at 08:11

0 Answers0