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

Re: [Sqlalchemy-tickets] [sqlalchemy] #2715: add more explicit documentation/flags for the many gaerdbms DBAPI options

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

#2715: add more explicit documentation/flags for the many gaerdbms DBAPI options
------------------------------+--------------------------------------
      Reporter:  moraes       |      Owner:  zzzeek
          Type:  enhancement  |     Status:  new
      Priority:  medium       |  Milestone:  0.8.xx
     Component:  engine       |   Severity:  minor - half an hour
    Resolution:               |   Keywords:  gaerdbms, mysql, dialect
Progress State:  in queue     |
------------------------------+--------------------------------------
Changes (by zzzeek):

 * status_field:  awaiting triage => in queue
 * severity:  no triage selected yet => minor - half an hour
 * milestone:   => 0.8.xx


Comment:

 Adding to the list of options we have in #2649, we have:

 {{{
             # Production mode.
             from google.storage.speckle.python.api import rdbms_apiproxy

             # Development mode with remote database.
             from google.storage.speckle.python.api import rdbms_googleapi

             # Development mode with local database.
             from google.appengine.api import rdbms_mysqldb

 }}}

 We can document right off that any of these APIs can be selected as
 follows (this is your workaround for now):

 {{{
 from google.xyz.qpr import some_api

 engine = create_engine("mysql+gaerdbms://...", module=some_api)

 }}}

 we should add a simple flag to support selecting among these APIs
 explicitly:

 {{{

 engine = create_engine("mysql+gaerdbms://...",
 googleapi="apiproxy|mysqldb|googleapi")

 }}}

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

Re: [Sqlalchemy-tickets] [sqlalchemy] #2715: add more explicit documentation/flags for the many gaerdbms DBAPI options (was: gaerdbms: local database mode)

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

#2715: add more explicit documentation/flags for the many gaerdbms DBAPI options
----------------------------------+--------------------------------------
      Reporter:  moraes           |      Owner:  zzzeek
          Type:  enhancement      |     Status:  new
      Priority:  medium           |  Milestone:
     Component:  engine           |   Severity:  no triage selected yet
    Resolution:                   |   Keywords:  gaerdbms, mysql, dialect
Progress State:  awaiting triage  |
----------------------------------+--------------------------------------

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

Re: [Sqlalchemy-tickets] [sqlalchemy] #1958: google app engine support

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

#1958: google app engine support
-----------------------------------+------------------------------------
      Reporter:  zzzeek            |      Owner:  zzzeek
          Type:  enhancement       |     Status:  closed
      Priority:  medium            |  Milestone:  0.x.xx
     Component:  mysql             |   Severity:  no triage selected yet
    Resolution:  fixed             |   Keywords:
Progress State:  completed/closed  |
-----------------------------------+------------------------------------
Changes (by zzzeek):

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


Comment:

 somehow this forgot to get closed.

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

Re: [Sqlalchemy-tickets] [sqlalchemy] #2698: loads() sometimes broken

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

#2698: loads() sometimes broken
-------------------------+-------------------------------------------------
      Reporter:          |      Owner:  zzzeek
  pjfasano               |     Status:  closed
          Type:  defect  |  Milestone:  0.8.xx
      Priority:  medium  |   Severity:  minor - half an hour
     Component:  ext     |   Keywords:
    Resolution:  fixed   |  serialize,serializer,sqlalchemy.ext.serializer
Progress State:          |
  completed/closed       |
-------------------------+-------------------------------------------------
Changes (by zzzeek):

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


Comment:

 fixed this and a bunch of other pretty obvious problems in r1fba0a40674c.

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

Re: [Sqlalchemy-tickets] [sqlalchemy] #2714: join on heavily with_polymorphic/base/subclass mismatch

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

#2714: join on heavily with_polymorphic/base/subclass mismatch
-----------------------------------+---------------------------------------
      Reporter:  zzzeek            |      Owner:  zzzeek
          Type:  defect            |     Status:  closed
      Priority:  highest           |  Milestone:  0.8.xx
     Component:  orm               |   Severity:  very major - up to 2 days
    Resolution:  fixed             |   Keywords:
Progress State:  completed/closed  |
-----------------------------------+---------------------------------------
Changes (by zzzeek):

 * status:  new => closed
 * resolution:   => fixed
 * status_field:  needs tests => completed/closed


Comment:

 diff:@4454510c657f:fe903fc5699a

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

Re: [Sqlalchemy-tickets] [sqlalchemy] #2716: table.tometadata(metadata) should copy column.info dictionary instead of referencing same dictionary

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

#2716: table.tometadata(metadata) should copy column.info dictionary instead of
referencing same dictionary
----------------------------+----------------------------------
      Reporter:  kentbower  |      Owner:  zzzeek
          Type:  defect     |     Status:  new
      Priority:  low        |  Milestone:  0.8.xx
     Component:  schema     |   Severity:  minor - half an hour
    Resolution:             |   Keywords:
Progress State:  in queue   |
----------------------------+----------------------------------
Changes (by zzzeek):

 * milestone:   => 0.8.xx
 * component:  orm => schema
 * severity:  no triage selected yet => minor - half an hour
 * status_field:  awaiting triage => in queue


Comment:

 supply a pull request at https://bitbucket.org/sqlalchemy/sqlalchemy

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

[Sqlalchemy-tickets] [sqlalchemy] #2716: table.tometadata(metadata) should copy column.info dictionary instead of referencing same dictionary

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

#2716: table.tometadata(metadata) should copy column.info dictionary instead of
referencing same dictionary
-----------------------+-----------------------------------------
 Reporter:  kentbower  |           Owner:  zzzeek
     Type:  defect     |          Status:  new
 Priority:  low        |       Milestone:
Component:  orm        |        Severity:  no triage selected yet
 Keywords:             |  Progress State:  awaiting triage
-----------------------+-----------------------------------------
 After executing `table.tometadata(metadata)`, the new table's new column's
 `info` dictionaries are references to the original columns' dictionaries
 instead of a copy of the dictionary.

 If this is by design for some reason, no hard feelings, close ticket, but
 seems bad design to me to have the new instances of the columns
 referencing the original columns' `info` dictionaries.

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

Re: [Sqlalchemy-tickets] [sqlalchemy] #2714: join on heavily with_polymorphic/base/subclass mismatch

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

#2714: join on heavily with_polymorphic/base/subclass mismatch
------------------------------+---------------------------------------
      Reporter:  zzzeek       |      Owner:  zzzeek
          Type:  defect       |     Status:  new
      Priority:  highest      |  Milestone:  0.8.xx
     Component:  orm          |   Severity:  very major - up to 2 days
    Resolution:               |   Keywords:
Progress State:  needs tests  |
------------------------------+---------------------------------------
Changes (by zzzeek):

 * status_field:  in queue => needs tests


Comment:

 the repo at https://bitbucket.org/zzzeek/sa_2714 has pretty much a fix-
 everything patch using an extremely simple approach.   all cases here pass
 without issue.

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

Re: [Sqlalchemy-tickets] [sqlalchemy] #2714: join on heavily with_polymorphic/base/subclass mismatch

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

#2714: join on heavily with_polymorphic/base/subclass mismatch
---------------------------+---------------------------------------
      Reporter:  zzzeek    |      Owner:  zzzeek
          Type:  defect    |     Status:  new
      Priority:  highest   |  Milestone:  0.8.xx
     Component:  orm       |   Severity:  very major - up to 2 days
    Resolution:            |   Keywords:
Progress State:  in queue  |
---------------------------+---------------------------------------

Comment (by zzzeek):

 workarounds for now can be accomplished by using AliasedClass for all join
 targets and explicit ON clauses:

 {{{
     up = aliased(UserPerson)
     pa = aliased(PersonAddress)

     session.query(User).\
         outerjoin(up, up.ItemUserPerson_Id == User.Id).\
         outerjoin(pa, pa.ItemPerson_Id == up.Id).\
         outerjoin(pa.Emails).\
         outerjoin(pa.Phones).\
         first()
 }}}

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

Re: [Sqlalchemy-tickets] [sqlalchemy] #2714: join on heavily with_polymorphic/base/subclass mismatch

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

#2714: join on heavily with_polymorphic/base/subclass mismatch
---------------------------+---------------------------------------
      Reporter:  zzzeek    |      Owner:  zzzeek
          Type:  defect    |     Status:  new
      Priority:  highest   |  Milestone:  0.8.xx
     Component:  orm       |   Severity:  very major - up to 2 days
    Resolution:            |   Keywords:
Progress State:  in queue  |
---------------------------+---------------------------------------

Comment (by zzzeek):

 second issue:

 {{{
     pa = aliased(PersonAddress)

     left = User
     right = AliasedClass(UserPerson)

     j1 = orm_join(left, right, User.Personal, join_to_left=True)

     right = pa


     j2 = orm_join(j1, right, UserPerson.Addresses, join_to_left=False)  #
 join_to_left is the problem
     print j2
 }}}

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

[Sqlalchemy-tickets] [sqlalchemy] #2715: gaerdbms: local database mode

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

#2715: gaerdbms: local database mode
-------------------------------------+-------------------------------------
 Reporter:  moraes                   |           Owner:  zzzeek
     Type:  enhancement              |          Status:  new
 Priority:  medium                   |       Milestone:
Component:  engine                   |        Severity:  no triage selected
 Keywords:  gaerdbms, mysql,         |  yet
  dialect                            |  Progress State:  awaiting triage
-------------------------------------+-------------------------------------
 I added support to gaerdbms to use a local database. A working dialect is
 here:

     http://stackoverflow.com/a/16198395/125967

 Basically, when running the development server, it uses a local `MySql`
 database through this snippet:

 {{{
     # Development mode with local database.
     from google.appengine.api import rdbms_mysqldb
     return rdbms_mysqldb
 }}}

 I suppose the os.environ way to configure won't be welcome, so maybe this
 should be an option passed to dialect constructor (or something, sorry I'm
 really new to `SqlAlchemy`).

 For backwards compatibility, the default setting for development could be
 remote access to the database, configurable to use a local database.
 However, a local database may be more desirable for development, so
 perhaps local mode should be the default.

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

Re: [Sqlalchemy-tickets] [sqlalchemy] #2714: join on heavily with_polymorphic/base/subclass mismatch

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

#2714: join on heavily with_polymorphic/base/subclass mismatch
---------------------------+---------------------------------------
      Reporter:  zzzeek    |      Owner:  zzzeek
          Type:  defect    |     Status:  new
      Priority:  highest   |  Milestone:  0.8.xx
     Component:  orm       |   Severity:  very major - up to 2 days
    Resolution:            |   Keywords:
Progress State:  in queue  |
---------------------------+---------------------------------------

Comment (by zzzeek):

 OK luckily this has nothing to do with with_polymorphic or anything like
 that, and actually comes down to just one spot, as the way query.join()
 works out here, it just goes for "try to stick the right side anywhere we
 can", so it's more of a "find a spot" problem.  but this is not the
 aliased issue yet.  The join can be broken down like this:

 {{{
     from sqlalchemy.orm.util import join as orm_join
     from sqlalchemy.orm.util import AliasedClass

     left = User.__table__
     right = AliasedClass(UserPerson)

     j1 = orm_join(left, right, User.Personal, join_to_left=True)

     right = AliasedClass(PersonAddress)
     j2 = orm_join(j1, right, UserPerson.Addresses, join_to_left=True)

     right = Email.__mapper__

     j3 = orm_join(j2, right, PersonAddress.Emails, join_to_left=True)

     # doesn't know how to find the right thing to alias the on clause
     # against
     right = Phone.__mapper__
     j4 = orm_join(j3, right, PersonAddress.Phones, join_to_left=True)

     print j4
 }}}

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

Re: [Sqlalchemy-tickets] [sqlalchemy] #2714: join on heavily with_polymorphic/base/subclass mismatch

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

#2714: join on heavily with_polymorphic/base/subclass mismatch
---------------------------+---------------------------------------
      Reporter:  zzzeek    |      Owner:  zzzeek
          Type:  defect    |     Status:  new
      Priority:  highest   |  Milestone:  0.8.xx
     Component:  orm       |   Severity:  very major - up to 2 days
    Resolution:            |   Keywords:
Progress State:  in queue  |
---------------------------+---------------------------------------
Description changed by zzzeek:

Old description:

> this case needs to be further reduced and also may contain more than one
> individual issue, see the additional failures regarding aliased.   Claims
> to be a regression since 0.7.
>
> {{{
>

> from sqlalchemy import *
> from sqlalchemy.ext.declarative import declarative_base
> from sqlalchemy.orm import relationship, Session, aliased
>
> Base = declarative_base()
>
> class Email(Base):
>     __tablename__ = 'Email'
>     Id = Column(Integer, primary_key=True)
>
>     # Many2One side of Address One2Many - backref Email.ItemEmail
>     ItemEmail_Id = Column(Integer,
>                           ForeignKey('Address.Id', use_alter=True,
> name="FK_Email_ItemEmail_Id"))
>
> class Phone(Base):
>     __tablename__ = 'Phone'
>     Id = Column(Integer, primary_key=True)
>
>     # Many2One side of Address One2Many - backref Phone.ItemPhone
>     ItemPhone_Id = Column(Integer,
>                           ForeignKey('Address.Id', use_alter=True,
> name="FK_Phone_ItemPhone_Id"))
>

> class Address(Base):
>     __tablename__ = 'Address'
>     Id = Column(Integer, primary_key=True)
>
>     AddressType = Column(String, nullable=False)
>
>     # One2Many side of Phone Many2One - backref Phone.ItemPhone
>     Phones = relationship("Phone", uselist=True, cascade="delete",
> backref="ItemPhone")
>
>     # One2Many side of Email Many2One - backref Email.ItemEmail
>     Emails = relationship("Email", uselist=True, cascade="delete",
> backref="ItemEmail")
>
>     __mapper_args__ = {'polymorphic_on': AddressType,
>                        'with_polymorphic': '*'
>                        }
>
> class PersonAddress(Address):
>     __tablename__ = 'PersonAddress'
>     Id = Column(Integer, ForeignKey('Address.Id'), primary_key=True)
>
>     __mapper_args__ = {'polymorphic_identity': "person",
>                         'inherit_condition': (Id == Address.Id)
>                        }
>
>     # ManyToOne side of Person OneTomany - backref
> PersonAddress.ItemPerson
>     ItemPerson_Id = Column(Integer,
>                            ForeignKey('Person.Id', use_alter=True,
> name="FK_PersonAddress_ItemPerson_Id"))
>

>
> class Person(Base):
>     __tablename__ = 'Person'
>     Id = Column(Integer, primary_key=True)
>
>     PersonType = Column(String, nullable=False)
>
>     # One2Many side of PersonAddress Many2One - backref
> PersonAddress.ItemPerson
>     Addresses = relationship("PersonAddress",
>                              uselist=True, cascade="delete",
>                              backref="ItemPerson")
>
>     __mapper_args__ = {'polymorphic_on': PersonType,
>                        'with_polymorphic': '*'
>                        }
>
> class UserPerson(Person):
>     __tablename__ = 'UserPerson'
>     Id = Column(Integer, ForeignKey('Person.Id'), primary_key=True)
>
>     __mapper_args__ = {'polymorphic_identity': "user",
>                         'inherit_condition': (Id == Person.Id)
>                        }
>     # One2One side of User One2One
>     ItemUserPerson_Id = Column(Integer,
>                                ForeignKey("User.Id", use_alter=True,
> name="FK_UserPerson_ItemUserPerson_Id"))
>     ItemUserPerson = relationship("User",
> primaryjoin="User.Id==UserPerson.ItemUserPerson_Id",
>                                   back_populates="Personal")
>

> class User(Base):
>     __tablename__ = 'User'
>     Id = Column(Integer, primary_key=True)
>
>     # One2One side of UserPerson One2One
>     Personal = relationship("UserPerson",
> primaryjoin="UserPerson.ItemUserPerson_Id==User.Id",
>                             uselist=False,
>                             cascade="delete",
>                             back_populates="ItemUserPerson")
>

> engine = create_engine('sqlite://', echo=True)
> Base.metadata.create_all(engine)
>
> session = Session(engine)
>
> # 1. users original q, but is in bad form
> #session.query(User, UserPerson, PersonAddress).filter(User.Id == 1).\
> #    outerjoin(User.Personal, UserPerson.Addresses, PersonAddress.Phones,
>     # PersonAddress.Emails)
>
> # break them up, it breaks - only one of the PersonAddress.* joins
> # can be accepted
> # have not reduced for backrefs, with_polymrophic yet
> #session.query(User, UserPerson, PersonAddress).filter(User.Id == 1).\
> #    outerjoin(User.Personal, UserPerson.Addresses).\
> #    outerjoin(PersonAddress.Emails).\
> #    outerjoin(PersonAddress.Phones).\
> #    first()
>
> # breaks
> pa = aliased(PersonAddress)
> session.query(User, UserPerson, pa).filter(User.Id == 1).\
>     outerjoin(User.Personal).\
>     outerjoin(pa, UserPerson.Addresses).\
>     outerjoin(pa.Emails).\
>     outerjoin(pa.Phones).\
>     first()
>
> # breaks? separate things wrong with aliased()? not sure
> pa = aliased(PersonAddress)
> session.query(User, UserPerson, pa).filter(User.Id == 1).\
>     outerjoin(User.Personal).\
>     outerjoin(pa, UserPerson.Addresses).\
>     first()
>
> # more breaks or not, haven't tested yet.
>
> # 2 - OK - but I need PersonAddress.Emails
> #session.query(User, UserPerson, PersonAddress).filter(User.Id == 1).\
> #
> outerjoin(User.Personal,UserPerson.Addresses,PersonAddress.Phones).first()
>
> # 3 - error - just clutching at straws
> #session.query(User, UserPerson, PersonAddress, Phone,
> Email).filter(User.Id == 1).\
> #
> outerjoin(User.Personal,UserPerson.Addresses,PersonAddress.Phones,PersonAddress.Emails).first()
>
> # 4 - OK - but why define Address
> #session.query(User, UserPerson, PersonAddress, Address).filter(User.Id
> == 1).\
> #
> outerjoin(User.Personal,UserPerson.Addresses,PersonAddress.Phones,PersonAddress.Emails).first()
>
> # 5 - OK - but PersonAddress might have some fields not in Adddress
> #session.query(User, UserPerson, Address).filter(User.Id == 1).\
> #
> outerjoin(User.Personal,UserPerson.Addresses,PersonAddress.Phones,PersonAddress.Emails).first()
> }}}

New description:

 this case needs to be further reduced and also may contain more than one
 individual issue, see the additional failures regarding aliased.  fails in
 0.7 also, so this is not as critical but still would like to research it.
 seems like the pattern "A join <joinedinh> join <joinedinh>" is somehow
 not working.

 {{{


 from sqlalchemy import *
 from sqlalchemy.ext.declarative import declarative_base
 from sqlalchemy.orm import relationship, Session, aliased

 Base = declarative_base()

 class Email(Base):
     __tablename__ = 'Email'
     Id = Column(Integer, primary_key=True)

     # Many2One side of Address One2Many - backref Email.ItemEmail
     ItemEmail_Id = Column(Integer,
                           ForeignKey('Address.Id', use_alter=True,
 name="FK_Email_ItemEmail_Id"))

 class Phone(Base):
     __tablename__ = 'Phone'
     Id = Column(Integer, primary_key=True)

     # Many2One side of Address One2Many - backref Phone.ItemPhone
     ItemPhone_Id = Column(Integer,
                           ForeignKey('Address.Id', use_alter=True,
 name="FK_Phone_ItemPhone_Id"))


 class Address(Base):
     __tablename__ = 'Address'
     Id = Column(Integer, primary_key=True)

     AddressType = Column(String, nullable=False)

     # One2Many side of Phone Many2One - backref Phone.ItemPhone
     Phones = relationship("Phone", uselist=True, cascade="delete",
 backref="ItemPhone")

     # One2Many side of Email Many2One - backref Email.ItemEmail
     Emails = relationship("Email", uselist=True, cascade="delete",
 backref="ItemEmail")

     __mapper_args__ = {'polymorphic_on': AddressType,
                        'with_polymorphic': '*'
                        }

 class PersonAddress(Address):
     __tablename__ = 'PersonAddress'
     Id = Column(Integer, ForeignKey('Address.Id'), primary_key=True)

     __mapper_args__ = {'polymorphic_identity': "person",
                         'inherit_condition': (Id == Address.Id)
                        }

     # ManyToOne side of Person OneTomany - backref
 PersonAddress.ItemPerson
     ItemPerson_Id = Column(Integer,
                            ForeignKey('Person.Id', use_alter=True,
 name="FK_PersonAddress_ItemPerson_Id"))



 class Person(Base):
     __tablename__ = 'Person'
     Id = Column(Integer, primary_key=True)

     PersonType = Column(String, nullable=False)

     # One2Many side of PersonAddress Many2One - backref
 PersonAddress.ItemPerson
     Addresses = relationship("PersonAddress",
                              uselist=True, cascade="delete",
                              backref="ItemPerson")

     __mapper_args__ = {'polymorphic_on': PersonType,
                        'with_polymorphic': '*'
                        }

 class UserPerson(Person):
     __tablename__ = 'UserPerson'
     Id = Column(Integer, ForeignKey('Person.Id'), primary_key=True)

     __mapper_args__ = {'polymorphic_identity': "user",
                         'inherit_condition': (Id == Person.Id)
                        }
     # One2One side of User One2One
     ItemUserPerson_Id = Column(Integer,
                                ForeignKey("User.Id", use_alter=True,
 name="FK_UserPerson_ItemUserPerson_Id"))
     ItemUserPerson = relationship("User",
 primaryjoin="User.Id==UserPerson.ItemUserPerson_Id",
                                   back_populates="Personal")


 class User(Base):
     __tablename__ = 'User'
     Id = Column(Integer, primary_key=True)

     # One2One side of UserPerson One2One
     Personal = relationship("UserPerson",
 primaryjoin="UserPerson.ItemUserPerson_Id==User.Id",
                             uselist=False,
                             cascade="delete",
                             back_populates="ItemUserPerson")


 engine = create_engine('sqlite://', echo=True)
 Base.metadata.create_all(engine)

 session = Session(engine)

 # 1. users original q, but is in bad form
 #session.query(User, UserPerson, PersonAddress).filter(User.Id == 1).\
 #    outerjoin(User.Personal, UserPerson.Addresses, PersonAddress.Phones,
     # PersonAddress.Emails)

 # break them up, it breaks - only one of the PersonAddress.* joins
 # can be accepted
 # have not reduced for backrefs, with_polymrophic yet
 #session.query(User, UserPerson, PersonAddress).filter(User.Id == 1).\
 #    outerjoin(User.Personal, UserPerson.Addresses).\
 #    outerjoin(PersonAddress.Emails).\
 #    outerjoin(PersonAddress.Phones).\
 #    first()

 # breaks
 pa = aliased(PersonAddress)
 session.query(User, UserPerson, pa).filter(User.Id == 1).\
     outerjoin(User.Personal).\
     outerjoin(pa, UserPerson.Addresses).\
     outerjoin(pa.Emails).\
     outerjoin(pa.Phones).\
     first()

 # breaks? separate things wrong with aliased()? not sure
 pa = aliased(PersonAddress)
 session.query(User, UserPerson, pa).filter(User.Id == 1).\
     outerjoin(User.Personal).\
     outerjoin(pa, UserPerson.Addresses).\
     first()

 # more breaks or not, haven't tested yet.

 # 2 - OK - but I need PersonAddress.Emails
 #session.query(User, UserPerson, PersonAddress).filter(User.Id == 1).\
 #
 outerjoin(User.Personal,UserPerson.Addresses,PersonAddress.Phones).first()

 # 3 - error - just clutching at straws
 #session.query(User, UserPerson, PersonAddress, Phone,
 Email).filter(User.Id == 1).\
 #
 outerjoin(User.Personal,UserPerson.Addresses,PersonAddress.Phones,PersonAddress.Emails).first()

 # 4 - OK - but why define Address
 #session.query(User, UserPerson, PersonAddress, Address).filter(User.Id ==
 1).\
 #
 outerjoin(User.Personal,UserPerson.Addresses,PersonAddress.Phones,PersonAddress.Emails).first()

 # 5 - OK - but PersonAddress might have some fields not in Adddress
 #session.query(User, UserPerson, Address).filter(User.Id == 1).\
 #
 outerjoin(User.Personal,UserPerson.Addresses,PersonAddress.Phones,PersonAddress.Emails).first()
 }}}

--

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

[Sqlalchemy-tickets] [sqlalchemy] #2714: join on heavily with_polymorphic/base/subclass mismatch

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

#2714: join on heavily with_polymorphic/base/subclass mismatch
---------------------+--------------------------------------------
 Reporter:  zzzeek   |           Owner:  zzzeek
     Type:  defect   |          Status:  new
 Priority:  highest  |       Milestone:  0.8.xx
Component:  orm      |        Severity:  very major - up to 2 days
 Keywords:           |  Progress State:  in queue
---------------------+--------------------------------------------
 this case needs to be further reduced and also may contain more than one
 individual issue, see the additional failures regarding aliased.   Claims
 to be a regression since 0.7.

 {{{


 from sqlalchemy import *
 from sqlalchemy.ext.declarative import declarative_base
 from sqlalchemy.orm import relationship, Session, aliased

 Base = declarative_base()

 class Email(Base):
     __tablename__ = 'Email'
     Id = Column(Integer, primary_key=True)

     # Many2One side of Address One2Many - backref Email.ItemEmail
     ItemEmail_Id = Column(Integer,
                           ForeignKey('Address.Id', use_alter=True,
 name="FK_Email_ItemEmail_Id"))

 class Phone(Base):
     __tablename__ = 'Phone'
     Id = Column(Integer, primary_key=True)

     # Many2One side of Address One2Many - backref Phone.ItemPhone
     ItemPhone_Id = Column(Integer,
                           ForeignKey('Address.Id', use_alter=True,
 name="FK_Phone_ItemPhone_Id"))


 class Address(Base):
     __tablename__ = 'Address'
     Id = Column(Integer, primary_key=True)

     AddressType = Column(String, nullable=False)

     # One2Many side of Phone Many2One - backref Phone.ItemPhone
     Phones = relationship("Phone", uselist=True, cascade="delete",
 backref="ItemPhone")

     # One2Many side of Email Many2One - backref Email.ItemEmail
     Emails = relationship("Email", uselist=True, cascade="delete",
 backref="ItemEmail")

     __mapper_args__ = {'polymorphic_on': AddressType,
                        'with_polymorphic': '*'
                        }

 class PersonAddress(Address):
     __tablename__ = 'PersonAddress'
     Id = Column(Integer, ForeignKey('Address.Id'), primary_key=True)

     __mapper_args__ = {'polymorphic_identity': "person",
                         'inherit_condition': (Id == Address.Id)
                        }

     # ManyToOne side of Person OneTomany - backref
 PersonAddress.ItemPerson
     ItemPerson_Id = Column(Integer,
                            ForeignKey('Person.Id', use_alter=True,
 name="FK_PersonAddress_ItemPerson_Id"))



 class Person(Base):
     __tablename__ = 'Person'
     Id = Column(Integer, primary_key=True)

     PersonType = Column(String, nullable=False)

     # One2Many side of PersonAddress Many2One - backref
 PersonAddress.ItemPerson
     Addresses = relationship("PersonAddress",
                              uselist=True, cascade="delete",
                              backref="ItemPerson")

     __mapper_args__ = {'polymorphic_on': PersonType,
                        'with_polymorphic': '*'
                        }

 class UserPerson(Person):
     __tablename__ = 'UserPerson'
     Id = Column(Integer, ForeignKey('Person.Id'), primary_key=True)

     __mapper_args__ = {'polymorphic_identity': "user",
                         'inherit_condition': (Id == Person.Id)
                        }
     # One2One side of User One2One
     ItemUserPerson_Id = Column(Integer,
                                ForeignKey("User.Id", use_alter=True,
 name="FK_UserPerson_ItemUserPerson_Id"))
     ItemUserPerson = relationship("User",
 primaryjoin="User.Id==UserPerson.ItemUserPerson_Id",
                                   back_populates="Personal")


 class User(Base):
     __tablename__ = 'User'
     Id = Column(Integer, primary_key=True)

     # One2One side of UserPerson One2One
     Personal = relationship("UserPerson",
 primaryjoin="UserPerson.ItemUserPerson_Id==User.Id",
                             uselist=False,
                             cascade="delete",
                             back_populates="ItemUserPerson")


 engine = create_engine('sqlite://', echo=True)
 Base.metadata.create_all(engine)

 session = Session(engine)

 # 1. users original q, but is in bad form
 #session.query(User, UserPerson, PersonAddress).filter(User.Id == 1).\
 #    outerjoin(User.Personal, UserPerson.Addresses, PersonAddress.Phones,
     # PersonAddress.Emails)

 # break them up, it breaks - only one of the PersonAddress.* joins
 # can be accepted
 # have not reduced for backrefs, with_polymrophic yet
 #session.query(User, UserPerson, PersonAddress).filter(User.Id == 1).\
 #    outerjoin(User.Personal, UserPerson.Addresses).\
 #    outerjoin(PersonAddress.Emails).\
 #    outerjoin(PersonAddress.Phones).\
 #    first()

 # breaks
 pa = aliased(PersonAddress)
 session.query(User, UserPerson, pa).filter(User.Id == 1).\
     outerjoin(User.Personal).\
     outerjoin(pa, UserPerson.Addresses).\
     outerjoin(pa.Emails).\
     outerjoin(pa.Phones).\
     first()

 # breaks? separate things wrong with aliased()? not sure
 pa = aliased(PersonAddress)
 session.query(User, UserPerson, pa).filter(User.Id == 1).\
     outerjoin(User.Personal).\
     outerjoin(pa, UserPerson.Addresses).\
     first()

 # more breaks or not, haven't tested yet.

 # 2 - OK - but I need PersonAddress.Emails
 #session.query(User, UserPerson, PersonAddress).filter(User.Id == 1).\
 #
 outerjoin(User.Personal,UserPerson.Addresses,PersonAddress.Phones).first()

 # 3 - error - just clutching at straws
 #session.query(User, UserPerson, PersonAddress, Phone,
 Email).filter(User.Id == 1).\
 #
 outerjoin(User.Personal,UserPerson.Addresses,PersonAddress.Phones,PersonAddress.Emails).first()

 # 4 - OK - but why define Address
 #session.query(User, UserPerson, PersonAddress, Address).filter(User.Id ==
 1).\
 #
 outerjoin(User.Personal,UserPerson.Addresses,PersonAddress.Phones,PersonAddress.Emails).first()

 # 5 - OK - but PersonAddress might have some fields not in Adddress
 #session.query(User, UserPerson, Address).filter(User.Id == 1).\
 #
 outerjoin(User.Personal,UserPerson.Addresses,PersonAddress.Phones,PersonAddress.Emails).first()
 }}}

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

[Sqlalchemy-tickets] [sqlalchemy] #2713: coerce postgresql_where from string?

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

#2713: coerce postgresql_where from string?
----------------------+---------------------------------------
 Reporter:  zzzeek    |           Owner:  zzzeek
     Type:  defect    |          Status:  new
 Priority:  medium    |       Milestone:  0.8.xx
Component:  postgres  |        Severity:  minor - half an hour
 Keywords:            |  Progress State:  in queue
----------------------+---------------------------------------
 this raises a stack trace in alembic:

 {{{
     op.create_index(
         'geocoded',
         'locations',
         ['coordinates'],
         postgresql_where="locations.coordinates != Null")
 }}}

 seems like we should coerce the string there to text().

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

Re: [Sqlalchemy-tickets] [sqlalchemy] #2673: add exists() convenience method to query

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

#2673: add exists() convenience method to query
-----------------------------------+----------------------------------
      Reporter:  zzzeek            |      Owner:  zzzeek
          Type:  enhancement       |     Status:  closed
      Priority:  medium            |  Milestone:  0.8.xx
     Component:  orm               |   Severity:  minor - half an hour
    Resolution:  fixed             |   Keywords:
Progress State:  completed/closed  |
-----------------------------------+----------------------------------
Changes (by zzzeek):

 * status:  new => closed
 * resolution:   => fixed
 * status_field:  needs review => completed/closed


Comment:

 r59e5d09ebf3c thanks!

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

Re: [Sqlalchemy-tickets] [sqlalchemy] #2712: Catch psycopg disconnect error

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

#2712: Catch psycopg disconnect error
-----------------------------------+----------------------------------
      Reporter:  elic              |      Owner:  zzzeek
          Type:  defect            |     Status:  closed
      Priority:  medium            |  Milestone:  0.8.xx
     Component:  postgres          |   Severity:  minor - half an hour
    Resolution:  fixed             |   Keywords:
Progress State:  completed/closed  |
-----------------------------------+----------------------------------

Comment (by elic):

 No problem! Glad it helps.

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

Re: [Sqlalchemy-tickets] [sqlalchemy] #2712: Catch psycopg disconnect error

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

#2712: Catch psycopg disconnect error
-----------------------------------+----------------------------------
      Reporter:  elic              |      Owner:  zzzeek
          Type:  defect            |     Status:  closed
      Priority:  medium            |  Milestone:  0.8.xx
     Component:  postgres          |   Severity:  minor - half an hour
    Resolution:  fixed             |   Keywords:
Progress State:  completed/closed  |
-----------------------------------+----------------------------------

Comment (by zzzeek):

 I had thought of that also but wasn't going to bother :).   But sure, this
 should help, in r6d75ba41fd57.   Forgive my internet stalking to get your
 real name for the changelog.

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

Re: [Sqlalchemy-tickets] [sqlalchemy] #2712: Catch psycopg disconnect error

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

#2712: Catch psycopg disconnect error
-----------------------------------+----------------------------------
      Reporter:  elic              |      Owner:  zzzeek
          Type:  defect            |     Status:  closed
      Priority:  medium            |  Milestone:  0.8.xx
     Component:  postgres          |   Severity:  minor - half an hour
    Resolution:  fixed             |   Keywords:
Progress State:  completed/closed  |
-----------------------------------+----------------------------------

Comment (by elic):

 Works fine for me and my test case, and definitely much more resilient to
 changes in psycopg's exception type mapping. There's just one rare
 (perhaps pathological) border case I noticed yesterday after submitting my
 original patch:

 Checks for {{{xxx in str(e)}}} could result in a false positive if the
 substring appears within the error message as part of a column name or
 other statement-derived text.  For example, the error messages thrown by
 {{{SELECT 'cursor closed'::text+1}}}, {{{SELECT "cursor closed"}}}, and
 {{{EXECUTE "cursor closed"}}} will now get treated as a disconnection.
 (Actually, I think some of these cases could slip through before as well).

 Attached is a patch to r4694131c564d (named
 `exclude_programming_errors.patch`) which attempts to mitigate that border
 case...

 First, it strips all but the first line from {{{str(e)}}}.  After
 examining the libpq source, I'm 99% certain all the substrings it's
 looking for occur on the first line.  This should take care of errors
 where the sql content is embedded in subsequent lines, such as for
 {{{SELECT 'cursor closed'::text+1}}}, which results in {{{operator does
 not exist: text + integer\nLINE 1: select 'cursor closed'::text+1;\n}}}.

 Second, when it finds a match to one of the substrings, it checks if
 there's a double-quote anywhere before the match location, to exclude
 cases where the substring occurs as part of some quoted statement text.
 This should take care of messages which include a quoted identifier on the
 first line, such as for {{{SELECT "cursor closed"}}}, which results in
 {{{column "cursor closed" does not exist\n}}}.

 Sidenote: I initially tried coding a fix by excluding certain types
 (`DataError`, `IntegrityError`, and `ProgrammingError`) from the test, but
 after looking closer at psycopg's `exception_from_sqlstate()`, I realized
 that wasn't enough: for example, {{{EXECUTE "cursor closed"}}} results a
 message similar to the 2nd case, but it's an `OperationalError`, not a
 `ProgrammingError`.

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

Re: [Sqlalchemy-tickets] [sqlalchemy] #2712: Catch psycopg disconnect error

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

#2712: Catch psycopg disconnect error
-----------------------------------+----------------------------------
      Reporter:  elic              |      Owner:  zzzeek
          Type:  defect            |     Status:  closed
      Priority:  medium            |  Milestone:  0.8.xx
     Component:  postgres          |   Severity:  minor - half an hour
    Resolution:  fixed             |   Keywords:
Progress State:  completed/closed  |
-----------------------------------+----------------------------------
Changes (by zzzeek):

 * status:  new => closed
 * resolution:   => fixed
 * severity:  no triage selected yet => minor - half an hour
 * status_field:  awaiting triage => completed/closed


Comment:

 OK, that message you can see we're already looking for almost the same
 thing twice above it, so I propose we just open it up to look for all of
 these messages straight down for all exceptions.  Otherwise, each time
 psycopg2/libpq makes some subtle change, the same message moves from
 `ProgrammingError` to `InterfaceError` etc.  I've used postgresql for many
 years and these messages pretty much mean the connection died.

 Let me know if what I did in r4694131c564d works for you, the tests we
 have for "disconnect" pass with this and the test you attached should work
 also.

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

Re: [Sqlalchemy-tickets] [sqlalchemy] #2682: is_ and isnot in conjunction with boolean values / MySQL

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

#2682: is_ and isnot in conjunction with boolean values / MySQL
-----------------------------------+-------------------------------
      Reporter:  lievensd          |      Owner:
          Type:  defect            |     Status:  closed
      Priority:  medium            |  Milestone:  0.8.xx
     Component:  mysql             |   Severity:  major - 1-3 hours
    Resolution:  fixed             |   Keywords:  sql, operators
Progress State:  completed/closed  |
-----------------------------------+-------------------------------
Changes (by zzzeek):

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


Comment:

 r25bb1aaf375a

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

[Sqlalchemy-tickets] [sqlalchemy] #2712: Catch psycopg disconnect error

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

#2712: Catch psycopg disconnect error
----------------------+-----------------------------------------
 Reporter:  elic      |           Owner:  zzzeek
     Type:  defect    |          Status:  new
 Priority:  medium    |       Milestone:  0.8.xx
Component:  postgres  |        Severity:  no triage selected yet
 Keywords:            |  Progress State:  awaiting triage
----------------------+-----------------------------------------
 I've found a rare libpq disconnect error which isn't being caught by the
 psycopg2 dialect:

 When a TCP Reset happens on an existing connection, psycopg raises
 `DatabaseError("server closed the connection unexpectedly")`.
 `PGDialect_psycopg2.is_disconnect()` doesn't have a check for this
 message, which causes `Connection._handle_dbapi_error()` to throw a
 spurious `InterfaceError`, as well as causes it to fail to `.invalidate()`
 the connection (which subsequently causes things like Session.rollback()
 to fail as well).

 Attached is a script which reproduces this behaviour (it requires linux
 iptables & sudo in order to force the tcp reset).

 Also attached is a patch adds support for this error to
 `PGDialect_psycopg2.is_disconnect()`

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

Re: [Sqlalchemy-tickets] [sqlalchemy] #2681: Postgresql array.contains doesn't accept set objects

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

#2681: Postgresql array.contains doesn't accept set objects
-----------------------------------+----------------------------------
      Reporter:  taha_jahangir     |      Owner:  zzzeek
          Type:  defect            |     Status:  closed
      Priority:  medium            |  Milestone:  0.8.xx
     Component:  postgres          |   Severity:  minor - half an hour
    Resolution:  fixed             |   Keywords:
Progress State:  completed/closed  |
-----------------------------------+----------------------------------

Comment (by zzzeek):

 I just broke empty arrays on that, fixed in rfb30a67562ee.

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

Re: [Sqlalchemy-tickets] [sqlalchemy] #2681: Postgresql array.contains doesn't accept set objects

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

#2681: Postgresql array.contains doesn't accept set objects
-----------------------------------+----------------------------------
      Reporter:  taha_jahangir     |      Owner:  zzzeek
          Type:  defect            |     Status:  closed
      Priority:  medium            |  Milestone:  0.8.xx
     Component:  postgres          |   Severity:  minor - half an hour
    Resolution:  fixed             |   Keywords:
Progress State:  completed/closed  |
-----------------------------------+----------------------------------

Comment (by zzzeek):

 scratch that, I just coerce to a list() in all cases, so now it just works
 (at the expense of even more performance if you don't specify a dimension,
 so specify a dimension!)

 r7675cafc5afc

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

Re: [Sqlalchemy-tickets] [sqlalchemy] #2681: Postgresql array.contains doesn't accept set objects

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

#2681: Postgresql array.contains doesn't accept set objects
-----------------------------------+----------------------------------
      Reporter:  taha_jahangir     |      Owner:  zzzeek
          Type:  defect            |     Status:  closed
      Priority:  medium            |  Milestone:  0.8.xx
     Component:  postgres          |   Severity:  minor - half an hour
    Resolution:  fixed             |   Keywords:
Progress State:  completed/closed  |
-----------------------------------+----------------------------------
Changes (by zzzeek):

 * status:  new => closed
 * type:  enhancement => defect
 * resolution:   => fixed
 * status_field:  in queue => completed/closed


Comment:

 OK, sets/generators/whatever are supported, but you must specify a
 dimension for the ARRAY:

 {{{
 Table('mytable', metadata, Column('x', ARRAY(Integer, dimensions=1))
 }}}

 otherwise we have to guess how many dimensions are being sent, which
 requires looking into `arr[0]` which only works for list/tuple.  You want
 to have a "dimension" specified always.  The error message is now
 informative:

 {{{
         "Cannot auto-coerce ARRAY value of type "
         "%s unless dimensions are specified "
         "for ARRAY type" % type(arr))
 }}}


 rfefed5aeaf25

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