Use binary COPY table FROM with psycopg2

Here is the binary equivalent of COPY FROM for Python 3: from io import BytesIO from struct import pack import psycopg2 # Two rows of data; “id” is not in the upstream data source # Columns: node, ts, val1, val2 data = [(23253, 342, -15.336734, 2494627.949375), (23256, 348, 43.23524, 2494827.949375)] conn = psycopg2.connect(“dbname=mydb user=postgres”) curs … Read more

Insert Python Dictionary using Psycopg2

from psycopg2.extensions import AsIs song = { ‘title’: ‘song 1’, ‘artist’: ‘artist 1’ } columns = song.keys() values = [song[column] for column in columns] insert_statement=”insert into song_table (%s) values %s” # cursor.execute(insert_statement, (AsIs(‘,’.join(columns)), tuple(values))) print cursor.mogrify(insert_statement, (AsIs(‘,’.join(columns)), tuple(values))) Prints: insert into song_table (artist,title) values (‘artist 1’, ‘song 1’) Psycopg adapts a tuple to a record … Read more

Mac + virtualenv + pip + postgresql = Error: pg_config executable not found

On the Mac, if you’re using Postgres.app, the pg_config file is in your /Applications/Postgres.app/Contents/Versions/<current_version>/bin directory. That’ll need to be added to your system path to fix this error, like this: export PATH=$PATH:/Applications/Postgres.app/Contents/Versions/<current_version>/bin So for example, if the current Postgres.app version is 9.5, this export line would be: export PATH=$PATH:/Applications/Postgres.app/Contents/Versions/9.5/bin With more recent versions of the … Read more

Create a Postgres database using python

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

Psycopg2 image not found

$ sudo ln -s /Library/PostgreSQL/9.2/lib/libssl.1.0.0.dylib /usr/lib $ sudo ln -s /Library/PostgreSQL/9.2/lib/libcrypto.1.0.0.dylib /usr/lib I encountered this error while working on Django. I have it working on virtualenv with Django==1.3 but not on Django==1.5 where I have to issue the commands above. In OS X El Capitan you can’t do these links without disabling system protection but … Read more

Python/psycopg2 WHERE IN statement

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,))

psycopg2.OperationalError: FATAL: unsupported frontend protocol 1234.5679: server supports 2.0 to 3.0

1234.5679 is the special code sent by the client to request SSL-encrypted database connections, and support for that has been in PostgreSQL since commit e0e7daef6da in 1999. But your PostgreSQL cannot be that old, because support for protocol version 3.0 was not added before 2003. Actually, from studying src/backend/postmaster/postmaster.c and reading the mailing list, this … Read more

SQLAlchemy, Psycopg2 and Postgresql COPY

accepted answer is correct but if you want more than just the EoghanM’s comment to go on the following worked for me in COPYing a table out to CSV… from sqlalchemy import sessionmaker, create_engine eng = create_engine(“postgresql://user:pwd@host:5432/db”) ses = sessionmaker(bind=engine) dbcopy_f = open(‘/tmp/some_table_copy.csv’,’wb’) copy_sql=”COPY some_table TO STDOUT WITH CSV HEADER” fake_conn = eng.raw_connection() fake_cur = … Read more