sqlalchemy-tickets — trac tickets and updates forwarded to this list

[Sqlalchemy-tickets] [sqlalchemy] #2912: multiple table update with two columns having the same name

[sqlalchemy <mi...@zz...>]

#2912: multiple table update with two columns having the same name
--------------------------------+-----------------------------------------
 Reporter:  shnavid             |           Owner:  zzzeek
     Type:  defect              |          Status:  new
 Priority:  medium              |       Milestone:
Component:  orm                 |        Severity:  no triage selected yet
 Keywords:  multi table update  |  Progress State:  awaiting triage
--------------------------------+-----------------------------------------
 There is a bug in the way we handle multiple table updates. I am attaching
 a unit test that reproduces the issue.

 Apparently if trying to update two columns with the same name, only one
 column is actually updated while the other one is silently ignored (does
 not even show up in the UPDATE query).

 As shown in the test unit, I'd expect the Parent row to have its "name"
 column updated to "newparentname" but that's not the case.

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2912>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

Re: [Sqlalchemy-tickets] [sqlalchemy] #2911: rework of unicode conversion, re: "conditional" as well as cx_oracle

[sqlalchemy <mi...@zz...>]

#2911: rework of unicode conversion, re: "conditional" as well as cx_oracle
------------------------------+-------------------------------
      Reporter:  zzzeek       |      Owner:  zzzeek
          Type:  defect       |     Status:  new
      Priority:  high         |  Milestone:  0.9.xx
     Component:  cextensions  |   Severity:  major - 1-3 hours
    Resolution:               |   Keywords:
Progress State:  in progress  |
------------------------------+-------------------------------

Comment (by zzzeek):

 upcoming is a new ``to_conditional_unicode_processor_factory()`` in both
 Python and C. modify cx_oracle as such:

 {{{
 #!python
 @@ -749,8 +752,9 @@ class OracleDialect_cx_oracle(OracleDialect):
                              outconverter=self._detect_decimal,
                              arraysize=cursor.arraysize)
              # allow all strings to come back natively as Unicode
 -            elif defaultType in (cx_Oracle.STRING, cx_Oracle.FIXED_CHAR):
 -                return cursor.var(util.text_type, size, cursor.arraysize)
 +            #elif defaultType in (cx_Oracle.STRING,
 cx_Oracle.FIXED_CHAR):
 +            #    return cursor.var(util.text_type, size,
 cursor.arraysize)
 }}}

 cx_oracle then returns bytes or unicode (py2K only) depending on the
 column type (CHAR or NVARCHAR, etc.).  In this case we seek to do
 "conditional" unicode returns, since we don't know when the user might be
 placing Unicode() around a CHAR or NVARCHAR expression.  conditional
 unicode returns are expensive since they require an isinstance().

 But when we have cx_oracle's converter in place, now we have the unicode
 conversion overhead for all strings, not just unicode. For whatever
 reason, cx_oracle on Py2K counts all the decodes as Python function calls;
 in Py3K it does not, even if you have that converter in place.  So there's
 some less than ideal shenanigans going on inside of cx_oracle making us
 look bad.

 If we standardize cx_oracle instead on "conditional", we pay a price for
 unicode conversion when the C extensions are not in place; however, when
 the C extensions are present, the new one that does "conditional" does the
 check without any fn call overhead.   results are as follows:

 {{{
 1. cx_oracle unicode, no C ext, no check, returning unicode - 200K

 2. no cx_oracle unicode, no C ext, conditional check, returning unicode -
 300K

 3. no cx_oracle unicode, no C ext, unconditional check, returning unicode
 - 250K

 4. cx_oracle unicode, no C ext, returning str - 200K

 5. no cx_oracle unicode, no C ext, returning str - 100K

 6. cx_oracle unicode, C ext, no check, returning unicode - 100K

 7. no cx_oracle unicode, C ext, conditional check, returning unicode - 254

 8. no cx_oracle unicode, C ext, unconditional check, returning unicode -
 254

 9. cx_oracle unicode, C ext, returning str - 100K

 10. no cx_oracle unicode, C ext, returning str - 236

 }}}

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2911#comment:2>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

Re: [Sqlalchemy-tickets] [sqlalchemy] #2911: rework of unicode conversion, re: "conditional" as well as cx_oracle

[sqlalchemy <mi...@zz...>]

#2911: rework of unicode conversion, re: "conditional" as well as cx_oracle
------------------------------+-------------------------------
      Reporter:  zzzeek       |      Owner:  zzzeek
          Type:  defect       |     Status:  new
      Priority:  high         |  Milestone:  0.9.xx
     Component:  cextensions  |   Severity:  major - 1-3 hours
    Resolution:               |   Keywords:
Progress State:  in progress  |
------------------------------+-------------------------------

Comment (by zzzeek):

 working with this script and changing things:

 {{{
 from sqlalchemy.testing.profiling import profiled
 from sqlalchemy import create_engine

 engine = create_engine('oracle://scott:tiger@localhost/xe')

 try:
     engine.execute("DROP TABLE test_stuff")
 except:
     pass

 #engine.execute("CREATE TABLE test_stuff (data nvarchar2(30))")
 engine.execute("CREATE TABLE test_stuff (data varchar2(30))")

 engine.execute("INSERT INTO test_stuff (data) values (:data)",
         [{"data": "d%d" % i} for i in range(50000)])


 from sqlalchemy.sql import select, column, cast
 from sqlalchemy import Unicode, String

 with engine.connect() as conn:
     @profiled()
     def go():
         #result = conn.execute("select * from test_stuff")
         result = conn.execute(select([cast(column('data', Unicode),
 Unicode(30)).label('data')]).select_from("test_stuff"))
         #result = conn.execute(select([column('data',
 String)]).select_from("test_stuff"))
         row = result.fetchone()
         assert isinstance(row['data'], unicode)
         for row in result.fetchall():
             x = row['data']


     go()
 }}}

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2911#comment:1>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

[Sqlalchemy-tickets] [sqlalchemy] #2911: rework of unicode conversion, re: "conditional" as well as cx_oracle

[sqlalchemy <mi...@zz...>]

#2911: rework of unicode conversion, re: "conditional" as well as cx_oracle
-------------------------+------------------------------------
 Reporter:  zzzeek       |           Owner:  zzzeek
     Type:  defect       |          Status:  new
 Priority:  high         |       Milestone:  0.9.xx
Component:  cextensions  |        Severity:  major - 1-3 hours
 Keywords:               |  Progress State:  in progress
-------------------------+------------------------------------
 working on removing the cx_oracle outputtype handler for "unicode" as it
 adds overhead to the non-unicode use case.  would like to only do unicode
 conversion on oracle when unicode is requested; this requires speeding up
 the "conditional" check.

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2911>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

Re: [Sqlalchemy-tickets] [sqlalchemy] #2218: new doc section, in engines/connections, "working with DBAPI connections"

[sqlalchemy <mi...@zz...>]

#2218: new doc section, in engines/connections, "working with DBAPI connections"
--------------------------------+-------------------------------
      Reporter:  zzzeek         |      Owner:  zzzeek
          Type:  enhancement    |     Status:  new
      Priority:  high           |  Milestone:  0.9.xx
     Component:  documentation  |   Severity:  major - 1-3 hours
    Resolution:                 |   Keywords:
Progress State:  in queue       |
--------------------------------+-------------------------------
Changes (by zzzeek):

 * priority:  medium => high
 * severity:  no triage selected yet => major - 1-3 hours
 * milestone:  0.x.xx => 0.9.xx


Comment:

 this is a real thing.  please see
 https://github.com/pydata/pandas/issues/2717#issuecomment-32638821 for an
 example of where user guidance is required.

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2218#comment:1>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

Re: [Sqlalchemy-tickets] [sqlalchemy] #2910: simplify primary key reflection and support empty PKConstraint + arguments that gathers PK cols

[sqlalchemy <mi...@zz...>]

#2910: simplify primary key reflection and support empty PKConstraint + arguments
that gathers PK cols
------------------------------+----------------------------------
      Reporter:  zzzeek       |      Owner:  zzzeek
          Type:  enhancement  |     Status:  new
      Priority:  high         |  Milestone:  0.9.xx
     Component:  sql          |   Severity:  minor - half an hour
    Resolution:               |   Keywords:
Progress State:  needs tests  |
------------------------------+----------------------------------
Description changed by zzzeek:

Old description:

> {{{
> #!diff
> diff --git a/lib/sqlalchemy/engine/reflection.py
> b/lib/sqlalchemy/engine/reflection.py
> index badec84..5e06ad4 100644
> --- a/lib/sqlalchemy/engine/reflection.py
> +++ b/lib/sqlalchemy/engine/reflection.py
> @@ -461,6 +461,9 @@ class Inspector(object):
>          found_table = False
>          cols_by_orig_name = {}
>
> +        existing_pk_cols = list(table.primary_key.columns)
> +        table.primary_key.columns.clear()
> +
>          for col_d in self.get_columns(table_name, schema, **tblkw):
>              found_table = True
>              orig_name = col_d['name']
> @@ -518,17 +521,17 @@ class Inspector(object):
>                  for pk in pk_cons['constrained_columns']
>                  if pk in cols_by_orig_name and pk not in exclude_columns
>              ]
> -            pk_cols += [
> -                pk
> -                for pk in table.primary_key
> -                if pk.key in exclude_columns
> -            ]
> -            primary_key_constraint = sa_schema.PrimaryKeyConstraint(
> -                name=pk_cons.get('name'),
> -                *pk_cols
> -            )
> +            # update pk constraint name
> +            table.primary_key.name = pk_cons.get('name')
> +
> +            # set the primary key flag on new columns
> +            for col in pk_cols:
> +                col.primary_key = True
> +
> +            # fire a new event; this will add all existing
> +            # primary key columns
> +            table.primary_key._set_parent_with_dispatch(table)
>
> -            table.append_constraint(primary_key_constraint)
>
>          # Foreign keys
>          fkeys = self.get_foreign_keys(table_name, schema, **tblkw)
> diff --git a/lib/sqlalchemy/sql/schema.py b/lib/sqlalchemy/sql/schema.py
> index 6ee9287..87f7d79 100644
> --- a/lib/sqlalchemy/sql/schema.py
> +++ b/lib/sqlalchemy/sql/schema.py
> @@ -434,11 +434,6 @@ class Table(SchemaItem, TableClause):
>
>      def _autoload(self, metadata, autoload_with, include_columns,
>                    exclude_columns=()):
> -        if self.primary_key.columns:
> -            PrimaryKeyConstraint(*[
> -                c for c in self.primary_key.columns
> -                if c.key in exclude_columns
> -            ])._set_parent_with_dispatch(self)
>
>          if autoload_with:
>              autoload_with.run_callable(
> @@ -2528,10 +2523,18 @@ class
> PrimaryKeyConstraint(ColumnCollectionConstraint):
>      def _set_parent(self, table):
>          super(PrimaryKeyConstraint, self)._set_parent(table)
>
> -        if table.primary_key in table.constraints:
> -            table.constraints.remove(table.primary_key)
> -        table.primary_key = self
> -        table.constraints.add(self)
> +        if table.primary_key is not self:
> +            table.constraints.discard(table.primary_key)
> +            table.primary_key = self
> +            table.constraints.add(self)
> +
> +        table_pks = set(c for c in table.c if c.primary_key)
> +        conflicts = set(c for c in self.columns if not c.primary_key)
> +        if table_pks and conflicts:
> +            raise exc.InvalidRequestError(
> +                    "table already specifies %d columns as primary key"
> %
> +                    len(table_pks))
> +        self.columns.extend(table_pks)
>
>          for c in self.columns:
>              c.primary_key = True
>           c.primary_key = True
> }}}
>
> test such as:
>
> {{{
> from sqlalchemy import *
>
> t = Table('t', MetaData(), Column('x', Integer, primary_key=True),
>                 PrimaryKeyConstraint(mssql_clustered=True)
>             )
> print(t.primary_key)
> print(t.primary_key.kwargs)
> }}}
>
> also this should raise an error:
>
> {{{
> from sqlalchemy import *
>
> t = Table('t', MetaData(), Column('x', Integer, primary_key=True),
>             Column('q', Integer),
>                 PrimaryKeyConstraint('q', mssql_clustered=True)
>             )
> print(t.primary_key)
> print(t.primary_key.kwargs)
> }}}

New description:

 {{{
 #!diff
 diff --git a/lib/sqlalchemy/engine/reflection.py
 b/lib/sqlalchemy/engine/reflection.py
 index badec84..5e06ad4 100644
 --- a/lib/sqlalchemy/engine/reflection.py
 +++ b/lib/sqlalchemy/engine/reflection.py
 @@ -461,6 +461,9 @@ class Inspector(object):
          found_table = False
          cols_by_orig_name = {}

 +        existing_pk_cols = list(table.primary_key.columns)
 +        table.primary_key.columns.clear()
 +
          for col_d in self.get_columns(table_name, schema, **tblkw):
              found_table = True
              orig_name = col_d['name']
 @@ -518,17 +521,17 @@ class Inspector(object):
                  for pk in pk_cons['constrained_columns']
                  if pk in cols_by_orig_name and pk not in exclude_columns
              ]
 -            pk_cols += [
 -                pk
 -                for pk in table.primary_key
 -                if pk.key in exclude_columns
 -            ]
 -            primary_key_constraint = sa_schema.PrimaryKeyConstraint(
 -                name=pk_cons.get('name'),
 -                *pk_cols
 -            )
 +            # update pk constraint name
 +            table.primary_key.name = pk_cons.get('name')
 +
 +            # set the primary key flag on new columns
 +            for col in pk_cols:
 +                col.primary_key = True
 +
 +            # fire a new event; this will add all existing
 +            # primary key columns
 +            table.primary_key._set_parent_with_dispatch(table)

 -            table.append_constraint(primary_key_constraint)

          # Foreign keys
          fkeys = self.get_foreign_keys(table_name, schema, **tblkw)
 diff --git a/lib/sqlalchemy/sql/schema.py b/lib/sqlalchemy/sql/schema.py
 index 6ee9287..0eb4d9e 100644
 --- a/lib/sqlalchemy/sql/schema.py
 +++ b/lib/sqlalchemy/sql/schema.py
 @@ -434,11 +434,6 @@ class Table(SchemaItem, TableClause):

      def _autoload(self, metadata, autoload_with, include_columns,
                    exclude_columns=()):
 -        if self.primary_key.columns:
 -            PrimaryKeyConstraint(*[
 -                c for c in self.primary_key.columns
 -                if c.key in exclude_columns
 -            ])._set_parent_with_dispatch(self)

          if autoload_with:
              autoload_with.run_callable(
 @@ -2528,10 +2523,22 @@ class
 PrimaryKeyConstraint(ColumnCollectionConstraint):
      def _set_parent(self, table):
          super(PrimaryKeyConstraint, self)._set_parent(table)

 -        if table.primary_key in table.constraints:
 -            table.constraints.remove(table.primary_key)
 -        table.primary_key = self
 -        table.constraints.add(self)
 +        if table.primary_key is not self:
 +            table.constraints.discard(table.primary_key)
 +            table.primary_key = self
 +            table.constraints.add(self)
 +
 +        table_pks = [c for c in table.c if c.primary_key]
 +#        conflicts = set(c for c in self.columns if not c.primary_key)
 +        if self.columns and table_pks and set(table_pks) !=
 set(self.columns.values()):
 +            raise exc.InvalidRequestError(
 +                    "table specifies columns %s as primary_key=True, "
 +                    "not matching locally specified columns %s" %
 +                    (", ".join(c.name for c in table_pks),
 +                    ", ".join(c.name for c in self.columns)
 +                    )
 +                )
 +        self.columns.extend(table_pks)

          for c in self.columns:
              c.primary_key = True

 }}}

 test such as:

 {{{
 from sqlalchemy import *

 t = Table('t', MetaData(), Column('x', Integer, primary_key=True),
                 PrimaryKeyConstraint(mssql_clustered=True)
             )
 print(t.primary_key)
 print(t.primary_key.kwargs)
 }}}

 also this should raise an error:

 {{{
 from sqlalchemy import *

 t = Table('t', MetaData(), Column('x', Integer, primary_key=True),
             Column('q', Integer),
                 PrimaryKeyConstraint('q', mssql_clustered=True)
             )
 print(t.primary_key)
 print(t.primary_key.kwargs)
 }}}

 ..annnnd:

 {{{
 from sqlalchemy import *
 metadata = MetaData()

 t = Table('t', metadata,
    Column('a', Integer, primary_key=True),
    Column('b', Integer, primary_key=True),
    Column('c', Integer, primary_key=True),
    PrimaryKeyConstraint('b', 'c', mssql_clustered=True)
 )
 }}}

--

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2910#comment:2>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

Re: [Sqlalchemy-tickets] [sqlalchemy] #2910: simplify primary key reflection and support empty PKConstraint + arguments that gathers PK cols

[sqlalchemy <mi...@zz...>]

#2910: simplify primary key reflection and support empty PKConstraint + arguments
that gathers PK cols
------------------------------+----------------------------------
      Reporter:  zzzeek       |      Owner:  zzzeek
          Type:  enhancement  |     Status:  new
      Priority:  high         |  Milestone:  0.9.xx
     Component:  sql          |   Severity:  minor - half an hour
    Resolution:               |   Keywords:
Progress State:  needs tests  |
------------------------------+----------------------------------
Description changed by zzzeek:

Old description:

> {{{
> #!diff
> diff --git a/lib/sqlalchemy/engine/reflection.py
> b/lib/sqlalchemy/engine/reflection.py
> index badec84..5e06ad4 100644
> --- a/lib/sqlalchemy/engine/reflection.py
> +++ b/lib/sqlalchemy/engine/reflection.py
> @@ -461,6 +461,9 @@ class Inspector(object):
>          found_table = False
>          cols_by_orig_name = {}
>
> +        existing_pk_cols = list(table.primary_key.columns)
> +        table.primary_key.columns.clear()
> +
>          for col_d in self.get_columns(table_name, schema, **tblkw):
>              found_table = True
>              orig_name = col_d['name']
> @@ -518,17 +521,17 @@ class Inspector(object):
>                  for pk in pk_cons['constrained_columns']
>                  if pk in cols_by_orig_name and pk not in exclude_columns
>              ]
> -            pk_cols += [
> -                pk
> -                for pk in table.primary_key
> -                if pk.key in exclude_columns
> -            ]
> -            primary_key_constraint = sa_schema.PrimaryKeyConstraint(
> -                name=pk_cons.get('name'),
> -                *pk_cols
> -            )
> +            # update pk constraint name
> +            table.primary_key.name = pk_cons.get('name')
> +
> +            # set the primary key flag on new columns
> +            for col in pk_cols:
> +                col.primary_key = True
> +
> +            # fire a new event; this will add all existing
> +            # primary key columns
> +            table.primary_key._set_parent_with_dispatch(table)
>
> -            table.append_constraint(primary_key_constraint)
>
>          # Foreign keys
>          fkeys = self.get_foreign_keys(table_name, schema, **tblkw)
> diff --git a/lib/sqlalchemy/sql/schema.py b/lib/sqlalchemy/sql/schema.py
> index 6ee9287..5b50ea2 100644
> --- a/lib/sqlalchemy/sql/schema.py
> +++ b/lib/sqlalchemy/sql/schema.py
> @@ -434,11 +434,6 @@ class Table(SchemaItem, TableClause):
>
>      def _autoload(self, metadata, autoload_with, include_columns,
>                    exclude_columns=()):
> -        if self.primary_key.columns:
> -            PrimaryKeyConstraint(*[
> -                c for c in self.primary_key.columns
> -                if c.key in exclude_columns
> -            ])._set_parent_with_dispatch(self)
>
>          if autoload_with:
>              autoload_with.run_callable(
> @@ -2528,10 +2523,12 @@ class
> PrimaryKeyConstraint(ColumnCollectionConstraint):
>      def _set_parent(self, table):
>          super(PrimaryKeyConstraint, self)._set_parent(table)
>
> -        if table.primary_key in table.constraints:
> -            table.constraints.remove(table.primary_key)
> -        table.primary_key = self
> -        table.constraints.add(self)
> +        if table.primary_key is not self:
> +            table.constraints.discard(table.primary_key)
> +            table.primary_key = self
> +            table.constraints.add(self)
> +
> +        self.columns.extend(c for c in table.c if c.primary_key)
>
>          for c in self.columns:
>              c.primary_key = True
> }}}
>
> test such as:
>
> {{{
> from sqlalchemy import *
>
> t = Table('t', MetaData(), Column('x', Integer, primary_key=True),
>                 PrimaryKeyConstraint(mssql_clustered=True)
>             )
> print(t.primary_key)
> print(t.primary_key.kwargs)
> }}}

New description:

 {{{
 #!diff
 diff --git a/lib/sqlalchemy/engine/reflection.py
 b/lib/sqlalchemy/engine/reflection.py
 index badec84..5e06ad4 100644
 --- a/lib/sqlalchemy/engine/reflection.py
 +++ b/lib/sqlalchemy/engine/reflection.py
 @@ -461,6 +461,9 @@ class Inspector(object):
          found_table = False
          cols_by_orig_name = {}

 +        existing_pk_cols = list(table.primary_key.columns)
 +        table.primary_key.columns.clear()
 +
          for col_d in self.get_columns(table_name, schema, **tblkw):
              found_table = True
              orig_name = col_d['name']
 @@ -518,17 +521,17 @@ class Inspector(object):
                  for pk in pk_cons['constrained_columns']
                  if pk in cols_by_orig_name and pk not in exclude_columns
              ]
 -            pk_cols += [
 -                pk
 -                for pk in table.primary_key
 -                if pk.key in exclude_columns
 -            ]
 -            primary_key_constraint = sa_schema.PrimaryKeyConstraint(
 -                name=pk_cons.get('name'),
 -                *pk_cols
 -            )
 +            # update pk constraint name
 +            table.primary_key.name = pk_cons.get('name')
 +
 +            # set the primary key flag on new columns
 +            for col in pk_cols:
 +                col.primary_key = True
 +
 +            # fire a new event; this will add all existing
 +            # primary key columns
 +            table.primary_key._set_parent_with_dispatch(table)

 -            table.append_constraint(primary_key_constraint)

          # Foreign keys
          fkeys = self.get_foreign_keys(table_name, schema, **tblkw)
 diff --git a/lib/sqlalchemy/sql/schema.py b/lib/sqlalchemy/sql/schema.py
 index 6ee9287..87f7d79 100644
 --- a/lib/sqlalchemy/sql/schema.py
 +++ b/lib/sqlalchemy/sql/schema.py
 @@ -434,11 +434,6 @@ class Table(SchemaItem, TableClause):

      def _autoload(self, metadata, autoload_with, include_columns,
                    exclude_columns=()):
 -        if self.primary_key.columns:
 -            PrimaryKeyConstraint(*[
 -                c for c in self.primary_key.columns
 -                if c.key in exclude_columns
 -            ])._set_parent_with_dispatch(self)

          if autoload_with:
              autoload_with.run_callable(
 @@ -2528,10 +2523,18 @@ class
 PrimaryKeyConstraint(ColumnCollectionConstraint):
      def _set_parent(self, table):
          super(PrimaryKeyConstraint, self)._set_parent(table)

 -        if table.primary_key in table.constraints:
 -            table.constraints.remove(table.primary_key)
 -        table.primary_key = self
 -        table.constraints.add(self)
 +        if table.primary_key is not self:
 +            table.constraints.discard(table.primary_key)
 +            table.primary_key = self
 +            table.constraints.add(self)
 +
 +        table_pks = set(c for c in table.c if c.primary_key)
 +        conflicts = set(c for c in self.columns if not c.primary_key)
 +        if table_pks and conflicts:
 +            raise exc.InvalidRequestError(
 +                    "table already specifies %d columns as primary key" %
 +                    len(table_pks))
 +        self.columns.extend(table_pks)

          for c in self.columns:
              c.primary_key = True
           c.primary_key = True
 }}}

 test such as:

 {{{
 from sqlalchemy import *

 t = Table('t', MetaData(), Column('x', Integer, primary_key=True),
                 PrimaryKeyConstraint(mssql_clustered=True)
             )
 print(t.primary_key)
 print(t.primary_key.kwargs)
 }}}

 also this should raise an error:

 {{{
 from sqlalchemy import *

 t = Table('t', MetaData(), Column('x', Integer, primary_key=True),
             Column('q', Integer),
                 PrimaryKeyConstraint('q', mssql_clustered=True)
             )
 print(t.primary_key)
 print(t.primary_key.kwargs)
 }}}

--

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2910#comment:1>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

[Sqlalchemy-tickets] [sqlalchemy] #2910: simplify primary key reflection and support empty PKConstraint + arguments that gathers PK cols

[sqlalchemy <mi...@zz...>]

#2910: simplify primary key reflection and support empty PKConstraint + arguments
that gathers PK cols
-------------------------+---------------------------------------
 Reporter:  zzzeek       |           Owner:  zzzeek
     Type:  enhancement  |          Status:  new
 Priority:  high         |       Milestone:  0.9.xx
Component:  sql          |        Severity:  minor - half an hour
 Keywords:               |  Progress State:  needs tests
-------------------------+---------------------------------------
 {{{
 #!diff
 diff --git a/lib/sqlalchemy/engine/reflection.py
 b/lib/sqlalchemy/engine/reflection.py
 index badec84..5e06ad4 100644
 --- a/lib/sqlalchemy/engine/reflection.py
 +++ b/lib/sqlalchemy/engine/reflection.py
 @@ -461,6 +461,9 @@ class Inspector(object):
          found_table = False
          cols_by_orig_name = {}

 +        existing_pk_cols = list(table.primary_key.columns)
 +        table.primary_key.columns.clear()
 +
          for col_d in self.get_columns(table_name, schema, **tblkw):
              found_table = True
              orig_name = col_d['name']
 @@ -518,17 +521,17 @@ class Inspector(object):
                  for pk in pk_cons['constrained_columns']
                  if pk in cols_by_orig_name and pk not in exclude_columns
              ]
 -            pk_cols += [
 -                pk
 -                for pk in table.primary_key
 -                if pk.key in exclude_columns
 -            ]
 -            primary_key_constraint = sa_schema.PrimaryKeyConstraint(
 -                name=pk_cons.get('name'),
 -                *pk_cols
 -            )
 +            # update pk constraint name
 +            table.primary_key.name = pk_cons.get('name')
 +
 +            # set the primary key flag on new columns
 +            for col in pk_cols:
 +                col.primary_key = True
 +
 +            # fire a new event; this will add all existing
 +            # primary key columns
 +            table.primary_key._set_parent_with_dispatch(table)

 -            table.append_constraint(primary_key_constraint)

          # Foreign keys
          fkeys = self.get_foreign_keys(table_name, schema, **tblkw)
 diff --git a/lib/sqlalchemy/sql/schema.py b/lib/sqlalchemy/sql/schema.py
 index 6ee9287..5b50ea2 100644
 --- a/lib/sqlalchemy/sql/schema.py
 +++ b/lib/sqlalchemy/sql/schema.py
 @@ -434,11 +434,6 @@ class Table(SchemaItem, TableClause):

      def _autoload(self, metadata, autoload_with, include_columns,
                    exclude_columns=()):
 -        if self.primary_key.columns:
 -            PrimaryKeyConstraint(*[
 -                c for c in self.primary_key.columns
 -                if c.key in exclude_columns
 -            ])._set_parent_with_dispatch(self)

          if autoload_with:
              autoload_with.run_callable(
 @@ -2528,10 +2523,12 @@ class
 PrimaryKeyConstraint(ColumnCollectionConstraint):
      def _set_parent(self, table):
          super(PrimaryKeyConstraint, self)._set_parent(table)

 -        if table.primary_key in table.constraints:
 -            table.constraints.remove(table.primary_key)
 -        table.primary_key = self
 -        table.constraints.add(self)
 +        if table.primary_key is not self:
 +            table.constraints.discard(table.primary_key)
 +            table.primary_key = self
 +            table.constraints.add(self)
 +
 +        self.columns.extend(c for c in table.c if c.primary_key)

          for c in self.columns:
              c.primary_key = True
 }}}

 test such as:

 {{{
 from sqlalchemy import *

 t = Table('t', MetaData(), Column('x', Integer, primary_key=True),
                 PrimaryKeyConstraint(mssql_clustered=True)
             )
 print(t.primary_key)
 print(t.primary_key.kwargs)
 }}}

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2910>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

Re: [Sqlalchemy-tickets] [sqlalchemy] #2685: default schema as an execution argument

[sqlalchemy <mi...@zz...>]

#2685: default schema as an execution argument
------------------------------+-------------------------------
      Reporter:  zzzeek       |      Owner:  zzzeek
          Type:  enhancement  |     Status:  new
      Priority:  high         |  Milestone:  0.9.xx
     Component:  engine       |   Severity:  major - 1-3 hours
    Resolution:               |   Keywords:
Progress State:  needs tests  |
------------------------------+-------------------------------

Comment (by zzzeek):

 this patch works for rendering but isn't taken into account by the
 inspector.  needs that also.

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2685#comment:4>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

Re: [Sqlalchemy-tickets] [sqlalchemy] #2909: support MS ODBC for linux / Connection is busy with results for another command

[sqlalchemy <mi...@zz...>]

#2909: support MS ODBC for linux / Connection is busy with results for another
command
-------------------------------------------+-------------------------------
      Reporter:  tuomur                    |      Owner:  zzzeek
          Type:  defect                    |     Status:  new
      Priority:  high                      |  Milestone:  0.9.xx
     Component:  mssql                     |   Severity:  major - 1-3 hours
    Resolution:                            |   Keywords:
Progress State:  needs questions answered  |
-------------------------------------------+-------------------------------

Comment (by tuomur):

 Okay, it looks like two datasources is the problem. Both of the
 connections work fine on their own, but when I try to use another one I
 get the same exception. I tried creating two connections with just pyodbc
 alone, and even that crashes. Only the first connection making the trip to
 database works. So  I guess this isn't SQLAlchemy issue after all.

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2909#comment:2>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

Re: [Sqlalchemy-tickets] [sqlalchemy] #2908: Regression in 0.9.x for polymorphic query

[sqlalchemy <mi...@zz...>]

#2908: Regression in 0.9.x for polymorphic query
-----------------------------------+-------------------------------
      Reporter:  jeremy.skippen    |      Owner:  zzzeek
          Type:  defect            |     Status:  closed
      Priority:  highest           |  Milestone:  0.9.xx
     Component:  orm               |   Severity:  major - 1-3 hours
    Resolution:  fixed             |   Keywords:
Progress State:  completed/closed  |
-----------------------------------+-------------------------------
Changes (by zzzeek):

 * status:  new => closed
 * resolution:   => fixed
 * status_field:  in progress => completed/closed


Comment:

 rbd74f81bb5b1158a1bc0a4

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2908#comment:2>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

Re: [Sqlalchemy-tickets] [sqlalchemy] #2906: Unicode/UnicodeText return str instead of unicode on MySQL-Python 1.2.3

[sqlalchemy <mi...@zz...>]

#2906: Unicode/UnicodeText return str instead of unicode on MySQL-Python 1.2.3
-----------------------------------+---------------------------------------
      Reporter:  stevejohnson      |      Owner:
          Type:  defect            |     Status:  closed
      Priority:  high              |  Milestone:  0.9.xx
     Component:  mysql             |   Severity:  very major - up to 2 days
    Resolution:  fixed             |   Keywords:
Progress State:  completed/closed  |
-----------------------------------+---------------------------------------
Changes (by zzzeek):

 * status:  new => closed
 * priority:  highest => high
 * resolution:   => fixed
 * severity:  minor - half an hour => very major - up to 2 days
 * status_field:  needs tests => completed/closed


Comment:

 actual change in r1536bc4664a248faf81c62326fe1be3db

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2906#comment:12>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

Re: [Sqlalchemy-tickets] [sqlalchemy] #2909: support MS ODBC for linux / Connection is busy with results for another command (was: Reflection error: Connection is busy with results for another command)

[sqlalchemy <mi...@zz...>]

#2909: support MS ODBC for linux / Connection is busy with results for another
command
-------------------------------------------+-------------------------------
      Reporter:  tuomur                    |      Owner:  zzzeek
          Type:  defect                    |     Status:  new
      Priority:  high                      |  Milestone:  0.9.xx
     Component:  mssql                     |   Severity:  major - 1-3 hours
    Resolution:                            |   Keywords:
Progress State:  needs questions answered  |
-------------------------------------------+-------------------------------
Changes (by zzzeek):

 * severity:  no triage selected yet => major - 1-3 hours
 * component:  engine => mssql
 * priority:  medium => high
 * milestone:   => 0.9.xx
 * status_field:  awaiting triage => needs questions answered


Comment:

 correct me if I'm wrong, this doesn't seem at all like any issue with
 reflecting tables; the reflection works fine.  Something is specifically
 wrong with either your second data source or just having an application
 that's talking to two datasources at once.

 let's keep it simple.  run this:

 {{{
 import sqlalchemy as sa

 e1 = sa.create_engine('mssql+pyodbc://user:pass@work_database',
     logging_name='work',
     echo=True)

 e2 = sa.create_engine('mssql+pyodbc://user:pass@terrain_database',
     logging_name='terrain',
     echo=True)

 e1.execute("select 1").fetchall()
 e2.execute("select 1").fetchall()

 }}}

 a. does it crash and b. does it crash only on the second engine?  in that
 case, how about:

 {{{
 import sqlalchemy as sa

 e2 = sa.create_engine('mssql+pyodbc://user:pass@terrain_database',
     logging_name='terrain',
     echo=True)

 e2.execute("select 1").fetchall()

 }}}

 e.g. is there some issue just with the "terrain" database.

 also note that we haven't supported the MS ODBC driver for linux as of
 yet.

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2909#comment:1>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

Re: [Sqlalchemy-tickets] [sqlalchemy] #2906: Unicode/UnicodeText return str instead of unicode on MySQL-Python 1.2.3

[sqlalchemy <mi...@zz...>]

#2906: Unicode/UnicodeText return str instead of unicode on MySQL-Python 1.2.3
-------------------------------+----------------------------------
      Reporter:  stevejohnson  |      Owner:
          Type:  defect        |     Status:  new
      Priority:  highest       |  Milestone:  0.9.xx
     Component:  mysql         |   Severity:  minor - half an hour
    Resolution:                |   Keywords:
Progress State:  needs tests   |
-------------------------------+----------------------------------

Comment (by zzzeek):

 reverted in r232e3762b7cbf425dd911ae24

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2906#comment:11>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

Re: [Sqlalchemy-tickets] [sqlalchemy] #2906: Unicode/UnicodeText return str instead of unicode on MySQL-Python 1.2.3

[sqlalchemy <mi...@zz...>]

#2906: Unicode/UnicodeText return str instead of unicode on MySQL-Python 1.2.3
-------------------------------+----------------------------------
      Reporter:  stevejohnson  |      Owner:
          Type:  defect        |     Status:  new
      Priority:  highest       |  Milestone:  0.9.xx
     Component:  mysql         |   Severity:  minor - half an hour
    Resolution:                |   Keywords:
Progress State:  needs tests   |
-------------------------------+----------------------------------

Comment (by zzzeek):

 this got committed by accident in r2775c95b1ee30831216cc558ceb8.  need to
 revert and also it is causing jenkins failures as utf8_bin is not
 unconditionally available.

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2906#comment:10>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

[Sqlalchemy-tickets] [sqlalchemy] #2909: Reflection error: Connection is busy with results for another command

[sqlalchemy <mi...@zz...>]

#2909: Reflection error: Connection is busy with results for another command
--------------------+-----------------------------------------
 Reporter:  tuomur  |           Owner:  zzzeek
     Type:  defect  |          Status:  new
 Priority:  medium  |       Milestone:
Component:  engine  |        Severity:  no triage selected yet
 Keywords:          |  Progress State:  awaiting triage
--------------------+-----------------------------------------
 I'm getting an error when using the "Microsoft ODBC Driver 11 for SQL
 Server on Linux" (http://msdn.microsoft.com/en-us/library/hh568451.aspx)
 driver with unixODBC, pyodbc and SQLAlchemy, but only when reflecting
 tables. My connection setup seems to be ok since I can query the database
 with raw pyodbc connection and get results back. Here's a minimal example
 of my reflection:

 {{{#!python
 import sqlalchemy as sa

 metadata = sa.MetaData()
 metadata.bind = sa.create_engine('mssql+pyodbc://user:pass@work_database',
     logging_name='work',
     echo=True)

 metadata_terrain = sa.MetaData()
 metadata_terrain.bind =
 sa.create_engine('mssql+pyodbc://user:pass@terrain_database',
     logging_name='terrain',
     echo=True)

 address = sa.Table('address', metadata,
     sa.Column('addressid', sa.Integer, primary_key=True,
 autoincrement=False),
     sa.Column('refid', sa.Integer, sa.ForeignKey('dbo.Foo.Bar')),
     schema='dbo',
     autoload=True,
     extend_existing=True,
     )
 }}}

 Echoed by the engine:

 {{{
 2014-01-13 15:29:16,508 INFO sqlalchemy.engine.base.Engine.work SELECT
 user_name()
 2014-01-13 15:29:16,508 INFO sqlalchemy.engine.base.Engine.work ()
 2014-01-13 15:29:16,535 INFO sqlalchemy.engine.base.Engine.work
             SELECT default_schema_name FROM
             sys.database_principals
             WHERE name = ?
             AND type = 'S'

 2014-01-13 15:29:16,535 INFO sqlalchemy.engine.base.Engine.work (u'dbo',)
 2014-01-13 15:29:16,681 INFO sqlalchemy.engine.base.Engine.work 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]
 2014-01-13 15:29:16,683 INFO sqlalchemy.engine.base.Engine.work
 ('address', 'dbo')
 /usr/lib64/python2.6/site-packages/sqlalchemy/dialects/mssql/base.py:1350:
 SADeprecationWarning: Additional keyword arguments passed to Float
 ignored.
   coltype = coltype(**kwargs)
 2014-01-13 15:29:16,753 INFO sqlalchemy.engine.base.Engine.work sp_columns
 @table_name = 'address', @table_owner = 'dbo'
 2014-01-13 15:29:16,754 INFO sqlalchemy.engine.base.Engine.work ()
 2014-01-13 15:29:16,801 INFO sqlalchemy.engine.base.Engine.work 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] = CAST(? AS NVARCHAR(max)) AND [C].[TABLE_SCHEMA] =
 CAST(? AS NVARCHAR(max))
 2014-01-13 15:29:16,801 INFO sqlalchemy.engine.base.Engine.work
 ('address', 'dbo')
 2014-01-13 15:29:16,868 INFO sqlalchemy.engine.base.Engine.work 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] = CAST(? AS NVARCHAR(max)) AND [C].[TABLE_SCHEMA] =
 CAST(? AS NVARCHAR(max)) 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]
 2014-01-13 15:29:16,868 INFO sqlalchemy.engine.base.Engine.work
 ('address', 'dbo')
 2014-01-13 15:29:16,952 INFO sqlalchemy.engine.base.Engine.work 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
 2014-01-13 15:29:16,953 INFO sqlalchemy.engine.base.Engine.work
 ('address', 'dbo')
 2014-01-13 15:29:17,009 INFO sqlalchemy.engine.base.Engine.work select
 ind_col.index_id, ind_col.object_id, col.name from sys.columns as col join
 sys.tables as tab on tab.object_id=col.object_id join sys.index_columns as
 ind_col on (ind_col.column_id=col.column_id and
 ind_col.object_id=tab.object_id) join sys.schemas as sch on
 sch.schema_id=tab.schema_id where tab.name=? and sch.name=?
 2014-01-13 15:29:17,010 INFO sqlalchemy.engine.base.Engine.work
 ('address', 'dbo')
 }}}

 Nothing wrong there, but when I add another table on the second database:

 {{{#!python
 terrain_attachment = sa.Table('Attachment', metadata_terrain,
     schema='dbo',
     autoload=True,
     extend_existing=True,
     )
 }}}

 Echo and traceback:
 {{{
 2014-01-13 15:29:17,321 INFO sqlalchemy.engine.base.Engine.terrain SELECT
 user_name()
 2014-01-13 15:29:17,321 INFO sqlalchemy.engine.base.Engine.terrain ()
 Traceback (most recent call last):
   File "<stdin>", line 4, in <module>
   File "/usr/lib64/python2.6/site-packages/sqlalchemy/sql/schema.py", line
 355, in __new__
     table._init(name, metadata, *args, **kw)
   File "/usr/lib64/python2.6/site-packages/sqlalchemy/sql/schema.py", line
 429, in _init
     self._autoload(metadata, autoload_with, include_columns)
   File "/usr/lib64/python2.6/site-packages/sqlalchemy/sql/schema.py", line
 457, in _autoload
     self, include_columns, exclude_columns
   File "/usr/lib64/python2.6/site-packages/sqlalchemy/engine/base.py",
 line 1578, in run_callable
     with self.contextual_connect() as conn:
   File "/usr/lib64/python2.6/site-packages/sqlalchemy/engine/base.py",
 line 1645, in contextual_connect
     self.pool.connect(),
   File "/usr/lib64/python2.6/site-packages/sqlalchemy/pool.py", line 271,
 in connect
     return _ConnectionFairy.checkout(self)
   File "/usr/lib64/python2.6/site-packages/sqlalchemy/pool.py", line 466,
 in checkout
     fairy = _ConnectionRecord.checkout(pool)
   File "/usr/lib64/python2.6/site-packages/sqlalchemy/pool.py", line 329,
 in checkout
     rec = pool._do_get()
   File "/usr/lib64/python2.6/site-packages/sqlalchemy/pool.py", line 799,
 in _do_get
     return self._create_connection()
   File "/usr/lib64/python2.6/site-packages/sqlalchemy/pool.py", line 224,
 in _create_connection
     return _ConnectionRecord(self)
   File "/usr/lib64/python2.6/site-packages/sqlalchemy/pool.py", line 320,
 in __init__
     exec_once(self.connection, self)
   File "/usr/lib64/python2.6/site-packages/sqlalchemy/event/attr.py", line
 238, in exec_once
     self(*args, **kw)
   File "/usr/lib64/python2.6/site-packages/sqlalchemy/event/attr.py", line
 247, in __call__
     fn(*args, **kw)
   File "/usr/lib64/python2.6/site-
 packages/sqlalchemy/util/langhelpers.py", line 1180, in go
     return once_fn(*arg, **kw)
   File "/usr/lib64/python2.6/site-
 packages/sqlalchemy/engine/strategies.py", line 166, in first_connect
     dialect.initialize(c)
   File "/usr/lib64/python2.6/site-
 packages/sqlalchemy/connectors/pyodbc.py", line 142, in initialize
     super(PyODBCConnector, self).initialize(connection)
   File "/usr/lib64/python2.6/site-
 packages/sqlalchemy/dialects/mssql/base.py", line 1129, in initialize
     super(MSDialect, self).initialize(connection)
   File "/usr/lib64/python2.6/site-packages/sqlalchemy/engine/default.py",
 line 199, in initialize
     self._get_default_schema_name(connection)
   File "/usr/lib64/python2.6/site-
 packages/sqlalchemy/dialects/mssql/base.py", line 1145, in
 _get_default_schema_name
     user_name = connection.scalar("SELECT user_name()")
   File "/usr/lib64/python2.6/site-packages/sqlalchemy/engine/base.py",
 line 594, in scalar
     return self.execute(object, *multiparams, **params).scalar()
   File "/usr/lib64/python2.6/site-packages/sqlalchemy/engine/base.py",
 line 656, in execute
     return self._execute_text(object, multiparams, params)
   File "/usr/lib64/python2.6/site-packages/sqlalchemy/engine/base.py",
 line 805, in _execute_text
     statement, parameters
   File "/usr/lib64/python2.6/site-packages/sqlalchemy/engine/base.py",
 line 874, in _execute_context
     context)
   File "/usr/lib64/python2.6/site-packages/sqlalchemy/engine/base.py",
 line 1023, in _handle_dbapi_exception
     exc_info
   File "/usr/lib64/python2.6/site-packages/sqlalchemy/util/compat.py",
 line 185, in raise_from_cause
     reraise(type(exception), exception, tb=exc_tb)
   File "/usr/lib64/python2.6/site-packages/sqlalchemy/engine/base.py",
 line 867, in _execute_context
     context)
   File "/usr/lib64/python2.6/site-packages/sqlalchemy/engine/default.py",
 line 388, in do_execute
     cursor.execute(statement, parameters)
 sqlalchemy.exc.DBAPIError: (Error) ('HY000', '[HY000] [Microsoft][SQL
 Server Native Client 11.0]Connection is busy with results for another
 command (0) (SQLExecDirectW)') 'SELECT user_name()' ()
 }}}

 The same application code works fine with the FreeTDS driver.

 CentOS 6.5 x86_64
 Python 2.6.6
 pyodbc 3.0.3
 SQLAlchemy 0.9.1
 unixODBC 2.3.0

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2909>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

Re: [Sqlalchemy-tickets] [sqlalchemy] #2907: clean out XID state on connection.close()

[sqlalchemy <mi...@zz...>]

#2907: clean out XID state on connection.close()
-----------------------------------+-------------------------------
      Reporter:  zzzeek            |      Owner:  zzzeek
          Type:  defect            |     Status:  closed
      Priority:  high              |  Milestone:  0.9.xx
     Component:  engine            |   Severity:  major - 1-3 hours
    Resolution:  fixed             |   Keywords:
Progress State:  completed/closed  |
-----------------------------------+-------------------------------

Comment (by zzzeek):

 more adjustments and fixes in rbebf30e34d669a5ede54e512e55.

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2907#comment:2>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

Re: [Sqlalchemy-tickets] [sqlalchemy] #2908: Regression in 0.9.x for polymorphic query

[sqlalchemy <mi...@zz...>]

#2908: Regression in 0.9.x for polymorphic query
---------------------------------+-------------------------------
      Reporter:  jeremy.skippen  |      Owner:  zzzeek
          Type:  defect          |     Status:  new
      Priority:  highest         |  Milestone:  0.9.xx
     Component:  orm             |   Severity:  major - 1-3 hours
    Resolution:                  |   Keywords:
Progress State:  in progress     |
---------------------------------+-------------------------------
Changes (by zzzeek):

 * priority:  medium => highest
 * severity:  no triage selected yet => major - 1-3 hours
 * status_field:  awaiting triage => in progress


Comment:

 (internal pdb notes follow)

 the turning point that is different in 0.9 is due to clause adaptation
 failing for raw joins, branch point is in orm/util.py, when joining for
 joinedload for second User.profile:

 {{{
         if prop:
             if sql_util.clause_is_present(on_selectable,
 left_info.selectable):
                 adapt_from = on_selectable
             else:
                 adapt_from = left_info.selectable
 }}}

 the clause_is_present() check fails and it the join doesn't have a
 specific enough "adapt_from".

 that check fails because onclause.comparator._source_selectable() does
 this: the mapped entity is:

 {{{
 > /Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/util.py(744)__init__()
 -> left_orm_info = getattr(left, '_joined_from_info', left_info)
 (Pdb) print
 onclause.comparator.property.parent._with_polymorphic_selectable
 party JOIN "user" ON party.party_id = "user".party_id
 (Pdb)
 }}}

 the _adapt_to_entity is:

 {{{
 (Pdb) print onclause.comparator._adapt_to_entity.selectable
 party AS party_1 JOIN "user" AS user_1 ON party_1.party_id =
 user_1.party_id
 }}}

 the adaptation in _source_selectable() fails, by taking the
 _adapt_to_entity and replacing it for both "party" and "user" in the
 mapped selectable, forming a garbage selectable:

 {{{
 (Pdb) print onclause.comparator._source_selectable()
 party AS party_1 JOIN "user" AS user_1 ON party_1.party_id =
 user_1.party_id JOIN party AS party_1 JOIN "user" AS user_1 ON
 party_1.party_id = user_1.party_id ON party_1.party_id = user_1.party_id
 }}}

 so the issue is ultimately with _source_selectable().

 so far a diff like this is working:

 {{{
 diff --git a/lib/sqlalchemy/orm/relationships.py
 b/lib/sqlalchemy/orm/relationships.py
 index 982f10a..6fdedd3 100644
 --- a/lib/sqlalchemy/orm/relationships.py
 +++ b/lib/sqlalchemy/orm/relationships.py
 @@ -747,11 +747,10 @@ class RelationshipProperty(StrategizedProperty):
              return self.property.parent

          def _source_selectable(self):
 -            elem = self.property.parent._with_polymorphic_selectable
 -            if self.adapter:
 -                return self.adapter(elem)
 +            if self._adapt_to_entity:
 +                return self._adapt_to_entity.selectable
              else:
 -                return elem
 +                return self.property.parent._with_polymorphic_selectable

          def __clause_element__(self):
              adapt_from = self._source_selectable()
 }}}

 but we can't test it fully because #2907 is breaking all the tests, so
 have to fix that first.

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2908#comment:1>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

[Sqlalchemy-tickets] [sqlalchemy] #2908: Regression in 0.9.x for polymorphic query

[sqlalchemy <mi...@zz...>]

#2908: Regression in 0.9.x for polymorphic query
----------------------------+-----------------------------------------
 Reporter:  jeremy.skippen  |           Owner:  zzzeek
     Type:  defect          |          Status:  new
 Priority:  medium          |       Milestone:  0.9.xx
Component:  orm             |        Severity:  no triage selected yet
 Keywords:                  |  Progress State:  awaiting triage
----------------------------+-----------------------------------------
 I have a reasonably complex query which worked in 0.8.x and no longer
 works in 0.9.x.

 I have created a script that defines the problematic entities and then
 queries for them.

 Here is an ERD for the script:
 [[Image(http://i40.tinypic.com/2hf3uja.png)]]

 There are 2 join paths from `Record` to `UserProfile`:
   - `Record` -> `User` -> `UserProfile`, and
   - `Record` -> `RecordUser2` -> `User` -> `UserProfile`

 Looking at the SQL output the error is pretty obvious; the second join
 from `User` to `UserProfile` uses the foreign key from the first join.

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2908>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

Re: [Sqlalchemy-tickets] [sqlalchemy] #2907: clean out XID state on connection.close()

[sqlalchemy <mi...@zz...>]

#2907: clean out XID state on connection.close()
-----------------------------------+-------------------------------
      Reporter:  zzzeek            |      Owner:  zzzeek
          Type:  defect            |     Status:  closed
      Priority:  high              |  Milestone:  0.9.xx
     Component:  engine            |   Severity:  major - 1-3 hours
    Resolution:  fixed             |   Keywords:
Progress State:  completed/closed  |
-----------------------------------+-------------------------------
Changes (by zzzeek):

 * status:  new => closed
 * resolution:   => fixed
 * severity:  minor - half an hour => major - 1-3 hours
 * status_field:  in queue => completed/closed


Comment:

 r9c64607572a04eb2ed7981db8999

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2907#comment:1>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

[Sqlalchemy-tickets] [sqlalchemy] #2907: clean out XID state on connection.close()

[sqlalchemy <mi...@zz...>]

#2907: clean out XID state on connection.close()
--------------------+---------------------------------------
 Reporter:  zzzeek  |           Owner:  zzzeek
     Type:  defect  |          Status:  new
 Priority:  high    |       Milestone:  0.9.xx
Component:  engine  |        Severity:  minor - half an hour
 Keywords:          |  Progress State:  in queue
--------------------+---------------------------------------
 On Friday, 10 January 2014 08:06:16 UTC-8, Michael Bayer wrote:
 maybe.   I was thinking, is the Session really doing the right thing here
 by not getting involved, but I think yeah that still might be appropriate.
 so we’d need to carry along some extra information about the transaction
 with the connection so that do_rollback() and do_commit() can pick up on
 that.

 or maybe session.close() needs to do some bookkeeping with the
 SessionTransaction.   not sure.

 The call stack here is:

 -> session.close()
   sqlalchemy/orm/session.py(999)close()
 -> transaction.close()
   sqlalchemy/orm/session.py(437)close()
 -> connection.close()
   sqlalchemy/engine/base.py(583)close()
 -> conn.close()
   sqlalchemy/pool.py(579)close()
 -> self.checkin()
   sqlalchemy/pool.py(506)checkin()
 -> self._pool, None, self._echo, fairy=self)
   sqlalchemy/pool.py(432)_finalize_fairy()
 -> pool._dialect.do_rollback(fairy)

 The tpc state (xid, is_prepared) is held on the TwoPhaseTransaction
 object, referenced (self.__transaction) by the engine Connection object
 which calls conn.close(). I don't think SessionTransaction need be
 involved here, but perhaps the engine Connection / Transaction should.
 Some options are:

 1. In engine Connection.close(), when self.__transaction.is_active pass
 down xid, is_prepared to the _ConnectionFairy. That would imply muddying
 the DBAPI Connection contract of the engine Connection.__connection.

 2. Make the engine Transaction aware of the configured reset_on_return
 behaviour so that Transaction.close() can either rollback or commit.
 Connection.close() could then call Transaction.close().

 3. Keep track of xid, is_prepared on the pool's connection_record.

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2907>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

Re: [Sqlalchemy-tickets] [sqlalchemy] #2795: Add pypyodbc support

[sqlalchemy <mi...@zz...>]

#2795: Add pypyodbc support
------------------------------+----------------------------------
      Reporter:  sontek       |      Owner:  zzzeek
          Type:  enhancement  |     Status:  new
      Priority:  high         |  Milestone:  0.9.xx
     Component:  mssql        |   Severity:  minor - half an hour
    Resolution:               |   Keywords:
Progress State:  in queue     |
------------------------------+----------------------------------
Changes (by zzzeek):

 * priority:  medium => high
 * milestone:  0.x.xx => 0.9.xx


--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2795#comment:8>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

Re: [Sqlalchemy-tickets] [sqlalchemy] #2795: Add pypyodbc support

[sqlalchemy <mi...@zz...>]

#2795: Add pypyodbc support
------------------------------+----------------------------------
      Reporter:  sontek       |      Owner:  zzzeek
          Type:  enhancement  |     Status:  new
      Priority:  medium       |  Milestone:  0.x.xx
     Component:  mssql        |   Severity:  minor - half an hour
    Resolution:               |   Keywords:
Progress State:  in queue     |
------------------------------+----------------------------------

Comment (by zzzeek):

 OK I've gotten some pypyodbc tests to run, will need its own
 connector/dialect with some different options and different way of
 detecting server version info.  pypyodbc isn't working for me with pypy at
 the moment but is working with cpython.

 here's the flags i need to have detected differently when its in use:

 {{{
 #!diff
 diff --git a/lib/sqlalchemy/connectors/pyodbc.py
 b/lib/sqlalchemy/connectors/pyodbc.py
 index 284de28..b920516 100644
 --- a/lib/sqlalchemy/connectors/pyodbc.py
 +++ b/lib/sqlalchemy/connectors/pyodbc.py
 @@ -47,7 +47,7 @@ class PyODBCConnector(Connector):

      @classmethod
      def dbapi(cls):
 -        return __import__('pyodbc')
 +        return __import__('pypyodbc')

      def create_connect_args(self, url):
          opts = url.translate_connect_args(username='user')
 @@ -138,6 +138,7 @@ class PyODBCConnector(Connector):
          else:
              self.supports_unicode_binds = True

 +        self.supports_unicode_binds = False
          # run other initialization which asks for user name, etc.
          super(PyODBCConnector, self).initialize(connection)

 diff --git a/lib/sqlalchemy/dialects/mssql/base.py
 b/lib/sqlalchemy/dialects/mssql/base.py
 index 90fd1f3..d5fa15d 100644
 --- a/lib/sqlalchemy/dialects/mssql/base.py
 +++ b/lib/sqlalchemy/dialects/mssql/base.py
 @@ -1127,6 +1127,7 @@ class MSDialect(default.DefaultDialect):

      def initialize(self, connection):
          super(MSDialect, self).initialize(connection)
 +        self.server_version_info = MS_2008_VERSION
          if self.server_version_info[0] not in list(range(8, 17)):
              # FreeTDS with version 4.2 seems to report here
              # a number like "95.10.255".  Don't know what
 }}}

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2795#comment:7>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

Re: [Sqlalchemy-tickets] [sqlalchemy] #2906: Unicode/UnicodeText return str instead of unicode on MySQL-Python 1.2.3

[sqlalchemy <mi...@zz...>]

#2906: Unicode/UnicodeText return str instead of unicode on MySQL-Python 1.2.3
-------------------------------+----------------------------------
      Reporter:  stevejohnson  |      Owner:
          Type:  defect        |     Status:  new
      Priority:  highest       |  Milestone:  0.9.xx
     Component:  mysql         |   Severity:  minor - half an hour
    Resolution:                |   Keywords:
Progress State:  needs tests   |
-------------------------------+----------------------------------
Changes (by zzzeek):

 * priority:  medium => highest
 * milestone:   => 0.9.xx
 * severity:  no triage selected yet => minor - half an hour
 * status_field:  awaiting triage => needs tests


--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2906#comment:9>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

Re: [Sqlalchemy-tickets] [sqlalchemy] #2906: Unicode/UnicodeText return str instead of unicode on MySQL-Python 1.2.3

[sqlalchemy <mi...@zz...>]

#2906: Unicode/UnicodeText return str instead of unicode on MySQL-Python 1.2.3
----------------------------------+------------------------------------
      Reporter:  stevejohnson     |      Owner:
          Type:  defect           |     Status:  new
      Priority:  medium           |  Milestone:
     Component:  mysql            |   Severity:  no triage selected yet
    Resolution:                   |   Keywords:
Progress State:  awaiting triage  |
----------------------------------+------------------------------------

Comment (by stevejohnson):

 I applied the patch and can confirm that it fixes the issue in my test.

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2906#comment:8>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python