Re: [sqlalchemy] avoid setting attribute on init and or.reconstructor

[Shane Carey]

Hey Mike,

I can expand my example. I have an orm mapped attribute like this

class Obj(Base):
_evaluator = Column(String)

def __init__(self, **kwargs):
super().__init__(**kwargs)
self._eval_func = eval(self._evaluator)

@orm.reconstructor
def init_on_load(self):
self._eval_func = eval(self._evaluator)

@property
def evaluator(self):
 return self._eval_func

@evaluator.setter
def set_evaluator(ev):
self._evaluator = ev
self._eval_func = eval(self._evaluator)

You can see that I have to explicitly set self._eval_func in three 
different places, when really I just want to set it every time 
self._evaluator is set.

It looks to me like the orm events are just a different way of placing the 
different settings of this class attribute

Also, I would like to not call eval in the getter of the property for the 
sake of performance (I know that would simplify the issue).

Is there a way to intercept the setting of self._evaluator for all cases?

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] avoid setting attribute on init and or.reconstructor

[Shane Carey]

I am trying to remove duplicate code in this scenario described here

http://docs.sqlalchemy.org/en/latest/orm/constructors.html

class Obj(Base):
def __init__(self, **kwargs):
super().__init__(**kwargs)
self.extra = # ... some complicated construction based off of a 
kwarg

@orm.reconstructor
def init_on_load(self):
self.extra = # ... same complicated construction based off of self 
attribute

Is it possible to intercept the setting of the attribute self.extra depends 
on, and only to the complicated construction of self.extra once?

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Re: inline polymorphic_load for single table inheritance

[Shane Carey]

Okay, I understand that. My example isnt actually the best for the question 
I am asking.

Say I have three subclasses ChildOne, ChildTwo, ChildThree

is there a way to query for ChildOne and ChildTwo in one query, or do I 
need to filter on the discriminator?

something like session.query(ChildOne, ChildTwo).all()

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Re: inline polymorphic_load for single table inheritance

[Shane Carey]

After reading the documentation more thoroughly, I realized that 
with_polymorphic does not filter the result set by subclasses included, it 
only eagerly loads the attributes of those subclasses.

In order to filter on certain subclasses, am I forced to user 
Query.filter() for that purpose?

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] inline polymorphic_load for single table inheritance

[Shane Carey]

When I have single table inheritance, how can I use the new 'inline' 
polymorphic_load feature to only query a subset of child classes?

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

Base = declarative_base()

class Parent(Base):
__tablename__ = 'parent'

id = Column(Integer, primary_key=True)
type = Column(String(8), nullable=False)

__mapper_args__ = {
'polymorphic_on': type,
'with_polymorphic': '*'
}

class ChildOne(Parent):
one = Column(String(8))

__mapper_args__ = {
'polymorphic_identity': 'one',
'polymorphic_load': 'inline'
}

class ChildTwo(Parent):
two = Column(String(8))

__mapper_args__ = {
'polymorphic_identity': 'two'
}

if __name__ == '__main__':
e = create_engine('sqlite:///inline.db', echo=True)

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

s = Session(e)

s.add_all([ChildOne(one='hahaha'), ChildTwo(two='lololol')])

s.commit()

for c in s.query(Parent).all():
print(c)

I've tried adding and removing 'with_polymorphic': '*' from the parent 
class, but it always queries ChildTwo as well

I feel like I need to set something on ChildTwo to tell it not to load, but 
nothing like this is documented

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Using sql expression to update a column from another table's column

[Shane Carey]

Given the following declarative schema, how can I generate the UPDATE 
statement? I am using MySQL

class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
str = Column(String(16), nullable=False)

bs = relationship(B)

class B(Base):
__tablename__ = 'b'
id = Column(Integer, primary_key=True)
a_id = Column(Integer, ForeignKey(A.id), nullable=False)
typ = Column(Enum('1', '2'), nullable=False)
str = Column(String(16), nullable=False)

__table_args__ = ( UniqueConstraint(a_id, typ), )

UPDATE a
INNER JOIN b ON b.a_id = a.id
SET a.str = CONCAT(a.str, b.str)
WHERE b.typ = '2';

I have tried

session.query(A).join(B).filter(B.typ == '2').update({ 'str': 
func.concat(A.str, B.str) }, synchonize_session=False)

but this raises an exception

s.execute(update(A).join(B, b.a_id == A.id).where(B.typ == 
'2').values(str=func.concat(A.str, B.str)))

does not work because join is not a member of Update

How can I achieve this? It would be especially nice if I could use the 
session to do so rather than raw sql expression


-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Re: Cannot use func when bulk saving objects

[Shane Carey]

I was able to solve this by using a subquery instead of loading and 
updating, thanks anyway!

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Re: Cannot use func when bulk saving objects

[Shane Carey]

I just realized this line in the docs may be what I was missing


   - SQL expression inserts / updates (e.g. Embedding SQL Insert/Update 
   Expressions into a Flush 
   

   )

However, I still would like a way to embed CURRENT_DATE into my bulk 
update, is there a way to do this? using the string 'CURRENT_DATE' yields 
an incorrect date value

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Cannot use func when bulk saving objects

[Shane Carey]

Hi, I found what I think to be a bug in the bulk save objects method. The 
following raises an exception:

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

Base = declarative_base()

class Obj(Base):
__tablename__ = 'obj_bulk_test'

id = Column(Integer, primary_key=True)
date = Column(Date, nullable=False)

if __name__ == '__main__':
e = create_engine('mysql+pymysql://user:password@localhost/test')
Base.metadata.create_all(e)
s = Session(e)

s.add_all([Obj(date=func.now()) for i in range(10)])

s.commit()

objs = s.query(Obj).all()

for o in objs:
o.date = func.current_date()

s.bulk_save_objects(objs)

s.commit()

I got the following exception

Traceback (most recent call last):
  File "C:\Python35\lib\site-packages\sqlalchemy\sql\elements.py", line 
676, in __getattr__
return getattr(self.comparator, key)
AttributeError: 'Comparator' object has no attribute 'translate'

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "sqa_bulk_save_func.py", line 28, in 
s.bulk_save_objects(objs)
  File "C:\Python35\lib\site-packages\sqlalchemy\orm\session.py", line 
2338, in bulk_save_objects
return_defaults, update_changed_only, False)
  File "C:\Python35\lib\site-packages\sqlalchemy\orm\session.py", line 
2502, in _bulk_save_mappings
transaction.rollback(_capture_exception=True)
  File "C:\Python35\lib\site-packages\sqlalchemy\util\langhelpers.py", line 
60, in __exit__
compat.reraise(exc_type, exc_value, exc_tb)
  File "C:\Python35\lib\site-packages\sqlalchemy\util\compat.py", line 186, 
in reraise
raise value
  File "C:\Python35\lib\site-packages\sqlalchemy\orm\session.py", line 
2493, in _bulk_save_mappings
isstates, update_changed_only)
  File "C:\Python35\lib\site-packages\sqlalchemy\orm\persistence.py", line 
123, in _bulk_update
bookkeeping=False)
  File "C:\Python35\lib\site-packages\sqlalchemy\orm\persistence.py", line 
732, in _emit_update_statements
execute(statement, multiparams)
  File "C:\Python35\lib\site-packages\sqlalchemy\engine\base.py", line 945, 
in execute
return meth(self, multiparams, params)
  File "C:\Python35\lib\site-packages\sqlalchemy\sql\elements.py", line 
263, in _execute_on_connection
return connection._execute_clauseelement(self, multiparams, params)
  File "C:\Python35\lib\site-packages\sqlalchemy\engine\base.py", line 
1053, in _execute_clauseelement
compiled_sql, distilled_params
  File "C:\Python35\lib\site-packages\sqlalchemy\engine\base.py", line 
1189, in _execute_context
context)
  File "C:\Python35\lib\site-packages\sqlalchemy\engine\base.py", line 
1396, in _handle_dbapi_exception
util.reraise(*exc_info)
  File "C:\Python35\lib\site-packages\sqlalchemy\util\compat.py", line 186, 
in reraise
raise value
  File "C:\Python35\lib\site-packages\sqlalchemy\engine\base.py", line 
1159, in _execute_context
context)
  File 
"C:\Python35\lib\site-packages\sqlalchemy\dialects\mysql\mysqldb.py", line 
95, in do_executemany
rowcount = cursor.executemany(statement, parameters)
  File "C:\Python35\lib\site-packages\pymysql\cursors.py", line 195, in 
executemany
self.rowcount = sum(self.execute(query, arg) for arg in args)
  File "C:\Python35\lib\site-packages\pymysql\cursors.py", line 195, in 

self.rowcount = sum(self.execute(query, arg) for arg in args)
  File "C:\Python35\lib\site-packages\pymysql\cursors.py", line 164, in 
execute
query = self.mogrify(query, args)
  File "C:\Python35\lib\site-packages\pymysql\cursors.py", line 143, in 
mogrify
query = query % self._escape_args(args, conn)
  File "C:\Python35\lib\site-packages\pymysql\cursors.py", line 123, in 
_escape_args
return dict((key, conn.literal(val)) for (key, val) in args.items())
  File "C:\Python35\lib\site-packages\pymysql\cursors.py", line 123, in 

return dict((key, conn.literal(val)) for (key, val) in args.items())
  File "C:\Python35\lib\site-packages\pymysql\connections.py", line 800, in 
literal
return self.escape(obj, self.encoders)
  File "C:\Python35\lib\site-packages\pymysql\connections.py", line 793, in 
escape
return escape_item(obj, self.charset, mapping=mapping)
  File "C:\Python35\lib\site-packages\pymysql\converters.py", line 27, in 
escape_item
val = encoder(val, mapping)
  File "C:\Python35\lib\site-packages\pymysql\converters.py", line 110, in 
escape_unicode
return u"'%s'" % _escape_unicode(value)
  File "C:\Python35\lib\site-packages\pymysql\converters.py", line 73, in 
_escape_unicode
return value.translate(_escape_table)
  File "C:\Python35\lib\site-packages\sqlalchemy\sql\elements.py", line 
682, in __getattr__
key)
AttributeError: Neither 'now' object nor 'Comparator' object has an 
attribute 'translate'

I thought that the func methods simply rendered text like CURRENT_DATE, but 
sqlalchemy echoes something like this

UPDATE obj_bulk_test SET date=%(date)s 

[sqlalchemy] Re: Filter on self referential polymorphic relationship

[Shane Carey]

A more complete example:

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

Base = declarative_base()

class Item(Base):
__tablename__ = 'item'
id = Column(Integer, primary_key=True)
type = Column(String)

__mapper_args__ = { 
'polymorphic_on': type, 
'with_polymorphic': '*'
}

class Food(Item):
calories = Column(Integer)

__mapper_args__ = {
'polymorphic_identity': 'food'
}

class Book(Item):
pages = Column(Integer)

__mapper_args__ = {
'polymorphic_identity': 'book'
}

class Toy(Item):
rating = Column(Integer)

__mapper_args__ = {
'polymorphic_identity': 'toy'
}

class Basket(Item):
pieces = relationship('BasketPiece', primaryjoin='Basket.id == 
BasketPiece.basket_id',)

__mapper_args__ = {
'polymorphic_identity': 'basket'
}

class BasketPiece(Base):
__tablename__ = 'basket_piece'

basket_id = Column(None, ForeignKey(Basket.id), primary_key=True)
piece_id = Column(None, ForeignKey(Item.id), primary_key=True)
quantity = Column(Integer)

piece = relationship(Item, foreign_keys=piece_id)

if __name__ == '__main__':
e = create_engine('sqlite:///poly_self_ref.db', echo=True)

Base.metadata.create_all(e)

s = Session(sessionmaker(e))

Piece = aliased(Item)
s.query(Item) \
.outerjoin(BasketPiece, BasketPiece.basket_id == Item.id) \
.outerjoin(Piece, Piece.id == BasketPiece.piece_id) \
.filter(or_(Food.calories > 100, Book.pages < 500, Piece.calories > 100, 
Piece.pages < 500)) \
.all()


-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Re: Filter on self referential polymorphic relationship

[Shane Carey]

A more complete example

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

Base = declarative_base()

class Item(Base):
__tablename__ = 'item'
id = Column(Integer, primary_key=True)
type = Column(String)

__mapper_args__ = { 
'polymorphic_on': type, 
'with_polymorphic': '*'
}

class Food(Item):
calories = Column(Integer)

__mapper_args__ = {
'polymorphic_identity': 'food'
}

class Book(Item):
pages = Column(Integer)

__mapper_args__ = {
'polymorphic_identity': 'book'
}

class Toy(Item):
rating = Column(Integer)

__mapper_args__ = {
'polymorphic_identity': 'toy'
}

class Basket(Item):
pieces = relationship('BasketPiece', primaryjoin='Basket.id == 
BasketPiece.basket_id',)

__mapper_args__ = {
'polymorphic_identity': 'basket'
}

class BasketPiece(Base):
__tablename__ = 'basket_piece'

basket_id = Column(None, ForeignKey(Basket.id), primary_key=True)
piece_id = Column(None, ForeignKey(Item.id), primary_key=True)
quantity = Column(Integer)

piece = relationship(Item, foreign_keys=piece_id)

if __name__ == '__main__':
e = create_engine('sqlite:///poly_self_ref.db', echo=True)

Base.metadata.create_all(e)

s = Session(sessionmaker(e))

Piece = aliased(Item)
s.query(Item) \
.outerjoin(BasketPiece, BasketPiece.basket_id == Item.id) \
.outerjoin(Piece, Piece.id == BasketPiece.piece_id) \
.filter(or_(Item.calories > 100, Item.pages < 500, Piece.calories > 100, 
Piece.pages < 500)) \
.all()

On Tuesday, April 25, 2017 at 4:36:18 PM UTC-5, Shane Carey wrote:
>
> I have a self referential polymorphic relationship using single table 
> inheritance
>
> Base = declarative_base()
>
> class Item(Base):
> __tablename__ = 'item'
> id = Column(Integer)
> type = Column(String)
>
> __mapper_args__ = { 'polymorphic_on': type, 'with_polymorphic': '*'}
>
> class Food(Item):
> calories = Column(Integer)
>
> __mapper_args__ = {'polymorphic_identity': 'food'}
>
> class Book(Item):
> pages = Column(Integer)
>
> __mapper_args__ = {'polymorphic_identity': 'book'}
>
> class Toy(Item):
> rating = Column(Integer)
>
> __mapper_args__ = {'polymorphic_identity': 'toy'}
>
> class BasetPiece(Base):
> __tablename__ = 'basket_piece'
> basket_id = Column(Integer, ...)
> piece_id = Column(Integer, ...)
> quantity = Column(Integer)
>
> class Basket(Item):
>  items = relationship()
>
> __mapper_args__ = {'polymorphic_identity': 'food'}
>
> So I need to essentially select all  food with > 100 calories, books with 
> < 500 pages, and baskets which contain either, in one query.
>
> SELECT * FROM item i
> LEFT OUTER JOIN basket_piece bp ON bp.basket_id = i.id
> LEFT OUTER JOIN item p ON p.id = bp.piece_id
> WHERE i.calories > 100 OR i.pages < 500 OR p.calories > 100 OR p.pages < 
> 500;
>
> But I can't figure out how to do this in sqlalchemy because the columns of 
> the subclasses are not attributes of the Item class.
>
> Piece = aliased(Item)
> session.query(Item).outerjoin(BasketPiece).outerjoin(Piece).filter(sa.or_(Item.calories
>  
> > 100, Item.pages < 500, Piece.calories > 100, Piece.pages < 500)).all()
>
> How do I acheieve this?
>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Filter on self referential polymorphic relationship

[Shane Carey]

I have a self referential polymorphic relationship using single table 
inheritance

Base = declarative_base()

class Item(Base):
__tablename__ = 'item'
id = Column(Integer)
type = Column(String)

__mapper_args__ = { 'polymorphic_on': type, 'with_polymorphic': '*'}

class Food(Item):
calories = Column(Integer)

__mapper_args__ = {'polymorphic_identity': 'food'}

class Book(Item):
pages = Column(Integer)

__mapper_args__ = {'polymorphic_identity': 'book'}

class Toy(Item):
rating = Column(Integer)

__mapper_args__ = {'polymorphic_identity': 'toy'}

class BasetPiece(Base):
__tablename__ = 'basket_piece'
basket_id = Column(Integer, ...)
piece_id = Column(Integer, ...)
quantity = Column(Integer)

class Basket(Item):
 items = relationship()

__mapper_args__ = {'polymorphic_identity': 'food'}

So I need to essentially select all  food with > 100 calories, books with < 
500 pages, and baskets which contain either, in one query.

SELECT * FROM item i
LEFT OUTER JOIN basket_piece bp ON bp.basket_id = i.id
LEFT OUTER JOIN item p ON p.id = bp.piece_id
WHERE i.calories > 100 OR i.pages < 500 OR p.calories > 100 OR p.pages < 
500;

But I can't figure out how to do this in sqlalchemy because the columns of 
the subclasses are not attributes of the Item class.

Piece = aliased(Item)
session.query(Item).outerjoin(BasketPiece).outerjoin(Piece).filter(sa.or_(Item.calories
 
> 100, Item.pages < 500, Piece.calories > 100, Piece.pages < 500)).all()

How do I acheieve this?

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Delete orphan occurs even before commit happens and when expunge happens

[Shane Carey]

So the reason the children are marked for delete is because they are no
longer associated with the parent, and for that same reason the expunge does
not propagate to them.

As much as I wish this would work for me, I have to agree with you. Is 
there any way to prevent
the delete from occurring on the autoflush? I simply need to stop all 
changes to my parent
from being persisted, without rolling back the session.

On Monday, April 3, 2017 at 9:47:13 AM UTC-5, Mike Bayer wrote:
>
>
>
> On 04/03/2017 09:52 AM, Shane Carey wrote: 
> > I am getting an error where delete orphan on a relationship seems to 
> > happen even when the parent object is expunged. 
> > 
> > I reproduced the issue in this example. 
> > 
> > from sqlalchemy import * 
> > from sqlalchemy.orm import * 
> > from sqlalchemy.ext.declarative import * 
> > 
> > Base = declarative_base() 
> > 
> > class Parent(Base): 
> > __tablename__ = 'parent' 
> > 
> > id = Column(Integer, primary_key=True) 
> > 
> > children = relationship('Child', lazy='joined', backref='parent', 
> > cascade='all, delete-orphan') 
> > 
> > def dict(self): 
> > return { 
> > 'id': self.id, 
> > 'children': [c.dict() for c in self.children] 
> > } 
> > 
> > class Child(Base): 
> > __tablename__ = 'child' 
> > 
> > parent_id = Column(None, ForeignKey(Parent.id), primary_key=True) 
> > name = Column(String(8), primary_key=True) 
> > 
> > def dict(self): 
> > return { 
> > 'name': self.name 
> > } 
> > 
> > if __name__ == '__main__': 
> > e = create_engine('sqlite:///orphan.db', echo=True) 
> > 
> > Base.metadata.drop_all(e) 
> > Base.metadata.create_all(e) 
> > 
> > s = sessionmaker(e)() 
> > 
> > p = Parent() 
> > p.children = [Child(name=str(i)) for i in range(5)] 
> > 
> > s.add(p) 
> > s.commit() 
> > 
> > p = s.query(Parent).one() 
> > print(p.dict()) 
> > 
> > p.children = [Child(name=str(i)) for i in range(5, 10)] 
> > 
> > print(p.dict()) 
> > 
> > s.expunge(p) 
> > 
> > p = s.query(Parent).one() 
> > print(p.dict()) 
> > 
> > The parent has no children after the expunge, is this intended or a bug? 
>
> Hi there - 
>
> OK, this is definitely more of a mindbender, and I don't know that 
> there's a way situations like these can have a little less cognitive 
> load on them.  However, let me propose an alternate form of what we see 
> above - let's see if our expectations change: 
>
>
> p = Parent() 
> p.children = [Child(name=str(i)) for i in range(5)] 
>
> s.add(p) 
> s.commit() 
>
> p = s.query(Parent).one() 
>
> for child in p.children: 
>  child.parent = None 
>
> for i in range(5, 10): 
>  Child(name=str(i), parent=p) 
>
> s.expunge(p) 
>
>
> now, with the above, for the original Child 1-5, do we expect: 
>
> 1. that they are also expunged?  Why would that be, when they are no 
> longer associated with p? 
>
> 2. that they be treated as non-orphans?  Why would that be, when we have 
> set child.parent = None? 
>
> therefore, looking at it from the above perspective it seems to me that 
> they *should* be deleted upon flush.   the flush here occurs as the 
> autoflush on the next query. 
>
> let me know if this makes sense.   I agree that initially, I asked 
> myself, hmm should expunge count for Parent.collection. 
> also, or somehow revert that change?   But looking from the perspective 
> of many-to-one, which is implicit in the one-to-many operations, it 
> seems like not. 
>
>
>
>
>
>
>
> > 
> > -- 
> > SQLAlchemy - 
> > The Python SQL Toolkit and Object Relational Mapper 
> > 
> > http://www.sqlalchemy.org/ 
> > 
> > To post example code, please provide an MCVE: Minimal, Complete, and 
> > Verifiable Example. See http://stackoverflow.com/help/mcve for a full 
> > description. 
> > --- 
> > You received this message because you are subscribed to the Google 
> > Groups "sqlalchemy" group. 
> > To unsubscribe from this group and stop receiving emails from it, send 
> > an email to sqlalchemy+...@googlegroups.com  
> > <mailto:sqlalchemy+unsubscr...@googlegroups.com >. 
> > To post to this group, send email to sqlal...@googlegroups.com 
>  
> > <mailto:sqlal...@googlegroups.com >. 
> > Visit this group at https://groups.google.com/group/sqlalchemy. 
> > For more options, visit https://groups.google.com/d/optout. 
>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Delete orphan occurs even before commit happens and when expunge happens

[Shane Carey]

I am getting an error where delete orphan on a relationship seems to happen 
even when the parent object is expunged.

I reproduced the issue in this example.

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

Base = declarative_base()

class Parent(Base):
__tablename__ = 'parent'

id = Column(Integer, primary_key=True)

children = relationship('Child', lazy='joined', backref='parent', 
cascade='all, delete-orphan')

def dict(self):
return {
'id': self.id,
'children': [c.dict() for c in self.children]
}

class Child(Base):
__tablename__ = 'child'

parent_id = Column(None, ForeignKey(Parent.id), primary_key=True)
name = Column(String(8), primary_key=True)

def dict(self):
return {
'name': self.name
}

if __name__ == '__main__':
e = create_engine('sqlite:///orphan.db', echo=True)

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

s = sessionmaker(e)()

p = Parent()
p.children = [Child(name=str(i)) for i in range(5)]

s.add(p)
s.commit()

p = s.query(Parent).one()
print(p.dict())

p.children = [Child(name=str(i)) for i in range(5, 10)]

print(p.dict())

s.expunge(p)

p = s.query(Parent).one()
print(p.dict())

The parent has no children after the expunge, is this intended or a bug?

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Re: Status of cascading polymorphism, in docs as well

[Shane Carey]

Works great for me

On Tuesday, February 21, 2017 at 2:56:58 PM UTC-6, Shane Carey wrote:
>
> I understand from the docs and several questions both here and on 
> bitbucket that cascading polymorphism is not supported. This
> is what it says on the docs:
>
> Warning
>
> Currently, *only one discriminator column may be set*, typically on the 
> base-most class in the hierarchy. “Cascading” polymorphic columns are not 
> yet supported.
>
>
> However, the following experiment works for me
>
>
> from sqlalchemy import *
>
> from sqlalchemy.orm import *
>
> from sqlalchemy.ext.declarative import declarative_base
>
> import json
>
>
> Base = declarative_base()
>
>
> class Top(Base):
>
> __tablename__ = 'top'
>
>
> id = Column(Integer, primary_key=True)
>
> type = Column(String(8), nullable=False)
>
>
> __mapper_args__ = {
>
> 'polymorphic_on': type,
>
> 'with_polymorphic': '*'
>
> }
>
>
> def dict(self):
>
> return {
>
> 'id': self.id
>
> }
>
>
> class Primary(Top):
>
> __tablename__ = 'primary'
>
>
> id = Column(None, ForeignKey(Top.id), primary_key=True)
>
> type = Column(String(8), nullable=False)
>
>
> __mapper_args__ = {
>
> 'polymorphic_identity': 'primary',
>
> 'polymorphic_on': type,
>
> 'with_polymorphic': '*'
>
> }
>
>
> def dict(self):
>
> return {
>
> 'type': self.type,
>
> **super().dict()
>
> }
>
>
> class One(Primary):
>
> __tablename__ = 'one'
>
>
> id = Column(None, ForeignKey(Primary.id), primary_key=True)
>
> one = Column(String(32), nullable=False)
>
>
> __mapper_args__ = {
>
> 'polymorphic_identity': 'one'
>
> }
>
>
> def dict(self):
>
> return {
>
> 'one': self.one,
>
> **super().dict()
>
> }
>
>
> class Two(Primary):
>
> __tablename__ = 'two'
>
>
> id = Column(None, ForeignKey(Primary.id), primary_key=True)
>
> two = Column(String(32), nullable=False)
>
>
> __mapper_args__ = {
>
> 'polymorphic_identity': 'two'
>
> }
>
>
> def dict(self):
>
> return {
>
> 'two': self.two,
>
> **super().dict()
>
> }
>
>
> class Secondary(Top):
>
> __tablename__ = 'secondary'
>
>
> id = Column(None, ForeignKey(Top.id), primary_key=True)
>
> type = Column(String(8), nullable=False)
>
>
> __mapper_args__ = {
>
> 'polymorphic_identity': 'secondary',
>
> 'polymorphic_on': type,
>
> 'with_polymorphic': '*'
>
> }
>
>
> def dict(self):
>
> return {
>
> 'type': self.type,
>
> **super().dict()
>
> }
>
>
> class Three(Secondary):
>
> __tablename__ = 'three'
>
>
> id = Column(None, ForeignKey(Secondary.id), primary_key=True)
>
> three = Column(String(32), nullable=False)
>
>
> __mapper_args__ = {
>
> 'polymorphic_identity': 'three'
>
> }
>
>
> def dict(self):
>
> return {
>
> 'three': self.three,
>
> **super().dict()
>
> }
>
>
> class Four(Secondary):
>
> __tablename__ = 'four'
>
>
> id = Column(None, ForeignKey(Secondary.id), primary_key=True)
>
> four = Column(String(32), nullable=False)
>
>
> __mapper_args__ = {
>
> 'polymorphic_identity': 'four'
>
> }
>
>
> def dict(self):
>
> return {
>
> 'four': self.four,
>
> **super().dict()
>
> }
>
>
> if __name__ == '__main__':
>
> e = create_engine('sqlite:///poly_casc.db', echo=True)
>
>
> Base.metadata.drop_all(e)
>
> Base.metadata.create_all(e)
>
>
> s = create_session(e)
>
>
> s.begin()
>
>
> s.add_all([One(one='one'), Two(two='two'), Three(three='three'), 
> Four(four='four')])
>
>
> s.commit()
>
>
> for m in s.query(Top).all():
>
> print(json.dumps(m.dict(), indent=4))
>
> print(type(m))
>
>
> s.expunge_all()
>
>
> for m in s.query(Primary).all():
>
> print(json.dumps(m.dict(), indent=4))
>
> print(type(m))
>
>
> s.expunge_all()
>
>
> for m in s.query(One).all():
>
> print(json.dumps(m.dict(), indent=4))
>
> print(type(m))
>
>
> so I am wondering if the docs needed to be updated, or I am missing 
> something regarding the functionality.
>
>
> Thanks
>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Status of cascading polymorphism, in docs as well

[Shane Carey]

I understand from the docs and several questions both here and on bitbucket 
that cascading polymorphism is not supported. This
is what it says on the docs:

Warning

Currently, *only one discriminator column may be set*, typically on the 
base-most class in the hierarchy. “Cascading” polymorphic columns are not 
yet supported.


However, the following experiment works for me


from sqlalchemy import *

from sqlalchemy.orm import *

from sqlalchemy.ext.declarative import declarative_base

import json


Base = declarative_base()


class Top(Base):

__tablename__ = 'top'


id = Column(Integer, primary_key=True)

type = Column(String(8), nullable=False)


__mapper_args__ = {

'polymorphic_on': type,

'with_polymorphic': '*'

}


def dict(self):

return {

'id': self.id

}


class Primary(Top):

__tablename__ = 'primary'


id = Column(None, ForeignKey(Top.id), primary_key=True)

type = Column(String(8), nullable=False)


__mapper_args__ = {

'polymorphic_identity': 'primary',

'polymorphic_on': type,

'with_polymorphic': '*'

}


def dict(self):

return {

'type': self.type,

**super().dict()

}


class One(Primary):

__tablename__ = 'one'


id = Column(None, ForeignKey(Primary.id), primary_key=True)

one = Column(String(32), nullable=False)


__mapper_args__ = {

'polymorphic_identity': 'one'

}


def dict(self):

return {

'one': self.one,

**super().dict()

}


class Two(Primary):

__tablename__ = 'two'


id = Column(None, ForeignKey(Primary.id), primary_key=True)

two = Column(String(32), nullable=False)


__mapper_args__ = {

'polymorphic_identity': 'two'

}


def dict(self):

return {

'two': self.two,

**super().dict()

}


class Secondary(Top):

__tablename__ = 'secondary'


id = Column(None, ForeignKey(Top.id), primary_key=True)

type = Column(String(8), nullable=False)


__mapper_args__ = {

'polymorphic_identity': 'secondary',

'polymorphic_on': type,

'with_polymorphic': '*'

}


def dict(self):

return {

'type': self.type,

**super().dict()

}


class Three(Secondary):

__tablename__ = 'three'


id = Column(None, ForeignKey(Secondary.id), primary_key=True)

three = Column(String(32), nullable=False)


__mapper_args__ = {

'polymorphic_identity': 'three'

}


def dict(self):

return {

'three': self.three,

**super().dict()

}


class Four(Secondary):

__tablename__ = 'four'


id = Column(None, ForeignKey(Secondary.id), primary_key=True)

four = Column(String(32), nullable=False)


__mapper_args__ = {

'polymorphic_identity': 'four'

}


def dict(self):

return {

'four': self.four,

**super().dict()

}


if __name__ == '__main__':

e = create_engine('sqlite:///poly_casc.db', echo=True)


Base.metadata.drop_all(e)

Base.metadata.create_all(e)


s = create_session(e)


s.begin()


s.add_all([One(one='one'), Two(two='two'), Three(three='three'), 
Four(four='four')])


s.commit()


for m in s.query(Top).all():

print(json.dumps(m.dict(), indent=4))

print(type(m))


s.expunge_all()


for m in s.query(Primary).all():

print(json.dumps(m.dict(), indent=4))

print(type(m))


s.expunge_all()


for m in s.query(One).all():

print(json.dumps(m.dict(), indent=4))

print(type(m))


so I am wondering if the docs needed to be updated, or I am missing 
something regarding the functionality.


Thanks

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Polymorphic 'all' query with discriminator surrogate key gives subquery multiple row error

[Shane Carey]

Great, the aliasing works. Thanks so much. But, if I want to have my design 
scale, how can I ask sqlalchemy to use a JOIN rather than a subquery?

On Thursday, February 9, 2017 at 10:38:42 AM UTC-6, Mike Bayer wrote:
>
> Running your example with 
>
> 'polymorphic_on': 
>  select([Type.type]).where(Type.id == type_id).as_scalar() 
>
>
> "res = s.query(Stuff).all()" works fine. 
>
> The reason "res = s.query(Thing).join(Type).filter(Type.type == 
> 'stuff').all()" fails is that Type is being injected into the SELECT 
> statement twice and it's confused. 
>
> the solution is to protect your subquery from interference by using an 
> alias: 
>
> type_alias = aliased(Type) 
>
>
> class Thing(Base): 
>  __tablename__ = 'thing' 
>
>  id = Column(Integer, primary_key=True) 
>  type_id = Column(Integer, ForeignKey(Type.id), nullable=False) 
>
>  type = relationship(Type) 
>
>  __mapper_args__ = { 
>  'polymorphic_on': 
>  select([type_alias.type]).where(type_alias.id == 
> type_id).as_scalar(), 
>  'with_polymorphic': '*' 
>  } 
>
>
> then the join example at the end produces: 
>
> SELECT thing.id AS thing_id, thing.type_id AS thing_type_id, (SELECT 
> type_1.type 
> FROM type AS type_1 
> WHERE type_1.id = thing.type_id) AS _sa_polymorphic_on, thing.stuff AS 
> thing_stuff, thing.junk AS thing_junk 
> FROM thing INNER JOIN type ON type.id = thing.type_id 
> WHERE type.type = %(type_2)s 
>
> noting that this query is not going to scale up to millions of rows that 
> well as MySQL hates those subqueries. 
>
>
>
>
>
>
>
> On 02/09/2017 11:09 AM, Shane Carey wrote: 
> > That is what I initially had, 
> > 
> > I get the error 
> > 
> > sqlalchemy.exc.InvalidRequestError: Select statement 'SELECT type.type 
> > FROM type, thing 
> > WHERE type.id = thing.type_id' returned no FROM clauses due to 
> > auto-correlation; specify correlate() to control correlation 
> > manually. 
> > 
> > On Thursday, February 9, 2017 at 10:02:40 AM UTC-6, Mike Bayer wrote: 
> > 
> > 
> > 
> > On 02/09/2017 10:14 AM, Shane Carey wrote: 
> > > Hi, another question. 
> > > 
> > > I set my discriminator on the surrogate primary key of its table. 
> > > However, when I query for all of a certain discriminator, I get an 
> > error 
> > > 'Multiple rows returned for subquery'. 
> > > 
> > > Here is my canonical example 
> > > 
> > > from sqlalchemy import * 
> > > from sqlalchemy import select, and_, event, inspect 
> > > from sqlalchemy.orm import * 
> > > from sqlalchemy.ext.declarative import * 
> > > 
> > > Base = declarative_base() 
> > > 
> > > class Type(Base): 
> > > __tablename__ = 'type' 
> > > 
> > > id = Column(Integer, primary_key=True) 
> > > type = Column(String(8), unique=True) 
> > > 
> > > class Thing(Base): 
> > > __tablename__ = 'thing' 
> > > 
> > > id = Column(Integer, primary_key=True) 
> > > type_id = Column(Integer, ForeignKey(Type.id), nullable=False) 
> > > 
> > > type = relationship(Type) 
> > > 
> > > __mapper_args__ = { 'polymorphic_on': 
> > select([Type.type]).where(Type.id 
> > > == type_id).correlate(Type).as_scalar(), 'with_polymorphic': '*' } 
> > > 
> > > @event.listens_for(Thing, 'init', propagate=True) 
> > > def set_identity(instance, *args, **kwargs): 
> > > instance.type_id = select([Type.id]).where(Type.type == 
> > > object_mapper(instance).polymorphic_identity) 
> > > 
> > > class Stuff(Thing): 
> > > stuff = Column(String(8)) 
> > > __mapper_args__ = { 'polymorphic_identity': 'stuff' } 
> > > 
> > > class Junk(Thing): 
> > > junk = Column(String(8)) 
> > > __mapper_args__ = { 'polymorphic_identity': 'junk' } 
> > > 
> > > if __name__ == '__main__': 
> > > e = create_engine('mysql+pymysql://user:password@localhost/test', 
> > echo=True) 
> > > 
> > > Base.metadata.drop_all(e) 
> > > Base.metadata.create_all(e) 
> > > 
> > > s = Session(e) 
> > > 
> > > s.add_all([Type(type='stuff'), Type(type='junk')]) 
> > > for i i

Re: [sqlalchemy] Polymorphic 'all' query with discriminator surrogate key gives subquery multiple row error

[Shane Carey]

That is what I initially had,

I get the error

sqlalchemy.exc.InvalidRequestError: Select statement 'SELECT type.type
FROM type, thing
WHERE type.id = thing.type_id' returned no FROM clauses due to 
auto-correlation; specify correlate() to control correlation 
manually.

On Thursday, February 9, 2017 at 10:02:40 AM UTC-6, Mike Bayer wrote:
>
>
>
> On 02/09/2017 10:14 AM, Shane Carey wrote: 
> > Hi, another question. 
> > 
> > I set my discriminator on the surrogate primary key of its table. 
> > However, when I query for all of a certain discriminator, I get an error 
> > 'Multiple rows returned for subquery'. 
> > 
> > Here is my canonical example 
> > 
> > from sqlalchemy import * 
> > from sqlalchemy import select, and_, event, inspect 
> > from sqlalchemy.orm import * 
> > from sqlalchemy.ext.declarative import * 
> > 
> > Base = declarative_base() 
> > 
> > class Type(Base): 
> > __tablename__ = 'type' 
> > 
> > id = Column(Integer, primary_key=True) 
> > type = Column(String(8), unique=True) 
> > 
> > class Thing(Base): 
> > __tablename__ = 'thing' 
> > 
> > id = Column(Integer, primary_key=True) 
> > type_id = Column(Integer, ForeignKey(Type.id), nullable=False) 
> > 
> > type = relationship(Type) 
> > 
> > __mapper_args__ = { 'polymorphic_on': select([Type.type]).where(Type.id 
> > == type_id).correlate(Type).as_scalar(), 'with_polymorphic': '*' } 
> > 
> > @event.listens_for(Thing, 'init', propagate=True) 
> > def set_identity(instance, *args, **kwargs): 
> > instance.type_id = select([Type.id]).where(Type.type == 
> > object_mapper(instance).polymorphic_identity) 
> > 
> > class Stuff(Thing): 
> > stuff = Column(String(8)) 
> > __mapper_args__ = { 'polymorphic_identity': 'stuff' } 
> > 
> > class Junk(Thing): 
> > junk = Column(String(8)) 
> > __mapper_args__ = { 'polymorphic_identity': 'junk' } 
> > 
> > if __name__ == '__main__': 
> > e = create_engine('mysql+pymysql://user:password@localhost/test', 
> echo=True) 
> > 
> > Base.metadata.drop_all(e) 
> > Base.metadata.create_all(e) 
> > 
> > s = Session(e) 
> > 
> > s.add_all([Type(type='stuff'), Type(type='junk')]) 
> > for i in range(10): 
> > s.add(Stuff(stuff='stuff_{}'.format(i))) 
> > s.add(Junk(junk='junk_{}'.format(i))) 
> > 
> > s.commit() 
> > 
> > res = s.query(Thing).join(Type).filter(Type.type == 'stuff').all() 
> > 
> > #res = s.query(Stuff).all() Also fails 
> > 
> > At first I was receiving the error 'No FROM table specified due to 
> > autocorrelate', but I was able to solve that with 'polymorphic_on': 
> > select([Type.type]).where(Type.id == 
> type_id).correlate(Type).as_scalar() 
> > which I still do not fully understand. The exact error I get is 
>
> I think you want to correlate(Thing) there.  correlate(X) means you 
> don't want "X" in the FROM list in the subquery.But also the 
> correlation should be automatic here so you wouldn't need to refer to 
> "Thing" in the class def (if you did, you'd need to turn __mapper_args__ 
> into a @declared_attr). 
>
> that is: 
>
> "polymorphic_on": select([Type.type]).where(Type.id == 
> type_id).as_scalar() 
>
> should work 
>
>
>
> > 
> > sqlalchemy.exc.InternalError: (pymysql.err.InternalError) (1242, 
> > 'Subquery returns more than 1 row') [SQL: 'SELECT thing.id AS thing_id, 
> > thing.type_id AS thing_type_id, thing.text AS thing_text, (SELECT 
> > type.type \nFROM type, thing \nWHERE type.id = thing.type_id) AS 
> > _sa_polymorphic_on, thing.stuff AS thing_stuff, thing.junk AS thing_junk 
> > \nFROM thing'] 
> > 
> > it seems like the SQL it should generate is 
> > 
> > SELECT thing.id AS thing_id, 
> > thing.type_id AS thing_type_id, 
> > thing.text AS thing_text, 
> > ( 
> > SELECT type 
> > FROM type # this was thing before 
> > WHERE type.id = thing.type_id 
> > ) AS _sa_polymorphic_on, 
> > thing.stuff AS thing_stuff, 
> > thing.junk AS thing_junk 
> > FROM thing 
> > INNER JOIN type ON type.id = thing.type_id 
> > WHERE type.type = 'junk'; 
> > 
> > but better again would be, this, right? 
> > 
> > SELECT thing.id AS thing_id, 
> > thing.type_id AS thing_type_id, 
> > thing.text AS thing_text, 
> > type.type AS _sa_polymorphic_on, 
> > thing.stuff AS thing_stuff, 
> > thing.junk AS thing_junk 
> > FROM thing 
> > INNER JOIN type ON type.id = thing.type_id 

[sqlalchemy] Polymorphic 'all' query with discriminator surrogate key gives subquery multiple row error

[Shane Carey]

Hi, another question.

I set my discriminator on the surrogate primary key of its table. However, 
when I query for all of a certain discriminator, I get an error 'Multiple 
rows returned for subquery'.

Here is my canonical example

from sqlalchemy import *
from sqlalchemy import select, and_, event, inspect
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import *

Base = declarative_base()

class Type(Base):
__tablename__ = 'type'

id = Column(Integer, primary_key=True)
type = Column(String(8), unique=True)

class Thing(Base):
__tablename__ = 'thing'

id = Column(Integer, primary_key=True)
type_id = Column(Integer, ForeignKey(Type.id), nullable=False)

type = relationship(Type)

__mapper_args__ = { 'polymorphic_on': select([Type.type]).where(Type.id == 
type_id).correlate(Type).as_scalar(), 'with_polymorphic': '*' }

@event.listens_for(Thing, 'init', propagate=True)
def set_identity(instance, *args, **kwargs):
instance.type_id = select([Type.id]).where(Type.type == 
object_mapper(instance).polymorphic_identity)

class Stuff(Thing):
stuff = Column(String(8))
__mapper_args__ = { 'polymorphic_identity': 'stuff' }

class Junk(Thing):
junk = Column(String(8))
__mapper_args__ = { 'polymorphic_identity': 'junk' }

if __name__ == '__main__':
e = create_engine('mysql+pymysql://user:password@localhost/test', echo=True)

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

s = Session(e)

s.add_all([Type(type='stuff'), Type(type='junk')])
for i in range(10):
s.add(Stuff(stuff='stuff_{}'.format(i)))
s.add(Junk(junk='junk_{}'.format(i)))

s.commit()

res = s.query(Thing).join(Type).filter(Type.type == 'stuff').all()

#res = s.query(Stuff).all() Also fails

At first I was receiving the error 'No FROM table specified due to 
autocorrelate', but I was able to solve that with 'polymorphic_on': 
select([Type.type]).where(Type.id == type_id).correlate(Type).as_scalar()
which I still do not fully understand. The exact error I get is 

sqlalchemy.exc.InternalError: (pymysql.err.InternalError) (1242, 'Subquery 
returns more than 1 row') [SQL: 'SELECT thing.id AS thing_id, thing.type_id 
AS thing_type_id, thing.text AS thing_text, (SELECT type.type \nFROM type, 
thing \nWHERE type.id = thing.type_id) AS _sa_polymorphic_on, thing.stuff 
AS thing_stuff, thing.junk AS thing_junk \nFROM thing']

it seems like the SQL it should generate is

SELECT thing.id AS thing_id, 
thing.type_id AS thing_type_id, 
thing.text AS thing_text, 
(
SELECT type 
FROM type # this was thing before
WHERE type.id = thing.type_id
) AS _sa_polymorphic_on, 
thing.stuff AS thing_stuff, 
thing.junk AS thing_junk 
FROM thing 
INNER JOIN type ON type.id = thing.type_id 
WHERE type.type = 'junk';

but better again would be, this, right?

SELECT thing.id AS thing_id, 
thing.type_id AS thing_type_id, 
thing.text AS thing_text, 
type.type AS _sa_polymorphic_on, 
thing.stuff AS thing_stuff, 
thing.junk AS thing_junk 
FROM thing 
INNER JOIN type ON type.id = thing.type_id 
WHERE type.type = 'junk';

so I am not sure what is going wrong or how I can generate the correct SQL,
or if possible generate a join rather than a subquery.

Thanks for you help and patience

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Can I create a query and associate it with a session later?

[Shane Carey]

Works great, and thanks for the update to the documentation.

On Wednesday, February 8, 2017 at 4:48:32 PM UTC-6, Mike Bayer wrote:
>
>
>
> On 02/08/2017 05:40 PM, mike bayer wrote: 
> > 
> > 
> > On 02/08/2017 05:31 PM, Shane Carey wrote: 
> >> This is an artificial example but it would help my application become 
> >> more modular if I could pass a Query object to my session. 
> >> I see in the docs it says "|Query| 
> >> <
> http://docs.sqlalchemy.org/en/latest/orm/query.html#sqlalchemy.orm.query.Query>
>  
>
> >> objects 
> >> are normally initially generated using the |query()| 
> >> <
> http://docs.sqlalchemy.org/en/latest/orm/session_api.html#sqlalchemy.orm.session.Session.query>
>  
>
> >> method 
> >> of |Session| 
> >> <
> http://docs.sqlalchemy.org/en/latest/orm/session_api.html#sqlalchemy.orm.session.Session>.
>  
>
> >> 
> >> For a full walkthrough of |Query| 
> >> <
> http://docs.sqlalchemy.org/en/latest/orm/query.html#sqlalchemy.orm.query.Query>
>  
>
> >> usage, 
> >> see the Object Relational Tutorial 
> >> <http://docs.sqlalchemy.org/en/latest/orm/tutorial.html>.", which 
> makes 
> >> me think this is possible. 
> >> 
> >> class Thing(Base): 
> >> __tablename__ = 'thing' 
> >> id = Column(Integer, primary_key=True) 
> >> 
> >> e = create_engine('...') 
> >> 
> >> s = Session(engine) 
> >> 
> >> q = Query(Thing).filter(Thing.id == 5) 
> >> 
> >> r = s.query(q).one() # How do I do this? if at all? 
> > 
> > 
> > You can definitely make a Query directly with the constructor as you're 
> > doing, and then to associate with a Session you simply say r = 
> > q.with_session(session).one(). 
>
> correction, need to pass Thing in a list: 
>
> q =  Query([Thing]) 
> q = q.with_session(some_session) 
>
>
> > 
> > 
> > 
> > 
> > 
> >> 
> >> -- 
> >> SQLAlchemy - 
> >> The Python SQL Toolkit and Object Relational Mapper 
> >> 
> >> http://www.sqlalchemy.org/ 
> >> 
> >> To post example code, please provide an MCVE: Minimal, Complete, and 
> >> Verifiable Example. See http://stackoverflow.com/help/mcve for a full 
> >> description. 
> >> --- 
> >> You received this message because you are subscribed to the Google 
> >> Groups "sqlalchemy" group. 
> >> To unsubscribe from this group and stop receiving emails from it, send 
> >> an email to sqlalchemy+...@googlegroups.com  
> >> <mailto:sqlalchemy+unsubscr...@googlegroups.com >. 
> >> To post to this group, send email to sqlal...@googlegroups.com 
>  
> >> <mailto:sqlal...@googlegroups.com >. 
> >> Visit this group at https://groups.google.com/group/sqlalchemy. 
> >> For more options, visit https://groups.google.com/d/optout. 
>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Can I create a query and associate it with a session later?

[Shane Carey]

This is an artificial example but it would help my application become more 
modular if I could pass a Query object to my session.
I see in the docs it says "Query 

 objects 
are normally initially generated using the query() 

 method 
of Session 
.
 
For a full walkthrough of Query 

 usage, 
see the Object Relational Tutorial 
.", which makes me 
think this is possible.

class Thing(Base):
__tablename__ = 'thing'
id = Column(Integer, primary_key=True)

e = create_engine('...')

s = Session(engine)

q = Query(Thing).filter(Thing.id == 5)

r = s.query(q).one() # How do I do this? if at all?

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Session.enable_relationship_loading with inline select statement

[Shane Carey]

Okay, perhaps there is another approach?

I got my required behavior with something like

t = Stuff(**kwargs)
t = session.merge(t)
print(t.dict())
session.rollback()

But this causes the db to give t a primary key even though all I would like 
is
the associated persistent objects. Unless I can inject the select execution 
directly
into the model?

On Thursday, February 2, 2017 at 8:55:06 AM UTC-6, Mike Bayer wrote:
>
>
>
> On 02/02/2017 09:29 AM, Shane Carey wrote: 
> > Hi, I have a use case where I need to enable relationship loading on a 
> > single object. However, I initialize the foreign key of this 
> > relationship using an inline select statement. 
> > 
> > 
> > @event.listens_for(Thing, 'init', propagate=True) 
> > def set_identity(instance, *args, **kwargs): 
> > mapper = object_mapper(instance) 
> > instance.type_id = select([Type.id]).where(Type.type == 
> > mapper.polymorphic_identity) 
> > 
> > 
> > thing = Stuff(text='thing1', stuff='stuff1') 
> > 
> > s.enable_relationship_loading(thing) 
> > 
> > print(thing.dict()) 
> > 
> > From the docs, I am fuzzy on whether or not this should be able to work 
> > because the relationship loading is enabled on the foreign key 
> > which is set here to a select statement. I get the error: 
>
> yeah no, that's not going to work.   you need to run that SELECT 
> statement manually and apply the integer identifier to the 
> instance.type_id for this use case. 
>
>
>
> > 
> > 2017-02-02 08:25:58,789 INFO sqlalchemy.engine.base.Engine SELECT 
> > type.id AS type_id, type.type AS type_type 
> > FROM type 
> > WHERE type.id = %(param_1)s 
> > 2017-02-02 08:25:58,789 INFO sqlalchemy.engine.base.Engine {'param_1': 
> > } 
> > Traceback (most recent call last): 
> >   File "sqlalchemy_casc_backref_test.py", line 70, in  
> > print(thing.dict()) 
> >   File "sqlalchemy_casc_backref_test.py", line 31, in dict 
> > 'type': self.type.type, 
> >   File "C:\Python35\lib\site-packages\sqlalchemy\orm\attributes.py", 
> > line 237, in __get__ 
> > return self.impl.get(instance_state(instance), dict_) 
> >   File "C:\Python35\lib\site-packages\sqlalchemy\orm\attributes.py", 
> > line 584, in get 
> > value = self.callable_(state, passive) 
> >   File "C:\Python35\lib\site-packages\sqlalchemy\orm\strategies.py", 
> > line 560, in _load_for_state 
> > return self._emit_lazyload(session, state, ident_key, passive) 
> >   File "", line 1, in  
> >   File "C:\Python35\lib\site-packages\sqlalchemy\orm\strategies.py", 
> > line 606, in _emit_lazyload 
> > return loading.load_on_ident(q, ident_key) 
> >   File "C:\Python35\lib\site-packages\sqlalchemy\orm\loading.py", line 
> > 223, in load_on_ident 
> > return q.one() 
> >   File "C:\Python35\lib\site-packages\sqlalchemy\orm\query.py", line 
> > 2754, in one 
> > ret = self.one_or_none() 
> >   File "C:\Python35\lib\site-packages\sqlalchemy\orm\query.py", line 
> > 2724, in one_or_none 
> > ret = list(self) 
> >   File "C:\Python35\lib\site-packages\sqlalchemy\orm\query.py", line 
> > 2795, in __iter__ 
> > return self._execute_and_instances(context) 
> >   File "C:\Python35\lib\site-packages\sqlalchemy\orm\query.py", line 
> > 2818, in _execute_and_instances 
> > result = conn.execute(querycontext.statement, self._params) 
> >   File "C:\Python35\lib\site-packages\sqlalchemy\engine\base.py", line 
> > 945, in execute 
> > return meth(self, multiparams, params) 
> >   File "C:\Python35\lib\site-packages\sqlalchemy\sql\elements.py", line 
> > 263, in _execute_on_connection 
> > return connection._execute_clauseelement(self, multiparams, params) 
> >   File "C:\Python35\lib\site-packages\sqlalchemy\engine\base.py", line 
> > 1053, in _execute_clauseelement 
> > compiled_sql, distilled_params 
> >   File "C:\Python35\lib\site-packages\sqlalchemy\engine\base.py", line 
> > 1189, in _execute_context 
> > context) 
> >   File "C:\Python35\lib\site-packages\sqlalchemy\engine\base.py", line 
> > 1396, in _handle_dbapi_exception 
> > util.reraise(*exc_info) 
> >   File "C:\Python35\lib\site-packages\sqlalchemy\util\compat.py", line 
> > 186, in reraise 
> > raise value 
> >   File "C:\Python35\lib\site-packages\sqlalchemy\engine\base.py", line 
> > 1182, in

[sqlalchemy] Session.enable_relationship_loading with inline select statement

[Shane Carey]

Hi, I have a use case where I need to enable relationship loading on a 
single object. However, I initialize the foreign key of this relationship 
using an inline select statement.

from sqlalchemy import *
from sqlalchemy import select, and_, event, inspect
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import *

Base = declarative_base()

class Type(Base):
__tablename__ = 'type'

id = Column(Integer, primary_key=True)
type = Column(String(8), unique=True)

class Thing(Base):
__tablename__ = 'thing'

id = Column(Integer, primary_key=True)
type_id = Column(Integer, ForeignKey(Type.id), nullable=False)
text = Column(String(8))

type = relationship(Type)

__mapper_args__ = {
'polymorphic_on': select([Type.type]).where(Type.id == type_id).as_scalar(),
'with_polymorphic': '*'
}

def dict(self):
return {
'id': self.id,
'type': self.type.type,
'text': self.text
}

@event.listens_for(Thing, 'init', propagate=True)
def set_identity(instance, *args, **kwargs):
mapper = object_mapper(instance)
instance.type_id = select([Type.id]).where(Type.type == 
mapper.polymorphic_identity)

class Stuff(Thing):
stuff = Column(String(8))

__mapper_args__ = {
'polymorphic_identity': 'stuff'
}

class Junk(Thing):
junk = Column(String(8))

__mapper_args__ = {
'polymorphic_identity': 'junk'
}


e = create_engine('mysql+pymysql://user:password@localhost/test', echo=True)

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

s = Session(e)

s.add_all([Type(type='stuff'), Type(type='junk')])

s.commit()

thing = Stuff(text='thing1', stuff='stuff1')

s.enable_relationship_loading(thing)

print(thing.dict())

>From the docs, I am fuzzy on whether or not this should be able to work 
because the relationship loading is enabled on the foreign key
which is set here to a select statement. I get the error:

2017-02-02 08:25:58,789 INFO sqlalchemy.engine.base.Engine SELECT type.id 
AS type_id, type.type AS type_type
FROM type
WHERE type.id = %(param_1)s
2017-02-02 08:25:58,789 INFO sqlalchemy.engine.base.Engine {'param_1': 
}
Traceback (most recent call last):
  File "sqlalchemy_casc_backref_test.py", line 70, in 
print(thing.dict())
  File "sqlalchemy_casc_backref_test.py", line 31, in dict
'type': self.type.type,
  File "C:\Python35\lib\site-packages\sqlalchemy\orm\attributes.py", line 
237, in __get__
return self.impl.get(instance_state(instance), dict_)
  File "C:\Python35\lib\site-packages\sqlalchemy\orm\attributes.py", line 
584, in get
value = self.callable_(state, passive)
  File "C:\Python35\lib\site-packages\sqlalchemy\orm\strategies.py", line 
560, in _load_for_state
return self._emit_lazyload(session, state, ident_key, passive)
  File "", line 1, in 
  File "C:\Python35\lib\site-packages\sqlalchemy\orm\strategies.py", line 
606, in _emit_lazyload
return loading.load_on_ident(q, ident_key)
  File "C:\Python35\lib\site-packages\sqlalchemy\orm\loading.py", line 223, 
in load_on_ident
return q.one()
  File "C:\Python35\lib\site-packages\sqlalchemy\orm\query.py", line 2754, 
in one
ret = self.one_or_none()
  File "C:\Python35\lib\site-packages\sqlalchemy\orm\query.py", line 2724, 
in one_or_none
ret = list(self)
  File "C:\Python35\lib\site-packages\sqlalchemy\orm\query.py", line 2795, 
in __iter__
return self._execute_and_instances(context)
  File "C:\Python35\lib\site-packages\sqlalchemy\orm\query.py", line 2818, 
in _execute_and_instances
result = conn.execute(querycontext.statement, self._params)
  File "C:\Python35\lib\site-packages\sqlalchemy\engine\base.py", line 945, 
in execute
return meth(self, multiparams, params)
  File "C:\Python35\lib\site-packages\sqlalchemy\sql\elements.py", line 
263, in _execute_on_connection
return connection._execute_clauseelement(self, multiparams, params)
  File "C:\Python35\lib\site-packages\sqlalchemy\engine\base.py", line 
1053, in _execute_clauseelement
compiled_sql, distilled_params
  File "C:\Python35\lib\site-packages\sqlalchemy\engine\base.py", line 
1189, in _execute_context
context)
  File "C:\Python35\lib\site-packages\sqlalchemy\engine\base.py", line 
1396, in _handle_dbapi_exception
util.reraise(*exc_info)
  File "C:\Python35\lib\site-packages\sqlalchemy\util\compat.py", line 186, 
in reraise
raise value
  File "C:\Python35\lib\site-packages\sqlalchemy\engine\base.py", line 
1182, in _execute_context
context)
  File "C:\Python35\lib\site-packages\sqlalchemy\engine\default.py", line 
462, in do_execute
cursor.execute(statement, parameters)
  File "C:\Python35\lib\site-packages\pymysql\cursors.py", line 164, in 
execute
query = self.mogrify(query, args)
  File "C:\Python35\lib\site-packages\pymysql\cursors.py", line 143, in 
mogrify
query = query % self._escape_args(args, conn)
  File "C:\Python35\lib\site-packages\pymysql\cursors.py", line 123, in 
_escape_args
return dict((key, conn.literal(val)) for (key, val) in args.items())
  File 

[sqlalchemy] Re: Duplicate primary key while merging polymorphic relationship

[Shane Carey]

I resolved this issue, it was a misconfiguration of my 'polymorphic_on'

On Wednesday, February 1, 2017 at 8:35:59 AM UTC-6, Shane Carey wrote:
>
> I took the SQL into mysql directly to see what may be going wrong, it 
> looks like the generated
>
> SELECT 
> thing.id AS thing_id,
> thing.type_id AS thing_type_id, 
> thing.txt AS thing_txt, 
> (SELECT type.id FROM type WHERE type.id = thing.type_id) AS 
> _sa_polymorphic_on, 
> thing.stuff AS thing_stuff
> FROM thing
> WHERE thing.id = 1 AND (SELECT type.id FROM type WHERE type.id = 
> thing.type_id) IN ('stuff');
>
> returns no rows, the final row of that query should be ... (SELECT 
> type.type FROM type ...
>
> which returns one row.
>
> I feel like I misconfigured something to generate the incorrect SQL.
>
> On Wednesday, February 1, 2017 at 8:14:06 AM UTC-6, Shane Carey wrote:
>>
>> I am having a strange issue with merging a polymorphic relationship, I've 
>> managed to reduce my problem to this code:
>>
>> from sqlalchemy import *
>> from sqlalchemy import select, and_, event, inspect
>> from sqlalchemy.orm import *
>> from sqlalchemy.ext.declarative import *
>>
>> Base = declarative_base()
>>
>> class Type(Base):
>> __tablename__ = 'type'
>>
>> id = Column(Integer, primary_key=True)
>> type = Column(String(8), unique=True)
>>
>> class Thing(Base):
>> __tablename__ = 'thing'
>>
>> id = Column(Integer, primary_key=True)
>> type_id = Column(Integer, ForeignKey(Type.id), nullable=False)
>> txt = Column(String(8))
>>
>> type = relationship(Type)
>> tags = relationship('Tag', backref='thing')
>>
>> __mapper_args__ = {
>> 'polymorphic_on': select([Type.id]).where(Type.id == type_id).as_scalar(),
>> 'with_polymorphic': '*'
>> }
>>
>> @event.listens_for(Thing, 'init', propagate=True)
>> def set_identity(instance, *args, **kwargs):
>> mapper = object_mapper(instance)
>> instance.type_id = select([Type.id]).where(Type.type == 
>> mapper.polymorphic_identity)
>>
>> class Stuff(Thing):
>> stuff = Column(String(8))
>>
>> __mapper_args__ = {
>> 'polymorphic_identity': 'stuff'
>> }
>>
>> class Junk(Thing):
>> junk = Column(String(8))
>>
>> __mapper_args__ = {
>> 'polymorphic_identity': 'junk'
>> }
>>
>> class Tag(Base):
>> __tablename__ = 'tag'
>>
>> id = Column(Integer, primary_key=True)
>> thing_id = Column(Integer, ForeignKey(Thing.id), nullable=False)
>> txt = Column(String(8))
>>
>> e = create_engine('mysql+pymysql://username:password@localhost/test', 
>> echo=True)
>>
>> Base.metadata.drop_all(e)
>> Base.metadata.create_all(e)
>>
>> s = Session(e)
>>
>> s.add_all([Type(type='stuff'), Type(type='junk')])
>>
>> thing = Stuff(id=1, txt='thing1', stuff='stuff1')
>> tag = Tag(id=1, txt='tag1', thing=thing)
>>
>> s.merge(tag)
>>
>> s.commit()
>>
>> thing = Stuff(id=1, txt='thing2', stuff='stuff2')
>> tag = Tag(id=1, txt='tag2', thing=thing)
>>
>> s.merge(tag)
>>
>> s.commit()
>>
>> This gives me the error 
>>
>> sqlalchemy.exc.IntegrityError: (pymysql.err.IntegrityError) (1062, 
>> "Duplicate entry '1' for key 'PRIMARY'") [SQL: 'INSERT INTO thing (id, 
>> type_id, txt, stuff) VALUES (%(id)s, (SELECT type.id \nFROM type \nWHERE 
>> type.type = %(type_1)s), %(txt)s, %(stuff)s)'] [parameters: {'txt': 
>> 'thing2', 'id': 1, 'type_1': 'stuff', 'stuff': 'stuff2'}]
>>
>> And the full output is
>>
>> 2017-02-01 08:07:29,401 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES 
>> LIKE 'sql_mode'
>> 2017-02-01 08:07:29,401 INFO sqlalchemy.engine.base.Engine {}
>> 2017-02-01 08:07:29,401 INFO sqlalchemy.engine.base.Engine SELECT 
>> DATABASE()
>> 2017-02-01 08:07:29,401 INFO sqlalchemy.engine.base.Engine {}
>> 2017-02-01 08:07:29,401 INFO sqlalchemy.engine.base.Engine show collation 
>> where `Charset` = 'utf8' and `Collation` = 'utf8_bin'
>> 2017-02-01 08:07:29,401 INFO sqlalchemy.engine.base.Engine {}
>> 2017-02-01 08:07:29,417 INFO sqlalchemy.engine.base.Engine SELECT 
>> CAST('test plain returns' AS CHAR(60)) AS anon_1
>> 2017-02-01 08:07:29,417 INFO sqlalchemy.engine.base.Engine {}
>> 2017-02-01 08:07:29,417 INFO sqlalchemy.engine.base.Engine SELECT 
>> CAST('test unicode returns' AS CHAR(60)) AS anon_1
>> 2017-02-01 08:07:29,417 INFO sqlalchemy.engine.base.Engine {}
>> 2017-02-01 08:07:29,417 INFO sqlalchemy.engine.base.Engine SELECT 
>> CAST('te

[sqlalchemy] Re: Duplicate primary key while merging polymorphic relationship

[Shane Carey]

I took the SQL into mysql directly to see what may be going wrong, it looks 
like the generated

SELECT 
thing.id AS thing_id,
thing.type_id AS thing_type_id, 
thing.txt AS thing_txt, 
(SELECT type.id FROM type WHERE type.id = thing.type_id) AS 
_sa_polymorphic_on, 
thing.stuff AS thing_stuff
FROM thing
WHERE thing.id = 1 AND (SELECT type.id FROM type WHERE type.id = 
thing.type_id) IN ('stuff');

returns no rows, the final row of that query should be ... (SELECT 
type.type FROM type ...

which returns one row.

I feel like I misconfigured something to generate the incorrect SQL.

On Wednesday, February 1, 2017 at 8:14:06 AM UTC-6, Shane Carey wrote:
>
> I am having a strange issue with merging a polymorphic relationship, I've 
> managed to reduce my problem to this code:
>
> from sqlalchemy import *
> from sqlalchemy import select, and_, event, inspect
> from sqlalchemy.orm import *
> from sqlalchemy.ext.declarative import *
>
> Base = declarative_base()
>
> class Type(Base):
> __tablename__ = 'type'
>
> id = Column(Integer, primary_key=True)
> type = Column(String(8), unique=True)
>
> class Thing(Base):
> __tablename__ = 'thing'
>
> id = Column(Integer, primary_key=True)
> type_id = Column(Integer, ForeignKey(Type.id), nullable=False)
> txt = Column(String(8))
>
> type = relationship(Type)
> tags = relationship('Tag', backref='thing')
>
> __mapper_args__ = {
> 'polymorphic_on': select([Type.id]).where(Type.id == type_id).as_scalar(),
> 'with_polymorphic': '*'
> }
>
> @event.listens_for(Thing, 'init', propagate=True)
> def set_identity(instance, *args, **kwargs):
> mapper = object_mapper(instance)
> instance.type_id = select([Type.id]).where(Type.type == 
> mapper.polymorphic_identity)
>
> class Stuff(Thing):
> stuff = Column(String(8))
>
> __mapper_args__ = {
> 'polymorphic_identity': 'stuff'
> }
>
> class Junk(Thing):
> junk = Column(String(8))
>
> __mapper_args__ = {
> 'polymorphic_identity': 'junk'
> }
>
> class Tag(Base):
> __tablename__ = 'tag'
>
> id = Column(Integer, primary_key=True)
> thing_id = Column(Integer, ForeignKey(Thing.id), nullable=False)
> txt = Column(String(8))
>
> e = create_engine('mysql+pymysql://username:password@localhost/test', 
> echo=True)
>
> Base.metadata.drop_all(e)
> Base.metadata.create_all(e)
>
> s = Session(e)
>
> s.add_all([Type(type='stuff'), Type(type='junk')])
>
> thing = Stuff(id=1, txt='thing1', stuff='stuff1')
> tag = Tag(id=1, txt='tag1', thing=thing)
>
> s.merge(tag)
>
> s.commit()
>
> thing = Stuff(id=1, txt='thing2', stuff='stuff2')
> tag = Tag(id=1, txt='tag2', thing=thing)
>
> s.merge(tag)
>
> s.commit()
>
> This gives me the error 
>
> sqlalchemy.exc.IntegrityError: (pymysql.err.IntegrityError) (1062, 
> "Duplicate entry '1' for key 'PRIMARY'") [SQL: 'INSERT INTO thing (id, 
> type_id, txt, stuff) VALUES (%(id)s, (SELECT type.id \nFROM type \nWHERE 
> type.type = %(type_1)s), %(txt)s, %(stuff)s)'] [parameters: {'txt': 
> 'thing2', 'id': 1, 'type_1': 'stuff', 'stuff': 'stuff2'}]
>
> And the full output is
>
> 2017-02-01 08:07:29,401 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES 
> LIKE 'sql_mode'
> 2017-02-01 08:07:29,401 INFO sqlalchemy.engine.base.Engine {}
> 2017-02-01 08:07:29,401 INFO sqlalchemy.engine.base.Engine SELECT 
> DATABASE()
> 2017-02-01 08:07:29,401 INFO sqlalchemy.engine.base.Engine {}
> 2017-02-01 08:07:29,401 INFO sqlalchemy.engine.base.Engine show collation 
> where `Charset` = 'utf8' and `Collation` = 'utf8_bin'
> 2017-02-01 08:07:29,401 INFO sqlalchemy.engine.base.Engine {}
> 2017-02-01 08:07:29,417 INFO sqlalchemy.engine.base.Engine SELECT 
> CAST('test plain returns' AS CHAR(60)) AS anon_1
> 2017-02-01 08:07:29,417 INFO sqlalchemy.engine.base.Engine {}
> 2017-02-01 08:07:29,417 INFO sqlalchemy.engine.base.Engine SELECT 
> CAST('test unicode returns' AS CHAR(60)) AS anon_1
> 2017-02-01 08:07:29,417 INFO sqlalchemy.engine.base.Engine {}
> 2017-02-01 08:07:29,417 INFO sqlalchemy.engine.base.Engine SELECT 
> CAST('test collated returns' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin 
> AS anon_1
> 2017-02-01 08:07:29,417 INFO sqlalchemy.engine.base.Engine {}
> 2017-02-01 08:07:29,417 INFO sqlalchemy.engine.base.Engine DESCRIBE `tag`
> 2017-02-01 08:07:29,417 INFO sqlalchemy.engine.base.Engine {}
> 2017-02-01 08:07:29,417 INFO sqlalchemy.engine.base.Engine ROLLBACK
> 2017-02-01 08:07:29,417 INFO sqlalchemy.engine.base.Engine DESCRIBE `type`
> 2017-02-01 08:07:29,417 INFO sqlalchemy.engine.base.Engine {}
> 2017-02-01 08:07:29,417 INFO sqlalchemy.engine.base.Engine ROLLBACK
> 2017-02-01 08:07:29,417 INFO sqlalchemy.engine.base.Engine DESCRIBE `thing`
> 20

[sqlalchemy] Duplicate primary key while merging polymorphic relationship

[Shane Carey]

I am having a strange issue with merging a polymorphic relationship, I've 
managed to reduce my problem to this code:

from sqlalchemy import *
from sqlalchemy import select, and_, event, inspect
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import *

Base = declarative_base()

class Type(Base):
__tablename__ = 'type'

id = Column(Integer, primary_key=True)
type = Column(String(8), unique=True)

class Thing(Base):
__tablename__ = 'thing'

id = Column(Integer, primary_key=True)
type_id = Column(Integer, ForeignKey(Type.id), nullable=False)
txt = Column(String(8))

type = relationship(Type)
tags = relationship('Tag', backref='thing')

__mapper_args__ = {
'polymorphic_on': select([Type.id]).where(Type.id == type_id).as_scalar(),
'with_polymorphic': '*'
}

@event.listens_for(Thing, 'init', propagate=True)
def set_identity(instance, *args, **kwargs):
mapper = object_mapper(instance)
instance.type_id = select([Type.id]).where(Type.type == 
mapper.polymorphic_identity)

class Stuff(Thing):
stuff = Column(String(8))

__mapper_args__ = {
'polymorphic_identity': 'stuff'
}

class Junk(Thing):
junk = Column(String(8))

__mapper_args__ = {
'polymorphic_identity': 'junk'
}

class Tag(Base):
__tablename__ = 'tag'

id = Column(Integer, primary_key=True)
thing_id = Column(Integer, ForeignKey(Thing.id), nullable=False)
txt = Column(String(8))

e = create_engine('mysql+pymysql://username:password@localhost/test', 
echo=True)

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

s = Session(e)

s.add_all([Type(type='stuff'), Type(type='junk')])

thing = Stuff(id=1, txt='thing1', stuff='stuff1')
tag = Tag(id=1, txt='tag1', thing=thing)

s.merge(tag)

s.commit()

thing = Stuff(id=1, txt='thing2', stuff='stuff2')
tag = Tag(id=1, txt='tag2', thing=thing)

s.merge(tag)

s.commit()

This gives me the error 

sqlalchemy.exc.IntegrityError: (pymysql.err.IntegrityError) (1062, 
"Duplicate entry '1' for key 'PRIMARY'") [SQL: 'INSERT INTO thing (id, 
type_id, txt, stuff) VALUES (%(id)s, (SELECT type.id \nFROM type \nWHERE 
type.type = %(type_1)s), %(txt)s, %(stuff)s)'] [parameters: {'txt': 
'thing2', 'id': 1, 'type_1': 'stuff', 'stuff': 'stuff2'}]

And the full output is

2017-02-01 08:07:29,401 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES 
LIKE 'sql_mode'
2017-02-01 08:07:29,401 INFO sqlalchemy.engine.base.Engine {}
2017-02-01 08:07:29,401 INFO sqlalchemy.engine.base.Engine SELECT DATABASE()
2017-02-01 08:07:29,401 INFO sqlalchemy.engine.base.Engine {}
2017-02-01 08:07:29,401 INFO sqlalchemy.engine.base.Engine show collation 
where `Charset` = 'utf8' and `Collation` = 'utf8_bin'
2017-02-01 08:07:29,401 INFO sqlalchemy.engine.base.Engine {}
2017-02-01 08:07:29,417 INFO sqlalchemy.engine.base.Engine SELECT 
CAST('test plain returns' AS CHAR(60)) AS anon_1
2017-02-01 08:07:29,417 INFO sqlalchemy.engine.base.Engine {}
2017-02-01 08:07:29,417 INFO sqlalchemy.engine.base.Engine SELECT 
CAST('test unicode returns' AS CHAR(60)) AS anon_1
2017-02-01 08:07:29,417 INFO sqlalchemy.engine.base.Engine {}
2017-02-01 08:07:29,417 INFO sqlalchemy.engine.base.Engine SELECT 
CAST('test collated returns' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin 
AS anon_1
2017-02-01 08:07:29,417 INFO sqlalchemy.engine.base.Engine {}
2017-02-01 08:07:29,417 INFO sqlalchemy.engine.base.Engine DESCRIBE `tag`
2017-02-01 08:07:29,417 INFO sqlalchemy.engine.base.Engine {}
2017-02-01 08:07:29,417 INFO sqlalchemy.engine.base.Engine ROLLBACK
2017-02-01 08:07:29,417 INFO sqlalchemy.engine.base.Engine DESCRIBE `type`
2017-02-01 08:07:29,417 INFO sqlalchemy.engine.base.Engine {}
2017-02-01 08:07:29,417 INFO sqlalchemy.engine.base.Engine ROLLBACK
2017-02-01 08:07:29,417 INFO sqlalchemy.engine.base.Engine DESCRIBE `thing`
2017-02-01 08:07:29,417 INFO sqlalchemy.engine.base.Engine {}
2017-02-01 08:07:29,417 INFO sqlalchemy.engine.base.Engine ROLLBACK
2017-02-01 08:07:29,417 INFO sqlalchemy.engine.base.Engine DESCRIBE `tag`
2017-02-01 08:07:29,417 INFO sqlalchemy.engine.base.Engine {}
2017-02-01 08:07:29,417 INFO sqlalchemy.engine.base.Engine ROLLBACK
2017-02-01 08:07:29,417 INFO sqlalchemy.engine.base.Engine DESCRIBE `type`
2017-02-01 08:07:29,417 INFO sqlalchemy.engine.base.Engine {}
2017-02-01 08:07:29,417 INFO sqlalchemy.engine.base.Engine ROLLBACK
2017-02-01 08:07:29,417 INFO sqlalchemy.engine.base.Engine DESCRIBE `thing`
2017-02-01 08:07:29,417 INFO sqlalchemy.engine.base.Engine {}
2017-02-01 08:07:29,417 INFO sqlalchemy.engine.base.Engine ROLLBACK
2017-02-01 08:07:29,417 INFO sqlalchemy.engine.base.Engine
CREATE TABLE type (
id INTEGER NOT NULL AUTO_INCREMENT,
type VARCHAR(8),
PRIMARY KEY (id),
UNIQUE (type)
)


2017-02-01 08:07:29,433 INFO sqlalchemy.engine.base.Engine {}
2017-02-01 08:07:29,479 INFO sqlalchemy.engine.base.Engine COMMIT
2017-02-01 08:07:29,479 INFO sqlalchemy.engine.base.Engine
CREATE TABLE thing (
id INTEGER NOT NULL AUTO_INCREMENT,
type_id INTEGER NOT NULL,
txt VARCHAR(8),
   

Re: [sqlalchemy] polymorphic_on using relationship discriminator

[Shane Carey]

Thanks for you help, Mike!

On Monday, January 30, 2017 at 10:36:06 AM UTC-6, Mike Bayer wrote:
>
>
>
> On 01/30/2017 11:06 AM, Shane Carey wrote: 
> > Thanks for the help! I was able to get this working with 
> > 
> > select([Series.type]).where(Series.id == series_id).as_scalar() 
> > 
> > I have a follow up question though, when initializing these objects, it 
> > seems I have to do something like this 
> > 
> > session.add(Publication(series=session.query(Series).filter(Series.type 
> > == 'book').one())) 
> > 
> > This becomes strange when I already know the subclass of the publication 
> > 
> > session.add(Book(series=session.query(Series).filter(Series.type == 
> > 'book').one())) 
> > 
> > Is there a way to setup the relationship in such a way that I can 
> > initialize it with a column value, so that the underlying query is 
> > performed during the INSERT i.e. 
> > 
> > session.add(Publication(series='book')) # Creates the subquery during 
> INSERT 
> > 
> > or 
> > 
> > session.add(Book()) 
> > 
> > This would make the superfluous querying for the appropriate row in the 
> > Series table go away, perhaps there is a keyword on the relationship 
> > which would allow for this? 
>
> So the most direct way to do this is in your constructor, and to assign 
> to the SQL expression instead of the result, so that the SQL expression 
> runs inline.  But also we would skip using the relationship here, we'd 
> go to the FK directly: 
>
> class Book(...): 
>  def __init__(self, **kw): 
>  self.series_id = select([Series.id]).where(Series.type == 'book') 
>
>
> the docs mentioned before illustrate using an event listener for this 
> purpose - as well as using mapper.polymorphic_identity to genericize it: 
>
> from sqlalchemy import event 
> from sqlalchemy import inspect 
>
> @event.listens_for(Publication, "init", propagate=True) 
> def set_identity(instance, *arg, **kw): 
>  mapper = inspect(instance) 
>  instance.series_id = select([Series.id]).where(Series.type == 
> mapper.polymorphic_identity) 
>
>
> These snippets are all good material for a new sub-section underneath 
> http://docs.sqlalchemy.org/en/latest/orm/inheritance.html . 
>
>
>
>
>
>
>
>
> > 
> > On Friday, January 27, 2017 at 5:34:30 PM UTC-6, Mike Bayer wrote: 
> > 
> > There's examples of custom SQL expressions at: 
> > 
> > 
> http://docs.sqlalchemy.org/en/latest/orm/mapping_api.html#sqlalchemy.orm.mapper.params.polymorphic_on
>  
> > <
> http://docs.sqlalchemy.org/en/latest/orm/mapping_api.html#sqlalchemy.orm.mapper.params.polymorphic_on>
>  
>
> > 
> > 
> > These examples don't currently illustrate a subquery (which would be 
> a 
> > good idea), but the idea is you make a scalar correlated subquery (a 
> > Core example is at 
> > 
> http://docs.sqlalchemy.org/en/latest/core/tutorial.html#correlated-subqueries 
> > <
> http://docs.sqlalchemy.org/en/latest/core/tutorial.html#correlated-subqueries>)
>  
>
> > 
> > and you can stick that right into polymorphic_on, or send it as a 
> > column_property. 
> > 
> > 
> > class Publication(db.Model): 
> >  __tablename__ = 'publication' 
> >  id = db.Column(db.Integer, primary_key=True) 
> >  series_id = db.Column(db.Integer, db.ForeignKey('series.id 
> > <http://series.id>'), 
> > nullable=False) 
> >  series = db.relationship(Series, backref='publications') 
> >  __mapper_args__ = { 
> >  'polymorphic_on': select([series.type]).where(series_id == 
> > Series.id).as_scalar(), 
> >  'polymorphic_identity': None 
> >  } 
> > 
> > 
> > 
> > 
> > On 01/27/2017 06:20 PM, Shane Carey wrote: 
> > > Hi, I want to use a discriminator based upon a column of a related 
> > table. 
> > > 
> > > I found the relevant place in the 
> > > docs 
> > 
> http://docs.sqlalchemy.org/en/latest/orm/inheritance.html#joined-table-inheritance
>  
> > <
> http://docs.sqlalchemy.org/en/latest/orm/inheritance.html#joined-table-inheritance>
>  
>
> > 
> > > 
> > > "The discriminator column is only needed if polymorphic loading is 
> > > desired, as is usually the case. It is not strictly necessary that 
> > it be 
> > > present directly on the b

Re: [sqlalchemy] polymorphic_on using relationship discriminator

[Shane Carey]

Thanks for the help! I was able to get this working with 

select([Series.type]).where(Series.id == series_id).as_scalar()

I have a follow up question though, when initializing these objects, it 
seems I have to do something like this

session.add(Publication(series=session.query(Series).filter(Series.type == 
'book').one()))

This becomes strange when I already know the subclass of the publication

session.add(Book(series=session.query(Series).filter(Series.type == 
'book').one()))

Is there a way to setup the relationship in such a way that I can 
initialize it with a column value, so that the underlying query is 
performed during the INSERT i.e.

session.add(Publication(series='book')) # Creates the subquery during INSERT

or

session.add(Book())

This would make the superfluous querying for the appropriate row in the 
Series table go away, perhaps there is a keyword on the relationship which 
would allow for this?

On Friday, January 27, 2017 at 5:34:30 PM UTC-6, Mike Bayer wrote:
>
> There's examples of custom SQL expressions at: 
>
>
> http://docs.sqlalchemy.org/en/latest/orm/mapping_api.html#sqlalchemy.orm.mapper.params.polymorphic_on
>  
>
> These examples don't currently illustrate a subquery (which would be a 
> good idea), but the idea is you make a scalar correlated subquery (a 
> Core example is at 
>
> http://docs.sqlalchemy.org/en/latest/core/tutorial.html#correlated-subqueries)
>  
>
> and you can stick that right into polymorphic_on, or send it as a 
> column_property. 
>
>
> class Publication(db.Model): 
>  __tablename__ = 'publication' 
>  id = db.Column(db.Integer, primary_key=True) 
>  series_id = db.Column(db.Integer, db.ForeignKey('series.id'), 
> nullable=False) 
>  series = db.relationship(Series, backref='publications') 
>  __mapper_args__ = { 
>  'polymorphic_on': select([series.type]).where(series_id == 
> Series.id).as_scalar(), 
>  'polymorphic_identity': None 
>  } 
>
>
>
>
> On 01/27/2017 06:20 PM, Shane Carey wrote: 
> > Hi, I want to use a discriminator based upon a column of a related 
> table. 
> > 
> > I found the relevant place in the 
> > docs 
> http://docs.sqlalchemy.org/en/latest/orm/inheritance.html#joined-table-inheritance
>  
> > 
> > "The discriminator column is only needed if polymorphic loading is 
> > desired, as is usually the case. It is not strictly necessary that it be 
> > present directly on the base mapped table, and can instead be defined on 
> > a derived select statement that’s used when the class is queried; 
> > however, this is a much more sophisticated configuration scenario" 
> > 
> > However this is not working for me, additionally I found this 
> > stackoverflow question which also encounters this problem to a 
> > tee. 
> http://stackoverflow.com/questions/40862634/sqlalchemy-orm-polymorphic-on-from-relationship
>  
> > 
> > What is the correct method to have a discriminator based on a more 
> > complex select statement? Thank you! 
> > 
> > -- 
> > SQLAlchemy - 
> > The Python SQL Toolkit and Object Relational Mapper 
> > 
> > http://www.sqlalchemy.org/ 
> > 
> > To post example code, please provide an MCVE: Minimal, Complete, and 
> > Verifiable Example. See http://stackoverflow.com/help/mcve for a full 
> > description. 
> > --- 
> > You received this message because you are subscribed to the Google 
> > Groups "sqlalchemy" group. 
> > To unsubscribe from this group and stop receiving emails from it, send 
> > an email to sqlalchemy+...@googlegroups.com  
> > <mailto:sqlalchemy+unsubscr...@googlegroups.com >. 
> > To post to this group, send email to sqlal...@googlegroups.com 
>  
> > <mailto:sqlal...@googlegroups.com >. 
> > Visit this group at https://groups.google.com/group/sqlalchemy. 
> > For more options, visit https://groups.google.com/d/optout. 
>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Re: JSON_EXTRACT with tuple generates incorrect SQL on MYSQL 5.7

[Shane Carey]

I am running SQLAlchemy 1.1.0

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] JSON_EXTRACT with tuple generates incorrect SQL on MYSQL 5.7

[Shane Carey]

Hi all,

I've encountered an error with filtering by JSON values on MYSQL 5.7. My 
code:

def MyClass:
__tablename__ = 'my_class'

data = Column(JSON)

keys = ('code',)
val = ('val',)
query = session.query(MyClass).filter(MyClass.data[keys].in_(val)).all()

generates the SQL snippet

JSON_EXTRACT(my_class.data, %s) IN (%s)
('$."code"', 'val')

The double quotes on the parameter are incorrect, should be

('$.code', 'val')

Is this a SQLAlchemy error, or my mistake?


-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.