SQLAlchemy connection error with Db2 on IBM Cloud

I've been trying to connect to the IBM DB2 server hosted on IBM cloud using the provided credentials. I followed the instruction but i received following error :

Connection info needed in SQLAlchemy format, example: postgresql://username:password@hostname/dbname or an existing connection: dict_keys([]) Can't load plugin: sqlalchemy.dialects:ibm_db_sa Connection info needed in SQLAlchemy format, example: postgresql://username:password@hostname/dbname or an existing connection: dict_keys([])

Here is my code that i used :

# Remember the connection string is of the format:
# %sql ibm_db_sa://my-username:my-password@my-hostname:my-port/my-db-name
# Enter the connection string for your Db2 on Cloud database instance below
# i.e. copy after db2:// from the URI string in Service Credentials of your Db2 instance. Remove             the double quotes at the end.
%sql ibm_db_sa://user:[email protected]:50000/BLUDB

I would appreciate if you help me with this error.

Solution 1:

Check out the IBM Cloud solution tutorial on Combining serverless and Cloud Foundry for data retrieval and analytics. It uses Db2 and the backend is written in Python and SQLAlchemy. The relevant part starts in line 78:

if dbInfo['port']==50001:
    # if we are on the SSL port, add additional parameter for the driver
    app.config['SQLALCHEMY_DATABASE_URI']=dbInfo['uri']+"Security=SSL;"
else:
    app.config['SQLALCHEMY_DATABASE_URI']=dbInfo['uri']

The code reads the URI from the credentials variable in the Cloud Foundry environment. If the port is 50001, it uses SSL/TLS for encrypting the connection and you need to add Security=SSL to that URI.

Also, make sure that your code has the Db2 modules for SQLAlchemy installed. See requirements.txt for the dependencies.

Solution 2:

You can try this:

!pip install --upgrade ibm_db
!pip install --upgrade ibm_db_sa
!pip install --upgrade SQLAlchemy

import ibm_db
import ibm_db_sa
import sqlalchemy

%load_ext sql

And then:

%sql ibm_db_sa://user:[email protected]:50000/BLUDB