[Python] How to fix SQLAlchemy connection problem: 'Connection info needed in SQLAlchemy...

So, I've been trying to connect to the IBM DB2 server hosted on IBM cloud for the past few days and managed to connect to it using the provided credentials and the 'ibm_db', 'ibm_db_sa', and the 'ibm_db_dbi' module. However, when I imported SQL magic and attempted to connect to the server, it failed.

I have tried a total of 3 methods: The IBM recommended method, and some other methods I found on the internet, which sadly, failed.

Method 1 (IBM recommended):

import ibm_db
import ibm_db_sa
import sqlalchemy
from sqlalchemy import *
%load_ext sql

%sql ibm_db_sa://qcf54xxx:qz^d5stlkbr6lxxx@https://dashdb-txn-sbox-yp-dal09-03.services.dal.bluemix.net:50000/BLUDB


Error:

Connection info needed in SQLAlchemy format, example:
postgresql://usernameassword@hostname/dbname
or an existing connection: dict_keys([])
invalid literal for int() with base 10: ''
Connection info needed in SQLAlchemy format, example:
postgresql://usernameassword@hostname/dbname
or an existing connection: dict_keys([])


Method 2:

from sqlalchemy import create_engine
engine = create_engine('ibm_db_sa://qcf54xxx:qz^d5stlkbr6lxxx@dashdb-txn-sbox-yp-dal09-03.services.dal.bluemix.net:50000/BLUDB')


Error:

Invalid Syntax


Method 3:

import sqlalchemy
from sqlalchemy import *
import ibm_db_sa
db2 = sqlalchemy.create_engine('ibm_db_sa://qcf54xxx:qz^d5stlkbr6lxxx@dashdb-txn-sbox-yp-dal09-03.services.dal.bluemix.net:50000/BLUDB')
metadata = MetaData()


Error:

Invalid Syntax


The method that succeeded (The method without SQLAlchemy)[Just for your references]:

import ibm_db
import ibm_db_sa
import ibm_db_dbi
import pandas
#Connects to the IBM database
dsn_hostname = "dashdb-txn-sbox-yp-dal09-03.services.dal.bluemix.net" # e.g.: "dashdb-txn-sbox-yp-dal09-04.services.dal.bluemix.net"
dsn_uid = "qcf54xxx" # e.g. "abc12345"
dsn_pwd = "qz^d5stlkbr6lxxx" # e.g. "7dBZ3wWt9XN6$o0J"

dsn_driver = "{IBM DB2 ODBC DRIVER}"
dsn_database = "BLUDB" # e.g. "BLUDB"
dsn_port = "50000" # e.g. "50000"
dsn_protocol = "TCPIP" # i.e. "TCPIP"

#DO NOT MODIFY THIS CELL. Just RUN it with Shift + Enter
#Create the dsn connection string
dsn = (
"DRIVER={0};"
"DATABASE={1};"
"HOSTNAME={2};"
"PORT={3};"
"PROTOCOL={4};"
"UID={5};"
"PWD={6};").format(dsn_driver, dsn_database, dsn_hostname, dsn_port, dsn_protocol, dsn_uid, dsn_pwd)

#print the connection string to check correct values are specified
print(dsn)

#DO NOT MODIFY THIS CELL. Just RUN it with Shift + Enter
#Create database connection

try:
conn = ibm_db.connect(dsn, "", "")
print ("Connected to database: ", dsn_database, "as user: ", dsn_uid, "on host: ", dsn_hostname)

except:
print ("Unable to connect: ", ibm_db.conn_errormsg() )


I expect to be able to use %sql to manipulate the database.

Thank you!

P.S. If I sounded rude or have offended you somehow, please understand that English is not my mother tongue.

Continue reading...