7

I'm using SQL Magic to connect to a db2 instance. However, I can't seem to find the syntax anywhere on how to close the connection when I'm done querying the database.

user3495958
  • 71
  • 1
  • 3

6 Answers6

2

you cannot explicitly close a connection using Jupyter SQL Magic. In fact, that is one of the shortcoming of using Jupyter SQL Magic to connect to DB2. You need to close your session to close the Db2 connection. Hope this helps.

koya
  • 21
  • 2
2

This probably isn't very useful, and to the extent it is it's probably not guaranteed to work in the future. But if you need a really hackish way to close the connection, I was able to do it this way (for a postgres db, I assume it's similar for db2):

In[87]: connections = %sql -l
Out[87]: {'postgresql://ngd@node1:5432/graph': <sql.connection.Connection at 0x7effdbcf6b38>}
In[88]: conn = connections['postgresql://ngd@node1:5432/graph'] 
In[89]: conn.session.close()
In[90]: %sql SELECT 1
...
StatementError: (sqlalchemy.exc.ResourceClosedError) This Connection is closed
[SQL: SELECT 1]
[parameters: [{'__name__': '__main__', '__doc__': 'Automatically created module for IPython interactive environment', '__package__': None, '__loader__': None, '__s ... (123202 characters truncated) ... stgresql://ngd@node1:5432/graph']", '_i28': "conn = connections['postgresql://ngd@node1:5432/graph']\nconn.session.close()", '_i29': '%sql SELECT 1'}]]

A big problem is--if you want to reconnect, that doesn't seem to work. Even after running %reload_ext sql, and trying to connect again, it still thinks the connection is closed when you try to use it. So unless someone knows how to fix that behavior, this is only useful for disconnecting if you don't want to re-connect again (to the same db with the same params) before restarting the kernel.

reductionista
  • 341
  • 1
  • 2
  • 6
0

You can also restart the kernel.

JGarcia
  • 37
  • 5
0

This is the most simple way I've found to close all connections at the end of the session. You must restart the kernel to be able to re-establish the connection.

connections = %sql -l
[c.session.close() for c in connections.values()]
Lorinc Nyitrai
  • 936
  • 1
  • 10
  • 26
0

sorry for being to late but I've just started with working with SQL Magic and got annoyed with the constant errors appearing. It's a bit of a awkward patch but this helped me use it.

def multiline_qry(qry):
    try:
        %sql {qry}
    except Exception as ex:
        if str(type(ex).__name__) != 'ResourceClosedError':
            template = "An exception of type {0} occurred. Arguments:\n{1!r}"
            message = template.format(type(ex).__name__, ex.args)
            print (message)
    
qry = '''DROP TABLE IF EXISTS EMPLOYEE;
CREATE TABLE EMPLOYEE(firstname varchar(50),lastname varchar(50));
INSERT INTO EMPLOYEE VALUES('Tom','Mitchell'),('Jack','Ryan');
'''

multiline_qry(qry)
KrisG
  • 146
  • 6
-1

log out the notebook first if you want to close the connection.

Odd Zhang
  • 19
  • 2