Michael, I am not following your meaning when you say """on OSX you need to pass "and not u" """. Where does the string "and not u" go and what is its purpose?
Why did you use a unicode string for the select statement and the other arguments to cursor.execute? Mak P.S. I have run the pyodbc test scripts without any errors being reported using the same database connection string. $ python tests/dbapitests.py DSN=frankie-w7-animal-sa ---------------------------------------------------------------------- Ran 35 tests in 1.308s R. Mark Sharp, Ph.D. msh...@sfbr.org<mailto:msh...@sfbr.org> On Jan 24, 2011, at 7:42 PM, Michael Bayer wrote: Here's a script that runs fine for me on Windows as well as OSX using SQL Server 2008 with pyodbc 2.1.4, I also tried 2.1.8 on OSX without issue (on OSX you need to pass '' and not u''). Try it on yours, and if you continue to get the NTEXT error you may have to report this issue to the pyodbc mailing list. import pyodbc conn = pyodbc.connect('DSN=mydsn', user='scott', password='tiger') cursor = conn.cursor() cursor.execute(u""" SELECT [COLUMNS_1].[TABLE_SCHEMA], [COLUMNS_1].[TABLE_NAME], [COLUMNS_1].[COLUMN_NAME], [COLUMNS_1].[IS_NULLABLE], [COLUMNS_1].[DATA_TYPE], [COLUMNS_1].[ORDINAL_POSITION], [COLUMNS_1].[CHARACTER_MAXIMUM_LENGTH], [COLUMNS_1].[NUMERIC_PRECISION], [COLUMNS_1].[NUMERIC_SCALE], [COLUMNS_1].[COLUMN_DEFAULT], [COLUMNS_1].[COLLATION_NAME] FROM [INFORMATION_SCHEMA].[COLUMNS] AS [COLUMNS_1] WHERE [COLUMNS_1].[TABLE_NAME] = ? AND [COLUMNS_1].[TABLE_SCHEMA] = ? ORDER BY [COLUMNS_1].[ORDINAL_POSITION] """, (u'tf_valid_species', u'dbo')) print cursor.fetchall() On Jan 24, 2011, at 8:35 PM, Mark Sharp wrote: Michael, pyodbc.version returns 2.1.8 I tried your code snippet with the same results. I am going to try creating some tables and see how that goes. Mark R. Mark Sharp, Ph.D. msh...@sfbr.org<mailto:msh...@sfbr.org> On Jan 24, 2011, at 6:33 PM, Michael Bayer wrote: that's weird since I don't have that issue testing against a 2008 database. I can't actually reproduce it. You might want to try ensuring you're on a recent Pyodbc build. You can also try this: from sqlalchemy import create_engine from sqlalchemy.ext.sqlsoup import SqlSoup db = SqlSoup(create_engine("mssql+pyodbc://mydsn", convert_unicode=True)) On Jan 24, 2011, at 7:17 PM, Mark Sharp wrote: I decided to try to get some reflection using SqlSoup on a MS SQL Server 2008 database. This is a copy of my session with the DSN obfuscated. >>> from sqlalchemy.ext.sqlsoup import SqlSoup >>> engine1 = SqlSoup("mssql+pyodbc://mydsn") >>> valid_species = db.valid_species.all() Traceback (most recent call last): File "<stdin>", line 1, in <module> NameError: name 'db' is not defined >>> valid_species = engine1.valid_species.all() Traceback (most recent call last): File "<stdin>", line 1, in <module> File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/ext/sqlsoup.py", line 792, in __getattr__ return self.entity(attr) File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/ext/sqlsoup.py", line 789, in entity return self.map_to(attr, tablename=attr, schema=schema) File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/ext/sqlsoup.py", line 672, in map_to schema=schema or self.schema) File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/schema.py", line 210, in __new__ table._init(name, metadata, *args, **kw) File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/schema.py", line 258, in _init include_columns=include_columns) File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/engine/base.py", line 1863, in reflecttable self.dialect.reflecttable(conn, table, include_columns) File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/engine/default.py", line 228, in reflecttable return insp.reflecttable(table, include_columns) File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/engine/reflection.py", line 382, in reflecttable for col_d in self.get_columns(table_name, schema, **tblkw): File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/engine/reflection.py", line 229, in get_columns **kw) File "<string>", line 1, in <lambda> File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/engine/reflection.py", line 46, in cache ret = fn(self, con, *args, **kw) File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/dialects/mssql/base.py", line 1244, in get_columns c = connection.execute(s) File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/engine/base.py", line 1191, in execute params) File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/engine/base.py", line 1271, in _execute_clauseelement return self.__execute_context(context) File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/engine/base.py", line 1302, in __execute_context context.parameters[0], context=context) File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/engine/base.py", line 1401, in _cursor_execute context) File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/engine/base.py", line 1394, in _cursor_execute context) File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/engine/default.py", line 299, in do_execute cursor.execute(statement, parameters) sqlalchemy.exc.ProgrammingError: (ProgrammingError) ('42000', '[42000] [Actual][SQL Server] The data types nvarchar and ntext are incompatible in the equal to operator. (402) (SQLExecDirectW)') u'SELECT [COLUMNS_1].[TABLE_SCHEMA], [COLUMNS_1].[TABLE_NAME], [COLUMNS_1].[COLUMN_NAME], [COLUMNS_1].[IS_NULLABLE], [COLUMNS_1].[DATA_TYPE], [COLUMNS_1].[ORDINAL_POSITION], [COLUMNS_1].[CHARACTER_MAXIMUM_LENGTH], [COLUMNS_1].[NUMERIC_PRECISION], [COLUMNS_1].[NUMERIC_SCALE], [COLUMNS_1].[COLUMN_DEFAULT], [COLUMNS_1].[COLLATION_NAME] \nFROM [INFORMATION_SCHEMA].[COLUMNS] AS [COLUMNS_1] \nWHERE [COLUMNS_1].[TABLE_NAME] = ? AND [COLUMNS_1].[TABLE_SCHEMA] = ? ORDER BY [COLUMNS_1].[ORDINAL_POSITION]' (u'valid_species', u'dbo') R. Mark Sharp, Ph.D. msh...@sfbr.org<mailto:msh...@sfbr.org> -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com<mailto:sqlalchemy@googlegroups.com>. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com<mailto:sqlalchemy+unsubscr...@googlegroups.com>. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com<mailto:sqlalchemy@googlegroups.com>. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com<mailto:sqlalchemy+unsubscr...@googlegroups.com>. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com<mailto:sqlalchemy@googlegroups.com>. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com<mailto:sqlalchemy+unsubscr...@googlegroups.com>. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com<mailto:sqlalchemy@googlegroups.com>. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com<mailto:sqlalchemy+unsubscr...@googlegroups.com>. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
