SQLalchemy with pytds

[ifuchs]

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

however when I 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)

How to get sqlalchemy and the sql extension to work?

P.S. I posted this question to the sqlalchemy list but they bounced me to here :-)

[takoau]

@ifuchs With autocommit=True in iPython-sql, a commit is issued (Line 345 in https://github.com/catherinedevlin/ipython-sql/blob/59de1b681009a4cf285d537f30cb904aa19d512b/src/sql/run.py) after select statement and that's the one which not produce any results. Solution is to turn autocommit off:

In [10]: %config SqlMagic.autocommit=True

In [11]: %sql select 1 + 1 In _execute : select 1 + 1 In _execute : commit Done. (pytds.tds_base.ProgrammingError) Previous statement didn't produce any results (Background on this error at: https://sqlalche.me/e/14/f405)

In [12]: %config SqlMagic.autocommit=False

In [13]: %sql select 1 + 1 In _execute : select 1 + 1 Done. Out[13]: [(2,)]

[ifuchs]

@takoau Thanks very much. Works now and you saved me a lot of debugging.

[takoau]

@ifuchs No problem. It's a tricky one as it involves sqlalchemy, pytds and ipython-sql. Actually the problem is in the last one. It's fair for sqlalchemy guys ask you to put the question somewhere else. 😃

[takoau]

@ifuchs mind to close this issue if you're happy with the solution?