CODEWITHSUNDEEP
pythonsqlcx-oracle
Avangard
Avangard

Reputation: 31

Error: maximum number of expressions in a list is 1000, how can I modify the code below to take more than 1000 records?

I have the code below and I know I could be probably close, but I still get the error that I have exceded the 1000.

I added the generate_sql_in_binds function but I guess I don't know how to pass it properly.

Upvotes: 0

Views: 104

Answers (2)

nbk
nbk

Reputation: 49385

as you can't make temporary tables, you could break it into chunks of 1000 and construct a SELECT like (this is not fast and you should think about if you actually need so much elements):

import mysql.connector as sq

con = sq.connect(host="localhost",
                 user="root",
                 password="****",
                 database="testdb")

def chunks(xs, n):
    n = max(1, n)
    return (xs[i:i+n] for i in range(0, len(xs), n))

df = [1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22]
ldf = chunks(df,10)
sqlstr = ""
for index, item in enumerate(ldf, start=0):   # default is zero
    if (index >0):
        sqlstr += """ UNION ALL"""
    sqlstr += """ SELECT * FROM testdb WHERE id IN (""" + ','.join(map(str,item)) + """)"""
sqlstr +=  ';'
print(sqlstr)

result is:

 SELECT * FROM testdb WHERE id IN (1,2,3,4,5,6,7,8,9,10) UNION ALL SELECT * FROM testdb WHERE id IN (11,12,13,14,15,16,17,18,19,20) UNION ALL SELECT * FROM testdb WHERE id IN (21,22);

Upvotes: 0

emmanuelFV
emmanuelFV

Reputation: 1

Here is needed more context about 'hour' and 'unitid' lists. However the best approach is to keep them in another table in DB. In the case what you are worried about the perfomance of using joins you can also add sub queries in where needed. Example:

query = """SELECT HOUR, UNITSCHEDULEID, VERSIONID, MINRUNTIME
FROM  int_Stg.UnitScheduleOfferHourly
WHERE Hour in (select hour in hours_table)
AND   UnitScheduleId in (select unitid from unitids_table)"""

Upvotes: 0

Related Questions