Thanks for the speedy reply, Michael.
I tried convert_unicode=True but it didn't quite do the trick and the bind
variables were still being shown as unicode u'table_name'
I then found this post,
http://code.google.com/p/pyodbc/issues/detail?id=159 with a work around.
set DRIVER={SQL Server Native Client 10.0}
so my create_engine command =
import urllib
quoted = urllib.quote_plus('DRIVER={SQL Server Native Client
10.0};SERVER=SXXX8\SQLEXPRESS;DATABASE=gXXXXl;UID=sa;PWD=XXX;')
db =
sqlalchemy.create_engine("mssql+pyodbc:///?odbc_connect={0}".format(quoted))
TableInstance = sqlalchemy.Table('table_name', metadata, autoload=True,
autoload_with=db) now works.
Thanks
Eugene
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 <javascript:>
>
>
>
>
>
--
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.
