Passing Python List in SQL Query and stored as list

Question

I have a scenario in which i have to store python list in database via python script. i have seen lot of solution like converting it into tuple or removing quotes around a string. but it want to store it into database in python list format.

['[email protected]', '[email protected]', '[email protected]', '[email protected]', '[email protected]', '[email protected]', '[email protected]', '[email protected]', '[email protected]']

and my query is

sql_test = "UPDATE log_messages SET unique_arguments =  %s , sent_to =  %s where log_id = %s " %  (unique_arguments_list,email_send_to,str(log_id))

and getting this error

syntax error at or near "["
LINE 1: UPDATE log_messages SET unique_arguments =  ['[email protected]...
                                                ^

Obviously string representation of list results in non-valid sql syntax, you have to either fix it (wrapping list in " quotation marks may be sufficient) of find other efficient way of serializing your list. — Łukasz Rogalski
– Łukasz Rogalski, Commented Mar 22, 2015 at 20:19
Obviously, I know this, that is why i ask here for any alternative — Muhammad Taqi
– Muhammad Taqi, Commented Mar 22, 2015 at 20:25
It's not clear what database / python interface you are using. — Mike T
– Mike T, Commented Mar 23, 2015 at 1:31
It's still not clear what python interface you are using, e.g. psycopg2, pygresql, pyodbc, etc. — Mike T
– Mike T, Commented Mar 23, 2015 at 6:30

Fran Borcic · Accepted Answer · 2015-03-22 20:40:24Z

1

This should work. You are not enclosing your list string within quotes.

sql_test = "UPDATE log_messages SET unique_arguments =  \"%s\" , sent_to =  %s where log_id = %s " %  (unique_arguments_list,email_send_to,str(log_id))

answered Mar 22, 2015 at 20:40

Fran Borcic

7146 silver badges9 bronze badges

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

5 Comments

Muhammad Taqi Over a year ago

this way, String breaks from \"

Fran Borcic Over a year ago

What do you mean by "String breaks"?

Muhammad Taqi Over a year ago

as you suggest, the query becomes

UPDATE log_messages SET unique_arguments = "['[email protected]', '[email protected]', '[email protected]', '[email protected]', '[email protected]', '[email protected]', '[email protected]', '[email protected]', '[email protected]']" ,   sent_to =  "['[email protected]', '[email protected]', '[email protected]', '[email protected]', '[email protected]', '[email protected]', '[email protected]', '[email protected]', '[email protected]']" where log_id = 5

Muhammad Taqi Over a year ago

See the parameter now, double quotes the query as it is in string from

Fran Borcic Over a year ago

Well, should it not be? In which format should it be stored? There's no such thing as list format in SQL as far as I know. Such a thing would be pointless.

Collectives™ on Stack Overflow

Passing Python List in SQL Query and stored as list

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related