- changed status to resolved
sybase: can't wrap db table into Table object if the table has some foreign keys
Issue #3509
resolved
DETAILED DESCRIPTION:
I face this issue when try to create Table object for sybase table with foreign keys.
base.py:1230 ERROR Error closing cursor
Traceback (most recent call last):
File sqlalchemy/engine/base.py", line 1226, in _safe_close_cursor
cursor.close()
File "Sybase.py", line 459, in close
self._cmd.ct_cmd_drop()
File "Sybase.py", line 265, in _clientmsg_cb
raise DatabaseError(msg)
DatabaseError: Layer: 1, Origin: 1
ct_cmd_drop(): user api layer: external error: This routine can be called only if the command structure is idle.
12:19:36 pool.py:638 ERROR Exception during reset or similar
Traceback (most recent call last):
File "sqlalchemy/pool.py", line 631, in _finalize_fairy
fairy._reset(pool)
File "sqlalchemy/pool.py", line 771, in _reset
pool._dialect.do_rollback(self)
File "sqlalchemy/engine/default.py", line 420, in do_rollback
dbapi_connection.rollback()
File "Sybase.py", line 1201, in rollback
self.execute('rollback transaction')
File "Sybase.py", line 1229, in execute
cursor.execute(sql)
File "Sybase.py", line 734, in execute
self._start()
File "Sybase.py", line 875, in _start
status = self._cmd.ct_send()
File "Sybase.py", line 265, in _clientmsg_cb
raise DatabaseError(msg)
DatabaseError: Layer: 1, Origin: 1
ct_send(): user api layer: external error: This routine cannot be called because another command structure has results pending.
Traceback (most recent call last):
....................................
cur_table = Table(record['tablename'], self.metadata, autoload=True)
File "sqlalchemy/sql/schema.py", line 416, in __new__
metadata._remove_table(name, schema)
File "sqlalchemy/util/langhelpers.py", line 60, in __exit__
compat.reraise(exc_type, exc_value, exc_tb)
File "sqlalchemy/sql/schema.py", line 411, in __new__
table._init(name, metadata, *args, **kw)
File "sqlalchemy/sql/schema.py", line 484, in _init
self._autoload(metadata, autoload_with, include_columns)
File "sqlalchemy/sql/schema.py", line 508, in _autoload
self, include_columns, exclude_columns
File "sqlalchemy/engine/base.py", line 1968, in run_callable
return conn.run_callable(callable_, *args, **kwargs)
File "sqlalchemy/engine/base.py", line 1477, in run_callable
return callable_(self, *args, **kwargs)
File "sqlalchemy/engine/default.py", line 364, in reflecttable
return insp.reflecttable(table, include_columns, exclude_columns)
File "sqlalchemy/engine/reflection.py", line 578, in reflecttable
exclude_columns, reflection_options)
File "sqlalchemy/engine/reflection.py", line 666, in _reflect_fk
table_name, schema, **table.dialect_kwargs)
File "sqlalchemy/engine/reflection.py", line 447, in get_foreign_keys
**kw)
File "<string>", line 2, in get_foreign_keys
File "sqlalchemy/engine/reflection.py", line 54, in cache
ret = fn(self, con, *args, **kw)
File "sqlalchemy/dialects/sybase/base.py", line 624, in get_foreign_keys
c = connection.execute(REFTABLE_SQL, table_id=reftable_id)
File "sqlalchemy/engine/base.py", line 914, in execute
return meth(self, multiparams, params)
File "sqlalchemy/sql/elements.py", line 323, in _execute_on_connection
return connection._execute_clauseelement(self, multiparams, params)
File "sqlalchemy/engine/base.py", line 1010, in _execute_clauseelement
compiled_sql, distilled_params
File "sqlalchemy/engine/base.py", line 1146, in _execute_context
context)
File "sqlalchemy/engine/base.py", line 1334, in _handle_dbapi_exception
self._autorollback()
File "sqlalchemy/engine/base.py", line 791, in _autorollback
self._root._rollback_impl()
File "sqlalchemy/engine/base.py", line 670, in _rollback_impl
self._handle_dbapi_exception(e, None, None, None, None)
File "sqlalchemy/engine/base.py", line 1266, in _handle_dbapi_exception
exc_info
File "sqlalchemy/util/compat.py", line 199, in raise_from_cause
reraise(type(exception), exception, tb=exc_tb)
File "sqlalchemy/engine/base.py", line 668, in _rollback_impl
self.engine.dialect.do_rollback(self.connection)
File "sqlalchemy/engine/default.py", line 420, in do_rollback
dbapi_connection.rollback()
File "Sybase.py", line 1201, in rollback
self.execute('rollback transaction')
File "Sybase.py", line 1229, in execute
cursor.execute(sql)
File "Sybase.py", line 734, in execute
self._start()
File "Sybase.py", line 875, in _start
status = self._cmd.ct_send()
File "Sybase.py", line 265, in _clientmsg_cb
raise DatabaseError(msg)
sqlalchemy.exc.DatabaseError: (Sybase.DatabaseError) Layer: 1, Origin: 1
ct_send(): user api layer: external error: This routine cannot be called because another command structure has results pending.
I fixed this issue in the following way:
*** dialects/sybase/base.py Thu Jun 25 17:11:32 2015
--- sqlalchemy/dialects/sybase/base.py Mon Aug 10 12:41:14 2015
***************
*** 609,615 ****
WHERE r.tableid = :table_id
""")
referential_constraints = connection.execute(REFCONSTRAINT_SQL,
! table_id=table_id)
REFTABLE_SQL = text("""
SELECT o.name AS name, u.name AS 'schema'
--- 609,615 ----
WHERE r.tableid = :table_id
""")
referential_constraints = connection.execute(REFCONSTRAINT_SQL,
! table_id=table_id).fetchall()
REFTABLE_SQL = text("""
SELECT o.name AS name, u.name AS 'schema'
***************
Comments (3)
-
repo owner -
repo owner - Fixed two issues regarding Sybase reflection, allowing tables
without primary keys to be reflected as well as ensured that
a SQL statement involved in foreign key detection is pre-fetched up
front to avoid driver issues upon nested queries. Fixes here
courtesy Eugene Zapolsky; note that we cannot currently test
Sybase to locally verify these changes.
fixes
#3508fixes#3509
(cherry picked from commit a7d7941d3ebafd16f603785c4677e371c675d1c0)
→ <<cset 90327f6a9369>>
- Fixed two issues regarding Sybase reflection, allowing tables
without primary keys to be reflected as well as ensured that
a SQL statement involved in foreign key detection is pre-fetched up
front to avoid driver issues upon nested queries. Fixes here
courtesy Eugene Zapolsky; note that we cannot currently test
Sybase to locally verify these changes.
fixes
-
repo owner thank you, note I had to merge these as-is without any testing. Please test the latest master and /or rel_1_0 branch! thanks.
- Log in to comment
#3508fixes#3509→ <<cset a7d7941d3eba>>