How to pass mutiple variables to the cx_Oracle query code

LynChen 0
Viewed 31

When I choosed python--cx_Oracle to query data, I can get correct query result when the variable only one number or str, code as below

sql = cursor.execute('''
select A.NUMBER,A.NAME
from table_a A 
where A.NUMBER in :alist
''', alist='12345a')

Now, I'd like to query many datas(pass many numbers/str in to 'alist'), I dont's know how to achieve my proposal. Below code didn't work: (cx_Oracle.NotSupportedError: Python value of type tuple not supported.)

sql = cursor.execute('''
select A.NUMBER,A.NAME
from table_a A 
where A.NUMBER in :alist
''', alist=('12345a','12345b'))

P.S.: I know in Oracle, I should query like this:

select A.NUMBER, A.NAME from table_a A where A.NUMBER in ('12345a','12345b','12345c')

Anybody can help me to modify my code? thank you in advance :)

cx-oracle oracle python
1 Answers

I've solved this by "cut all data into several LIST with 1000 element in each". pass those LIST one by one, then concat all result after query finished.:

bind_values = RAWdata_list
for i in range(0,int(len(bind_values))//1000+1):
    bind_values_i = bind_values[i*1000:(i+1)*1000-1]
    bind_names_i = [":" + str(i + 1) for i in range(len(bind_values_i))]

    sql = '''
        select A.NUMBER,A.AGE from table_a A
        where A.NUMBER in (%s)
    ''' %(",".join(bind_names_i))
    cursor.execute(sql, bind_values_i)
    RAW = cursor.fetchall()
    df_RAW_i = pd.DataFrame(RAW, columns=col)

    if i<1:
        df_all = df_RAW_i
    else:
        df_all = pd.concat([df_all,df_RAW_i])

df_all.to_excel('RAW.xlsx', index=False)
LynChen0
Related