explain analyze on statement

[Zsolt Ero]

Hi, I'm using the following snippet to run explain analyize on raw sql (PostgreSQL, psycopg2).

def print_explain(engine, raw_sql, values):
    sql_text = text('EXPLAIN (ANALYZE, BUFFERS) ' + raw_sql)
    q = engine.execute(sql_text, values)
    lines = q.fetchall()
    print('--- EXPLAIN ANALYZE ---')
    for l in lines:
        print(l[0])
    print('--- EXPLAIN END     ---')

How can I make it work with Core statements? So far my best idea seems to be:

def print_explain_stmt(stmt):
    raw_sql = str(
        stmt.compile(dialect=postgresql.dialect(), compile_kwargs={"literal_binds": True})
    )

    sql_text = text('EXPLAIN (ANALYZE, BUFFERS) ' + raw_sql)
    q = pg_engine.execute(sql_text)
    lines = q.fetchall()
    print('--- EXPLAIN ANALYZE ---')
    for l in lines:
        print(l[0])
    print('--- EXPLAIN END     ---')

But is this OK? I mean compiling the statement isn't really reliable for some column types. Can you make this function to use the stmt directly?

[Mike Bayer]

There's a recipe at https://github.com/sqlalchemy/sqlalchemy/wiki/Explain that should give you all you need.

--
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.

[Zsolt Ero]

Thanks it works perfectly, even with datetimes!

Can I do something similar to make

stmt.compile(dialect=postgresql.dialect(),
compile_kwargs={"literal_binds": True})

compatible with datetime? Or maybe not this, but I'm looking for a way
to print a statement which I could copy and paste into psql console.

Zsolt

[Mike Bayer]

that's what literal_binds is for but it doesn't support formatting
every kind of type directly, so I assume you're getting an error
message, at the moment the literal_processor can't be injected so you
have to use a new type:

from sqlalchemy import *
from sqlalchemy.dialects import postgresql
from sqlalchemy.ext.compiler import compiles
import datetime


class SDateTime(TypeDecorator):
impl = DateTime

def literal_processor(self, dialect):
return lambda value: str(value)


print(
select([column("q", SDateTime) == datetime.datetime.now()]).compile(
dialect=postgresql.dialect(), compile_kwargs=dict(literal_binds=True)
)
)




>
> Zsolt

[Zsolt Ero]

Do I understand it right that there is no approach which could print a
query without the need to modify it, if it contains datetimes?

If so, how can I turn a where statement into something which is
printable using SDateTime?

.where(trips.c.end_time >= datetime.datetime.now())

Zsolt

> You received this message because you are subscribed to a topic in the Google Groups "sqlalchemy" group.
> To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/KXnv1_9B_Fw/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to sqlalchemy+...@googlegroups.com.

[Mike Bayer]

On Thu, Jan 24, 2019 at 9:07 PM Zsolt Ero <zsol...@gmail.com> wrote:
>
> Do I understand it right that there is no approach which could print a
> query without the need to modify it, if it contains datetimes?
>
> If so, how can I turn a where statement into something which is
> printable using SDateTime?
>
> .where(trips.c.end_time >= datetime.datetime.now())

where you define your Table and its column called end_time, don't use
DateTime, use your custom version of that type.

if you are relying upon reflection, use a column_reflect event
(https://docs.sqlalchemy.org/en/latest/core/events.html?highlight=column_reflect#sqlalchemy.events.DDLEvents.column_reflect)
to replace occurrences of DateTime with the custom DateTime object.


SQLAlchemy can of course eventually support rendering datetimes as
literal strings but this becomes a slippery slope where it has to
support literal rendering for every possible type, and we're then in a
place where we are reimplementing what the DBAPI already does and I
don't like to be doing that. We likely could benefit from a hook
that works like @compiles here but that's not available at this
moment.

[Zsolt Ero]

I understand. I define my tables like

Table('tablename', metadata, Column('time', DateTime(timezone=True),
nullable=False))

Can I simply replace DateTime with SDateTime and use timezone=True, or
I'd need to add some initializer args for timezone?

Zsolt

[Mike Bayer]

On Fri, Jan 25, 2019 at 6:37 PM Zsolt Ero <zsol...@gmail.com> wrote:
>
> I understand. I define my tables like
>
> Table('tablename', metadata, Column('time', DateTime(timezone=True),
> nullable=False))
>
> Can I simply replace DateTime with SDateTime and use timezone=True, or
> I'd need to add some initializer args for timezone?

you should be able to do that, sure. TypeDecorator sends the
constructor arguments through to impl.

[Mike Bayer]

note someone was wroking on adding literal date rendering for Postgresql at:

https://gerrit.sqlalchemy.org/#/c/sqlalchemy/sqlalchemy/+/18/

but this review has fallen out of work. If you'd like to submit a
complete feature request we can try to revive the work here.

[Zsolt Ero]

Just a conclusion to this thread, I've been using the given snippet
with a minor modification with great success. The modification was
that single quotation marks had to be added around datetime.

class PGDateTime(TypeDecorator):

impl = DateTime

def literal_processor(self, dialect):

return lambda value: "'" + str(value) + "'"

If this could be implemented to be recognised automatically it'd very
valuable for a lot of users! But for me personally, I'm very happy
with the snippet.