On Jan 25, 2011, at 12:11 PM, Mark Sharp wrote: > 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?
The usage of a phrase like u'somestring' in an email indicates a Python unicode literal. Pyodbc does not accept Python unicode literals when FreeTDS is used. For other ODBC drivers like those of Windows, it does. I did not intend to mean that you pass the phrase "and not u" literally, I intended to mean that the strings you pass to Pyodbc when using FreeTDS should not include the "u" indicator preceding string literals. I'm not sure what platform you're on but it seems like its not using FreeTDS, since the stack trace you sent included the names of the schema and tablename as Python unicode literals. SQLAlchemy will not send strings as Python unicode literals if FreeTDS is in use, nor does it receive them back in result sets without special settings. If SQLAlchemy was in fact sending Python unicode literals on your platform and you were in fact using FreeTDS, it would be producing a different error message in any case. Without understanding what is special about your "information schema" tables, why Pyodbc is producing a comparison of NVARCHAR to NTEXT, and why your SQL server installation doesn't accept this comparison, this issue cannot be resolved. I cannot reproduce the issue here. The Pyodbc author will know a lot more than I do about type coercion at the Pyodbc level. The query is extremely basic and should not fail with a pure Pyodbc script the way it does. > > 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. > [email protected] > > > > > 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. >>> [email protected] >>> >>> >>> >>> >>> 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. >>>>> [email protected] >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> -- >>>>> You received this message because you are subscribed to the Google Groups >>>>> "sqlalchemy" group. >>>>> To post to this group, send email to [email protected]. >>>>> To unsubscribe from this group, send email to >>>>> [email protected]. >>>>> 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 [email protected]. >>>> To unsubscribe from this group, send email to >>>> [email protected]. >>>> 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 [email protected]. >>> To unsubscribe from this group, send email to >>> [email protected]. >>> 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 [email protected]. >> To unsubscribe from this group, send email to >> [email protected]. >> 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 [email protected]. > To unsubscribe from this group, send email to > [email protected]. > 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 [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
