Hello! I could not figure it out and nowhere I see the answer. The situation is this. I form the string that I need to add to the database later, in the form of a list. But now how to formulate a request to add this list to the database?

Everything seems clear when I have a constant number of columns in the table, for example:

list = [1, 10, 15, 37] c.execute('INSERT INTO table VALUES (%s,%s,%s,%s)' % tuple(list) )

But what if I do not know in advance the number of columns? That is, in the course of the program, the number of columns may increase. How then to make a request?

I got out of the situation this way:

 for i in range(len(list)): list[i] = str(list[i]) list_text = ', '.join(list) c.execute('INSERT INTO chrom VALUES (%s)' % list_text)

Well, that is, I just riveted the request as text.

 >>>print(list_text) "1, 10, 15, 37"

It seems to me that this crutch is completely indecent, and move through one place. Can you please tell me how to do it according to your mind?

  • By the way, in most languages ​​such a method would not be suitable, because the driver would see one parameter and the values ​​would require a lot. Alternatively, make the character string '%s'+',%s'*(длина_списка-1) and substitute its values ​​in values. (as I understand it in python, strings can be multiplied like this by multiplication) - Mike
  • @kff: this is not a normal practice. Formatting specific values ​​in a sql query leads to SQL injection. xkcd.com/327 - jfs

1 answer 1

Do not use string formatting values ​​when creating queries . This can lead to SQL injection .

Use parameterized queries instead. For example, do not do this:

 c.execute('INSERT INTO table VALUES (%s,%s,%s,%s)' % tuple(list_)) #XXX DON'T DO IT

Do this:

 c.execute('INSERT INTO table VALUES (?,?,?,?)', list_)

The specific language for the parameters depends on the database driver ( sqlite3 understand ? ).

If the number of columns is not known in advance (which indicates a possible problem with the data model), then you can create a template: ','.join('?' * len(list_)) :

 c.execute('INSERT INTO table VALUES (%s)' % ','.join('?' * len(list_)), list_)

Do not use built-in names (such as list ) for your variables - this makes reading difficult.

  • Thank you for the answer, and I’ll do it) - Vladimir