you likely need to specify convert_unicode=True on your create_engine(), you'll
notice the u'dbo' being sent by SQLAlchemy whereas it is 'dbo' in your plain
pyodbc example. pyodbc + freetds (which I'm guessing is used here) has a lot
of problems with u'' strings.
On Feb 19, 2013, at 5:26 PM, Eugene Timmermans <eugenetimmerm...@gmail.com>
wrote:
> Hello!
> I am getting a very similar error message but sqlalchemy but not in pyodbc.
> My database is SQL EXPRESS 2008.
>
> Did you ever get a solution to your posts on Re: [sqlalchemy] SqlSoup
> ProgrammingError The data types nvarchar and ntext are incompatible in the
> equal operator
> I am getting exactly the same error but in sqlalchemy.
> I turned logging on and sqlalchemy reflected 5 larger tables with short names
> and then choked at one with two rows but a long name.
> Tables with more than 10 characters are not reflected by the sqlalchemy.Table
> command when connected to sql server.
> The statement sqlalchemy generates with table names > 10 characters errors
> out but works in a pyodbc query.
>
> I noticed short table names were being reflected so I renamed one table to
> have fewer than 11 characters and it was reflected. Long named tables work
> in pyodbc
>
> I have shown the working query followed by the failed one.
>
> PYODBC connection: WORKS
>
> cnxn = pyodbc.connect('DRIVER={SQL
> Server};SERVER=MADE_UP_HOST_NAME\SQLEXPRESS;DATABASE=MADE_UP_DB_NAME;UID=sa;PWD=MADE_UP_PASSWORD;')
>
> sqlStmt = "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]"
> cursor.execute(sqlStmt, 'site', 'dbo')
> for row in cursor.fetchall():
> print row
> cursor.execute(sqlStmt, 'taxonomy_species', 'dbo')
> for row in cursor.fetchall():
> print row
>
> output:
> (u'dbo', u'site', u'owned_date', 'NO', u'datetime2', 14, None, None, None,
> None None)
> (u'dbo', u'site', u'owned_by', 'NO', u'int', 15, None, 10, 0, None, None)
> (u'dbo', u'taxonomy_species', u'taxonomy_species_id', 'NO', u'int', 1, None,
> 10 0, None, None)
> (u'dbo', u'taxonomy_species', u'current_taxonomy_species_id', 'YES', u'int',
> 2,None, 10, 0, None, None)
> (u'dbo', u'taxonomy_species', u'nomen_number', 'YES', u'int', 3, None, 10, 0,
> None, None)
> (u'dbo', u'taxonomy_species', u'is_specific_hybrid', 'NO', u'nvarchar', 4, 1,
> None, None, None, u'SQL_Latin1_General_CP1_CI_AS')
>
>
> SQLALCHEMY connection: DOESN'T WORK
> import urllib
> quoted = urllib.quote_plus('DRIVER={SQL
> Server};SERVER=MADE_UP_HOST\SQLEXPRESS;DATABASE=MADE_UP_DB_NAME;UID=sa;PWD=MADE_UP_PASSWORD;')
> db =
> sqlalchemy.create_engine("mssql+pyodbc:///?odbc_connect={0}".format(quoted))
>
> output:
> INFO:sqlalchemy.engine.base.Engine:SELECT [C].[COLUMN_NAME],
> [R].[TABLE_SCHEMA], [R].[TABLE_NAME], [R].[COLUMN_NAME],
> [REFERENTIAL_CONSTRAINTS_1].[CONSTRAINT_NAME],
> [REFERENTIAL_CONSTRAINTS_1].[MATCH_OPTION],
> [REFERENTIAL_CONSTRAINTS_1].[UPDATE_RULE],
> [REFERENTIAL_CONSTRAINTS_1].[DELETE_RULE]
> FROM [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] AS [C],
> [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] AS [R],
> [INFORMATION_SCHEMA].[REFERENTIAL_CONSTRAINTS] AS [REFERENTIAL_CONSTRAINTS_1]
> WHERE [C].[TABLE_NAME] = ? AND [C].[TABLE_SCHEMA] = ? AND
> [C].[CONSTRAINT_NAME] = [REFERENTIAL_CONSTRAINTS_1].[CONSTRAINT_NAME] AND
> [R].[CONSTRAINT_NAME] = [REFERENTIAL_CONSTRAINTS_1].[UNIQUE_CONSTRAINT_NAME]
> AND [C].[ORDINAL_POSITION] = [R].[ORDINAL_POSITION] ORDER BY
> [REFERENTIAL_CONSTRAINTS_1].[CONSTRAINT_NAME], [R].[ORDINAL_POSITION]
> INFO:sqlalchemy.engine.base.Engine:(u'sys_lang', u'dbo')
> INFO:sqlalchemy.engine.base.Engine:select ind.index_id, ind.is_unique,
> ind.name from sys.indexes as ind join sys.tables as tab on
> ind.object_id=tab.object_id join sys.schemas as sch on
> sch.schema_id=tab.schema_id where tab.name = ? and sch.name=? and
> ind.is_primary_key=0
> INFO:sqlalchemy.engine.base.Engine:(u'sys_lang', u'dbo')
> .
> .
> .
> INFO:sqlalchemy.engine.base.Engine: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]
> INFO:sqlalchemy.engine.base.Engine:(u'taxonomy_species', u'dbo')
> INFO:sqlalchemy.engine.base.Engine:ROLLBACK
> .
> .
> .
> sqlalchemy.exc.ProgrammingError: (ProgrammingError) ('42000', '[42000]
> [Microsoft][ODBC SQL Server Driver][SQL Server]The data types nvarchar and
> ntext are incompatible in the equal to operator. (402) (SQLExecDirectW);
> [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not
> be prepared. (8180)') 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'taxonomy_species', u'dbo')
>
> Configuration
>
> Windows XP
>
> sqlalchemy.__version__ = 0.8.0b2
>
> python version = 2.6.6 (r266:84297, Aug 24 2010, 18:46:32) [MSC v.1500 32
> bit (
> Intel)]
>
> ODBC:
> Microsoft SQL Server ODBC Driver Version 03.85.1132
>
> Data Source Name: MADE_UP_DSN
> Data Source Description:
> Server: MADE_UP_NAME \SQLEXPRESS
> Database: MADE_UP_DATABASE
> Language: (Default)
> Translate Character Data: Yes
> Log Long Running Queries: No
> Log Driver Statistics: No
> Use Integrated Security: Yes
> Use Regional Settings: No
> Prepared Statements Option: Drop temporary procedures on disconnect
> Use Failover Server: No
> Use ANSI Quoted Identifiers: Yes
> Use ANSI Null, Paddings and Warnings: Yes
> Data Encryption: No
>
> Config: Sql Server 2008 Express
>
> BuildClrVersion v2.0.50727
> Collation Latin1_General_CI_AS
> CollationID 53256
> ComparisonStyle 196609
> ComputerNamePhysicalNetBIOS MADE_UP_NAME
> Edition Express Edition with Advanced Services
> EditionID -133711905
> EngineEdition 4
> InstanceName SQLEXPRESS
> IsClustered 0
> IsFullTextInstalled 0
> IsIntegratedSecurityOnly 0
> IsSingleUser 0
> LCID 1033
> LicenseType DISABLED
> MachineName MADE_UP_NAME
> NumLicenses NULL
> ProcessID 1684
> ProductVersion 10.0.1600.22
> ProductLevel RTM
> ResourceLastUpdateDateTime 2008-07-09 16:50:29.033
> ResourceVersion 10.00.1600
> ServerName MADE_UP_NAME \SQLEXPRESS
> SqlCharSet 1
> SqlCharSetName iso_1
> SqlSortOrder 0
> SqlSortOrderName bin_ascii_8
> Eugene Timmermans
>
>
> On Monday, January 24, 2011 6:17:44 PM UTC-6, 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
>
>
>
>
>
> --
> 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?hl=en.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>
--
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?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
