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

Re: [Sqlalchemy-tickets] [sqlalchemy] #2726: ColumnClause pretends to be iterable and then isn't

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

#2726: ColumnClause pretends to be iterable and then isn't
---------------------------+-----------------------------------
      Reporter:  gthb      |      Owner:  zzzeek
          Type:  defect    |     Status:  new
      Priority:  high      |  Milestone:  0.8.xx
     Component:  sql       |   Severity:  trivial - <10 minutes
    Resolution:            |   Keywords:
Progress State:  in queue  |
---------------------------+-----------------------------------
Changes (by zzzeek):

 * priority:  medium => high
 * severity:  no triage selected yet => trivial - <10 minutes
 * status_field:  awaiting triage => in queue


Comment:

 there's not much option here except to take that whole thing out, and wait
 for the stackoverflow issues when people call list() on their custom
 columns...

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

[Sqlalchemy-tickets] [sqlalchemy] #2727: ProgrammingError when autoload from PostgreSQL (Amazon Redshift)

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

#2727: ProgrammingError when autoload from PostgreSQL (Amazon Redshift)
-------------------------+-----------------------------------------
 Reporter:  ashkop       |           Owner:  zzzeek
     Type:  defect       |          Status:  new
 Priority:  medium       |       Milestone:  0.8.xx
Component:  cextensions  |        Severity:  no triage selected yet
 Keywords:               |  Progress State:  awaiting triage
-------------------------+-----------------------------------------
 I'm trying to use sqlalchemy with Amazon Redshift. It uses Postgres
 drivers so I'm using psycopg2.
 Here's what I got when trying to autload table structure:
 {{{
 >>> from sqlalchemy import *
 >>> engine = create_engine('postgresql+psycopg2://<connection string>')
 >>> meta = MetaData(engine)
 >>> t = Table('table_name', meta, autoload=True)
 Traceback (most recent call last):
   File "<stdin>", line 1, in <module>
   File "/usr/local/lib/python2.7/site-packages/sqlalchemy/schema.py", line
 332, in __new__
     table._init(name, metadata, *args, **kw)
   File "/usr/local/lib/python2.7/site-packages/sqlalchemy/schema.py", line
 396, in _init
     self._autoload(metadata, autoload_with, include_columns)
   File "/usr/local/lib/python2.7/site-packages/sqlalchemy/schema.py", line
 424, in _autoload
     self, include_columns, exclude_columns
   File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py",
 line 1595, in run_callable
     return conn.run_callable(callable_, *args, **kwargs)
   File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py",
 line 1118, in run_callable
     return callable_(self, *args, **kwargs)
   File "/usr/local/lib/python2.7/site-
 packages/sqlalchemy/engine/default.py", line 262, in reflecttable
     return insp.reflecttable(table, include_columns, exclude_columns)
   File "/usr/local/lib/python2.7/site-
 packages/sqlalchemy/engine/reflection.py", line 443, in reflecttable
     pk_cons = self.get_pk_constraint(table_name, schema, **tblkw)
   File "/usr/local/lib/python2.7/site-
 packages/sqlalchemy/engine/reflection.py", line 290, in get_pk_constraint
     **kw)
   File "<string>", line 1, in <lambda>
   File "/usr/local/lib/python2.7/site-
 packages/sqlalchemy/engine/reflection.py", line 49, in cache
     ret = fn(self, con, *args, **kw)
   File "/usr/local/lib/python2.7/site-
 packages/sqlalchemy/dialects/postgresql/base.py", line 1823, in
 get_pk_constraint
     c = connection.execute(t, table_oid=table_oid)
   File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py",
 line 662, in execute
     params)
   File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py",
 line 761, in _execute_clauseelement
     compiled_sql, distilled_params
   File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py",
 line 874, in _execute_context
     context)
   File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py",
 line 1024, in _handle_dbapi_exception
     exc_info
   File "/usr/local/lib/python2.7/site-packages/sqlalchemy/util/compat.py",
 line 163, in raise_from_cause
     reraise(type(exception), exception, tb=exc_tb)
   File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py",
 line 867, in _execute_context
     context)
   File "/usr/local/lib/python2.7/site-
 packages/sqlalchemy/engine/default.py", line 324, in do_execute
     cursor.execute(statement, parameters)
 sqlalchemy.exc.ProgrammingError: (ProgrammingError) op ANY/ALL (array)
 requires array on right side
  "\n                SELECT a.attname\n                FROM\n
 pg_class t\n                    join pg_index ix on t.oid = ix.indrelid\n
 join pg_attribute a\n                        on t.oid=a.attrelid and
 a.attnum=ANY(ix.indkey)\n                 WHERE\n                  t.oid =
 %(table_oid)s and ix.indisprimary = 't'\n                ORDER BY
 a.attnum\n            " {'table_oid': 165790}
 }}}

 I'm using sqlalchemy 0.8.1, Python 2.7.4

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

Re: [Sqlalchemy-tickets] [sqlalchemy] #2726: ColumnClause pretends to be iterable and then isn't

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

#2726: ColumnClause pretends to be iterable and then isn't
----------------------------------+------------------------------------
      Reporter:  gthb             |      Owner:  zzzeek
          Type:  defect           |     Status:  new
      Priority:  medium           |  Milestone:  0.8.xx
     Component:  sql              |   Severity:  no triage selected yet
    Resolution:                   |   Keywords:
Progress State:  awaiting triage  |
----------------------------------+------------------------------------

Comment (by gthb):

 Hopefully nothing, for a while. :) (Our codebase is not a library, does
 not need to run on Python 3 until either (a) we want it to, or (b)
 libraries we want to use no longer support 2.7.x, neither of which seems
 imminent.)

 We're not only detecting lists/tuples/sets, it's also generators, and our
 own types with `__iter__` defined. So just `isinstance(o, (list, tuple,
 set))` or `isinstance(o, collections.Sequence)` wouldn't hack it. In these
 (fairly common) cases we're looking for anything iterable that isn't a
 string. Presumably we'll just end up saying that verbatim, i.e.
 `isinstance(o, collections.Iterable) and not isinstance(o, basestring)`,
 which isn't much neater, but at least explicit.

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

Re: [Sqlalchemy-tickets] [sqlalchemy] #2726: ColumnClause pretends to be iterable and then isn't

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

#2726: ColumnClause pretends to be iterable and then isn't
----------------------------------+------------------------------------
      Reporter:  gthb             |      Owner:  zzzeek
          Type:  defect           |     Status:  new
      Priority:  medium           |  Milestone:  0.8.xx
     Component:  sql              |   Severity:  no triage selected yet
    Resolution:                   |   Keywords:
Progress State:  awaiting triage  |
----------------------------------+------------------------------------

Comment (by zzzeek):

 what are you going to do about strings in Python 3?  they have
 `__iter__()` also.   I'm not sure if checking for `__iter__`/`Iterable`
 and nothing else to detect lists/tuples/sets is really feasible due to
 that.

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

[Sqlalchemy-tickets] [sqlalchemy] #2726: ColumnClause pretends to be iterable and then isn't

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

#2726: ColumnClause pretends to be iterable and then isn't
--------------------+-----------------------------------------
 Reporter:  gthb    |           Owner:  zzzeek
     Type:  defect  |          Status:  new
 Priority:  medium  |       Milestone:  0.8.xx
Component:  sql     |        Severity:  no triage selected yet
 Keywords:          |  Progress State:  awaiting triage
--------------------+-----------------------------------------
 Updating to SQLAlchemy 0.8, I'm getting regressions because
 `ColumnClause.__iter__` now exists (defined in `ColumnOperators` in
 r4230396c6e24) only to raise an exception. This sucker-punches code that
 checks for iterableness either by `hasattr(o, '__iter__')` or
 `isinstance(o, collections.Iterable)` ... such as this `VALUES` compiler
 that supports tuples/lists/iterables on PostgreSQL, representing them as
 `ARRAY`s:

 {{{#!python
 @compiles(values)
 def compile_values(element, compiler, asfrom=False, **kw):
     v = u"VALUES %s" % ", ".join(
         u"(%s)" % u", ".join(
             u'ARRAY[%s]' % ','.join(
                 (u"'%s'" % subel.replace(u"'", u"''").replace(u"%",
 u"%%"))
                 if isinstance(subel, unicode)
                 else str(subel) if isinstance(subel, int)
                 else compiler.process(subel)
                 for subel in elem
             ) if hasattr(elem, '__iter__')
             and elem.__iter__.__func__ is not blocking_iter_func
             else (u"'%s'" % elem.replace(u"'", u"''").replace(u"%",
 u"%%"))
             if isinstance(elem, unicode)
             else u'NULL' if elem is None
             else compiler.process(elem) if isinstance(elem, ColumnElement)
             else repr(elem)
             for elem in tup)
         for tup in element.list
     )
     if asfrom:
         v = "(%s)" % v
     return v
 }}}

 I get what the method is there for, but it satisfies the conventional ways
 of checking for iterableness, while it's actually there to assert
 ''non-''iterableness. That's not very neat.

 I'm provisionally working around this as a special case:

 {{{#!python
 from sqlalchemy.sql.operators import ColumnOperators
 blocking_iter_func = ColumnOperators.__iter__.__func__

     ... if hasattr(elem, '__iter__') and elem.__iter__.__func__ is not
 blocking_iter_func
 }}}

 (and the wind carries a soft murmur of kittens screaming in the
 distance...)

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

Re: [Sqlalchemy-tickets] [sqlalchemy] #2481: more specific check for recursion in joinedload w no joined_depth

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

#2481: more specific check for recursion in joinedload w no joined_depth
-----------------------------------+----------------------------------
      Reporter:  zzzeek            |      Owner:  zzzeek
          Type:  defect            |     Status:  closed
      Priority:  medium            |  Milestone:  0.8.0b1
     Component:  orm               |   Severity:  minor - half an hour
    Resolution:  fixed             |   Keywords:
Progress State:  completed/closed  |
-----------------------------------+----------------------------------
Changes (by zzzeek):

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


Comment:

 r7699a1080742

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

Re: [Sqlalchemy-tickets] [sqlalchemy] #2481: more specific check for recursion in joinedload w no joined_depth

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

#2481: more specific check for recursion in joinedload w no joined_depth
------------------------------+----------------------------------
      Reporter:  zzzeek       |      Owner:  zzzeek
          Type:  defect       |     Status:  reopened
      Priority:  medium       |  Milestone:  0.8.0b1
     Component:  orm          |   Severity:  minor - half an hour
    Resolution:               |   Keywords:
Progress State:  in progress  |
------------------------------+----------------------------------
Changes (by zzzeek):

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


Comment:

 failure:

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

 e = create_engine('sqlite:///', echo=True)
 Base = declarative_base()
 Base.metadata = MetaData(e)


 class Item(Base):
     __tablename__ = 'items'

     id = Column(Integer, primary_key=True)
     item_type = Column(String(24), default='item')

     __mapper_args__ = {
         'polymorphic_identity': 'item',
         'polymorphic_on': 'item_type',
         'with_polymorphic': '*',
         }

 class PolyItem(Item):
     __tablename__ = 'poly_items'

     id = Column(Integer, ForeignKey(Item.id), primary_key=True)

     __mapper_args__ = {
         'polymorphic_identity': 'polyitem',
         }

     item = relationship(Item, lazy=False)

 if __name__ == '__main__':
     Base.metadata.create_all()

     session = Session(e)

     session.query(Item).first()

 }}}

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

Re: [Sqlalchemy-tickets] [sqlalchemy] #2725: The sense foreign_keys parameter to orm.relationship is unclear

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

#2725: The sense foreign_keys parameter to orm.relationship is unclear
-----------------------------------+----------------------------------
      Reporter:  amluto            |      Owner:  zzzeek
          Type:  defect            |     Status:  closed
      Priority:  medium            |  Milestone:  0.8.xx
     Component:  documentation     |   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:  needs questions answered => completed/closed


Comment:

 Replying to [comment:2 amluto]:
 > Hmm.  That particular example is actually not bad, although I got thrown
 off a bit since I don't use Declarative.  (My project predates
 declarative.)

 Well obviously the declarative use case is the focus of the documentation
 since that's the standard.

 >
 > The actual situation I have is that there are three tables, A, B, and C.
 B is a child of A and C is a child of both A and B.
 >
 > A has a_id, B has a_id (a foreign key to A) and b_id (its primary key).
 C has a_id and b_id.  The catch is that, for each row in C, we require
 that a_id match both a row in A and the a_id from the corresponding row in
 B.  So the ForeignKey to A in the schema is unnecessary, but I still want
 to map it.

 that's a significantly more esoteric use case than the one where a
 Customer has to link to two different Address objects ;).  The `C.a_id`
 column here is essentially redundant.


 > I still think that making the documentation for the foreign_key
 parameter (as opposed to just an example) unambiguous would improve the
 situation.

 I'm guessing that you're referring to the docstring on relationship
 itself, so I have updated this to be more modern and to also refer to the
 previously mentioned sections in rd0aaaf28c640.

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

Re: [Sqlalchemy-tickets] [sqlalchemy] #2725: The sense foreign_keys parameter to orm.relationship is unclear

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

#2725: The sense foreign_keys parameter to orm.relationship is unclear
-------------------------------------------+-------------------------------
      Reporter:  amluto                    |      Owner:  zzzeek
          Type:  defect                    |     Status:  new
      Priority:  medium                    |  Milestone:  0.8.xx
     Component:  documentation             |   Severity:  no triage
    Resolution:                            |  selected yet
Progress State:  needs questions answered  |   Keywords:
-------------------------------------------+-------------------------------

Comment (by amluto):

 Hmm.  That particular example is actually not bad, although I got thrown
 off a bit since I don't use Declarative.  (My project predates
 declarative.)

 The actual situation I have is that there are three tables, A, B, and C.
 B is a child of A and C is a child of both A and B.

 A has a_id, B has a_id (a foreign key to A) and b_id (its primary key).  C
 has a_id and b_id.  The catch is that, for each row in C, we require that
 a_id match both a row in A and the a_id from the corresponding row in B.
 So the ForeignKey to A in the schema is unnecessary, but I still want to
 map it.

 I still think that making the documentation for the foreign_key parameter
 (as opposed to just an example) unambiguous would improve the situation.

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

Re: [Sqlalchemy-tickets] [sqlalchemy] #2725: The sense foreign_keys parameter to orm.relationship is unclear

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

#2725: The sense foreign_keys parameter to orm.relationship is unclear
-------------------------------------------+-------------------------------
      Reporter:  amluto                    |      Owner:  zzzeek
          Type:  defect                    |     Status:  new
      Priority:  medium                    |  Milestone:  0.8.xx
     Component:  documentation             |   Severity:  no triage
    Resolution:                            |  selected yet
Progress State:  needs questions answered  |   Keywords:
-------------------------------------------+-------------------------------
Changes (by zzzeek):

 * status_field:  awaiting triage => needs questions answered
 * milestone:   => 0.8.xx


Comment:

 well foreign_keys is the kind of parameter that you don't really use
 unless an error message tells you to.  The main introduction to this
 concept is here:
 http://docs.sqlalchemy.org/en/rel_0_8/orm/relationships.html#handling-
 multiple-join-paths.  The example is a customer with a "billing" and
 "shipping" address.  Is that the example you're saying is "arcane"?  some
 specifics here would be helpful.

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

[Sqlalchemy-tickets] [sqlalchemy] #2725: The sense foreign_keys parameter to orm.relationship is unclear

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

#2725: The sense foreign_keys parameter to orm.relationship is unclear
---------------------------+-----------------------------------------
 Reporter:  amluto         |           Owner:  zzzeek
     Type:  defect         |          Status:  new
 Priority:  medium         |       Milestone:
Component:  documentation  |        Severity:  no triage selected yet
 Keywords:                 |  Progress State:  awaiting triage
---------------------------+-----------------------------------------
 I just spent several hours cursing at truly bizarre errors involving
 circular dependencies and then, once I fixed those, bogus SQL queries
 being generated.  The problem is that the documentation for foreign_keys
 does not indicate which ''side'' of the foreign key relationship is
 supposed to be specified.

 Something like:

 foreign_keys - a list of columns which are to be used as “foreign key”
 columns.  These are the columns on the table that is constrained to
 reference another table, not the columns being referenced.

 would help a lot.  (It's also unhelpful that all of the examples given are
 rather arcane.  A completely trivial example showing a normal ForeignKey
 and the foreign_key and primaryjoin parameters that are implied would make
 this much easier to follow.)

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

Re: [Sqlalchemy-tickets] [sqlalchemy] #2720: new C extensions for attribute accessing

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

#2720: new C extensions for attribute accessing
-----------------------------------+---------------------------------------
      Reporter:  zzzeek            |      Owner:  zzzeek
          Type:  enhancement       |     Status:  new
      Priority:  medium            |  Milestone:  0.9.0
     Component:  cextensions       |   Severity:  very major - up to 2 days
    Resolution:                    |   Keywords:
Progress State:  not decided upon  |
-----------------------------------+---------------------------------------

Comment (by claudiofreire):

 Well, it certainly is tricky to get optimal performance, but I've never
 failed achieving C speeds with Pyrex. I know Cython tries to do type
 inference to speed up things, in Pyrex you have to annotate types. I've
 found explicit annotation is more predictable than relying on type
 inference, that's why I prefer Pyrex. I just need careful cdefs and
 everything flies.

 When there is a need for native C code, you can always include C code from
 Pyrex. I've in fact done that to include C++ code, when C++'s template
 power was required. The benefit over pure C extensions is that you avoid
 all that CPython boilerplate.

 So it's a matter of how much needs to be pure C and how much can Pyrex
 replace, I don't think it will make a difference in speed (ie: it will be
 just as fast).

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

Re: [Sqlalchemy-tickets] [sqlalchemy] #2720: new C extensions for attribute accessing

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

#2720: new C extensions for attribute accessing
-----------------------------------+---------------------------------------
      Reporter:  zzzeek            |      Owner:  zzzeek
          Type:  enhancement       |     Status:  new
      Priority:  medium            |  Milestone:  0.9.0
     Component:  cextensions       |   Severity:  very major - up to 2 days
    Resolution:                    |   Keywords:
Progress State:  not decided upon  |
-----------------------------------+---------------------------------------

Comment (by ged):

 Replying to [comment:16 claudiofreire]:
 > BTW: Hold on a bit if 426 lines of C look intimidating (I'd think so),
 since I still have to try Pyrex. I've got to check whether Pyrex can
 replace the other extensions too, for it would be pointless to just
 introduce it for this one and not the others.

 FWIW, I discovered Cython just after I finished implementing the initial C
 extensions (but before it got committed), so I did try to generate them
 using Cython. Back then, I could not get Cython (never tried pyrex) code
 to be nearly as fast as the pure C extensions, so I dropped the idea since
 the pure C extensions were already written. It was faster than pure-python
 but not fast enough (IMO) to be worth it. It is certainly worth it to try
 that again, as if the speed improvement is close enough to the pure C
 extensions, Cython code is indeed much more maintainable.

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

Re: [Sqlalchemy-tickets] [sqlalchemy] #2720: new C extensions for attribute accessing

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

#2720: new C extensions for attribute accessing
-----------------------------------+---------------------------------------
      Reporter:  zzzeek            |      Owner:  zzzeek
          Type:  enhancement       |     Status:  new
      Priority:  medium            |  Milestone:  0.9.0
     Component:  cextensions       |   Severity:  very major - up to 2 days
    Resolution:                    |   Keywords:
Progress State:  not decided upon  |
-----------------------------------+---------------------------------------

Comment (by claudiofreire):

 Ok, timeit.

 This is the test:

 {{{
 >>> def test():
 ...    for _ in xrange(10):
 ...        _ = rc.hash
 ...        _ = rc.hash
 ...        _ = rc.hash
 ...        _ = rc.hash
 ...        _ = rc.hash
 ...        _ = rc.hash
 ...        _ = rc.hash
 ...        _ = rc.hash
 ...        _ = rc.hash
 ...        _ = rc.hash
 ...        _ = rc.hash
 ...        _ = rc.hash
 ...        _ = rc.hash
 ...        _ = rc.hash
 ...        _ = rc.hash
 ...        _ = rc.hash
 ...
 }}}

 Yeah, I did it on the console, and yeah, I made sure both runs contained
 the same number of rc.hash lines.

 With cinstrumented:

 {{{
 >>> timeit.timeit(test)
 156.2858281135559
 }}}

 Without cinstrumented:

 {{{
 >>> timeit.timeit(test)
 188.22997498512268
 }}}

 Unsurprising.

 PS: I did not include a query in the timeit function because I'm working
 with a remote DB ATM, and it'd overshadow attribute access. I don't have a
 local db handy.

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

Re: [Sqlalchemy-tickets] [sqlalchemy] #2723: SQLAlchemy relationships on inherited attributes fail

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

#2723: SQLAlchemy relationships on inherited attributes fail
-------------------------------------+-------------------------------------
      Reporter:  mitchellrj          |      Owner:  zzzeek
          Type:  defect              |     Status:  closed
      Priority:  medium              |  Milestone:
     Component:  orm                 |   Severity:  no triage selected yet
    Resolution:  worksforme          |   Keywords:  relationship inherited
Progress State:  needs questions     |  inheritance foreign key
  answered                           |
-------------------------------------+-------------------------------------
Changes (by zzzeek):

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


Comment:

 okey doke, reopen when you have time to post a test case, thanks !

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

Re: [Sqlalchemy-tickets] [sqlalchemy] #2720: new C extensions for attribute accessing

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

#2720: new C extensions for attribute accessing
-----------------------------------+---------------------------------------
      Reporter:  zzzeek            |      Owner:  zzzeek
          Type:  enhancement       |     Status:  new
      Priority:  medium            |  Milestone:  0.9.0
     Component:  cextensions       |   Severity:  very major - up to 2 days
    Resolution:                    |   Keywords:
Progress State:  not decided upon  |
-----------------------------------+---------------------------------------

Comment (by claudiofreire):

 BTW: Hold on a bit if 426 lines of C look intimidating (I'd think so),
 since I still have to try Pyrex. I've got to check whether Pyrex can
 replace the other extensions too, for it would be pointless to just
 introduce it for this one and not the others.

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

Re: [Sqlalchemy-tickets] [sqlalchemy] #2720: new C extensions for attribute accessing

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

#2720: new C extensions for attribute accessing
-----------------------------------+---------------------------------------
      Reporter:  zzzeek            |      Owner:  zzzeek
          Type:  enhancement       |     Status:  new
      Priority:  medium            |  Milestone:  0.9.0
     Component:  cextensions       |   Severity:  very major - up to 2 days
    Resolution:                    |   Keywords:
Progress State:  not decided upon  |
-----------------------------------+---------------------------------------

Comment (by claudiofreire):

 Right. Well, {{{key in dict_}}} then {{{return dict_[key]}}} does the
 lookup twice, and catching the !KeyError has to set up a try/catch block,
 which is what is expensive about it (kinda like a stack bookmark CPython's
 VM can roll back to).

 But the real savings here come from:

  * {{{Entity.id}}} goes from CPython's VM (LOAD_ATTR) directly towards the
 C code, there's no frame setup or bytecodes involved (that's why the call
 doesn't show up in profiles). With these ultra-cheap functions, frame
 setup can be 90% of the cost.
  * {{{PyBlah_Bleh}}} calls are all native. While, true, not immensely
 quicker than equivalent VM bytecodes, the fact that they're just working
 with simple dict ops and non-property attributes makes the VM itself a
 hinderance. {{{Entity.id}}} was about a dozen opcodes, that's a dozen
 loops over a huge switch block that interprets the bytecode. Now it's a
 pair of native calls. That's '''way''' faster in comparison.

 While those two wouldn't normally matter much in the overall picture, the
 fact that properly optimized CPU-bound SQLAlchemy apps tend to be all
 about reading attributes (in my experience), changes things.

 I didn't try the timeit. I will as soon as I get back to my SA devel
 machine (ie: the office).

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

Re: [Sqlalchemy-tickets] [sqlalchemy] #2720: new C extensions for attribute accessing

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

#2720: new C extensions for attribute accessing
-----------------------------------+---------------------------------------
      Reporter:  zzzeek            |      Owner:  zzzeek
          Type:  enhancement       |     Status:  new
      Priority:  medium            |  Milestone:  0.9.0
     Component:  cextensions       |   Severity:  very major - up to 2 days
    Resolution:                    |   Keywords:
Progress State:  not decided upon  |
-----------------------------------+---------------------------------------

Comment (by zzzeek):

 so the speed savings are:

 1. the comparison of `self->cached_instance_dict == instance_dict ` and
 such are straight C references, so this is extremely fast, then

 2. `rv = PyObject_GetItem(basic_dict, key);`  allows us to quickly get a
 value or NULL without the overhead of either "key in dict" or catching an
 expensive `KeyError`

 is that right?

 also did you run any regular timeit's?   I'm assuming we'd see similar
 percentages.

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

Re: [Sqlalchemy-tickets] [sqlalchemy] #2721: SQLAlchemy leaks the deferrable keyword in FKs for mysql databases

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

#2721: SQLAlchemy leaks the deferrable keyword in FKs for mysql databases
---------------------------+----------------------------------
      Reporter:  kashif    |      Owner:
          Type:  defect    |     Status:  new
      Priority:  medium    |  Milestone:  0.8.xx
     Component:  mysql     |   Severity:  minor - half an hour
    Resolution:            |   Keywords:  mysql deferrable
Progress State:  in queue  |
---------------------------+----------------------------------

Comment (by kashif):

 Thanks :-)

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

Re: [Sqlalchemy-tickets] [sqlalchemy] #2720: new C extensions for attribute accessing

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

#2720: new C extensions for attribute accessing
-----------------------------------+---------------------------------------
      Reporter:  zzzeek            |      Owner:  zzzeek
          Type:  enhancement       |     Status:  new
      Priority:  medium            |  Milestone:  0.9.0
     Component:  cextensions       |   Severity:  very major - up to 2 days
    Resolution:                    |   Keywords:
Progress State:  not decided upon  |
-----------------------------------+---------------------------------------

Comment (by claudiofreire):

 No, it's not that at all. What it's doing, is try to get the __dict__, for
 comparison against the instance_dict() return value. Caching (which is
 what accelerates things) is only enabled when:

 {{{instance_dict(instance) is instance.__dict__}}}

 and

 {{{self.impl.get(instance_state(instance), dict_)}}} is successful (ie: no
 errors).

 If both happen, then from that point onwards, all attribute accesses of
 that kind (ie: {{{Entity.id}}} for all instances of Entity) will shortcut
 directly towards {{{instance.__dict__[self.key]}}}, as long as
 {{{globals()['instance_dict']}}} hasn't changed.

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

Re: [Sqlalchemy-tickets] [sqlalchemy] #2721: SQLAlchemy leaks the deferrable keyword in FKs for mysql databases

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

#2721: SQLAlchemy leaks the deferrable keyword in FKs for mysql databases
---------------------------+----------------------------------
      Reporter:  kashif    |      Owner:
          Type:  defect    |     Status:  new
      Priority:  medium    |  Milestone:  0.8.xx
     Component:  mysql     |   Severity:  minor - half an hour
    Resolution:            |   Keywords:  mysql deferrable
Progress State:  in queue  |
---------------------------+----------------------------------
Changes (by zzzeek):

 * priority:  high => medium


Comment:

 oh also here's a workaround for you, just override
 visit_foreign_key_constraint, should work in 0.7 too:

 {{{
 from sqlalchemy.schema import ForeignKeyConstraint
 from sqlalchemy.ext.compiler import compiles

 @compiles(ForeignKeyConstraint, "mysql")
 def visit_foreign_key_constraint(element, compiler, **kw):
     deferrable = getattr(element, "deferrable", None)
     if deferrable is True:
         element.deferrable = None
     text = compiler.visit_foreign_key_constraint(element, **kw)
     if deferrable is True:
         element.deferrable = True
     return text

 if __name__ == '__main__':
     from sqlalchemy import MetaData, Column, Table, create_engine,
 ForeignKey, Integer

     m = MetaData()

     t1 = Table('t1', m, Column('id', Integer, primary_key=True))
     t2 = Table('t2', m, Column('id', Integer, ForeignKey('t2.id',
 deferrable=True), primary_key=True))

     e1 = create_engine("postgresql://scott:tiger@localhost/test",
 echo=True)
     e2 = create_engine("mysql://scott:tiger@localhost/test", echo=True)

     m.create_all(e1)
     m.create_all(e2)

     m.drop_all(e1)
     m.drop_all(e2)


 }}}

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

Re: [Sqlalchemy-tickets] [sqlalchemy] #2721: SQLAlchemy leaks the deferrable keyword in FKs for mysql databases

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

#2721: SQLAlchemy leaks the deferrable keyword in FKs for mysql databases
---------------------------+----------------------------------
      Reporter:  kashif    |      Owner:
          Type:  defect    |     Status:  new
      Priority:  high      |  Milestone:  0.8.xx
     Component:  mysql     |   Severity:  minor - half an hour
    Resolution:            |   Keywords:  mysql deferrable
Progress State:  in queue  |
---------------------------+----------------------------------

Comment (by kashif):

 Yes, if no deferrable or deferred words are present in the CREATE TABLE
 statement for mysql then it would start working fine again.

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

Re: [Sqlalchemy-tickets] [sqlalchemy] #2721: SQLAlchemy leaks the deferrable keyword in FKs for mysql databases

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

#2721: SQLAlchemy leaks the deferrable keyword in FKs for mysql databases
---------------------------+----------------------------------
      Reporter:  kashif    |      Owner:
          Type:  defect    |     Status:  new
      Priority:  high      |  Milestone:  0.8.xx
     Component:  mysql     |   Severity:  minor - half an hour
    Resolution:            |   Keywords:  mysql deferrable
Progress State:  in queue  |
---------------------------+----------------------------------
Changes (by zzzeek):

 * status_field:  needs questions answered => in queue


Comment:

 OK so you are still getting a rough equivalent of "deferrable" with MySQL
 here, which was what I was curious about.  Since MySQL's foreign keys are
 kind of fungible I can see how its fine to skip the keyword on this
 platform.

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

Re: [Sqlalchemy-tickets] [sqlalchemy] #2720: new C extensions for attribute accessing

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

#2720: new C extensions for attribute accessing
-----------------------------------+---------------------------------------
      Reporter:  zzzeek            |      Owner:  zzzeek
          Type:  enhancement       |     Status:  new
      Priority:  medium            |  Milestone:  0.9.0
     Component:  cextensions       |   Severity:  very major - up to 2 days
    Resolution:                    |   Keywords:
Progress State:  not decided upon  |
-----------------------------------+---------------------------------------

Comment (by zzzeek):

 OK...can you tell me about this:

 {{{
   basic_dict = PyObject_GetAttrString(instance, "__dict__");
         142         if (basic_dict == NULL) {
         143             /* No problem, we'll fall back to the generic
 implementation anyway */
         144             PyErr_Clear();
         145         }
 }}}

 what it seems like we're doing here, is seeing if `__dict__` is there, and
 if so, then great we can skip using `instance_dict()`.   That's not
 actually how `instance_dict()` is supposed to work - it's supposed to be
 consulted unconditionally.   The whole `instance_dict()` thing is really
 unfortunate in that I think there is exactly one application on the planet
 that actually needs it to access something other than `__dict__`, but the
 fact is that in the default case it is actually an `attrgetter` for
 `__dict__` and should be extremely fast.   Am I correct about that step
 and can that be removed, hopefully simplifying the code ?    Overall it's
 a little weird that we're replacing literally six lines of Python code
 with 426 lines of C.

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

Re: [Sqlalchemy-tickets] [sqlalchemy] #2722: clarify what should happen with query(A).select_from(A).join(B.a)

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

#2722: clarify what should happen with query(A).select_from(A).join(B.a)
--------------------------------+-------------------------------
      Reporter:  rbu            |      Owner:  zzzeek
          Type:  defect         |     Status:  new
      Priority:  medium         |  Milestone:  0.8.xx
     Component:  documentation  |   Severity:  major - 1-3 hours
    Resolution:                 |   Keywords:
Progress State:  in queue       |
--------------------------------+-------------------------------

Comment (by zzzeek):

 yeah I've looked into seeing how an expression like:

 {{{
 session.query(A).select_from(A).join(B.a)
 }}}

 could establish the join as "A to B", but this would be an awkward
 exception case added to what is otherwise becoming a fairly consistent
 system.  Using the relationship for the join, "B.a", means "join from B to
 A", and that takes precedence here.   This works well because "B.a"
 contains both the two endpoints, the ON clause, and the direction, i.e.
 which side is left and which is right.   If Query were to second-guess
 this, and look at "select from", that becomes more complicated, in the
 case where we are already "selecting from" many things, such as
 `query(A).select_from(A).join(A.cs).join(C.bs)` etc. - it means we have to
 look at each of "A", "C", and "B" and make a guess which is the best "left
 side" to use.

 I do sometimes have the case where I don't have a backref set up and would
 like to use the reverse side, so I'm wondering if a new symbol like
 `session.query(A).join(B.a.reversed)` might be possible.

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