[Sqlalchemy-tickets] [sqlalchemy] #2747: Version 0.8.1 can't reflect a table in mssql server 2000
Brought to you by:
zzzeek
Menu
▾
▴
[Sqlalchemy-tickets] [sqlalchemy] #2747: Version 0.8.1 can't reflect a table in mssql server 2000
|
From: sqlalchemy <mi...@zz...> - 2013-06-06 15:02:55
|
#2747: Version 0.8.1 can't reflect a table in mssql server 2000
------------------------+-----------------------------------------
Reporter: nosklo | Owner:
Type: defect | Status: new
Priority: high | Milestone:
Component: mssql | Severity: no triage selected yet
Keywords: regression | Progress State: awaiting triage
------------------------+-----------------------------------------
This is accessing a SQL Server 2000 from Linux using FreeTDS + Unixodbc +
pyodbc.
Using version 0.8.0 it works works fine. But when trying the new 0.8.1
version got a sqlalchemy.exc.ProgrammingError: (ProgrammingError)
('42000', '[42000] [FreeTDS][SQL Server]Statement(s) could not be
prepared. (8180) (SQLExecDirectW)')
By examining both generated queries, it seems the new version generates a
CAST(? AS NVARCHAR(max)) and that doesn't work on SQL SERVER 2000. Please
provide a way to disable this cast when sql version is 8 or lower.
Full code and traceback output is below:
Code:
{{{#!python
import sqlalchemy
import urllib
table_name = 'lancamentos_passivo'
con_string =
'servername=mg7684sr300;uid=rsa;pwd=some_pwd;database=Passivo;driver={FreeTDS};app=testapp'
con_string = urllib.quote_plus(con_string)
engine = sqlalchemy.create_engine('mssql:///?odbc_connect=' + con_string,
echo=True)
meta = sqlalchemy.MetaData(bind=engine)
tb = sqlalchemy.Table(table_name, meta, autoload=True)
}}}
Version rel_0_8_1 output:
{{{#!python
2013-06-06 11:51:29,925 INFO sqlalchemy.engine.base.Engine SELECT
user_name()
2013-06-06 11:51:29,925 INFO sqlalchemy.engine.base.Engine ()
2013-06-06 11:51:29,927 INFO sqlalchemy.engine.base.Engine
SELECT default_schema_name FROM
sys.database_principals
WHERE name = ?
AND type = 'S'
2013-06-06 11:51:29,927 INFO sqlalchemy.engine.base.Engine (u'dbo',)
2013-06-06 11:51:29,936 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] = CAST(? AS NVARCHAR(max)) AND
[COLUMNS_1].[TABLE_SCHEMA] = CAST(? AS NVARCHAR(max)) ORDER BY
[COLUMNS_1].[ORDINAL_POSITION]
2013-06-06 11:51:29,936 INFO sqlalchemy.engine.base.Engine
(u'lancamentos_passivo', u'dbo')
2013-06-06 11:51:29,953 INFO sqlalchemy.engine.base.Engine ROLLBACK
Traceback (most recent call last):
File "/tmp/teste.py", line 10, in <module>
tb = sqlalchemy.Table(table_name, meta, autoload=True)
File "/home/c036337/.local/lib/python2.7/site-
packages/sqlalchemy/schema.py", line 332, in __new__
table._init(name, metadata, *args, **kw)
File "/home/c036337/.local/lib/python2.7/site-
packages/sqlalchemy/schema.py", line 396, in _init
self._autoload(metadata, autoload_with, include_columns)
File "/home/c036337/.local/lib/python2.7/site-
packages/sqlalchemy/schema.py", line 424, in _autoload
self, include_columns, exclude_columns
File "/home/c036337/.local/lib/python2.7/site-
packages/sqlalchemy/engine/base.py", line 1595, in run_callable
return conn.run_callable(callable_, *args, **kwargs)
File "/home/c036337/.local/lib/python2.7/site-
packages/sqlalchemy/engine/base.py", line 1118, in run_callable
return callable_(self, *args, **kwargs)
File "/home/c036337/.local/lib/python2.7/site-
packages/sqlalchemy/engine/default.py", line 262, in reflecttable
return insp.reflecttable(table, include_columns, exclude_columns)
File "/home/c036337/.local/lib/python2.7/site-
packages/sqlalchemy/engine/reflection.py", line 397, in reflecttable
for col_d in self.get_columns(table_name, schema, **tblkw):
File "/home/c036337/.local/lib/python2.7/site-
packages/sqlalchemy/engine/reflection.py", line 254, in get_columns
**kw)
File "<string>", line 1, in <lambda>
File "/home/c036337/.local/lib/python2.7/site-
packages/sqlalchemy/engine/reflection.py", line 49, in cache
ret = fn(self, con, *args, **kw)
File "/home/c036337/.local/lib/python2.7/site-
packages/sqlalchemy/dialects/mssql/base.py", line 1050, in wrap
tablename, dbname, owner, schema, **kw)
File "/home/c036337/.local/lib/python2.7/site-
packages/sqlalchemy/dialects/mssql/base.py", line 1059, in _switch_db
return fn(*arg, **kw)
File "/home/c036337/.local/lib/python2.7/site-
packages/sqlalchemy/dialects/mssql/base.py", line 1310, in get_columns
c = connection.execute(s)
File "/home/c036337/.local/lib/python2.7/site-
packages/sqlalchemy/engine/base.py", line 662, in execute
params)
File "/home/c036337/.local/lib/python2.7/site-
packages/sqlalchemy/engine/base.py", line 761, in _execute_clauseelement
compiled_sql, distilled_params
File "/home/c036337/.local/lib/python2.7/site-
packages/sqlalchemy/engine/base.py", line 874, in _execute_context
context)
File "/home/c036337/.local/lib/python2.7/site-
packages/sqlalchemy/engine/base.py", line 1024, in _handle_dbapi_exception
exc_info
File "/home/c036337/.local/lib/python2.7/site-
packages/sqlalchemy/util/compat.py", line 163, in raise_from_cause
reraise(type(exception), exception, tb=exc_tb)
File "/home/c036337/.local/lib/python2.7/site-
packages/sqlalchemy/engine/base.py", line 867, in _execute_context
context)
File "/home/c036337/.local/lib/python2.7/site-
packages/sqlalchemy/engine/default.py", line 324, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (ProgrammingError) ('42000', '[42000]
[FreeTDS][SQL Server]Statement(s) could not be prepared. (8180)
(SQLExecDirectW)') '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] = CAST(? AS NVARCHAR(max)) AND
[COLUMNS_1].[TABLE_SCHEMA] = CAST(? AS NVARCHAR(max)) ORDER BY
[COLUMNS_1].[ORDINAL_POSITION]' (u'lancamentos_passivo', u'dbo')
}}}
Version rel_0_8_0 output:
{{{#!python
2013-06-06 11:50:11,313 INFO sqlalchemy.engine.base.Engine SELECT
user_name()
2013-06-06 11:50:11,313 INFO sqlalchemy.engine.base.Engine ()
2013-06-06 11:50:11,316 INFO sqlalchemy.engine.base.Engine
SELECT default_schema_name FROM
sys.database_principals
WHERE name = ?
AND type = 'S'
2013-06-06 11:50:11,316 INFO sqlalchemy.engine.base.Engine (u'dbo',)
2013-06-06 11:50:11,331 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]
2013-06-06 11:50:11,331 INFO sqlalchemy.engine.base.Engine
(u'lancamentos_passivo', u'dbo')
2013-06-06 11:50:11,405 INFO sqlalchemy.engine.base.Engine sp_columns
@table_name = 'lancamentos_passivo', @table_owner = 'dbo'
2013-06-06 11:50:11,406 INFO sqlalchemy.engine.base.Engine ()
2013-06-06 11:50:11,424 INFO sqlalchemy.engine.base.Engine SELECT
[C].[COLUMN_NAME], [TABLE_CONSTRAINTS_1].[CONSTRAINT_TYPE],
[C].[CONSTRAINT_NAME]
FROM [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] AS [C],
[INFORMATION_SCHEMA].[TABLE_CONSTRAINTS] AS [TABLE_CONSTRAINTS_1]
WHERE [TABLE_CONSTRAINTS_1].[CONSTRAINT_NAME] = [C].[CONSTRAINT_NAME] AND
[TABLE_CONSTRAINTS_1].[TABLE_SCHEMA] = [C].[TABLE_SCHEMA] AND
[C].[TABLE_NAME] = ? AND [C].[TABLE_SCHEMA] = ?
2013-06-06 11:50:11,424 INFO sqlalchemy.engine.base.Engine
(u'lancamentos_passivo', u'dbo')
2013-06-06 11:50:11,517 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]
2013-06-06 11:50:11,517 INFO sqlalchemy.engine.base.Engine
(u'lancamentos_passivo', u'dbo')
}}}
--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2747>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python
|
