8

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
  • 81
  • 1
  • 3

7 Answers7

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
  • 406
  • 1
  • 4
  • 7
0

You can also restart the kernel.

JGarcia
  • 57
  • 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
  • 968
  • 1
  • 10
  • 27
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
  • 166
  • 7
0

You can use the 'url' attribute of the connection object. Here are the steps to close the server connection:

    # save the available connections to a dictionary
    connection_dict = %sql --connections 

    # get the connection string from the dictionary
    connection_string = list(connection_dict.keys())[0]

    # get the connection object from the dictionary using the connection string
    connection_object = connection_dict[connection_string]

    # close the connection using the connection object's url attribute
    %sql --close $connection_object.url
Ben Kaan
  • 51
  • 2
-1

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

Odd Zhang
  • 19
  • 2