Re: [sqlalchemy] Re: question about `association_proxy` interface
On Wednesday, October 3, 2018 at 9:40:37 AM UTC-4, Mike Bayer wrote: > > > those are not going to change within 1.2 or 1.3 but it's not ideal to > be relying on them. For query._entities, you can use the public > accessor query.column_descriptions. for _with_options I'm not sure > what it is you want to do. > Thanks. This is good to know. I'll try adjusting with `column_descriptions`. The 'with_options' is used by some code that attempts to determine if a 'contains_eager' or joinedload/subqueryload was made. In response to your other suggestion, I never thought of dynamically generating the proxies. I don't think that code meeds my requirements as-is, but it definitely points me in the right direction and I can slightly alter it. Thank you so much. This is wonderful. -- 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] Issue on inserts statements for pg explain recipe
The insert is by itself not a RETURNING and doesn't return any rows.
The actual exception raised is:
sqlalchemy.exc.ResourceClosedError: This result object does not return
rows. It has been closed automatically.
and you'll get this just if you run the insert by itself:
returning_query = Foo.__table__.insert()
print(sess.execute(returning_query, {'id': 1}).fetchall())
Python 3 is just also showing you the exception we catch internally
which is the "'NoneType' object has no attribute 'fetchall'" one, but
that is normal, we are catching that to detect that this result
doesn't return any rows.
the cursor is getting closed because for any DML that doesn't return
rows, it closes the cursor because there's no other point at which it
could naturally be closed automatically from the point of view of the
resultproxy.
If you tell the insert statement to return rows, the cursor doesnt get
closed and the explain gets the rows back:
returning_query = Foo.__table__.insert().returning(Foo.id)
print(sess.execute(explain(returning_query), {"id": 1}).fetchall())
EXPLAIN INSERT INTO foo (id) VALUES (%(id)s) RETURNING foo.id
2018-10-03 23:15:38,272 INFO sqlalchemy.engine.base.Engine {'id': 1}
[('Insert on foo (cost=0.00..0.01 rows=1 width=4)',), (' -> Result
(cost=0.00..0.01 rows=1 width=4)',)]
to get the insert() to return the rows without returning being
present, we have to flip off the flags that are telling the execution
context that this is an INSERT, or more generally crud, that are
making it autoclose the cursor:
@compiles(explain, 'postgresql')
def pg_explain(element, compiler, **kw):
text = "EXPLAIN "
if element.analyze:
text += "ANALYZE "
text += compiler.process(element.statement, **kw)
compiler.isinsert = compiler.isupdate = compiler.isdelete = False
return text
that seems to fix the codepath that is looking for _returning also, so
I'll add that.
On Wed, Oct 3, 2018 at 5:04 AM wrote:
>
> Hello,
>
> I use the recipe for explain as described
> (https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/Explain), which
> does the job completely for select statements (I have no idea how it works
> however :p) in version 1.2.11
> The recipe at the moment is
>
> from sqlalchemy import *
> from sqlalchemy.ext.compiler import compiles
> from sqlalchemy.sql.expression import Executable, ClauseElement,
> _literal_as_text
>
>
> class explain(Executable, ClauseElement):
> def __init__(self, stmt, analyze=False):
> self.statement = _literal_as_text(stmt)
> self.analyze = analyze
> # helps with INSERT statements
> self.inline = getattr(stmt, 'inline', None)
>
>
> @compiles(explain, 'postgresql')
> def pg_explain(element, compiler, **kw):
> text = "EXPLAIN "
> if element.analyze:
> text += "ANALYZE "
> text += compiler.process(element.statement, **kw)
> return text
>
>
> The recipe is not up to date, as it doesn't work on inserts, however by
> replacing
>
> self.inline = getattr(stmt, 'inline', None)
> by
> self._returning = getattr(stmt, '_returning', None)
> self.table = getattr(stmt, 'table', None)
>
> It works on inserts with returning, however it crashes when I try to
> fetchall() on explain of simple inserts with a "AttributeError: 'NoneType'
> object has no attribute 'fetchall'"
> Below a code which have an error
>
>
>
> from sqlalchemy import *
> from sqlalchemy.ext.compiler import compiles
> from sqlalchemy.ext.declarative import declarative_base
> from sqlalchemy.orm import sessionmaker, aliased
>
>
>
>
> class explain(Executable, ClauseElement):
> def __init__(self, stmt, analyze=False):
> self.statement = _literal_as_text(stmt)
> self.analyze = analyze
> # helps with INSERT statements
> self._returning = getattr(stmt, '_returning', None)
>
>
> self.table = getattr(stmt, 'table', None)
>
>
>
>
> @compiles(explain, 'postgresql')
> def pg_explain(element, compiler, **kw):
> text = "EXPLAIN "
> if element.analyze:
> text += "ANALYZE "
> text += compiler.process(element.statement, **kw)
> return text
>
>
>
>
> Base = declarative_base()
>
>
> class Foo(Base):
> __tablename__ = 'foo'
> id = Column(Integer, primary_key=True)
>
>
> engine = create_engine(postgres_uri, echo=False)
>
>
> Base.metadata.create_all(engine)
>
>
> sess = sessionmaker(engine)()
>
>
> returning_query = Foo.__table__.insert()
>
>
> print(sess.execute(explain("INSERT INTO foo VALUES (1)")).fetchall())
> print(sess.execute(explain(returning_query), {'id': 1}).fetchall())
>
>
> My 2 questions :
> - can the recipe be updated for newer versions of sqlalchemy (the inline ->
> _returning and table change) ?
> - How can I change the recipe so I can fetch for inserts (I know it is pretty
> fucking useless, but it is supposed not to crash) ?
>
> --
> Nicolas Rolin
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
>
Re: [sqlalchemy] Is there any way to get around the "redundancy" of coupled .join and contains_eager chains?
Ok, you're gonna have to give me a pointer or two to where the aliasing is actually happening internally to get this done. I've poked and prodded the source, but I can't find where the aliasing *actually* happens. I'm assuming it's in *strategies.py*, and *_generate_row_adapter*, but I can't really figure it out. On Monday, October 1, 2018 at 8:41:27 PM UTC+1, Mike Bayer wrote: > > that would be a much easier option to add. however you need to be OK > adding your criteria with "filter" and not as part of the ON clause. > > feel free to provide a PR for that which includes tests.im not > sure how hard of a change it is as the joined eager load code is > pretty intricate. might need some flags propagated in a bunch of > places, not sure. > > > -- > > 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 . > > To post to this group, send email to 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.
Re: [sqlalchemy] question about `association_proxy` interface
I would look to generalize the whole thing, e.g. auto-generate the
*Metacontent class, as well as the association proxy (like a
HasMetacontent mixin for Item), the assoc proxy can be genericized
starting like this:
class Item(Base):
__tablename__ = 'item'
id = Column(Integer, primary_key=True)
@declared_attr
def item_description(cls):
def creator(description):
target = inspect(cls).relationships.item_metacontent.mapper.class_
return target(item_description=desc)
return association_proxy(
'item_metacontent', 'item_description', creator=creator)
item_metacontent = relationship("ItemMetacontent",
primaryjoin="Item.id==ItemMetacontent.id",
uselist=False,
back_populates = 'item',
)
On Tue, Oct 2, 2018 at 3:48 PM Jonathan Vanasco wrote:
>
> I have a common design in my database in which the heavy write/update columns
> exist in their own 'metacontent' table. An `association_proxy` is used to
> link them:
>
> class Item(Base):
> __tablename__ = 'item'
> id = Column(Integer, primary_key=True)
> item_description = association_proxy('item_metacontent',
> 'item_description')
> item_metacontent = relationship("ItemMetacontent",
> primaryjoin="Item.id==ItemMetacontent.id",
> uselist=False,
> back_populates = 'item',
> )
>
>
> class ItemMetacontent(Base):
> __tablename__ = 'item_metacontent'
> id = Column(Integer, ForeignKey("item.id"), nullable=False,
> primary_key=True)
> item_description = Column(UnicodeText, nullable=True)
> item = relationship("Item",
> primaryjoin="ItemMetacontent.id==Item.id",
> uselist=False,
> back_populates = 'item_metacontent',
> )
>
> The issue I've run into is in the creation of new objects.
>
> The required code looks like this...
>
> _item = Item()
> _item.id = 1
> _item.item_metacontent = ItemMetacontent() # generate a new
> ItemMetacontent object
> _item.item_description = 'example description'
>
>
> While metacontent does offer a constructor hook...
>
> item_description = association_proxy('item_metacontent',
> 'item_description',
> creator=lambda desc:
> ItemMetacontent(item_description= desc))
>
>
> It is not optimal to implement in my situation for two reasons:
>
> * the order of imports. SqlAlchemy's string syntax is preferable or required
> in some cases.
> * the number of association_proxy columns. some tables have over a dozen
> proxied columns.
>
> It would be ideal, at least in a 1:1 relationship, to automatically create
> the object of an AssociationProxy target when it does not exist.
>
> Does anyone know if that is possible in the current codebase?
>
> --
> 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 -
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] Re: question about `association_proxy` interface
On Tue, Oct 2, 2018 at 4:44 PM Jonathan Vanasco wrote:
>
> And a quick followup to Michael:
>
> I didn't want to pollute the comments in
> https://bitbucket.org/zzzeek/sqlalchemy/issues/3225/query-heuristic-inspection-system
> as there may not have been any substantial changes and I'm just reviewing my
> old notes wrong...
>
> How long do you think it is safe to inspect query attributes like:
>
> * `query._with_options`
> * `query._entities`
those are not going to change within 1.2 or 1.3 but it's not ideal to
be relying on them. For query._entities, you can use the public
accessor query.column_descriptions. for _with_options I'm not sure
what it is you want to do.
>
> Reviewing some of my old notes, it looks like these used to work:
>
> * `query.__dict__.get('with_options')`
> * `query.__dict__.get('entities')`
>
> but they've been replaced at some point with a leading underscore, and a
> corresponding attribute.
>
> --
> 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 -
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] Issue on inserts statements for pg explain recipe
Hello,
I use the recipe for explain as described
(https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/Explain), which
does the job completely for select statements (I have no idea how it works
however :p) in version 1.2.11
The recipe at the moment is
from sqlalchemy import *
from sqlalchemy.ext.compiler import compiles
from sqlalchemy.sql.expression import Executable, ClauseElement,
_literal_as_text
class explain(Executable, ClauseElement):
def __init__(self, stmt, analyze=False):
self.statement = _literal_as_text(stmt)
self.analyze = analyze
# helps with INSERT statements
self.inline = getattr(stmt, 'inline', None)
@compiles(explain, 'postgresql')
def pg_explain(element, compiler, **kw):
text = "EXPLAIN "
if element.analyze:
text += "ANALYZE "
text += compiler.process(element.statement, **kw)
return text
The recipe is not up to date, as it doesn't work on inserts, however by
replacing
self.inline = getattr(stmt, 'inline', None)
by
self._returning = getattr(stmt, '_returning', None)
self.table = getattr(stmt, 'table', None)
It works on inserts with returning, however it crashes when I try to
fetchall() on explain of simple inserts with a "AttributeError: 'NoneType'
object has no attribute 'fetchall'"
Below a code which have an error
from sqlalchemy import *
from sqlalchemy.ext.compiler import compiles
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, aliased
class explain(Executable, ClauseElement):
def __init__(self, stmt, analyze=False):
self.statement = _literal_as_text(stmt)
self.analyze = analyze
# helps with INSERT statements
self._returning = getattr(stmt, '_returning', None)
self.table = getattr(stmt, 'table', None)
@compiles(explain, 'postgresql')
def pg_explain(element, compiler, **kw):
text = "EXPLAIN "
if element.analyze:
text += "ANALYZE "
text += compiler.process(element.statement, **kw)
return text
Base = declarative_base()
class Foo(Base):
__tablename__ = 'foo'
id = Column(Integer, primary_key=True)
engine = create_engine(postgres_uri, echo=False)
Base.metadata.create_all(engine)
sess = sessionmaker(engine)()
returning_query = Foo.__table__.insert()
print(sess.execute(explain("INSERT INTO foo VALUES (1)")).fetchall())
print(sess.execute(explain(returning_query), {'id': 1}).fetchall())
My 2 questions :
- can the recipe be updated for newer versions of sqlalchemy (the inline ->
_returning and table change) ?
- How can I change the recipe so I can fetch for inserts (I know it is
pretty fucking useless, but it is supposed not to crash) ?
--
Nicolas Rolin
--
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.
