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 [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
