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.
