#pyodbc

[howtorapeurjob]

Python Install

1. pyodbc

https://github.com/mkleehammer/pyodbc

2. openpyxl

https://bitbucket.org/openpyxl/openpyxl/downloads

複製openpyxl-2.3.3\openpyxl 這個資料夾到C:\Python27\Lib\openpyxl

[tsworman]

UnixODBC, Python3, Netezza, pyodbc/ceODBC = Hell

 Simple goal for the day.. I wanted to move a Python 3.2 application from Windows to Linux. It used ODBC via ceODBC on Windows.

ceODBC only officially works on Python 3.2 at the moment (4/26/2015). It seems to be okay on 3.4 once I had all my ducks in a row.

Bonus points here as I installed for 64 bit linux

Process: Install unixodbc (apt-get install unixodbc) Install Netezza Linux Drivers. First install the 32 bit drivers (run ./unpack in the lib dir), then install the 64 bit drivers. The 32 bit drivers come with the applications you really want to use, the 64 bit driver package just installs a 64bit ODBC driver and it will sym link that over the 32bit version. Once this is installed try to run nzsql from the /usr/local/nz/bin directory. You’ll probably be missing a few dependencies but I was able to find them with apt-get.

Setup your ODBC connection:

odbcinst -j

this will list out where unixodbc is looking for config files. Netezza includes sample config files but they have issues and don’t actually work.

We’ll setup some path variables now and then run the command again. Your setup should look something like the following:

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/nz/lib export PATH=$PATH:/usr/local/nz/bin export ODBCINI=$HOME/.odbc.ini export NZ_ODBC_INI_PATH=$HOME

What do these do? LD Library path is going to hopefully point anything linking to the Netezza drivers to the right libraries. The 64 bit library is soft linked to that folder. PATH puts the fancy new NZ tools you just installed in your path when you try to run things like NZSQL. ODBCINI, you’ll notice that I hardcode the file at the end. If you don’t then the ODBCManageDataSources tools will not work. You’ll notice that I set NZ_ODBC_INI_PATH to home. This is where the Netezza drivers will look for the odbc.ini file. Even if you use a custom .odbc.ini file, Netezza won’t look at it even when unixODBC is. You need a soft link from your .odbc.ini to a plain odbc.ini in the directory where you set NZ_ODBC_INI_PATH to. With your environment straightened out, you can proceed to configure these files. For the life of me I can’t get unixodbc to look at a local odbcinsta.ini to find the drivers. Documentation said it should work, but it doesn’t for me. I ended up editing the /etc/odbcinst.ini file.

Run odbcinst -j and see if you get something similar.

unixODBC 2.3.1 DRIVERS............: /etc/odbcinst.ini SYSTEM DATA SOURCES: /etc/odbc.ini FILE DATA SOURCES..: /etc/ODBCDataSources USER DATA SOURCES..: /home/tworman/.odbc.ini SQLULEN Size.......: 8 SQLLEN Size........: 8 SQLSETPOSIROW Size.: 8 Edit your /etc/odbcinst.ini file and populate it like this:

[ODBC Drivers] NetezzaSQL = Installed[NetezzaSQL] Driver           = /usr/local/nz/lib64/libnzodbc.so Setup            = /usr/local/nz/lib64/libnzodbc.so APILevel         = 1 ConnectFunctions = YYN Description      = Netezza ODBC driver DriverODBCVer    = 03.51 DebugLogging     = false LogPath          = /tmp UnicodeTranslationOption = utf16 CharacterTranslationOption = all PreFetch         = 25600 Socket           = 16384 You’ll notice a few differences here from the provided Netezza file. My libraries are pointed to the lib64 version. I did this so that I knew that I’m using the 64 bit drivers and didn’t have to question myself. This is optional. The next major change is the Unicode Translation Option. All the documentation I found said that this should be UTF8. I had some errors connecting with the isql tool with this setting (online solutions suggested changing TranslationDLL in connection configuration, this fixed nothing) and if you do this, then you will have major issues with Python. Set this value to UTF16. 

If you find that ceODBC crashes on connect and provides you with an error about “double free or corruption”, you need UTF16. If pyodbc generates errors when using executemany that often state 0 parameters provided in SQL but X parameters provided, you need UTF16. This is caused becase pyodbc isn’t seeing the ? token in your statements. If you use execute and directly try an insert, then you will receive an error similar to this: pyodbc.ProgrammingError: ('42000', '[42000] ERROR:  \'S\'\nerror    ^ found "I" (at char 1) expecting a keyword (27) (SQLExecDirectW)') See bug report here: https://code.google.com/p/pyodbc/issues/detail?id=381 

Unixodbc is going to use the file located in /etc/odbcinst.ini for configuration from it’s GUI and tools. The actual file used at runtime by netezza is the version located in your home directory as .odbcinst.ini. This is extremely inconsistent behavior and annoying to track down.

With this in place, configure your data source. User data sources can be in our home directory based on how we set the environment up. We put them in .odbc.ini (it’s softlinked to odbc.ini in the same folder, otherwise NZ drivers won’t use it)

My connection looks like this:

[ODBC Data Sources] NZSQL = NetezzaSQL

[NZSQL] Driver                = /usr/local/nz/lib64/libnzodbc.so Description           = NetezzaSQL ODBC Servername            = Port                  = 5480 Database              = Username              = Password              =

;Only Select SQL statements will be allowed if this field is checked.                                                                                         ReadOnly              = false FastSelect            = false ShowSystemTables      = false ;When set to true, use old (2.5) functionality for returning schemas in SQLTables.                                                                           LegacySQLTables       = false LoginTimeout          = 0 ;Specifies query timeout in seconds.                                                                                                                         QueryTimeout          = 0 ;Specifies date format as follows -                                                                                                                           ; 1: YMD                                                                                                                                                     ; 2: MDY                                                                                                                                                     ; 3: DMY                                                                                                                                                     DateFormat            = 1 ;When selected, driver treats SQL_C_NUMERIC buffers as SQL_C_CHAR buffers.                                                                                   NumericAsChar         = false ;Return SQL_BIT as "1"/"0" when char.                

SQLBitOneZero         = false ;When enabled, replaces all occurences of CRLF, CR and LF with a single space.                                                                               StripCRLF             = false ;Specifies level of security on the system                                                                                                                   securityLevel         = preferredUnSecured ;Name of the Root CA certificate.                                                                                                                             caCertFile            = ;Specifies maximum errors allowed. 0 value means no limit on loadMaxErrors value.                                                                             loadMaxErrors         = 1 ;If value is true, it rounds the data of numeric columns whose precision exceeds the precision defined.                                                       ;loadRoundFraction     = 0                                                                                                                                  

;Possible fixes for translation error that didn't work. UTF16 was the solution.                                                                               ;TranslationDLL=                                                                                                      ;TranslationName=         ;TranslationOption=

[ODBC] IANAAppCodePage=4 InstallDir=/opt/odbc32v51 Trace=0 TraceDll=/opt/odbc32v51/lib/odbctrac.so TraceFile=odbctrace.out UseCursorLib=0

Python - I spent a good 5-6 hours here. Unicode is a huge issue with Python. It’s slightly better in 3.3 but the documentation is horrible and figuring out what character set you are currently working with isn’t as intuitive as it was in Python2.x. I seem to run into problems every time I do database work. For this install it did not matter if my version of Python was compiled with UCS2 or UCS4. I tried this with both versions and got the same result. The present default in the latest Ubuntu is a UCS4 build. 

Pip installed ceODBC and pyodbc. With UTF16 set on the connection, both should be fine on Python 3.2 or 3.4. pyodbc had errors on import complaining that it was unable to import decimal or cdecimal. Manually importing decimal didn’t fix it and resulted in more errors. I used pip to install cdecimal and then no import was needed and things just worked.

[onehashkey-blog]

在Ubuntu上安裝pyodbc

在Ubuntu上安裝pyodbc，沒有像Windows那般有Installer可用，因此一點點手動是必需的。以下是我在Ubuntu 12.04 LTS上安裝pyodbc的經驗。

首先，在pyodbc官網下載最新版本pyodbc的source code（行文之時最新版本為3.0.6，所以應下載檔案為pyodbc-3.0.6.zip）。如下圖：

下載完成後，把zip檔案解壓，得出一個pyodbc-3.0.6的folder，裡頭包含一個setup.py檔案。進入這個folder並執行下面命令，即可成功安裝：

$ sudo python3 setup.py build install

但如果在安裝過程中出現"error: command 'gcc' failed with exit status 1"的錯誤訊息的話，那表示一些必需的libraries還沒有安裝在系統裡。據官網與這位網友所說，我們需要同時安裝unixodbc和python-dev這些libraries方能自行build pyodbc，因此，我們先執行以下命令：

$ sudo apt-get install unixodbc-dev unixodbc-bin unixodbc python-dev python3.2-dev

（留意，因為我的系統裡安裝的是Python 3.2，因此我除了python-dev外，還多安裝python3.2-dev libraries，閣下也要留意你所安裝的Python是哪個版本。）

然後，再build pyodbc：

$ sudo python3 setup.py build install

這樣，pyodbc就成功安裝在系統裡，可以隨時拿來使用了。