Pyodbc prevented from establishing connection when using PostgreSQL Unicode driver.

[slmnhq]

Hi all,

My simple attempt to connect to a PostgreSQL database via pyodbc results in a generic error when I use the postgres unicode driver.

This is the snippet of code:

#!/usr/bin/env python
import pyodbc
from sqlalchemy import *
def connect(): return pyodbc.connect('dsn=ckandb')
cn = connect()

This is the generated traceback:

Traceback (most recent call last):
  File "./tmp.py", line 12, in <module>
    cx = connect()
  File "./tmp.py", line 6, in connect
    def connect(): return pyodbc.connect('dsn=ckandb')
pyodbc.Error: ('0', '[0] [unixODBC]c (0) (SQLDriverConnectW)')

This is the odbc.ini (with some edits):

[ckandb]
Description           = CKAN std DB
Driver                = PostgreSQL Unicode
Trace                 = No
TraceFile             = sql.log
Database                = std
Servername              = localhost
UserName                = <edited>
Password                = <edited>
Port                    = 5432
Protocol                = 8.4
ReadOnly                = No
RowVersioning           = No
ShowSystemTables        = No
ShowOidColumn           = No
FakeOidIndex            = No
ConnSettings            =

And this is the odbcinst.ini:

[PostgreSQL ANSI]
Description        = PostgreSQL ODBC driver (ANSI version)
Driver        = /usr/lib/odbc/psqlodbca.so
Setup        = /usr/lib/odbc/libodbcpsqlS.so
Debug        = 0
CommLog        = 1
UsageCount        = 1

[PostgreSQL Unicode]
Description        = PostgreSQL ODBC driver (Unicode version)
Driver        = /usr/lib/odbc/psqlodbcw.so
Setup        = /usr/lib/odbc/libodbcpsqlS.so
Debug        = 0
CommLog        = 1
UsageCount        = 1

Note that the error only occurs when using the PostgreSQL Unicode driver. It does not occur when using the ANSI driver and I am able to execute cursor operations. Also note that  'isql -v ckandb' can successfully connect to the database regardless of driver type.

Note the following packages are installed on my Ubuntu 10.04 system:

odbc-postgresql                  1:08.03.0200-1
postgresql-8.4                   8.4.11-0ubuntu0.10.04
python-pyodbc                    2.1.7-1
unixodbc                         2.2.11-21

Can someone explain why my configuration prevent pyodbc from establishing a database connection using the postgres unicode driver?

Thank you,
Salman

[Michael Kleehammer]

Can you post an ODBC trace of the connection attempt?

[slmnhq]

Sure. Where which log file will I find it in? I'm a little new to ODBC
configuration.

Thanks,
Salman

[eatmeim...@gmail.com]

This has been a while, but thought I would chime in.  You do not need an ODBC setup on the local machine to connect with PYODBC and PSYCOPG2.  

PYODBC Example connecting to MSSQL:

[code]

import pyodbc

msconn_string = 'DRIVER={SQL Server};SERVER=krypton;DATABASE=master;UID=superman;PWD=loislane'

try:

    connect = pyodbc.connect(msconn_string)

except:

    sys.exit("Unable to connect to MSSQL database")

cursor = connect.cursor()

[/code]

PSYCOPG2 Example connecting to PostgreSQL:

[code]

import psycopg2

pconn_string = "host='gothim' dbname='batlayer' user='batman' password='vickyvale'"

try:

    pconn = psycopg2.connect(pconn_string)

except:

    sys.exit("Unable to connect to PostgreSQL database")

cursor = pconn.cursor()

[/code] 

I don't setup anything in ODBC and don't even have the native drivers or postgresql drivers installed.