Python syntax error for executemany function

Question

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)

Are you sure question marks are used? stackoverflow.com/questions/19235686/… Also initd.org/psycopg/docs/sql.html — OneCricketeer
– OneCricketeer, Commented Feb 15, 2018 at 1:49
The link that you refer to is for sqlite, but you use postgresql. Are you sure they have the same Python interface? — DYZ
– DYZ, Commented Feb 15, 2018 at 1:56
Well, by judging the errors the console is giving me I don't believe they have they same interface. — Cap Lee
– Cap Lee, Commented Feb 15, 2018 at 1:59

clemens · Accepted Answer · 2018-02-15 05:45:20Z

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()))

answered Feb 15, 2018 at 5:45

clemens

17.9k12 gold badges52 silver badges71 bronze badges

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

Comments

Cap Lee · Accepted Answer · 2018-02-16 21:37:51Z

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.

Collectives™ on Stack Overflow

Python syntax error for executemany function

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related