How to pass mutiple variables to the cx_Oracle query code-CodePudding

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 :)

CodePudding user response：

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)