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

[Sqlalchemy-tickets] Issue #3030: Firebird dialect strange behaivour wuth uppercase table names. (zzzeek/sqlalchemy)

[Evgeny U. <iss...@bi...>]

New issue 3030: Firebird dialect strange behaivour wuth uppercase table names.
https://bitbucket.org/zzzeek/sqlalchemy/issue/3030/firebird-dialect-strange-behaivour-wuth

Evgeny Uspensky:

When i use field/table names written in uppercase with **firebird** dialect, selecting stops working.
I use 

Python3.3,
fdb==1.4,
sqlalchemy==0.9.4

this is also reproducable with python 2.7.


```
#!python

from sqlalchemy.ext.declarative.api import declarative_base
from sqlalchemy.schema import MetaData, Column
from sqlalchemy.types import Integer

metadata = MetaData()
DeclarativeBase = declarative_base(metadata=metadata)


class Entity_U(DeclarativeBase):
    __tablename__ = 'ENTITY_U'
    id = Column('ENTITY_ID', Integer, primary_key=True)


class Entity_L(DeclarativeBase):
    __tablename__ = 'entity_l'
    id = Column('entity_id', Integer, primary_key=True)


if __name__ == '__main__':
    from sqlalchemy import create_engine
    from sqlalchemy.orm import sessionmaker, scoped_session

    engine = create_engine('firebird://sysdba:123456@localhost/var/local/fdb/test.fdb')
    session = scoped_session(sessionmaker(engine))
    metadata.bind = engine
    metadata.drop_all()
    metadata.create_all()
    session.add(Entity_U(id=1))
    session.commit()

    session.add(Entity_L(id=1))
    session.commit()

    if 1:
        session.query(Entity_U).first()
    else:
        session.query(Entity_L).first()
```
this code crashes with

```
Traceback (most recent call last):
  File "/home/eugeny/work/ve33/lib/python3.3/site-packages/sqlalchemy/engine/result.py", line 70, in __getitem__
    processor, obj, index = self._keymap[key]
KeyError: Column('ENTITY_ID', Integer(), table=<ENTITY_U>, primary_key=True, nullable=False)

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/home/eugeny/work/fb/schema.py", line 55, in <module>
    session.query(Entity_U).first()
  File "/home/eugeny/work/ve33/lib/python3.3/site-packages/sqlalchemy/orm/query.py", line 2333, in first
    ret = list(self[0:1])
  File "/home/eugeny/work/ve33/lib/python3.3/site-packages/sqlalchemy/orm/query.py", line 2200, in __getitem__
    return list(res)
  File "/home/eugeny/work/ve33/lib/python3.3/site-packages/sqlalchemy/orm/loading.py", line 72, in instances
    rows = [process[0](row, None) for row in fetch]
  File "/home/eugeny/work/ve33/lib/python3.3/site-packages/sqlalchemy/orm/loading.py", line 72, in <listcomp>
    rows = [process[0](row, None) for row in fetch]
  File "/home/eugeny/work/ve33/lib/python3.3/site-packages/sqlalchemy/orm/loading.py", line 360, in _instance
    tuple([row[column] for column in pk_cols])
  File "/home/eugeny/work/ve33/lib/python3.3/site-packages/sqlalchemy/orm/loading.py", line 360, in <listcomp>
    tuple([row[column] for column in pk_cols])
  File "/home/eugeny/work/ve33/lib/python3.3/site-packages/sqlalchemy/engine/result.py", line 72, in __getitem__
    processor, obj, index = self._parent._key_fallback(key)
  File "/home/eugeny/work/ve33/lib/python3.3/site-packages/sqlalchemy/engine/result.py", line 332, in _key_fallback
    expression._string_or_unprintable(key))
sqlalchemy.exc.NoSuchColumnError: "Could not locate column in row for column 'ENTITY_U.ENTITY_ID'"
```
when i select from table defined with uppercase name, but it works fine for lowercase table.

also there is no problem with other dialects, it is only firebird issue.

[Sqlalchemy-tickets] Issue #3029: fully self equated join condition? (zzzeek/sqlalchemy)

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

New issue 3029: fully self equated join condition?
https://bitbucket.org/zzzeek/sqlalchemy/issue/3029/fully-self-equated-join-condition

Mike Bayer:


```
#!python

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

Base = declarative_base()

class Category(Base):
    __tablename__ = 'categories'

    id = Column(SmallInteger, primary_key=True)
    path = Column(String, unique=True, nullable=False)

    all_subcats = relationship('Category', viewonly=True,
                               primaryjoin=path == remote(foreign(path)).concat('%')
                               )

print Session().query(Category).options(joinedload("all_subcats"))
```

patch:

```
#!diff


diff --git a/lib/sqlalchemy/orm/relationships.py b/lib/sqlalchemy/orm/relationships.py
index 311fba4..ed0a9a5 100644
--- a/lib/sqlalchemy/orm/relationships.py
+++ b/lib/sqlalchemy/orm/relationships.py
@@ -2405,6 +2405,10 @@ class JoinCondition(object):
                     self.direction = ONETOMANY
                 elif manytoone_local and not onetomany_local:
                     self.direction = MANYTOONE
+                elif self_equated.intersection(self.foreign_key_columns):
+                    self.direction = ONETOMANY
+                elif self_equated and not self_equated.intersection(self.foreign_key_columns):
+                    self.direction = MANYTOONE
                 else:
                     raise sa_exc.ArgumentError(
                         "Can't determine relationship"
```

[Sqlalchemy-tickets] Issue #3028: support text() in Index that's already table bound (zzzeek/sqlalchemy)

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

New issue 3028: support text() in Index that's already table bound
https://bitbucket.org/zzzeek/sqlalchemy/issue/3028/support-text-in-index-thats-already-table

Mike Bayer:

no reason this shouldn't be possible


```
#!python

Table('x', metadata, Column('x', Integer),
    Index('asdf', text("func.foo.bar(x)"))
)
```

[Sqlalchemy-tickets] Issue #3027: autoload_with shoudl imply autoload=True (zzzeek/sqlalchemy)

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

New issue 3027: autoload_with shoudl imply autoload=True
https://bitbucket.org/zzzeek/sqlalchemy/issue/3027/autoload_with-shoudl-imply-autoload-true

Mike Bayer:

[Sqlalchemy-tickets] Issue #3026: 'test plain returns' noise when setting echo=True (zzzeek/sqlalchemy)

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

New issue 3026: 'test plain returns' noise when setting echo=True
https://bitbucket.org/zzzeek/sqlalchemy/issue/3026/test-plain-returns-noise-when-setting-echo

monsanto:

When using the pysqlite dialect, setting echo=True will print 

```
2014-04-17 14:25:25,950 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2014-04-17 14:25:25,950 INFO sqlalchemy.engine.base.Engine ()
2014-04-17 14:25:25,950 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2014-04-17 14:25:25,950 INFO sqlalchemy.engine.base.Engine ()
```

when executing the first query. There appears to be no way to tell DefaultDialect not to run these tests--it ignores any existing value of `returns_unicode_strings`.

Why this is a problem: I have a script that connects, runs a query, and disconnects. This script is run at a regular interval. When debugging this script, the cast tests take up 2/3s of the log.

[Sqlalchemy-tickets] Issue #3025: Table.create fails with checkfirst=True on already existing table when TABLE_SCHEMA is not 'dbo' (zzzeek/sqlalchemy)

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

New issue 3025: Table.create fails with checkfirst=True on already existing table when TABLE_SCHEMA is not 'dbo'
https://bitbucket.org/zzzeek/sqlalchemy/issue/3025/tablecreate-fails-with-checkfirst-true-on

Brian Pantano:

I'm on SQL Server using sqlalchemy 0.9.2.

It's hard to provide a repro steps without my particular db setup.

Basically table.create will fail because "There is already an object named 'table' in the database".

It's failing because checkfirst=true is executing this SQL:

SELECT [COLUMNS_1].[TABLE_SCHEMA], [COLUMNS_1].[TABLE_NAME], [COLUMNS_1].[COLUMN_NAME], [COLUMNS_1].[IS_NULLABLE], [COLUMNS_1].[DATA_TYPE], [COLUMNS_1].[ORDINAL_POSITION], [COLUMNS_1].[CHARACTER_MAXIMUM_LENGTH], [COLUMNS_1].[NUMERIC_PRECISION], [COLUMNS_1].[NUMERIC_SCALE], [COLUMNS_1].[COLUMN_DEFAULT], [COLUMNS_1].[COLLATION_NAME] FROM [INFORMATION_SCHEMA].[COLUMNS] AS [COLUMNS_1] WHERE [COLUMNS_1].[TABLE_NAME] = CAST('table_name' AS NVARCHAR(max)) AND [COLUMNS_1].[TABLE_SCHEMA] = CAST('dbo' AS NVARCHAR(max))

is returning zero rows because the owner of the table isn't dbo, but my username.

This breaks alembic because checkfirst is used on the alembic_version table.

[Sqlalchemy-tickets] Issue #3024: Cannot use argument_for on DialectKWArgs not explicitly in construct_arguments (zzzeek/sqlalchemy)

[Dennis H. <iss...@bi...>]

New issue 3024: Cannot use argument_for on DialectKWArgs not explicitly in construct_arguments
https://bitbucket.org/zzzeek/sqlalchemy/issue/3024/cannot-use-argument_for-on-dialectkwargs

Dennis Hennen:

Using 0.9.4

    from sqlalchemy.sql.expression import Insert
    Insert.argument_for('mysql', 'foo', False)

returns

    Traceback (most recent call last):
      File "test.py", line 2, in <module>
        Insert.argument_for('mysql', 'foo', False)
      File "/usr/local/lib/python2.7/site-packages/sqlalchemy/sql/base.py", line 184, in argument_for
        construct_arg_dictionary[cls][argument_name] = default
    KeyError: <class 'sqlalchemy.sql.dml.Insert'>

Because mysql doesn't have Insert explicitly in it's construct_arguments we get the KeyError.

[Sqlalchemy-tickets] Issue #3023: inconsistent behavior of nullable with PrimaryKeyConstraint (zzzeek/sqlalchemy)

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

New issue 3023: inconsistent behavior of nullable with PrimaryKeyConstraint
https://bitbucket.org/zzzeek/sqlalchemy/issue/3023/inconsistent-behavior-of-nullable-with

Mike Bayer:

this is fairly egregious, breaks alembic as one might expect

```
#!python

from sqlalchemy import *

m = MetaData()

t1 = Table('t1', m, Column('x', Integer), PrimaryKeyConstraint('x'))

t2 = Table('t2', m, Column('x', Integer, primary_key=True))

assert list(t1.primary_key) == [t1.c.x]

assert list(t2.primary_key) == [t2.c.x]

assert t1.c.x.primary_key
assert t2.c.x.primary_key

assert not t2.c.x.nullable
assert not t1.c.x.nullable
```

[Sqlalchemy-tickets] Issue #3022: propagate=False for relationship properties (zzzeek/sqlalchemy)

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

New issue 3022: propagate=False for relationship properties
https://bitbucket.org/zzzeek/sqlalchemy/issue/3022/propagate-false-for-relationship

Mike Bayer:

patch attached

[Sqlalchemy-tickets] Issue #3021: SSL EOF not detected as disconnect in psycopg2 (zzzeek/sqlalchemy)

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

New issue 3021: SSL EOF not detected as disconnect in psycopg2
https://bitbucket.org/zzzeek/sqlalchemy/issue/3021/ssl-eof-not-detected-as-disconnect-in

Andrew Suffield:

PGDialect_psycopg2.is_disconnect does not recognise the string "SSL SYSCALL error: EOF detected" as a disconnect error, with all the chaos that usually causes. This is the error I get when using postgres 9.3 and the connection goes away below an ssl connection

[Sqlalchemy-tickets] Issue #3020: Improper column quoting with ORDER BY (zzzeek/sqlalchemy)

[Jon N. <iss...@bi...>]

New issue 3020: Improper column quoting with ORDER BY
https://bitbucket.org/zzzeek/sqlalchemy/issue/3020/improper-column-quoting-with-order-by

Jon Nelson:

Below, please find a chunk of code.
You'll note that - at least on 0.9.4 - the generated statement doesn't
quote the column in the ORDER BY.


import sqlalchemy as sa
import sqlalchemy.dialects.postgresql as sa_pg
import sqlalchemy.dialects.mysql as sa_mysql

m = sa.MetaData()
t = sa.Table(
  'foo', m,
  sa.Column('a', sa.String()),
)

c = t.c.a.label('t.c.a')
q = sa.select([c])
q = q.order_by( c )

print q.compile(dialect=sa_pg.dialect())
print q.compile(dialect=sa_mysql.dialect())


Responsible: zzzeek

[Sqlalchemy-tickets] Issue #3019: id column with serial small integer primary key (zzzeek/sqlalchemy)

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

New issue 3019: id column with serial small integer primary key
https://bitbucket.org/zzzeek/sqlalchemy/issue/3019/id-column-with-serial-small-integer

Moritz Beber:

It's rather common to declare an `id` column as primary key which is usually converted effortlessly. (I work with Flask-SQLAlchemy and a postgresql database.)

```python
class Dummy(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(16), unique=True, index=True)
```

Emits the following statement:

```
CREATE TABLE dummy (
        id SERIAL NOT NULL, 
        name VARCHAR(16), 
        PRIMARY KEY (id)
)
```

Perfect! However, when I declare the id column with a small integer:

```python
class Dummy(db.Model):
    id = db.Column(db.SmallInteger, primary_key=True)
    name = db.Column(db.String(16), unique=True, index=True)
```

The emitted statement is:

```
CREATE TABLE dummy (
        id SMALLINT NOT NULL, 
        name VARCHAR(16), 
        PRIMARY KEY (id)
)
```
and adding an object naturally leads to an error:

```
(IntegrityError) null value in column "id" violates not-null constraint
 'INSERT INTO dummy (name) VALUES (%(name)s) RETURNING dummy.id' {'name': 'crash test'}
```

Is there a more profound reason than a type check preventing this from being possible?

[Sqlalchemy-tickets] Issue #3018: Bad parameter conversion on execute when it is list with one element (zzzeek/sqlalchemy)

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

New issue 3018: Bad parameter conversion on execute when it is list with one element
https://bitbucket.org/zzzeek/sqlalchemy/issue/3018/bad-parameter-conversion-on-execute-when

maxdamage:



```
#!python
some_list = [1, ] # list with one element

query = "SELECT * FROM some_table WHERE id IN :param_list "

params = { "param_list": some_list }

result = db_session.execute(expression.text(query), params)

```

query will look like:

```
#!sql

SELECT * FROM some_table WHERE id IN ('1',)
```


When we want to pass list as a parameter to query, and the list has one element there is added coma at the end  that breaks the query. 
When list has more elements there is no additional coma at the end so the query is fine.

Tested on 0.7.4. Didn't check newer versions.

[Sqlalchemy-tickets] Issue #3017: relationship loading strategy `expired` (zzzeek/sqlalchemy)

[Ronny P. <iss...@bi...>]

New issue 3017: relationship loading strategy `expired`
https://bitbucket.org/zzzeek/sqlalchemy/issue/3017/relationship-loading-strategy-expired

Ronny Pfannschmidt:

currently all relationship loading loads the object on access

to use such objects without hitting the database,
i propose a loading strategy which returns an expired instance
with only the identity key set

roguhly the equivalent of

```
def hull(session, class_, *pk):
  mapper = class_mapper(class_)
  obj = class_()
  inspect(obj).key = mapper.identity_key_from_primary_key(pk)
  session.add(obj)
  session.expire(obj)
  return obj
```

[Sqlalchemy-tickets] Issue #3016: Ordered list is not correctly ordered (zzzeek/sqlalchemy)

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

New issue 3016: Ordered list is not correctly ordered
https://bitbucket.org/zzzeek/sqlalchemy/issue/3016/ordered-list-is-not-correctly-ordered

Marc Schlaich:

[Sqlalchemy-tickets] Issue #3015: DELETEs executed twice when using history_meta and subclassing models (zzzeek/sqlalchemy)

[matt c. <iss...@bi...>]

New issue 3015: DELETEs executed twice when using history_meta and subclassing models
https://bitbucket.org/zzzeek/sqlalchemy/issue/3015/deletes-executed-twice-when-using

matt chisholm:

When using history_meta and also subclassing from model classes (which we do in order to add additional query parameters in our API), SQLAlchemy 0.9.4 raises an error about the number of rows that were deleted not matching the number of rows intended to be deleted. 

This bug is new in 0.9.4, it does not occur in 0.9.3 or 0.9.2. 

Attached is a minimal test case.

There is also a hack in here which fixes another bug, the `SubclassEquivalenceMixIn`. If you remove that from the base classes for `APIBar`, you can see why we are using that mix-in.

Here is the traceback:

```
2014-04-04 18:55:38,671 INFO sqlalchemy.engine.base.Engine DELETE FROM foo_bar WHERE foo_bar.foo_id = %(foo_id)s AND foo_bar.bar_id = %(bar_id)s
2014-04-04 18:55:38,671 INFO sqlalchemy.engine.base.Engine {'foo_id': 1, 'bar_id': 1}
2014-04-04 18:55:38,672 INFO sqlalchemy.engine.base.Engine DELETE FROM foo_bar WHERE foo_bar.foo_id = %(foo_id)s AND foo_bar.bar_id = %(bar_id)s
2014-04-04 18:55:38,672 INFO sqlalchemy.engine.base.Engine {'foo_id': 1, 'bar_id': 1}
2014-04-04 18:55:38,672 INFO sqlalchemy.engine.base.Engine ROLLBACK
Traceback (most recent call last):
  File "sqlalchemy-delete-bug.py", line 100, in <module>
    db.session.commit()
  File "/Users/matt/.virtualenvs/sqlalchemy-delete-bug/lib/python2.7/site-packages/sqlalchemy/orm/scoping.py", line 149, in do
    return getattr(self.registry(), name)(*args, **kwargs)
  File "/Users/matt/.virtualenvs/sqlalchemy-delete-bug/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 765, in commit
    self.transaction.commit()
  File "/Users/matt/.virtualenvs/sqlalchemy-delete-bug/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 370, in commit
    self._prepare_impl()
  File "/Users/matt/.virtualenvs/sqlalchemy-delete-bug/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 350, in _prepare_impl
    self.session.flush()
  File "/Users/matt/.virtualenvs/sqlalchemy-delete-bug/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 1903, in flush
    self._flush(objects)
  File "/Users/matt/.virtualenvs/sqlalchemy-delete-bug/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 2021, in _flush
    transaction.rollback(_capture_exception=True)
  File "/Users/matt/.virtualenvs/sqlalchemy-delete-bug/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py", line 57, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "/Users/matt/.virtualenvs/sqlalchemy-delete-bug/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 1985, in _flush
    flush_context.execute()
  File "/Users/matt/.virtualenvs/sqlalchemy-delete-bug/lib/python2.7/site-packages/sqlalchemy/orm/unitofwork.py", line 370, in execute
    rec.execute(self)
  File "/Users/matt/.virtualenvs/sqlalchemy-delete-bug/lib/python2.7/site-packages/sqlalchemy/orm/unitofwork.py", line 479, in execute
    self.dependency_processor.process_saves(uow, states)
  File "/Users/matt/.virtualenvs/sqlalchemy-delete-bug/lib/python2.7/site-packages/sqlalchemy/orm/dependency.py", line 1083, in process_saves
    secondary_update, secondary_delete)
  File "/Users/matt/.virtualenvs/sqlalchemy-delete-bug/lib/python2.7/site-packages/sqlalchemy/orm/dependency.py", line 1104, in _run_crud
    result.rowcount)
sqlalchemy.orm.exc.StaleDataError: DELETE statement on table 'foo_bar' expected to delete 1 row(s); Only 0 were matched.
```

I would not be the least bit surprised if either this code or flask-sqlalchemy were just doing something horribly stupid. 


Responsible: zzzeek

[Sqlalchemy-tickets] Issue #3014: Documentation for recursive CTE query - column order in second select needs to be same order as first select. (zzzeek/sqlalchemy)

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

New issue 3014: Documentation for recursive CTE query - column order in second select needs to be same order as first select.
https://bitbucket.org/zzzeek/sqlalchemy/issue/3014/documentation-for-recursive-cte-query

Stephen Bridgett:

Firstly thank you for all the great work developing SQLalchemy.

I was trying to create a recursive CTE query using the "sub_part" example given on:

(1)     http://docs.sqlalchemy.org/en/rel_0_9/core/selectable.html
and on:
(2)    http://docs.sqlalchemy.org/en/rel_0_9/orm/query.html

    both in sections titled "cte(name=None, recursive=False)"

however the query hangs, - when tried on SQLite 3 and on Postgres 8.4.
(I am using SQLalchemy version 0.9.3)

when I compared the query with the SQL query given on the Postgresql page:
 http://www.postgresql.org/docs/8.4/static/queries-with.html
(half way down that page)

the postgres SQL always has the columns in each SELECT in the order:
"sub_part, part, quantity" (in the first select) and:  "p.sub_part, p.part, p.quantity" (in the second select)

However the SQLalchemy ORM query on page:
    http://docs.sqlalchemy.org/en/rel_0_9/orm/query.html
has:
     Part.sub_part,  Part.part, Part.quantity  (for the first select),
but then in the second select the order is changed to:
   parts_alias.part, parts_alias.sub_part, parts_alias.quantity

If I change the order of columns in this second select to:

    session.query(
        parts_alias.sub_part,
        parts_alias.part,
        parts_alias.quantity).\

(ie. putting "sub_part" first to match the order in the first select)

Then the query works well. It seems to be important that the columns are in same order for the recursive cte union to work.

This applies: 

(1) to the ORM query page:
   http://docs.sqlalchemy.org/en/rel_0_9/orm/query.html
  section titled:   cte(name=None, recursive=False)

(2) and to the CORE selectable page:
   http://docs.sqlalchemy.org/en/rel_0_9/core/selectable.html
  section titled:   cte(name=None, recursive=False)

Could these two pages be updated to correct this?


Also on that CORE selectable page, the select_from function: 

"select_from(included_parts.join(parts,
                    included_parts.c.part==parts.c.part))"

at the end of that CTE "statement=" recursive query isn't really needed, as the final query doesn't select from "parts", although it could be useful if wish to select additional columns from "part".

Thank you again for all the great work developing SQLalchemy.

[Sqlalchemy-tickets] Issue #3013: new boolean feature not working in ORM (zzzeek/sqlalchemy)

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

New issue 3013: new boolean feature not working in ORM
https://bitbucket.org/zzzeek/sqlalchemy/issue/3013/new-boolean-feature-not-working-in-orm

Mike Bayer:


```
#!python

(Pdb) print s.query(TestModel).statement.where(column('x', Boolean()))
SELECT testmodel.id, testmodel.flags 
FROM testmodel 
WHERE x = 1
(Pdb) print s.query(TestModel).filter(column('x', Boolean())).statement
SELECT testmodel.id, testmodel.flags 
FROM testmodel 
WHERE x
(Pdb) 

```

[Sqlalchemy-tickets] Issue #3012: can't overrride __and__(), __or__() (and __invert__()?) (zzzeek/sqlalchemy)

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

New issue 3012: can't overrride __and__(), __or__() (and __invert__()?)
https://bitbucket.org/zzzeek/sqlalchemy/issue/3012/cant-overrride-__and__-__or__-and

Mike Bayer:


```
#!python

from sqlalchemy import Integer, TypeDecorator, Column, type_coerce

class Foo(TypeDecorator):
    impl = Integer

    class comparator_factory(TypeDecorator.Comparator):
        def __eq__(self, other):
            print "EQ!"
            return type_coerce(self.expr, Integer) == other

        def __and__(self, other):
            print "AND!"
            return type_coerce(self.expr, Integer) & other


c = Column('x', Foo())

print c == 5
print c & 2
```

[Sqlalchemy-tickets] Issue #3011: naming_convention doesn't support MS SQL DEFAULT constraints (zzzeek/sqlalchemy)

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

New issue 3011: naming_convention doesn't support MS SQL DEFAULT constraints
https://bitbucket.org/zzzeek/sqlalchemy/issue/3011/naming_convention-doesnt-support-ms-sql

Marek Baczyński:

SQLAlchemy 0.9.4, Python 3.4.0, MS SQL 2008 R2

This causes issues when dropping columns in alembic.

Table definition:


```
#!python

class SomeTable(Base):
    __tablename__ = 'some_table'

    id = Column(BigInteger, Sequence('some_table_seq_id'), primary_key=True, nullable=False)
    col = Column(String)
    status = Column(Enum('HAS_A', 'HAS_A_OR_B', 'NEITHER', name='status'), server_default='NEITHER', nullable=False)
```

Causes this SQL to be generated:


```
#!sql
CREATE TABLE some_table (
	id BIGINT NOT NULL IDENTITY(1,1), 
	col VARCHAR(max) NULL, 
	status VARCHAR(10) NOT NULL DEFAULT 'NEITHER', 
	CONSTRAINT pk_some_table PRIMARY KEY (id), 
	CONSTRAINT ck_some_table_status CHECK (status IN ('HAS_A', 'HAS_A_OR_B', 'NEITHER'))
)

```

But what MS SQL does for defaults is it creates a default constraint, which gets a default, unpredictable name:


```
#!sql

select [name] from sys.default_constraints
where parent_object_id = object_id('some_table')
and col_name(parent_object_id, parent_column_id) = 'status'

-- name
-- DF__some_tabl__statu__50C6C558
```

There should be a way of providing a constraint name so the generated SQL looks like this:


```
#!sql

CREATE TABLE some_table (
	id BIGINT NOT NULL IDENTITY(1,1), 
	col VARCHAR(max) NULL, 
	status VARCHAR(10) NOT NULL CONSTRAINT [df_some_table_status] DEFAULT 'NEITHER', 
	CONSTRAINT pk_some_table PRIMARY KEY (id), 
	CONSTRAINT ck_some_table_status CHECK (status IN ('HAS_A', 'HAS_A_OR_B', 'NEITHER'))
)

-- check the name

select [name] from sys.default_constraints
where parent_object_id = object_id('some_table')
and col_name(parent_object_id, parent_column_id) = 'status'

-- name
-- df_some_table_status
```

[Sqlalchemy-tickets] Issue #3010: Connection limit exceeded, after updating to 0.9.4 (zzzeek/sqlalchemy)

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

New issue 3010: Connection limit exceeded, after updating to 0.9.4
https://bitbucket.org/zzzeek/sqlalchemy/issue/3010/connection-limit-exceeded-after-updating

Taha Jahangir:

After move to sqlalchemy 0.9.4 in production environment, we encountered error `FATAL: remaining connection slots are reserved for non-replication superuser connections`.

Is there any change in session code, or connection pool manager in sqlalchemy 0.9? This issue occurred on production server, so it's hard (and costly) to do some debugging tasks. We temporarily fixed the issue by reverting to sqlalchemy 0.8.6. 

(Environment is: python 3.3 with mod_wsgi/apache with postgreql 9.0, all on FreeBSD 9.0 servers)

[Sqlalchemy-tickets] Issue #3009: tarball contains only minified versions of javascript libraries (zzzeek/sqlalchemy)

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

New issue 3009: tarball contains only minified versions of javascript libraries
https://bitbucket.org/zzzeek/sqlalchemy/issue/3009/tarball-contains-only-minified-versions-of

piotro:

Hi,

Could you please add sources (i.e. not only minified versions) for below files in the tarball?

* doc/searchindex.js
* doc/_static/jquery.js
* doc/_static/underscore.js

I rebuild docs in Debian anyway, but we distribute original tarball as well and hence need all the sources (I can rebuild the tarball to remove all generated docs, but I'd prefer to keep the original tarball)

[Sqlalchemy-tickets] Issue #3008: make innerjoin='nested' the default behavior for innerjoin=True (zzzeek/sqlalchemy)

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

New issue 3008: make innerjoin='nested' the default behavior for innerjoin=True
https://bitbucket.org/zzzeek/sqlalchemy/issue/3008/make-innerjoin-nested-the-default-behavior

Mike Bayer:

we can maintain the old behavior for the random SQLite user perhaps with "unnested", though might not be worth it.

[Sqlalchemy-tickets] Issue #3007: allow deleted rows check to take place based on flag (zzzeek/sqlalchemy)

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

New issue 3007: allow deleted rows check to take place based on  flag
https://bitbucket.org/zzzeek/sqlalchemy/issue/3007/allow-deleted-rows-check-to-take-place

Mike Bayer:

see #2403 for original rationale to remote the "check deleted rows", we'll now do this:

        mapper(Parent, parent, confirm_deleted_rows=False)

the flag defaults to True and just emits a warning when the rows matched don't match.

[Sqlalchemy-tickets] Issue #3006: orm DELETE not using DB-persited PK in the event of PK change (zzzeek/sqlalchemy)

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

New issue 3006: orm DELETE not using DB-persited PK in the event of PK change
https://bitbucket.org/zzzeek/sqlalchemy/issue/3006/orm-delete-not-using-db-persited-pk-in-the

Mike Bayer:


```
#!python

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)

e = create_engine("sqlite://", echo=True)
#e = create_engine('postgresql://scott:tiger@localhost/test', echo=True)

Base.metadata.drop_all(e)
Base.metadata.create_all(e)

sess = Session(e)
a1 = A(id=1)
sess.add(a1)
sess.commit()

a1.id = 2
sess.delete(a1)
sess.commit()
```

note that we will turn on "multi rowcount" checking which seems to have been removed, also psycopg2 supports this now:


```
#!diff

diff --git a/lib/sqlalchemy/dialects/postgresql/psycopg2.py b/lib/sqlalchemy/dialects/postgresql/psycopg2.py
index 099ddf0..ac17706 100644
--- a/lib/sqlalchemy/dialects/postgresql/psycopg2.py
+++ b/lib/sqlalchemy/dialects/postgresql/psycopg2.py
@@ -347,7 +347,7 @@ class PGDialect_psycopg2(PGDialect):
         supports_unicode_statements = False
 
     default_paramstyle = 'pyformat'
-    supports_sane_multi_rowcount = False
+    supports_sane_multi_rowcount = False  # set to true based on psycopg2 version
     execution_ctx_cls = PGExecutionContext_psycopg2
     statement_compiler = PGCompiler_psycopg2
     preparer = PGIdentifierPreparer_psycopg2
@@ -393,6 +393,9 @@ class PGDialect_psycopg2(PGDialect):
                             is not None
         self._has_native_json = self.psycopg2_version >= (2, 5)
 
+        # http://initd.org/psycopg/docs/news.html#what-s-new-in-psycopg-2-0-9
+        self.supports_sane_multi_rowcount = self.psycopg2_version >= (2, 0, 9)
+
     @classmethod
     def dbapi(cls):
         import psycopg2
diff --git a/lib/sqlalchemy/orm/persistence.py b/lib/sqlalchemy/orm/persistence.py
index 3563198..7a9f3d5 100644
--- a/lib/sqlalchemy/orm/persistence.py
+++ b/lib/sqlalchemy/orm/persistence.py
@@ -702,7 +702,16 @@ def _emit_delete_statements(base_mapper, uowtransaction, cached_connections,
                     stacklevel=12)
                 connection.execute(statement, del_objects)
         else:
-            connection.execute(statement, del_objects)
+            c = connection.execute(statement, del_objects)
+            if connection.dialect.supports_sane_multi_rowcount and \
+                c.rowcount != len(del_objects):
+                raise orm_exc.StaleDataError(
+                    "DELETE statement on table '%s' expected to "
+                    "delete %d row(s); %d were matched." %
+                    (table.description, len(del_objects), c.rowcount)
+                )
+
+
 
 
 def _finalize_insert_update_commands(base_mapper, uowtransaction,

```