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
– SS_DBA, Commented Dec 30, 2020 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
– Maurice Meyer, Commented Dec 30, 2020 at 17:51
@Maurice Meyer Yes, just accepted the answer as that is what worked for my particular situation. Thanks! — c0lton
– c0lton, Commented Dec 31, 2020 at 4:43

Barbaros Özhan · Accepted Answer · 2020-12-30 13:42:19Z

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, 2020 at 13:42

answered Dec 30, 2020 at 13:27

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

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

Comments

Christoph Thiede · Accepted Answer · 2020-12-30 13:27:29Z

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, 2020 at 13:27

Christoph Thiede

1,2181 gold badge13 silver badges20 bronze badges

2 Comments

c0lton Over a year ago

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

Christoph Thiede Over a year ago

I think you used the wrong placeholder, it should be %s. Maybe the following questions and its answers could help you? stackoverflow.com/questions/8134602/…

Collectives™ on Stack Overflow

How to insert a Python list into SQL Row

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related