Macでpyodbcを使ってredshiftにアクセスする

## Windows ではすんなりできたのに...
Mac で pyodbc つかって Amazon Redshift にアクセスしようとしたら色々はまったのでメモ. 

## 前準備
* pyodbc のインストール

```
>> pip install pyodbc
```

* Redshift の odbcドライバ のインストール
    * https://docs.aws.amazon.com/ja_jp/redshift/latest/mgmt/configure-odbc-connection.html

## import できない
```
>> import pyodbc

Traceback (most recent call last):
  File "/Users/napinoco/.pyenv/versions/anaconda3-x.x.x/lib/python3.x/site-packages/IPython/core/interactiveshell.py", line 2881, in run_code
    exec(code_obj, self.user_global_ns, self.user_ns)
  File "<ipython-input-2-5ecb2b5e392f>", line 1, in <module>
    import pyodbc
  File "/Applications/PyCharm.app/Contents/helpers/pydev/_pydev_bundle/pydev_import_hook.py", line 20, in do_import
    module = self._system_import(name, *args, **kwargs)
ImportError: dlopen(/Users/napinoco/.pyenv/versions/anaconda3-x.x.x/lib/python3.x/site-packages/pyodbc.cpython-36m-darwin.so, 2): Library not loaded: /usr/local/opt/unixodbc/lib/libodbc.2.dylib
  Referenced from: /Users/napinoco/.pyenv/versions/anaconda3-x.x.x/lib/python3.x/site-packages/pyodbc.cpython-36m-darwin.so
  Reason: image not found
```

-> unixodbcをインストールする

```
>> brew install unixodbc
```

## ドライバが見つからない
```
>> server = XXX
>> database = YYY
>> username = ZZZ
>> password = WWW
>> cnxn = pyodbc.connect('DRIVER={Amazon Redshift (x64)};SERVER=%s;DATABASE=%s;UID=%s;PWD=%s' % (server, database, username, password))

Traceback (most recent call last):
  File "/Users/napinoco/.pyenv/versions/anaconda3-x.x.x/lib/python3.x/site-packages/IPython/core/interactiveshell.py", line 2881, in run_code
    exec(code_obj, self.user_global_ns, self.user_ns)
  File "<ipython-input-56-da8a2331fbb5>", line 1, in <module>
    cnxn = pyodbc.connect('DRIVER={Amazon Redshift (x64)};SERVER=%s;DATABASE=%s;UID=%s;PWD=%s' % (server, database, username, password))
pyodbc.Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'Amazon Redshift (x86)' : file not found (0) (SQLDriverConnect)")
```
-> Redshiftのページでは設定ファイルをいじるよう書いてあるが、プログラムで直接パスを指定したほうが楽

```
>> cnxn = pyodbc.connect('DRIVER={/opt/amazon/redshift/lib/libamazonredshiftodbc.dylib};SERVER=%s;DATABASE=%s;UID=%s;PWD=%s' % (server, database, username, password))
```

## クエリが文字化けして投げられない
```
>> import pandas as pd
>> test_sql = 'select * from hogehoge limit 100;'
>> test_df = pd.read_sql(test_sql, cnxn)

Traceback (most recent call last):
  File "/Users/napinoco/.pyenv/versions/anaconda3-x.x.x/lib/python3.x/site-packages/pandas/io/sql.py", line 1401, in execute
    cur.execute(*args)
pyodbc.ProgrammingError: ('42601', '[42601] [Amazon][Amazon Redshift] (30) Error occurred while trying to execute a query: [SQLState 42601] ERROR:  syntax error at or near "ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½"\nLINE 1: ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½;\n        ^\n (30) (SQLExecDirectW)')
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
  File "/Users/napinoco/.pyenv/versions/anaconda3-x.x.x/lib/python3.x/site-packages/IPython/core/interactiveshell.py", line 2881, in run_code
    exec(code_obj, self.user_global_ns, self.user_ns)
  File "<ipython-input-59-74a1b8cc7d1c>", line 1, in <module>
    test_df = pd.read_sql(test_sql, cnxn)
  File "/Users/napinoco/.pyenv/versions/anaconda3-x.x.x/lib/python3.x/site-packages/pandas/io/sql.py", line 399, in read_sql
    chunksize=chunksize)
  File "/Users/napinoco/.pyenv/versions/anaconda3-x.x.x/lib/python3.x/site-packages/pandas/io/sql.py", line 1436, in read_query
    cursor = self.execute(*args)
  File "/Users/napinoco/.pyenv/versions/anaconda3-x.x.x/lib/python3.x/site-packages/pandas/io/sql.py", line 1413, in execute
    raise_with_traceback(ex)
  File "/Users/napinoco/.pyenv/versions/anaconda3-x.x.x/lib/python3.x/site-packages/pandas/compat/__init__.py", line 340, in raise_with_traceback
    raise exc.with_traceback(traceback)
  File "/Users/napinoco/.pyenv/versions/anaconda3-x.x.x/lib/python3.x/site-packages/pandas/io/sql.py", line 1401, in execute
    cur.execute(*args)
pandas.io.sql.DatabaseError: Execution failed on sql 'select * from hogehoge limit 100;': ('42601', '[42601] [Amazon][Amazon Redshift] (30) Error occurred while trying to execute a query: [SQLState 42601] ERROR:  syntax error at or near "ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½"\nLINE 1: ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½ï¿½;\n        ^\n (30) (SQLExecDirectW)')
```

-> encoding を直す

```
>> cnxn.setencoding(encoding='utf-8')
```

## 戻り値の文字列が変
```
>> test_df.iloc[0,0]
Out[64]: 
'1\x00'
```
-> decoding も修正する

```
## Python 3.x
cnxn.setdecoding(pyodbc.SQL_CHAR, encoding='utf-8')
cnxn.setdecoding(pyodbc.SQL_WCHAR, encoding='utf-8')
cnxn.setdecoding(pyodbc.SQL_WMETADATA, encoding='utf-32le')
```

## References
* https://github.com/mkleehammer/pyodbc/wiki/Unicode
* https://github.com/mkleehammer/pyodbc/issues/87