Hello!
I finally managed to solve the problem (with the great help of my colleagues)!
The culprit: python-pyodbc package in Debian/testing is nearly THREE years old
now (version 2.1.7). I removed it and easy-installed the version 3.0.6 which
does not have the encoding bug anymore. Now everything works like a charm,
inluding SqlSoup. I even receive unicode objects directly for both VARCHAR and
NVARCHAR columns!
For future reference: If you want to connect to MSSQL server from a Linux
(Debian) machine, you need to...
[1] Install unixodbc:
aptitude install unixodbc
I also have unixodbc-dev installed, but I am not sure if it is necessary.
[2] Install freetds and the driver:
aptitude install freetds-bin tdsodbc
[3] Edit /etc/odbcinst.ini (note: DO NOT indent lines in the odbc config
files!):
[FreeTDS]
# See dpkg -L tdsodbc | grep libtdsodbc
Driver = /path/to/libtdsodbc.so
UsageCount = 1
[4] Edit /etc/odbc.ini:
[ODBC Data Sources]
zfp = test
[zfp]
Driver = FreeTDS # The section name from /etd/odbcinst.ini
Description = test
Servername = zfp # The section name from /etc/freetds/freetds.conf (see below)
# Do NOT use option 'Server'.
TDS_Version = 8.0 # Important
[5] Edit /etc/freetds/freetds.conf:
[zfp]
host = 10.230.128.140
port = 1433
asa database = ZFP_CRM # Database name
tds version = 8.0 # Important
client charset = UTF-8 # Important
text size = 50000000
[6] Install python2.7-dev which is needed for the next step, because pyodbc
compiles some stuff and thus needs header files:
aptitude install python2.7-dev
[7] Install pyodbc "manually" (i.e. without aptitude):
easyinstall pyodbc
[8] Verify that freetds works (zfp is the section name in
/etc/freetds/freetds.conf):
tsql -S zfp -U username
Try to select some strings to see that they display correctly.
[9] Verify that unixodbc works (zfp here is the section name in /etc/odbc.ini):
isql zfp username password
Again, try to select some strings to see that they display correctly.
[10] Verify that pyodbc works:
import pyodbc
cnxn = pyodbc.connect("DSN=zfp;UID=username;PWD=password", unicode_results=True)
cursor = cnxn.cursor()
# Select some VARCHAR column.
(x,) = cursor.execute("select...").fetchone()
# Should print a meaningful unicode representation of the result string.
print repr(x)
# Repeat the same for NVARCHAR column. The result should be the same, i.e.
# proper unicode representation).
(x,) = cursor.execute("select...").fetchone()
print repr(x)
[11] Connect via SQLAlchemy:
from sqlalchemy.engine import create_engine
from sqlalchemy.ext.sqlsoup import SqlSoup
# zfp here is the section name in /etc/odbc.ini
engine = create_engine(
"mssql+pyodbc://username:password@zfp",
convert_unicode=True,
echo='debug'
)
db = SqlSoup(engine)
# Same as in step 10
db.execute(...)
# OR
x = db.some_table.filter(...).one()
Enjoy!
Ladislav Lenart
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
