0

Let's say i have a python list of customer id like this:

id = ('12','14','15','11',.......)

the array has 1000 values in it, and i need to insert the customer name to a table based on the ids from the list above.

my code is like:

ids = ",".join(id)
sql = "insert into cust_table(name)values(names)where cust_id IN('ids')"
cursor.execute(sql)

after running the code, i get nothing inserted to the table. What mistake do i have?

Please help :(

Ardi Tan
  • 142
  • 3
  • 16

2 Answers2

0

You need to format the string.

ids = ",".join(id)
sql = "insert into cust_table(name)values(names)where cust_id IN('{ids}')"
cursor.execute(sql.format(ids= ids))
Nf4r
  • 109
  • 3
  • 2
    parameterized sql would be even better. see for example http://stackoverflow.com/questions/8671702/passing-list-of-parameters-to-sql-in-psycopg2 –  Oct 04 '16 at 14:04
0

Simply writing the name of a variable into a string doesn't magically make its contents appear in the string.

>>> p = 'some part'
>>> s = 'replace p of a string'
>>> s
'replace p of a string'
>>> s = 'replace %s of a string' % p
>>> s
'replace some part of a string'
>>> s = 'replace {} of a string'.format(p)
>>> s
'replace some part of a string'

In your case this would mean:

>>> sql = "insert into cust_table (name) values (names) where cust_id IN ('%s')"
>>> ids = ", ".join(id)
>>> cursor.execute(sql % ids)

although I strongly suspect that you have a similar problem with names.

In order to avoid possible sql injection problems, it would be preferable to use a "parameterized statement". This would look something like:

>>> sql = 'insert into ... where cust_id IN %s'
>>> cursor.execute(sql, (id,))

Some database connectors for python are capable of this, but yours probably isn't.

A workaround might be something like

>>> params = ', '.join(['%s']*len(id))
>>> sql = 'insert into ... where cust_id IN (%s)' % params
>>> cursor.execute(sql, id)