Re: [sqlalchemy] Issue with return results

2014-09-08 Thread 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=35t=1863p=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 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

Re: [sqlalchemy] Issue with return results

2014-09-08 Thread 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.  


On Monday, September 8, 2014 7:04:17 PM UTC-4, Horcle wrote:

 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=35t=1863p=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!


-- 
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.

Re: [sqlalchemy] Issue with return results

2014-09-08 Thread 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--

On Mon, Sep 8, 2014 at 6:31 PM, Jonathan Vanasco jonat...@findmeon.com
wrote:

 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.


 On Monday, September 8, 2014 7:04:17 PM UTC-4, Horcle wrote:

 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=35t=1863p=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!

  --
 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+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.

[sqlalchemy] Issue with return results

2014-09-04 Thread 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

Re: [sqlalchemy] Issue with return results

2014-09-04 Thread 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.



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

Re: [sqlalchemy] Issue with return results

2014-09-04 Thread 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--

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 g...@umn.edu javascript: 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 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.

Re: [sqlalchemy] Issue with return results

2014-09-04 Thread 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--


On Thu, Sep 4, 2014 at 8:31 PM, Horcle g...@umn.edu wrote:

 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 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 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
 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.




-- 
Greg M. Silverman
Senior Developer Analyst
Cardiovascular Informatics http://www.med.umn.edu/cardiology/