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-- > > > > 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-- >> >> On Thursday, September 4, 2014 5:23:02 PM UTC-5, Michael Bayer wrote: >>> >>> 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 <[email protected]> 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 <span >>> style="color: #660;" class="st >>> >>> ... >> >> -- >> 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 >> [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. >> > > > > > -- 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.
