How to insert a Python list into SQL Row

Question

I am trying to insert a list of values into a single column and getting the following error:

postgresConnection = psycopg2.connect(
host='x',
user='x',
password='x',
database='x'
)
data = '[12, 37]'
sqlpoptable = ("INSERT INTO datas (conditions) VALUES (?);", data)
cursor.execute(sqlpoptable, data)
postgresConnection.commit()`          


---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
<ipython-input-36-fa661d7bfe6a> in <module>
    7 data = '[12, 37]'
    8 sqlpoptable = ("INSERT INTO datas (conditions) VALUES (?);", data)
    ----> 9 cursor.execute(sqlpoptable, data)
    10 postgresConnection.commit()

TypeError: argument 1 must be a string or unicode object: got tuple instead

You need to convert the list into a string first. Try something like this into a variable. `', '.join(data)` — SS_DBA, Dec 30 '20 at 13:25
@c0lton: What's the database type for column `conditions`, `data` could be interpreted as a string or list of integers ? — Maurice Meyer, Dec 30 '20 at 17:51
@Maurice Meyer Yes, just accepted the answer as that is what worked for my particular situation. Thanks! — c0lton, Dec 31 '20 at 04:43

score 1 · Answer 1 · answered Dec 30 '20 at 13:27

1

sqlpoptable should contain the query only, but you specified the data in it, too, so eventually you specified data twice.

Either do this:

data = '[12, 37]'
sqlpoptable = "INSERT INTO datas (conditions) VALUES (?);"
cursor.execute(sqlpoptable, data)

or this (semantically equivalent, just using a bit of syntactic sugar):

data = '[12, 37]'
sqlpoptable = ("INSERT INTO datas (conditions) VALUES (?);", data)
cursor.execute(*sqlpoptable)

BTW: You do not need to pass a trailing semicolon to psycopg2.

answered Dec 30 '20 at 13:27

Christoph Thiede

305
5
12

Thank you for your response, your suggestion makes since to me however I am receiving the following error: SyntaxError: syntax error at or near ")" LINE 1: INSERT INTO datas (conditions) VALUES (?); – c0lton Dec 30 '20 at 13:43
I think you used the wrong placeholder, it should be `%s`. Maybe the following questions and its answers could help you? https://stackoverflow.com/questions/8134602/psycopg2-insert-multiple-rows-with-one-query – Christoph Thiede Dec 31 '20 at 14:42

Barbaros Özhan · Accepted Answer · 2020-12-30T13:42:19.060

1

You can use a list for parameters such as

data = [[12],[37]]
cursor.executemany("INSERT INTO datas(conditions) VALUES (?)",(data))
postgresConnection.commit()

where executemany is more performant method than execute

edited Dec 30 '20 at 13:42

answered Dec 30 '20 at 13:27

Barbaros Özhan

47,993
9
26
51

How to insert a Python list into SQL Row

2 Answers2