Insert python list into Postgres database

Question

I am having trouble in formatting the list for insertion using psycopg. Here is a sample of code i am trying to do. Basically I am just reading data from one table and trying to insert it into another table.

Code:

cur.execute("""select data from \"Table1\" where lat=-20.004189 and lon=-63.848004""")
rows = cur.fetchall()
print rows
cur.execute("""INSERT INTO \"%s\" (data) VALUES (ARRAY%s)""" % (args.tableName,rows)))

The result returned by first select query is like this:

[([6193, 3975, 4960, 5286, 3380, 970, 3328, 3173, 2897, 2457, 2443, 2674, 2172, 2740, 3738, 4907, 3691, 4234, 3651, 3215],)]

When I try to insert this into another table I get the following format error.

   cur.execute(cur.mogrify("""INSERT INTO \"%s\" (data) VALUES (%s)""" % (args.tableName,rows)))
psycopg2.ProgrammingError: syntax error at or near "["
LINE 1: INSERT INTO "DUMMY1km" (data) VALUES ([([6193, 3975, 4960, 5...

I tried cur.mogrify, but it does not seem to help.

Please let me know if anyone has a work around for this issue.

Thanks Adi

Column data is an array, the syntax for the value is invalid as already diagnosed in this duplicate question. Where I could not help: better python syntax to create valid syntax. — Erwin Brandstetter
– Erwin Brandstetter, Commented Oct 13, 2011 at 21:51
Yeah, I was just trying out different ways of sending the array as a parameter. This was just one of the attempts.! :) — Aditya
– Aditya, Commented Oct 13, 2011 at 22:41

Community · Accepted Answer · 2017-05-23 12:14:56Z

8

I don't think mogrify is needed here. Use executemany and pass rows as the second argument.

cur.executemany(
    """INSERT INTO "%s" (data) VALUES (%%s)""" % (args.tableName),rows)

Using parametrized arguments helps prevent SQL injection.

The table name can not be parametrized, so we do have to use string interpolation to place the table name in the SQL query. %%s gets escapes the percent sign and becomes %s after string interpolation.

By the way, (as a_horse_with_no_name has already pointed out) you can use the INSERT INTO ... SELECT form of INSERT to perform both SQL queries as one:

cur.execute(
    """INSERT INTO %s (data)
       SELECT data FROM Table1
       WHERE lat=-20.004189 AND lon=-63.848004""" % (args.tableName))

Per the question in the comments, if there are multiple fields, then the SQL becomes:

cur.executemany(
    """INSERT INTO {t} (lat,lon,data1,data2) 
       VALUES (%s,%s,%s,%s)""".format(t=args.tableName),rows)

(If you use the format method, then you don't have to escape all the other %ss.)

edited May 23, 2017 at 12:14

CommunityBot

11 silver badge

answered Oct 13, 2011 at 20:50

unutbu

886k197 gold badges1.9k silver badges1.7k bronze badges

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

5 Comments

Aditya Over a year ago

If I try execute many, it get the this as error:TypeError: Required argument 'vars_list' (pos 2) not found Also, I intend to read data from a file before inserting. So cannot always combine insert and select

unutbu Over a year ago

Please post the exact cur.executemany statement. And what is vars_list?

Aditya Over a year ago

cur.executemany("""INSERT INTO \"%s\" (data) VALUES (%s)""" % (args.tableName,rows)) vars_list was included in the TypeError thrown by python

unutbu Over a year ago

Move the rows outside the parentheses. (See how I wrote it in my post above).

Aditya Over a year ago

What is there are multiple attributes in row. Right now there is only data. But is there is lat,lon,data1, data2 then how should we use the executemany function?

Collectives™ on Stack Overflow

Insert python list into Postgres database

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related