CODEWITHSUNDEEP
pythonpostgresqlpsycopg2
nicholsonjf
nicholsonjf

Reputation: 1001

Psycopg2 query with variable number of parameters throws syntax error

I wrote a Python function to take a list of lists (of variable length) and insert it into a table (with the same number of columns as values in each list):

ps.cur = psycopg_cursor

def load_lists(list_of_lists, table):
    # Get number of columns in table
    sql = """
          SELECT column_name FROM information_schema.columns
          WHERE table_schema = 'public'
          AND table_name = '{}'
          """.format(table)
    ps.cur.execute(sql)
    columns_list = [i[0] for i in ps.cur.fetchall()]
    # Insert list of lists into table
    columns = '(' + ','.join(columns_list) + ')'
    parameters = '(' + ','.join(['%%s' for i in columns_list]) + ')'
    for i in list_of_lists:
        sql = """
              INSERT INTO {} {}
              VALUES {}
              """.format(table, columns, parameters)
        values = tuple([j for j in i])
        ps.cur.execute(sql, values)

I get the following traceback try to execute the function:

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "psyco.py", line 24, in load_lists
    ps.cur.execute(sql, values)
psycopg2.ProgrammingError: syntax error at or near "%"
LINE 3:               VALUES (%s,%s,%s,%s,%s)
                              ^

It appears Psycopg2 is unable to bind my variables to the %s parameters, but I can't figure out why. Any help or ideas are greatly appreciated!

Upvotes: 4

Views: 2692

Answers (1)

nicholsonjf
nicholsonjf

Reputation: 1001

Turns out I only need one % in front of each 's' when building the parameter markers for my query. It's a bit confusing, because the old string formatting method in Python used % signs as well, but not the same way Psycopg2 does. See the working code below (only change is in the 'parameters' variable):

ps.cur = psycopg_cursor

def load_lists(list_of_lists, table):
    # Get number of columns in table
    sql = """
          SELECT column_name FROM information_schema.columns
          WHERE table_schema = 'public'
          AND table_name = '{}'
          """.format(table)
    ps.cur.execute(sql)
    columns_list = [i[0] for i in ps.cur.fetchall()]
    # Insert list of lists into table
    columns = '(' + ','.join(columns_list) + ')'
    parameters = '(' + ','.join(['%s' for i in columns_list]) + ')' # <--- THIS LINE
    for i in list_of_lists:
        sql = """
              INSERT INTO {} {}
              VALUES {}
              """.format(table, columns, parameters)
        values = tuple([j for j in i])
        ps.cur.execute(sql, values)

Upvotes: 6

Related Questions