pypyodbc
pypyodbc copied to clipboard
jiangwen365
in Python 3, type(cursor.description[0][0]) is str on Windows but bytes on 64-bit CentOS 7 Linux
On Python 3.5.1:
Windows 7 x64: both pyodbc 3.0.10 and pypyodbc 1.3.3 obtain full column names with the below codes CentOS 7 x86-64 (unixODBC 2.3.1-11.el7): pyodbc 3.0.10 gets full column names, but pypyodbc 1.3.3 gets only the first character --- see comments in code below
This occurs with both oracle 12.1 and mysql 5.3 ODBC drivers. For mysql, both ANSI and Unicode drivers.
import pyodbc
import pypyodbc
def get_column_names(conn, table_name):
with conn.cursor() as cursor:
cursor = cursor.execute("SELECT * FROM " + table_name)
column_names = [desc[0] for desc in cursor.description]
return column_names
oracle_connection_string = "DRIVER=/usr/lib/oracle/12.1/client64/lib/libsqora.so.12.1;..."
table_name = "..."
pyora = pyodbc.connect(oracle_connection_string, autocommit=True)
pypyora = pypyodbc.connect(oracle_connection_string, autocommit=True)
get_column_names(pyora, table_name)
#['BE_ID',
# 'SECURITY_ID',
# 'ID_TYPE',
# 'COUNTRY_OF_REG',
# 'EXCHANGE',
# 'START_DATE',
# 'END_DATE',
# 'INFERRED',
# 'UPDATE_DATE']
get_column_names(pypyora, table_name)
#[b'b', b's', b'i', b'c', b'e', b's', b'e', b'i', b'u']
pyora.close()
pypyora.close()
mysql_connection_string = "Driver=/usr/lib64/libmyodbc5w.so;..."
table_name = "..."
pymys = pyodbc.connect(mysql_connection_string, autocommit=True)
pypymys = pypyodbc.connect(mysql_connection_string, autocommit=True)
get_column_names(pymys, table_name)
#['risk_id',
# 'start_date',
# 'end_date',
# 'last_date',
# 'parent_id',
# 'security_name',
# 'ticker',
# 'cusip',
# 'isin',
# 'sedol',
# 'common_code',
# 'be_id']
get_column_names(pypymys, table_name)
#[b'r', b's', b'e', b'l', b'p', b's', b't', b'c', b'i', b's', b'c', b'b']
pymys.close()
pypymys.close()
Windows
Python 3.5.1 |Anaconda 2.4.1 (64-bit)| (default, Dec 7 2015, 15:00:12) [MSC v.1900 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import pypyodbc
>>> conn = pypyodbc.connect("DSN=risk", autocommit=True)
>>> cursor = conn.cursor()
>>> cursor = cursor.execute("SELECT * FROM risk.risk_id")
>>> type(cursor.description)
<class 'list'>
>>> type(cursor.description[0])
<class 'tuple'>
>>> type(cursor.description[0][0])
<class 'str'>
Linux
Python 3.5.1 |Anaconda 2.4.0 (64-bit)| (default, Dec 7 2015, 11:16:01)
[GCC 4.4.7 20120313 (Red Hat 4.4.7-1)] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import pypyodbc
>>> conn = pypyodbc.connect("DSN=risk", autocommit=True)
>>> cursor = conn.cursor()
>>> cursor = cursor.execute("SELECT * FROM risk.risk_id")
>>> type(cursor.description)
<class 'list'>
>>> type(cursor.description[0])
<class 'tuple'>
>>> type(cursor.description[0][0])
<class 'bytes'>
The difference in the last line seems to be related to the truncated-column-names symptom.
As far I see, some of the code does not work correctly with Python 3... please try again using python 2.7 and tell us.
Python 2.7 on both Windows and Linux obtains
>>> type(cursor.description[0][0])
<type 'str'>
and does not exhibit the problem.
I am having the same issue with pypyodbc on RHEL 6.5 using unixODBC and freeTDS.
I solved the issue using this link: http://www.pynut.com/?p=241 setting force_unicode = False in the _UpdateDesc() method of pypyodbc.py.
Seems the unicode buffer isnt working.
If I apply this fix, I get back full column names, but all as type bytes. The only remaining problem is decoding this to unicode - a simple b'abc \x80 def'.decode("cp1252") doesnt work.
Never mind, all fixed now: found this - https://code.google.com/archive/p/pypyodbc/issues/52. Check Comment 1, it fixes both the truncation and the unicode issue.
Looks like this fixes it for python 3, but breaks it for python 2.7 which worked previously.
I now get the exact same issue that was reported for python 3 when I use pypyodbc 1.3.4 with python 2.7 (with 1.3.3 it works fine and column names are returned correctly).
Can the fix be made dependant on the python version, so the latest version of pypyodbc can be used with python 2.7 and python 3?
