Sqlalchemy with pytds

[ifuchs]

I have Jupyter notebook in which I am able to access using pytds. I would like to use it with sqlalchemy and the %%sql command.

however when try the following:
sqlalchemy.create_engine('mssql+pytds://user:pw@ip/db')
I get:
Engine(mssql+pytds://user:***@ip/db)
and then I do %load_ext sql
and
%%sql

Select ...

The result is : Done.
(pytds.tds_base.ProgrammingError) Previous statement didn't produce any results
(Background on this error at: https://sqlalche.me/e/14/f405)

I know the select produces a result if I just do:
import pytds
with pytds.connect('ip, 'db', 'sa', pw as conn:
with conn.cursor() as cur:
cur.execute("SELECT ...")
a = cur.fetchall()
print(a)

In order to eliminate any possibility of an SQL syntax error, I tried this:
%%sql mssql+pytds://userid:password@serverip:1433/dbname
SELECT * from sysobjects where xtype = 'U'

and once again I got:
Done.
(pytds.tds_base.ProgrammingError) Previous statement didn't produce any results
(Background on this error at: https://sqlalche.me/e/14/f405)

How to get sqlalchemy and the sql extension to work?

[yafimvo]

@ifuchs
Try to disable the autocommit option

%config SqlMagic.autocommit=False