Skip to main content

pyODBC on mac

  • November 4, 2013
  • 10 replies
  • 32 views
Guillaume
Forum|alt.badge.img+1
Hi,

It seems I cannot connect to Vertica via pyodbc on a mac. My exaxt same setup (modulo the paths) works fine on linux, though. I had no luck looking in these forums or anywhere else. Would anybody know what it going on ?
  • Vertica client 6.1.2 or 6.1.3, installed as my user or root
  • pyobc for all versions 3+
I create a virtualenv, pip install odbc.

my relevant files are:
/etc/odbc.ini:
[VerticaDWHSTG]
Description = VerticaDSN ODBC driver for DWH (staging)
Driver = Vertica
Database = stgdwh
Servername = vertica.stg
UserName = stguser
Password = ****
Port = 5433
ConnSettings =
Locale = en_US@collation=binary

[Driver]
ErrorMessagesPath = /opt/vertica/lib/
ODBCInstLib = /usr/local/Cellar/unixodbc/2.3.1/lib/libodbcinst.dylib
DriverManagerEncoding=UTF-16
/etc/odbcinst.ini
[Vertica]
Description=Vertica driver
Driver=/opt/vertica/lib/libverticaodbc.dylib
Driver64=/opt/vertica/lib/libverticaodbc.dylib
Threading=1
From then on, I run:
VERTICAINI=/etc/odbc.ini python -c 'import pyodbc; pyodbc.connect("DSN= VerticaDWHSTG ")'
raceback (most recent call last):  File "", line 1, in pyodbc.Error: ('HY000', '[]  (10360) (SQLDriverConnect)')
  • If I give a wrong DSN, which does not exist, I have another error message (Data source name not found...). which means that the right odbc.ini file is found.
  • If I do not set VERTICAINI, Queries do run but the output is not right, for instance, where all values are numbers, dates or string:
python -c 'import pyodbc; c=pyodbc.connect("DSN=VerticaDWHSTG"); print c.cursor().execute("SELECT * from meta.spil_users").fetchone()'

(u'g\x00u\x00i\x00l\x00l\x00a\x00u\x00m\x00e\x00.\x00r\x00o\x00g\x00e\x00r\x00', None, u's\x00p\x00i\x00l\x00', u'Z\x00e\x00B\x00o\x00s\x00s\x00', datetime.datetime(2013, 10, 15, 13, 57, 57, 100737), datetime.datetime(2013, 10, 15, 13, 57, 57, 100737), u'g\x00u\x00i\x00l\x00l\x00a\x00u\x00m\x00e\x00.\x00r\x00o\x00g\x00e\x00r\x00', u'd\x00e\x00f\x00a\x00u\x00l\x00t\x00', u'g\x00e\x00n\x00e\x00r\x00a\x00l\x00')
Is there a way around?
Thanks,

    10 replies

    Daniel_Leybovic
    Forum|alt.badge.img+1
    • Daniel_Leybovic
    • Participating Frequently
    • November 6, 2013
    1. Check ODBC encoding (UTF-32 for Mac )
    You have:
    DriverManagerEncoding=UTF-16
    2. set locale for ODBC session
    3. pyodbc 2.1.x has bug with encoding. Use in version 3.0.6 and above.
    I tested this version (pyodbc 3.0.6).
    Except a bug with PROFILE no issues found. 

    https://my.vertica.com/docs/6.1.x/HTML/index.htm#13632.htm
    https://my.vertica.com/docs/6.1.x/HTML/index.htm#16837.htm
    https://my.vertica.com/docs/6.1.x/HTML/index.htm#17028.htm

    If you can, install pyodbc from PIP and not distributive package.




    Guillaume
    Forum|alt.badge.img+1
    • Guillaume
    • Author
    • Participating Frequently
    • November 7, 2013
    Thanks a lot for you answer.

    - I missed the DriverManagerEncoding, although setting it to UTF-32 did not make a difference.
    - The Locale is properly set I believe (it works under linux, and does match the output of the locale command on my Mac)
    - I am using pyodbc 3.0.7, from pip

    Sadly this does not change  thing, I still have the same issues. I carry on looking.

    Cheers!

    Shinji_Suzuki
    • Shinji_Suzuki
    • New Participant
    • November 7, 2013
    In the last case, the query seems to run fine. The output is what is expected when printing a tuple/list. Elements are printed using repr() rather than str().

    Guillaume
    Forum|alt.badge.img+1
    • Guillaume
    • Author
    • Participating Frequently
    • November 12, 2013
    You are completely right indeed.

    The weird stuff then is that VERTICAINI does not seem to be needed on MacOs. I do have exactly the same output with or without it (as long as in the VERTICAINI file I am stipulating UTF-32 encoding. With VERTICAINI and UTF-16 the connection fails.).

    Thanks!
    kesten_broughto
    Forum|alt.badge.img
    • kesten_broughto
    • Participating Frequently
    • May 13, 2014
    UTF-32 for mac?
    locale
    LANG="en_US.UTF-8"
    LC_COLLATE="en_US.UTF-8"
    LC_CTYPE="en_US.UTF-8"
    LC_MESSAGES="en_US.UTF-8"
    LC_MONETARY="en_US.UTF-8"
    LC_NUMERIC="en_US.UTF-8"
    LC_TIME="en_US.UTF-8"
    LC_ALL=

    And XCode's default setting is utf-8

    I got invalid locale setting errors when i tried utf-32

    Can you point to any documentation about utf-32 on mac?

    kesten_broughto
    Forum|alt.badge.img
    • kesten_broughto
    • Participating Frequently
    • May 13, 2014
    indeed, utf-32 was the key to getting past the HY000 error.  It seems that unixodbc works in utf-8 land whereas iODBC is utf-32. 
    kobymeir
    • kobymeir
    • New Participant
    • November 13, 2014
    @Guillaume Where you able to resolve the issue ? and how?
    Guillaume
    Forum|alt.badge.img+1
    • Guillaume
    • Author
    • Participating Frequently
    • November 13, 2014
    My 'fix' is sadly to always use pyodbc from a Linux box... I had no luck with Mac.
    kobymeir
    • kobymeir
    • New Participant
    • November 13, 2014
    Thanks :)
    kesten_broughto
    Forum|alt.badge.img
    • kesten_broughto
    • Participating Frequently
    • November 13, 2014
    I did finally get pyodbc working on my mac.  One of the problems troubleshooting is that odbc driver seems to look in lots of places for the .ini file.  In the end, i found three places and was having problems because i had only made changes to two of the files.  UTF32 was definitely important too.

    Locations:  /Library/ODBC, ~/ODBC, /usr/local/etc

    ls /usr/local/etc

    odbc.ini -> /Users/kbroughton/ODBC/odbc.ini

    odbcinst.ini -> /Users/kbroughton/ODBC/odbcinst.ini

    ls /Library/ODBC

    odbc.ini -> /usr/local/etc/odbc.ini

    odbcinst.ini -> /usr/local/etc/odbcinst.ini


    Example entry in odbc.ini

    [vertica_vagrant]

    Driver = Vertica

    Server=192.168.10.11

    Servername=192.168.10.11

    Port=5433

    Description = vertica_vagrant

    UID=dbadmin

    PWD=dbamin

    Locale=en_US

    Database=dev

    Debug=1

    Host=192.168.10.11

    DriverManagerEncoding=UTF-32


    example odbcinst.ini

    [Vertica]

    Description = HP Vertica ODBC Driver

    Driver = /opt/vertica/lib/libverticaodbc.dylib

    Port=5433

    Database=metrics

    #Host=10.0.9.108

    Host=192.168.10.11

    DriverManagerEncoding=UTF-32

    Locale=en_US


    cat ~/ODBC/vertica.ini

    [Driver]

    DriverManagerEncoding=UTF-32

    ODBCInstLib=/usr/local/lib/libodbcinst.dylib

    ErrorMessagesPath=/opt/vertica/lib

    LogLevel=4

    LogPath=/opt/vertica/logs

    Port=5433

    Datbase=metrics

    Host=10.0.9.108


    make sure your LogPath and ErrorMessagePath exist.


    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement