SQL Server and unix, many things can change:
- UnixODBC version
- FreeTDS version (0.82 and 0.91 have *extremely* different behaviors)
- FreeTDS configuration
The first place I'd look in this case would be your freetds.conf, you probably
need to configure the character set correctly in there.
On Sep 4, 2014, at 5:06 PM, Horcle <g...@umn.edu> wrote:
> I had to reinstall my python dev environment from scratch due to a hd
> failure, and in the process something seems to have changed.
>
> When querying against MS SQL using the script (test_conenction.py):
> import pyodbc
> import sqlalchemy
> from sqlalchemy.engine import reflection
> from sqlalchemy.engine.reflection import Inspector
>
> def connect():
> return pyodbc.connect(
> 'DRIVER={FreeTDS};SERVER=server.ip.address;'
>
> 'DATABASE=STUDY_PARTICIPANT;UID=test;PWD=test;port=1433;CHARSET=utf8;'
> 'TDS_Version=9.1;')
> engine = sqlalchemy.create_engine('mssql+pyodbc://', creator=connect,
> encoding='latin1',echo='debug',supports_unicode_binds=False)
> conn = engine.connect()
> print conn
>
> for row in engine.execute('select 6 * 7 as [Result];'):
> print row.Result
>
> insp = reflection.Inspector.from_engine(engine)
> table_name = 'irb_desc'
> table_names = insp.get_table_names()
> if table_name not in table_names:
> print 'A: ' + table_name
>
> I used to get the following nice output:
>
> python test_connect.py
> 2014-08-18 16:15:06,611 INFO sqlalchemy.engine.base.Engine
> SELECT default_schema_name FROM
> sys.database_principals
> WHERE principal_id=database_principal_id()
>
> 2014-08-18 16:15:06,611 INFO sqlalchemy.engine.base.Engine ()
> 2014-08-18 16:15:06,613 DEBUG sqlalchemy.engine.base.Engine Col
> ('default_schema_name',)
> 2014-08-18 16:15:06,614 DEBUG sqlalchemy.engine.base.Engine Row (u'dbo', )
> 2014-08-18 16:15:06,616 INFO sqlalchemy.engine.base.Engine SELECT CAST('test
> plain returns' AS VARCHAR(60)) AS anon_1
> 2014-08-18 16:15:06,616 INFO sqlalchemy.engine.base.Engine ()
> 2014-08-18 16:15:06,619 INFO sqlalchemy.engine.base.Engine SELECT CAST('test
> unicode returns' AS NVARCHAR(60)) AS anon_1
> 2014-08-18 16:15:06,619 INFO sqlalchemy.engine.base.Engine ()
> <sqlalchemy.engine.base.Connection object at 0x101877ed0>
> 2014-08-18 16:15:06,639 INFO sqlalchemy.engine.base.Engine select 6 * 7 as
> [Result];
> 2014-08-18 16:15:06,639 INFO sqlalchemy.engine.base.Engine ()
> 2014-08-18 16:15:06,641 DEBUG sqlalchemy.engine.base.Engine Col ('Result',)
> 2014-08-18 16:15:06,641 DEBUG sqlalchemy.engine.base.Engine Row (42, )
> 42
> 2014-08-18 16:15:06,647 INFO sqlalchemy.engine.base.Engine SELECT
> [TABLES_1].[TABLE_NAME]
> FROM [INFORMATION_SCHEMA].[TABLES] AS [TABLES_1]WHERE
> [TABLES_1].[TABLE_SCHEMA] = CAST(? AS NVARCHAR(max)) AND
> [TABLES_1].[TABLE_TYPE] = ? ORDER BY [TABLES_1].[TABLE_NAME]
> 2014-08-18 16:15:06,647 INFO sqlalchemy.engine.base.Engine ('dbo', 'BASE
> TABLE')
> 2014-08-18 16:15:06,663 DEBUG sqlalchemy.engine.base.Engine Col
> ('TABLE_NAME',)
> 2014-08-18 16:15:06,663 DEBUG sqlalchemy.engine.base.Engine Row (u'irb_desc',
> )
> 2014-08-18 16:15:06,663 DEBUG sqlalchemy.engine.base.Engine Row (u'irbd', )
> 2014-08-18 16:15:06,663 DEBUG sqlalchemy.engine.base.Engine Row
> (u'study_desc', )
> 2014-08-18 16:15:06,664 DEBUG sqlalchemy.engine.base.Engine Row
> (u'study_irb', )
> 2014-08-18 16:15:06,664 DEBUG sqlalchemy.engine.base.Engine Row
> (u'study_status', )
> 2014-08-18 16:15:06,664 DEBUG sqlalchemy.engine.base.Engine Row
> (u'study_status_desc', )
> 2014-08-18 16:15:06,664 DEBUG sqlalchemy.engine.base.Engine Row
> (u'study_subject', )
> 2014-08-18 16:15:06,664 DEBUG sqlalchemy.engine.base.Engine Row
> (u'subj_desc', )
> 2014-08-18 16:15:06,664 DEBUG sqlalchemy.engine.base.Engine Row
> (u'subj_status_desc', )
> 2014-08-18 16:15:06,665 DEBUG sqlalchemy.engine.base.Engine Row
> (u'subject_status', )
> 2014-08-18 16:15:06,665 DEBUG sqlalchemy.engine.base.Engine Row
> (u'sysdiagrams', )
>
>
> Now, in my updated environment, it looks like this:
>
> python test_connect.py
> 2014-09-04 15:26:05,974 INFO sqlalchemy.engine.base.Engine
> SELECT default_schema_name FROM
> sys.database_principals
> WHERE principal_id=database_principal_id()
>
> 2014-09-04 15:26:05,974 INFO sqlalchemy.engine.base.Engine ()
> 2014-09-04 15:26:05,976 DEBUG sqlalchemy.engine.base.Engine Col
> ('default_schema_name',)
> 2014-09-04 15:26:05,976 DEBUG sqlalchemy.engine.base.Engine Row
> (u'd\x00b\x00o\x00', )
> 2014-09-04 15:26:05,978 INFO sqlalchemy.engine.base.Engine SELECT CAST('test
> plain returns' AS VARCHAR(60)) AS anon_1
> 2014-09-04 15:26:05,978 INFO sqlalchemy.engine.base.Engine ()
> 2014-09-04 15:26:05,981 INFO sqlalchemy.engine.base.Engine SELECT CAST('test
> unicode returns' AS NVARCHAR(60)) AS anon_1
> 2014-09-04 15:26:05,981 INFO sqlalchemy.engine.base.Engine ()
> <sqlalchemy.engine.base.Connection object at 0x10fda1510>
> 2014-09-04 15:26:06,000 INFO sqlalchemy.engine.base.Engine select 6 * 7 as
> [Result];
> 2014-09-04 15:26:06,001 INFO sqlalchemy.engine.base.Engine ()
> 2014-09-04 15:26:06,002 DEBUG sqlalchemy.engine.base.Engine Col ('Result',)
> 2014-09-04 15:26:06,002 DEBUG sqlalchemy.engine.base.Engine Row (42, )
> 42
> 2014-09-04 15:26:06,007 INFO sqlalchemy.engine.base.Engine SELECT
> [TABLES_1].[TABLE_NAME]
> FROM [INFORMATION_SCHEMA].[TABLES] AS [TABLES_1]
> WHERE [TABLES_1].[TABLE_SCHEMA] = CAST(? AS NVARCHAR(max)) AND
> [TABLES_1].[TABLE_TYPE] = ? ORDER BY [TABLES_1].[TABLE_NAME]
> 2014-09-04 15:26:06,007 INFO sqlalchemy.engine.base.Engine
> ('d\x00b\x00o\x00', 'BASE TABLE')
> 2014-09-04 15:26:06,012 DEBUG sqlalchemy.engine.base.Engine Col
> ('TABLE_NAME',)
> 2014-09-04 15:26:06,012 DEBUG sqlalchemy.engine.base.Engine Row
> (u'i\x00r\x00b\x00_\x00d\x00e\x00s\x00c\x00', )
> 2014-09-04 15:26:06,012 DEBUG sqlalchemy.engine.base.Engine Row
> (u'i\x00r\x00b\x00d\x00_\x00b\x00a\x00l\x00a\x00n\x00c\x00e\x00', )
> 2014-09-04 15:26:06,013 DEBUG sqlalchemy.engine.base.Engine Row
> (u's\x00t\x00u\x00d\x00y\x00_\x00d\x00e\x00s\x00c\x00', )
> 2014-09-04 15:26:06,013 DEBUG sqlalchemy.engine.base.Engine Row
> (u's\x00t\x00u\x00d\x00y\x00_\x00i\x00r\x00b\x00', )
> 2014-09-04 15:26:06,013 DEBUG sqlalchemy.engine.base.Engine Row
> (u's\x00t\x00u\x00d\x00y\x00_\x00s\x00t\x00a\x00t\x00u\x00s\x00', )
> 2014-09-04 15:26:06,013 DEBUG sqlalchemy.engine.base.Engine Row
> (u's\x00t\x00u\x00d\x00y\x00_\x00s\x00t\x00a\x00t\x00u\x00s\x00_\x00d\x00e\x00s\x00c\x00',
> )
> 2014-09-04 15:26:06,013 DEBUG sqlalchemy.engine.base.Engine Row
> (u's\x00t\x00u\x00d\x00y\x00_\x00s\x00u\x00b\x00j\x00e\x00c\x00t\x00', )
> 2014-09-04 15:26:06,013 DEBUG sqlalchemy.engine.base.Engine Row
> (u's\x00u\x00b\x00j\x00_\x00d\x00e\x00s\x00c\x00', )
> 2014-09-04 15:26:06,016 DEBUG sqlalchemy.engine.base.Engine Row
> (u's\x00u\x00b\x00j\x00_\x00s\x00t\x00a\x00t\x00u\x00s\x00_\x00d\x00e\x00s\x00c\x00',
> )
> 2014-09-04 15:26:06,016 DEBUG sqlalchemy.engine.base.Engine Row
> (u's\x00u\x00b\x00j\x00e\x00c\x00t\x00_\x00s\x00t\x00a\x00t\x00u\x00s\x00', )
> 2014-09-04 15:26:06,016 DEBUG sqlalchemy.engine.base.Engine Row
> (u's\x00y\x00s\x00d\x00i\x00a\x00g\x00r\x00a\x00m\x00s\x00', )
> A: irb_desc
>
>
> In digging around, I found this (see
> https://code.google.com/p/pyodbc/wiki/ConnectionStrings): "If you are using
> UTF8 in your database and are getting results like "\x0038", you probably
> need to add "CHARSET=UTF8" to your connection string." But, this was for
> MySQL. I added it anyway, with no change.
>
> Obviously, something changed since this was working a couple weeks ago,
> either in the version of python (now @ 2.7.8), sqalchemy (now @ 0.9.7),
> unixodbc (now @2.32) or freetds (now @ 0.91_1). Any ideas?
>
> TIA!
>
>
>
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
