Re: [sqlalchemy] explain analyze on statement
On Mon, Jan 21, 2019 at 12:23 PM Zsolt Ero wrote:
>
> 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.
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
>
> --
> 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] explain analyze on statement
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
--
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] Eager load extra columns in many to many relationships
On Mon, Jan 21, 2019 at 10:17 AM Harshvardhan Gupta
wrote:
>
> Hi, thanks for the reply.
>
> From what I understand , your example shows me filtering through a many to
> many relationship. However I was looking for explicitly also loading the
> extra field in the pivot table.
> In your example, will CorporateApplications.foo also be loaded in the result ?
There's an easy answer which is that you need to use the association
object pattern:
https://docs.sqlalchemy.org/en/latest/orm/basic_relationships.html?highlight=association%20object#association-object
otherwise, if you were to load Corporate ->
Corporate.corporate_applicants -> User, now you iterate:
for corp in results:
for user in corp.corporate_applicants:
print("User name %s application type %s" % (
user.name, ? ? ? what goes here? User
doesn't have a "type" field, so you can't say User.type
) )
It wouldn't make sense for the "type" attribute and all the other
attributes of CorporateApplication to be shoved onto each User object.
The same User object can be associated with many
CorporateApplication rows per this schema design and there is only one
User object per identity per Session.
using normal association object pattern you instead get a proper
namespace for your different entities:
for corp in results:
for applicant in corp.corporate_applicants:
print("User name %s application type %s" % (
applicant.user.name, applicant.type
) )
>
>
>
> On Monday, 21 January 2019 20:38:52 UTC+5:30, Mike Bayer wrote:
>>
>> On Sun, Jan 20, 2019 at 7:50 AM Harshvardhan Gupta
>> wrote:
>> >
>> > I have two tables, Corporates and Users , each corporate can be reviewing
>> > multiple users, and each user can apply to multiple corporates.
>> >
>> > My models are defined as:
>> >
>> > class Corporate(Dictifiable, db.Model):
>> >
>> >
>> >
>> > __tablename__ = 'corporate'
>> >
>> >
>> >
>> >
>> > id = Column(Integer, Sequence('corporate_id_seq'), primary_key=True)
>> >
>> > name = Column(String(1024), nullable=False)
>> >
>> >
>> >
>> >
>> > admins = db.relationship('User', secondary='corporate_admin')
>> >
>> > tests = db.relationship('Test', secondary='corporate_test')
>> >
>> > applicants = db.relationship('User', secondary='corporate_applicants')
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> > class User(Dictifiable, db.Model, UserMixin):
>> >
>> >
>> > __tablename__ = 'user'
>> >
>> >
>> >
>> >
>> > applicant_for = db.relationship('Corporate',
>> >
>> > secondary='corporate_applicants')
>> >
>> >
>> > My many to many table is defined as:
>> >
>> > class CorporateApplicants(Dictifiable, db.Model):
>> >
>> >
>> >
>> > __tablename__ = 'corporate_applicants'
>> >
>> >
>> >
>> >
>> > user_id = Column(Integer, ForeignKey('user.id'), primary_key=True)
>> >
>> > corporate_id = Column(Integer, ForeignKey('corporate.id'),
>> >
>> > primary_key=True)
>> >
>> >
>> >
>> >
>> > # i want to access this
>> >
>> > type = Column(db.Enum(ApplicationStatusTypes), nullable=True)
>> >
>> >
>> > My queries are usually done like this:
>> >
>> > Corporate.query .join(Corporate.applicants)# SQLAlchemy magic will make it
>> > join through the many to many
>> > .options(contains_eager(Corporate.applications)) .all()
>> >
>> > But this way I am unable to access the type field (for each user,
>> > corporate combination it will be either "shortlist","hired","reject".
>> >
>> > It is not possible to run a loop and then fetch each value separately due
>> > to the high number of applicants.
>> >
>> > How can I access this extra column while querying a many to many
>> > relationship in an eager manner.
>>
>> there's still magic that will help you but as always, explicit
>> approach will get you there, you need to name the entity you want to
>> query:
>>
>> sess.query(Corporate).join(CorporateApplicants).join(User).filter(CorporateApplications.foo
>> == 'bar')
>>
>> if those joins fail that there is too much ambiguity, or just as a
>> better practice in general, build some extra relationships so you can
>> use them:
>>
>> sess.query(Corporate).join(Corporate.applicant_associations).join(CorporateApplicant.user).filter(CorporateApplications.foo
>> == 'bar')
>>
>>
>>
>>
>> >
>> > --
>> > 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
Re: [sqlalchemy] Eager load extra columns in many to many relationships
Hi, thanks for the reply.
>From what I understand , your example shows me filtering through a many to
many relationship. However I was looking for explicitly also loading the
extra field in the pivot table.
In your example, will CorporateApplications.foo also be loaded in the
result ?
On Monday, 21 January 2019 20:38:52 UTC+5:30, Mike Bayer wrote:
>
> On Sun, Jan 20, 2019 at 7:50 AM Harshvardhan Gupta
> > wrote:
> >
> > I have two tables, Corporates and Users , each corporate can be
> reviewing multiple users, and each user can apply to multiple corporates.
> >
> > My models are defined as:
> >
> > class Corporate(Dictifiable, db.Model):
> >
> >
> >
> > __tablename__ = 'corporate'
> >
> >
> >
> >
> > id = Column(Integer, Sequence('corporate_id_seq'), primary_key=True)
> >
> > name = Column(String(1024), nullable=False)
> >
> >
> >
> >
> > admins = db.relationship('User', secondary='corporate_admin')
> >
> > tests = db.relationship('Test', secondary='corporate_test')
> >
> > applicants = db.relationship('User',
> secondary='corporate_applicants')
> >
> >
> >
> >
> >
> >
> >
> > class User(Dictifiable, db.Model, UserMixin):
> >
> >
> > __tablename__ = 'user'
> >
> >
> >
> >
> > applicant_for = db.relationship('Corporate',
> >
> > secondary='corporate_applicants')
> >
> >
> > My many to many table is defined as:
> >
> > class CorporateApplicants(Dictifiable, db.Model):
> >
> >
> >
> > __tablename__ = 'corporate_applicants'
> >
> >
> >
> >
> > user_id = Column(Integer, ForeignKey('user.id'), primary_key=True)
> >
> > corporate_id = Column(Integer, ForeignKey('corporate.id'),
> >
> > primary_key=True)
> >
> >
> >
> >
> > # i want to access this
> >
> > type = Column(db.Enum(ApplicationStatusTypes), nullable=True)
> >
> >
> > My queries are usually done like this:
> >
> > Corporate.query .join(Corporate.applicants)# SQLAlchemy magic will make
> it join through the many to many
> .options(contains_eager(Corporate.applications)) .all()
> >
> > But this way I am unable to access the type field (for each user,
> corporate combination it will be either "shortlist","hired","reject".
> >
> > It is not possible to run a loop and then fetch each value separately
> due to the high number of applicants.
> >
> > How can I access this extra column while querying a many to many
> relationship in an eager manner.
>
> there's still magic that will help you but as always, explicit
> approach will get you there, you need to name the entity you want to
> query:
>
> sess.query(Corporate).join(CorporateApplicants).join(User).filter(CorporateApplications.foo
>
>
> == 'bar')
>
> if those joins fail that there is too much ambiguity, or just as a
> better practice in general, build some extra relationships so you can
> use them:
>
> sess.query(Corporate).join(Corporate.applicant_associations).join(CorporateApplicant.user).filter(CorporateApplications.foo
>
>
> == 'bar')
>
>
>
>
> >
> > --
> > 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] Eager load extra columns in many to many relationships
On Sun, Jan 20, 2019 at 7:50 AM Harshvardhan Gupta
wrote:
>
> I have two tables, Corporates and Users , each corporate can be reviewing
> multiple users, and each user can apply to multiple corporates.
>
> My models are defined as:
>
> class Corporate(Dictifiable, db.Model):
>
>
>
> __tablename__ = 'corporate'
>
>
>
>
> id = Column(Integer, Sequence('corporate_id_seq'), primary_key=True)
>
> name = Column(String(1024), nullable=False)
>
>
>
>
> admins = db.relationship('User', secondary='corporate_admin')
>
> tests = db.relationship('Test', secondary='corporate_test')
>
> applicants = db.relationship('User', secondary='corporate_applicants')
>
>
>
>
>
>
>
> class User(Dictifiable, db.Model, UserMixin):
>
>
> __tablename__ = 'user'
>
>
>
>
> applicant_for = db.relationship('Corporate',
>
> secondary='corporate_applicants')
>
>
> My many to many table is defined as:
>
> class CorporateApplicants(Dictifiable, db.Model):
>
>
>
> __tablename__ = 'corporate_applicants'
>
>
>
>
> user_id = Column(Integer, ForeignKey('user.id'), primary_key=True)
>
> corporate_id = Column(Integer, ForeignKey('corporate.id'),
>
> primary_key=True)
>
>
>
>
> # i want to access this
>
> type = Column(db.Enum(ApplicationStatusTypes), nullable=True)
>
>
> My queries are usually done like this:
>
> Corporate.query .join(Corporate.applicants)# SQLAlchemy magic will make it
> join through the many to many
> .options(contains_eager(Corporate.applications)) .all()
>
> But this way I am unable to access the type field (for each user, corporate
> combination it will be either "shortlist","hired","reject".
>
> It is not possible to run a loop and then fetch each value separately due to
> the high number of applicants.
>
> How can I access this extra column while querying a many to many relationship
> in an eager manner.
there's still magic that will help you but as always, explicit
approach will get you there, you need to name the entity you want to
query:
sess.query(Corporate).join(CorporateApplicants).join(User).filter(CorporateApplications.foo
== 'bar')
if those joins fail that there is too much ambiguity, or just as a
better practice in general, build some extra relationships so you can
use them:
sess.query(Corporate).join(Corporate.applicant_associations).join(CorporateApplicant.user).filter(CorporateApplications.foo
== 'bar')
>
> --
> 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.
