Good Afternoon,
I though my response would be a good addition to this discussion. You can see from the previous answers that there was no discusion on how to handle a list of values which would go into the IN clause of the query. Hope this version of the answer also helps.
import oracledb
oracledb.version = "8.3.0"
import sys
sys.modules["cx_Oracle"] = oracledb
from sqlalchemy.engine import create_engine
from sqlalchemy import text
from sqlalchemy import bindparam
import pandas as pd
def makeOraEngine():
global oraEngine
oracledb.init_oracle_client(lib_dir=r'C:\app\Oracle\product\21.0.0\client_1')
DIALECT = 'oracle'
DRIVER = f'{DIALECT}db'
USER = 'user'
PASSWORD = 'password'
DSN = 'dsn'
url = f'{DIALECT}+{DRIVER}://:@'
params = {'param1':['PREF1','PREF2','PREF3'],'param2':[2,3]}
oraEngine = create_engine( url, connect_args={"user": USER,"password": PASSWORD,"dsn": DSN})
sql_text = "select well_name, well_prefix, bore_number from well_laterals where well_prefix in :param1 and bore_number in :param2
order by well_prefix, bore_number"
qry =text(sql_text).bindparams(
bindparam('param1', expanding=True),
bindparam('param2', expanding=True)
)
df = pd.read_sql(qry, con=oraEngine, params=params)
print(df)
if __name__=='__main__':
makeOraEngine()