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

Re: [Sqlalchemy-tickets] [sqlalchemy] #2824: Add ability to query column sets as one entity

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

#2824: Add ability to query column sets as one entity
-----------------------------------+---------------------------------------
      Reporter:  vmagamedov        |      Owner:  zzzeek
          Type:  enhancement       |     Status:  new
      Priority:  medium            |  Milestone:  0.x.xx
     Component:  orm               |   Severity:  very major - up to 2 days
    Resolution:                    |   Keywords:
Progress State:  not decided upon  |
-----------------------------------+---------------------------------------

Comment (by vmagamedov):

 It works!:) Thank you, this is much better, now {{{Query}}} subclass (or
 mixin) will be simpler.

 > Another way to go here would be just to implement your own _QueryEntity
 subclass. At the moment I'd not encourage this use as the _QueryEntity
 hasn't been tested and fleshed out for end-user production (it's one thing
 to make a base class, another to make one that users can subclass). But
 that could be the expansion point too perhaps.

 I'm understand that my case isn't broad enough, so you can close this
 ticket if you wish or leave it open to publish later more expansion points
 to support custom entity wrappers (as you said above) when their
 implementation would be stabilised/tested.

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

Re: [Sqlalchemy-tickets] [sqlalchemy] #2828: emit warning when Column is assigned directly to multiple names (was: column_property hides original column)

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

#2828: emit warning when Column is assigned directly to multiple names
------------------------------+-------------------------------
      Reporter:  jerryji      |      Owner:  zzzeek
          Type:  defect       |     Status:  new
      Priority:  medium       |  Milestone:  0.8.xx
     Component:  declarative  |   Severity:  major - 1-3 hours
    Resolution:               |   Keywords:  column_property
Progress State:  needs tests  |
------------------------------+-------------------------------
Changes (by zzzeek):

 * milestone:   => 0.8.xx
 * component:  orm => declarative
 * severity:  no triage selected yet => major - 1-3 hours
 * status_field:  awaiting triage => needs tests


Comment:

 there's no bug here, the declaration of `name = Column` is equivalent of
 `name_alias = column_property(Column)`, and an explicit column property
 trumps the plain column as you might be passing extra mapping options
 along.   We can emit a warning for when this occurs, this patch is a
 start, needs some cleanup and tests:

 {{{
 #!python
 diff --git a/lib/sqlalchemy/ext/declarative/base.py
 b/lib/sqlalchemy/ext/declarative/base.py
 index 820c087..73723bc 100644
 --- a/lib/sqlalchemy/ext/declarative/base.py
 +++ b/lib/sqlalchemy/ext/declarative/base.py
 @@ -14,7 +14,7 @@ from ... import util, exc
  from ...sql import expression
  from ... import event
  from . import clsregistry
 -
 +import collections

  def _declared_mapping_info(cls):
      # deferred mapping
 @@ -173,15 +173,19 @@ def _as_declarative(cls, classname, dict_):

      # extract columns from the class dict
      declared_columns = set()
 +    col_to_prop = collections.defaultdict(dict)
      for key, c in list(our_stuff.items()):
          if isinstance(c, (ColumnProperty, CompositeProperty)):
              for col in c.columns:
                  if isinstance(col, Column) and \
                      col.table is None:
                      _undefer_column_name(key, col)
 +                    if isinstance(c, ColumnProperty):
 +                        col_to_prop[col][key] = c
                      declared_columns.add(col)
          elif isinstance(c, Column):
              _undefer_column_name(key, c)
 +            col_to_prop[c][key] = c
              declared_columns.add(c)
              # if the column is the same name as the key,
              # remove it from the explicit properties dict.
 @@ -190,6 +194,10 @@ def _as_declarative(cls, classname, dict_):
              # in multi-column ColumnProperties.
              if key == c.key:
                  del our_stuff[key]
 +    for col, entry in col_to_prop.items():
 +        if len(entry) > 1:
 +            util.warn("Column object named directly multiple times, "
 +                "only one will be used: %s" % (", ".join(entry)))
      declared_columns = sorted(
          declared_columns, key=lambda c: c._creation_order)
      table = None
 diff --git a/lib/sqlalchemy/orm/mapper.py b/lib/sqlalchemy/orm/mapper.py
 index 4336c19..f828180 100644
 --- a/lib/sqlalchemy/orm/mapper.py
 +++ b/lib/sqlalchemy/orm/mapper.py
 @@ -1218,7 +1218,6 @@ class Mapper(_InspectionAttr):
              self._log("Identified primary key columns: %s", primary_key)

      def _configure_properties(self):
 -
          # Column and other ClauseElement objects which are mapped
          self.columns = self.c = util.OrderedProperties()

 }}}

 this also should probably try to detect if a straight Column is specified
 multiple times.

 in this use case you want to be using
 [http://docs.sqlalchemy.org/en/rel_0_8/orm/mapper_config.html?highlight=synonym#sqlalchemy.orm.synonym
 synonym] in order to produce a straight "alias" of a name.

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

[Sqlalchemy-tickets] [sqlalchemy] #2828: column_property hides original column

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

#2828: column_property hides original column
-----------------------------+-----------------------------------------
 Reporter:  jerryji          |           Owner:  zzzeek
     Type:  defect           |          Status:  new
 Priority:  medium           |       Milestone:
Component:  orm              |        Severity:  no triage selected yet
 Keywords:  column_property  |  Progress State:  awaiting triage
-----------------------------+-----------------------------------------
 column_property hides original column from __mapper__ when used with no
 change.

 Results from running the attached column_property.py, tested against
 sqlalchemy git clone just now --

 {{{
 (sqlalchemy) C:\sqlalchemy>ipython
 Python 2.7.5 (default, May 15 2013, 22:44:16) [MSC v.1500 64 bit (AMD64)]

 In [1]: import sqlalchemy
 In [2]: sqlalchemy.__version__
 Out[2]: '0.9.0'

 In [3]: %run column_property.py
 ['name_alias', 'id']
 ['name_alias', 'id', 'name']
 }}}

 column_property.py (in case attachment fails), maybe not the right way to
 create an aliasing column, but still looks like a bug to me --

 {{{
 from sqlalchemy import (
     create_engine,
     Column,
     Text,
     Integer,
 )
 from sqlalchemy.orm import (
     scoped_session,
     sessionmaker,
     column_property,
 )
 from sqlalchemy.ext.declarative import declarative_base

 Base = declarative_base()

 class NameMissing(Base):
     __tablename__ = 'name_missing'
     id = Column(Integer, primary_key=True)
     name = Column(Text)
     name_alias = column_property(name)

     def __init__(self, id, name):
         self.id = id
         self.name = name

 class NameOK(Base):
     __tablename__ = 'name_ok'
     id = Column(Integer, primary_key=True)
     name = Column(Text)
     name_alias = column_property(name+'')

     def __init__(self, id, name):
         self.id = id
         self.name = name

 engine = create_engine('sqlite://')
 Base.metadata.create_all(engine)
 dbsession = scoped_session(sessionmaker(bind=engine))
 new_name_missing = NameMissing(1, 'foobar')
 dbsession.add(new_name_missing)
 new_name_ok = NameOK(1, 'foobar')
 dbsession.add(new_name_ok)
 dbsession.commit()

 name_missing = dbsession.query(NameMissing).first()
 print name_missing.__mapper__.columns.keys()

 name_ok = dbsession.query(NameOK).first()
 print name_ok.__mapper__.columns.keys()
 }}}

 Jerry

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

Re: [Sqlalchemy-tickets] [sqlalchemy] #2827: Typo in documentation

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

#2827: Typo in documentation
-----------------------------------+-----------------------------------
      Reporter:  andrewkittredge   |      Owner:  zzzeek
          Type:  defect            |     Status:  closed
      Priority:  low               |  Milestone:  0.8.xx
     Component:  documentation     |   Severity:  trivial - <10 minutes
    Resolution:  fixed             |   Keywords:
Progress State:  completed/closed  |
-----------------------------------+-----------------------------------
Changes (by zzzeek):

 * status:  new => closed
 * resolution:   => fixed
 * severity:  no triage selected yet => trivial - <10 minutes
 * status_field:  awaiting triage => completed/closed


Comment:

 r3e947c2c06abbcf4ea8df63f79e1efcfc367bd61

 rc3c4b2d23dacc9e7e3b772c

 thank you

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

Re: [Sqlalchemy-tickets] [sqlalchemy] #2824: Add ability to query column sets as one entity

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

#2824: Add ability to query column sets as one entity
-----------------------------------+---------------------------------------
      Reporter:  vmagamedov        |      Owner:  zzzeek
          Type:  enhancement       |     Status:  new
      Priority:  medium            |  Milestone:  0.x.xx
     Component:  orm               |   Severity:  very major - up to 2 days
    Resolution:                    |   Keywords:
Progress State:  not decided upon  |
-----------------------------------+---------------------------------------
Changes (by zzzeek):

 * milestone:  0.9.xx => 0.x.xx


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

Re: [Sqlalchemy-tickets] [sqlalchemy] #2824: Add ability to query column sets as one entity

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

#2824: Add ability to query column sets as one entity
-----------------------------------+---------------------------------------
      Reporter:  vmagamedov        |      Owner:  zzzeek
          Type:  enhancement       |     Status:  new
      Priority:  medium            |  Milestone:  0.9.xx
     Component:  orm               |   Severity:  very major - up to 2 days
    Resolution:                    |   Keywords:
Progress State:  not decided upon  |
-----------------------------------+---------------------------------------
Changes (by zzzeek):

 * severity:  no triage selected yet => very major - up to 2 days


Comment:

 this is a pretty lame proof of concept but im not very comfortable with
 it, as it only would address your immediate concern which seems to be not
 a broad enough case:

 {{{
 #!diff
 diff --git a/lib/sqlalchemy/orm/loading.py b/lib/sqlalchemy/orm/loading.py
 index 512a07d..5dc1184 100644
 --- a/lib/sqlalchemy/orm/loading.py
 +++ b/lib/sqlalchemy/orm/loading.py
 @@ -42,8 +42,8 @@ def instances(query, cursor, context):
              def filter_fn(row):
                  return tuple(fn(x) for x, fn in zip(row, filter_fns))

 -    custom_rows = single_entity and \
 -                    query._entities[0].mapper.dispatch.append_result
 +    custom_rows = False #single_entity and \
 +                    #query._entities[0].mapper.dispatch.append_result

      (process, labels) = \
                  list(zip(*[
 diff --git a/lib/sqlalchemy/orm/query.py b/lib/sqlalchemy/orm/query.py
 index d64575a..01a7238 100644
 --- a/lib/sqlalchemy/orm/query.py
 +++ b/lib/sqlalchemy/orm/query.py
 @@ -2890,6 +2890,8 @@ class _QueryEntity(object):
              if not isinstance(entity, util.string_types) and \
                          _is_mapped_class(entity):
                  cls = _MapperEntity
 +            elif isinstance(entity, UserDefinedEntity):
 +                cls = _UserDefinedEntity
              else:
                  cls = _ColumnEntity
          return object.__new__(cls)
 @@ -2900,6 +2902,35 @@ class _QueryEntity(object):
          return q


 +class UserDefinedEntity(object):
 +    def setup_columns(self, column_collection, context):
 +        pass
 +
 +    def process_rows(self, context):
 +        pass
 +
 +
 +class _UserDefinedEntity(_QueryEntity):
 +    """User defined entity!"""
 +
 +    filter_fn = id
 +
 +    entities = ()
 +
 +    def __init__(self, query, entity):
 +        query._entities.append(self)
 +        self.entity = entity
 +
 +    def setup_entity(self, ext_info, aliased_adapter):
 +        raise NotImplementedError()
 +
 +    def setup_context(self, query, context):
 +        self.entity.setup_columns(context.primary_columns, context)
 +
 +    def row_processor(self, query, context, custom_rows):
 +        return self.entity.process_rows(context)
 +
 +
  class _MapperEntity(_QueryEntity):
      """mapper/class/AliasedClass entity"""

 }}}

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

Re: [Sqlalchemy-tickets] [sqlalchemy] #2823: Wrong sqlalchemy.sql.expression.false() / true() compilation when using SQL Server dialect

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

#2823: Wrong sqlalchemy.sql.expression.false() / true() compilation when using SQL
Server dialect
-------------------------------------------+-------------------------------
      Reporter:  leavittx                  |      Owner:
          Type:  defect                    |     Status:  new
      Priority:  medium                    |  Milestone:
     Component:  mssql                     |   Severity:  no triage
    Resolution:                            |  selected yet
Progress State:  needs questions answered  |   Keywords:
-------------------------------------------+-------------------------------

Comment (by leavittx):

 nice, looks like it works just fine! I will send pull request with your
 enhancement to orm_tree repository on github soon

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

[Sqlalchemy-tickets] [sqlalchemy] #2827: Typo in documentation

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

#2827: Typo in documentation
-----------------------------+-----------------------------------------
 Reporter:  andrewkittredge  |           Owner:  zzzeek
     Type:  defect           |          Status:  new
 Priority:  low              |       Milestone:  0.8.xx
Component:  documentation    |        Severity:  no triage selected yet
 Keywords:                   |  Progress State:  awaiting triage
-----------------------------+-----------------------------------------
 http://docs.sqlalchemy.org/en/rel_0_8/orm/session.html#session-faq-
 whentocreate

 class ThingTwo(object):
     def go(self):
         session.query(Widget).update({'q' : 18})

 session is not in scope I think it should be
 class ThingTwo(object):
     def go(self, session):
         ...

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

[Sqlalchemy-tickets] [sqlalchemy] #2826: New recipe -- solution to select through large number of rows for SQLITE

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

#2826: New recipe -- solution to select through large number of rows for SQLITE
-------------------------+-----------------------------------------
 Reporter:  jamercee     |           Owner:  zzzeek
     Type:  enhancement  |          Status:  new
 Priority:  low          |       Milestone:  0.8.xx
Component:  sqlite       |        Severity:  no triage selected yet
 Keywords:               |  Progress State:  awaiting triage
-------------------------+-----------------------------------------
 Sqlite does not support the window/range function described in this recipe

 [http://www.sqlalchemy.org/trac/wiki/UsageRecipes/WindowedRangeQuery]

 We have developed a solution along the same lines using LIMIT/OFFSET, and
 wanted to share this back with the community.

 {{{#!python
 def _yield_limit(qry, pk, maxrq=1000):
     r"""specialized windowed query generator (using LIMIT/OFFSET)

     This recipe is to select through a large number of rows thats too
     large to fetch at once. The technique depends on the primary key
     of the FROM clause being an integer value, and selects items
     surrounded with LIMIT/OFFSET"""

     key = pk.property.key
     nextid = qry.session.query(pk).order_by(key).\
                 limit(1).scalar()

     if nextid is None:
         return

     while 1:
         count = 0
         for rec in qry.filter(pk >= nextid).limit(maxrq):
             # Retrieve the key value before yielding as the
             # caller could delete the object.
             nextid = rec.__getattribute__(key) + 1
             count += 1
             yield rec

         # If we received less than the number of items we requested
         # we have reached the end.

         if count != maxrq:
             return

 }}}

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

[Sqlalchemy-tickets] [sqlalchemy] #2825: Index.unique should be only True or False, index=True on Column breaking this

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

#2825: Index.unique should be only True or False, index=True on Column breaking
this
--------------------+---------------------------------------
 Reporter:  zzzeek  |           Owner:  zzzeek
     Type:  defect  |          Status:  new
 Priority:  high    |       Milestone:  0.8.xx
Component:  schema  |        Severity:  minor - half an hour
 Keywords:          |  Progress State:  needs tests
--------------------+---------------------------------------
 {{{
 #!python
 >>> from sqlalchemy import Table, MetaData, Column, Integer
 >>> t1 = Table('t', MetaData(), Column('x', Integer, index=True))
 >>> list(t1.indexes)[0].unique
 False
 }}}

 {{{
 #!diff
 --- a/lib/sqlalchemy/sql/schema.py
 +++ b/lib/sqlalchemy/sql/schema.py
 @@ -1123,7 +1123,7 @@ class Column(SchemaItem, ColumnClause):
                      "To create indexes with a specific name, create an "
                      "explicit Index object external to the Table.")
              Index(_truncated_label('ix_%s' % self._label),
 -                                    self, unique=self.unique)
 +                                    self, unique=self.unique if
 self.unique is not None else False)
          elif self.unique:
              if isinstance(self.unique, util.string_types):
                  raise exc.ArgumentError(
 }}}

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

Re: [Sqlalchemy-tickets] [sqlalchemy] #2823: Wrong sqlalchemy.sql.expression.false() / true() compilation when using SQL Server dialect

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

#2823: Wrong sqlalchemy.sql.expression.false() / true() compilation when using SQL
Server dialect
-------------------------------------------+-------------------------------
      Reporter:  leavittx                  |      Owner:
          Type:  defect                    |     Status:  new
      Priority:  medium                    |  Milestone:
     Component:  mssql                     |   Severity:  no triage
    Resolution:                            |  selected yet
Progress State:  needs questions answered  |   Keywords:
-------------------------------------------+-------------------------------

Comment (by zzzeek):

 OK, I may be reading this wrong but isn't that much more simply (and
 efficiently, no nesting) just `or_(*[_filter_children_of_node_helper(arg)
 for arg in args])` ?   it seems like it currently is spitting out a
 recursive `or_(or_(or_ ...` structure which will cause a recursion
 overflow if you go too far.

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

Re: [Sqlalchemy-tickets] [sqlalchemy] #2823: Wrong sqlalchemy.sql.expression.false() / true() compilation when using SQL Server dialect

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

#2823: Wrong sqlalchemy.sql.expression.false() / true() compilation when using SQL
Server dialect
-------------------------------------------+-------------------------------
      Reporter:  leavittx                  |      Owner:
          Type:  defect                    |     Status:  new
      Priority:  medium                    |  Milestone:
     Component:  mssql                     |   Severity:  no triage
    Resolution:                            |  selected yet
Progress State:  needs questions answered  |   Keywords:
-------------------------------------------+-------------------------------

Comment (by leavittx):

 my deeper research on why sqlalchemy-orm-tree generates this gives the
 following line of code
 https://github.com/monetizeio/sqlalchemy-orm-
 tree/blob/master/sqlalchemy_tree/manager/class_.py#L458

 but i also want to know is there a workaround to make things work even
 with hacks (without modifying sqlalchemy-orm-tree's code, which is not so
 trivial) ?

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

Re: [Sqlalchemy-tickets] [sqlalchemy] #2823: Wrong sqlalchemy.sql.expression.false() / true() compilation when using SQL Server dialect

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

#2823: Wrong sqlalchemy.sql.expression.false() / true() compilation when using SQL
Server dialect
-------------------------------------------+-------------------------------
      Reporter:  leavittx                  |      Owner:
          Type:  defect                    |     Status:  new
      Priority:  medium                    |  Milestone:
     Component:  mssql                     |   Severity:  no triage
    Resolution:                            |  selected yet
Progress State:  needs questions answered  |   Keywords:
-------------------------------------------+-------------------------------

Comment (by zzzeek):

 unacceptable!   OK well I'd like to see exactly why sqlalchemy-orm-tree
 generates this.    I'm not sure how comfortable I am doing the collapsing.
 If someone says and_(x=='foo', y=='bar', false(), g==7), it should
 become....what exactly if not the "1=0" thing?   Overall I think that an
 app which is trying to be database agnostic should not be using the
 true()/false() constants at all.  I'm pretty sure SQLAlchemy Core or ORM
 doesn't spit these out anywhere, someone has to use them explicitly.

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

Re: [Sqlalchemy-tickets] [sqlalchemy] #2823: Wrong sqlalchemy.sql.expression.false() / true() compilation when using SQL Server dialect

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

#2823: Wrong sqlalchemy.sql.expression.false() / true() compilation when using SQL
Server dialect
-------------------------------------------+-------------------------------
      Reporter:  leavittx                  |      Owner:
          Type:  defect                    |     Status:  new
      Priority:  medium                    |  Milestone:
     Component:  mssql                     |   Severity:  no triage
    Resolution:                            |  selected yet
Progress State:  needs questions answered  |   Keywords:
-------------------------------------------+-------------------------------

Comment (by leavittx):

 I'd not use such expressions at all, but that's the filter code which
 sqlalchemy-orm-tree extension produces (https://github.com/monetizeio
 /sqlalchemy-orm-tree). So I see two possible solutions for the problem
 with true()/false() in isolation of a comparison. The first one is to
 generate hacky constructions like you said (I don't think they will slow
 the things so much). The second one is to do expression analysys &
 simplifying in some cases (i.e.  'false OR someexpr' becomes 'someexpr',
 'true AND someexpr' becomes 'someexpr' too, 'true OR someexpr' becomes
 '1=1 or someexpr', 'false AND someexpr' becomes '1=0'). I think the
 current behavior (generation of invalid sql code for sql server) is
 unacceptable!

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

Re: [Sqlalchemy-tickets] [sqlalchemy] #2824: Add ability to query column sets as one entity

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

#2824: Add ability to query column sets as one entity
-----------------------------------+------------------------------------
      Reporter:  vmagamedov        |      Owner:  zzzeek
          Type:  enhancement       |     Status:  new
      Priority:  medium            |  Milestone:  0.9.xx
     Component:  orm               |   Severity:  no triage selected yet
    Resolution:                    |   Keywords:
Progress State:  not decided upon  |
-----------------------------------+------------------------------------

Comment (by vmagamedov):

 > you're overriding twoHHH four methods, that's not much "quirk".

 I'm overriding non-public method in the {{{Query}}} class and reading non-
 public attribute of the {{{NamedTuple}}} class.

 It would be great if I could provide custom {{{_QueryEntity}}} subclass
 with simple API to somehow wrap columns and process them as one thing.
 Currently {{{_MapperEntity}}} and {{{_ColumnEntity}}} looks very complex
 for me, so I even didn't tried to write custom wrapper.

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

Re: [Sqlalchemy-tickets] [sqlalchemy] #2824: Add ability to query column sets as one entity

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

#2824: Add ability to query column sets as one entity
-----------------------------------+------------------------------------
      Reporter:  vmagamedov        |      Owner:  zzzeek
          Type:  enhancement       |     Status:  new
      Priority:  medium            |  Milestone:  0.9.xx
     Component:  orm               |   Severity:  no triage selected yet
    Resolution:                    |   Keywords:
Progress State:  not decided upon  |
-----------------------------------+------------------------------------

Comment (by zzzeek):

 Another way to go here would be just to implement your own `_QueryEntity`
 subclass.   At the moment I'd not encourage this use as the `_QueryEntity`
 hasn't been tested and fleshed out for end-user production (it's one thing
 to make a base class, another to make one that users can subclass).   But
 that could be the expansion point too perhaps.

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

Re: [Sqlalchemy-tickets] [sqlalchemy] #2824: Add ability to query column sets as one entity

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

#2824: Add ability to query column sets as one entity
-----------------------------------+------------------------------------
      Reporter:  vmagamedov        |      Owner:  zzzeek
          Type:  enhancement       |     Status:  new
      Priority:  medium            |  Milestone:  0.9.xx
     Component:  orm               |   Severity:  no triage selected yet
    Resolution:                    |   Keywords:
Progress State:  not decided upon  |
-----------------------------------+------------------------------------

Comment (by zzzeek):

 you're overriding two methods, that's not much "quirk".

 the feature that you're doing is interesting but not something I have any
 notion of an API that would do exactly that, it's better that there are
 enough expansion points that it can be implemented via custom code as
 you're doing.   Wrapping columns in an ad-hoc python function on return,
 that could be useful, that would be more of a Core feature and perhaps
 something that builds on `type_coerce()`.   I can see helpers here, and
 maybe a "filter" extension.   What "quirks" are bothering you exactly?

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

Re: [Sqlalchemy-tickets] [sqlalchemy] #2824: Add ability to query column sets as one entity

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

#2824: Add ability to query column sets as one entity
-----------------------------------+------------------------------------
      Reporter:  vmagamedov        |      Owner:  zzzeek
          Type:  enhancement       |     Status:  new
      Priority:  medium            |  Milestone:  0.9.xx
     Component:  orm               |   Severity:  no triage selected yet
    Resolution:                    |   Keywords:
Progress State:  not decided upon  |
-----------------------------------+------------------------------------

Comment (by vmagamedov):

 This is what I'm doing right now:) I've just implemented this in more
 general way (not only for the case when we query single entity, for
 example {{{session.query(Product.id, product_struct, ...)}}}).

 Here is my implementation:
 {{{#!python
 def _obj_getter(struct, i, count):
     return lambda row: struct.from_row(row[i:i+count])


 def _label_getter(i):
     return lambda row: row._labels[i]


 _none_getter = lambda row: None


 def _expand_entities(entity):
     return entity.columns if isinstance(entity, Construct) else (entity,)


 class Query(orm.Query):

     def _add_value_extractors(self, start_from, entities):
         i = start_from
         for entity in entities:
             if isinstance(entity, Construct):
                 count = len(entity.columns)
                 self._value_extractors.append(_obj_getter(entity, i,
 count))
                 self._label_extractors.append(_none_getter)
                 i += count
             else:
                 self._value_extractors.append(itemgetter(i))
                 self._label_extractors.append(_label_getter(i))
                 i += 1

     def _set_entities(self, entities, entity_wrapper=None):
         self._value_extractors = []
         self._label_extractors = []
         expanded_entities = tuple(chain(*(imap(_expand_entities,
 entities))))
         super(Query, self)._set_entities(expanded_entities,
 entity_wrapper)
         self._add_value_extractors(0, entities)

     def add_struct(self, struct):
         query = super(Query, self).add_columns(*struct.columns)
         query._add_value_extractors(len(self._entities), [struct])
         return query

     def add_entity(self, entity, alias=None):
         query = super(Query, self).add_entity(entity, alias)
         query._add_value_extractors(len(self._entities), [entity])
         return query

     def add_columns(self, *columns):
         query = super(Query, self).add_columns(*columns)
         query._add_value_extractors(len(self._entities), columns)
         return query

     def instances(self, *args, **kwargs):
         contains_struct = _none_getter in self._label_extractors
         single_struct = contains_struct and len(self._value_extractors) ==
 1

         gen = super(Query, self).instances(*args, **kwargs)

         if single_struct:
             for row in gen:
                 yield self._value_extractors[0](row)
         elif contains_struct:
             for row in gen:
                 yield NamedTuple([ext(row) for ext in
 self._value_extractors],
                                  [ext(row) for ext in
 self._label_extractors])
         else:
             for row in gen:
                 yield row
 }}}

 But how I can convince someone else to use this approach if it requires
 these quirks? An ideal way is to be able to query such structures with
 original Query and Session, without extra setup. I hope this are not very
 selfish statements:)

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

Re: [Sqlalchemy-tickets] [sqlalchemy] #2824: Add ability to query column sets as one entity

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

#2824: Add ability to query column sets as one entity
-----------------------------------+------------------------------------
      Reporter:  vmagamedov        |      Owner:  zzzeek
          Type:  enhancement       |     Status:  new
      Priority:  medium            |  Milestone:  0.9.xx
     Component:  orm               |   Severity:  no triage selected yet
    Resolution:                    |   Keywords:
Progress State:  not decided upon  |
-----------------------------------+------------------------------------
Changes (by zzzeek):

 * status_field:  awaiting triage => not decided upon
 * milestone:   => 0.9.xx


Comment:

 this seems like just a simple filter on top of column-based results.  I'd
 think you could just hit Session.query_cls, and __iter__(), and be done
 with it, proof of concept:

 {{{
 #!python
 from sqlalchemy.orm import Query

 class Thing(Query):
     _special = False

     @classmethod
     def factory(cls, entities, session=None):
         if isinstance(entities[0], EntityThing):
             ent = entities[0]
             query = Thing(ent._expand(), session)
             query._special = ent
             return query
         else:
             return Query(entities, session)

     def __iter__(self):
         if self._special:
             return self._special._iter(Query.__iter__(self))
         else:
             return Query.__iter__(self)

 class EntityThing(object):
     def __init__(self, expr):
         self.expr = expr

     def _expand(self):
         return [
             e.label(key)
             for key, e in self.expr.items()
         ]

     def _iter(self, rows):
         for row in rows:
             yield self._process(row)

     def _process(self, row):
         return dict((key, col) for key, col in zip(self.expr, row))

 from sqlalchemy import *
 from sqlalchemy.orm import *
 from sqlalchemy.ext.declarative import declarative_base

 Base = declarative_base()

 class A(Base):
     __tablename__ = 'a'

     id = Column(Integer, primary_key=True)
     data = Column(String)

 e = create_engine("sqlite://", echo=True)
 Base.metadata.create_all(e)

 sess = Session(e, query_cls=Thing.factory)
 sess.add(A(data='a1'), A(data='a2'))

 et = EntityThing({"id": A.id, "data": A.data})
 print sess.query(et).all()
 }}}

 the only feature I can see here is maybe a more idiomatic way of filtering
 the output of a Query, we've just had people overriding `__iter__()` but
 we'd want to revisit several existing examples/recipes that feature this
 usage.

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

[Sqlalchemy-tickets] [sqlalchemy] #2824: Add ability to query column sets as one entity

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

#2824: Add ability to query column sets as one entity
-------------------------+-----------------------------------------
 Reporter:  vmagamedov   |           Owner:  zzzeek
     Type:  enhancement  |          Status:  new
 Priority:  medium       |       Milestone:
Component:  orm          |        Severity:  no triage selected yet
 Keywords:               |  Progress State:  awaiting triage
-------------------------+-----------------------------------------
 In our company we have a tool called {{{construct}}}, which is doing next
 things:

 1. You declare what you want to show in your template (we use Mako, but we
 want to make templates as dumb as possible):
 {{{#!python
 product_struct = Construct(dict(
     name=Product.name,
     url=apply_(
         get_product_url,
         args=[Product.id, Product.name],
     ),
     image_url=if_(
         Product.main_image_id,
         then_=apply_(get_image_url,
                      args=[Image.id, Image.file_name, Image.store_type,
                            100, 100]),
         else_=None,
     ),
 ))
 }}}

 2. You query this structure using {{{session.query}}}
 {{{#!python
 products = (
     db.session.query(product_struct)
     .outerjoin(Product.main_image)
     .limit(10)
     .all()
 )
 }}}

 3. And you get these results:
 {{{#!python
 [
     Object(name=u'Foo',
            url=u'/p1-foo.html',
            image_url=u'http://images.example.st/123-foo-100x100.jpg'),
     Object(name=u'Bar',
            url=u'/p2-bar.html',
            image_url=None),

     # ...

     Object(name=u'Baz',
            url=u'/p10-baz.html',
            image_url=u'http://images.example.st/789-baz-100x100.jpg'),
 ]
 }}}
 {{{Object}}} is a namedtuple-like data structure.

 At this time, construct requires custom {{{Query}}} subclass, which
 overrides {{{_set_entities}}} and {{{instances}}} methods (plus some other
 methods). I find this hacky and I can't find any other solution in how to
 make construct and {{{Query}}} work better together.

 I've tried to subclass {{{ClauseList}}} and {{{ColumnElement}}} classes,
 to provide necessary columns to query (this works), but I can't figure out
 how to gather these columns as one entity/structure in the resulting row.
 Looks like {{{_ColumnEntity}}} wrapper doesn't support this functionality
 or implementation would be also very hacky, {{{_MapperEntity}}} is tied to
 mapped classes and {{{Query}}} doesn't support any other option.

 Is it possible to support so weird option?

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

Re: [Sqlalchemy-tickets] [sqlalchemy] #2823: Wrong sqlalchemy.sql.expression.false() / true() compilation when using SQL Server dialect

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

#2823: Wrong sqlalchemy.sql.expression.false() / true() compilation when using SQL
Server dialect
-------------------------------------------+-------------------------------
      Reporter:  leavittx                  |      Owner:
          Type:  defect                    |     Status:  new
      Priority:  medium                    |  Milestone:
     Component:  mssql                     |   Severity:  no triage
    Resolution:                            |  selected yet
Progress State:  needs questions answered  |   Keywords:
-------------------------------------------+-------------------------------
Changes (by zzzeek):

 * priority:  high => medium
 * status_field:  awaiting triage => needs questions answered


Comment:

 SQL server doesn't have "true" or "false" constants so you can't refer to
 them in isolation of a comparison.    otherwise, what SQL would you like
 the expression `or_(true(), false())` to produce on SQL server ?   There
 are of course hacks like "1==1" "1==0" and stuff like that but we prefer
 to do as few of those as possible (the only one we do is the IN () hack,
 which already confuses everyone).

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

[Sqlalchemy-tickets] [sqlalchemy] #2823: Wrong sqlalchemy.sql.expression.false() / true() compilation when using SQL Server dialect

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

#2823: Wrong sqlalchemy.sql.expression.false() / true() compilation when using SQL
Server dialect
----------------------+-----------------------------------------
 Reporter:  leavittx  |           Owner:
     Type:  defect    |          Status:  new
 Priority:  high      |       Milestone:
Component:  mssql     |        Severity:  no triage selected yet
 Keywords:            |  Progress State:  awaiting triage
----------------------+-----------------------------------------
 Engine instance was created via
 {{{
 #!div style="font-size: 80%"
 Code highlighting:
   {{{#!python
   engine =
 create_engine('mssql+pyodbc://user:pa...@se.../db?driver=SQL Server',
 echo=True)
   }}}
 }}}


 When I do for example
 {{{
 #!div style="font-size: 80%"
 Code highlighting:
   {{{#!python
   session.query(User).filter(or_(expression.false(),
 expression.true())).all()
   }}}
 }}}

 Output:
 {{{
 2013-09-15 23:40:11,761 INFO sqlalchemy.engine.base.Engine SELECT
 [user].id AS user_id, [user].name AS user_name
 FROM [user]
 WHERE 0 OR 1
 2013-09-15 23:40:11,761 INFO sqlalchemy.engine.base.Engine ()
 Traceback (most recent call last):
   File "<console>", line 1, in <module>
   File "C:\Python33\lib\site-packages\sqlalchemy-0.9.0dev-py3.3-win-
 amd64.egg\sqlalchemy\orm\query.py", line 2249, in all
     return list(self)
   File "C:\Python33\lib\site-packages\sqlalchemy-0.9.0dev-py3.3-win-
 amd64.egg\sqlalchemy\orm\query.py", line 2361, in __iter__
     return self._execute_and_instances(context)
   File "C:\Python33\lib\site-packages\sqlalchemy-0.9.0dev-py3.3-win-
 amd64.egg\sqlalchemy\orm\query.py", line 2376, in _execute_and_instances
     result = conn.execute(querycontext.statement, self._params)
   File "C:\Python33\lib\site-packages\sqlalchemy-0.9.0dev-py3.3-win-
 amd64.egg\sqlalchemy\engine\base.py", line 661, in execute
     params)
   File "C:\Python33\lib\site-packages\sqlalchemy-0.9.0dev-py3.3-win-
 amd64.egg\sqlalchemy\engine\base.py", line 762, in _execute_clauseelement
     compiled_sql, distilled_params
   File "C:\Python33\lib\site-packages\sqlalchemy-0.9.0dev-py3.3-win-
 amd64.egg\sqlalchemy\engine\base.py", line 875, in _execute_context
     context)
   File "C:\Python33\lib\site-packages\sqlalchemy-0.9.0dev-py3.3-win-
 amd64.egg\sqlalchemy\engine\base.py", line 1019, in
 _handle_dbapi_exception
     exc_info
   File "C:\Python33\lib\site-packages\sqlalchemy-0.9.0dev-py3.3-win-
 amd64.egg\sqlalchemy\util\compat.py", line 197, in raise_from_cause
     reraise(type(exception), exception, tb=exc_tb, cause=exc_value)
   File "C:\Python33\lib\site-packages\sqlalchemy-0.9.0dev-py3.3-win-
 amd64.egg\sqlalchemy\util\compat.py", line 190, in reraise
     raise value.with_traceback(tb)
   File "C:\Python33\lib\site-packages\sqlalchemy-0.9.0dev-py3.3-win-
 amd64.egg\sqlalchemy\engine\base.py", line 868, in _execute_context
     context)
   File "C:\Python33\lib\site-packages\sqlalchemy-0.9.0dev-py3.3-win-
 amd64.egg\sqlalchemy\engine\default.py", line 372, in do_execute
     cursor.execute(statement, parameters)
 sqlalchemy.exc.ProgrammingError: (ProgrammingError) ('42000', "[42000]
 [Microsoft][ODBC SQL Server Driver][SQL Server]An expression of non-
 boolean type specified in a context where a condition is expected, near
 'OR'. (4145) (SQLExecDirectW)") 'SELECT [user].id AS user_id, [user].name
 AS user_name \nFROM [user] \nWHERE 0 OR 1' ()
 }}}

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

Re: [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:  closed
      Priority:  medium           |  Milestone:  0.7.xx
     Component:  mysql            |   Severity:  no triage selected yet
    Resolution:  worksforme       |   Keywords:  mysql session transcaction
Progress State:                   |
  completed/closed                |
----------------------------------+----------------------------------------

Comment (by glongwave):

 Yes , it works for me after setting storage engine with InnoDB. Thanks.

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

Re: [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:  closed
      Priority:  medium           |  Milestone:  0.7.xx
     Component:  mysql            |   Severity:  no triage selected yet
    Resolution:  worksforme       |   Keywords:  mysql session transcaction
Progress State:                   |
  completed/closed                |
----------------------------------+----------------------------------------
Changes (by zzzeek):

 * status:  new => closed
 * resolution:   => worksforme
 * status_field:  awaiting triage => completed/closed


Comment:

 Reopen this if I'm incorrect, but I'm 99.9% sure you don't have your
 tables set as InnoDB (and therefore commit/rollback have no effect):

 http://dev.mysql.com/doc/refman/5.0/en/ansi-diff-transactions.html

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

Re: [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 yet
    Resolution:                   |   Keywords:  mysql session transcaction
Progress State:  awaiting triage  |
----------------------------------+----------------------------------------

Comment (by glongwave):

 1)This is output with mysql

 {{{
 -bash-4.1# ./test1.py
 -----------sqlalchemy version-----------
 0.7.9
 2013-09-11 05:31:23,118 INFO sqlalchemy.engine.base.Engine SELECT
 DATABASE()
 2013-09-11 05:31:23,118 INFO sqlalchemy.engine.base.Engine ()
 2013-09-11 05:31:23,124 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES
 LIKE 'character_set%%'
 2013-09-11 05:31:23,125 INFO sqlalchemy.engine.base.Engine ()
 2013-09-11 05:31:23,126 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES
 LIKE 'lower_case_table_names'
 2013-09-11 05:31:23,126 INFO sqlalchemy.engine.base.Engine ()
 2013-09-11 05:31:23,128 INFO sqlalchemy.engine.base.Engine SHOW COLLATION
 2013-09-11 05:31:23,128 INFO sqlalchemy.engine.base.Engine ()
 2013-09-11 05:31:23,132 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES
 LIKE 'sql_mode'
 2013-09-11 05:31:23,132 INFO sqlalchemy.engine.base.Engine ()
 2013-09-11 05:31:23,133 INFO sqlalchemy.engine.base.Engine DESCRIBE
 `users`
 2013-09-11 05:31:23,133 INFO sqlalchemy.engine.base.Engine ()
 2013-09-11 05:31:23,135 INFO sqlalchemy.engine.base.Engine delete from
 users
 2013-09-11 05:31:23,135 INFO sqlalchemy.engine.base.Engine ()
 2013-09-11 05:31:23,136 INFO sqlalchemy.engine.base.Engine COMMIT
 1
 ------------test1 ----------------
 2013-09-11 05:31:23,138 INFO sqlalchemy.engine.base.Engine BEGIN
 (implicit)
 2013-09-11 05:31:23,139 INFO sqlalchemy.engine.base.Engine INSERT INTO
 users (id, name) VALUES (%s, %s)
 2013-09-11 05:31:23,139 INFO sqlalchemy.engine.base.Engine ((1, 'user1'),
 (1, 'user2'))
 2013-09-11 05:31:23,140 INFO sqlalchemy.engine.base.Engine ROLLBACK
 get IntegrityError1
 -----------------------get all users-------------------
 2013-09-11 05:31:23,143 INFO sqlalchemy.engine.base.Engine BEGIN
 (implicit)
 2013-09-11 05:31:23,144 INFO sqlalchemy.engine.base.Engine SELECT users.id
 AS users_id, users.name AS users_name
 FROM users
 2013-09-11 05:31:23,144 INFO sqlalchemy.engine.base.Engine ()
 <User('1','user1')>
 }}}

 2)This is output with sqlite


 {{{
 -bash-4.1# ./test1.py
 -----------sqlalchemy version-----------
 0.7.9
 2013-09-11 05:31:46,127 INFO sqlalchemy.engine.base.Engine PRAGMA
 table_info("users")
 2013-09-11 05:31:46,127 INFO sqlalchemy.engine.base.Engine ()
 2013-09-11 05:31:46,128 INFO sqlalchemy.engine.base.Engine delete from
 users
 2013-09-11 05:31:46,128 INFO sqlalchemy.engine.base.Engine ()
 2013-09-11 05:31:46,128 INFO sqlalchemy.engine.base.Engine COMMIT
 0
 ------------test1 ----------------
 2013-09-11 05:31:46,139 INFO sqlalchemy.engine.base.Engine BEGIN
 (implicit)
 2013-09-11 05:31:46,140 INFO sqlalchemy.engine.base.Engine INSERT INTO
 users (id, name) VALUES (?, ?)
 2013-09-11 05:31:46,140 INFO sqlalchemy.engine.base.Engine ((1, 'user1'),
 (1, 'user2'))
 2013-09-11 05:31:46,141 INFO sqlalchemy.engine.base.Engine ROLLBACK
 get IntegrityError1
 -----------------------get all users-------------------
 2013-09-11 05:31:46,143 INFO sqlalchemy.engine.base.Engine BEGIN
 (implicit)
 2013-09-11 05:31:46,143 INFO sqlalchemy.engine.base.Engine SELECT users.id
 AS users_id, users.name AS users_name
 FROM users
 2013-09-11 05:31:46,144 INFO sqlalchemy.engine.base.Engine ()



 }}}

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