search

catherinedevlin / ipython-sql

%%sql magic for IPython, hopefully evolving into full SQL client
MIT License
1.79k stars 368 forks source link

Sqlalchemy with pytds #220

Open ifuchs opened 1 year ago

ifuchs commented 1 year ago

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 commented 1 year ago

@ifuchs Try to disable the autocommit option

%config SqlMagic.autocommit=False