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

[Sqlalchemy-tickets] [sqlalchemy] #2822: mysql can't rollback all in one transaction

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

#2822: mysql can't rollback all in one transaction
-------------------------------------+-------------------------------------
 Reporter:  glongwave                |           Owner:
     Type:  defect                   |          Status:  new
 Priority:  medium                   |       Milestone:  0.7.xx
Component:  mysql                    |        Severity:  no triage selected
 Keywords:  mysql session            |  yet
  transcaction                       |  Progress State:  awaiting triage
-------------------------------------+-------------------------------------
 I used one session to insert two records( with same primary key) as
 follow:

 {{{

     session1 = Session()
     user1 = User(1, 'user1')
     user2 = User(1, 'user2')
     try:
         with session1.begin(subtransactions=True):
             session1.add(user1)
             session1.add(user2)
     except IntegrityError:
          print('get IntegrityError1')
 }}}


 1) I expect no user can be inserted due to IntegrityError, but I got the
 usr1 was inserted .
   It seemed that session didn't rollback all the operations.
 2) This only occured with mysql. I can't insert user at all with sqlite
 with same code.

 3) The sqlalchemy version is 0.7.9. Do I miss something or missuse session

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

Re: [Sqlalchemy-tickets] [sqlalchemy] #2819: VMware vFabric Postgres - Could not determine version from string

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

#2819: VMware vFabric Postgres - Could not determine version from string
-----------------------------+---------------------------------------
      Reporter:  sjschaefer  |      Owner:  zzzeek
          Type:  defect      |     Status:  new
      Priority:  high        |  Milestone:  0.8.xx
     Component:  postgres    |   Severity:  trivial - <10 minutes
    Resolution:              |   Keywords:  postgres,vpostgres,vmware
Progress State:  in queue    |
-----------------------------+---------------------------------------

Comment (by sjschaefer):

 Pull request sent on GitHub.

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

Re: [Sqlalchemy-tickets] [sqlalchemy] #2821: Engine.__repr__ should censor out password

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

#2821: Engine.__repr__ should censor out password
------------------------------+----------------------------------
      Reporter:  gthb         |      Owner:  zzzeek
          Type:  enhancement  |     Status:  new
      Priority:  high         |  Milestone:  0.8.xx
     Component:  engine       |   Severity:  minor - half an hour
    Resolution:               |   Keywords:
Progress State:  in progress  |
------------------------------+----------------------------------
Changes (by zzzeek):

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


Comment:

 ok, take a look at what alembic does here:
 https://bitbucket.org/zzzeek/alembic/src/3b86868a6f8ebc77f9cba031ca524795556f9e67/alembic/command.py?at=master#cl-221
 so we do something like that in URL.__repr__.  send me a pullreq on
 github/bitbu... a pull request is coming in!   look at that.  Will try to
 merge it later tonight...thanks!

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

Re: [Sqlalchemy-tickets] [sqlalchemy] #2820: session.merge/nullable primary keys - FlushError: Can't update table using NULL for primary key value

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

#2820: session.merge/nullable primary keys - FlushError: Can't update table using
NULL for primary key value
-------------------------------------------+-------------------------------
      Reporter:  elsdoerfer                |      Owner:  zzzeek
          Type:  defect                    |     Status:  new
      Priority:  medium                    |  Milestone:  0.8.xx
     Component:  orm                       |   Severity:  major - 1-3 hours
    Resolution:                            |   Keywords:
Progress State:  needs questions answered  |
-------------------------------------------+-------------------------------
Changes (by zzzeek):

 * status_field:  awaiting triage => needs questions answered
 * severity:  no triage selected yet => major - 1-3 hours
 * milestone:   => 0.8.xx


Comment:

 the intent of your code isn't clear.  the issue is not the merge, it's the
 implicit removal of `Related(3, None)` from the collection which is not
 supported.  Here's a simpler reproduction:

 {{{
 #!python
 from sqlalchemy import *
 from sqlalchemy.ext.declarative import declarative_base
 from sqlalchemy.orm import sessionmaker, relationship

 engine = create_engine('sqlite:///:memory:', echo=True)
 Session = sessionmaker(bind=engine)
 session = Session()

 Base = declarative_base()

 class Related(Base):
     __tablename__ = 'related'
     parent = Column(Integer, ForeignKey("parent.id"))
     key1 = Column(Integer, primary_key=True, nullable=True)
     key2 = Column(Integer, primary_key=True, nullable=True)

     def __init__(self, key1, key2):
         self.key1, self.key2 = key1, key2

 class Parent(Base):
     __tablename__ = 'parent'
     id = Column(Integer, primary_key=True)
     related = relationship(Related, collection_class=set)

 Base.metadata.create_all(engine)

 # First object
 parent = Parent()
 r1 = Related(3, None)
 parent.related.add(r1)
 session.add(parent)
 session.commit()

 parent.related.remove(r1)
 session.commit()

 }}}

 you may ask, "why is it trying to remove in my example?" because you are
 merging Parent->related((Related(3, None)) which no longer includes
 Related(2, None).   Even though SQLite is allowing this primary key to
 proceed, it is in fact illegal in SQL to have a NULL value in a table-
 bound primary key column (most if not all other backends will reject it in
 some way - PG places an implicit NOT NULL on any PK col, MySQL seems to be
 inserting a zero despite it being missing).  SQLAlchemy allows limited
 support of a NULL for the case where a class is mapped to an OUTER JOIN or
 other composed construct where the columns noted as primary key may
 contain nulls, but they would not ever be the target of an UPDATE.

 So the real issue here is why exactly you need to have a table that stores
 a NULL within a first-class primary key column.   I'm not really sure
 SQLAlchemy should seek to support this pattern.

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

Re: [Sqlalchemy-tickets] [sqlalchemy] #2819: VMware vFabric Postgres - Could not determine version from string

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

#2819: VMware vFabric Postgres - Could not determine version from string
-----------------------------+---------------------------------------
      Reporter:  sjschaefer  |      Owner:  zzzeek
          Type:  defect      |     Status:  new
      Priority:  high        |  Milestone:  0.8.xx
     Component:  postgres    |   Severity:  trivial - <10 minutes
    Resolution:              |   Keywords:  postgres,vpostgres,vmware
Progress State:  in queue    |
-----------------------------+---------------------------------------
Changes (by zzzeek):

 * priority:  medium => high
 * status_field:  awaiting triage => in queue


Comment:

 feel free to state the patch you used here or send a pullreq.

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

[Sqlalchemy-tickets] [sqlalchemy] #2821: Engine.__repr__ should censor out password

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

#2821: Engine.__repr__ should censor out password
-------------------------+-----------------------------------------
 Reporter:  gthb         |           Owner:  zzzeek
     Type:  enhancement  |          Status:  new
 Priority:  medium       |       Milestone:
Component:  engine       |        Severity:  no triage selected yet
 Keywords:               |  Progress State:  awaiting triage
-------------------------+-----------------------------------------
 To make it less likely that a database password leaks out into logs, error
 notifications and the like, `Engine.__repr__` should censor it out.

 Could just add a `__repr__` method to `URL` that replaces the password
 with `'***'`. Then format the URL with `%r` in `Engine.__repr__`.

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

[Sqlalchemy-tickets] [sqlalchemy] #2820: session.merge/nullable primary keys - FlushError: Can't update table using NULL for primary key value

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

#2820: session.merge/nullable primary keys - FlushError: Can't update table using
NULL for primary key value
------------------------+-----------------------------------------
 Reporter:  elsdoerfer  |           Owner:  zzzeek
     Type:  defect      |          Status:  new
 Priority:  medium      |       Milestone:
Component:  orm         |        Severity:  no triage selected yet
 Keywords:              |  Progress State:  awaiting triage
------------------------+-----------------------------------------
 SQLAlchemy==0.8.2

 I believe this is a bug:

 {{{
 Traceback (most recent call last):
   File "test.py", line 42, in <module>
     session.commit()
   File "/Users/michael/.virtualenvs/cms/lib/python2.7/site-
 packages/sqlalchemy/orm/session.py", line 721, in commit
     self.transaction.commit()
   File "/Users/michael/.virtualenvs/cms/lib/python2.7/site-
 packages/sqlalchemy/orm/session.py", line 354, in commit
     self._prepare_impl()
   File "/Users/michael/.virtualenvs/cms/lib/python2.7/site-
 packages/sqlalchemy/orm/session.py", line 334, in _prepare_impl
     self.session.flush()
   File "/Users/michael/.virtualenvs/cms/lib/python2.7/site-
 packages/sqlalchemy/orm/session.py", line 1818, in flush
     self._flush(objects)
   File "/Users/michael/.virtualenvs/cms/lib/python2.7/site-
 packages/sqlalchemy/orm/session.py", line 1936, in _flush
     transaction.rollback(_capture_exception=True)
   File "/Users/michael/.virtualenvs/cms/lib/python2.7/site-
 packages/sqlalchemy/util/langhelpers.py", line 58, in __exit__
     compat.reraise(exc_type, exc_value, exc_tb)
   File "/Users/michael/.virtualenvs/cms/lib/python2.7/site-
 packages/sqlalchemy/orm/session.py", line 1900, in _flush
     flush_context.execute()
   File "/Users/michael/.virtualenvs/cms/lib/python2.7/site-
 packages/sqlalchemy/orm/unitofwork.py", line 372, in execute
     rec.execute(self)
   File "/Users/michael/.virtualenvs/cms/lib/python2.7/site-
 packages/sqlalchemy/orm/unitofwork.py", line 525, in execute
     uow
   File "/Users/michael/.virtualenvs/cms/lib/python2.7/site-
 packages/sqlalchemy/orm/persistence.py", line 54, in save_obj
     table, states_to_update)
   File "/Users/michael/.virtualenvs/cms/lib/python2.7/site-
 packages/sqlalchemy/orm/persistence.py", line 383, in
 _collect_update_commands
     "Can't update table "
 sqlalchemy.orm.exc.FlushError: Can't update table using NULL for primary
 key value
 }}}

 This is the code that triggers this:

 {{{
 from sqlalchemy import *
 from sqlalchemy.ext.declarative import declarative_base
 from sqlalchemy.orm import sessionmaker, relationship

 engine = create_engine('sqlite:///:memory:', echo=True)
 Session = sessionmaker(bind=engine)
 session = Session()

 Base = declarative_base()

 class Related(Base):
     __tablename__ = 'related'
     parent = Column(Integer, ForeignKey("parent.id"))
     key1 = Column(Integer, primary_key=True, nullable=True)
     key2 = Column(Integer, primary_key=True, nullable=True)

     def __init__(self, key1, key2):
         self.key1, self.key2 = key1, key2

 class Parent(Base):
     __tablename__ = 'parent'
     id = Column(Integer, primary_key=True)
     related = relationship(Related, collection_class=set)

 Base.metadata.create_all(engine)

 # First object
 parent = Parent()
 parent.id = 1
 parent.related.add(Related(3, None))
 session.add(parent)
 session.commit()

 # Update first object
 parent = Parent()
 parent.id = 1
 parent.related.add(Related(2, None))
 session.merge(parent)
 session.commit()
 }}}

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

[Sqlalchemy-tickets] [sqlalchemy] #2819: VMware vFabric Postgres - Could not determine version from string

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

#2819: VMware vFabric Postgres - Could not determine version from string
-------------------------------------+-------------------------------------
 Reporter:  sjschaefer               |           Owner:  zzzeek
     Type:  defect                   |          Status:  new
 Priority:  medium                   |       Milestone:  0.8.xx
Component:  postgres                 |        Severity:  trivial - <10
 Keywords:                           |  minutes
  postgres,vpostgres,vmware          |  Progress State:  awaiting triage
-------------------------------------+-------------------------------------
 SQL Alchemy Version: 0.8.2
 Python Version: 3.3
 Postgres Version: 9.2.4.0

 We are using VMware vFabric Postgres with SQLAlchemy. Unfortunately the
 version string returned by {{{select version()}}} isn't accounted for
 properly.

 Here is the string: {{{[PostgreSQL 9.2.4 ] VMware vFabric Postgres 9.2.4.0
 release build 1080137}}}

 The version check is located at:
 ''sqlalchemy/dialects/postgresql/base.py'' line 1547.

 The check uses re.match, which looks for the match only at the beginning
 of the string. The brackets in the return string (shown above) cause the
 match to fail.

 I changed re.match to re.search and this fixed the problem in my tests.

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

Re: [Sqlalchemy-tickets] [sqlalchemy] #2818: Query.exists() don't works with queries without filters

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

#2818: Query.exists() don't works with queries without filters
-----------------------------------+----------------------------------
      Reporter:  vmagamedov        |      Owner:  zzzeek
          Type:  defect            |     Status:  closed
      Priority:  high              |  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:  in progress => completed/closed


Comment:

 re8167548429b9d4937caaa09 master

 r5d1bd984e603f3c1910967c2b 0.8

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

Re: [Sqlalchemy-tickets] [sqlalchemy] #2818: Query.exists() don't works with queries without filters

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

#2818: Query.exists() don't works with queries without filters
------------------------------+----------------------------------
      Reporter:  vmagamedov   |      Owner:  zzzeek
          Type:  defect       |     Status:  new
      Priority:  high         |  Milestone:  0.8.xx
     Component:  orm          |   Severity:  minor - half an hour
    Resolution:               |   Keywords:
Progress State:  in progress  |
------------------------------+----------------------------------
Changes (by zzzeek):

 * priority:  medium => high
 * severity:  no triage selected yet => minor - half an hour
 * status_field:  awaiting triage => in progress


Comment:

 ah that's a very nice approach, and test, great.  sure, send a pullreq via
 github or bitbucket and I'll fast-track it (i have a some pullreqs backed
 up at the moment).

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

[Sqlalchemy-tickets] [sqlalchemy] #2818: Query.exists() don't works with queries without filters

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

#2818: Query.exists() don't works with queries without filters
------------------------+-----------------------------------------
 Reporter:  vmagamedov  |           Owner:  zzzeek
     Type:  defect      |          Status:  new
 Priority:  medium      |       Milestone:  0.8.xx
Component:  orm         |        Severity:  no triage selected yet
 Keywords:              |  Progress State:  awaiting triage
------------------------+-----------------------------------------
 Action:
 {{{
 #!python
 db.session.query(User).exists()
 }}}
 Expectation:
 {{{
 #!sql
 SELECT 1 FROM users
 }}}
 Current result:
 {{{
 #!sql
 SELECT 1
 }}}
 Solution:
 {{{
 #!diff
 diff --git a/lib/sqlalchemy/orm/query.py b/lib/sqlalchemy/orm/query.py
 index b71bfe0..d64575a 100644
 --- a/lib/sqlalchemy/orm/query.py
 +++ b/lib/sqlalchemy/orm/query.py
 @@ -2494,7 +2494,7 @@ class Query(object):
          .. versionadded:: 0.8.1

          """
 -        return sql.exists(self.with_entities('1').statement)
 +        return sql.exists(self.statement.with_only_columns(['1']))

      def count(self):
          """Return a count of rows this Query would return.
 diff --git a/test/orm/test_query.py b/test/orm/test_query.py
 index 0973dc3..bc56668 100644
 --- a/test/orm/test_query.py
 +++ b/test/orm/test_query.py
 @@ -1730,9 +1730,16 @@ class ExistsTest(QueryTest, AssertsCompiledSQL):
      def test_exists(self):
          User = self.classes.User
          sess = create_session()
 -        q1 = sess.query(User).filter(User.name == 'fred')
 +        q1 = sess.query(User)
          self.assert_compile(sess.query(q1.exists()),
              'SELECT EXISTS ('
 +                'SELECT 1 FROM users'
 +            ') AS anon_1',
 +            dialect=default.DefaultDialect()
 +        )
 +        q2 = sess.query(User).filter(User.name == 'fred')
 +        self.assert_compile(sess.query(q2.exists()),
 +            'SELECT EXISTS ('
                  'SELECT 1 FROM users WHERE users.name = :name_1'
              ') AS anon_1',
              dialect=default.DefaultDialect()
 }}}
 P.S. Is it normal to send pull requests on !GitHub? Or you prefer
 Trac/patches?

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

[Sqlalchemy-tickets] [sqlalchemy] #2817: apply the autoquoting logic of mysql.ENUM to mysql.SET

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

#2817: apply the autoquoting logic of mysql.ENUM to mysql.SET
--------------------+---------------------------------------
 Reporter:  zzzeek  |           Owner:
     Type:  defect  |          Status:  new
 Priority:  high    |       Milestone:  0.8.xx
Component:  mysql   |        Severity:  minor - half an hour
 Keywords:          |  Progress State:  in queue
--------------------+---------------------------------------
 this was an oversight.  SET has the same outdated quoting system, apply
 the one for ENUM directly to it.  see also
 https://bitbucket.org/zzzeek/alembic/issue/138/mysql-set

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

Re: [Sqlalchemy-tickets] [sqlalchemy] #2809: Strange effect with association proxy and event listener

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

#2809: Strange effect with association proxy and event listener
-----------------------------------+------------------------------------
      Reporter:  schlamar          |      Owner:  zzzeek
          Type:  defect            |     Status:  closed
      Priority:  medium            |  Milestone:  0.8.xx
     Component:  (none)            |   Severity:  no triage selected yet
    Resolution:  invalid           |   Keywords:
Progress State:  completed/closed  |
-----------------------------------+------------------------------------

Comment (by zzzeek):

 Replying to [comment:7 schlamar]:

 > I know that this is a constructed example, but any query to the database
 in the event handler will cause the auto flush and therefore cause this
 exception (which makes it not such a constructed case). And I know that I
 can workaround this issue by wrapping the event handler code into an with
 session.no_autoflush.

 OK well if you use most any other ORM, there either *is no flush*, you
 have to save() anytime you want INSERT or UPDATE yourself, or there are no
 attribute events. Use any of those ORMs and this problem doesn't exist -
 but neither does the feature. There is no way this particular example
 could function, because attribute events fire off before the actual change
 occurs. Only if you manually set the foreign key value to that of the
 user.id. do that if you'd like.

 > But I see this just as a workaround and not as the solution. I do not
 agree that this should be up to the user. IMO sqlalchemy should detect
 that it is in an inconsistent state where a flush would (or could) fail
 and automatically disable it until you are back in a consistent state

 SQLAlchemy would be making a significant guess here that when it goes to
 autoflush on behalf of your `job.pallets` collection that's local to your
 event handler, that it needs to be concerned about the state of what is at
 this point an entirely unrelated collection of `job.programs` that's
 external to the event handler.    If a user expects autoflush to occur, it
 would be pretty surprising and inconsistent if it turned itself off in
 certain hard-to-spot situations.   It's a lot more simple to understand
 that autoflush occurred, an integrity error happened, so therefore this
 particular event handler needs no_autoflush on it.   IMHO having a
 consistent and predictable autoflush is a lot more straightforward than an
 autoflush that makes guesses in obscure situations as to when it might not
 want to proceed, as it wouldn't be consistently effective.

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

Re: [Sqlalchemy-tickets] [sqlalchemy] #2809: Strange effect with association proxy and event listener

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

#2809: Strange effect with association proxy and event listener
-----------------------------------+------------------------------------
      Reporter:  schlamar          |      Owner:  zzzeek
          Type:  defect            |     Status:  closed
      Priority:  medium            |  Milestone:  0.8.xx
     Component:  (none)            |   Severity:  no triage selected yet
    Resolution:  invalid           |   Keywords:
Progress State:  completed/closed  |
-----------------------------------+------------------------------------

Comment (by zzzeek):

 a quick poll on autoflush:

 Hibernate (main inspiration for SQLAlchemy) - autoflush by default:
 http://docs.jboss.org/hibernate/orm/4.2/manual/en-US/html/ch11.html
 #objectstate-flushing

 Storm ORM (at one time a key SQLAlchemy inspiration / competitor) -
 autoflush by default: https://storm.canonical.com/Tutorial#Flushing

 Doctrine ORM (the creator of Doctrine is now a Python programmer and uses
 SQLAlchemy), but OK, they don't autoflush (ever? though I seem to see it
 in their source): https://doctrine-orm.readthedocs.org/en/latest/reference
 /working-with-objects.html#effects-of-database-and-unitofwork-being-out-
 of-sync

 nhibernate (.NET version of hibernate) - autoflush by default:
 http://nhforge.org/doc/nh/en/index.html#manipulatingdata-flushing

 now, we're always improving how and when we call autoflush, but for the
 moment we autoflush when you access an unloaded collection.  because that
 collection's state can be composed of pending data.

 for years, I was completely uncomfortable with the idea of autoflush, and
 even though i emulated hibernate, I thought the feature was nuts.  but
 after dealing with the product directly and via user experience, we
 switched it to true - but it is always an option that can be modified.   I
 find it most effective to be left on most of the time, and disabled just
 in those cases where it gets in the way.   these cases are easy to
 identify by looking for "autoflush" in the stack trace.  it makes lots of
 neat patterns possible, originally the "dynamic relationship loader" was
 the main one, where otherwise you'd append an item to the list, then
 access the list but the item you just appended wouldn't be there if not
 for autoflush.  But this kind of pattern turned out to be a lot more
 prevalent after that point.

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

Re: [Sqlalchemy-tickets] [sqlalchemy] #2816: clean up identity_key util documentation

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

#2816: clean up identity_key util documentation
--------------------------------+----------------------------------
      Reporter:  zzzeek         |      Owner:  zzzeek
          Type:  defect         |     Status:  new
      Priority:  high           |  Milestone:  0.8.xx
     Component:  documentation  |   Severity:  minor - half an hour
    Resolution:                 |   Keywords:
Progress State:  in queue       |
--------------------------------+----------------------------------

Comment (by zzzeek):

 also figure out this part:

 {{{
 #!diff
 @@ -196,6 +199,7 @@ def identity_key(*args, **kwargs):
          elif len(args) == 2:
              class_, ident = args
          elif len(args) == 3:
 +            # XXX: How can this work? I'd expect a ValueError here  --
 Torsten Landschoff
              class_, ident = args
          else:
              raise sa_exc.ArgumentError("expected up to three "
 }}}

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

[Sqlalchemy-tickets] [sqlalchemy] #2816: clean up identity_key util documentation

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

#2816: clean up identity_key util documentation
---------------------------+---------------------------------------
 Reporter:  zzzeek         |           Owner:  zzzeek
     Type:  defect         |          Status:  new
 Priority:  high           |       Milestone:  0.8.xx
Component:  documentation  |        Severity:  minor - half an hour
 Keywords:                 |  Progress State:  in queue
---------------------------+---------------------------------------
 these old functions are confusing esp when given an instance, as you
 usually just want inspect(obj).key.

 A patch with some partial wording:

 {{{
 #!diff
 --- a/lib/sqlalchemy/orm/mapper.py
 +++ b/lib/sqlalchemy/orm/mapper.py
 @@ -2225,7 +2225,9 @@ class Mapper(_InspectionAttr):

      def identity_key_from_instance(self, instance):
          """Return the identity key for the given instance, based on
 -        its primary key attributes.
 +        its primary key attributes. If the instance's state is expired
 this
 +        will check if the object has been deleted. If that is the case,
 +        :class:`~sqlalchemy.orm.exc.ObjectDeletedError` is raised.

          This value is typically also found on the instance state under
 the
          attribute name `key`.
 @@ -2245,7 +2247,9 @@ class Mapper(_InspectionAttr):

      def primary_key_from_instance(self, instance):
          """Return the list of primary key values for the given
 -        instance.
 +        instance. If the instance's state is expired this
 +        will check if the object has been deleted. If that is the case,
 +        :class:`~sqlalchemy.orm.exc.ObjectDeletedError` is raised.

          """
          state = attributes.instance_state(instance)
 diff --git a/lib/sqlalchemy/orm/util.py b/lib/sqlalchemy/orm/util.py
 index 9737072..f3f2c05 100644
 --- a/lib/sqlalchemy/orm/util.py
 +++ b/lib/sqlalchemy/orm/util.py
 @@ -176,6 +176,9 @@ def identity_key(*args, **kwargs):

        instance
            object instance (must be given as a keyword arg)
 +      If the instance's state is expired this will check if the object
 has
 +      been deleted. If that is the case,
 +      :class:`~sqlalchemy.orm.exc.ObjectDeletedError` is raised.

      * ``identity_key(class, row=row)``

 }}}

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

Re: [Sqlalchemy-tickets] [sqlalchemy] #2809: Strange effect with association proxy and event listener

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

#2809: Strange effect with association proxy and event listener
-----------------------------------+------------------------------------
      Reporter:  schlamar          |      Owner:  zzzeek
          Type:  defect            |     Status:  closed
      Priority:  medium            |  Milestone:  0.8.xx
     Component:  (none)            |   Severity:  no triage selected yet
    Resolution:  invalid           |   Keywords:
Progress State:  completed/closed  |
-----------------------------------+------------------------------------

Comment (by zzzeek):

 Replying to [comment:8 schlamar]:
 > You should even consider if autoflush=False as default doesn't fit
 better to your "no magic" policy.

 that's how it was for years.  autoflush being on has solved a lot of
 problems.   I'd ask that you consider this project has been going for
 eight years and has evolved tremendously.  At this point, not a lot is by
 accident anymore.

 > The behavior above is really unexpected (and I couldn't find any clue
 about such an issue in documentation) and I found similar issues regarding
 autoflush across the net, e.g. http://lists.okfn.org/pipermail/ckan-
 dev/2011-January/000189.html

 there were many issues with autoflush being turned off too.

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

Re: [Sqlalchemy-tickets] [sqlalchemy] #2809: Strange effect with association proxy and event listener

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

#2809: Strange effect with association proxy and event listener
-----------------------------------+------------------------------------
      Reporter:  schlamar          |      Owner:  zzzeek
          Type:  defect            |     Status:  closed
      Priority:  medium            |  Milestone:  0.8.xx
     Component:  (none)            |   Severity:  no triage selected yet
    Resolution:  invalid           |   Keywords:
Progress State:  completed/closed  |
-----------------------------------+------------------------------------

Comment (by schlamar):

 You should even consider if autoflush=False as default doesn't fit better
 to your "no magic" policy.

 The behavior above is really unexpected (and I couldn't find any clue
 about such an issue in documentation) and I found similar issues regarding
 autoflush across the net, e.g. http://lists.okfn.org/pipermail/ckan-
 dev/2011-January/000189.html

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

Re: [Sqlalchemy-tickets] [sqlalchemy] #2809: Strange effect with association proxy and event listener

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

#2809: Strange effect with association proxy and event listener
-----------------------------------+------------------------------------
      Reporter:  schlamar          |      Owner:  zzzeek
          Type:  defect            |     Status:  closed
      Priority:  medium            |  Milestone:  0.8.xx
     Component:  (none)            |   Severity:  no triage selected yet
    Resolution:  invalid           |   Keywords:
Progress State:  completed/closed  |
-----------------------------------+------------------------------------

Comment (by schlamar):

 I'm still not convinced, sorry.

 I know that this is a constructed example, but any query to the database
 in the event handler will cause the auto flush and therefore cause this
 exception (which makes it not such a constructed case). And I know that I
 can workaround this issue by wrapping the event handler code into an `with
 session.no_autoflush`.

 But I see this just as a workaround and not as the solution. I do not
 agree that this should be up to the user. IMO sqlalchemy should detect
 that it is in an inconsistent state where a flush would (or could) fail
 and automatically disable it until you are back in a consistent state.

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

Re: [Sqlalchemy-tickets] [sqlalchemy] #2809: Strange effect with association proxy and event listener

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

#2809: Strange effect with association proxy and event listener
-----------------------------------+------------------------------------
      Reporter:  schlamar          |      Owner:  zzzeek
          Type:  defect            |     Status:  closed
      Priority:  medium            |  Milestone:  0.8.xx
     Component:  (none)            |   Severity:  no triage selected yet
    Resolution:  invalid           |   Keywords:
Progress State:  completed/closed  |
-----------------------------------+------------------------------------
Changes (by zzzeek):

 * status:  reopened => closed
 * resolution:   => invalid


Comment:

 OK, please, please, can we go to the mailing list.  this is not a bug.
 You are calling flush() in the middle of an event which is occurring
 before your object is ready to be flushed.  if you remove the event
 handler you're adding, there's no stack trace - you've essentially
 illustrated with flush() what is already happening in your real app due to
 autoflush (note the original stack trace has _autoflush() vs. this one
 which has flush()), and I've already explained, if you have a block that
 can't be autoflushed, please use the "no_autoflush" helper, that's what
 it's for.

 Of course if you tell the session to flush() at the point at which your
 objects are not completely associated with each other such that you'll get
 integrity violations due to null constraints, you'll get an integrity
 violation.   SQLAlchemy can't magically undo what you're telling it to do
 in order to avoid an exception.

 Lets talk about where/how you can temporarily turn off autoflush on the
 mailing list, it's here: http://groups.google.com/group/sqlalchemy/
 (though I'm off the grid for the next couple of days).

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

Re: [Sqlalchemy-tickets] [sqlalchemy] #2809: Strange effect with association proxy and event listener

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

#2809: Strange effect with association proxy and event listener
-----------------------------------+------------------------------------
      Reporter:  schlamar          |      Owner:  zzzeek
          Type:  defect            |     Status:  reopened
      Priority:  medium            |  Milestone:  0.8.xx
     Component:  (none)            |   Severity:  no triage selected yet
    Resolution:                    |   Keywords:
Progress State:  completed/closed  |
-----------------------------------+------------------------------------

Comment (by schlamar):

 BTW, the testcase works if Keyword doesn't have a reference to
 !UserKeyword. This was the key issue to find a reproducible testcase.

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

Re: [Sqlalchemy-tickets] [sqlalchemy] #2809: Strange effect with association proxy and event listener

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

#2809: Strange effect with association proxy and event listener
-----------------------------------+------------------------------------
      Reporter:  schlamar          |      Owner:  zzzeek
          Type:  defect            |     Status:  reopened
      Priority:  medium            |  Milestone:  0.8.xx
     Component:  (none)            |   Severity:  no triage selected yet
    Resolution:                    |   Keywords:
Progress State:  completed/closed  |
-----------------------------------+------------------------------------
Changes (by schlamar):

 * status:  closed => reopened
 * resolution:  worksforme =>


Comment:

 Finally I was able to find a common test case for this issue. It looks
 like that the combination of association proxy + event listening on the
 associated attribute has the effect that you have an inconsistent state
 while in the event handler which causes any flush to fail.


 {{{
 #!python


 from sqlalchemy import (create_engine, event, inspect, Column, Integer,
                         ForeignKey, String)
 from sqlalchemy.orm import relationship, sessionmaker

 from sqlalchemy.ext.associationproxy import association_proxy
 from sqlalchemy.ext.declarative import declarative_base


 Base = declarative_base()


 class User(Base):
     __tablename__ = 'user'
     id = Column(Integer, primary_key=True)
     name = Column(String(64))
     keywords = association_proxy('user_keywords', 'keyword')

     user_keywords = relationship('UserKeyword', cascade='all, delete-
 orphan',
                                  backref='user')


 @event.listens_for(User.user_keywords, 'append')
 def keywords_added(user, value, initiator):
     session = inspect(user).session
     if session:
         session.flush()


 class UserKeyword(Base):
     __tablename__ = 'user_keyword'
     user_id = Column(Integer, ForeignKey('user.id'), primary_key=True)
     keyword_id = Column(Integer, ForeignKey('keyword.id'),
 primary_key=True)

     def __init__(self, keyword=None, **kwargs):
         super(UserKeyword, self).__init__(keyword=keyword, **kwargs)


 class Keyword(Base):
     __tablename__ = 'keyword'
     id = Column(Integer, primary_key=True)
     keyword = Column(String(64))

     user_keywords = relationship(
         'UserKeyword', cascade='all, delete-orphan',
         backref='keyword')


 engine = create_engine('sqlite:///:memory:')
 Base.metadata.create_all(engine)
 Session = sessionmaker(bind=engine)
 session = Session()

 user = User(name='xx')
 user.keywords.append(Keyword(keyword='x'))
 session.add(user)
 session.commit()

 session = Session()

 user = session.query(User).first()
 for kw in session.query(Keyword):
     user.keywords.append(kw)

 }}}

 Result:

 {{{
 #!pytb
 $ python sqla_2809.py
 Traceback (most recent call last):
   File "sqla_2809.py", line 63, in <module>
     user.keywords.append(kw)
   File
 "d:\Projekte\HACS\hacs\packages\sqlalchemy\ext\associationproxy.py", line
 568, in append
     self.col.append(item)
   File "d:\Projekte\HACS\hacs\packages\sqlalchemy\orm\collections.py",
 line 1057, in append
     item = __set(self, item, _sa_initiator)
   File "d:\Projekte\HACS\hacs\packages\sqlalchemy\orm\collections.py",
 line 1029, in __set
     item = getattr(executor, 'fire_append_event')(item, _sa_initiator)
   File "d:\Projekte\HACS\hacs\packages\sqlalchemy\orm\collections.py",
 line 733, in fire_append_event
     item, initiator)
   File "d:\Projekte\HACS\hacs\packages\sqlalchemy\orm\attributes.py", line
 910, in fire_append_event
     value = fn(state, value, initiator or self)
   File "d:\Projekte\HACS\hacs\packages\sqlalchemy\orm\events.py", line
 1496, in wrap
     orig_fn(target, value, *arg)
   File "sqla_2809.py", line 27, in keywords_added
     session.flush()
   File "d:\Projekte\HACS\hacs\packages\sqlalchemy\orm\session.py", line
 1818, in flush
     self._flush(objects)
   File "d:\Projekte\HACS\hacs\packages\sqlalchemy\orm\session.py", line
 1936, in _flush
     transaction.rollback(_capture_exception=True)
   File "d:\Projekte\HACS\hacs\packages\sqlalchemy\util\langhelpers.py",
 line 58, in __exit__
     compat.reraise(exc_type, exc_value, exc_tb)
   File "d:\Projekte\HACS\hacs\packages\sqlalchemy\orm\session.py", line
 1900, in _flush
     flush_context.execute()
   File "d:\Projekte\HACS\hacs\packages\sqlalchemy\orm\unitofwork.py", line
 372, in execute
     rec.execute(self)
   File "d:\Projekte\HACS\hacs\packages\sqlalchemy\orm\unitofwork.py", line
 525, in execute
     uow
   File "d:\Projekte\HACS\hacs\packages\sqlalchemy\orm\persistence.py",
 line 64, in save_obj
     table, insert)
   File "d:\Projekte\HACS\hacs\packages\sqlalchemy\orm\persistence.py",
 line 569, in _emit_insert_statements
     execute(statement, params)
   File "d:\Projekte\HACS\hacs\packages\sqlalchemy\engine\base.py", line
 662, in execute
     params)
   File "d:\Projekte\HACS\hacs\packages\sqlalchemy\engine\base.py", line
 761, in _execute_clauseelement
     compiled_sql, distilled_params
   File "d:\Projekte\HACS\hacs\packages\sqlalchemy\engine\base.py", line
 874, in _execute_context
     context)
   File "d:\Projekte\HACS\hacs\packages\sqlalchemy\engine\base.py", line
 1024, in _handle_dbapi_exception
     exc_info
   File "d:\Projekte\HACS\hacs\packages\sqlalchemy\util\compat.py", line
 195, in raise_from_cause
     reraise(type(exception), exception, tb=exc_tb)
   File "d:\Projekte\HACS\hacs\packages\sqlalchemy\engine\base.py", line
 867, in _execute_context
     context)
   File "d:\Projekte\HACS\hacs\packages\sqlalchemy\engine\default.py", line
 324, in do_execute
     cursor.execute(statement, parameters)
 sqlalchemy.exc.IntegrityError: (IntegrityError) user_keyword.user_id may
 not be NULL u'INSERT INTO user_keyword (keyword
 _id) VALUES (?)' (1,)

 }}}

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

Re: [Sqlalchemy-tickets] [sqlalchemy] #2815: any way to detect this cycle?

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

#2815: any way to detect this cycle?
---------------------------+----------------------------------
      Reporter:  zzzeek    |      Owner:  zzzeek
          Type:  defect    |     Status:  new
      Priority:  high      |  Milestone:  0.8.xx
     Component:  sql       |   Severity:  minor - half an hour
    Resolution:            |   Keywords:
Progress State:  in queue  |
---------------------------+----------------------------------

Comment (by zzzeek):

 that really only catches exactly this mistake and nothing else, but im not
 sure what other variants of this mistake there are.

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

Re: [Sqlalchemy-tickets] [sqlalchemy] #2815: any way to detect this cycle?

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

#2815: any way to detect this cycle?
---------------------------+----------------------------------
      Reporter:  zzzeek    |      Owner:  zzzeek
          Type:  defect    |     Status:  new
      Priority:  high      |  Milestone:  0.8.xx
     Component:  sql       |   Severity:  minor - half an hour
    Resolution:            |   Keywords:
Progress State:  in queue  |
---------------------------+----------------------------------

Comment (by zzzeek):

 eh?  maybe

 {{{
 #!diff
 diff --git a/lib/sqlalchemy/sql/selectable.py
 b/lib/sqlalchemy/sql/selectable.py
 index e06262c..3ee0bfb 100644
 --- a/lib/sqlalchemy/sql/selectable.py
 +++ b/lib/sqlalchemy/sql/selectable.py
 @@ -1936,6 +1936,8 @@ class Select(HasPrefixes, SelectBase):

          def add(items):
              for item in items:
 +                if item is self:
 +                    raise exc.InvalidRequestError("boom")
                  if translate and item in translate:
                      item = translate[item]
                  if not seen.intersection(item._cloned_set):
 }}}

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

[Sqlalchemy-tickets] [sqlalchemy] #2815: any way to detect this cycle?

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

#2815: any way to detect this cycle?
--------------------+---------------------------------------
 Reporter:  zzzeek  |           Owner:  zzzeek
     Type:  defect  |          Status:  new
 Priority:  high    |       Milestone:  0.8.xx
Component:  sql     |        Severity:  minor - half an hour
 Keywords:          |  Progress State:  in queue
--------------------+---------------------------------------
 also why does it happen with append_whereclause, but not where?

 {{{
 #!python
 from sqlalchemy import *
 from sqlalchemy.sql import table, column

 t = table('t', column('x'))

 s = select([t])

 s = s.append_whereclause(s.c.x > 5)

 print s
 }}}

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