Issue with return results

[Horcle]

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!

[Michael Bayer]

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.

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

[Horcle]

Thanks. I forgot to mention that I had tried adding the encoding scheme to freetds.conf. I also tried other encoding schemes, all to no avail. I may try pymssql tomorrow to see what that does. I would have tried mxodbc, but I am not about to pay $379 for a driver. I may also see if I can get the MS ODBC driver for Linux to work on my Mac. 

I have to say that the MS SQL stuff is a royal PITA, but unfortunately, that is what I am stuck with at work. Uggh. (;_;)

The version of FreeTDS I have been using has always been 9.1 (although, I noticed that the Brew formula for it changed in the last few days to 9.1_1, for what that's worth).

Greg--

2014-08-18 16:15:06,664 DEBUG sqlalchemy.engine.base.Engine Row <span style="color: #660;" class="st

...

[Greg Silverman]

I think I am going to dump SQL Server and just go with Postgres. Much easier, and less of a headache. Fortunately, we are not yet in production.

Thanks!

Greg--

--
You received this message because you are subscribed to a topic in the Google Groups "sqlalchemy" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/-i4-GQpXkzY/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sqlalchemy+...@googlegroups.com.

To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

--

Greg M. Silverman

Senior Developer Analyst

Cardiovascular Informatics 

University of Minnesota

612-626-0919  

g...@umn.edu

 ›  flora-script ‹

 ›  grenzi.org  ‹

[Horcle]

Unfortunately, dumping SQL Server (in favor of Oracle) may not be an option, due to management concerns and other factors. Still working on it. 

However, I did manage to get this working with pymssql. Apparently, there is a bug with pyodbc and 64-bit python (see https://community.vertica.com/vertica/topics/mac_pyodbc_string_encoding_issue). So, short of applying the fix to the cpp file recompiling pyodbc (see http://www.vertica-forums.com/viewtopic.php?f=35&t=1863&p=6174#p6174), the easy solution is to use pymssql. I believe the upgrade I did from Lion to Mavericks allows use of 64-bit python now, so this makes sense.

Thanks!

Greg--

On Thursday, September 4, 2014 9:20:53 PM UTC-5, Horcle wrote:

I think I am going to dump SQL Server and just go with Postgres. Much easier, and less of a headache. Fortunately, we are not yet in production.

Thanks!

Greg--

To unsubscribe from this group and all its topics, send an email to sqlalchemy+unsubscribe@googlegroups.com.

[Jonathan Vanasco]

Looking at that issue, and suggested fix... I think you're best going with that route.  the stock apple Python is usually pretty bad, and it seems to be the compile settings apple selected, not python.  apple's version is often VERY out of date and has some weird settings.   It's screwed me and colleagues up a lot. I'd strongly suggest you do the following:

1. Install a second, custom python [ you can get one from Python.org in a click-installer https://www.python.org/download/mac ].  You can then update your bash so that your console user will use that python, and not break anything on your mac.

2. reinstall all pyodbc and everything else into that custom python's site-packages.  you actually need to do all this stuff whenever you have a version bump (2.7.7 to 2.7.8), but some packages magically work even if you don't.  

i've found that "shit just works" when you use a python.org interpreter.  

[Greg Silverman]

Hi Jonathan,

For the record, in my current setup, I installed python via Homebrew. Prior to that, I was using the Apple build of python, which would have been the 32-bit version that came with Lion.

Greg--

--

You received this message because you are subscribed to a topic in the Google Groups "sqlalchemy" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/-i4-GQpXkzY/unsubscribe.

To unsubscribe from this group and all its topics, send an email to sqlalchemy+...@googlegroups.com.