[Sqlalchemy-tickets] Issue #3509: sybase: can't wrap db table into Table object if the table has some foreign keys (zzzeek/sqlalchemy)

[Eugene Z. <iss...@bi...>]

New issue 3509: sybase: can't wrap db table into Table object if the table has some foreign keys
https://bitbucket.org/zzzeek/sqlalchemy/issues/3509/sybase-cant-wrap-db-table-into-table

Eugene Zapolsky:

**DETAILED DESCRIPTION:**

I face this issue when try to create Table object for sybase table with foreign keys.

```
#!python

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:


```
#!python

*** 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'
***************

```