Inserting 200 variables in a string stored in list

Question

I have a list of variables as:

listV = ['v1, 'v2', ...,'v200']

I want to do something of the following sort in a cleaner way:

q1 = 'insert into myTable ({}, {}, {},....{})'.format(listV)

My desired output will be:

q1 = 'insert into myTable (v1, v2, v3, ...., v200)'

For fewer number of variables, I am able to write a cleaner code but with 200 variables I am having difficulty creating cleaner code.

I need to create the q1 dynamically as the content of listV changes but the length of listV stays constant.

Obligatory [Bobby Drop Tables](https://xkcd.com/327/). You should use [parameterized](https://stackoverflow.com/questions/775296/mysql-parameterized-queries/775344#775344) [queries](https://stackoverflow.com/questions/1633332/how-to-put-parameterized-sql-query-into-variable-and-then-execute-in-python) or you're leaving yourself open to [SQL-injection attacks](https://stackoverflow.com/questions/601300/what-is-sql-injection). — pault, Jun 04 '19 at 14:48
If I'm guessing correctly, you're trying to construct an SQL query. (If so, please confirm. If not, then ignore this comment.) This looks like an [xy problem](http://xyproblem.info/). You seem to be looking for direct/raw formatting, but there are better, easier, safer ways to construct SQL queries. — TrebledJ, Jun 04 '19 at 15:03

score 3 · Accepted Answer · answered Jun 04 '19 at 14:49

3

>>> listV = ['v1', 'v2', 'v200']
>>> q1 = 'insert into myTable'
>>> q1 = q1 + ' (' + ', '.join(listV) +  ')'
>>> q1
'insert into myTable (v1, v2, v200 )'

answered Jun 04 '19 at 14:49

Supratim Haldar

1,976
2
12
24

Olvin Roght · Answer 2 · 2019-06-04T14:59:45.247

2

listV = ['v1', 'v2', 'v200']
q1 = "insert into myTable ({})".format(", ".join(listV))

edited Jun 04 '19 at 14:59

answered Jun 04 '19 at 14:55

Olvin Roght

5,080
2
11
27

score 1 · Answer 3 · answered Jun 04 '19 at 14:47

1

To save yourself some trouble, you can create the format string programatically as well.

listV = ["V1", "V2", "V3"]

n = len(listV)

fstring = "insert into myTable (" + "{}, " * (n-1) + "{})"

q1 = fstring.format(*listV) 
# The * unpacks the items in listV into separate arguments
print(q1)
# >>> insert into myTable (V1, V2, V3)

answered Jun 04 '19 at 14:47

SyntaxVoid supports Monica

2,163
2
13
21

or just call `format` in resulted string `"".format(*listV)` – Olvin Roght Jun 04 '19 at 14:50

score 1 · Answer 4 · answered Jun 04 '19 at 15:00

That's the way I can think of....

my_list = [1,2,3]
q = "insert into mytable ("
for i in range(0, len(my_list), 1):
    currNum = my_list[i]
    if i != len(my_list) - 1:
        q += str(currNum) + ","
    else:
        q += str(currNum)
q += ")"
print(q)

I'm a Python newbie -_-

Kent Martin · Answer 5 · 2019-06-04T15:21:02.313

listV = [i for i in range(200)]
q1 = str("insert into myTable (" + "{}, "*(len(listV)-1) + "{})").format(*listV)

As others have mentioned, string-formatting sql-queries leaves your database vulnerable for SQL-injections.

EDIT Olvyn Roght had a very nice answer! Here is an alternative version that also works with integers:

listV = [i for i in range(200)]
q1 = "insert into myTable ({})".format(str(listV)[1:-1])

Inserting 200 variables in a string stored in list

5 Answers5