0

I'm trying to insert data into my database with python, but there's a syntax error in my query. I'm trying to get the code to take a list and be able to add the list into a row in a postgresql table. I've looked up some ways to add a list into a table and some of them had the '?' in it. I don't know what it means and I don't know why it might be causing the error. The error it's giving me is:

syntax error at or near ","
LINE 1: INSERT INTO TestTable VALUES (?, ?, ?, ?, ?, ?, ?);

Here's my code.

var_string = ', '.join('?' * len(items_list))
query_string = 'INSERT INTO TestTable VALUES (%s);' % var_string
cur.executemany(query_string, items_list)
Cap Lee
  • 43
  • 2
  • 10
  • 2
    Are you sure question marks are used? https://stackoverflow.com/questions/19235686/psycopg2-insert-into-table-with-placeholders Also http://initd.org/psycopg/docs/sql.html – OneCricketeer Feb 15 '18 at 01:49
  • 1
    Also, you're executing a single query, not many. – OneCricketeer Feb 15 '18 at 01:50
  • I don't know I found the code at [link](https://stackoverflow.com/questions/8316176/insert-list-into-my-database-using-python) – Cap Lee Feb 15 '18 at 01:52
  • The link that you refer to is for sqlite, but you use postgresql. Are you sure they have the same Python interface? – DYZ Feb 15 '18 at 01:56
  • Well, by judging the errors the console is giving me I don't believe they have they same interface. – Cap Lee Feb 15 '18 at 01:59

2 Answers2

0

Parameters for PyGreSQL are described by format, either by name or by position:

query_string = 'INSERT INTO TestTable VALUES (%(id)d, %(name)s, %(date)s)'
cur.execute(query_string, dict(
    id = 1, name = 'Cap Lee', date = datetime.date.today()))

or

query_string = 'INSERT INTO TestTable VALUES (%d, %s, %s)'
cur.execute(query_string, (1, 'Cap Lee', datetime.date.today()))
clemens
  • 16,716
  • 11
  • 50
  • 65
0

Ok so I found the answer to my question. So I was able to find another way to add a list to my table. I first hard coded the query and I learned that '?' marks are used for another framework, not postgresql. For postgresql I needed to use %s. I replace the question marks with '%s' character and it worked. The next thing I did was to fix make a less hard coded version. Here it is:

items = [data] // items is the list we are trying to add to the db table
copy_string = re.sub(r'([a-z])(?!$)', r'\1,', '%s' * len(items)) // len of items list is 7
final_string = re.sub(r'(?<=[.,])(?=[^\s])', r' ', copy_string)
query_string = 'INSERT INTO TestTable VALUES (%s);' % final_string
cur.execute(query_string, items)

The output for the query string should look like this:

INSERT INTO TestTable VALUES(%s, %s, %s, %s, %s, %s, %s);

Each '%s' character takes in a value from the list. The code takes in a list and adds them into a row in the db table.

Cap Lee
  • 43
  • 2
  • 10