the answer to this is a combination of: http://initd.org/psycopg/docs/module.html#psycopg2.connect Any other connection parameter supported by the client library/server can be passed either in the connection string or as keywords. The PostgreSQL documentation contains the complete list of the supported parameters. Also note that the same parameters can be passed to the client library using environment … Read more
cursor.execute(“INSERT INTO …. RETURNING id”) id_of_new_row = cursor.fetchone()[0] And please do not build SQL strings containing values manually. You can (and should!) pass values separately, making it unnecessary to escape and SQL injection impossible: sql_string = “INSERT INTO domes_hundred (name,name_slug,status) VALUES (%s,%s,%s) RETURNING id;” cursor.execute(sql_string, (hundred_name, hundred_slug, status)) hundred = cursor.fetchone()[0] See the psycopg docs … Read more
Probably the main reason why to_sql needs a SQLAlchemy Connectable (Engine or Connection) object is that to_sql needs to be able to create the database table if it does not exist or if it needs to be replaced. Early versions of pandas worked exclusively with DBAPI connections, but I suspect that when they were adding … Read more
In my case I didn’t realize that you had to pass a tuple to cursor.execute. I had this: cursor.execute(query, (id)) But I needed to pass a tuple instead cursor.execute(query, (id,))
Use ISOLATION_LEVEL_AUTOCOMMIT, a psycopg2 extensions: No transaction is started when command are issued and no commit() or rollback() is required. import psycopg2 from psycopg2 import sql from psycopg2.extensions import ISOLATION_LEVEL_AUTOCOMMIT # <– ADD THIS LINE con = psycopg2.connect(dbname=”postgres”, user=self.user_name, host=””, password=self.password) con.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT) # <– ADD THIS LINE cur = con.cursor() # Use the psycopg2.sql module … Read more
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,))
The URI should start with postgresql:// instead of postgres://. SQLAlchemy used to accept both, but has removed support for the postgres name.
Running .execute() When executing a plain SELECT * FROM tablename, there’s no difference in the result provided. The differences between these three objects do become important depending on the context that the SELECT statement is used in or, more commonly, when you want to do other things like INSERT, DELETE, etc. When to use Engine, … Read more
I ran pip install psycopg2-binary and it worked like charm More info about the binary package Python 3 pip3 install psycopg2-binary
Does the error say libssl.dylib version is too old? On my mac, the error is that libssl.dylib and libcrypto.dylib is too old for pyscopg to use. The openssl package used by mac is 0.98, while pyscopg needs 1.0.0 or later version. My solution is this: install openssl from brew $ brew install openssl copy libssl.1.0.0.dylib … Read more