#psycopg2

The Postgres.app website has pretty detailed information on upgrading your installation, however it does not cover a situation I came across.

After upgrading a minor release (9.3.x -> 9.4.x) psycopg2 failed to run with the following error:

Library not loaded: /Applications/Postgres.app/Contents/Versions/9.3/lib/libssl.1.0.0.dylib

Fixing this issue required that I reinstall and rebuild psycopg2 without pip's cache in all my environments.

$ pip uninstall psycopg2 $ pip install --no-cache-dir psycopg2

psql 설치 후에도 psycopg2 가 깔리지 않고 에러가 뜸

$ pip install psycopg2 Downloading/unpacking psycopg2  Running setup.py egg_info for package psycopg2    Error: pg_config executable not found.    Please add the directory containing pg_config to the PATH    or specify the full executable path with the option:        python setup.py build_ext --pg-config /path/to/pg_config build ...    or with the pg_config option in 'setup.cfg'.    Complete output from command python setup.py egg_info:    running egg_info writing pip-egg-info/psycopg2.egg-info/PKG-INFO writing top-level names to pip-egg-info/psycopg2.egg-info/top_level.txt writing dependency_links to pip-egg-info/psycopg2.egg-info/dependency_links.txt warning: manifest_maker: standard file '-c' not found Error: pg_config executable not found.

방법 : PATH 설정을 해주자!

export PATH=/path/to/compiled/postgresql/bin:"$PATH" pip install psycopg2

Building the psycopg2.6 in Python 2.7 has to use the VS2008 environment. Otherwise you will get an error message “Unable to find vcvarsall.bat”. But, now days, it’s hard to get VS2008 VC 9.0. If you have VS2013 already been installed, you can have a OS environment variable set as a simple workaround. For example, i have installed VS2013 in C:\app\VS12:

SET VS90COMNTOOLS=C:\app\VS12\Commons7\Tools\

The Python 2.7 psycopg2 setup script will look forward this environment variable and locate the vcvarsall.bat.

And then, the VC 12.0 has different headers and libraries from VC9.0. That will cause the error message showing below:

psycopgmodule.c .\psycopg/config.h(134) : warning C4005: 'isnan' : macro redefinition        C:\app\VS12\VC\INCLUDE\math.h(289) : see previous definition of 'isnan' .\psycopg/config.h(138) : warning C4005: 'isinf' : macro redefinition        C:\app\VS12\VC\INCLUDE\math.h(288) : see previous definition of 'isinf' .\psycopg/config.h(150) : error C2491: 'round' : definition of dllimport function not allowed error: Setup script exited with error: command 'C:\\app\\VS12\\VC\\BIN\\amd64\\cl.exe' failed with exit status 2

To fix this one, you could apply this patch lines below to psycopg2.6\psycopg\config.h:

--- psycopg2-2.6/psycopg/config.h   Mon Feb  9 18:00:36 2015 +++ psycopg2-2.6-1/psycopg/config.h Wed May 27 11:58:15 2015 @@ -130,19 +130,21 @@ #define inline

/* Hmmm, MSVC doesn't have a isnan/isinf function, but has _isnan function */ -#if defined (_MSC_VER) +#if defined (_MSC_VER) && _MSC_VER < 1800 #define isnan(x) (_isnan(x)) /* The following line was hacked together from simliar code by Bjorn Reese  * in libxml2 code */ #define isinf(x) ((_fpclass(x) == _FPCLASS_PINF) ? 1 \    : ((_fpclass(x) == _FPCLASS_NINF) ? -1 : 0))

-#define strcasecmp(x, y) lstrcmpi(x, y) + #endif + +#define strcasecmp(x, y) lstrcmpi(x, y) #endif

#if (defined(__FreeBSD__) && __FreeBSD_version < 503000) \ -    || (defined(_WIN32) && !defined(__GNUC__)) \ +    || (defined(_WIN32) && !defined(__GNUC__) && _MSC_VER < 1800) \     || (defined(sun) || defined(__sun__)) \         && (defined(__SunOS_5_8) || defined(__SunOS_5_9)) /* what's this, we have no round function either? */

Then, you could run the "easy_install .", you should be OK to build now.

However, this is not the whole story either a happy ending. When you start up the Python and import psycopg2 module, it will fail to load DLLs which psycopg2 depends on. These DLLs can be found in PostgreSQL Windows installation \bin folder. Copy the bin\lib*.dll, ssleay32.dll and zlib1.dll to your Python 2.7\\Lib\site-packages\psycopg2-2.6-py2.7-win-amd64.egg\psycopg2 folder. Now, you should be all set.

Fixed How to install psycopg2 with "pip" on Python? #dev #it #asnwer

How to install psycopg2 with "pip" on Python?

I’m using “virtualenv” and I need to install “psycopg2″.

I have done the following:

pip install http://pypi.python.org/packages/source/p/psycopg2/psycopg2-2.4.tar.gz#md5=24f4368e2cfdc1a2b03282ddda814160

And I have the following messages:

Downloading/unpacking http://pypi.python.org/packages/source/p/psycopg2/psycopg2-2.4.tar.gz#md5=24f4368e2cfdc1a2b…

How to: DatabaseError: current transaction is aborted, commands ignored until end of transaction block

DatabaseError: current transaction is aborted, commands ignored until end of transaction block

I got a lot of errors with the message :

"DatabaseError: current transaction is aborted, commands ignored until end of transaction block"

after changed from python-psycopg to python-psycopg2 as Django project’s database engine.

The code remains the same, just dont know where those errors are from.

Answ…

How to: How to install psycopg2 with "pip" on Python?

How to install psycopg2 with "pip" on Python?

I’m using “virtualenv” and I need to install “psycopg2″.

I have done the following:

pip install http://pypi.python.org/packages/source/p/psycopg2/psycopg2-2.4.tar.gz#md5=24f4368e2cfdc1a2b03282ddda814160

And I have the following messages:

Downloading/unpacking http://pypi.python.org/packages/source/p/psycopg2/psycopg2-2.4.tar.gz#md5=24f4368e2cfdc1a2b…

psycopg2のexecutemanyでの挿入を挿入対象とデータベースを比べて挿入してもいいデータかどうかを判定しながら行いたかった。

INSERT INTO t (a, b) VALUES (%(a)s, %(b)s) WHERE ~~~;

これは構文エラーになってしまうが

INSERT INTO t (a, b) SELECT (%(a)s, %(b)s) WHERE ~~~;

#!/usr/bin/env python import psycopg2 import sys import pprint 

DB_server ='localhost' DB_name ='my_super_db' DB_username ='user' DB_pass ='pass'

 conn_string = "host="+DB_server+" dbname="+DB_name+" user="+DB_username+" password="+DB_pass  

try:    conn = psycopg2.connect(conn_string)    cursor = conn.cursor()    cursor.execute("INSERT INTO city (\"gus_id\",\"pow\",\"gmi\",\"rodz_gmi\",\"rm\",\"mz\",\"nazwa\",\"sym\",\"sympod\",\"stan_na\",\"woj\") VALUES("+data['gus_id']+","+data['pow']+","+data['gmi']+","+data['rodz_gmi']+","+data['rm']+","+data['mz']+",\'"+data['nazwa']+"\',"+data['sym']+","+data['sympod']+","+"to_timestamp(\'"+ data['stan_na'] + "\',\'YYYY MM DD\'),"+data['gus_id']+")")

 except:

exceptionType, exceptionValue, exceptionTraceback = sys.exc_info() sys.exit("Database connection failed!\n ->%s" % (exceptionValue)) 

 In PostgreSQL log You  can see:

 [root@amd-x6 ~]# tail -f -n 10 /var/lib/pgsql/data/pg_log/postgresql-Sat.log DZIENNIK: polecenie: SET DATESTYLE TO 'ISO' DZIENNIK: polecenie: SHOW client_encoding DZIENNIK: polecenie: SHOW default_transaction_isolation DZIENNIK: polecenie: BEGIN; SET TRANSACTION ISOLATION LEVEL READ COMMITTED DZIENNIK: polecenie: INSERT INTO city ("gus_id","pow","gmi","rodz_gmi","rm","mz","nazwa","sym","sympod","stan_na","woj") VALUES(14,28,03,2,01,1,'Sadowo',0566530,0566530,to_timestamp('2012 06 13','YYYY MM DD'),14) DZIENNIK: polecenie: ROLLBACK DZIENNIK: polecenie: SET DATESTYLE TO 'ISO' DZIENNIK: polecenie: SHOW client_encoding DZIENNIK: polecenie: SHOW default_transaction_isolation DZIENNIK: nieoczekiwany EOF w połączeniu klienta

SOLUTION:

Set isolation level on Your connection, on  use conn.commit() after each coursor(execute)

conn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)

More info: