Re: [sqlalchemy] contains_eager option on polymorphic joined query
Ah my bad! I really didn't do good testing. Your reply prompted me to find
the root of the problem and solution though. Posting it here just in case
this is useful to anyone else!
A gist of the problem I actually had was that I has a column_property in
Common defined in terms of the Superclass, but really needed to be defined
on the polymorphic alias of the superclass instead. In full:
from sqlalchemy import Column
from sqlalchemy import create_engine
from sqlalchemy import ForeignKey
from sqlalchemy import Integer
from sqlalchemy import String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import contains_eager
from sqlalchemy.orm import relationship
from sqlalchemy.orm import Session
from sqlalchemy.orm import with_polymorphic
from sqlalchemy.orm import column_property
from sqlalchemy import select, func, and_, event
Base = declarative_base()
class Superclass(Base):
__tablename__ = "s1"
id = Column(Integer, primary_key=True)
common_id = Column(ForeignKey("c.id"))
common_relationship = relationship("Common", uselist=False,
innerjoin=True, lazy='noload')
discriminator_field = Column(String)
__mapper_args__ = {
"polymorphic_identity": "superclass",
"polymorphic_on": discriminator_field,
}
class Subclass(Superclass):
__tablename__ = "s2"
id = Column(ForeignKey("s1.id"), primary_key=True)
__mapper_args__ = {"polymorphic_identity": "subclass"}
class Common(Base):
__tablename__ = "c"
id = Column(Integer, primary_key=True)
num_superclass = column_property(
select([func.count(Superclass.id)]).where(Superclass.common_id ==
id).correlate_except(Superclass)
)
e = create_engine("postgresql://jaychia:tiger@localhost/test", echo=True)
Base.metadata.drop_all(e)
Base.metadata.create_all(e)
s = Session(e)
s.add(Subclass(common_relationship=Common()))
s.commit()
poly = with_polymorphic(Superclass, "*")
s.query(poly).options(contains_eager(poly.common_relationship)).join(
poly.common_relationship
).filter(Common.id == 1).all()
The code above throws an error on the num_superclass column. Fixing the
num_superclass column to instead use the polymorphic version of Superclass
fixed it ! So the definition of Common should look like this instead:
class Common(Base):
__tablename__ = "c"
id = Column(Integer, primary_key=True)
poly = with_polymorphic(Superclass, "*")
Common.num_superclass = column_property(
select([func.count(poly.id)]).where(poly.common_id ==
Common.id).correlate_except(poly)
)
Am I doing this right? Seems like this is the best possible fix.
On Friday, March 8, 2019 at 8:53:35 PM UTC-8, Mike Bayer wrote:
>
> On Fri, Mar 8, 2019 at 3:56 PM >
> wrote:
> >
> > Hi everyone!
> >
> > I am trying to use the contains_eager option on a simple polymorphic
> query that looks like:
> >
> > class Superclass():
> > common_relationship = relationship('Common', ...)
> > discriminator_field = Column(String...)
> > __mapper_args__ = {'polymorphic_identity': 'superclass',
> 'polymorphic_on': discriminator_field}
> >
> > class Subclass(Superclass):
> > fields...
> >
> > poly = with_polymorphic(Superclass, '*')
> >
> db.session.query(poly).options(contains_eager(poly.common_relationship)).join(poly.common_relationship).filter(Common.id
>
> == 1).all()
> >
> > This code throws an error when I execute the query:
> >
> > sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) subquery
> uses ungrouped column "common.id" from outer query
>
> this example is very vague and does not indicate what you are doing in
> full. Below is your example turned into a working mapping including
> your exact query using Postgresql, works fine, and notably does not
> generate a subquery which would only occur if there was more going on
> above. Please provide a complete MCVE, most likely by modifying the
> fully runnable code below, thanks!
>
> from sqlalchemy import Column
> from sqlalchemy import create_engine
> from sqlalchemy import ForeignKey
> from sqlalchemy import Integer
> from sqlalchemy import String
> from sqlalchemy.ext.declarative import declarative_base
> from sqlalchemy.orm import contains_eager
> from sqlalchemy.orm import relationship
> from sqlalchemy.orm import Session
> from sqlalchemy.orm import with_polymorphic
>
> Base = declarative_base()
>
>
> class Superclass(Base):
> __tablename__ = "s1"
>
> id = Column(Integer, primary_key=True)
> common_id = Column(ForeignKey("c.id"))
>
> common_relationship = relationship("Common")
> discriminator_field = Column(String)
> __mapper_args__ = {
> "polymorphic_identity": "superclass",
> "polymorphic_on": discriminator_field,
> }
>
>
> class Subclass(Superclass):
> __tablename__ = "s2"
> id = Column(ForeignKey("s1.id"), primary_key=True)
> __mapper_args__ =
Re: [sqlalchemy] Security Implications of Directly Executing Queries Compiled to Strings
> On Mar 8, 2019, at 9:06 PM, Mike Bayer wrote: > > Datatypes like Integer() when rendering the literal value pass the > incoming input directly to str() which goes straight to the database... Thank you so much for the explanation! This is very helpful. -- 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] MySQL Connector
Note the mysql-connector people actually contacted us this week on the devel list, and while mysql-connector-python does still have a lot of major issues they are not as bad as the ones I listed on the site, as apparently there is a totally different fork of it called mysql-connector also on pypi that is ancient so most of the problems were there.Current 8.x release still has binary data issues and possibly some lockup issues though. On Tue, Mar 5, 2019 at 7:08 PM Warwick Prince wrote: > > Hi Mike > > Thanks very much. It’s a shame they broke it so badly! I’ll change to your > recommendation immediately. > > Cheers > Warwick > > Warwick A. Prince > Mushroom Systems International Pty. Ltd. > > > On 5 Mar 2019, at 3:07 am, Mike Bayer wrote: > > > >> On Sun, Mar 3, 2019 at 8:40 PM Warwick Prince > >> wrote: > >> > >> Hi Community > >> > >> We’ve been using sqlalchemy with great success for many years. We’ve been > >> using the mysqlconnector to connect to my MySQL databases with no issues > >> to speak of. > >> > >> My issue is that I recently upgraded to the latest version of the > >> connector from Oracle which has changed from Python to C. I’m now > >> regularly receiving the following error where this has never happened in > >> the past; MySQL 5.6 BTW. > >> > >> DatabaseError: (mysql.connector.errors.HashError) Hashed authentication > >> data is invalid > >> > >> Obviously, I would assume that this is not a sqlalchemy issue, however > >> there’s nothing really being spoken about this anywhere else and I > >> wondered if anyone had had the problem and fixed it - or eventually rolled > >> back to the very old version of mysqlconnector? > > > > Unfortunately recent versions of MySQL connector, specifically when > > the C code was added, work very poorly, such that I had to remove > > mysql connector from testing. I documented a partial list of > > blocking issues I encountered here: > > https://docs.sqlalchemy.org/en/latest/dialects/mysql.html#current-issues > > > > > >> > >> Alternatively, @Mike - could you recommend in your opinion the ‘best’ > >> connector to use for MySQL based on reliability and performance. > > > > The best drivers for MySQL are mysqlclient and pymysql, both > > maintained by the same person. Both drivers work very well and are > > well supported. mysqlclient is a fork of the original C based > > python-mysql driver which is no longer maintained. > > > > > >> > >> Cheers > >> Warwick > >> > >> Warwick Prince > >> Managing Director > >> mobile: +61 411 026 992 > >> skype: warwickprince > >> phone: +61 7 3102 3730 > >> fax: +61 7 3319 6734 > >> web: www.mushroomsys.com > >> > >> -- > >> 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 - > 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
[sqlalchemy] Referencing the value of a table linked by a foreign Key
Hello every one,
I am new on Python/SqlAlchemy and I try to develop an app but I can't find
how to display the name of people and not their foreignkey when I reference
them, could you help me ?
Here are my classes :
from application.main import db
class Lettre(db.Model):
__tablename__ = "lettre"
lettre_id = db.Column(db.Integer, unique=True, nullable=False,
primary_key=True)
titre = db.Column(db.Text, nullable=False)
contenu = db.Column(db.Text, nullable=False)
date_label = db.Column(db.Text, nullable=False)
date_norm = db.Column(db.Text, nullable=False)
lettre_expediteur = db.Column(db.Text, db.ForeignKey('correspondant.nom'),
autoincrement='ignore_fk')
lettre_destinataire=db.Column(db.Text, db.ForeignKey('correspondant.nom'),
autoincrement='ignore_fk')
depuis_lieu=db.Column(db.Text, db.ForeignKey('lieu.lieu_id'))
vers_lieu=db.Column(db.Text, db.ForeignKey('lieu.lieu_id'))
# Nous avons ici créé une première classe (table) pour notre base de
données.
class Correspondant(db.Model):
__tablename__ = "correspondant"
id_correspondant = db.Column(db.Integer, unique=True, nullable=False,
primary_key=True)
nom = db.Column(db.Text, nullable=False)
prenom = db.Column(db.Text, nullable=False)
class Lieu(db.Model):
__tablename__ = "lieu"
lieu_id=db.Column(db.Integer, unique=True, nullable=False, primary_key=True)
label=db.Column(db.Integer, unique=True, nullable=False)
And here is what I wrote in my HTML pages :
{{correspondance.titre}}
Expéditeur {{correspondance.lettre_expediteur}}
Date {{correspondance.date_label}}
Destinataire
{{correspondance.lettre_destinataire}}
Contenu {{correspondance.contenu}}
But I always get the foreign key and not the name (nom) of the
correspondant.
Could you help me please ?
--
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.
