CODEWITHSUNDEEP
postgresqlpsycopg2
Josef
Josef

Reputation: 3

Getting error: ValueError: unsupported format character: '('

I have the below function for generating an SQL query dynamically to insert a pandas dataframe in postgres using psycopg2. I'll be using this function to insert multiple dataframes which might not have all the columns in the database hence why I'm not using pandas.to_sql(). I keep getting the error ValueError: unsupported format character: '(' and can't figure what is causing it.

Any help would be appreciated.

def execute_values(conn, df, schema, table):
    """
    Using psycopg2.extras.execute_values() to insert the dataframe
    """
    
    # Create a tuple of dicts from the dataframe values
    dicts = tuple(df.to_dict('records'))

    columns = sql.SQL(',').join(map(sql.Identifier, list(df.columns)))
    values = sql.SQL(',').join(map(sql.Placeholder, list(df.columns)))

    # SQL query to execute
    query = sql.SQL('INSERT INTO {} ({}) VALUES ({})').format(
        sql.Identifier(schema, table),
        columns,
        values
    )
    
    cursor = conn.cursor()
    try:
        extras.execute_values(cursor, query, dicts)
        conn.commit()
    except (Exception, psycopg2.DatabaseError) as error:
        print("Error: %s" % error)
        conn.rollback()
        cursor.close()
        raise

    print("execute_values() done")
    cursor.close()

Upvotes: 0

Views: 1305

Answers (1)

Adrian Klaver
Adrian Klaver

Reputation: 19684

How to do make dynamic SQL using execute_values:

import psycopg2
from psycopg2.extras import execute_values
from psycopg2 import sql

con = psycopg2.connect("dbname=test host=localhost user=aklaver")
cur = con.cursor()

columns = sql.SQL(',').join(map(sql.Identifier, ['id', 'cond', 'animal']))

data_dict = [{"id": 20, "cond": "fair", "animal": "fish"}, {"id": 30, "cond": "poor", "animal": "fish"}, {"id": 40, "cond": "great", "animal": "fish"}]


values = sql.SQL(',').join(map(sql.Placeholder, ['id', 'cond', 'animal']))
# Since you are using a dict you need to provide a template for 
# execute_values
values_template = sql.SQL('(') + values + sql.SQL(')')

print(values_template.as_string(con))                                                                                                                                     (%(id)s,%(cond)s,%(animal)s)


insert_sql = sql.SQL("INSERT INTO {} ({}) VALUES %s").format(sql.Identifier("animals"), columns)

print(insert_sql.as_string(con))
INSERT INTO "animals" ("id","cond","animal") VALUES %s

execute_values(cur, insert_sql, data_dict, template=values_template)
con.commit()

 select * from animals ;
 id | cond  | animal 
----+-------+--------
 20 | fair  | fish
 30 | poor  | fish
 40 | great | fish

Upvotes: 1

Related Questions