I am encountering errors when trying to use the pd.to_sql function to write
a dataframe to MS SQL Data Warehouse.
The connection works when NOT using sqlalchemy engines.
I can read dataframes as well as row-by-row via select statements when I
use pyodbc connections
I can write data via insert statements (as well as delete data) when using
pyodbc.
However, when I try to connect using a sqlalchemy engine I run into a
string of error messages starting with:
ProgrammingError: (pyodbc.ProgrammingError) ('42000', "[42000] [Microsoft][ODBC
Driver 13 for SQL Server][SQL Server]Catalog view 'dm_exec_sessions' is not
supported in this version. (104385) (SQLExecDirectW)")
I have searched online, and this exact error seems to have been reported /
evaluated in May of this year as issue #3994:
https://bitbucket.org/zzzeek/sqlalchemy/issues/3994/azure-sql-datawarehouse-basic
I could not find a solution to this, and I'd really dislike to do a line-wise
or blob insert statement (I'm working with multiple datasets that each has a
few million rows, so execution time is a consideration, although the result
sets I'm getting are more like in the 100k lines area each.)
I get the same error messages even when I replace the pd.to_sql command with a
simple engine.connect()
Enclosed my installed packages (packages.list)
Enclosed the full traceback (traceback.txt)
This is the code I'm using:
connection_string =
"mssql+pyodbc://<username>:<password>@<sqlhost>.database.windows.net:<port>/<database>?driver=ODBC+Driver+13+for+SQL+Server"
engn = sqlalchemy.engine.create_engine(connection_string, echo=True)
engn.connect()
I'm very well aware that MS SQL DataWarehouse behaves a bit different, so
I'm open for some experimenting to get this issue narrowed down.
In case it matters: I'm running an ubuntu 16.04 VM on azure with jupyter
notebook server and python 3.6.1.
Best,
DB
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
