For anyone looking for a solution for this on macOS Sierra 10.12 (or later, most likely): I fixed this by installing the command line tools: xcode-select –install After that, pip install psycopg2 should work. If it doesn’t, you could also try to link against brew’s openssl: env LDFLAGS=”-I/usr/local/opt/openssl/include -L/usr/local/opt/openssl/lib” pip install psycopg2 with openssl installed … Read more
This is what postgres does when a query produces an error and you try to run another query without first rolling back the transaction. (You might think of it as a safety feature, to keep you from corrupting your data.) To fix this, you’ll want to figure out where in the code that bad query … Read more
Note: Since a while back, there are binary wheels for Windows in PyPI, so this should no longer be an issue for Windows users. Below are solutions for Linux, Mac users, since lots of them find this post through web searches. Option 1 Install the psycopg2-binary PyPI package instead, it has Python wheels for Linux … Read more
According to the official documentation: If you need to generate dynamically an SQL query (for instance choosing dynamically a table name) you can use the facilities provided by the psycopg2.sql module. The sql module is new in psycopg2 version 2.7. It has the following syntax: from psycopg2 import sql cur.execute( sql.SQL(“insert into {table} values (%s, … Read more
psycopg2 follows the rules for DB-API 2.0 (set down in PEP-249). That means you can call execute method from your cursor object and use the pyformat binding style, and it will do the escaping for you. For example, the following should be safe (and work): cursor.execute(“SELECT * FROM student WHERE last_name = %(lname)s”, {“lname”: “Robert’); … Read more
The read_sql docs say this params argument can be a list, tuple or dict (see docs). To pass the values in the sql query, there are different syntaxes possible: ?, :1, :name, %s, %(name)s (see PEP249). But not all of these possibilities are supported by all database drivers, which syntax is supported depends on the … Read more
I built a program that inserts multiple lines to a server that was located in another city. I found out that using this method was about 10 times faster than executemany. In my case tup is a tuple containing about 2000 rows. It took about 10 seconds when using this method: args_str=”,”.join(cur.mogrify(“(%s,%s,%s,%s,%s,%s,%s,%s,%s)”, x) for x … Read more
pg_config is in postgresql-devel (libpq-dev in Debian/Ubuntu, libpq-devel on Centos/Fedora/Cygwin/Babun.)