CODEWITHSUNDEEP
pythondb2database-connectionibm-cloud
user3795105
user3795105

Reputation: 69

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.

Upvotes: 0

Views: 6529

Answers (4)

Pradyumn Tiwari
Pradyumn Tiwari

Reputation: 11

!pip uninstall sqlalchemy==1.4 -y && pip install sqlalchemy==1.3.24

%load_ext sql

Use this prior to running your query and your query will work.

Upvotes: 0

Alysson Correia Pedra
Alysson Correia Pedra

Reputation: 1

I had similar issues. Here is what worked for me:

Try to install the SQLAchemy version 1.4 before you run the SQL using

pip install --upgrade 'SQLAlchemy<1.4'

Be sure to install the IBM DB2 Python Libraries as follows:

!pip install --force-reinstall ibm_db ibm_db_sa

Upvotes: 0

nlpvtzz
nlpvtzz

Reputation: 31

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

Upvotes: 1

data_henrik
data_henrik

Reputation: 17176

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.

Upvotes: 1

Related Questions