CODEWITHSUNDEEP
pythonpostgresqlpsycopg2
ZAR
ZAR

Reputation: 2736

psycopg2: dynamic table, columns and values

I am trying t use psycopg2 to generate a dynamic INSERT INTO statement where table, columns and values are all dynamic.

I've read the documentation around the psycopg2.SQL class, which provides some hints, however I can't get the final result.

# inputs to the DB. Keys represent COLS. 2 records.

kvalues = [{'first':11,'second':22},{'first':33,'second':44}]

table   = 'my_table'
columns = list(kvalues[0].keys())
values  = [list(kvalue.values()) for kvalue in kvalues]

# Generate SQL string
conn = psycopg2.connect(#...details..)
cur  = conn.cursor()

query_string = sql.SQL("INSERT INTO {} ({}) VALUES %s").format(
        sql.Identifier(table), 
        sql.SQL(', ').join(map(sql.Identifier, columns))).as_string(cur)

print(cur.mogrify(query_string, values))

#>> TypeError: not all arguments converted during string formatting

I've tried adding the values in the SQL statement, but this also produces more errors.

How can I generate a dynamic insert into statement which accepts the cols, vals from kvalues?

Thank you!

Upvotes: 1

Views: 2510

Answers (1)

ZAR
ZAR

Reputation: 2736

Issue is around Tuple/List formatting. Below is the code that works:

kvalues = [{'first':11,'second':22},{'first':33,'second':44}]

table   = 'my_table'
columns = list(kvalues[0].keys())
values  = [tuple(kvalue.values()) for kvalue in kvalues]

# Generate SQL string
conn = psycopg2.connect(#...details..)
cur  = conn.cursor()

query_string = sql.SQL("INSERT INTO {} ({}) VALUES {}").format(
        sql.Identifier(table),
        sql.SQL(', ').join(map(sql.Identifier, columns)),
        sql.SQL(', ').join(sql.Placeholder()*len(values)),
        ).as_string(cur)

print(cur.mogrify(query_string, values))

Upvotes: 2

Related Questions