Re: [sqlalchemy] Issue on inserts statements for pg explain recipe
On Thu, Oct 4, 2018 at 5:10 AM wrote:
>
> That's great ! Thanks a lot !
>
> Btw it seems that the
>
> self.inline = getattr(stmt, 'inline', None)
>
> is no longer necessary, I tryed my insert exemple with it commented and it
> still runned fine.
well there's also an "inline" argument for Insert() so maybe it was
trying to preserve that, I don't remember.
>
>
> Le jeudi 4 octobre 2018 05:23:02 UTC+2, Mike Bayer a écrit :
>>
>> 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 =
Re: [sqlalchemy] Issue on inserts statements for pg explain recipe
That's great ! Thanks a lot !
Btw it seems that the
self.inline = getattr(stmt, 'inline', None)
is no longer necessary, I tryed my insert exemple with it commented and it
still runned fine.
Le jeudi 4 octobre 2018 05:23:02 UTC+2, Mike Bayer a écrit :
>
> 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)
> >
> >
> >
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
>
>
[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.
