Construct SQL query string from list of values in efficient way in Python 3.7

Question

I am new in Python. In my current python project I am constructing a SQL query string as follows:

sql = f'INSERT INTO my_table (col1, col2) VALUES'

val_str = ''
num_rows_updated_in_curr_batch = 0;
for param in params :
    val_str += str(param) +','    ##params = [(v1, v2),(v3, v4)...]
    num_rows_updated_in_curr_batch += 1
    if num_rows_updated_in_curr_batch == 1000:
       val_str = val_str.strip(',')
       curr_sql_query = sql + val_str
       val_str = ''

Basically in each batch, I want SQL query string as follows:

INSERT INTO my_table (col1, col2) VALUES (v1,v2),(v3,v4)

Please suggest if there is any better way to do this.

Nick · Accepted Answer · 2020-03-05 06:26:51Z

3

You can use a list comprehension on an enumeration of params to get individual tuples and their index, and then join all the values where the index is less than 1000:

sql = 'INSERT INTO my_table (col1, col2) VALUES '

params = [(4, 6),(3, 7),(1,8)]

sql += ','.join([str(p) for i, p in enumerate(params) if i < 1000])
print(sql)

Output:

INSERT INTO my_table (col1, col2) VALUES (4, 6),(3, 7),(1, 8)

To process params in batches, you can loop over the size of the batch you want:

sql = 'INSERT INTO my_table (col1, col2) VALUES '
params = [(4, 6),(3, 7),(1,8)]
batchsize = 1000
for batch in range(0, len(params), batchsize):
    curr_sql_query = sql + ','.join([str(p) for i, p in enumerate(params) if batch <= i < batch + batchsize])
    print(curr_sql_query)

edited Mar 5, 2020 at 6:26

answered Mar 5, 2020 at 4:56

Nick

147k23 gold badges67 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Joy Over a year ago

thanks a ton for your response. I have one small doubt, here I would like to process params list 1000 in one batch. I am thinking how will I accommodate this in my use case as I have to update sections of params list I am processing somehow.

Nick Over a year ago

@Joy is the params list longer than 1000 elements?

Joy Over a year ago

Yes @Nick this will contain more than 1000 elements.

Nick Over a year ago

@Joy please see my edit, that will generate queries for a batch of 1000 (or whatever you set the batchsize variable to) at a time

Collectives™ on Stack Overflow

Construct SQL query string from list of values in efficient way in Python 3.7

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related