Menu
What is the correct method to have the list (countryList) be available via %s in the SQL statement?
# using psycopg2 countryList=['UK','France'] sql='SELECT * from countries WHERE country IN (%s)' data=[countryList] cur.execute(sql,data) As it is now, it errors out after trying to run "WHERE country in (ARRAY[...])". Is there a way to do this other than through string manipulation?
Thanks
487
You can fetch data from PostgreSQL using the fetch() method provided by the psycopg2. The Cursor class provides three methods namely fetchall(), fetchmany() and, fetchone() where, The fetchall() method retrieves all the rows in the result set of a query and returns them as list of tuples.
Using the Error class of Psycopg2, we can handle any database error and exception while working with PostgreSQL from Python. Using this approach, we can make our application robust. The error class helps us to understand the error in detail. It returns an error message and error code if any.
Psycopg2 is a PostgreSQL database driver, it is used to perform operations on PostgreSQL using python, it is designed for multi-threaded applications. SQL queries are executed with psycopg2 with the help of the execute() method. It is used to Execute a database operation query or command.
For the IN operator, you want a tuple instead of list, and remove parentheses from the SQL string.
# using psycopg2 data=('UK','France') sql='SELECT * from countries WHERE country IN %s' cur.execute(sql,(data,)) During debugging you can check that the SQL is built correctly with
cur.mogrify(sql, (data,)) If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
