Connect to IBM DB2 on Cloud using python api ibm_db

0
votes

Can you please let me know how to connect to DB2 on IBM Cloud using python?

I have tried the below steps.

  1. installed ibm_db using pip install ibm_db
  2. Created a free tier Db2 service on IBM cloud
  3. Generated Service credentials key
  4. Trying to establish a connection with the database with the credential details (Database, host, port, user id, and password) extracted from the Service credentials key
import ibm_db

print("Creating connection.......")

conn_string = "DATABASE=bludb;HOSTNAME=54a2f15b-5c0f-46df-8954-7e38e612c2bd.c1ogj3sd0tgtu0lqde00.databases.appdomain.cloud;PORT=32733;PROTOCOL=TCPIP;UID=<userId>;PWD=<password>;"

conn = ibm_db.connect(conn_string,"","")
if conn:
    print("Connection ...... [SUCCESS]")
else:
    print("Connection ...... [FAILURE]")

I am getting below error message:

SQLCODE=-30082n: [IBM][CLI Driver] SQL30082N Security processing failed with reason "17" ("UNSUPPORTED FUNCTION"). SQLSTATE=08001

3
pythondb2ibm-cloud
Please provide details on the type of credentials you have. Do they already include a database URI for SSL? Use that as input to your connect command. ibm_db.connect(ssldsn, "", "") - data_henrik
The port and hostname details I got from the service credentials key. - Ashis Sahoo

3 Answers

2
votes

It seems like you are on the new Db2 on Cloud lite plan with non-standard ports and SSL enforced. When you connect to Db2 using the Python driver and use SSL, you have to add the SECURITY=SSL property, e.g.:

conn_string = "DATABASE=bludb;HOSTNAME=yourhostname;PORT=<port>;PROTOCOL=TCPIP;UID=<userId>;PWD=<password>;SECURITY=SSL"
1
votes

I think the easy way to do this is with SQL Magic. This way, you can just type a SQL statement just by adding %sql before your query

First install the packages

!pip install sqlalchemy==1.3.9
!pip install ibm_db_sa

Then,

%load_ext sql

Finally, run the following code by replacing your username, password, hostname and SSL. You can find these in your IBM DB2 under credentials.

%sql ibm_db_sa://my-username:my-password@hostname:port/BLUDB?security=SSL

Now you can run any SQL by using %sql before or %%sql if the whole cell is going to be SQL. Example:

%sql SELECT * FROM TABLENAME;

or

%%sql 
SELECT *
FROM TABLENAME;
0
votes

There's a python package called ibm_db, does this link or (cited in the first one) this one help?