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

[Sqlalchemy-tickets] Issue #3080: load_only triggers `sqlalchemy.exc.NoSuchColumnError` if primary key is not included on subqueryload (zzzeek/sqlalchemy)

[jvanasco <iss...@bi...>]

New issue 3080: load_only triggers `sqlalchemy.exc.NoSuchColumnError` if primary key is not included  on subqueryload
https://bitbucket.org/zzzeek/sqlalchemy/issue/3080/load_only-triggers

jvanasco:

example attached below; this happens in 0.9.4

if trying to load_only a column of the child relationship and not passing in the primary_key, sqlalchemy isn't happy

the sql generated is fine and runs -- it pulls the specified column and uses query string as an anonymous column ( sidenote: is that really necessary ?)

but the ORM is unhappy and raises an error.  

adding the primary key works.  

using a joinedload instead of subqueryload works as well.

and hey, i made a proper test case!


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

    Base = declarative_base()

    class TableA(Base):
        __tablename__ = 'table_a'
        id = Column(Integer, primary_key=True)
        name = Column(String(50))

        bs = relationship("TableB", primaryjoin="TableB.table_a_id==TableA.id")

    class TableB(Base):
        __tablename__ = 'table_b'
        id = Column(Integer, primary_key=True)
        name = Column(String(30))
        table_a_id = Column(Integer, ForeignKey('table_a.id'), nullable=False)


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

    s = Session(e)

    s.add_all([
        TableA(id=1, name='a',),
        TableA(id=2, name='aa',),
        TableA(id=3, name='aaa',),
        TableA(id=4, name='aaaa',),
        TableB(id=1, name='b', table_a_id=1),
        TableB(id=2, name='aa', table_a_id=2),
        TableB(id=3, name='aaa', table_a_id=3),
        TableB(id=4, name='aaaa', table_a_id=4),
    ])

    s.commit()


    q = s.query( TableA ).filter( TableA.id == 2 )

    ## this passes
    q_good = q.options( subqueryload('bs').load_only('id','name') )
    print "trying q_good"
    result = q_good.first()

    q_bad = q.options( subqueryload('bs').load_only('name') )
    print "trying q_bad"
    result = q_bad.first()

    s.close()

[Sqlalchemy-tickets] Issue #3079: select statement returning 0 rows generates: sqlalchemy.exc.ResourceClosedError: This result object does not return rows. It has been closed automatically. (zzzeek/sqlalchemy)

[mike_solomon <iss...@bi...>]

New issue 3079: select statement returning 0 rows generates: sqlalchemy.exc.ResourceClosedError: This result object does not return rows. It has been closed automatically.
https://bitbucket.org/zzzeek/sqlalchemy/issue/3079/select-statement-returning-0-rows

mike_solomon:

Below is code that generates what is, IMO, a bug.  To see the bug, comment out the line towards the end with the comment saying COMMENT ME OUT.  That line will result in the issuing of a select request that, given how I understand SQL and sqlalchemy, should generate 0 rows.  Instead, it causes a ResourceClosedError.  I can use this select statement, however, to be inserted into other tables (see http://stackoverflow.com/questions/24094857/subqueries-for-filters-in-joined-sqlalchemy-statements/24159702#24159702).  I think that the statement should return 0 rows instead of raising the error.

```
#!python

from sqlalchemy import Table, Column, String, Integer, MetaData, \
    select, func, ForeignKey, text
import sys
from functools import reduce

from sqlalchemy import create_engine
engine = create_engine('sqlite:///:memory:', echo=False)

metadata = MetaData()

linked_list = Table('linked_list', metadata,
    Column('id', Integer, primary_key = True),
    Column('at', Integer, nullable=False),
    Column('val', Integer, nullable=False),
    Column('next', Integer, ForeignKey('linked_list.at'))
)

refs = Table('refs', metadata,
    Column('id', Integer, primary_key = True),
    Column('ref', Integer, ForeignKey('linked_list.at')),
)

metadata.create_all(engine)
conn = engine.connect()

refs_al = refs.alias()

linked_list_m = select([
                    linked_list.c.at,
                    linked_list.c.val,
                    linked_list.c.next]).\
                    where(linked_list.c.at==refs_al.c.ref).\
                    cte(recursive=True)

llm_alias = linked_list_m.alias()
ll_alias = linked_list.alias()

linked_list_m = linked_list_m.union_all(
    select([
        llm_alias.c.at,
        ll_alias.c.val * llm_alias.c.val,
        ll_alias.c.next
    ]).
        where(ll_alias.c.at==llm_alias.c.next)
)


llm_alias_2 = linked_list_m.alias()

sub_statement = select([
            llm_alias_2.c.at,
            llm_alias_2.c.val]).\
        order_by(llm_alias_2.c.val.desc()).\
        limit(1)

def gen_statement(v) :
  return select([refs_al.c.ref, func.max(llm_alias_2.c.val)]).\
    select_from(
     refs_al.\
       join(llm_alias_2, onclause=refs_al.c.ref == llm_alias_2.c.at)).\
     group_by(refs_al.c.ref).where(llm_alias_2.c.val > v)

LISTS = [[2,4,4,11],[3,4,5,6]]

idx = 0
for LIST in LISTS :
  start = idx
  for x in range(len(LIST)) :
    ELT = LIST[x]
    conn.execute(linked_list.insert().\
      values(at=idx, val = ELT, next=idx+1 if x != len(LIST) - 1 else None))
    idx += 1
  conn.execute(refs.insert().values(ref=start))
  
print "LISTS:"
for LIST in LISTS :
  print "  ", LIST

def PRODUCT(L) : return reduce(lambda x,y : x*y, L, 1)
print "PRODUCTS OF LISTS:"
for LIST in LISTS :
  print "  ", PRODUCT(LIST)

for x in (345,355,365) :
  if x == 365 : continue # COMMENT ME OUT TO GET sqlalchemy.exc.ResourceClosedError
  statement_ = gen_statement(x)
  print "########"
  print "Lists that are greater than:", x
  conn.execute(statement_)
  allresults = conn.execute(statement_).fetchall()
  if len(allresults) == 0 :
    print "  /no results found/"
  else :
    for res in allresults :
      print res

print "########"
```

[Sqlalchemy-tickets] Issue #3078: postgresql full-text search on `match` should accept multiple parameters (zzzeek/sqlalchemy)

[jvanasco <iss...@bi...>]

New issue 3078: postgresql full-text search on `match` should accept multiple parameters
https://bitbucket.org/zzzeek/sqlalchemy/issue/3078/postgresql-full-text-search-on-match

jvanasco:

I wanted to note a deficiency in the support for full-text search.

the various tsvector and tsquery commands accept 1 or 2 arguments  

   ( string )
   ( dictionary_type, string )

as in - http://www.postgresql.org/docs/9.1/static/functions-textsearch.html

while it possible to explicitly search with a to_tsvector()/to_tesquery that accepts a configuration argument ( as in https://bitbucket.org/zzzeek/sqlalchemy/issue/2800/unable-to-create-tsvector-functional-index ), it is not possible to sneak this parameter into the `match` command.

ie:

    search_query = 'dogs & cats'
    sqlalchemy.func.to_tsquery( text("'english'"), MyTable.mycolumn )\
    .match( sqlalchemy.text("'english'"), search_query )

the target sql would be:

    to_tsvector('english', mytable.mycolumn) @@ to_tsquery('english', 'dogs & cats')

The effects of this behavior:

- loss of control in how the actual matching occurs
- vectors may need to be computed in real-time, rendering indexes or precomputed values useless.

[Sqlalchemy-tickets] Issue #3077: SQLALCHEMY_DATABASE_URI with psycopg2 wont work if password have '+' (zzzeek/sqlalchemy)

[Marcin J. <iss...@bi...>]

New issue 3077: SQLALCHEMY_DATABASE_URI with psycopg2 wont work if password have '+'
https://bitbucket.org/zzzeek/sqlalchemy/issue/3077/sqlalchemy_database_uri-with-psycopg2-wont

Marcin Jabrzyk:

Just tried to create URI connection string where user have password with '+' character, SQLAlchemy gives no clue in error and connecting to database using psql works fine. 
Example URI:
SQLALCHEMY_DATABASE_URI = "postgresql+psycopg2://user:us...@so...:5432/some_db"

Output:
sqlalchemy.exc.OperationalError: (OperationalError) FATAL:  password authentication failed for user "user"
FATAL:  password authentication failed for user "user"
 None None

Changing password to not having '+' works fine.

[Sqlalchemy-tickets] Issue #3076: support reraise of custom exceptions within core exception handling (zzzeek/sqlalchemy)

[Mike B. <iss...@bi...>]

New issue 3076: support reraise of custom exceptions within core exception handling
https://bitbucket.org/zzzeek/sqlalchemy/issue/3076/support-reraise-of-custom-exceptions

Mike Bayer:

patch attached.

[Sqlalchemy-tickets] Issue #3075: support non-standard DBAPI exception classes (zzzeek/sqlalchemy)

[Mike B. <iss...@bi...>]

New issue 3075: support non-standard DBAPI exception classes
https://bitbucket.org/zzzeek/sqlalchemy/issue/3075/support-non-standard-dbapi-exception

Mike Bayer:

apparently psycopg2 has an OperationalError subclass `psycopg2.extensions.TransactionRollbackError`.  We should be reporting this at least as an OperationalError and not dropping down to DBAPIError.

[Sqlalchemy-tickets] Issue #3074: allow **kw for TypeCompiler, specifically pass the parent SQL expression or column (zzzeek/sqlalchemy)

[Mike B. <iss...@bi...>]

New issue 3074: allow **kw for TypeCompiler, specifically pass the parent SQL expression or column
https://bitbucket.org/zzzeek/sqlalchemy/issue/3074/allow-kw-for-typecompiler-specifically

Mike Bayer:

this would allow type-compilation that can be altered by the properties of the enclosing column, such as if nullable affects things.

patch would use a conditional that allows legacy dialects to not require the **kw.

[Sqlalchemy-tickets] Issue #3073: Don't allow to automatically query database when use load_only option (zzzeek/sqlalchemy)

[toan n. <iss...@bi...>]

New issue 3073: Don't allow to automatically query database when use load_only option
https://bitbucket.org/zzzeek/sqlalchemy/issue/3073/dont-allow-to-automatically-query-database

toan nguyen:

When I query object like that:


```
#!python

user = session.query(User).options(load_only('id', 'email')).first()
```

If I use user object but I don't remember I only loaded 'email', 'id' columns and used like that:


```
#!python
print user.name 

```
It'll automatically query database to set 'name' attribute. I want an option for me to don't allow it to do automatic. Because of special reason, I can't query like that

```
#!python
user = session.query(User.id, User.email).first()

```

[Sqlalchemy-tickets] Issue #3072: Session rollback fails to undo an attribute change. (zzzeek/sqlalchemy)

[ehynes <iss...@bi...>]

New issue 3072: Session rollback fails to undo an attribute change.
https://bitbucket.org/zzzeek/sqlalchemy/issue/3072/session-rollback-fails-to-undo-an

ehynes:

Calling session.rollback() while following along with the ORM tutorial failed to revert the user name from 'Edwardo' back to 'ed'.

[Sqlalchemy-tickets] Issue #3071: str/repr might be broken on RowProxy (zzzeek/sqlalchemy)

[Dustin O. <iss...@bi...>]

New issue 3071: str/repr might be broken on RowProxy
https://bitbucket.org/zzzeek/sqlalchemy/issue/3071/str-repr-might-be-broken-on-rowproxy

Dustin Oprea:

There are some situations under which the str/repr representations of the returned records are malformed. However, I am unsure of the conditions.

```
#!python

print("0: %s" % (record.__class__))
print("1: %s" % (str(record)))
print("2: %s" % (repr(record)))
print("3: %s" % (dict(record)))
print("4: %s" % (record.items()))
```

When it works:

```
0: <class 'sqlalchemy.engine.result.RowProxy'>
1: (5L, 'Dev Client (dustin)', 5L, 1L, '2019-05-27 23:11:47', 0L, 1L)
2: (5L, 'Dev Client (dustin)', 5L, 1L, '2019-05-27 23:11:47', 0L, 1L)
3: {u'certificate_validity_y': 5L, u'certificate_expire_timestamp': '2019-05-27 23:11:47', u'name': 'Dev Client (dustin)', u'can_proxy': 0L, u'is_admin': 1L, u'client_id': 5L, u'allow_api': 1L}
4: [(u'client_id', 5L), (u'name', 'Dev Client (dustin)'), (u'certificate_validity_y', 5L), (u'allow_api', 1L), (u'certificate_expire_timestamp', '2019-05-27 23:11:47'), (u'can_proxy', 0L), (u'is_admin', 1L)]
```

When it's broken:

```
0: <class 'sqlalchemy.engine.result.RowProxy'>
1: (1L,)
2: (1L,)
3: {u'affected': 1L}
4: [(u'affected', 1L)]
```

I also used "xyz" as the column name, but it didn't make a difference:

```
0: <class 'sqlalchemy.engine.result.RowProxy'>
1: (1L,)
2: (1L,)
3: {u'xyz': 1L}
4: [(u'xyz', 1L)]
```

I am using MySQL. It happens on latest (0.9.5).

[Sqlalchemy-tickets] Issue #3070: ForeignKey schema detection from MetaData problem (zzzeek/sqlalchemy)

[Iurie G. <iss...@bi...>]

New issue 3070: ForeignKey schema detection from MetaData problem
https://bitbucket.org/zzzeek/sqlalchemy/issue/3070/foreignkey-schema-detection-from-metadata

Iurie Gheorghies:

"""Python 2.7.5+
SQLAlchemy 9.4"""

A regression fix was made:
https://github.com/zzzeek/sqlalchemy/pull/67

but the script fails with exception

The script attached output:

sqlalchemy.exc.NoReferencedTableError: Foreign key associated with column 'user.account_id' could not find table 'account' with which to generate a foreign key to target column 'id'

[Sqlalchemy-tickets] Issue #3069: multi-valued insert isn't checking additonal values for literal (zzzeek/sqlalchemy)

[Mike B. <iss...@bi...>]

New issue 3069: multi-valued insert isn't checking additonal values for literal
https://bitbucket.org/zzzeek/sqlalchemy/issue/3069/multi-valued-insert-isnt-checking

Mike Bayer:


```
#!python

from sqlalchemy import create_engine, MetaData, Table, Integer, Date, Column
import sqlalchemy.sql as sql

md = MetaData()
table = Table("data", md)
table.append_column(Column("id", Integer))
table.append_column(Column("adate", Date))
table.append_column(Column("value", Integer))

data = [
   {"id": 1, "value": "foo", "adate": sql.func.now()},
   {"id": 2, "value": "bar", "adate": sql.func.now()}
]

stmt = table.insert().values(data)
print stmt

```

patch:

```
#!diff


index cd01ea5..dff4c99 100644
--- a/lib/sqlalchemy/sql/compiler.py
+++ b/lib/sqlalchemy/sql/compiler.py
@@ -2207,10 +2207,12 @@ class SQLCompiler(Compiled):
                 [
                     (
                         c,
-                            self._create_crud_bind_param(
+                            (self._create_crud_bind_param(
                                     c, row[c.key],
                                     name="%s_%d" % (c.key, i + 1)
-                            )
+                            ) if elements._is_literal(row[c.key])
+                                else self.process(
+                                            row[c.key].self_group(), **kw))
                             if c.key in row else param
                     )
                     for (c, param) in values_0

```

[Sqlalchemy-tickets] Issue #3068: using conv() should be bypassing naming convention entirely (zzzeek/sqlalchemy)

[Mike B. <iss...@bi...>]

New issue 3068: using conv() should be bypassing naming convention entirely
https://bitbucket.org/zzzeek/sqlalchemy/issue/3068/using-conv-should-be-bypassing-naming

Mike Bayer:

this issue is mostly fixed by c33d0378802a with #3028 but that is a different issue being fixed here.  if conv() is present we shouldn't be hitting naming convention logic at all.  the below fails in 0.9.4 but succeeds in rel_0_9:


```
#!python

from sqlalchemy import UniqueConstraint, Table, MetaData
from sqlalchemy.sql.naming import conv

m = MetaData(naming_convention={"uq": "uq_%(table_name)s_%(column_0_name)s"})

t = Table('mytable', m)
uq = UniqueConstraint(name=conv('my_special_key'))

t.append_constraint(uq)
```

[Sqlalchemy-tickets] Issue #3067: Naming convention exception for Boolean type on PostgreSQL (zzzeek/sqlalchemy)

[Maik R. <iss...@bi...>]

New issue 3067: Naming convention exception for Boolean type on PostgreSQL
https://bitbucket.org/zzzeek/sqlalchemy/issue/3067/naming-convention-exception-for-boolean

Maik Riechert:


```
#!python

mybool = Column(Boolean, nullable=False, default=False)
```


I use PostgreSQL which has a native boolean type. If I then also use naming convention:


```
#!python

"ck": "ck_%(table_name)s_%(constraint_name)s",
```


Then sqlalchemy complains:

sqlalchemy.exc.InvalidRequestError: Naming convention including %(constraint_name)s token requires that constraint is explicitly named.

If I define the column as


```
#!python

isPublic = Column(Boolean(create_constraint=False), nullable=False, default=False)
```


it works, so I think this is a bug in which sqlalchemy doesn't connect the fact that there is a native Boolean and no constraint exists actually.

[Sqlalchemy-tickets] Issue #3066: add __clause_element__() to column_property (zzzeek/sqlalchemy)

[Mike B. <iss...@bi...>]

New issue 3066: add __clause_element__() to column_property
https://bitbucket.org/zzzeek/sqlalchemy/issue/3066/add-__clause_element__-to-column_property

Mike Bayer:

from #3050


```
#!diff

diff --git a/lib/sqlalchemy/orm/properties.py b/lib/sqlalchemy/orm/properties.py
index a0def7d..26196c1 100644
--- a/lib/sqlalchemy/orm/properties.py
+++ b/lib/sqlalchemy/orm/properties.py
@@ -148,6 +148,9 @@ class ColumnProperty(StrategizedProperty):
                                     ("instrument", self.instrument)
                                     )

+    def __clause_element__(self):
+        return self.expression
+
     @property
     def expression(self):
         """Return the primary column or expression for this ColumnProperty.


```

[Sqlalchemy-tickets] Issue #3065: class attributes with parent/child in the name (zzzeek/sqlalchemy)

[igorsf <iss...@bi...>]

New issue 3065: class attributes with parent/child in the name
https://bitbucket.org/zzzeek/sqlalchemy/issue/3065/class-attributes-with-parent-child-in-the

igorsf:

The following  declaration returns an error "could not assemble any primary key columns". I traced the problem to the names of the attributes in the class "parent" and "child". ORM doesn't see any attributes with parent or child in the name. If you rename class attributes and remove "parent" and "child" keywords it works.

Doesn't work:

class Association(Base):
    __tablename__ = 'association'
    parent_left_id = Column(Integer, ForeignKey('parent_left_id.id'), primary_key=True)
    child_right_id = Column(Integer, ForeignKey('child_right_id.id'), primary_key=True)
    extra_data = Column(String(50))
    child = relationship("Child")

Works fine:

class Association(Base):
    __tablename__ = 'association'
    left_id = Column(Integer, ForeignKey('parent_left_id.id'), primary_key=True)
    right_id = Column(Integer, ForeignKey('child_right_id.id'), primary_key=True)
    extra_data = Column(String(50))
    child = relationship("Child")

[Sqlalchemy-tickets] Issue #3064: Materialized path join: additional join criteria results in indeterministic SQL (zzzeek/sqlalchemy)

[Jack Z. <iss...@bi...>]

New issue 3064: Materialized path join: additional join criteria results in indeterministic SQL
https://bitbucket.org/zzzeek/sqlalchemy/issue/3064/materialized-path-join-additional-join

Jack Zhou:

Consider the following configuration:

```
#!python
class Node(Base):
    __tablename__ = "node"
    id = Column(Integer, primary_key=True)
    path = Column(String(500), nullable=False)
    depth = Column(Integer, nullable=False)
    children = relationship("Node", viewonly=True,
                            primaryjoin=and_(
                                remote(foreign(path)).like(path.concat(".%")),
                                remote(depth) == depth + 1))
```

And the query:

```
#!python
db.add(Node(path="foo", depth=0))
db.add(Node(path="foo.baz", depth=1))
db.flush()
db.expunge_all()
print(db.query(Node).first().children)
db.expunge_all()
print(db.query(Node).options(subqueryload(Node.children)).first().children)
```

**The output of this is not deterministic.** Depending on external factors (such as whether an unused import is present), the second print statement may or may not be an empty list. I've tracked it down to incorrectly rendered SQL. On "correct" runs, the rendered SQL looks like this:

```
#!sql
SELECT node.id AS node_id,
       node.path AS node_path,
       node.depth AS node_depth,
       anon_1.node_path AS anon_1_node_path,
       anon_1.node_depth AS anon_1_node_depth
FROM
  (SELECT DISTINCT node.path AS node_path,
                   node.depth AS node_depth
   FROM node LIMIT 1) AS anon_1
JOIN node ON node.path LIKE (anon_1.node_path || '.%')
AND node.depth = anon_1.node_depth + 1
ORDER BY anon_1.node_path,
         anon_1.node_depth;
```

On incorrect runs, the rendered SQL looks like this:

```
#!sql
SELECT node.id AS node_id,
       node.path AS node_path,
       node.depth AS node_depth,
       anon_1.node_depth AS anon_1_node_depth,
       anon_1.node_path AS anon_1_node_path
FROM
  (SELECT DISTINCT node.depth AS node_depth,
                   node.path AS node_path
   FROM node LIMIT 1) AS anon_1
JOIN node ON node.path LIKE (anon_1.node_path || '.%')
AND node.depth = anon_1.node_depth + 1
ORDER BY anon_1.node_depth,
         anon_1.node_path;
```

The problem lies in `anon_1`. The correct subquery for `anon_1` should be `SELECT DISTINCT node.depth AS node_depth FROM node LIMIT 1`. I may be wrong, but I've inferred from this that SQLAlchemy is incorrectly considering `depth` to be a foreign key when it is not marked as such.

[Sqlalchemy-tickets] Issue #3063: engine/base -> safe_close_cursor assumes our connection has a _logger (zzzeek/sqlalchemy)

[Mike B. <iss...@bi...>]

New issue 3063: engine/base -> safe_close_cursor assumes our connection has a _logger
https://bitbucket.org/zzzeek/sqlalchemy/issue/3063/engine-base-safe_close_cursor-assumes-our

Mike Bayer:

it's very tough to figure out how to reproduce this one.  If you get a non-disconnect exception, but an unclosable cursor within first connect, the exception is squashed because we have a DBAPI connection in first_connect, not a connection fairy.

```
#!python

    def _safe_close_cursor(self, cursor):
        """Close the given cursor, catching exceptions
        and turning into log warnings.

        """
        try:
            cursor.close()
        except (SystemExit, KeyboardInterrupt):
            raise
        except Exception:
            self.connection._logger.error(
                                    "Error closing cursor", exc_info=True)

```

[Sqlalchemy-tickets] Issue #3062: version_id_col ihretired incorrect (zzzeek/sqlalchemy)

[tbicr <iss...@bi...>]

New issue 3062: version_id_col ihretired incorrect
https://bitbucket.org/zzzeek/sqlalchemy/issue/3062/version_id_col-ihretired-incorrect

tbicr:

I have next example:

    from datetime import datetime
    
    from sqlalchemy import Column, Integer, DateTime
    from sqlalchemy import create_engine
    from sqlalchemy.orm import sessionmaker, scoped_session
    from sqlalchemy.ext.declarative import declarative_base
    
    
    engine = create_engine('sqlite://')
    Session = scoped_session(sessionmaker(bind=engine))
    Base = declarative_base()
    
    
    def datetime_utcnow():
        return datetime.utcnow()
    
    
    class TestBase(Base):
        id = Column(Integer, primary_key=True)
        version_id = Column(Integer, nullable=False)
        create_datetime = Column(DateTime, default=datetime_utcnow, nullable=False)
        update_datetime = Column(DateTime, default=datetime_utcnow, onupdate=datetime_utcnow, nullable=False)
    
        __abstract__ = True
        __mapper_args__ = {
            'version_id_col': version_id,
        }
    
    
    class Test1(TestBase):
    
        __tablename__ = 'test1'
    
        data = Column(Integer)
    
    
    class Test2(TestBase):
    
        __tablename__ = 'test2'
    
        data = Column(Integer)
    
    
    if __name__ == '__main__':
        Base.metadata.create_all(engine)
        session = Session()
        session.add(Test1())
        session.commit()
        session.add(Test2())
        session.commit()

This code throw exception on second commit:

    /home/tbicr/Project/env/bin/python /home/tbicr/Project/test_sample/main_dev.py
    Traceback (most recent call last):
      File "/home/tbicr/Project/env/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 940, in _execute_context
        context)
      File "/home/tbicr/Project/env/lib/python3.4/site-packages/sqlalchemy/engine/default.py", line 441, in do_execute
        cursor.execute(statement, parameters)
    sqlite3.IntegrityError: NOT NULL constraint failed: test2.version_id

    The above exception was the direct cause of the following exception:

    Traceback (most recent call last):
      File "/home/tbicr/Project/test_sample/main_dev.py", line 50, in <module>
        session.commit()
      File "/home/tbicr/Project/env/lib/python3.4/site-packages/sqlalchemy/orm/session.py", line 765, in commit
        self.transaction.commit()
      File "/home/tbicr/Project/env/lib/python3.4/site-packages/sqlalchemy/orm/session.py", line 370, in commit
        self._prepare_impl()
      File "/home/tbicr/Project/env/lib/python3.4/site-packages/sqlalchemy/orm/session.py", line 350, in _prepare_impl
        self.session.flush()
      File "/home/tbicr/Project/env/lib/python3.4/site-packages/sqlalchemy/orm/session.py", line 1903, in flush
        self._flush(objects)
      File "/home/tbicr/Project/env/lib/python3.4/site-packages/sqlalchemy/orm/session.py", line 2021, in _flush
        transaction.rollback(_capture_exception=True)
      File "/home/tbicr/Project/env/lib/python3.4/site-packages/sqlalchemy/util/langhelpers.py", line 57, in __exit__
        compat.reraise(exc_type, exc_value, exc_tb)
      File "/home/tbicr/Project/env/lib/python3.4/site-packages/sqlalchemy/util/compat.py", line 168, in reraise
        raise value
      File "/home/tbicr/Project/env/lib/python3.4/site-packages/sqlalchemy/orm/session.py", line 1985, in _flush
        flush_context.execute()
      File "/home/tbicr/Project/env/lib/python3.4/site-packages/sqlalchemy/orm/unitofwork.py", line 370, in execute
        rec.execute(self)
      File "/home/tbicr/Project/env/lib/python3.4/site-packages/sqlalchemy/orm/unitofwork.py", line 523, in execute
        uow
      File "/home/tbicr/Project/env/lib/python3.4/site-packages/sqlalchemy/orm/persistence.py", line 64, in save_obj
        mapper, table, insert)
      File "/home/tbicr/Project/env/lib/python3.4/site-packages/sqlalchemy/orm/persistence.py", line 594, in _emit_insert_statements
        execute(statement, params)
      File "/home/tbicr/Project/env/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 720, in execute
        return meth(self, multiparams, params)
      File "/home/tbicr/Project/env/lib/python3.4/site-packages/sqlalchemy/sql/elements.py", line 317, in _execute_on_connection
        return connection._execute_clauseelement(self, multiparams, params)
      File "/home/tbicr/Project/env/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 817, in _execute_clauseelement
        compiled_sql, distilled_params
      File "/home/tbicr/Project/env/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 947, in _execute_context
        context)
      File "/home/tbicr/Project/env/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 1108, in _handle_dbapi_exception
        exc_info
      File "/home/tbicr/Project/env/lib/python3.4/site-packages/sqlalchemy/util/compat.py", line 174, in raise_from_cause
        reraise(type(exception), exception, tb=exc_tb, cause=exc_value)
      File "/home/tbicr/Project/env/lib/python3.4/site-packages/sqlalchemy/util/compat.py", line 167, in reraise
        raise value.with_traceback(tb)
      File "/home/tbicr/Project/env/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 940, in _execute_context
        context)
      File "/home/tbicr/Project/env/lib/python3.4/site-packages/sqlalchemy/engine/default.py", line 441, in do_execute
        cursor.execute(statement, parameters)
    sqlalchemy.exc.IntegrityError: (IntegrityError) NOT NULL constraint failed: test2.version_id 'INSERT INTO test2 (version_id, create_datetime, update_datetime, data) VALUES (?, ?, ?, ?)' (None, '2014-05-30 09:29:16.697519', '2014-05-30 09:29:16.697548', None)

    Process finishe

Look like `version_id` column mapped to `Test1` on first definition and on `Test2` definition check that it is instance of `Test1` and skip it: https://bitbucket.org/zzzeek/sqlalchemy/src/28fbecaaa00ac6039d6c6b7e5abd594160f74104/lib/sqlalchemy/orm/persistence.py?at=master#cl-251.

[Sqlalchemy-tickets] Issue #3061: set attribute history to None when we do an implicit get() of an unset attribute? (zzzeek/sqlalchemy)

[Mike B. <iss...@bi...>]

New issue 3061: set attribute history to None when we do an implicit get() of an unset attribute?
https://bitbucket.org/zzzeek/sqlalchemy/issue/3061/set-attribute-history-to-none-when-we-do

Mike Bayer:

see #3060.  We have a lot of problems here due to the fact that:


```
#!python

x = Obj()
x.foo  # is None, sets it to None, doesn't write any history
inspect(x).attrs.foo.history # shows no history due to the get() above.
```

[Sqlalchemy-tickets] Issue #3060: Implicit merge ignores relationship properties set to None (zzzeek/sqlalchemy)

[ods <iss...@bi...>]

New issue 3060: Implicit merge ignores relationship properties set to None
https://bitbucket.org/zzzeek/sqlalchemy/issue/3060/implicit-merge-ignores-relationship

ods:

New object with the same ID as existing one implicitly merged via cascade rules has old value of relationship property after the commit when the new value is None.

In short:
```
#!python
doc.doc_links = [DocLink(id=link_id, link=None)]
session.commit()
assert doc.doc_links[0].link is None  # fails
```

Full test is attached (test3 is the test case that fails).

[Sqlalchemy-tickets] Issue #3059: Tutorial should note that MATCH doesn't work out-of-the-box in SQLite (zzzeek/sqlalchemy)

[Tom X. T. <iss...@bi...>]

New issue 3059: Tutorial should note that MATCH doesn't work out-of-the-box in SQLite
https://bitbucket.org/zzzeek/sqlalchemy/issue/3059/tutorial-should-note-that-match-doesnt

Tom X. Tobin:

The following example in the tutorial doesn't work when using SQLite as instructed, raising an `OperationalError`:
```
# The tutorial doesn't make this explicit:
# query = session.query(User)

query.filter(User.name.match('wendy'))
```
The tutorial should note that `MATCH` (like `REGEXP`) [doesn't work out-of-the-box in SQLite] [1]:
> The MATCH operator is a special syntax for the match() application-defined function. The default match() function implementation raises an exception and is not really useful for anything. But extensions can override the match() function with more helpful logic.
[1]: http://www.sqlite.org/lang_expr.html

[Sqlalchemy-tickets] Issue #3058: Allow to inject expression-based columns into Query objects (zzzeek/sqlalchemy)

[Henrique C. A. <iss...@bi...>]

New issue 3058: Allow to inject expression-based columns into Query objects
https://bitbucket.org/zzzeek/sqlalchemy/issue/3058/allow-to-inject-expression-based-columns

Henrique Carvalho Alves:

I'm trying to do something that, in principle, should be simple: inject a expression-based column into an ORM-based Query. I've tried this:

```
#!python

query = db.session.query(MyClass).all()
# Using `now` just as an example, ideally it's something that takes a parameter
# so it can't be replaced by `orm.column_property`
some_expression = func.now() 
selectable = query.selectable
# The following works as expected, injecting the expression as a column
# the AnnotatedSelect object...
selectable.append_column(some_expression.label('some_column'))
# ... but I can't set it back on the query
query.selectable = selectable
query = query.order_by('some_column')

```

It fails with `AttributeError` since `query.selectable` is read-only. I haven't found another way to inject an expression by reading the docs or the source.

- `Query.add_column` isn't quite the same thing because the column is returned alongside instead of nested on `MyClass` objects in the result, which messes with serialization.

- `orm.column_property` is of limited usefulness since you can't pass arguments only known at runtime instead of class-evaluation time.

I thought SQLAlchemy made things like this a breeze, but I've spent days without finding an adequate solution. Either I'm missing something, or an API for this is badly needed.

[Sqlalchemy-tickets] Issue #3057: union query fails in some case (zzzeek/sqlalchemy)

[hiroaki_kawai <iss...@bi...>]

New issue 3057: union query fails in some case
https://bitbucket.org/zzzeek/sqlalchemy/issue/3057/union-query-fails-in-some-case

hiroaki_kawai:

I got an Exception with an union query with sqlite 0.9.x and sqlite, while which looks it should work. I could not figure out the root cause but managed to create a reproducable code below:

I tested on:

 * sqlalchemy 0.8.6, 0.9.0, 0.9.1, 0.9.2, 0.9.3, 0.9.4 with and without CEXT
 * sqlite on cygwin python2 and 3 and Ubuntu 14.04 python2 and 3
 * mysql on cygwin and Ubuntu

Errors with sqlalchemy 0.9.x and sqlite only. If I change the table name "a_b" to "ab", then the error won't be triggered.

```
#!python

from sqlalchemy import create_engine, Column, Integer, ForeignKey
from sqlalchemy.orm import sessionmaker
import sqlalchemy.ext.declarative
Base = sqlalchemy.ext.declarative.declarative_base()

class A(Base):
	__tablename__="a"
	id = Column(Integer, primary_key=True)
	b_id = Column(Integer, ForeignKey("a_b.id"))

class AB(Base):
	__tablename__="a_b"
	id = Column(Integer, primary_key=True)

#engine = create_engine("mysql://root:test@127.0.0.1/testdb", echo=True)
engine = create_engine("sqlite://", echo=True)
Base.metadata.create_all(engine)
session = sessionmaker(bind=engine)()
q1 = session.query(A, AB).select_from(A, AB).outerjoin(AB, A.b_id==AB.id)
q2 = session.query(A, AB).select_from(AB, A).outerjoin(A,  A.b_id==AB.id)
query = q1.union(q2)
print query.all()

```

result:

```
#!shell
2014-05-20 23:41:29,145 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2014-05-20 23:41:29,145 INFO sqlalchemy.engine.base.Engine ()
2014-05-20 23:41:29,146 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2014-05-20 23:41:29,146 INFO sqlalchemy.engine.base.Engine ()
2014-05-20 23:41:29,147 INFO sqlalchemy.engine.base.Engine PRAGMA table_info("a_b")
2014-05-20 23:41:29,147 INFO sqlalchemy.engine.base.Engine ()
2014-05-20 23:41:29,147 INFO sqlalchemy.engine.base.Engine PRAGMA table_info("a")
2014-05-20 23:41:29,147 INFO sqlalchemy.engine.base.Engine ()
2014-05-20 23:41:29,148 INFO sqlalchemy.engine.base.Engine
CREATE TABLE a_b (
        id INTEGER NOT NULL,
        PRIMARY KEY (id)
)


2014-05-20 23:41:29,148 INFO sqlalchemy.engine.base.Engine ()
2014-05-20 23:41:29,148 INFO sqlalchemy.engine.base.Engine COMMIT
2014-05-20 23:41:29,148 INFO sqlalchemy.engine.base.Engine
CREATE TABLE a (
        id INTEGER NOT NULL,
        b_id INTEGER,
        PRIMARY KEY (id),
        FOREIGN KEY(b_id) REFERENCES a_b (id)
)


2014-05-20 23:41:29,149 INFO sqlalchemy.engine.base.Engine ()
2014-05-20 23:41:29,149 INFO sqlalchemy.engine.base.Engine COMMIT
2014-05-20 23:41:29,152 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2014-05-20 23:41:29,154 INFO sqlalchemy.engine.base.Engine SELECT anon_1.a_id AS anon_1_a_id, anon_1.a_b_id AS anon_1_a_b_id, anon_1.id_1 AS anon_1_id_1
FROM (SELECT a.id AS a_id, a.b_id AS a_b_id, a_b.id AS id_2
FROM a LEFT OUTER JOIN a_b ON a.b_id = a_b.id UNION SELECT a.id AS a_id, a.b_id AS a_b_id, a_b.id AS id_2
FROM a_b LEFT OUTER JOIN a ON a.b_id = a_b.id) AS anon_1
2014-05-20 23:41:29,154 INFO sqlalchemy.engine.base.Engine ()
Traceback (most recent call last):
  File "test_sqlite_union.py", line 22, in <module>
    print query.all()
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/query.py", line 2292, in all
    return list(self)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/query.py", line 2404, in __iter__
    return self._execute_and_instances(context)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/query.py", line 2419, in _execute_and_instances
    result = conn.execute(querycontext.statement, self._params)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 720, in execute
    return meth(self, multiparams, params)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/sql/elements.py", line 317, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 817, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 947, in _execute_context
    context)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1108, in _handle_dbapi_exception
    exc_info
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/util/compat.py", line 185, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 940, in _execute_context
    context)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/default.py", line 435, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (OperationalError) no such column: anon_1.id_1 u'SELECT anon_1.a_id AS anon_1_a_id, anon_1.a_b_id AS anon_1_a_b_id, anon_1.id_1 AS anon_1_id_1 \nFROM (SELECT a.id AS a_id, a.b_id AS a_b_id, a_b.id AS id_2 \nFROM a LEFT OUTER JOIN a_b ON a.b_id = a_b.id UNION SELECT a.id AS a_id, a.b_id AS a_b_id, a_b.id AS id_2 \nFROM a_b LEFT OUTER JOIN a ON a.b_id = a_b.id) AS anon_1' ()

```

[Sqlalchemy-tickets] Issue #3056: Materialized path relationship: `lazy="subquery"` not working while `subqueryload()` works (zzzeek/sqlalchemy)

[Jack Z. <iss...@bi...>]

New issue 3056: Materialized path relationship: `lazy="subquery"` not working while `subqueryload()` works
https://bitbucket.org/zzzeek/sqlalchemy/issue/3056/materialized-path-relationship-lazy

Jack Zhou:

Reference: http://stackoverflow.com/questions/23054398/materialized-path-relationship-in-declarative-sqlalchemy/23176477#comment36511621_23176477

The configuration:

```
#!python

class Node(Base):
    __tablename__ = "node"
    id = Column(Integer, primary_key=True)
    path = Column(String(500), nullable=False)
    children = relationship(
        "Node", primaryjoin=remote(foreign(path)).like(path.concat(".%")),
        viewonly=True, lazy="subquery")
```

The query:

```
#!python

db.add(Node(path="foo"))
db.add(Node(path="foo.bar"))
db.add(Node(path="foo.bar.baz"))
db.flush()

db.query(Node).all()  # issues one SELECT to find all nodes
db.query(Node).options(subqueryload(Node.children)).all()  # issues two SELECTs to find all nodes and their children
```

The `lazy="subquery"` declaration does not seem to have any effect.