How do I set current schema by using ibm_db_dbi to connect database?

Question

I've been trying to use ibm_db_dbi to connect the database and read table by pandas.read_sql.

After creating the connection, I use set_current_schema('mySchema') to set the current schema for myConnection, so that I don't need to specify the schema in front of every tables.

However, when I read table by using pd.read_sql(sql, myConnection), it always uses the username as the schema, not the scheme that I specified.

Is there a way to fix it?

Any help would be appreciated.

Thanks,

Terry

import ibm_db_dbi as db2
import pandas as pd

myConnection = db2.connect("DATABASE=name;HOSTNAME=host;PORT=port;PROTOCOL=TCPIP;UID=username;PWD=password;",  " ", " ")

myConnection.set_current_schema('mySchema')

sql = ('SELECT NAME FROM EMPLOYEE')

names = pd.read_sql(sql, myConnection)

mao · Accepted Answer · 2020-06-18T06:03:09.777

You can extend the connection-string with the ;CURRENTSCHEMA=MYSCHEMA;

This works for me with pandas and Db2.

Example

myConnection = db2.connect("DATABASE=name;HOSTNAME=host;PORT=port;PROTOCOL=TCPIP;UID=username;PWD=password;CURRENTSCHEMA=MYSCHEMA;",  " ", " ")

For people who use a DSN (instead of a connection string in the script), you can include this additional information as a database parameter (and DSN parameter) in your db2dsdriver.cfg file.

How do I set current schema by using ibm_db_dbi to connect database?

1 Answers1