[sqlalchemy] Writing ORM from a complex sql statement
Hi there,
I really have no idea how to do this via sqlalchemy. I have 2 tables:
class NodesModel(db.Model):
__tablename__ = 'nodes'
id = db.Column(db.BigInteger, primary_key=True)
topic = db.Column(db.String(20), nullable=False)
pn = relationship("PNModel", backref="nodes")
class PNModel(db.Model):
__tablename__ = 'pn'
post_id = db.Column(db.BigInteger, db.ForeignKey('posts.id'),
primary_key=True)
node_id = db.Column(db.BigInteger, db.ForeignKey('nodes.id'),
primary_key=True)
I will get a list of nodes that have the topic (e.g. one, two). Then taking
the nodes, I'll select the PN that have both nodes (it can be 1 or more)
associated with it.
e.g.
[image: telegram-cloud-photo-size-5-6327616107510343927-x.jpg]
I'm doing it this way at this point:
nodes = NodesModel.find_by_topic_list(topic_list)
node_id_list = [node.id for node in nodes]
Then running this sql statement:
WITH pn_1 AS (SELECT post_id, ARRAY_AGG(DISTINCT node_id) AS nodes
FROM pn GROUP BY post_id),
nodes_1 AS (SELECT ARRAY_AGG(DISTINCT id) AS s_nodes
FROM pn WHERE node_id IN (dict(node_id_list))
SELECT DISTINCT post_id FROM pn_1
WHERE nodes_1 = (SELECT s_nodes FROM select_nodes);
There has to be a better and more elegant way to do this with SQLAlchemy
and I can't seem to figure it out. I've tried using subqueries and then I
realised that I'm actually calling the nodes table twice, which I shouldn't
have to.
Can anyone tell me how to use the nodes table to get the post_id form pn
(after which getting the post should be easy via ORM should be easy, I
think).
Thanks.
Desmond
--
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.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/CAM%2BCzahgN2TF88p4__5W76DiYO3THD1tV2BgekvX913Mv6nU7Q%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] How to get output from PostgreSQL's VERBOSE functions
Thanks with logging.basicConfig() it indeed works.
On Sun, 9 Jun 2019 at 16:19, Mike Bayer wrote:
>
>
> On Sun, Jun 9, 2019, at 1:33 AM, Zsolt Ero wrote:
>
> I figured out the keyword is NOTICES.
>
> However this section of the docs doesn't work / didn't had any effect:
> https://docs.sqlalchemy.org/en/13/dialects/postgresql.html#notice-logging
>
>
> that document assumes one is familiar with logging in general which it
> probably should not, so did you also set up logging config? If you call
> this ahead of time:
>
> logging.basicConfig()
>
> otherwise the feature is broken
>
>
>
>
>
>
> What worked was to manually clear and print
> conn.connection.connection.notices like this:
>
> if notices:
> conn.connection.connection.notices = list()
>
> conn.execute(stmt)
>
> if notices:
> print('--')
> print('\n'.join(conn.connection.connection.notices))
> print('--')
>
>
> --
> 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.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/CAKw-smCqxotj0tSoHjFjNJ4NVCtf3KYE1nakYAdsiXsuv4xmDw%40mail.gmail.com
>
> .
> 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 a topic in the
> Google Groups "sqlalchemy" group.
> To unsubscribe from this topic, visit
> https://groups.google.com/d/topic/sqlalchemy/89NEx4yA1ww/unsubscribe.
> To unsubscribe from this group and all its topics, 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.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/8a713933-a056-4f97-a8d2-f27983879ac5%40www.fastmail.com
>
> .
> 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.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/CAKw-smBjSAPxJ1aEVrT5ra_ySgzhYgrAfAh36wThab7s7gUyKw%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] How to get output from PostgreSQL's VERBOSE functions
On Sun, Jun 9, 2019, at 1:33 AM, Zsolt Ero wrote:
> I figured out the keyword is NOTICES.
>
> However this section of the docs doesn't work / didn't had any effect:
> https://docs.sqlalchemy.org/en/13/dialects/postgresql.html#notice-logging
that document assumes one is familiar with logging in general which it probably
should not, so did you also set up logging config? If you call this ahead of
time:
logging.basicConfig()
otherwise the feature is broken
>
> What worked was to manually clear and print
> conn.connection.connection.notices like this:
>
> if notices:
> conn.connection.connection.notices = list()
>
> conn.execute(stmt)
>
> if notices:
> print('--')
> print('\n'.join(conn.connection.connection.notices))
> print('--')
>
> --
> 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.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/CAKw-smCqxotj0tSoHjFjNJ4NVCtf3KYE1nakYAdsiXsuv4xmDw%40mail.gmail.com
>
> .
> 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.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/8a713933-a056-4f97-a8d2-f27983879ac5%40www.fastmail.com.
For more options, visit https://groups.google.com/d/optout.
