Connection info needed in SQLalchemy format for ibm_db_sa

[swaroop-k]

Hello,

I am trying to connect to IBM db2 from the notebook in IBM watson studio itself.

I am connecting with the proper format: (%sql ibm_db_sa://my-username:my-password@my-hostname:my-port/my-db-name?security=SSL)

%sql ibm_db_sa://jl9:ReG*****kkz@ea286ace--4d5b-8******************od8lcg.data bases.appdomain.cloud:35/bludb?security=SSL

Still it is not connecting and prompting the error ::

Connection info needed in SQLAlchemy format, example: postgresql://username:password@hostname/dbname or an existing connection: dict_keys([])

[idomic]

@swaroop-k are you sure the pip packages installed correctly?

Try importing these, is it passing?

import ibm_db
import ibm_db_sa
import ibm_db_dbi

FYI @edublancas

[edublancas]

in those cases, it's usually easier to ensure you can establish a connection using create_engine and then pass the engine object:

https://jupysql.ploomber.io/en/latest/howto.html#connect-to-existing-engine

[vvujica1]

Hi,

To Connect Db2 to IBM Watson Studio, in the project you should add "new asset" select Db2 Cloud and complete credentials. Make sure that location is set same as Db2 (London, Dalas...). Once successfully connected, you can add your credentials and query to the notebook by clicking up right (icon looks like 0010).

I think that other kind of connections wouldn't work.

[edublancas]

can you try connecting via create_engine? see the example here:

import sqlalchemy
from sqlalchemy import *
import ibm_db_sa.ibm_db_sa
db2 = sqlalchemy.create_engine('ibm_db_sa://user:[email protected]:50000/database')
metadata = MetaData()
users = Table('STAFF', metadata, 
Column('ID', Integer, primary_key = True),
Column('NAME', String(9), nullable = False),
Column('DEPT', Integer, nullable = False),
Column('JOB', String(5), nullable = False)
)

if that works, you can pass the engine object. e.g, if you do:

db2 = sqlalchemy.create_engine('ibm_db_sa://user:[email protected]:50000/database')

then:

%sql db2

%sql SELECT * FROM ...

Note that this feature is only available on JupySQL, it won't work on ipython-sql:

pip uninstall ipython-sql 
pip install jupysql