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

[Sqlalchemy-tickets] Issue #3567: BaseModel constructor is not documented (zzzeek/sqlalchemy)

[p-vital <iss...@bi...>]

New issue 3567: BaseModel constructor is not documented
https://bitbucket.org/zzzeek/sqlalchemy/issues/3567/basemodel-constructor-is-not-documented

p-vital:

https://pythonhosted.org/Flask-SQLAlchemy/api.html#flask.ext.sqlalchemy.BaseQuery has no documentation for BaseModel constructor. This:

- makes it unclear how to write BaseQuery derivative classes
- makes it unclear how to instantiate BaseQuery or derived class instances

[Sqlalchemy-tickets] Issue #3566: figure out how to support all of PG's bizarro SETOF, RECORD, etc. types w/ emphasis on JSON fucntions (zzzeek/sqlalchemy)

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

New issue 3566: figure out how to support all of PG's bizarro SETOF, RECORD, etc. types w/ emphasis on JSON fucntions
https://bitbucket.org/zzzeek/sqlalchemy/issues/3566/figure-out-how-to-support-all-of-pgs

Mike Bayer:

that is, all the functions in http://www.postgresql.org/docs/9.4/static/functions-json.html need to be possible without building subclasses of functions.

[Sqlalchemy-tickets] Issue #3565: Improve JSON documentation (zzzeek/sqlalchemy)

[Thijs D. <iss...@bi...>]

New issue 3565: Improve JSON documentation
https://bitbucket.org/zzzeek/sqlalchemy/issues/3565/improve-json-documentation

Thijs Damsma:

I am having difficulty using sqlalchemy to setup queries for JSON fields, specifically the issue I mentioned in the following [stack-overflow question](http://stackoverflow.com/questions/33214610/how-to-query-jsonb-array-with-sqlalchemy)

Of course this is also a not so subtle attempt to draw some attention to my question, but more in general I found the documentation for using JSON/JSONB and sqlalchemy very limited. It would be of great help if there were some more examples for doing less trivial querying and manipulation 

Thanks!

[Sqlalchemy-tickets] Issue #3564: Arrays of strings that containg commas are being separated incorrectly (zzzeek/sqlalchemy)

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

New issue 3564: Arrays of strings that containg commas are being separated incorrectly
https://bitbucket.org/zzzeek/sqlalchemy/issues/3564/arrays-of-strings-that-containg-commas-are

Forest Gregg:



```
#!python

result = conn.execute(sa.text("select array['foo', 'bar,baz']"))
result.first()
(('foo', 'bar', 'baz'),)
```

The result should be

```
#!python

result = conn.execute(sa.text("select array['foo', 'bar,baz']"))
result.first()
(('foo', "bar,baz"),)
```

[Sqlalchemy-tickets] Issue #3563: Improve docs for `contains_eager` (zzzeek/sqlalchemy)

[Ian M. <iss...@bi...>]

New issue 3563: Improve docs for `contains_eager`
https://bitbucket.org/zzzeek/sqlalchemy/issues/3563/improve-docs-for-contains_eager

Ian McCullough:

It took me a *really* long time to discover, and then figure out how to use `contains_eager` for the use case of:

> Load entity A (which has a to-many relationship to B) populating its list of Bs with the subset of related Bs that meet a certain filter criteria.

The existing docs for `contains_eager` don't express well its applicability for this goal, and neither of the examples show any filtering. The closest they come is:

> The above query would join from the Order entity to its related User entity, and the returned Order objects would have the Order.user attribute pre-populated.

It took me a long time to figure out how this was different from `joinedload` and to understand that it could filter relationship collections. Even just a trivial example that showed a trivial filter on the related entity would go a long way. Thanks!

[Sqlalchemy-tickets] Issue #3562: support callable boundparam in primaryjoin? (zzzeek/sqlalchemy)

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

New issue 3562: support callable boundparam in primaryjoin?
https://bitbucket.org/zzzeek/sqlalchemy/issues/3562/support-callable-boundparam-in-primaryjoin

Mike Bayer:

lazyloading can be made to work for this case:


```
#!python

def get_age():
    return 15


class Child(Base):
    __tablename__ = 'child'
    id = Column('id', Integer, primary_key=True)
    age = Column(Integer)
    parent_id = Column(Integer, ForeignKey('parent.id'))


class Parent(Base):
    __tablename__ = 'parent'
    id = Column('id', Integer, primary_key=True)
    @classmethod
    def __declare_last__(cls):
        cls.children_by_age = relationship(
            Child,
            lazy=True, 
            primaryjoin=and_(
                Parent.id == Child.parent_id,
                Child.age == bindparam('age', callable_=get_age)
            )
        )

```

if we do this:
```
#!diff



diff --git a/lib/sqlalchemy/orm/strategies.py b/lib/sqlalchemy/orm/strategies.py
index 67dac1c..aa0a9f7 100644
--- a/lib/sqlalchemy/orm/strategies.py
+++ b/lib/sqlalchemy/orm/strategies.py
@@ -434,6 +434,8 @@ class LazyLoader(AbstractRelationshipLoader, util.MemoizedSlots):
         params = []
 
         def visit_bindparam(bindparam):
+            if bindparam.callable:
+                return
             bindparam.unique = False
             if bindparam._identifying_key in bind_to_col:
                 params.append((
```

should we do that (and is that where we should do that)?   Is there a better way to support bound callables in join conditions? what are the use cases for this ?  note that the bindparam w/ callable_ works for joinedload, subqueryload, works for query.join(Parent.foo), so it's likely we should do this so that the behavior is consistent.

[Sqlalchemy-tickets] Issue #3561: sqlite nested transactions fail with "(sqlite3.OperationalError) no such savepoint" (zzzeek/sqlalchemy)

[Brian C. <iss...@bi...>]

New issue 3561: sqlite nested transactions fail with "(sqlite3.OperationalError) no such savepoint"
https://bitbucket.org/zzzeek/sqlalchemy/issues/3561/sqlite-nested-transactions-fail-with

Brian Candler:

Using savepoints via the sqlite3 command line tool works fine; but doing it via SQLAlchemy gives a "no such savepoint" error.

~~~
$ cat requirements.txt
PyMySQL
SQLAlchemy

$ cat savepoint.py
from __future__ import absolute_import, division, print_function, unicode_literals
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
import os

if os.path.exists("test.db"): os.unlink("test.db")
engine = create_engine("sqlite:///test.db", echo=True)
session_factory = sessionmaker(bind=engine)
session = session_factory()
session.execute("create table foo (val int)")

session.execute("insert into foo values (1)")
session.begin_nested()
session.execute("insert into foo values (2)")
session.rollback()
session.execute("insert into foo values (3)")
session.commit()
print(repr(session.execute("select * from foo")))

$ python savepoint.py
2015-10-22 17:12:28,535 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2015-10-22 17:12:28,536 INFO sqlalchemy.engine.base.Engine ()
2015-10-22 17:12:28,536 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2015-10-22 17:12:28,537 INFO sqlalchemy.engine.base.Engine ()
2015-10-22 17:12:28,539 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2015-10-22 17:12:28,540 INFO sqlalchemy.engine.base.Engine create table foo (val int)
2015-10-22 17:12:28,541 INFO sqlalchemy.engine.base.Engine ()
2015-10-22 17:12:28,556 INFO sqlalchemy.engine.base.Engine insert into foo values (1)
2015-10-22 17:12:28,557 INFO sqlalchemy.engine.base.Engine ()
2015-10-22 17:12:28,558 INFO sqlalchemy.engine.base.Engine SAVEPOINT sa_savepoint_1
2015-10-22 17:12:28,559 INFO sqlalchemy.engine.base.Engine ()
2015-10-22 17:12:28,569 INFO sqlalchemy.engine.base.Engine insert into foo values (2)
2015-10-22 17:12:28,570 INFO sqlalchemy.engine.base.Engine ()
2015-10-22 17:12:28,570 INFO sqlalchemy.engine.base.Engine ROLLBACK TO SAVEPOINT sa_savepoint_1
2015-10-22 17:12:28,571 INFO sqlalchemy.engine.base.Engine ()
Traceback (most recent call last):
  File "savepoint.py", line 15, in <module>
    session.rollback()
  File "/home/brian/savepoint/venv/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 765, in rollback
    self.transaction.rollback()
  File "/home/brian/savepoint/venv/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 419, in rollback
    transaction._rollback_impl()
  File "/home/brian/savepoint/venv/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 449, in _rollback_impl
    t[1].rollback()
  File "/home/brian/savepoint/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1563, in rollback
    self._do_rollback()
  File "/home/brian/savepoint/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1625, in _do_rollback
    self._savepoint, self._parent)
  File "/home/brian/savepoint/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 717, in _rollback_to_savepoint_impl
    self.engine.dialect.do_rollback_to_savepoint(self, name)
  File "/home/brian/savepoint/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 441, in do_rollback_to_savepoint
    connection.execute(expression.RollbackToSavepointClause(name))
  File "/home/brian/savepoint/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 914, in execute
    return meth(self, multiparams, params)
  File "/home/brian/savepoint/venv/local/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 323, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/home/brian/savepoint/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1010, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/home/brian/savepoint/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1146, in _execute_context
    context)
  File "/home/brian/savepoint/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1341, in _handle_dbapi_exception
    exc_info
  File "/home/brian/savepoint/venv/local/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 199, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb)
  File "/home/brian/savepoint/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1139, in _execute_context
    context)
  File "/home/brian/savepoint/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 450, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such savepoint: sa_savepoint_1 [SQL: u'ROLLBACK TO SAVEPOINT sa_savepoint_1']
~~~

But from the sqlite3 CLI tool:

~~~
$ sqlite3 test.db
SQLite version 3.8.2 2013-12-06 14:53:30
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> insert into foo values (1);
sqlite> SAVEPOINT sa_savepoint_1;
sqlite> insert into foo values (2);
sqlite> ROLLBACK TO SAVEPOINT sa_savepoint_1;
sqlite> select * from foo;
1
sqlite> commit;
sqlite>
~~~

It's nothing to do with creating the table, because if you run the python script again with these two lines commented out, you get the same error.

~~~
#if os.path.exists("test.db"): os.unlink("test.db")
...
#session.execute("create table foo (val int)")
~~~

Environment:

* ubuntu 14.04.3 x86_64
* pip picked up the following versions:

~~~
Downloading/unpacking PyMySQL (from -r requirements.txt (line 1))
  Downloading PyMySQL-0.6.7-py2.py3-none-any.whl (69kB): 69kB downloaded
Downloading/unpacking SQLAlchemy (from -r requirements.txt (line 2))
  Downloading SQLAlchemy-1.0.9.tar.gz (4.7MB): 4.7MB downloaded
~~~

~~~
>>> import sqlite3 as s
>>> s.sqlite_version
'3.8.2'

$ dpkg-query -l | grep -i sqlite
ii  libaprutil1-dbd-sqlite3:amd64       1.5.3-1                          amd64        Apache Portable Runtime Utility Library - SQLite3 Driver
ii  libsqlite3-0:amd64                  3.8.2-1ubuntu2.1                 amd64        SQLite 3 shared library
ii  sqlite3                             3.8.2-1ubuntu2.1                 amd64        Command line interface for SQLite 3
~~~

[Sqlalchemy-tickets] Issue #3560: Problem using custom DateTime type with Postgres (zzzeek/sqlalchemy)

[Peter T. <iss...@bi...>]

New issue 3560: Problem using custom DateTime type with Postgres
https://bitbucket.org/zzzeek/sqlalchemy/issues/3560/problem-using-custom-datetime-type-with

Peter Taylour:

I haven't used SqlAlchemy much beyond the Flask-SqlAlchemy API, so apologies if this is trivial--and not a bug.

The issue occurs when filtering by a datetime field, which uses a Custom Type UTCDateTime.
I can reproduce the problem on Postgres but not with SQLite.

Behaviour can be reproduced here https://github.com/PTaylour/SqlAlchemy-Postgres-DateTime-Issue/tree/master
(Apologies again for the Flask dependancy)

postgres version:  `PostgreSQL 9.4.5 on x86_64-apple-darwin14.5.0, compiled by Apple LLVM version 7.0.0 (clang-700.0.72), 64-bit`

tested sqlalchemy versions: `1.0.9`, `SQLAlchemy==1.1.0b1.dev0`

 with `psycopg2==2.6.1`


I'm using a TypeDecorator to enforce UTC timezoneaware datetimes, instead of using the standard DateTime type.

```
class UTCDateTime(types.TypeDecorator):
    impl = types.DateTime

    def process_bind_param(self, value, engine):
        if value is not None:
            return value.astimezone(utc)

    def process_result_value(self, value, engine):
        if value is not None:
            return value.replace(tzinfo=utc)
``` 

This works with postgres when simply storing and accessing the datetime field.

However, it behaves unexpected when filtering on the datetime field.

## In the repo https://github.com/PTaylour/SqlAlchemy-Postgres-DateTime-Issue/
A script that adds a few rows to a table using the SQLalchemy type `DateTime` and a table using the custom type `UTCDateTime`

It then performs a `SELECT` `WHERE` the datetime filed is less than the value of another row.

This performs correctly using sqlite (you can switch to sqlite in the config file `hello.cfg`).

But using a postres database the filter does not perform as I would expect.

[Sqlalchemy-tickets] Issue #3559: Document bindparam usage in join condition docs (zzzeek/sqlalchemy)

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

New issue 3559: Document bindparam usage in join condition docs
https://bitbucket.org/zzzeek/sqlalchemy/issues/3559/document-bindparam-usage-in-join-condition

ctolsen:

I've been using sqlalchemy for a fairly long time, but never touched upon using the `bindparam` concept in a join condition. It's an incredibly useful concept that saves me a lot of headaches, and I'm sad I didn't discover it before.

Maybe it could be documented at more accessible stage, somewhere around [here](http://docs.sqlalchemy.org/en/rel_1_0/orm/join_conditions.html#specifying-alternate-join-conditions)? A use case where you can specify a bindparam for `Address.city` instead of using the static `Boston` value would be great.

I had a stab at writing an update, but I'm a bit out of water unfortunately.

[Sqlalchemy-tickets] Issue #3558: Joining Subquery on Relationship (zzzeek/sqlalchemy)

[Lukas S. <iss...@bi...>]

New issue 3558: Joining Subquery on Relationship
https://bitbucket.org/zzzeek/sqlalchemy/issues/3558/joining-subquery-on-relationship

Lukas Siemon:

Consider the following sqlalchemy generated query.

```
#!sql
SELECT
   venue.id AS venue_id   
FROM
   venue   
WHERE
   (
      EXISTS (
         SELECT
            1   
         FROM
            venue AS alias1 
         JOIN
            venue_to_label AS venue_to_label_1 
               ON alias1.id = venue_to_label_1.venue_id 
         JOIN
            label 
               ON label.id = venue_to_label_1.label_id   
         WHERE
            label.id = :id_1 
            AND venue.id = alias1.id
      )
   ) 
   AND (
      EXISTS (
         SELECT
            1   
         FROM
            venue AS alias2 
         JOIN
            venue_to_label AS venue_to_label_2 
               ON alias2.id = venue_to_label_2.venue_id 
         JOIN
            label 
               ON label.id = venue_to_label_2.label_id   
         WHERE
            label.name = :name_1 
            AND venue.id = alias2.id
      )
   )
```
I'm trying to find all venues that have a label attached with a specific id and a label attached with a specific name. The sub-queries are necessary since this is a *_to_many relationship, but instead of joining them on the venue.id we could join them on venue_to_label.venue_id and move the relationship join into the main query. This would shorten the query by one join.

Is there an easy way to achieve this? I've attached the code generating the sql query below.

```
#!python

import unittest
from sqlalchemy import Table, Column, Integer, ForeignKey, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, aliased
from flask import Flask
from flask.ext.sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = (
    'postgres://postgres:password@localhost:5432/tmp')
db = SQLAlchemy(app)

Base = declarative_base()

# many (venue) <-> many (label) mapping table
venue_to_label = Table(
    'venue_to_label', db.metadata,
    Column('venue_id', Integer, ForeignKey('venue.id'), primary_key=True),
    Column('label_id', Integer, ForeignKey('label.id'), primary_key=True)
)


class Label(db.Model):
    __tablename__ = 'label'
    id = Column(Integer, primary_key=True, nullable=False)
    name = Column(String(254))


class Venue(db.Model):
    id = Column(Integer, primary_key=True, nullable=False)
    labels = relationship(Label, secondary=venue_to_label)

db.create_all()


class TestJoinSubquery(unittest.TestCase):

    def test_join_subquery(self):
        query = Venue.query

        # subquery one
        alias = aliased(Venue, name="alias1")
        subquery = db.session.query(alias)
        subquery = subquery.join(alias.labels)
        subquery = subquery.filter(Label.id == 3)
        subquery = subquery.filter(Venue.id == alias.id)
        query = query.filter(subquery.exists())

        # subquery two
        alias = aliased(Venue, name="alias2")
        subquery = db.session.query(alias)
        subquery = subquery.join(alias.labels)
        subquery = subquery.filter(Label.name == "label_name")
        subquery = subquery.filter(Venue.id == alias.id)
        query = query.filter(subquery.exists())

        print query
        print query.all()

```

[Sqlalchemy-tickets] Issue #3557: Hybrid attributes are under wrong name in result objects (zzzeek/sqlalchemy)

[Łukasz F. <iss...@bi...>]

New issue 3557: Hybrid attributes are under wrong name in result objects
https://bitbucket.org/zzzeek/sqlalchemy/issues/3557/hybrid-attributes-are-under-wrong-name-in

Łukasz Fidosz:

When querying hybrid property it uses underlying column name instead of hybrid property name as attribute name in result object.

So code bellow (please note the commented line on the bottom):
```
#!python

from sqlalchemy import Integer, Column
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.hybrid import hybrid_property
from sqlalchemy.orm import sessionmaker, scoped_session
from sqlalchemy.types import TypeDecorator, Unicode, UnicodeText

Base = declarative_base()

class SomeClass(Base):
    __tablename__ = 'some_table'
    id = Column(Integer(), primary_key=True)
    raw_foo = Column('foo', Integer(), nullable=False, default=0)

    @hybrid_property
    def foo(self):
        if False: # some conditional code
            return self.raw_foo * 10

        return self.raw_foo

    @foo.expression
    def country(cls):
        return cls.raw_foo

    @foo.setter
    def country(self, value):
        self.raw_foo = value 

engine = create_engine('sqlite://', echo=True)
Session = scoped_session(sessionmaker(bind=engine))
session = Session()
Base.metadata.create_all(engine)

session.commit()
session.add_all([
    SomeClass(id=1, foo=1),
    SomeClass(id=2, foo=10),
    SomeClass(id=3, foo=30),
])
session.commit()

q = session.query(SomeClass.id, SomeClass.foo)
item = q.first()
# the commented line will work so apparently it uses
# underlying column as a name:
# print item.raw_foo
print item.foo
```

Will crash like this:
```
#!python

Traceback (most recent call last):
  File "/home/virhilo/sqlalchemy_hybrids_error.py", line 47, in <module>
    print item.foo
AttributeError: 'result' object has no attribute 'foo'
```

[Sqlalchemy-tickets] Issue #3556: update executemany() enhancement breaks versioning (zzzeek/sqlalchemy)

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

New issue 3556: update executemany() enhancement breaks versioning
https://bitbucket.org/zzzeek/sqlalchemy/issues/3556/update-executemany-enhancement-breaks

Mike Bayer:


```
#!diff

diff --git a/test/orm/test_versioning.py b/test/orm/test_versioning.py
index d46799c..67d580e 100644
--- a/test/orm/test_versioning.py
+++ b/test/orm/test_versioning.py
@@ -952,6 +952,46 @@ class ServerVersioningTest(fixtures.MappedTest):
             )
         self.assert_sql_execution(testing.db, sess.flush, *statements)
 
+    def test_multi_update(self):
+        sess = self._fixture()
+
+        f1 = self.classes.Foo(value='f1')
+        f2 = self.classes.Foo(value='f2')
+        f3 = self.classes.Foo(value='f3')
+        sess.add_all([f1, f2, f3])
+        sess.flush()
+
+        f1.value = 'f1a'
+        f2.value = 'f2a'
+        f3.value = 'f3a'
+
+        statements = [
+            # note that the assertsql tests the rule against
+            # "default" - on a "returning" backend, the statement
+            # includes "RETURNING"
+            CompiledSQL(
+                "UPDATE version_table SET version_id=2, value=:value "
+                "WHERE version_table.id = :version_table_id AND "
+                "version_table.version_id = :version_table_version_id",
+                lambda ctx: [
+                    {
+                        "version_table_id": 1,
+                        "version_table_version_id": 1, "value": "f2"}]
+            )
+        ]
+        if not testing.db.dialect.implicit_returning:
+            # DBs without implicit returning, we must immediately
+            # SELECT for the new version id
+            statements.append(
+                CompiledSQL(
+                    "SELECT version_table.version_id "
+                    "AS version_table_version_id "
+                    "FROM version_table WHERE version_table.id = :param_1",
+                    lambda ctx: [{"param_1": 1}]
+                )
+            )
+        self.assert_sql_execution(testing.db, sess.flush, *statements)
+
     def test_delete_col(self):
         sess = self._fixture()
 

```

PG:

```
#!


File "/Users/classic/dev/sqlalchemy/test/../lib/sqlalchemy/engine/result.py", line 1026, in fetchone
    self.cursor, self.context)
  File "/Users/classic/dev/sqlalchemy/test/../lib/sqlalchemy/engine/base.py", line 1341, in _handle_dbapi_exception
    exc_info
  File "/Users/classic/dev/sqlalchemy/test/../lib/sqlalchemy/util/compat.py", line 199, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb)
  File "/Users/classic/dev/sqlalchemy/test/../lib/sqlalchemy/engine/result.py", line 1017, in fetchone
    row = self._fetchone_impl()
  File "/Users/classic/dev/sqlalchemy/test/../lib/sqlalchemy/engine/result.py", line 898, in _fetchone_impl
    return self.cursor.fetchone()
ProgrammingError: (psycopg2.ProgrammingError) no results to fetch

```

[Sqlalchemy-tickets] Issue #3555: Extract a record's field (zzzeek/sqlalchemy)

[Andrey S. <iss...@bi...>]

New issue 3555: Extract a record's field
https://bitbucket.org/zzzeek/sqlalchemy/issues/3555/extract-a-records-field

Andrey Semenov:

I need to construct a query like
```
#!sql
SELECT (jsonb_each('{"a": 1, "b": 2}')).key

```
`jsonb_each()` returns a RECORD type which in turn has it's own fields named `key` and `value`. Yes, in PostgreSQL it MUST be also wrapped into braces, because of extraction and executing precedence (or it will try to select a `key` column from the `jsonb_each()` column - that leads to SQL engine error raised). It's not obvious how to query those fields while constructing a query (or make a dot-notated reference to a custom sub-field when this capability is provided by an engine).

Also, dialects.util.dottedgetter does not work with func.* (returns None if called against)

[Sqlalchemy-tickets] Issue #3554: zxjdbc driver url is 404 (zzzeek/sqlalchemy)

[John V. <iss...@bi...>]

New issue 3554: zxjdbc driver url is 404
https://bitbucket.org/zzzeek/sqlalchemy/issues/3554/zxjdbc-driver-url-is-404

John Vandenberg:

http://docs.sqlalchemy.org/en/rel_1_0/dialects/oracle.html#module-sqlalchemy.dialects.oracle.zxjdbc

links to

http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/index.html.

the trailing '.' is a problem.

without the period, it redirects to

http://www.oracle.com/technetwork/database/application-development/index-099369.html

that page suggests the drivers are now at

http://www.oracle.com/technetwork/database/features/jdbc/index-091264.html

while on the topic, would be worth checking whether the needed features come with the instantclient jdbc driver available at http://www.oracle.com/technetwork/topics/linuxsoft-082809.html

[Sqlalchemy-tickets] Issue #3553: Wrong or missing reference on sqlalchemy.orm.properties.ColumnProperty (zzzeek/sqlalchemy)

[Maurice S. <iss...@bi...>]

New issue 3553: Wrong or missing reference on sqlalchemy.orm.properties.ColumnProperty
https://bitbucket.org/zzzeek/sqlalchemy/issues/3553/wrong-or-missing-reference-on

Maurice Schleußinger:

In the current docs:

Both `sqlalchemy.orm.properties.ColumnProperty` and `sqlalchemy.orm.properties.RelationshipProperty` claim to have `sqlalchemy.orm.interfaces.StrategizedProperty` as base class (see http://docs.sqlalchemy.org/en/latest/orm/internals.html#sqlalchemy.orm.properties.ColumnProperty). 

However `sqlalchemy.orm.interfaces.StrategizedProperty` is neither linked nor to be found with the search: http://docs.sqlalchemy.org/en/latest/search.html?q=sqlalchemy.orm.interfaces.StrategizedProperty&check_keywords=yes&area=default

[Sqlalchemy-tickets] Issue #3552: Exception when pickle.dumps(DeclarativeObject) with sqlalchemy.ext.mutable.Mutable field (zzzeek/sqlalchemy)

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

New issue 3552: Exception when pickle.dumps(DeclarativeObject) with sqlalchemy.ext.mutable.Mutable field
https://bitbucket.org/zzzeek/sqlalchemy/issues/3552/exception-when-pickledumps

dwt:

Hi there,

today we noticed an explosion when we tried to pickle.dumps one of our model objects with an error like this:

```
pickle.PicklingError: Can't pickle <function remove at 0x107afcb90>: it's not found as weakref.remove
```

Googling we found this: https://groups.google.com/forum/#!topic/sqlalchemy/b3eoWwhKriI which lead us to reduce the issue to this sample code which seems to be pretty minimal in showing the error:

```python
#!/usr/bin/env python

import json
from sqlalchemy import types



# Basically stolen from
# http://docs.sqlalchemy.org/en/rel_0_8/core/types.html#marshal-json-strings
class JSONEncodedDict(types.TypeDecorator):
    """Represents an immutable structure as a json-encoded string.

    Usage::
        JSONEncodedDict(255)

    Made mutation aware by the MutableDict below!

    Defaults to an empty dict if read as NULL from the DB for migration friendliness.
    """

    impl = types.VARCHAR
    python_type = dict

    def process_bind_param(self, value, dialect):
        if value is not None:
            value = json.dumps(value)

        return value

    def process_result_value(self, value, dialect):
        if value is not None:
            value = json.loads(value, object_hook=dict)
        else:
            value = {}
        return value



from sqlalchemy.ext.mutable import Mutable

class MutableDict(Mutable, dict):
    @classmethod
    def coerce(cls, key, value):
        "Convert plain dictionaries to MutableDict."

        if not isinstance(value, MutableDict):
            if isinstance(value, dict):
                return MutableDict(value)

            # this call will raise ValueError
            return Mutable.coerce(key, value)
        else:
            return value

    def __setitem__(self, key, value):
        "Detect dictionary set events and emit change events."

        dict.__setitem__(self, key, value)
        self.changed()

    def __delitem__(self, key):
        "Detect dictionary del events and emit change events."

        dict.__delitem__(self, key)
        self.changed()

MutableDict.associate_with(JSONEncodedDict)


from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base(constructor=None)

from sqlalchemy import Column, Integer

class User(Base):
    __tablename__ = 'user'
    
    id = Column("iduser", Integer, primary_key=True)
    settings = Column(JSONEncodedDict(4096), default=dict)


from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session, sessionmaker

engine = create_engine('sqlite://')
DBSession = scoped_session(sessionmaker())
DBSession.configure(bind=engine)
Base.metadata.bind = engine
Base.metadata.create_all()

import pickle

user = User()
DBSession.add(user)
DBSession.flush()
pickle.dumps(user)
print('Worked')
```

This still errors in the latest version from pypi (1.0.8).

What can you make from this? It looks like an error to us - but maybe we're using mutable wrong?

[Sqlalchemy-tickets] Issue #3551: Would it be possible to have association_proxy allow the 'info' param, similar to columns and relationships? (zzzeek/sqlalchemy)

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

New issue 3551: Would it be possible to have association_proxy allow the 'info' param, similar to columns and relationships?
https://bitbucket.org/zzzeek/sqlalchemy/issues/3551/would-it-be-possible-to-have

dieselmachine:

I realize this probably isn't feasible right now, but it seems like it could be implemented easily alongside issue #3423. 

[Sqlalchemy-tickets] Issue #3550: typo, begin_nested should not be called SAVEPOINT (zzzeek/sqlalchemy)

[Jean-Sébastien S. <iss...@bi...>]

New issue 3550: typo, begin_nested should not be called SAVEPOINT
https://bitbucket.org/zzzeek/sqlalchemy/issues/3550/typo-begin_nested-should-not-be-called

Jean-Sébastien Suzanne:

See http://docs.sqlalchemy.org/en/latest/orm/session_transaction.html#using-savepoint.

A save point can be released, it is not a commit or rollback. Or in the documentation, the ``begin_nested`` must be committed or rollbacked.

[Sqlalchemy-tickets] Issue #3549: sqlalchemy.exc.UnsupportedCompilationError JSONB (zzzeek/sqlalchemy)

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

New issue 3549: sqlalchemy.exc.UnsupportedCompilationError JSONB
https://bitbucket.org/zzzeek/sqlalchemy/issues/3549/sqlalchemyexcunsupportedcompilationerror

flashmob:

Trying to compile query:


```
#!python

_update = str(S.__table__.update().where(and_(*_filters)).values({col.name: func.jsonb_concat(col,func.cast(bindparam(bind_name), JSONB))}))
```

and getting error:


```
#!python

sqlalchemy.exc.UnsupportedCompilationError: Compiler <sqlalchemy.sql.compiler.GenericTypeCompiler object at 0x108cd6110> can't render element of type <class 'sqlalchemy.dialects.postgresql.json.JSONB'>

```

if I remove cast then sqlalche,y trying to cast dict to HSTORE

Responsible: zzzeek

[Sqlalchemy-tickets] Issue #3548: detect oracle pure-quoted lowercase names (zzzeek/sqlalchemy)

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

New issue 3548: detect oracle pure-quoted lowercase names
https://bitbucket.org/zzzeek/sqlalchemy/issues/3548/detect-oracle-pure-quoted-lowercase-names

Mike Bayer:


```
#!diff

diff --git a/test/dialect/test_oracle.py b/test/dialect/test_oracle.py
index e080568..32dce9c 100644
--- a/test/dialect/test_oracle.py
+++ b/test/dialect/test_oracle.py
@@ -5,6 +5,7 @@ from sqlalchemy.testing import eq_
 from sqlalchemy import *
 from sqlalchemy import types as sqltypes, exc, schema
 from sqlalchemy.sql import table, column
+from sqlalchemy.sql.elements import quoted_name
 from sqlalchemy.testing import fixtures, AssertsExecutionResults, AssertsCompiledSQL
 from sqlalchemy import testing
 from sqlalchemy.util import u, b
@@ -1859,6 +1860,27 @@ class TableReflectionTest(fixtures.TestBase):
         tbl = Table('test_compress', m2, autoload=True)
         assert tbl.dialect_options['oracle']['compress'] == "OLTP"
 
+    @testing.provide_metadata
+    def test_reflect_lowercase_forced_tables(self):
+        metadata = self.metadata
+
+        t1 = Table(quoted_name('t1', quote=True), metadata, 
+             Column('id', Integer, primary_key=True),
+        )
+        t2 = Table(quoted_name('t2', quote=True), metadata, 
+             Column('id', Integer, primary_key=True),
+             Column('t1id', ForeignKey('t1.id'))
+        )
+        metadata.create_all()
+
+        m2 = MetaData(testing.db)
+        t2_ref = Table(quoted_name('t2', quote=True), m2, autoload=True)
+        t1_ref = m2.tables['t1']
+        assert t2_ref.c.t1id.references(t1_ref.c.id)
+
+        m3 = MetaData(testing.db)
+        m3.reflect(only=lambda name, m: name.lower() in ('t1', 't2'))
+        assert m3.tables['t2'].c.t1id.references(m3.tables['t1'].c.id)
 

```

at some point this would apply to firebird as well...

[Sqlalchemy-tickets] Issue #3547: MySQL 5.7 JSON type support (zzzeek/sqlalchemy)

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

New issue 3547: MySQL 5.7 JSON type support
https://bitbucket.org/zzzeek/sqlalchemy/issues/3547/mysql-57-json-type-support

Mathieu Rodic:

MySQL came up with an implementation of the JSON type : [https://dev.mysql.com/doc/refman/5.7/en/json.html](https://dev.mysql.com/doc/refman/5.7/en/json.html)

It would be nice to have it available in `sqlalchemy.dialects.mysql`, in the same way its PostgreSQL counterpart can be imported from `sqlalchemy.dialects.postgresql`.

I wouldn't mind working on the implementation, if given the required elements (some exceptions are poorly documented, e.g.: `sqlalchemy.exc.CompileError: (in table 'entities', column 'data'): Compiler <sqlalchemy.dialects.mysql.base.MySQLTypeCompiler object at 0x7f1fcd4a0d10> can't render element of type <class '__main__.JSON'>
`).

[Sqlalchemy-tickets] Issue #3546: Query object having multiple entities fails when trying to join (zzzeek/sqlalchemy)

[Andrey S. <iss...@bi...>]

New issue 3546: Query object having multiple entities fails when trying to join
https://bitbucket.org/zzzeek/sqlalchemy/issues/3546/query-object-having-multiple-entities

Andrey Semenov:


```
#!python

Session.query(Address, Order, OrderLog).outerjoin(OrderLog, Order.id == OrderLog.order_id)
```
This code produces a query like:


```
#!SQL
SELECT ...
FROM orders, addresses LEFT OUTER JOIN order_logs ON orders.id = order_logs.order_id
```
This leads to an error at server side:

```
#!text

ProgrammingError: (psycopg2.ProgrammingError) invalid reference to FROM-clause entry for table "orders"
LINE 3: ...M orders, addresses LEFT OUTER JOIN order_logs ON orders.id ...
                                                             ^
```

so, the autowiring joins (auto-grouping them by entity relationship given at join_clauses) does not work. The docs don't say how to explicitly point the left part of join.

The query I'm trying to build up is like:

```
#!SQL

SELECT ...

FROM entity_1 JOIN entity_1_1 ON ..., entity_2 JOIN entity_2_1 ON ..., entity_3, entity_4 JOIN entity_4_1 ON ...
```

Current query builder chains all .join() of Query objects to the first entity of the `_entities` list

[Sqlalchemy-tickets] Issue #3545: filter_by danger if accidentally using "and" (zzzeek/sqlalchemy)

[Brian C. <iss...@bi...>]

New issue 3545: filter_by danger if accidentally using "and"
https://bitbucket.org/zzzeek/sqlalchemy/issues/3545/filter_by-danger-if-accidentally-using-and

Brian Candler:

There is some dangerous behaviour with filter_by which I think warrants a warning in the documentation. [Tested with SQLAlchemy 0.9.9]

# WRONG
Foo.filter_by(Foo.ref == ref and Foo.type == type and Foo.tag == tag)

# RIGHT
Foo.filter_by(Foo.ref == ref, Foo.type == type, Foo.tag == tag)
Foo.filter_by((Foo.ref == ref) & (Foo.type == type) & (Foo.tag == tag))
from sqlalchemy import and_
Foo.filter_by(and_(Foo.ref == ref, Foo.type == type, Foo.tag == tag))

The incorrect form is silently accepted and apparently works, but actually behaves the same as
Foo.filter_by(Foo.ref == ref)
i.e. part of the query you expected to be generated is quietly dropped off.

At http://docs.sqlalchemy.org/en/rel_1_0/orm/query.html it says:
"Multiple criteria are joined together by AND:"
followed by a valid example, but I think it should clarify that you don't actually use the python "and" operator.

[Sqlalchemy-tickets] Issue #3544: from_self() with contains_eager (zzzeek/sqlalchemy)

[Lukas S. <iss...@bi...>]

New issue 3544: from_self() with contains_eager
https://bitbucket.org/zzzeek/sqlalchemy/issues/3544/from_self-with-contains_eager

Lukas Siemon:

The test case below generates the following query:

```
#!sql
SELECT
   label_alias.id,
   label_alias.name,
   anon_1.venue_id,
   anon_1.venue_name   
FROM
   label AS label_alias,  -- this should come from the inner query
   (SELECT
      venue.id AS venue_id,
      venue.name AS venue_name   
   FROM
      venue 
   JOIN
      venue_to_label AS venue_to_label_1 
         ON venue.id = venue_to_label_1.venue_id 
   JOIN
      label AS label_alias 
         ON label_alias.id = venue_to_label_1.label_id   
   WHERE
      label_alias.name IN (
         %(
            name_1
         )s
      )) AS anon_1
```

However, based on the alchemy query, I'd expect the label_alias to come from the inner sql query instead of from the outer. Is there an easy way to resolve this?

To give some background: We're using this strategy generically with joinedload instead of contains_eager to serialize all our queries to json. It works really well, however now the requirement came up to only return "certain joins from the joinedload".

I've tested the issue with
SQLAlchemy==1.0.8
Flask==0.10.1
Flask-SQLAlchemy==2.0

Test case:
```
#!python

import unittest
from sqlalchemy import (
    Table, Column, Integer, ForeignKey, String, func)
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import (relationship, aliased, contains_eager)
from flask import Flask
from flask.ext.sqlalchemy import SQLAlchemy


# -- create all the database models
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = ("sqlite://")
db = SQLAlchemy(app)

Base = declarative_base()

venue_to_label = Table(
    'venue_to_label', db.metadata,
    Column('venue_id', Integer, ForeignKey('venue.id'), primary_key=True),
    Column('label_id', Integer, ForeignKey('label.id'), primary_key=True)
)


class Label(db.Model):
    __tablename__ = 'label'
    id = Column(Integer, primary_key=True, nullable=False)
    name = Column(String(254))


class Venue(db.Model):
    __tablename__ = 'venue'
    id = Column(Integer, primary_key=True, nullable=False)
    name = Column(String(254))
    labels = relationship(Label, secondary=venue_to_label)

db.drop_all()
db.create_all()


class TestContainsEager(unittest.TestCase):

    def test_contains_eager(self):
        query = Venue.query
        # filtered join
        label_alias = aliased(Label, name="label_alias")
        query = query.join(label_alias, Venue.labels)
        query = query.filter(label_alias.name.in_(["label1"]))

        # together with a windowing function this allows to correctly apply
        # limit and order to the query, needed since labels is a *-to-many join
        query = query.from_self()

        # define to load from the inner query
        query = query.options(
            contains_eager(
                'labels', alias=label_alias
            ).load_only('id', 'name')
        )

        # contains_eager does not pick up the alias from the inner query
        import sqlalchemy.dialects.postgresql as postgresql
        print query.statement.compile(dialect=postgresql.dialect())

```

[Sqlalchemy-tickets] Issue #3543: support new Oracle 12c OFFSET / TOP N (zzzeek/sqlalchemy)

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

New issue 3543: support new Oracle 12c OFFSET / TOP N
https://bitbucket.org/zzzeek/sqlalchemy/issues/3543/support-new-oracle-12c-offset-top-n

Mike Bayer:

Let's support at least a subset of https://oracle-base.com/articles/12c/row-limiting-clause-for-top-n-queries-12cr1 to fulfill LIMIT/OFFSET.

This is a big enough deal that I'd like to at some point get an Oracle 12c CI environment going.

There should be a manual flag to select either style, and if set to None it will auto-select based on detected Oracle version.