Re: [sqlalchemy] Selectinload doesn't exploit FK

I've tested the patch. Works perfectly with my usecase. Thank you very much ! Le vendredi 19 juillet 2019 20:22:40 UTC+3, Mike Bayer a écrit : > > > > On Fri, Jul 19, 2019, at 4:40 AM, yoch melka wrote: > > Thank you for your help. > > I'd be happy to submit a

Re: [sqlalchemy] Selectinload doesn't exploit FK

Thank you for your help. I'd be happy to submit a PR (I've already tried to fix this by myself), but the code is a little hard to master. Le vendredi 19 juillet 2019 01:24:03 UTC+3, Mike Bayer a écrit : > > > > On Thu, Jul 18, 2019, at 5:02 PM, yoch melka wrote: > &

[sqlalchemy] Selectinload doesn't exploit FK

Hi, According to the documentation , from 1.3 "selectin loading can omit the JOIN for a simple one-to-many collection" in case if the PK is known by the primary request. But in the opposite case that we know t

Re: [sqlalchemy] How to bind all class associated with an engine ?

Oh, it's perfect for me if I can use bases for bindings. Thank you Le mardi 6 novembre 2018 21:27:19 UTC+2, Mike Bayer a écrit : > > On Tue, Nov 6, 2018 at 2:20 PM > wrote: > > > > I have many classes, so it's seems a better idea to use something like > that (but I don't know what exactly is t

Re: [sqlalchemy] How to bind all class associated with an engine ?

I have many classes, so it's seems a better idea to use something like that (but I don't know what exactly is this registry, and why it's a WeakRef dict) : binding = {cls: engine for cls in Base._decl_class_registry.values()} If not, maybe I can use the second approach, overloading Session, but

[sqlalchemy] How to bind all class associated with an engine ?

Hi, I'm using a schema with multiple DB, like : engine1 = create_engine("mysql://localhost/{db}?charset=utf8".format(db=db1 )) engine2 = create_engine("mysql://localhost/{db}?charset=utf8".format(db=db2 )) Then I use automap and reflection to generate mapping of all classes needed. Sometime, I

Re: [sqlalchemy] How to use labels directly in compare expression

OK, thank you very much ! Le vendredi 22 juin 2018 20:10:53 UTC+3, Mike Bayer a écrit : > > Just use literal_column for now , that is the clearest statement of > intention , I'll try to find time to look at the code to see if there's > some flag I'm forgetting > > On Fri, Jun 22, 2018, 3:08 AM

Re: [sqlalchemy] How to use labels directly in compare expression

In standard SQL, I might use a subquery with filter: code = func.json_value(User.meta, '$.code').label('code') q = db.query(code).subquery() db.query(q).filter(q.c.code!=None).all() Le vendredi 22 juin 2018 10:08:04 UTC+3, yoch@gmail.com a écrit : > > Yes, I tried that. It produces the follo

Re: [sqlalchemy] How to use labels directly in compare expression

Yes, I tried that. It produces the following SQL: SELECT json_value(user.meta, :json_value_1) AS code FROM user HAVING json_value(user.meta, :json_value_1) IS NOT NULL This make sense in general, because in MySQL you cannot refer to an alias in the WHERE part. But I want to refer directly to the

[sqlalchemy] How to use labels directly in compare expression

Hi, I want to translate this MariaDB query to sqlalchemy : SELECT JSON_VALUE(user.meta, '$.code') AS code FROM user HAVING code IS NOT NULL; (Note: the use of HAVING is because I want to filter on the alias `code`.) I don't find a simple way to use the alias in comparison, currently I wrote th

Re: [sqlalchemy] Re: SQLAlchemy 1.2.0b1 released

OK, thank a lot ! Le jeudi 13 juillet 2017 06:01:45 UTC+3, Mike Bayer a écrit : > > this is how that would have to be mapped, hypothetically: > > class EngineerBase(Person): > __tablename__ = 'engineer' > > id = Column(ForeignKey('person.id'), primary_key=True) > engineer_name = Co

Re: [sqlalchemy] Re: SQLAlchemy 1.2.0b1 released

Here a MCWE : from sqlalchemy import Table, Column, Integer, String, ForeignKey, create_engine from sqlalchemy.orm import Session from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() class Person(Base): __tablename__ = 'person' id = Column(Integer, primar

Re: [sqlalchemy] Re: SQLAlchemy 1.2.0b1 released

I have a mixed configuration with both joined and single table subclasses in a two-levels inheritance (like that ), so selectin seems to be the right choice for me. Le jeudi 13 juillet 2017 01:09:50 UTC+3, Mike Bayer a écrit : > >

[sqlalchemy] Re: SQLAlchemy 1.2.0b1 released

I noticed that {'polymorphic_load': 'selectin'} on single table inheritance can make several SQL queries unnecessarily. Le mercredi 12 juillet 2017 22:02:04 UTC+3, yoch@gmail.com a écrit : > > Very awaited version for me (because the selectin) ! > > I tested in my code both the eagerload and

[sqlalchemy] Re: SQLAlchemy 1.2.0b1 released

Very awaited version for me (because the selectin) ! I tested in my code both the eagerload and the polymorphic usages, and everything works perfectly. Thank you Mike Le lundi 10 juillet 2017 16:44:03 UTC+3, Mike Bayer a écrit : > > SQLAlchemy release 1.2.0b1 is now available. > > This is the

Re: [sqlalchemy] Is this a valid inheritance configuration ?

Thank you Mike ! Le mardi 20 juin 2017 21:23:01 UTC+3, Mike Bayer a écrit : > > > > On 06/20/2017 02:08 PM, yoch@gmail.com wrote: > > Hi, > > > > I wish to have a two-levels inheritance, but I don't know how to > proceed. > > The docs says that "only one discriminator column or SQL expre

[sqlalchemy] Is this a valid inheritance configuration ?

Hi, I wish to have a two-levels inheritance, but I don't know how to proceed. The docs says that "only one discriminator column or SQL expression may be configured for the entire inheritance hierarchy". I tried with this example (mixing joined and single inheritances) : class Person(Base):

Re: [sqlalchemy] many-to-many relationship : how to update items properly if there are duplicates entries ?

Thank you Mike for this detailled response ! The UniqueObject recipe is very interesting, but not very suitable to my case because my webservice don't use the same session on each Post insertion. Maybe a session.merge_all() method can help to improve performance in such cases by grouping the u

[sqlalchemy] many-to-many relationship : how to update items properly if there are duplicates entries ?

Hi, I'm trying to create a tag system with a many-to-many relationship approach, and I have problems with the updating phase. class Post(Base): __tablename__ = 'post' id = Column(Integer, primary_key=True) _tags = relationship('Tag', secondary='post_tags') @property def tag

Re: [sqlalchemy] bulk update with inheritance : weird behavior

Thank you Mike for this clear explanation ! Le jeudi 4 mai 2017 16:41:37 UTC+3, Mike Bayer a écrit : > > > > > > In my real use case, we have to update both parent and child columns, so > > I want to use the Children class. > > so SQLite won't support that (nor will Postgresql or most other DBs

Re: [sqlalchemy] bulk update with inheritance : weird behavior

Le jeudi 4 mai 2017 16:07:22 UTC+3, Mike Bayer a écrit : > > > > On 05/04/2017 08:41 AM, yoch@gmail.com wrote: > > Hi, > > > > I'm facing to a strange behavior with bulk update on inherited class. > > > > Is this a bug ? > > it's not. > > > http://docs.sqlalchemy.org/en/latest/orm/quer

[sqlalchemy] bulk update with inheritance : weird behavior

Hi, I'm facing to a strange behavior with bulk update on inherited class. These two queries work differently : from sqlalchemy import Column, Integer, String, ForeignKey, create_engine from sqlalchemy.orm import relationship, Session from sqlalchemy.ext.declarative import declarative_base Base

Re: [sqlalchemy] automap with self-referential relationship

Thank tou, I solved the problem with this code : def name_for_collection_relationship(base, local_cls, referred_cls, constraint): disc = '_'.join(col.name for col in constraint.columns) return referred_cls.__name__.lower() + '_' + disc + "_collection" Le lundi 3 avril 2017 17:41:20 UTC

[sqlalchemy] automap with self-referential relationship

Hi, I want to use automap to generate mapping from existing database, but the relation mapping fails. I understand what happens, but I can't find a proper workaround to fix this error. Here a minimal code example with the minimal schema : CREATE TABLE `user` ( `id` INT UNSIGNED NOT NULL, PRI

Re: [sqlalchemy] using mysql user variables

OK, thank you. I understand that there is no way to do this kind of thing with the ORM. So I'm going to use litteral SQL in this case. Le mercredi 13 janvier 2016 22:58:26 UTC+2, Michael Bayer a écrit : > > it would depend upon if this can be done within a single > cursor.execute() or if it woul

[sqlalchemy] using mysql user variables

Hello, I want to compute difference between successive records. In MySQL, I had written something like : SET @prev := 0; SELECT time_to_sec(@prev), @prev := ts FROM mytable; How to achieve the same with sqlalchemy ? (I prefer to do it in SQL rather than python because I want to aggregate the r

Re: [sqlalchemy] automap : problem with two relationships on same foreign key

For now, I don't have a sufficiently comprehensive view of the automap process and its effects to make a pull-request. Maybe one day I'll take time to learn more about that, and to understand the corresponding tests. Thank you Le lundi 4 janvier 2016 05:19:17 UTC+2, Michael Bayer a écrit : > >

Re: [sqlalchemy] automap : problem with two relationships on same foreign key

Okay, I have one test failed for automap : ~/sqlalchemy $ ./sqla_nose.py test.ext.test_automap .E.. == ERROR: test.ext.test_automap.AutomapTest.test_relationship_explicit_override_m2o

Re: [sqlalchemy] automap : problem with two relationships on same foreign key

I've added theses lines here : if relationship_name in map_config.properties: ms

Re: [sqlalchemy] automap : problem with two relationships on same foreign key

OK, thanks you. I think it's a good idea to issue a warning in such cases. Best regards Le samedi 2 janvier 2016 19:18:12 UTC+2, Michael Bayer a écrit : > > > > On 01/02/2016 11:38 AM, yoch@gmail.com wrote: > > Thank you. > > > > I hesitate between using this way, or explicitly specify t

Re: [sqlalchemy] automap : problem with two relationships on same foreign key

Thank you. I hesitate between using this way, or explicitly specify the relationship (is this a good idea? In my test I found 3 relations after prepare()) : class Thermostat(Base): __tablename__ = 'thermostats' idbuiltin = Column(Integer, ForeignKey('device.id')) idthermometer = Colum

[sqlalchemy] automap : problem with two relationships on same foreign key

Hi all, I use automap with database reflection to import schema with sqlalchemy. In case I have two relationships on same foreign key in some table, only one relationship is created by prepare(), the second one seems overwrited. My table looks like : Table('thermostat', Base.metadata,

Re: [sqlalchemy] Re: how to set assocation proxy pattern with automap

Thank you very much, it work fine now. Le lundi 31 août 2015 23:03:17 UTC+3, Michael Bayer a écrit : > > > > On 8/31/15 3:39 PM, yoch@gmail.com wrote: > > Great thanks ! > > > There is something I still doesn't understand with automap_base : some > relationships are created only after the f

Re: [sqlalchemy] Re: how to set assocation proxy pattern with automap

Great thanks ! There is something I still doesn't understand with automap_base : some relationships are created only after the first query call (which takes much more time to terminate). For instance : >>> from dbaccess import *# import engine, models, etc. >>> Dispositif.hard

Re: [sqlalchemy] Re: how to set assocation proxy pattern with automap

Thank you very much. Le lundi 31 août 2015 06:17:37 UTC+3, Michael Bayer a écrit : > > > > On 8/29/15 2:27 PM, yoch@gmail.com wrote: > > Thanks for the reply. > > Le vendredi 28 août 2015 18:52:37 UTC+3, Michael Bayer a écrit : > > > > On 8/28/15 3:51 AM, yoch@gmail.com wrote: > > Anoth

Re: [sqlalchemy] Re: how to set assocation proxy pattern with automap

Thanks for the reply. Le vendredi 28 août 2015 18:52:37 UTC+3, Michael Bayer a écrit : > > > > On 8/28/15 3:51 AM, yoch@gmail.com wrote: > > Another question is why sqlalchemy produce two queries to get hardwares > collections from a dispositif : > > > >>> some_disp.hardwares > 2015-08-28 10

[sqlalchemy] Re: how to set assocation proxy pattern with automap

I now have a code that seems to work, but I'm not sure I did it right : Base = automap_base() class Dispositif(Base): __tablename__ = 'dispositifs' hardwares = association_proxy('disp_hdw_collection', 'hardware_ref') class Hardware(Base): __tablename__ = 'hardware' Base.prepare(engi

[sqlalchemy] how to set assocation proxy pattern with automap

Hello, I want to use assocation proxy pattern with automap. I tried this code : Base = automap_base() class Dispositif(Base): __tablename__ = 'dispositifs' hardwares = association_proxy('disp_hardwares', 'hard

Re: [sqlalchemy] using inheritance with automap_base

Wow, thank you. I'm going to try. Le lundi 10 août 2015 18:01:37 UTC+3, Michael Bayer a écrit : > > > > On 8/10/15 10:55 AM, yoch@gmail.com wrote: > > Hello, > > I use reflection with automap_base to load my database schema. > > # my code currently looks > Base = automap_base() > Base.prepar

[sqlalchemy] using inheritance with automap_base

Hello, I use reflection with automap_base to load my database schema. # my code currently looks Base = automap_base() Base.prepare(engine, reflect=True) And for now, I want to configure the Base to take in account some inheritance relationships. In database, inheritance are modeled by : -- ba