Re: [sqlalchemy] Left join and nested inner join

[Michael Ekoka]

Thanks, it worked.

On Monday, July 3, 2023 at 8:49:58 PM UTC+7 Mike Bayer wrote:

> use the join construct directly
>
> from sqlalchemy.orm import join
>
>
> stmt = select(A).outerjoin(join(B, C), A.id == B.a_id)
>
>
>
> On Mon, Jul 3, 2023, at 8:29 AM, Michael Ekoka wrote:
>
>
> Hi, I'm looking for the SQLAlchemy equivalent to the query
>
> SELECT *
> FROM a 
> LEFT OUTER JOIN (b INNER JOIN c ON b.id = c.b_id) 
> ON a.id = b.a_id
>
> Related:
> https://stackoverflow.com/a/56815807/56974
> https://stackoverflow.com/questions/25514160/nested-joins-in-sqlalchemy
>
> Table "b" and "c" are joined and filtered first, then the outer join is 
> applied. I was able to achieve the same results using a subquery, whose 
> fields I was subsequently able to load using `contains_eager`. FYI
>
> subq = session.query(B).join(C).subquery(with_labels=True)
> q = (session.query(A)
>  .outerjoin(subq, A.id==subq.c.b_a_id)
>  .options(contains_eager(A.b, alias=subq)
>   .options(contains_eager(B.c, alias=subq
> r = q.all()
>
> I'm curious whether there's an equivalent using the above nested join 
> syntax.
>
> Thanks.
>
>
>
> -- 
> 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 view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/86b6cc02-be68-400f-9a13-ef486b560329n%40googlegroups.com
>  
> <https://groups.google.com/d/msgid/sqlalchemy/86b6cc02-be68-400f-9a13-ef486b560329n%40googlegroups.com?utm_medium=email&utm_source=footer>
> .
>
>
>

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/88a837ce-3013-4f33-bdee-fd90d93d9ca6n%40googlegroups.com.

Re: [sqlalchemy] Should I use a surrogate primary key on an Association Object pattern?

['Michael Mulqueen' via sqlalchemy]

Hi Pierre,

This isn't an official answer, I'm just a long time user of SQLAlchemy.

Either way should work fine. The association object is driven by the
columns on the association table being FKs, whether or not they're part of
a PK isn't relevant.

I've used both ways. In my experience, an artificial PK is easier to
maintain in the long run. Each way has its minor advantages and
disadvantages, but generally a single artificial PK would be my preference.

Mike

On Mon, 3 Jul 2023, 16:43 Pierre Massé,  wrote:

> Dear all,
>
> I am currently reworking a bit of my model and stumbled into this
> question, which I think mainly has opinionated answers - but I would like
> to have some insight regarding SQLAlchemy usage or preferences.
>
> I have a situation where I am in the exact same case like the one
> described in the Association Object
> 
>  in
> the SQLAlchemy ORM docs.
>
> I want to modelize :
> - Caregiver - a person taking care of one or more Helpee
> - Helpee - a person who is being taken care of, by one or more Caregiver
> - their Relationship, which links a Caregiver to a Helpee, but with
> additional data like their family ties (spouse, parent, friend, ...)
>
> This is typically the Association object use case, a many to many
> relationship, holding additional data.
>
> So far, I have been using a "natural" primary key on the Relationship
> table, by using the Caregiver Id, and the Helpee Id to form a composite
> primary key.
>
> From a handful of blog posts (this StackOverflow answer
>  being quite in depth), it looks
> like adding an "artificial" or surrogate primary key on the Relationship
> table should be the way to go. Of course, I would keep a unique constraint
> on (Caregiver Id x Helpee Id) on this table along the new primary key.
>
> My questions are :
> - is the addition of a surrogate primary key a good idea - without taking
> into account the fact that I am using SQLAlchemy?
> - would the "magic" of the association object still operate even though
> the mapped ORM relationships would not be part of the primary key anymore?
>
> The docs example would become:
>
>
> from typing import Optional
>
> from sqlalchemy import ForeignKey
> from sqlalchemy import Integer
> from sqlalchemy.orm import Mapped
> from sqlalchemy.orm import mapped_column
> from sqlalchemy.orm import DeclarativeBase
> from sqlalchemy.orm import relationship
>
>
> class Base(DeclarativeBase):
> pass
>
>
> class Association(Base):
> __tablename__ = "association_table"
> *id: Mapped[int] = mapped_column(primary_key=True)*
> left_id: Mapped[int] = mapped_column(ForeignKey("left_table.id")*,
> primary_key=True*)
> right_id: Mapped[int] = mapped_column(
> ForeignKey("right_table.id")*, primary_key=True*
> )
> extra_data: Mapped[Optional[str]]
> child: Mapped["Child"] = relationship(back_populates="parents")
> parent: Mapped["Parent"] = relationship(back_populates="children")
> *__table_args__ = (UniqueConstraint('left_id', 'right_id',
> name='_relationship_uc'),)*
>
>
> class Parent(Base):
> __tablename__ = "left_table"
> id: Mapped[int] = mapped_column(primary_key=True)
> children: Mapped[List["Association"]] =
> relationship(back_populates="parent")
>
>
> class Child(Base):
> __tablename__ = "right_table"
> id: Mapped[int] = mapped_column(primary_key=True)
> parents: Mapped[List["Association"]] =
> relationship(back_populates="child")
>
>
> --
> 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 view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/CAH4TWVvPVKtjtyVHJO9WtX2ZCjmdX3aGWDBGCQrvU_7c-CswEg%40mail.gmail.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 view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/CAHxMHYUTkEbh5MHRZKW3H_qhXkk03HKr8pAv1Tp0

[sqlalchemy] Left join and nested inner join

[Michael Ekoka]

Hi, I'm looking for the SQLAlchemy equivalent to the query

SELECT *
FROM a 
LEFT OUTER JOIN (b INNER JOIN c ON b.id = c.b_id) 
ON a.id = b.a_id

Related: 
https://stackoverflow.com/a/56815807/56974
https://stackoverflow.com/questions/25514160/nested-joins-in-sqlalchemy 

Table "b" and "c" are joined and filtered first, then the outer join is 
applied. I was able to achieve the same results using a subquery, whose 
fields I was subsequently able to load using `contains_eager`. FYI

subq = session.query(B).join(C).subquery(with_labels=True)
q = (session.query(A)
 .outerjoin(subq, A.id==subq.c.b_a_id)
 .options(contains_eager(A.b, alias=subq)
  .options(contains_eager(B.c, alias=subq
r = q.all()

I'm curious whether there's an equivalent using the above nested join 
syntax.

Thanks.


-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/86b6cc02-be68-400f-9a13-ef486b560329n%40googlegroups.com.

[sqlalchemy] Re: this is a test message

[Michael Bayer]

this is...another test!  imagine that

On Wednesday, January 4, 2023 at 7:27:10 PM UTC-5 Mike Bayer wrote:

> test
>

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/2fbb074e-c606-4924-a3fb-d566381ad131n%40googlegroups.com.

[sqlalchemy] Re: after_update event is fired when I save a many-to-many relationship

[Michael Bayer]

the net-change to "user1.teams" is likely sending it through the 
"after_update" event but no net SQL is being emitted if no actual columns 
have changed on user1.

you would need to adjust the logic in your application to more specifically 
check for those conditions in which you want to act upon an event.  for 
example, if you want the event only to occur when specific attributes were 
affected, you could try using the history interface to see if those 
attributes have net changes: 
https://docs.sqlalchemy.org/en/14/orm/session_api.html#sqlalchemy.orm.attributes.History
  
or you can use a method like session.is_modified(): 
https://docs.sqlalchemy.org/en/14/orm/session_api.html#sqlalchemy.orm.Session.is_modified

On Friday, December 23, 2022 at 12:23:04 PM UTC-5 bboym...@gmail.com wrote:

> I'm trying to update an audit log table called 'loggings', here is the 
> definition of the corresponding model:
>
> class *Logging*(Base):
> __tablename__ = 'loggings' 
>
>  id = Column(GUID(), primary_key=True, nullable=False, default=uuid4)
>  target_id = Column(GUID(), doc="The ID of the altered object")
>  version = Column('version', Integer, default=0) 
>  created_at = Column(DateTime, default=datetime.now)
>  modified_at = Column(DateTime, default=datetime.now, 
> onupdate=datetime.now)
>
> and two other models and an association table:
>
> membership_table = Table('membership', Base.metadata,
> Column('user_id', GUID(), ForeignKey('user.id')),
> Column('team_id', GUID(), ForeignKey('team.id')),
> PrimaryKeyConstraint('user_id', 'team_id'))
>
> class *User*(ActivityMixin, Base): # Product
> __tablename__ = "user"
>
> id = Column(GUID(), primary_key=True, nullable=False, default=uuid4)
> name = Column(String)
> password = Column(String)
>
> def __repr__(self): # optional
> return f"User {self.name}"
>
> class *Team*(Base):
>__tablename__ = 'team'
>id = Column(GUID(), primary_key=True, nullable=False, default=uuid4)
>name = Column(String(64))
>users = relationship(lambda:User, secondary=membership_table, 
> backref='teams')
>
> I have an event listener/handler attached to the User class and another to 
> the Team class:
> @event.listens_for(User, 'after_update') 
> def create_logs_for_user(mapper, connection, target):
># logic to add a creation related  record to the loggings table
>
> @event.listens_for(User, 'after_update') 
> def update_logs_for_user(mapper, connection, target):
># logic to add user-updates  records to the loggings table
>
> @event.listens_for(Team, 'after_update') 
> def update_logs_for_team(mapper, connection, target):
># logic to add team-updates records to the loggings table
>
> These are the operations that I perform:
>
> [1]: from app import Logging, User, Team, session
> [2]: user1 = User(name='qwerh') 
> [3]: session.add(user1)
> [4]: session.commit()  # after_insert gets fired here
> [5]: team1 = Team(name="team1")
> [6]: session.add(team1)
> [7]: session.commit() # After this point, the users, team, and loggings 
> tables have one record each, as expected.
> [8]: user1.teams.append(team1)   
> [10]: session.commit() # Two update listeners are getting fired here! 
> After the append.
>
> *Question:*
> When I perform the operation on step [8] above, the loggings table gets 
> filled in twice, due to the "after_update" event getting fired by both the 
> User and the Team models.
> How do I prevent this from happening, I only want to capture the one event 
> from the Team model, sorry I'm very new to SQLAlchemy, thank you!
>  
>

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/d2a54ca3-24fa-4cb4-bf9c-9e4d54245fa9n%40googlegroups.com.

[sqlalchemy] Re: How to generate view columns in mapped class with async postgresql

[Michael Bayer]

the async aspect here is not that important, for Declarative + reflection 
you likely should look at DeferredReflection: 
https://docs.sqlalchemy.org/en/14/orm/declarative_tables.html#using-deferredreflection

for async, where it has in the example:

Reflected.prepare(engine)

you would instead say:

await async_connection.run_sync(Reflected.prepare)



On Wednesday, December 14, 2022 at 8:43:45 AM UTC-5 dcs3spp wrote:

> Hi,
>
> I am using alembic to apply a migration for a postgresql view using an *async 
> *engine.
> I can see this successfully applied in the database.
>
> I have the following declarative mapped class to  the view, defined as:
>
>
>
>
>
> *class MailingListView(Base): """View for mailing labels.After 
> metata reflection from db -> model expecting columns tobe available on 
> this class. """*
> *__tablename__ = "mailing_list_view"*
>
>
>
> *# Specify the column override from the underlying view that is the 
> primary keyid = Column(UUID(as_uuid=True), primary_key=True)*
>
>
>
>
>
>
>
>
>
> *# Expecting  these columns  below to be mapped in this class after
> # metadata reflection. Currently have to uncomment these# to manually 
> synchronise with view!## addressee = Column(String)# street = 
> Column(String)# town = Column(String)# county = Column(String)# 
> postcode = Column(String)# cursor = Column(String)*
>
> I am reflecting the views using the following:
>
>  
>
>
>
>
>
>
> * def use_inspector(conn):inspector = inspect(conn)
> return inspector.get_view_names()views = await 
> connection.run_sync(use_inspector)# I can see the table columns in 
> __table__.c.keys()# after the reflection below has run*
> *await connection.run_sync(*
> *target_metadata.reflect,*
> *only=views,*
> *views=True,*
> *extend_existing=True,*
> *)*
>
> After performing the above reflection I can see that my mapped model has 
> the underlyingtable columns updated with those defined in the underlying 
> view.
>
>
>
> *obj = MailingListView()obj.__table__.c.keys()*
> However, the properties of my mapped class are not updated after 
> reflection, raising an exception:
>
>
>
> *obj = MailingListView()obj.town = "town" # this raises an exception with 
> unavailable property*
> How is it possible for a postgresql db (asyncpg) + async sqlalchemy to:
>
> Synchronise the columns of a declarative model with its underlying table 
> after metadata reflection. Currently, I have to manually specify the 
> columns in the declarative model.
>
>
>

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/6cb2e213-02a3-4d9f-921f-ec6886ad68ecn%40googlegroups.com.

[sqlalchemy] Re: SQLAlchemy with MySQL on AWS Lambda is taking long time to truncate table

[Michael Bayer]

is truncate happening in some other process?   does it speed up once you 
kill the initial python process?   does this truncate take time with other 
kinds of INSERT statements?   is the table very large?   overall you need 
to perform more observations here as to what conditions cause this 
"truncate" to take a lot of time.  there's nothing happening in SQLAlchemy 
that affects this much, particularly for MySQL which is not very aggressive 
about locking tables.

On Wednesday, December 14, 2022 at 12:19:53 AM UTC-5 chandra...@gmail.com 
wrote:

> I tried this, kept commit and close statements and removed dispose 
> statement, but yet the problem is not solved, it's taking time to truncate 
> the table.
>
> On Wednesday, 14 December 2022 at 05:19:27 UTC+5:30 yaakovb...@gmail.com 
> wrote:
>
>> Is it possible your sessions hasn't committed the change / closed the 
>> transaction?
>>
>> Also, I don't think dispose is helping you here. Consider removing it?
>>
>> How about modifying:
>>
>> def add_user():
>> session = Session()
>> session.add(User(**{'user_id': 1, 'name': 'user name'}))
>> session.commit()
>> session.close()
>> # consider removing
>> # session.bind.dispose()
>>
>>
>> On Tuesday, December 13, 2022 at 8:13:14 AM UTC-5 chandra...@gmail.com 
>> wrote:
>>
>>> On creating all tables using alembic for migrations and then truncate 
>>> any empty table gets completed quickly, BUT once lambda function is 
>>> triggered to insert some data in a table through SQLAlchemy ORM Session 
>>> query (as given below) and then truncate the table takes very much time. 
>>> Where is the problem?
>>>
>>> ```
>>> from sqlalchemy import create_engine
>>> from sqlalchemy.orm import sessionmaker
>>>
>>> engine = create_engine("mysql+pymysql://")
>>> Session = sessionmaker(bind=engine)
>>>
>>> def add_user():
>>> session = Session()
>>> session.add(User(**{'user_id': 1, 'name': 'user name'}))
>>> session.close()
>>> session.bind.dispose()
>>>
>>> ```
>>>
>>

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/d1c8298d-d7f2-4686-8ba7-26fb162994c9n%40googlegroups.com.

[sqlalchemy] Re: getting raw of the query

[Michael Bayer]

using logging is the most direct way, however if you want to 
programmatically intercept all SQL you can use before_cursor_execute: 
https://docs.sqlalchemy.org/en/14/core/events.html#sqlalchemy.events.ConnectionEvents.before_cursor_execute

On Thursday, December 8, 2022 at 9:31:56 AM UTC-5 yahme...@gmail.com wrote:

> how to get  *raw* query of each *sqlalchemy session orm* in db file:
> for example in db.py file
>
> the raw of the executing query has to be taken 
>

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/fd1c5620-36d2-46f3-a208-7be7ff9c5fecn%40googlegroups.com.

[sqlalchemy] Re: Does SQLAlchemy Core support MERGE?

[Michael Bayer]

we don't support the SQL standard MERGE directly right now but you can 
always emit this statement using text().

We support only the MySQL / PostgreSQL / SQlite driver specific INSERT..ON 
CONFLICT kinds of statements directly right now.  

On Sunday, December 11, 2022 at 11:34:43 AM UTC-5 mkmo...@gmail.com wrote:

> Apologies if this is in the documentation, but I was not able to find it.
>
> Does SQLAlchemy Core support the MERGE statement?
>
> Thanks and best regards,
>
> Matthew
>

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/4bb050d2-e828-4c82-b578-9978225c33f8n%40googlegroups.com.

[sqlalchemy] Re: load_only when loading relatinoships from an instance

[Michael Bayer]

yeah you can use defaultload.load_only


defaultload(ModelA.model_b).load_only(ModelB.only_field)



On Wednesday, January 4, 2023 at 3:15:02 AM UTC-5 to...@revenuecat.com 
wrote:

> Hi,
>
> This might be a strange question, but I tried to find this in the 
> documentation to no avail.
>
> Is it possible to use something like load_only to override which columns 
> are loaded in when loading a relationship (as in, a relationship that is 
> not loaded at first with the original query)?
>
> Something like:
> class ModelB:
>   ...
>
> class ModelA:
>   model_b = relationship("ModelB")
>
> model_a = 
> session.query(ModelA).options(load_only(Model_b.only_field_i_want_in_the_future)).filter(ModelA.id==1).first()
>
> It's a bit strange, but I want to ensure if someone loads the model_b 
> property in the future, only specific columns are loaded in at first.
>
> I can do this if I just query for model_b via the foreign key instead of 
> using the relationship property, but I'd like to avoid that if possible.
>
> Sorry if this question is a bit weird/confusing, it's kind of a strange 
> use case.
>
> Thanks,
> Tony
>

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/8a25a58e-73a0-42c1-94ba-6007860e1e3fn%40googlegroups.com.

[sqlalchemy] Caching with selectedinload causing AttributeError: 'PathToken' object has no attribute 'serialize'

[Michael Bukachi]

Hello, 

So I've got a very weird when trying to set up caching using dogpile based 
on the examples provided. I'm using the latest version of SQLAlchemy 1.4. 
For some strange reason using selectinload and attempting to cache the 
frozen result is causing  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 view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/f3e129fd-c28b-4db5-8f02-2d9a12d66de6n%40googlegroups.com.

[sqlalchemy] caching with selecinload causing AttributeError: 'PathToken' object has no attribute 'serialize'

[Michael Bukachi]

Michael Bukachi
5:11 AM (now) 
to sqlalchemy
Hello, 

So I've got a very weird when trying to setup caching using dogpile base on 
the examples provided. For some strange reason using selectinload is 
resulting in 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 view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/f69d2d44-29a5-4227-9d2c-a4877ba99326n%40googlegroups.com.

[sqlalchemy] cache with selectinload causing AttributeError: 'PathToken' object has no attribute 'serialize'

[Michael Bukachi]

Hello, 

So I've got a very weird when trying to setup caching using dogpile base on 
the examples provided. For some strange reason using selectinload is 
resulting in load_options: Optional[List] = 
[
joinedload(CatalogProduct.partner).load_only(
"name", "partner_type", "status"
),
joinedload(CatalogProduct.product).options(
selectinload(Product.attributes),
selectinload(Product.values),
joinedload(Product.subcategory).joinedload(Subcategory.category),
joinedload(Product.template).selectinload(ProductTemplate.attributes),
),
selectinload(CatalogProduct.uoms)
.joinedload(CatalogProductUoM.uom)
.load_only("name"),
joinedload(CatalogProduct.default_uom)
.joinedload(CatalogProductUoM.uom)
.load_only("name"),
joinedload(CatalogProduct.restock_uom)
.joinedload(CatalogProductUoM.uom)
.load_only("name"),
selectinload(CatalogProduct.areas)
.joinedload(CatalogProductArea.marketplace_area)
.joinedload(CatalogMarketplaceArea.area)
.load_only("name"),
selectinload(CatalogProduct.excluded_areas)
.joinedload(CatalogMarketplaceArea.area)
.load_only("name"),
selectinload(CatalogProduct.allowed_areas).load_only(
"partner_id", "area_id"
),
selectinload(CatalogProduct.images),
joinedload(CatalogProduct.default_image),
joinedload(CatalogProduct.supplier).load_only(
"name", "partner_type", "status"
),
raiseload("*"),
]

if I replace all the root selectinloads without joinedloads I don't get the 
error. Is there something to do with selectinloads and caching that I'm 
missing?

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/4ff567a7-e89e-471e-85ba-162814bd28a3n%40googlegroups.com.

Re: [sqlalchemy] Re: many-to-many orm warnings

[Michael Merickel]

Thank you Mike. Really appreciate you unpacking my rambling. This works for
me. I found a few spots in our codebase where we were relying on append()
working because it really was a simple link table but I rewrote them to
just create the link manually and add it to the session which also causes
them to appear in the lists.

On Thu, Mar 10, 2022 at 9:17 AM Mike Bayer  wrote:

> hey there.
>
> The warnings go away entirely by making Parent.children viewonly=True,
> which for this type of mapping is recommended:
>
> class Parent(Base):
> __tablename__ = "left"
> id = Column(Integer, primary_key=True)
> children = relationship(
> "Child", secondary=Association.__table__, backref="parents",
> viewonly=True
>
> )
>
>
> you wouldn't want to append new records to Parent.children because that
> would create invalid Association rows (missing extra_data).
>
> The warning box at the end of
> https://docs.sqlalchemy.org/en/14/orm/basic_relationships.html#association-object
> discusses this situation and the desirability of making the relationship
> which includes "secondary" as viewonly=True.
>
> hope this helps
>
>
> On Wed, Mar 9, 2022, at 8:09 PM, Michael Merickel wrote:
>
> Sorry for the rambling, it's been difficult for me to figure out what
> question to ask because I'm so confused. Below is the minimum viable
> example that produces no warnings with respect to the overlaps flags and I
> cannot explain hardly any of them. For example, why does Child.parents
> require "child_links,parent,child"? 3 values that seem to be somewhat
> unrelated and are at the very least definitely on different models?
>
> class Association(Base):
> __tablename__ = 'association'
> left_id = Column(ForeignKey('left.id'), primary_key=True)
> right_id = Column(ForeignKey('right.id'), primary_key=True)
> extra_data = Column(String(50))
>
> parent = relationship('Parent', back_populates='child_links')
> child = relationship('Child', back_populates='parent_links')
>
> class Parent(Base):
> __tablename__ = 'left'
> id = Column(Integer, primary_key=True)
>
> children = relationship(
> 'Child',
> secondary=Association.__table__,
> back_populates='parents',
> overlaps='child,parent',
> )
> child_links = relationship(
> 'Association',
> back_populates='parent',
> overlaps='children',
> )
>
> class Child(Base):
> __tablename__ = 'right'
> id = Column(Integer, primary_key=True)
>
> parents = relationship(
> 'Parent',
> secondary=Association.__table__,
> back_populates='children',
> overlaps='child_links,parent,child',
> )
> parent_links = relationship(
> 'Association',
> back_populates='child',
> overlaps='children,parents',
> )
>
>
> On Wed, Mar 9, 2022 at 4:50 PM Michael Merickel 
> wrote:
>
> I think ultimately I want the overlaps config but reading through
> https://docs.sqlalchemy.org/en/14/errors.html#relationship-x-will-copy-column-q-to-column-p-which-conflicts-with-relationship-s-y
> it doesn't make any sense to me what the values in the overlaps= argument
> are referring to. For example in last snippet that was simpler, what is
> overlaps='parent' referring to? Neither the Parent object, nor the Child
> object has something named "parent" so other than blinding trusting the
> warning I'm unclear how to see what the mapper is building that conflicts
> here.
>
> On Wed, Mar 9, 2022 at 4:33 PM Michael Merickel 
> wrote:
>
> It's probably worth noting I can narrow it down to a single warning with
> the following snippet and it's still unclear to me how to resolve this:
>
> class Association(Base):
> __tablename__ = 'association'
> left_id = Column(ForeignKey('left.id'), primary_key=True)
> right_id = Column(ForeignKey('right.id'), primary_key=True)
> extra_data = Column(String(50))
>
> parent = relationship('Parent')
>
> class Parent(Base):
> __tablename__ = 'left'
> id = Column(Integer, primary_key=True)
> children = relationship('Child', secondary=Association.__table__)
>
> class Child(Base):
> __tablename__ = 'right'
> id = Column(Integer, primary_key=True)
>
> foo.py:24: SAWar

[sqlalchemy] Re: many-to-many orm warnings

[Michael Merickel]

Sorry for the rambling, it's been difficult for me to figure out what
question to ask because I'm so confused. Below is the minimum viable
example that produces no warnings with respect to the overlaps flags and I
cannot explain hardly any of them. For example, why does Child.parents
require "child_links,parent,child"? 3 values that seem to be somewhat
unrelated and are at the very least definitely on different models?

class Association(Base):
__tablename__ = 'association'
left_id = Column(ForeignKey('left.id'), primary_key=True)
right_id = Column(ForeignKey('right.id'), primary_key=True)
extra_data = Column(String(50))

parent = relationship('Parent', back_populates='child_links')
child = relationship('Child', back_populates='parent_links')

class Parent(Base):
__tablename__ = 'left'
id = Column(Integer, primary_key=True)

children = relationship(
'Child',
secondary=Association.__table__,
back_populates='parents',
overlaps='child,parent',
)
child_links = relationship(
'Association',
back_populates='parent',
overlaps='children',
)

class Child(Base):
__tablename__ = 'right'
id = Column(Integer, primary_key=True)

parents = relationship(
'Parent',
secondary=Association.__table__,
back_populates='children',
overlaps='child_links,parent,child',
)
parent_links = relationship(
'Association',
back_populates='child',
overlaps='children,parents',
)

On Wed, Mar 9, 2022 at 4:50 PM Michael Merickel  wrote:

> I think ultimately I want the overlaps config but reading through
> https://docs.sqlalchemy.org/en/14/errors.html#relationship-x-will-copy-column-q-to-column-p-which-conflicts-with-relationship-s-y
> it doesn't make any sense to me what the values in the overlaps= argument
> are referring to. For example in last snippet that was simpler, what is
> overlaps='parent' referring to? Neither the Parent object, nor the Child
> object has something named "parent" so other than blinding trusting the
> warning I'm unclear how to see what the mapper is building that conflicts
> here.
>
> On Wed, Mar 9, 2022 at 4:33 PM Michael Merickel 
> wrote:
>
>> It's probably worth noting I can narrow it down to a single warning with
>> the following snippet and it's still unclear to me how to resolve this:
>>
>> class Association(Base):
>> __tablename__ = 'association'
>> left_id = Column(ForeignKey('left.id'), primary_key=True)
>> right_id = Column(ForeignKey('right.id'), primary_key=True)
>> extra_data = Column(String(50))
>>
>> parent = relationship('Parent')
>>
>> class Parent(Base):
>> __tablename__ = 'left'
>> id = Column(Integer, primary_key=True)
>> children = relationship('Child', secondary=Association.__table__)
>>
>> class Child(Base):
>> __tablename__ = 'right'
>> id = Column(Integer, primary_key=True)
>>
>> foo.py:24: SAWarning: relationship 'Parent.children' will copy column
>>> left.id to column association.left_id, which conflicts with
>>> relationship(s): 'Association.parent' (copies left.id to
>>> association.left_id). If this is not the intention, consider if these
>>> relationships should be linked with back_populates, or if viewonly=True
>>> should be applied to one or more if they are read-only. For the less common
>>> case that foreign key constraints are partially overlapping, the
>>> orm.foreign() annotation can be used to isolate the columns that should be
>>> written towards.   To silence this warning, add the parameter
>>> 'overlaps="parent"' to the 'Parent.children' relationship. (Background on
>>> this error at: https://sqlalche.me/e/14/qzyx)
>>
>>
>> On Wed, Mar 9, 2022 at 4:31 PM Michael Merickel 
>> wrote:
>>
>>> I have looked at the couple examples in the docs (many-to-many, and
>>> association table) and have noticed that my codebase has a slightly
>>> different pattern which is causing warnings when upgrading to 1.4. I'm
>>> trying to figure out the best pattern to accomplish what I've been doing
>>> which doesn't match the docs exactly.
>>>
>>> In the below example you can see that there are backrefs on all of the
>>> lin

[sqlalchemy] Re: many-to-many orm warnings

[Michael Merickel]

I think ultimately I want the overlaps config but reading through
https://docs.sqlalchemy.org/en/14/errors.html#relationship-x-will-copy-column-q-to-column-p-which-conflicts-with-relationship-s-y
it doesn't make any sense to me what the values in the overlaps= argument
are referring to. For example in last snippet that was simpler, what is
overlaps='parent' referring to? Neither the Parent object, nor the Child
object has something named "parent" so other than blinding trusting the
warning I'm unclear how to see what the mapper is building that conflicts
here.

On Wed, Mar 9, 2022 at 4:33 PM Michael Merickel  wrote:

> It's probably worth noting I can narrow it down to a single warning with
> the following snippet and it's still unclear to me how to resolve this:
>
> class Association(Base):
> __tablename__ = 'association'
> left_id = Column(ForeignKey('left.id'), primary_key=True)
> right_id = Column(ForeignKey('right.id'), primary_key=True)
> extra_data = Column(String(50))
>
> parent = relationship('Parent')
>
> class Parent(Base):
> __tablename__ = 'left'
> id = Column(Integer, primary_key=True)
> children = relationship('Child', secondary=Association.__table__)
>
> class Child(Base):
> __tablename__ = 'right'
> id = Column(Integer, primary_key=True)
>
> foo.py:24: SAWarning: relationship 'Parent.children' will copy column
>> left.id to column association.left_id, which conflicts with
>> relationship(s): 'Association.parent' (copies left.id to
>> association.left_id). If this is not the intention, consider if these
>> relationships should be linked with back_populates, or if viewonly=True
>> should be applied to one or more if they are read-only. For the less common
>> case that foreign key constraints are partially overlapping, the
>> orm.foreign() annotation can be used to isolate the columns that should be
>> written towards.   To silence this warning, add the parameter
>> 'overlaps="parent"' to the 'Parent.children' relationship. (Background on
>> this error at: https://sqlalche.me/e/14/qzyx)
>
>
> On Wed, Mar 9, 2022 at 4:31 PM Michael Merickel 
> wrote:
>
>> I have looked at the couple examples in the docs (many-to-many, and
>> association table) and have noticed that my codebase has a slightly
>> different pattern which is causing warnings when upgrading to 1.4. I'm
>> trying to figure out the best pattern to accomplish what I've been doing
>> which doesn't match the docs exactly.
>>
>> In the below example you can see that there are backrefs on all of the
>> links, and that there are backrefs from the link table to the related
>> objects, as well as a secondary link from Parent to Child via
>> Parent.children and Child.parents.
>>
>> There seem to be several options and I'm struggling to figure out what
>> the solution should be to maintain the behavior with all of the following
>> relationships working:
>>
>> - Parent.children
>> - Parent.child_links
>> - Child.parents
>> - Child.parent_links
>> - Association.parent
>> - Association.child
>>
>> Code and warnings are below:
>>
>> from sqlalchemy import Column, ForeignKey, String, Integer
>> from sqlalchemy.orm import configure_mappers, relationship
>> from sqlalchemy.ext.declarative import declarative_base
>>
>> Base = declarative_base()
>>
>> class Association(Base):
>> __tablename__ = 'association'
>> left_id = Column(ForeignKey('left.id'), primary_key=True)
>> right_id = Column(ForeignKey('right.id'), primary_key=True)
>> extra_data = Column(String(50))
>>
>> parent = relationship('Parent', backref='child_links')
>> child = relationship('Child', backref='parent_links')
>>
>> class Parent(Base):
>> __tablename__ = 'left'
>> id = Column(Integer, primary_key=True)
>> children = relationship('Child', secondary=Association.__table__,
>> backref='parents')
>>
>> class Child(Base):
>> __tablename__ = 'right'
>> id = Column(Integer, primary_key=True)
>>
>> configure_mappers()
>>
>> foo.py:25: SAWarning: relationship 'Child.parents' will copy column
>>> right.id to column association.right_id, which conflicts with
>>> relationship(s): 'Association.child' (copies right.id to
>>> association.righ

[sqlalchemy] Re: many-to-many orm warnings

[Michael Merickel]

It's probably worth noting I can narrow it down to a single warning with
the following snippet and it's still unclear to me how to resolve this:

class Association(Base):
__tablename__ = 'association'
left_id = Column(ForeignKey('left.id'), primary_key=True)
right_id = Column(ForeignKey('right.id'), primary_key=True)
extra_data = Column(String(50))

parent = relationship('Parent')

class Parent(Base):
__tablename__ = 'left'
id = Column(Integer, primary_key=True)
children = relationship('Child', secondary=Association.__table__)

class Child(Base):
__tablename__ = 'right'
id = Column(Integer, primary_key=True)

foo.py:24: SAWarning: relationship 'Parent.children' will copy column
> left.id to column association.left_id, which conflicts with
> relationship(s): 'Association.parent' (copies left.id to
> association.left_id). If this is not the intention, consider if these
> relationships should be linked with back_populates, or if viewonly=True
> should be applied to one or more if they are read-only. For the less common
> case that foreign key constraints are partially overlapping, the
> orm.foreign() annotation can be used to isolate the columns that should be
> written towards.   To silence this warning, add the parameter
> 'overlaps="parent"' to the 'Parent.children' relationship. (Background on
> this error at: https://sqlalche.me/e/14/qzyx)


On Wed, Mar 9, 2022 at 4:31 PM Michael Merickel  wrote:

> I have looked at the couple examples in the docs (many-to-many, and
> association table) and have noticed that my codebase has a slightly
> different pattern which is causing warnings when upgrading to 1.4. I'm
> trying to figure out the best pattern to accomplish what I've been doing
> which doesn't match the docs exactly.
>
> In the below example you can see that there are backrefs on all of the
> links, and that there are backrefs from the link table to the related
> objects, as well as a secondary link from Parent to Child via
> Parent.children and Child.parents.
>
> There seem to be several options and I'm struggling to figure out what the
> solution should be to maintain the behavior with all of the following
> relationships working:
>
> - Parent.children
> - Parent.child_links
> - Child.parents
> - Child.parent_links
> - Association.parent
> - Association.child
>
> Code and warnings are below:
>
> from sqlalchemy import Column, ForeignKey, String, Integer
> from sqlalchemy.orm import configure_mappers, relationship
> from sqlalchemy.ext.declarative import declarative_base
>
> Base = declarative_base()
>
> class Association(Base):
> __tablename__ = 'association'
> left_id = Column(ForeignKey('left.id'), primary_key=True)
> right_id = Column(ForeignKey('right.id'), primary_key=True)
> extra_data = Column(String(50))
>
> parent = relationship('Parent', backref='child_links')
> child = relationship('Child', backref='parent_links')
>
> class Parent(Base):
> __tablename__ = 'left'
> id = Column(Integer, primary_key=True)
> children = relationship('Child', secondary=Association.__table__,
> backref='parents')
>
> class Child(Base):
> __tablename__ = 'right'
> id = Column(Integer, primary_key=True)
>
> configure_mappers()
>
> foo.py:25: SAWarning: relationship 'Child.parents' will copy column
>> right.id to column association.right_id, which conflicts with
>> relationship(s): 'Association.child' (copies right.id to
>> association.right_id), 'Child.parent_links' (copies right.id to
>> association.right_id). If this is not the intention, consider if these
>> relationships should be linked with back_populates, or if viewonly=True
>> should be applied to one or more if they are read-only. For the less common
>> case that foreign key constraints are partially overlapping, the
>> orm.foreign() annotation can be used to isolate the columns that should be
>> written towards.   To silence this warning, add the parameter
>> 'overlaps="child,parent_links"' to the 'Child.parents' relationship.
>> (Background on this error at: https://sqlalche.me/e/14/qzyx)
>>   configure_mappers()
>> foo.py:25: SAWarning: relationship 'Child.parents' will copy column
>> left.id to column association.left_id, which conflicts with
>> relationship(s): 'Association.parent' (copies left.id to
>> association.left_id), 'Parent.child_links'

[sqlalchemy] many-to-many orm warnings

[Michael Merickel]

sociation.right_id). If this is not the intention, consider if these
> relationships should be linked with back_populates, or if viewonly=True
> should be applied to one or more if they are read-only. For the less common
> case that foreign key constraints are partially overlapping, the
> orm.foreign() annotation can be used to isolate the columns that should be
> written towards.   To silence this warning, add the parameter
> 'overlaps="child,parent_links"' to the 'Parent.children' relationship.
> (Background on this error at: https://sqlalche.me/e/14/qzyx)
>   configure_mappers()


Thanks!

-- 

Michael

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/CAKdhhwGNLvZo3gF6h-PdWTkb%2BPDdzgq8avWC30oOWgu7%2BObTmQ%40mail.gmail.com.

[sqlalchemy] When building a query, option to only join once

[Michael Elsdörfer]

I keep running into this issue where I have a complex set of filter 
parameters, some of which need a join, and it would be nice to have an 
ergonomic way to say: join this table, but not if there already is a join.

I found this old post on the subject: 
https://groups.google.com/g/sqlalchemy/c/ooFYsED4CI8

I was just curious if there have been further developments since then; in 
particular, using new-style querying in 1.4, I guess there isn't a way to 
add a custom, de-duplicating  `join()` method onto the `select()` object?

Michael

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/280e2f1b-ae95-487a-9d99-aef1bd73d20an%40googlegroups.com.

[sqlalchemy] Re: Unique Partial Indexes throwing UniqueViolation

[Michael Bukachi]

Typo alert. It's supposed to be `postgresql_where`. How silly of me.

On Wednesday, May 12, 2021 at 3:25:35 AM UTC+3 Michael Bukachi wrote:

>
> Hi
>
> I'm trying to setup unique partial indexes so that constraint violation is 
> only thrown when a a certain column is true. This is the index I'm using:
>
> __table_args__ = (
>Index(
>  "idx_one_active_fulfillment",
>  member_id,
>  status, 
>  unique=True,
>  postgres_where=(status == 'ACTIVE'),
>),
> )
>
> As long as the status is not 'ACTIVE', a member can have multiple entries 
> i.e
> member_id, status
> 1, 'COMPLETED'
> 1, 'COMPLETED'
> 2, 'ACTIVE'
>
> However, this is not the case. Once I insert an entry e.g 1, 'COMPLETED'; 
> I can't insert it again since it throws an error of 'duplicate key value 
> violates unique constraint'
>
> The weird thing is that, if I run all the queries using psql, it works 
> properly. Is there something I'm missing? I'm trying to see what 
> `create_all` executes but it seems setting echo=True doesn't affect it.
>
> Cheers.
>

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/d76c0079-0fd3-4bdd-ad4a-5e6e45b54f9cn%40googlegroups.com.

[sqlalchemy] Unique Partial Indexes throwing UniqueViolation

[Michael Bukachi]

Hi

I'm trying to setup unique partial indexes so that constraint violation is 
only thrown when a a certain column is true. This is the index I'm using:

__table_args__ = (
   Index(
 "idx_one_active_fulfillment",
 member_id,
 status, 
 unique=True,
 postgres_where=(status == 'ACTIVE'),
   ),
)

As long as the status is not 'ACTIVE', a member can have multiple entries 
i.e
member_id, status
1, 'COMPLETED'
1, 'COMPLETED'
2, 'ACTIVE'

However, this is not the case. Once I insert an entry e.g 1, 'COMPLETED'; I 
can't insert it again since it throws an error of 'duplicate key value 
violates unique constraint'

The weird thing is that, if I run all the queries using psql, it works 
properly. Is there something I'm missing? I'm trying to see what 
`create_all` executes but it seems setting echo=True doesn't affect it.

Cheers.

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/f1fd46c2-1a89-42af-9676-32cf965b4b3cn%40googlegroups.com.

Re: [sqlalchemy] Implementing Role-Based Access Control (RBAC) in SQLAlchemy with oso

['Michael Mulqueen' via sqlalchemy]

Thanks for sharing this. Will take a good look at it, I've been looking for
something like this.

There's a broken link on the text "guide to roles patterns

".

On Thu, 7 Jan 2021, 18:15 Stephie Glaser,  wrote:

> Hi all, we've been working towards building Role-Based Access Control
> (RBAC) features into our libraries at oso. We had released a preview of
> those features in our sqlalchemy-oso package, and since then have polished
> those features up, written some docs, and are excited to share a sample app
> showcasing our new out-of-the box roles features!
>
> Link to Introducing Built-in Roles with oso.
>  It covers how to
> structure Role-Based Access Control (RBAC) and how we ship roles
> out-of-the-box for SQLAlchemy. Plus feature designs, broader thinking on
> roles, and the sample app we use to validate and showcase the
> sqlalchemy-oso library.
>
>
>
>
>
> --
> 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 view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/50f0c039-9fdb-42cb-b40e-a17f8c70c282n%40googlegroups.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 view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/CAHxMHYWWekkQ7TWbUPfznYupgi5LczjB-yWNKKTBL4X1M0bJYg%40mail.gmail.com.

[sqlalchemy] Re: synonym equivalent for core?

[Michael Merickel]

Well it looks like if I use column objects in a dict then it works out. For 
example:

...insert({Event.start_time: datetime.utcnow()})

On Tuesday, December 22, 2020 at 2:16:23 AM UTC-6 Michael Merickel wrote:

> Is there a way to make the following insert statement work by changing 
> something in the Event object's definition? Note I've simplified the 
> example, the reasons for using the core constructs in the first place are 
> to use postgres ON CONFLICT directives with it otherwise yes I could simply 
> use the ORM where the synonyms would work fine.
>
> class Event(Base):
> id = Column(BigInteger, primary_key=True)
> start_date = Column(DateTime)
> start_time = synonym('start_date')
>
>
> dbsession.execute(insert(Event.__table__).values(start_time=datetime.utcnow()).returning(Event.id)).scalar()
>
> Error raised, presumably because a core insert construct doesn't support 
> column aliases defined at the mapper level?
>
> sqlalchemy.exc.CompileError: Unconsumed column names: start_time
>
> Thanks!
>
> - Michael
>
>

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/c20edd8d-78e9-4efb-a040-27d262be48b1n%40googlegroups.com.

[sqlalchemy] synonym equivalent for core?

[Michael Merickel]

Is there a way to make the following insert statement work by changing 
something in the Event object's definition? Note I've simplified the 
example, the reasons for using the core constructs in the first place are 
to use postgres ON CONFLICT directives with it otherwise yes I could simply 
use the ORM where the synonyms would work fine.

class Event(Base):
id = Column(BigInteger, primary_key=True)
start_date = Column(DateTime)
start_time = synonym('start_date')

dbsession.execute(insert(Event.__table__).values(start_time=datetime.utcnow()).returning(Event.id)).scalar()

Error raised, presumably because a core insert construct doesn't support 
column aliases defined at the mapper level?

sqlalchemy.exc.CompileError: Unconsumed column names: start_time

Thanks!

- Michael

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/e08eadbd-ade9-4ee5-bcc0-3609ecd959f0n%40googlegroups.com.

Re: [sqlalchemy] How to disable the integer col + pk = serial behaviour?

['Michael Mulqueen' via sqlalchemy]

I've just done something like this the other day, but it was with an
existing sequence. We're using Alembic for schema updates, so I'm not sure
whether SQLAlchemy's built-in create_all would behave the same way. You
should still be able to use a similar approach.

shared_sequence = Sequence('shared_id__seq')


class ModelA(Base):
shared_id = Column(Integer,
   primary_key=True,
   default=shared_sequence.next_value(),
   server_default=shared_sequence.next_value())

class ModelB(Base):
shared_id = Column(Integer,
   primary_key=True,
   default=shared_sequence.next_value(),
   server_default=shared_sequence.next_value())

This seems to be working fine.

Before that I'd tried providing Sequence as an arg to Column like you have
and I'd run into some problems and this seemed like an easier option.


On Wed, 5 Aug 2020 at 13:59, Zsolt Ero  wrote:

> Hi,
>
> I've split a table into two tables, for performance reasons. I'd like to
> insert into both tables using the same sequence. I'm inserting using
> executemany_mode='values'.
>
> My idea is to call nextval() on the sequence before insert and fill in the
> values client side, before inserting.
>
> select nextval('mysql') FROM generate_series(1,...)
>
> Everything looks good, except for the default behaviour of SQLAlchemy to
> turn an integer + pk column into a SERIAL.
>
> As an alternative I'm also looking at using Sequence('myseq') from
> https://docs.sqlalchemy.org/en/13/dialects/postgresql.html#sequences-serial-identity,
> but this is broken for issuing "CREATE SEQUENCE myseq" before the table
> creation, which is missing the IF NOT EXISTS part.
>
> How can I either:
> - turn off the automatic behaviour of making a pg + int = serial?
> - add a IF NOT EXISTS to the Sequence()?
>
> Or any alternative ideas?
>
> --
> 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 view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/7860f5ab-64a4-481d-8e72-9b40d2ab2527o%40googlegroups.com
> <https://groups.google.com/d/msgid/sqlalchemy/7860f5ab-64a4-481d-8e72-9b40d2ab2527o%40googlegroups.com?utm_medium=email&utm_source=footer>
> .
>


-- 

Michael Mulqueen

*Method B Ltd*
m...@method-b.uk / +44 (0)330 223 0864
http://www.method-b.uk/

Method B Limited is registered in England and Wales with the company number
9189496. Registered office: 28-29 Richmond Place, Brighton, East Sussex,
England, BN2 9NA

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/CAHxMHYXTJ8zLobCdW%2BAtTMaUHGFKkub-9L8cEJMJhYBfPUO%3DQA%40mail.gmail.com.

Re: [sqlalchemy] How can I query two fields in my database to confirm it exists and then delete

['Michael Mulqueen' via sqlalchemy]

With the ORM, you'd filter, something like this:

.filter(LessonEntity.lesson_id == lesson_id, LessionEntity.device_type_id
== device_type_id)

If you were just expecting to find one, you might want to chain .first()
onto the end of that.

You can delete as normal: session.delete(obj)

If you want to delete without checking whether they exist first and avoid
shuttling data back and forth from the database to the application, you can
use the expression language to issue a DELETE command limited by a WHERE
clause. https://docs.sqlalchemy.org/en/13/core/tutorial.html

Kind regards,
Mike


On Tue, 4 Aug 2020 at 10:32, Adam Shock  wrote:

> My SQL database has the following:
>
>
>
> to query the lesson_id i would do :
> lesson = LessonEntity.query.get(lesson_id)
>
> How can i query the database to check if lesson_id exists on the same
> entry as device type and then remove..
>
> so for example. if lesson_id matches 107 and the same entry includes
> device_type_id = 7 i want to remove this whole entry
>
> --
> 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 view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/85301330-9208-4f8e-b1d4-e89ea8942239o%40googlegroups.com
> <https://groups.google.com/d/msgid/sqlalchemy/85301330-9208-4f8e-b1d4-e89ea8942239o%40googlegroups.com?utm_medium=email&utm_source=footer>
> .
>


-- 

Michael Mulqueen

*Method B Ltd*
m...@method-b.uk / +44 (0)330 223 0864
http://www.method-b.uk/

Method B Limited is registered in England and Wales with the company number
9189496. Registered office: 28-29 Richmond Place, Brighton, East Sussex,
England, BN2 9NA

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/CAHxMHYVRk9bSD31Ut6ppQttPr-A1OQ5E8moW6JbdO_uSN-SV7Q%40mail.gmail.com.

Re: [sqlalchemy] session.add with insert-or-update

['Michael Mulqueen' via sqlalchemy]

Hi Keith,

Small world!

You have at least 3 options depending on your requirements:

1. Handle it in your own application logic (e.g. make a get_or_create
method) - I tend to prefer this, business rules for create vs. update often
creeps in.
2. Use session.merge
https://docs.sqlalchemy.org/en/13/orm/session_state_management.html#merging
3. Upserts -
https://docs.sqlalchemy.org/en/13/dialects/mysql.html#insert-on-duplicate-key-update-upsert
(this also exists for PostgreSQL).

First option example:

# SQLAlchemy models
class Incident(Base):
incident_id = Column(Integer, primary_key=True)

@classmethod
def get_or_create(cls, session, id_=None):
if id_:
incident = session.query(cls).filter(cls.incident_id == id_).one()
else:
incident = Incident()
session.add(incident)
return incident

def populate(self, data):
for key, value in data.items():
assert hasattr(self, key)
setattr(self, key, value)



# Request handler.
def report_incident():
incident = Incident.get_or_create(session, record.get("incident_id"))
incident.populate(record)
session.commit()

session.add doesn't imply insert by the way (it's adding to the session,
not adding to the database), so calling it on an object that's been
retrieved in the same session won't cause problems.

Hope that helps.

Cheers,
Mike



On Sat, 7 Mar 2020, 18:34 Keith Edmunds,  wrote:

> I'm new to SQLAlchemy. Sorry if it shows.
>
> I'm using a MySQL backend. I've set up a declarative_base, defined a table
> class, set up a session. I defined a record as a dictionary and added it
> successfully to the db with:
>
> incident = Incidents(**record)
> session.add(incident)
> session.commit()
>
> The behaviour I'd like from the add/commit steps is to update any existing
> records that has a matching Primary Key, or insert as a new record if
> there's no match.
>
> I see posts on how to do that with core functionality, but how would I do
> that using the ORM as above?
>
> --
> 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 view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/9dfd3fb5-5516-4bea-8cd1-9abf3e6280fc%40googlegroups.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 view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/CAHxMHYUoo3bmtg%3DZjcQE%2B5u7dMcwXvy4jYCMknkawz84Gpcr9g%40mail.gmail.com.

Re: [sqlalchemy] Complex Constraints in Many to Many relationships

[Michael P. McDonnell]

 You make it seem so easy.

Thank you!

On Thu, Sep 5, 2019 at 11:11 AM Mike Bayer  wrote:

>
>
> On Wed, Sep 4, 2019, at 5:12 PM, Michael P. McDonnell wrote:
>
> Hey -
> I'm again at a loss of what to google, and as this will ultimately need to
> be represented in some fashion in sqlalchemy, I figured this is a great
> place to start:
>
> I have a |person| table and a |team| table with a many to many table in
> between |team_person|.
> Simple enough!
>
> Now - to make it fun.
> |team| has a relationship to |tournament|
> How can I prevent a user from joining more than 1 team in a given
> tournament?
>
> I thought about adding a 3rd column to my M2M table,
> (team_tournament_person), but that could still fail because it could be a
> team from tournament x, tournament y's ID and a Person Q's ID.
>
>
> if you make team_person have tournament_id, person_id, team_id, then
> team_person has (tournament_id, person_id) as primary key, that means
> person_id can only be part of a tournament_id once.  Then you create
> foreign key on team_person for (team_id, tournament_id) to team_table(id,
> tournament_id).  this might need you to create  a unique constraint on
> team(id, tournament_id) or set that as its primary key.
>
>
>
> So any suggestions on what I should be googling, and then how to implement
> in SA would be hugely appreciated!
>
> Thanks!
>
>
> --
> 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 view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/CAHmCLHq%2BCorBMogufyGh6jpX%2BBt6gNpT38oPe4TJ85xDoahHxQ%40mail.gmail.com
> <https://groups.google.com/d/msgid/sqlalchemy/CAHmCLHq%2BCorBMogufyGh6jpX%2BBt6gNpT38oPe4TJ85xDoahHxQ%40mail.gmail.com?utm_medium=email&utm_source=footer>
> .
>
>
> --
> 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 view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/e644c7a1-c72e-42a3-b9dc-262775c46cf5%40www.fastmail.com
> <https://groups.google.com/d/msgid/sqlalchemy/e644c7a1-c72e-42a3-b9dc-262775c46cf5%40www.fastmail.com?utm_medium=email&utm_source=footer>
> .
>

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/CAHmCLHoOqeBCL9ZEnBy7zsu0WJQtAuvOz7USanfSMe4bVXqqPw%40mail.gmail.com.

Re: [sqlalchemy] Re: Complex Constraints in Many to Many relationships

[Michael P. McDonnell]

*bump* anything?

On Wed, Sep 4, 2019 at 5:02 PM Michael P. McDonnell 
wrote:

> So I must be missing something, but here's what I have right now:
>
> tournament_table = Table(
> 'tournament',
> Base.metadata,
> Column('id', UUID(as_uuid=True), primary_key=True))
>
> team_table = Table(
> 'team',
> Base.metadata,
> Column('id', UUID(as_uuid=True), primary_key=True,
> *Column('tournament_id', UUID(as_uuid=True), ForeignKey('tournament.id
> <http://tournament.id>'), nullable=False),*
> Column('display_name', String(length=255), nullable=False),
> UniqueConstraint('tournament_id', 'display_name'))
>
> team_person_table = Table(
> 'team_person',
> Base.metadata,
> *Column('team_id', UUID(as_uuid=True), ForeignKey('team.id
> <http://team.id>'), primary_key=True*),
> *Column('person_id', UUID(as_uuid=True), ForeignKey('person.id
> <http://person.id>'), primary_key=True)*)
>
> person_table = Table(
> 'person',
> Base.metadata,
> Column('id', UUID(as_uuid=True), primary_key=True))
>
>
> In my current model I can create
>
> Tournament A, Team A (paired to Tournament A), Team B (also paired to
> Tournament A), and Team C (also paired to Tournament A)
> I can then assign *Person Z* to Team A, Team B and Team C separately
> without throwing an integrity exception.
>
> How can I make it so that doesn't happen?
>
>
> On Wed, Sep 4, 2019 at 4:53 PM Derek Lambert 
> wrote:
>
>> If I'm understanding correctly...
>>
>> You're on the right track. I'd use a composite primary key on
>> |team_person|, consisting of foreign keys from |person| and |team|, and
>> another composite key (or unique index) on the |team| to |tournament|
>> table. This lets the database do all the work.
>>
>> -Derek
>>
>> On Wednesday, September 4, 2019 at 4:16:02 PM UTC-5, Michael P. McDonnell
>> wrote:
>>>
>>> Hey -
>>> I'm again at a loss of what to google, and as this will ultimately need
>>> to be represented in some fashion in sqlalchemy, I figured this is a great
>>> place to start:
>>>
>>> I have a |person| table and a |team| table with a many to many table in
>>> between |team_person|.
>>> Simple enough!
>>>
>>> Now - to make it fun.
>>> |team| has a relationship to |tournament|
>>> How can I prevent a user from joining more than 1 team in a given
>>> tournament?
>>>
>>> I thought about adding a 3rd column to my M2M table,
>>> (team_tournament_person), but that could still fail because it could be a
>>> team from tournament x, tournament y's ID and a Person Q's ID.
>>>
>>> So any suggestions on what I should be googling, and then how to
>>> implement in SA would be hugely appreciated!
>>>
>>> Thanks!
>>>
>> --
>> 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 view this discussion on the web visit
>> https://groups.google.com/d/msgid/sqlalchemy/d93a10e6-cae8-4346-bae4-99782546becf%40googlegroups.com
>> <https://groups.google.com/d/msgid/sqlalchemy/d93a10e6-cae8-4346-bae4-99782546becf%40googlegroups.com?utm_medium=email&utm_source=footer>
>> .
>>
>

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/CAHmCLHrPTG63dYfxnPvR2Ej3jQ8zF9q6w-7hzP1w%3DQ3qfA-T4Q%40mail.gmail.com.

Re: [sqlalchemy] Re: Complex Constraints in Many to Many relationships

[Michael P. McDonnell]

So I must be missing something, but here's what I have right now:

tournament_table = Table(
'tournament',
Base.metadata,
Column('id', UUID(as_uuid=True), primary_key=True))

team_table = Table(
'team',
Base.metadata,
Column('id', UUID(as_uuid=True), primary_key=True,
*Column('tournament_id', UUID(as_uuid=True), ForeignKey('tournament.id
<http://tournament.id>'), nullable=False),*
Column('display_name', String(length=255), nullable=False),
UniqueConstraint('tournament_id', 'display_name'))

team_person_table = Table(
'team_person',
Base.metadata,
*Column('team_id', UUID(as_uuid=True), ForeignKey('team.id
<http://team.id>'), primary_key=True*),
*Column('person_id', UUID(as_uuid=True), ForeignKey('person.id
<http://person.id>'), primary_key=True)*)

person_table = Table(
'person',
Base.metadata,
Column('id', UUID(as_uuid=True), primary_key=True))


In my current model I can create

Tournament A, Team A (paired to Tournament A), Team B (also paired to
Tournament A), and Team C (also paired to Tournament A)
I can then assign *Person Z* to Team A, Team B and Team C separately
without throwing an integrity exception.

How can I make it so that doesn't happen?


On Wed, Sep 4, 2019 at 4:53 PM Derek Lambert 
wrote:

> If I'm understanding correctly...
>
> You're on the right track. I'd use a composite primary key on
> |team_person|, consisting of foreign keys from |person| and |team|, and
> another composite key (or unique index) on the |team| to |tournament|
> table. This lets the database do all the work.
>
> -Derek
>
> On Wednesday, September 4, 2019 at 4:16:02 PM UTC-5, Michael P. McDonnell
> wrote:
>>
>> Hey -
>> I'm again at a loss of what to google, and as this will ultimately need
>> to be represented in some fashion in sqlalchemy, I figured this is a great
>> place to start:
>>
>> I have a |person| table and a |team| table with a many to many table in
>> between |team_person|.
>> Simple enough!
>>
>> Now - to make it fun.
>> |team| has a relationship to |tournament|
>> How can I prevent a user from joining more than 1 team in a given
>> tournament?
>>
>> I thought about adding a 3rd column to my M2M table,
>> (team_tournament_person), but that could still fail because it could be a
>> team from tournament x, tournament y's ID and a Person Q's ID.
>>
>> So any suggestions on what I should be googling, and then how to
>> implement in SA would be hugely appreciated!
>>
>> Thanks!
>>
> --
> 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 view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/d93a10e6-cae8-4346-bae4-99782546becf%40googlegroups.com
> <https://groups.google.com/d/msgid/sqlalchemy/d93a10e6-cae8-4346-bae4-99782546becf%40googlegroups.com?utm_medium=email&utm_source=footer>
> .
>

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/CAHmCLHrH0kCuYReaQgCZpAy4T9HkJxV_CFv5wKbTWJDr-x6nUg%40mail.gmail.com.

[sqlalchemy] Complex Constraints in Many to Many relationships

[Michael P. McDonnell]

Hey -
I'm again at a loss of what to google, and as this will ultimately need to
be represented in some fashion in sqlalchemy, I figured this is a great
place to start:

I have a |person| table and a |team| table with a many to many table in
between |team_person|.
Simple enough!

Now - to make it fun.
|team| has a relationship to |tournament|
How can I prevent a user from joining more than 1 team in a given
tournament?

I thought about adding a 3rd column to my M2M table,
(team_tournament_person), but that could still fail because it could be a
team from tournament x, tournament y's ID and a Person Q's ID.

So any suggestions on what I should be googling, and then how to implement
in SA would be hugely appreciated!

Thanks!

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/CAHmCLHq%2BCorBMogufyGh6jpX%2BBt6gNpT38oPe4TJ85xDoahHxQ%40mail.gmail.com.

Re: [sqlalchemy] Explicit Column Combination vs Implicit?

[Michael P. McDonnell]

Okay - I gotcha!

That makes perfect sense too. It also means that my (eventual) work around
worked:
class Round(Base):
__table__ = round_join
game_id = column_property(
round_table.c.game_id, game_table.c.id,
game_language_table.c.game_id)

On Wed, Aug 21, 2019 at 2:21 PM Mike Bayer  wrote:

>
>
> On Wed, Aug 21, 2019, at 1:46 PM, Michael P. McDonnell wrote:
>
> Hey team -
>
> I've went through the docs, and likely because I'm a bit of a hack - I'm
> finding it hard to find my answers (because I'm not sure what I'm
> specifically looking to "do" other than "make it work")
>
> So I have the following situation:
> I have a GAME table (with a corresponding LANGUAGE table) - because my
> users might play games.
>
>
>
>
>
>
>
>
>
>
>
>
> *game_language_table = Table('game_language',Base.metadata,
> Column('game_id', UUID(as_uuid=True), ForeignKey('game.id
> <http://game.id>'), primary_key=True, nullable=False),
> Column('language_id', String(length=255), ForeignKey('language.id
> <http://language.id>'), primary_key=True, nullable=False),
> Column('name', String(length=255), nullable=False, unique=True))game_table
> = Table('game',Base.metadata,Column('id', UUID(as_uuid=True),
> primary_key=True, nullable=False,
> default=sqlalchemy.text('uuid_generate_v4()'),
> server_default=sqlalchemy.text('uuid_generate_v4()')),
> Column('join_code', String(length=8), nullable=False, unique=True))*
> I also have a ROUND table which is effectively just an extension of a
> game, but with a sequence number. Basically - if I have a collection of
> games that I want users to play - they play them as "Rounds" in a given
> order.
>
>
>
>
>
>
>
>
> *round_table = Table('round',Base.metadata,Column('id',
> UUID(as_uuid=True), primary_key=True, nullable=False,
> default=sqlalchemy.text('uuid_generate_v4()'),
> server_default=sqlalchemy.text('uuid_generate_v4()')),
> Column('tournament_id', UUID(as_uuid=True), ForeignKey('tournament.id
> <http://tournament.id>'), nullable=False),Column('game_id',
> UUID(as_uuid=True), ForeignKey('game.id <http://game.id>'),
> nullable=False),Column('sequence', Integer, nullable=False,
> default=0),UniqueConstraint('tournament_id', 'sequence'))*
> So my GAME table has an "id" field, and my round table has a game_id
> field, and an id field.
>
> When I try to join(game_table, round_table) - I get the following error:
> *Implicitly combining column round.id <http://round.id> with column
> game.id <http://game.id> under attribute 'id'.  Please configure one or
> more attributes for these same-named columns explicitly.*
>
>
> join() does not emit that error; that error occurs when you create a
> mapping to the join using either mapper() or a declarative base, since
> "attribute" refers to ORM mapped attribute.Mapping to joins with
> same-named columns is demonstrated at
> https://docs.sqlalchemy.org/en/13/orm/nonstandard_mappings.html#mapping-a-class-against-multiple-tables
> .
>
>
>
>
>
> So - because there's multiple points of entry into these tables: Editing a
> Game is a direct thing, editing a round (which also edits its underlying
> game) is also a direct thing.
>
> How do I craft the JOIN so I don't step on toes?
>
> I tried doing the following:
> *round_join = join(round_table, game_join.alias('game'))*
> That didn't help, because game_id still squashes between game_language and
> round.
>
> Is there a smarter way to go about this?
>
>
> --
> 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 view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/CAHmCLHq8euMKns8i%2BGseAQJE%2Bb3d491%2B8Bk_yMkp3dC3Ae5T%2Bg%40mail.gmail.com
> <https://groups.google.com/d/msgid/sqlalchemy/CAHmCLHq8euMKns8i%2BGseAQJE%2Bb3d491%2B8Bk_yMkp3dC3Ae5T%2Bg%40mail.gmail.com?utm_medium=email&utm_source

[sqlalchemy] Explicit Column Combination vs Implicit?

[Michael P. McDonnell]

Hey team -

I've went through the docs, and likely because I'm a bit of a hack - I'm
finding it hard to find my answers (because I'm not sure what I'm
specifically looking to "do" other than "make it work")

So I have the following situation:
I have a GAME table (with a corresponding LANGUAGE table) - because my
users might play games.












*game_language_table = Table('game_language',Base.metadata,
Column('game_id', UUID(as_uuid=True), ForeignKey('game.id
'), primary_key=True, nullable=False),
Column('language_id', String(length=255), ForeignKey('language.id
'), primary_key=True, nullable=False),
Column('name', String(length=255), nullable=False, unique=True))game_table
= Table('game',Base.metadata,Column('id', UUID(as_uuid=True),
primary_key=True, nullable=False,
default=sqlalchemy.text('uuid_generate_v4()'),
server_default=sqlalchemy.text('uuid_generate_v4()')),
Column('join_code', String(length=8), nullable=False, unique=True))*
I also have a ROUND table which is effectively just an extension of a game,
but with a sequence number. Basically - if I have a collection of games
that I want users to play - they play them as "Rounds" in a given order.








*round_table = Table('round',Base.metadata,Column('id',
UUID(as_uuid=True), primary_key=True, nullable=False,
default=sqlalchemy.text('uuid_generate_v4()'),
server_default=sqlalchemy.text('uuid_generate_v4()')),
Column('tournament_id', UUID(as_uuid=True), ForeignKey('tournament.id
'), nullable=False),Column('game_id',
UUID(as_uuid=True), ForeignKey('game.id '),
nullable=False),Column('sequence', Integer, nullable=False,
default=0),UniqueConstraint('tournament_id', 'sequence'))*
So my GAME table has an "id" field, and my round table has a game_id field,
and an id field.

When I try to join(game_table, round_table) - I get the following error:
*Implicitly combining column round.id  with column game.id
 under attribute 'id'.  Please configure one or more
attributes for these same-named columns explicitly.*

So - because there's multiple points of entry into these tables: Editing a
Game is a direct thing, editing a round (which also edits its underlying
game) is also a direct thing.

How do I craft the JOIN so I don't step on toes?

I tried doing the following:
*round_join = join(round_table, game_join.alias('game'))*
That didn't help, because game_id still squashes between game_language and
round.

Is there a smarter way to go about this?

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/CAHmCLHq8euMKns8i%2BGseAQJE%2Bb3d491%2B8Bk_yMkp3dC3Ae5T%2Bg%40mail.gmail.com.

Re: [sqlalchemy] Can't filter on Join?

[Michael P. McDonnell]

I'm sorry this took me so long - but thank you. This is actually much
cleaner syntax and I like it.

Thanks for all you do!

On Mon, Aug 12, 2019 at 8:44 PM Mike Bayer  wrote:

>
>
> On Mon, Aug 12, 2019, at 5:49 PM, Michael P. McDonnell wrote:
>
> Hey team -
>
> I'm trying to figure out how to basically rewrite this:
> SELECT
> count(task.id)
> FROM task
> JOIN round on task.game_id = round.game_id
> JOIN tournament ON round.tournament_id = tournament.id
> WHERE tournament.id = '626aeaa7-783b-415c-85f9-5222d9c95973';
>
> As this:
> total_tasks = column_property(
> select([func.count(Task.id)])
> .outerjoin(Round, Task.game_id == Round.game_id)
> .filter(round.tournament_id == tournament_id)
> .correlate_except(Task))
>
> I keep getting the following error:
> AttributeError: 'Join' object has no attribute 'filter'
>
> Which I *know* tells me that a join object has no method called "filter",
> but there's also no "where", "filter_by" etc...
>
> What painfully obvious thing am I missing?
>
>
> hi -
>
> you're doing a classic mistake-ish thing that will be less possible in 1.4
> and the longer term plan is by 2.0 all confusion will be eliminated, which
> is that you are using the Core select() construct in the way you would use
> the ORM Query object, when in fact these are two totally different objects
> that work very differently.
>
> To do a join (and filter) with Core select():
>
> from sqlalchemy import outerjoin, select
> j = outerjoin(Task, Round, Task.game_id== Round.game_id)
> stmt =
> select([func.count(..)]).select_from(j).where(...).correlate_except(...)
>
> basically:
>
> 1. do not call select.join() or select.outerjoin(). These methods are 100%
> useless, unfortunately, and are gone in 1.4.  use
> select.select_from(join(A, B, onclause)).
>
> 2. select() doesn't have filter(), it has where().
>
>
> For now, as we are still in SQLAlchemy medieval times, background on how
> to use select() is at
> https://docs.sqlalchemy.org/en/13/core/tutorial.html#selecting
>
>
>
> --
> 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 view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/ad3b421d-bf9f-43bd-bd1a-061136572200%40googlegroups.com
> <https://groups.google.com/d/msgid/sqlalchemy/ad3b421d-bf9f-43bd-bd1a-061136572200%40googlegroups.com?utm_medium=email&utm_source=footer>
> .
>
>
> --
> 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 view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/d465c941-6441-46d3-8bbe-315d857ff7bb%40www.fastmail.com
> <https://groups.google.com/d/msgid/sqlalchemy/d465c941-6441-46d3-8bbe-315d857ff7bb%40www.fastmail.com?utm_medium=email&utm_source=footer>
> .
>

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/CAHmCLHreC7-U2p%2BTh%2B4m3%3DugLKwys%3DTG8B6_M6raDtfYSdMWiQ%40mail.gmail.com.

[sqlalchemy] Can't filter on Join?

[Michael P. McDonnell]

Hey team -

I'm trying to figure out how to basically rewrite this:
SELECT
count(task.id) 
FROM task
JOIN round on task.game_id = round.game_id
JOIN tournament ON round.tournament_id = tournament.id
WHERE tournament.id = '626aeaa7-783b-415c-85f9-5222d9c95973';

As this:
total_tasks = column_property(
select([func.count(Task.id)])
.outerjoin(Round, Task.game_id == Round.game_id)
.filter(round.tournament_id == tournament_id)
.correlate_except(Task))

I keep getting the following error:
AttributeError: 'Join' object has no attribute 'filter'

Which I *know* tells me that a join object has no method called "filter", 
but there's also no "where", "filter_by" etc...

What painfully obvious thing am I missing?

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/ad3b421d-bf9f-43bd-bd1a-061136572200%40googlegroups.com.

Re: [sqlalchemy] Percentile calculation

[Michael]

Thanks a lot! Indeed SQLite seems to be the problem and the code works fine 
with PostgreSQL. 

Unfortunately, a full fledged database server is not an option. Therefore I 
probably have to work around the problem in Python. 

Is there an easy way to obtain a list of objects generated by multiple 
group_by conditions? Then I could calculate the percentiles e.g. in numpy. 

Am Montag, 5. August 2019 18:16:38 UTC+2 schrieb Mike Bayer:
>
> does SQLite support WITHIN GROUP ?Try it out on PostgreSQL, I think 
> this is just not syntax SQLite supports.
>
>
> On Mon, Aug 5, 2019, at 10:38 AM, Michael wrote:
>
> Hi!
>
> I'm really having a great time with sqlalchemy so far! 
>
> Currently I'm trying to apply a percentile function on a ORM schema with 
> sqlite3. Average, min, max etc are working fine, but i cannot compute the 
> median or any other percentile using 'percentile_cont'. 
>
> A minimal example and the corresponding error messages can be found below.
>
>
> Any help would be greatly appreciated!
>
> Best
> Michael
>
>
> import sqlalchemy
> sqlalchemy.__version__ # '1.3.5'
>
>
>
> from sqlalchemy import create_engine
> engine = create_engine('sqlite:///:memory:', echo=False)
>
>
> from sqlalchemy.ext.declarative import declarative_base
> Base = declarative_base()
>
>
> from sqlalchemy import Column, Float, String, Integer
> class User(Base):
> __tablename__ = 'users'
>
>
> id = Column(Integer, primary_key=True)
> name = Column(String)
> role = Column(String)
> salary = Column(Float)
>
>
> def __repr__(self):
> return "" % (
> self.name, self.fullname, self.nickname)
>
>
>
>
>
>
> Base.metadata.create_all(engine)
>
>
> u1 = User(name='u1', role='Manager', salary = 100)
> u2 = User(name='u2', role='Manager', salary = 110)
> u3 = User(name='u3', role='Employee', salary = 1000)
> u4 = User(name='u4', role='Employee', salary = 200)
>
>
>
>
> from sqlalchemy.orm import sessionmaker
> Session = sessionmaker(bind=engine)
> session = Session()
>
>
>
>
> session.add(u1)
> session.add(u2)
> session.add(u3)
> session.add(u4)
>
>
>
>
> from sqlalchemy.sql import func
> from sqlalchemy import within_group
> 
> q1 = session.query(func.avg(User.salary).label('average')).group_by(User.
> role)
> print(q1.all())
> q2 = session.query(func.percentile_disc(0.5).within_group(User.salary)).
> group_by(User.role)
> print(q2)
>
>
> print(q2.all()) # ERROR
>
>
> ---
> OperationalError  Traceback (most recent call last)
> /usr/lib64/python3.7/site-packages/sqlalchemy/engine/base.py in 
> _execute_context(self, dialect, constructor, statement, parameters, *args)
>1243 self.dialect.do_execute(
> -> 1244 cursor, statement, parameters, context
>1245 )
>
> /usr/lib64/python3.7/site-packages/sqlalchemy/engine/default.py in 
> do_execute(self, cursor, statement, parameters, context)
> 549 def do_execute(self, cursor, statement, parameters, context=None):
> --> 550 cursor.execute(statement, parameters)
> 551 
>
> OperationalError: near "(": syntax error
>
> The above exception was the direct cause of the following exception:
>
> OperationalError  Traceback (most recent call last)
>  in 
> > 1 print(q2.all()) # ERROR
>
> /usr/lib64/python3.7/site-packages/sqlalchemy/orm/query.py in all(self)
>3166 
>3167 """
> -> 3168 return list(self)
>3169 
>3170 @_generative(_no_clauseelement_condition)
>
> /usr/lib64/python3.7/site-packages/sqlalchemy/orm/query.py in __iter__(self)
>3322 if self._autoflush and not self._populate_existing:
>3323 self.session._autoflush()
> -> 3324 return self._execute_and_instances(context)
>3325 
>3326 def __str__(self):
>
> /usr/lib64/python3.7/site-packages/sqlalchemy/orm/query.py in 
> _execute_and_instances(self, querycontext)
>3347 )
>3348 
> -> 3349 result = conn.execute(querycontext.statement, self._params)
>3350 return loading.instances(querycontext.query, result, 
> querycontext)
>3351 
>
> /usr/lib64/python3.7/site-packages/sqlalchemy/engine/base.py in execute(self, 
> object_, *multip

[sqlalchemy] Percentile calculation

[Michael]

Hi!

I'm really having a great time with sqlalchemy so far! 

Currently I'm trying to apply a percentile function on a ORM schema with 
sqlite3. Average, min, max etc are working fine, but i cannot compute the 
median or any other percentile using 'percentile_cont'. 

A minimal example and the corresponding error messages can be found below.


Any help would be greatly appreciated!

Best
Michael

import sqlalchemy
sqlalchemy.__version__ # '1.3.5'



from sqlalchemy import create_engine
engine = create_engine('sqlite:///:memory:', echo=False)


from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()


from sqlalchemy import Column, Float, String, Integer
class User(Base):
__tablename__ = 'users'


id = Column(Integer, primary_key=True)
name = Column(String)
role = Column(String)
salary = Column(Float)


def __repr__(self):
return "" % (
self.name, self.fullname, self.nickname)






Base.metadata.create_all(engine)


u1 = User(name='u1', role='Manager', salary = 100)
u2 = User(name='u2', role='Manager', salary = 110)
u3 = User(name='u3', role='Employee', salary = 1000)
u4 = User(name='u4', role='Employee', salary = 200)




from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine)
session = Session()




session.add(u1)
session.add(u2)
session.add(u3)
session.add(u4)




from sqlalchemy.sql import func
from sqlalchemy import within_group

q1 = session.query(func.avg(User.salary).label('average')).group_by(User.
role)
print(q1.all())
q2 = session.query(func.percentile_disc(0.5).within_group(User.salary)).
group_by(User.role)
print(q2)


print(q2.all()) # ERROR


---
OperationalError  Traceback (most recent call last)
/usr/lib64/python3.7/site-packages/sqlalchemy/engine/base.py in 
_execute_context(self, dialect, constructor, statement, parameters, *args)
   1243 self.dialect.do_execute(
-> 1244 cursor, statement, parameters, context
   1245 )

/usr/lib64/python3.7/site-packages/sqlalchemy/engine/default.py in 
do_execute(self, cursor, statement, parameters, context)
549 def do_execute(self, cursor, statement, parameters, context=None):
--> 550 cursor.execute(statement, parameters)
551 

OperationalError: near "(": syntax error

The above exception was the direct cause of the following exception:

OperationalError  Traceback (most recent call last)
 in 
> 1 print(q2.all()) # ERROR

/usr/lib64/python3.7/site-packages/sqlalchemy/orm/query.py in all(self)
   3166 
   3167 """
-> 3168 return list(self)
   3169 
   3170 @_generative(_no_clauseelement_condition)

/usr/lib64/python3.7/site-packages/sqlalchemy/orm/query.py in __iter__(self)
   3322 if self._autoflush and not self._populate_existing:
   3323 self.session._autoflush()
-> 3324 return self._execute_and_instances(context)
   3325 
   3326 def __str__(self):

/usr/lib64/python3.7/site-packages/sqlalchemy/orm/query.py in 
_execute_and_instances(self, querycontext)
   3347 )
   3348 
-> 3349 result = conn.execute(querycontext.statement, self._params)
   3350 return loading.instances(querycontext.query, result, 
querycontext)
   3351 

/usr/lib64/python3.7/site-packages/sqlalchemy/engine/base.py in execute(self, 
object_, *multiparams, **params)
986 raise exc.ObjectNotExecutableError(object_)
987 else:
--> 988 return meth(self, multiparams, params)
989 
990 def _execute_function(self, func, multiparams, params):

/usr/lib64/python3.7/site-packages/sqlalchemy/sql/elements.py in 
_execute_on_connection(self, connection, multiparams, params)
285 def _execute_on_connection(self, connection, multiparams, params):
286 if self.supports_execution:
--> 287 return connection._execute_clauseelement(self, multiparams, 
params)
288 else:
289 raise exc.ObjectNotExecutableError(self)

/usr/lib64/python3.7/site-packages/sqlalchemy/engine/base.py in 
_execute_clauseelement(self, elem, multiparams, params)
   1105 distilled_params,
   1106 compiled_sql,
-> 1107 distilled_params,
   1108 )
   1109 if self._has_events or self.engine._has_events:

/usr/lib64/python3.7/site-packages/sqlalchemy/engine/base.py in 
_execute_context(self, dialect, constructor, statement, parameters, *args)
   1246 except BaseException as e:
   1247 self._handle_dbapi_exception(
-> 1248 e, statement, 

Re: [sqlalchemy] Thank you for SQLAlchemy

[Michael P. McDonnell]

I agree!

I ~started~ python in March this year after a 10 year hiatus from
programming anything outside of powershell/perl/bash.

This is by and far the best library in the python community - especially
given the amazing support on this email group.

So I second the thanks.

On Sat, Aug 3, 2019, 10:00 AM Aaron Krohn  wrote:

> I just wanted to let you all know that I really appreciate the effort put
> into the SQLAlchemy library. Its interfaces are extremely simple and
> intuitive, and this has been done by using the full extent of Python
> language features. It really is a pleasure to work with, and makes writing
> database code fun. Thanks again, and know that you all are creating on the
> best Python project I've worked with. Cheers!
>
> --
> 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 view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/3b25d4b8-8329-4246-a393-565f71e84ed2%40googlegroups.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 view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/CAHmCLHpR0EzZs%3Dy2sZCHsazqGO6mVZDDio0rqEXcFfz_fdQfiQ%40mail.gmail.com.

[sqlalchemy] Re: query.all() returning 1 when actual query returns 2?

[Michael P. McDonnell]

Holy buckets I found it.

I *had* this:
display_name = Column(
'display_name', String(length=255), nullable=False, primaryKey=True)
That little primaryKey bit right there, not supposed to be there.

On Fri, Aug 2, 2019 at 2:16 PM Michael P. McDonnell 
wrote:

> Oh! I should add - when do a len() on the results of query.all() - I Get
> *1* until I change the display_name
>
> On Fri, Aug 2, 2019 at 2:10 PM Michael P. McDonnell 
> wrote:
>
>> So when I generate the query with SQL Alchemy (using the ORM) I get the
>> following:
>>
>> SELECT game_leaderboard.game_id, game_leaderboard.person_id,
>> game_leaderboard.display_name, game_leaderboard.total_tasks,
>> game_leaderboard.tasks_completed, game_leaderboard.duration
>> FROM game_leaderboard
>> WHERE game_leaderboard.game_id = %(game_id_1)s
>> Data: game_id='5184b7eb-b687-4471-8aa9-7b6715b7a9aa'
>>
>> I get the following result:
>> [
>> {
>> "total_tasks": 2,
>> "display_name": "Auto Test ",
>> "tasks_completed": 2,
>> "person_id": "96736b38-b854-403c-90d8-0701c23efe55",
>> "duration": "0:08:00",
>> "game_id": "5184b7eb-b687-4471-8aa9-7b6715b7a9aa"
>> }
>> ]
>>
>> However when I run this same query (generated) in psql:
>> odyssey=# SELECT game_leaderboard.game_id, game_leaderboard.person_id,
>> game_leaderboard.display_name, game_leaderboard.total_tasks,
>> game_leaderboard.tasks_completed, game_leaderboard.duration
>> FROM game_leaderboard
>> WHERE game_leaderboard.game_id = '5184b7eb-b687-4471-8aa9-7b6715b7a9aa';
>>game_id|  person_id
>> | display_name | total_tasks | tasks_completed | duration
>>
>> --+--+--+-+-+--
>>  5184b7eb-b687-4471-8aa9-7b6715b7a9aa |
>> 96736b38-b854-403c-90d8-0701c23efe55 | Auto Test|   2 |
>>   2 | 00:08:00
>>  5184b7eb-b687-4471-8aa9-7b6715b7a9aa |
>> ac370ba6-0ea8-4068-8cac-0337b6807481 | Auto Test|   2 |
>>   2 | 00:16:00
>>
>> I have determined however - if I change the "display_name" to something
>> different between the two people, I suddenly get 2 results from query.all()
>>
>> This is doing a query on a VIEW.
>>
>> Any ideas on where I can start looking at this?
>>
>>

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/CAHmCLHpmHN2NUtCnoJ65tq5DipH1hp_LgyWtDynZnztS2StHsQ%40mail.gmail.com.

[sqlalchemy] Re: query.all() returning 1 when actual query returns 2?

[Michael P. McDonnell]

Oh! I should add - when do a len() on the results of query.all() - I Get *1*
until I change the display_name

On Fri, Aug 2, 2019 at 2:10 PM Michael P. McDonnell 
wrote:

> So when I generate the query with SQL Alchemy (using the ORM) I get the
> following:
>
> SELECT game_leaderboard.game_id, game_leaderboard.person_id,
> game_leaderboard.display_name, game_leaderboard.total_tasks,
> game_leaderboard.tasks_completed, game_leaderboard.duration
> FROM game_leaderboard
> WHERE game_leaderboard.game_id = %(game_id_1)s
> Data: game_id='5184b7eb-b687-4471-8aa9-7b6715b7a9aa'
>
> I get the following result:
> [
> {
> "total_tasks": 2,
> "display_name": "Auto Test ",
> "tasks_completed": 2,
> "person_id": "96736b38-b854-403c-90d8-0701c23efe55",
> "duration": "0:08:00",
> "game_id": "5184b7eb-b687-4471-8aa9-7b6715b7a9aa"
> }
> ]
>
> However when I run this same query (generated) in psql:
> odyssey=# SELECT game_leaderboard.game_id, game_leaderboard.person_id,
> game_leaderboard.display_name, game_leaderboard.total_tasks,
> game_leaderboard.tasks_completed, game_leaderboard.duration
> FROM game_leaderboard
> WHERE game_leaderboard.game_id = '5184b7eb-b687-4471-8aa9-7b6715b7a9aa';
>game_id|  person_id
>   | display_name | total_tasks | tasks_completed | duration
>
> --+--+--+-+-+--
>  5184b7eb-b687-4471-8aa9-7b6715b7a9aa |
> 96736b38-b854-403c-90d8-0701c23efe55 | Auto Test|   2 |
>   2 | 00:08:00
>  5184b7eb-b687-4471-8aa9-7b6715b7a9aa |
> ac370ba6-0ea8-4068-8cac-0337b6807481 | Auto Test|   2 |
>   2 | 00:16:00
>
> I have determined however - if I change the "display_name" to something
> different between the two people, I suddenly get 2 results from query.all()
>
> This is doing a query on a VIEW.
>
> Any ideas on where I can start looking at this?
>
>

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/CAHmCLHpxjezSeMHaOh-aZogTGq5DvvSkx1vzkwxJyEv%3DZo%2BXuw%40mail.gmail.com.

[sqlalchemy] query.all() returning 1 when actual query returns 2?

[Michael P. McDonnell]

So when I generate the query with SQL Alchemy (using the ORM) I get the
following:

SELECT game_leaderboard.game_id, game_leaderboard.person_id,
game_leaderboard.display_name, game_leaderboard.total_tasks,
game_leaderboard.tasks_completed, game_leaderboard.duration
FROM game_leaderboard
WHERE game_leaderboard.game_id = %(game_id_1)s
Data: game_id='5184b7eb-b687-4471-8aa9-7b6715b7a9aa'

I get the following result:
[
{
"total_tasks": 2,
"display_name": "Auto Test ",
"tasks_completed": 2,
"person_id": "96736b38-b854-403c-90d8-0701c23efe55",
"duration": "0:08:00",
"game_id": "5184b7eb-b687-4471-8aa9-7b6715b7a9aa"
}
]

However when I run this same query (generated) in psql:
odyssey=# SELECT game_leaderboard.game_id, game_leaderboard.person_id,
game_leaderboard.display_name, game_leaderboard.total_tasks,
game_leaderboard.tasks_completed, game_leaderboard.duration
FROM game_leaderboard
WHERE game_leaderboard.game_id = '5184b7eb-b687-4471-8aa9-7b6715b7a9aa';
   game_id|  person_id
  | display_name | total_tasks | tasks_completed | duration
--+--+--+-+-+--
 5184b7eb-b687-4471-8aa9-7b6715b7a9aa |
96736b38-b854-403c-90d8-0701c23efe55 | Auto Test|   2 |
  2 | 00:08:00
 5184b7eb-b687-4471-8aa9-7b6715b7a9aa |
ac370ba6-0ea8-4068-8cac-0337b6807481 | Auto Test|   2 |
  2 | 00:16:00

I have determined however - if I change the "display_name" to something
different between the two people, I suddenly get 2 results from query.all()

This is doing a query on a VIEW.

Any ideas on where I can start looking at this?

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/CAHmCLHotQVWsx7X-eN%2BV%3D-nZu5P72b2oLJONmcHOjBNa0gO_FA%40mail.gmail.com.

Re: [sqlalchemy] Wrapper Table needs Joins on SELECT, not on Insert.

[Michael P. McDonnell]

Awesome! Thanks! I've been trying to figure out how to do a reliable object
-> json conversion (including nested relationships). I'm hoping this is it.

On Sat, Jul 13, 2019, 9:53 AM Mike Bayer  wrote:

>
>
> On Fri, Jul 12, 2019, at 6:49 PM, Michael P. McDonnell wrote:
>
> Hey Mike -
>
> First off - thanks for the association proxy idea - I like that, and even
> if heavy handed -  specifically like the idea that its a *view* in to
> column as opposed to a manipulable column.
> The next question is - how do I iterate over those association proxy
> fields within the object?
> So if I'm to do a marshal to json - I'd like to say "my task has a name,
> description, instructions, etc.."
>
>
> the association proxy will show up in the mapping under
> all_orm_descriptors, e.g.
>
> from sqlalchemy import inspect
>
> d = {
>   k getattr(some_object, k) for k in
> inspect(some_object).mapper.all_orm_descriptors
> }
>
>
> https://docs.sqlalchemy.org/en/13/orm/mapping_api.html?highlight=all_orm_descriptors#sqlalchemy.orm.mapper.Mapper.all_orm_descriptors
>
>
> On Fri, Jul 12, 2019 at 11:19 AM Mike Bayer 
> wrote:
>
>
>
>
> On Fri, Jul 12, 2019, at 11:20 AM, Michael P. McDonnell wrote:
>
> Hey Team -
>
> So I'm working on a relatively fun hierarchy that allows me to relate
> tasks to games.
> So I have 2 tables:
>
> # A Generic Task Definition - not related to anything
> class Task_Definition(Base):
> def __repr__(self):
> return (
> " + "name='" + self.name + "'>")
> __table__ = task_definition_table # has name, id, etc...
>
> # and a Task Table - specific to a game:
> class Task(Base):
> def __repr__(self):
> return (
> "")
> __table__ = task_table # has game_id, start_dttm, end_dttm, etc...
>
> So originally I had my task as a
> __table__ = join(task_definition_table, task_table)
>
> That allowed me to select a task, and see all of the task_definition
> properties as one "Object"
> But the problem is: when I created a task - it wanted to create a new
> task_definition at the same time, which is not what I wanted - given that
> task_definitions are a generic that can be used anytime.
>
> So then I created a task like this:
> class Task(Base):
> def __repr__(self):
> return (
> "")
> __table__ = task_table
> task_definition_id = column_property(
> task_definition_table.c.id,
> task_definition_language_table.c.task_definition_id,
> task_table.c.task_definition_id)
> name = column_property(task_definition_language_table.c.name)
> description =
> column_property(task_definition_language_table.c.description)
> instructions = column_property(
> task_definition_language_table.c.instructions)
>
> That allowed me to insert properly - but then my selects were coming back
> with tons of duplicate rows.
>
> When I played with the query - it was because it was doing a
> SELECT * from task, task_definition
> as opposed to a
> select * from task JOIN task_definition...
>
> Is there an easy way to force a join on select, and then a direct table
> communication on insert?
>
>
> So if you wanted to use column_property()  like that, you would do it with
> a correlated select, as seen in the example at
> https://docs.sqlalchemy.org/en/13/orm/mapped_sql_expr.html#using-column-property.
> <https://docs.sqlalchemy.org/en/13/orm/mapped_sql_expr.html#using-column-property>
> However, you have a whole bunch of them here and correlated selects aren't
> efficient from a SQL perspective.
>
> From the ORM point of view, exactly what you want is available using
> relationship() with lazy='joined':
>
> class Task(Base):
># ...
>
>description = relationship("TaskDefinition", lazy="joined")
>
> But you don't want to say "mytask.description.name", OK.   The data is in
> Python now so you can use all of Python's capabilities, in this case it
> would be to use descriptors:
>
> class Task(Base):
> # ...
>
> @property
> def description(self):
> return self._description.description
>
> @description.setter# optional setter
> def description(self, value):
> self._description.description = value
>
> # etc ...
>
>_description = relationship("TaskDefinition", lazy="joined")
>
>
> The above pattern is also available using the association proxy, which
> might be a little bit heavy-handed here, h

Re: [sqlalchemy] Wrapper Table needs Joins on SELECT, not on Insert.

[Michael P. McDonnell]

And for clarity's sake - I mean without knowing specifically ahead of time
its a "task" object.

On Fri, Jul 12, 2019 at 5:49 PM Michael P. McDonnell 
wrote:

> Hey Mike -
>
> First off - thanks for the association proxy idea - I like that, and even
> if heavy handed -  specifically like the idea that its a *view* in to
> column as opposed to a manipulable column.
> The next question is - how do I iterate over those association proxy
> fields within the object?
> So if I'm to do a marshal to json - I'd like to say "my task has a name,
> description, instructions, etc.."
>
> On Fri, Jul 12, 2019 at 11:19 AM Mike Bayer 
> wrote:
>
>>
>>
>> On Fri, Jul 12, 2019, at 11:20 AM, Michael P. McDonnell wrote:
>>
>> Hey Team -
>>
>> So I'm working on a relatively fun hierarchy that allows me to relate
>> tasks to games.
>> So I have 2 tables:
>>
>> # A Generic Task Definition - not related to anything
>> class Task_Definition(Base):
>> def __repr__(self):
>> return (
>> "> + "name='" + self.name + "'>")
>> __table__ = task_definition_table # has name, id, etc...
>>
>> # and a Task Table - specific to a game:
>> class Task(Base):
>> def __repr__(self):
>> return (
>> "")
>> __table__ = task_table # has game_id, start_dttm, end_dttm, etc...
>>
>> So originally I had my task as a
>> __table__ = join(task_definition_table, task_table)
>>
>> That allowed me to select a task, and see all of the task_definition
>> properties as one "Object"
>> But the problem is: when I created a task - it wanted to create a new
>> task_definition at the same time, which is not what I wanted - given that
>> task_definitions are a generic that can be used anytime.
>>
>> So then I created a task like this:
>> class Task(Base):
>> def __repr__(self):
>> return (
>> "")
>> __table__ = task_table
>> task_definition_id = column_property(
>> task_definition_table.c.id,
>> task_definition_language_table.c.task_definition_id,
>> task_table.c.task_definition_id)
>> name = column_property(task_definition_language_table.c.name)
>> description =
>> column_property(task_definition_language_table.c.description)
>> instructions = column_property(
>> task_definition_language_table.c.instructions)
>>
>> That allowed me to insert properly - but then my selects were coming back
>> with tons of duplicate rows.
>>
>> When I played with the query - it was because it was doing a
>> SELECT * from task, task_definition
>> as opposed to a
>> select * from task JOIN task_definition...
>>
>> Is there an easy way to force a join on select, and then a direct table
>> communication on insert?
>>
>>
>> So if you wanted to use column_property()  like that, you would do it
>> with a correlated select, as seen in the example at
>> https://docs.sqlalchemy.org/en/13/orm/mapped_sql_expr.html#using-column-property.
>> <https://docs.sqlalchemy.org/en/13/orm/mapped_sql_expr.html#using-column-property>
>> However, you have a whole bunch of them here and correlated selects aren't
>> efficient from a SQL perspective.
>>
>> From the ORM point of view, exactly what you want is available using
>> relationship() with lazy='joined':
>>
>> class Task(Base):
>># ...
>>
>>description = relationship("TaskDefinition", lazy="joined")
>>
>> But you don't want to say "mytask.description.name", OK.   The data is
>> in Python now so you can use all of Python's capabilities, in this case it
>> would be to use descriptors:
>>
>> class Task(Base):
>> # ...
>>
>> @property
>> def description(self):
>> return self._description.description
>>
>> @description.setter# optional setter
>> def description(self, value):
>> self._description.description = value
>>
>> # etc ...
>>
>>_description = relationship("TaskDefinition", lazy="joined")
>>
>>
>> The above pattern is also available using the association proxy, which
>> might be a little bit heavy-handed here, however is less code up front,
>> association proxy is at
>> https://docs.sqlalchemy.org/en/13/orm/extensions/associationproxy.html#:
>&

Re: [sqlalchemy] Wrapper Table needs Joins on SELECT, not on Insert.

[Michael P. McDonnell]

Hey Mike -

First off - thanks for the association proxy idea - I like that, and even
if heavy handed -  specifically like the idea that its a *view* in to
column as opposed to a manipulable column.
The next question is - how do I iterate over those association proxy fields
within the object?
So if I'm to do a marshal to json - I'd like to say "my task has a name,
description, instructions, etc.."

On Fri, Jul 12, 2019 at 11:19 AM Mike Bayer 
wrote:

>
>
> On Fri, Jul 12, 2019, at 11:20 AM, Michael P. McDonnell wrote:
>
> Hey Team -
>
> So I'm working on a relatively fun hierarchy that allows me to relate
> tasks to games.
> So I have 2 tables:
>
> # A Generic Task Definition - not related to anything
> class Task_Definition(Base):
> def __repr__(self):
> return (
> " + "name='" + self.name + "'>")
> __table__ = task_definition_table # has name, id, etc...
>
> # and a Task Table - specific to a game:
> class Task(Base):
> def __repr__(self):
> return (
> "")
> __table__ = task_table # has game_id, start_dttm, end_dttm, etc...
>
> So originally I had my task as a
> __table__ = join(task_definition_table, task_table)
>
> That allowed me to select a task, and see all of the task_definition
> properties as one "Object"
> But the problem is: when I created a task - it wanted to create a new
> task_definition at the same time, which is not what I wanted - given that
> task_definitions are a generic that can be used anytime.
>
> So then I created a task like this:
> class Task(Base):
> def __repr__(self):
> return (
> "")
> __table__ = task_table
> task_definition_id = column_property(
> task_definition_table.c.id,
> task_definition_language_table.c.task_definition_id,
> task_table.c.task_definition_id)
> name = column_property(task_definition_language_table.c.name)
> description =
> column_property(task_definition_language_table.c.description)
> instructions = column_property(
> task_definition_language_table.c.instructions)
>
> That allowed me to insert properly - but then my selects were coming back
> with tons of duplicate rows.
>
> When I played with the query - it was because it was doing a
> SELECT * from task, task_definition
> as opposed to a
> select * from task JOIN task_definition...
>
> Is there an easy way to force a join on select, and then a direct table
> communication on insert?
>
>
> So if you wanted to use column_property()  like that, you would do it with
> a correlated select, as seen in the example at
> https://docs.sqlalchemy.org/en/13/orm/mapped_sql_expr.html#using-column-property.
> <https://docs.sqlalchemy.org/en/13/orm/mapped_sql_expr.html#using-column-property>
> However, you have a whole bunch of them here and correlated selects aren't
> efficient from a SQL perspective.
>
> From the ORM point of view, exactly what you want is available using
> relationship() with lazy='joined':
>
> class Task(Base):
># ...
>
>description = relationship("TaskDefinition", lazy="joined")
>
> But you don't want to say "mytask.description.name", OK.   The data is in
> Python now so you can use all of Python's capabilities, in this case it
> would be to use descriptors:
>
> class Task(Base):
> # ...
>
> @property
> def description(self):
> return self._description.description
>
> @description.setter# optional setter
> def description(self, value):
> self._description.description = value
>
> # etc ...
>
>_description = relationship("TaskDefinition", lazy="joined")
>
>
> The above pattern is also available using the association proxy, which
> might be a little bit heavy-handed here, however is less code up front,
> association proxy is at
> https://docs.sqlalchemy.org/en/13/orm/extensions/associationproxy.html#:
>
> from sqlalchemy.ext.associationproxy import association_proxy
>
> class Task(Base):
> # ...
> description = association_proxy("_description", "description")
>
> # etc ...
>
>_description = relationship("TaskDefinition", lazy="joined")
>
>
> hope this helps!
>
>
>
>
>
>
>
> -Mike
>
>
> --
> 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 ht

[sqlalchemy] Wrapper Table needs Joins on SELECT, not on Insert.

[Michael P. McDonnell]

Hey Team - 

So I'm working on a relatively fun hierarchy that allows me to relate tasks 
to games. 
So I have 2 tables:

# A Generic Task Definition - not related to anything
class Task_Definition(Base):
def __repr__(self):
return (
"")
__table__ = task_definition_table # has name, id, etc...

# and a Task Table - specific to a game:
class Task(Base):
def __repr__(self):
return (
"")
__table__ = task_table # has game_id, start_dttm, end_dttm, etc...

So originally I had my task as a 
__table__ = join(task_definition_table, task_table)

That allowed me to select a task, and see all of the task_definition 
properties as one "Object"
But the problem is: when I created a task - it wanted to create a new 
task_definition at the same time, which is not what I wanted - given that 
task_definitions are a generic that can be used anytime.

So then I created a task like this:
class Task(Base):
def __repr__(self):
return (
"")
__table__ = task_table
task_definition_id = column_property(
task_definition_table.c.id,
task_definition_language_table.c.task_definition_id,
task_table.c.task_definition_id)
name = column_property(task_definition_language_table.c.name)
description = 
column_property(task_definition_language_table.c.description)
instructions = column_property(
task_definition_language_table.c.instructions)

That allowed me to insert properly - but then my selects were coming back 
with tons of duplicate rows. 

When I played with the query - it was because it was doing a
SELECT * from task, task_definition
as opposed to a 
select * from task JOIN task_definition...

Is there an easy way to force a join on select, and then a direct table 
communication on insert?


-Mike

-- 
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/328f63b3-dda4-4b59-a85b-52846e319d53%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] load extension spatialite in windows

[Michael Lane]

I tried all combination for path with your exemple and always got the same 
error message:

conn.load_extension('c:\\mydll\\mod_spatialite')
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) OsError 0xc1 
(193)


conn = sqlite3.connect(":memory:")
conn.enable_load_extension(True)
conn.load_extension('c:\\mydll\\mod_spatialite')


If I don't add enable_load_extension, i got a "not authorized" message 
instead of the previous error.

Sorry for my ignorance but I don't understand what you tell me to do with 
cPython.

Le mercredi 10 juillet 2019 10:58:27 UTC-4, Mike Bayer a écrit :
>
> I have no information on that, this has to do with the sqlite3 module 
> included with Python:
>
> import sqlite3
>
> conn = sqlite3.connect(":memory:")
> conn.load_extension("c:\\path\\to\\dll")
>
> Use cPython resources to get help and use the above code as what you're 
> trying to get to work:
>
> https://www.python.org/community/lists/
>
>
>
>
>
> On Wed, Jul 10, 2019, at 10:39 AM, Michael Lane wrote:
>
> Try to reproduce this 
> <https://geoalchemy-2.readthedocs.io/en/latest/spatialite_tutorial.html> in 
> windows. How to do that?
>
>
>
> Even though I put the dll file in a directory defined in %PATH% or try 
> with/without full path, with/without file extension, with '//' or '\', with 
> libspatialite-4.dll or using excute with all combination mentionned 
> before...
>  dbapi_conn.execute("SELECT load_extension('mod_spatialite.dll')")
>
>
> For example i tried this kind of combinaison:
>
> def load_spatialite(dbapi_conn, connection_record):
>
> dbapi_conn.load_extension(*'C:*\\*Scripts*\\*mod_spatialite.dll'*)
>
> # dbapi_conn.load_extension('C:\\Scripts\\libspatialite-4.dll')
> # dbapi_conn.load_extension('mod_spatialite')
> # dbapi_conn.load_extension('mod_spatialite.dll')
> # dbapi_conn.execute("SELECT 
> load_extension('C:\\pyramid\\risc\\api\\Scripts\\mod_spatialite.dll')")
> # dbapi_conn.execute("SELECT load_extension('mod_spatialite.dll')")
>
> # dbapi_conn.execute("SELECT load_extension('mod_spatialite')")
>
> # dbapi_conn.load_extension('C:/Scripts\mod_spatialite.dll')
>
>
>
>
> Can make it work, always getting:
>
>
> dbapi_conn.load_extension('C:\\Scripts\\mod_spatialite.dll')
>
> sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) OsError 0xc1 (193)
> (Background on this error at: http://sqlalche.me/e/e3q8)
>
>
> Any hint will be appreciated.
>
> Thanks
>
>
> --
> 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 sqlal...@googlegroups.com .
> To post to this group, send email to sqlal...@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/31ce59e1-ac35-45ae-b584-d50a594adc70%40googlegroups.com
>  
> <https://groups.google.com/d/msgid/sqlalchemy/31ce59e1-ac35-45ae-b584-d50a594adc70%40googlegroups.com?utm_medium=email&utm_source=footer>
> .
> 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/89626ede-54f0-4353-8669-fa8ca8e38976%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] load extension spatialite in windows

[Michael Lane]

Try to reproduce this 
 in 
windows. How to do that?



Even though I put the dll file in a directory defined in %PATH% or try 
with/without full path, with/without file extension, with '//' or '\', with 
libspatialite-4.dll or using excute with all combination mentionned 
before...
 dbapi_conn.execute("SELECT load_extension('mod_spatialite.dll')")


For example i tried this kind of combinaison:

def load_spatialite(dbapi_conn, connection_record):

dbapi_conn.load_extension('C:\\Scripts\\mod_spatialite.dll')

# dbapi_conn.load_extension('C:\\Scripts\\libspatialite-4.dll')
# dbapi_conn.load_extension('mod_spatialite')
# dbapi_conn.load_extension('mod_spatialite.dll')
# dbapi_conn.execute("SELECT 
load_extension('C:\\pyramid\\risc\\api\\Scripts\\mod_spatialite.dll')")
# dbapi_conn.execute("SELECT load_extension('mod_spatialite.dll')")

# dbapi_conn.execute("SELECT load_extension('mod_spatialite')")

# dbapi_conn.load_extension('C:/Scripts\mod_spatialite.dll')




Can make it work, always getting:


dbapi_conn.load_extension('C:\\Scripts\\mod_spatialite.dll')

sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) OsError 0xc1 (193)
(Background on this error at: http://sqlalche.me/e/e3q8)


Any hint will be appreciated.

Thanks

-- 
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/31ce59e1-ac35-45ae-b584-d50a594adc70%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Re: Limiting Relationship Depths

[Michael P. McDonnell]

Awesome, thank you Jonathan.

I know I've read that at least 3-4 times, but I think I've been staring at
the screen too much these days to actually read. I'll give it a go and let
you know how it goes!

On Wed, Jun 26, 2019 at 10:58 AM Jonathan Vanasco 
wrote:

> This section of the narrative dogs will help you configure the
> relationship as you want.
>
>https://docs.sqlalchemy.org/en/13/orm/loading_relationships.html
> 
>
> In terms of API docs,
>
>  https://docs.sqlalchemy.org/en/13/orm/relationship_api.html
>
> look for the `lazy` keyword
>
> --
> 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/e4b5b44a-2764-4d89-8fa1-e529fad821d5%40googlegroups.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/CAHmCLHqocAZ_P-RiuQ4yGo_eJmSh%2BSLZ%3DNOR0EhOM3xqQBDYDw%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Limiting Relationship Depths

[Michael P. McDonnell]

Hey team -

I was wondering - is it possible to limit the depth of objects by which SA
will populate members?

Say I have a Person who is on a Team that plays a Game

class Person(Base):
  # .
  team = relationship("Team", uselist=False)
class Team(Base):
  # .
  game = relationship("Game", uselist=False)
 class Game(Base):
  # 

If I get a person - I don't want it to go all the way down and grab the
game object. Just the team one.

Is there a setting that makes that easy?

-- 
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/CAHmCLHpp36J%3DNfaraJOXbWm34903hPMuyury%3DZF5mJTPVi%2BJJg%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Re: Looking to consume query as relationship (multiple column return)

[Michael P. McDonnell]

That's really helpful! Thanks for everything Simon and Johnathan!

On Fri, May 24, 2019, 6:30 AM  wrote:

>
>
> On Thursday, May 23, 2019 at 9:17:42 PM UTC-4, Michael P. McDonnell wrote:
>>
>> Nevermind - that was dumb. I just established things as a primary key @
>> the ORM level
>>
>
> Yep!  sqlalchemy needs there to be a primary key for the identity map, but
> it doesn't need to exist at the db level!
>
>>
>>
> --
> 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/24c7f764-8e07-418e-ba16-4fab5ccbfa0f%40googlegroups.com
> <https://groups.google.com/d/msgid/sqlalchemy/24c7f764-8e07-418e-ba16-4fab5ccbfa0f%40googlegroups.com?utm_medium=email&utm_source=footer>
> .
> 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/CAHmCLHqCVsRCwkGVbpds2Di_K5L5CmTwxNxyeiadaHz9qqUZPw%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Re: Looking to consume query as relationship (multiple column return)

[Michael P. McDonnell]

Nevermind - that was dumb. I just established things as a primary key @ the
ORM level.

On Fri, May 24, 2019 at 3:13 AM Michael P. McDonnell 
wrote:

> Johnathan -
>
> I love this. I've created a view - and can easily get to it as an object
> itself.
>
> Quick question: how do I establish a "view" as a relationship then? Given
> there's no primary key...?
>
> On Thu, May 23, 2019 at 6:52 PM Jonathan Vanasco 
> wrote:
>
>> FWIW, I often make queries that map to a 'logical object' like this one
>> (e.g. "leaderboard") that have a lot of reads as a View in the database,
>> then create a separate SqlAlchemy ORM object for it and have every relation
>> to/from that marked as view_only. This has nothing to do with writing the
>> query in SqlAlchemy (you'll be writing much more complex ones sooner, and
>> with ease), and more with managing that type of info and keeping the
>> queries optimized on the database management layer. So I tied an onion to
>> my belt, which was the style at the time.
>>
>>
>>
>> On Thursday, May 23, 2019 at 3:52:00 AM UTC-4, Michael P. McDonnell wrote:
>>
>>> Hi Everybody! (Hi Dr. Nick!)
>>>
>>> I am creating a game for the work I'm doing - and while I didn't create
>>> the schema - I certainly have to program against it.
>>> What I have is a PERSON, a GAME, a PLAY and a collection of RESULT
>>>
>>> What I'd like to do is call the "leaderboard" member of my GAME class -
>>> and get the read only results from the following query:
>>> SELECT
>>> person.display_name AS display_name,
>>> COUNT(result.task_id) AS total_tasks_completed,
>>> SUM(
>>> (result.finish_dttm - result.start_dttm) - result.validation_time
>>> ) AS total_time
>>> FROM
>>> result
>>> JOIN play ON (result.play_id = play.id)
>>> JOIN person ON (play.person_id = person.id)
>>> JOIN game ON (play.game_id = game.id)
>>> WHERE game.id = 'fc8dd2e5-ecdb-47f4-811e-3c01ee9f4176'
>>> GROUP BY person.display_name
>>
>>
>>
>> --
>> 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/06bfe40f-30f9-4580-b2af-87e43ab552ee%40googlegroups.com
>> <https://groups.google.com/d/msgid/sqlalchemy/06bfe40f-30f9-4580-b2af-87e43ab552ee%40googlegroups.com?utm_medium=email&utm_source=footer>
>> .
>> 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/CAHmCLHom2rvdeYGY8XL8EmryVNktJHevbTwAz3Appd__oLtfOA%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Re: Looking to consume query as relationship (multiple column return)

[Michael P. McDonnell]

Johnathan -

I love this. I've created a view - and can easily get to it as an object
itself.

Quick question: how do I establish a "view" as a relationship then? Given
there's no primary key...?

On Thu, May 23, 2019 at 6:52 PM Jonathan Vanasco 
wrote:

> FWIW, I often make queries that map to a 'logical object' like this one
> (e.g. "leaderboard") that have a lot of reads as a View in the database,
> then create a separate SqlAlchemy ORM object for it and have every relation
> to/from that marked as view_only. This has nothing to do with writing the
> query in SqlAlchemy (you'll be writing much more complex ones sooner, and
> with ease), and more with managing that type of info and keeping the
> queries optimized on the database management layer. So I tied an onion to
> my belt, which was the style at the time.
>
>
>
> On Thursday, May 23, 2019 at 3:52:00 AM UTC-4, Michael P. McDonnell wrote:
>
>> Hi Everybody! (Hi Dr. Nick!)
>>
>> I am creating a game for the work I'm doing - and while I didn't create
>> the schema - I certainly have to program against it.
>> What I have is a PERSON, a GAME, a PLAY and a collection of RESULT
>>
>> What I'd like to do is call the "leaderboard" member of my GAME class -
>> and get the read only results from the following query:
>> SELECT
>> person.display_name AS display_name,
>> COUNT(result.task_id) AS total_tasks_completed,
>> SUM(
>> (result.finish_dttm - result.start_dttm) - result.validation_time
>> ) AS total_time
>> FROM
>> result
>> JOIN play ON (result.play_id = play.id)
>> JOIN person ON (play.person_id = person.id)
>> JOIN game ON (play.game_id = game.id)
>> WHERE game.id = 'fc8dd2e5-ecdb-47f4-811e-3c01ee9f4176'
>> GROUP BY person.display_name
>
>
>
> --
> 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/06bfe40f-30f9-4580-b2af-87e43ab552ee%40googlegroups.com
> <https://groups.google.com/d/msgid/sqlalchemy/06bfe40f-30f9-4580-b2af-87e43ab552ee%40googlegroups.com?utm_medium=email&utm_source=footer>
> .
> 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/CAHmCLHoF%3DeA9VtQOpUWvwzRs00Krh4x4Ha2xEn8pqLdNHu%3DU-A%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Looking to consume query as relationship (multiple column return)

[Michael P. McDonnell]

Hey Simon -

after all that ado - I was able to get it working exactly how I wanted.
Thank you so much for your time and patience!

-Mike

On Thu, May 23, 2019 at 12:58 PM Simon King  wrote:

> I think something like this should work:
>
> import sqlalchemy.orm as saorm
>
> class Game(Base):
> # columns etc.
> def get_leaderboard(self):
> session = saorm.object_session(self)
> query = session.query(...).filter(Game.id == self.id)
> return query.all()
>
> If you want to turn get_leaderboard into a @property, that's fine as
> well; I'm often nervous of properties that execute db queries, because
> it's too easy to write code like this:
>
> if len(game.leaderboard) < 10:
> print(game.leaderboard)
>
> ...where the query is executed multiple times. Leaving it as a method
> makes it more obvious. I think you'd be less likely to write this:
>
> if len(game.get_leaderboard()) < 10:
> print(game.get_leaderboard())
>
> ...and instead change it to:
>
> leaderboard = game.get_leaderboard()
> if len(leaderboard) < 10:
> print(leaderboard)
>
> Simon
>
> On Thu, May 23, 2019 at 11:26 AM Michael P. McDonnell
>  wrote:
> >
> > I'm sorry if I'm being dense here then:
> >
> > In my game class - do I just do something like this?
> >
> > class Game(Base):
> > id = Column('id')  # UUID PRIMARY KEY, etc.
> > # Stuff
> > def _get_leaderboard(self, SessionObj):
> > SessionObj.query(.# blah blah blah
> >
> > leaderboard = _get_leaderboard
> >
> > Or what am I missing here?
> >
> > I wouldn't flood the thread if I knew what to google to flesh this out,
> sorry. I've seen thousands of examples and nothing seems to match up with
> what I want to do.
> >
> >
> >
> > On Thu, May 23, 2019 at 12:07 PM Simon King 
> wrote:
> >>
> >> On Thu, May 23, 2019 at 10:47 AM Michael P. McDonnell
> >>  wrote:
> >> >
> >> > So if I understand what I'm reading correctly: if I do the
> object-session, I can do the query - but I can only get 1 "column"'s worth
> of information or SA tends to be unhappy with the result.
> >>
> >> I don't know what you mean by this. Once you've got the session, you
> >> can perform any query you like and return whatever information you
> >> like, including multiple columns and multiple rows.
> >>
> >> >
> >> > That kind of actually puts me on the second link - which then if I am
> to understand correctly: I create a "select" which is then the "_table_"
> for the Leaderboard class?
> >> > How would I "pass in" the game id to match against? (I don't need a
> group_by for game if there's only 1 game)
> >> >
> >> > Effectively - my game class should eventually look like this?
> >> >
> >> > class Game(Base):
> >> > id = Column('id')  # UUID PRIMARY KEY, etc.
> >> > # Stuff
> >> > leaderboard = relationship("Leaderboard")
> >> >
> >> > and my Leaderboard class like this:
> >> > lb_select = select( Person.display_name, func.count(Result.task_id),
> func.sum(Result.finish_dttm - Result.start_dttm -
> Result.validation_time).join(Play).join(Person).join(Game).where(Game.id ==
> #PASSED IN ID).group_by(Person.display_name)
> >> >
> >> > class Leaderboard(Base):
> >> > _table_ = lb_select
> >> >
> >> > Is this remotely correct? Or am I missing something really obvious?
> >> >
> >>
> >> To go down this road, you would write your query so that it returns
> >> the leaderboard for *all* games. You wouldn't filter on Game.id at
> >> all. When you make the relationship between Game and Leaderboard,
> >> SQLAlchemy will join the Game table to the Leaderboard as a subquery
> >> and add the game ID as the join criterion. I don't know if your
> >> database's query planner will be intelligent enough to realise that
> >> the game ID restriction can be pushed down into the subquery as well.
> >> If it *is*, the performance should be pretty good. If it *isn't*,
> >> performance will degrade as the number of plays increases.
> >>
> >> Simon
> >>
> >> --
> >> SQLAlchemy -
> >> The Python SQL Toolkit and Object Relational Mapper
> >>
> >&g

Re: [sqlalchemy] Looking to consume query as relationship (multiple column return)

[Michael P. McDonnell]

I'm sorry if I'm being dense here then:

In my game class - do I just do something like this?

class Game(Base):
id = Column('id')  # UUID PRIMARY KEY, etc.
# Stuff
def _get_leaderboard(self, SessionObj):
SessionObj.query(.# blah blah blah

leaderboard = _get_leaderboard

Or what am I missing here?

I wouldn't flood the thread if I knew what to google to flesh this out,
sorry. I've seen thousands of examples and nothing seems to match up with
what I want to do.



On Thu, May 23, 2019 at 12:07 PM Simon King  wrote:

> On Thu, May 23, 2019 at 10:47 AM Michael P. McDonnell
>  wrote:
> >
> > So if I understand what I'm reading correctly: if I do the
> object-session, I can do the query - but I can only get 1 "column"'s worth
> of information or SA tends to be unhappy with the result.
>
> I don't know what you mean by this. Once you've got the session, you
> can perform any query you like and return whatever information you
> like, including multiple columns and multiple rows.
>
> >
> > That kind of actually puts me on the second link - which then if I am to
> understand correctly: I create a "select" which is then the "_table_" for
> the Leaderboard class?
> > How would I "pass in" the game id to match against? (I don't need a
> group_by for game if there's only 1 game)
> >
> > Effectively - my game class should eventually look like this?
> >
> > class Game(Base):
> > id = Column('id')  # UUID PRIMARY KEY, etc.
> > # Stuff
> > leaderboard = relationship("Leaderboard")
> >
> > and my Leaderboard class like this:
> > lb_select = select( Person.display_name, func.count(Result.task_id),
> func.sum(Result.finish_dttm - Result.start_dttm -
> Result.validation_time).join(Play).join(Person).join(Game).where(Game.id ==
> #PASSED IN ID).group_by(Person.display_name)
> >
> > class Leaderboard(Base):
> > _table_ = lb_select
> >
> > Is this remotely correct? Or am I missing something really obvious?
> >
>
> To go down this road, you would write your query so that it returns
> the leaderboard for *all* games. You wouldn't filter on Game.id at
> all. When you make the relationship between Game and Leaderboard,
> SQLAlchemy will join the Game table to the Leaderboard as a subquery
> and add the game ID as the join criterion. I don't know if your
> database's query planner will be intelligent enough to realise that
> the game ID restriction can be pushed down into the subquery as well.
> If it *is*, the performance should be pretty good. If it *isn't*,
> performance will degrade as the number of plays increases.
>
> Simon
>
> --
> 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/CAFHwexfeJ%2BVUTQLJrmG1E4295SLhRoeXvEo8T7az3RSY%3DgR2EQ%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/CAHmCLHqBP%2Bgg-RKP4Xvr2CWDRKVfnkgq%2B3d7H5PS7M3s0AAMXg%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Looking to consume query as relationship (multiple column return)

[Michael P. McDonnell]

So if I understand what I'm reading correctly: if I do the object-session,
I can do the query - but I can only get 1 "column"'s worth of information
or SA tends to be unhappy with the result.

That kind of actually puts me on the second link - which then if I am to
understand correctly: I create a "select" which is then the "_table_" for
the Leaderboard class?
How would I "pass in" the game id to match against? (I don't need a
group_by for game if there's only 1 game)

Effectively - my game class should eventually look like this?

class Game(Base):
id = Column('id')  # UUID PRIMARY KEY, etc.
# Stuff
leaderboard = relationship("Leaderboard")

and my Leaderboard class like this:
lb_select = select( Person.display_name, func.count(Result.task_id),
func.sum(Result.finish_dttm - Result.start_dttm -
Result.validation_time).join(Play).join(Person).join(Game).where(Game.id ==
#PASSED IN ID).group_by(Person.display_name)

class Leaderboard(Base):
_table_ = lb_select

Is this remotely correct? Or am I missing something really obvious?

On Thu, May 23, 2019 at 11:32 AM Simon King  wrote:

> On Thu, May 23, 2019 at 8:52 AM Michael P. McDonnell
>  wrote:
> >
> > Hi Everybody! (Hi Dr. Nick!)
> >
> > I am creating a game for the work I'm doing - and while I didn't create
> the schema - I certainly have to program against it.
> > What I have is a PERSON, a GAME, a PLAY and a collection of RESULT
> >
> > What I'd like to do is call the "leaderboard" member of my GAME class -
> and get the read only results from the following query:
> > SELECT
> > person.display_name AS display_name,
> > COUNT(result.task_id) AS total_tasks_completed,
> > SUM(
> > (result.finish_dttm - result.start_dttm) - result.validation_time
> > ) AS total_time
> > FROM
> > result
> > JOIN play ON (result.play_id = play.id)
> > JOIN person ON (play.person_id = person.id)
> > JOIN game ON (play.game_id = game.id)
> > WHERE game.id = 'fc8dd2e5-ecdb-47f4-811e-3c01ee9f4176'
> > GROUP BY person.display_name
> >
> > Now - I know with a session object - this is easy to just call
> "query(Person.display_name...", but as an object member - how can I
> accomplish this?
> >
> > I've tried:
> >
> > Column Property - but that requires a single data point returned
> > Leaderboard(Base) class - but that requires a table
> > attempting a @property and doing a def _get_leaderboard..
>
> You can get the session for your Game using the object_session function:
>
>
> https://docs.sqlalchemy.org/en/13/orm/session_basics.html#how-can-i-get-the-session-for-a-certain-object
>
> Alternatively, you can map a class to an arbitrary query:
>
>
> https://docs.sqlalchemy.org/en/13/orm/nonstandard_mappings.html#mapping-a-class-against-arbitrary-selects
>
> ...so it ought to be possible to create a "LeaderboardEntry" class
> mapped to something like your query above. You'd have to adapt it
> slightly (it probably needs to GROUP BY game as well as person), and
> you'd have to tell SA what it should consider to be the primary key,
> presumably (game.id, person.id).
>
> The first option will definitely be simpler.
>
> Hope that helps,
>
> Simon
>
> --
> 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/CAFHwexd3ninjd96AhDgVN8cXQXpyxHdMWmawBY0n%2BW3qXQ-sQA%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/CAHmCLHrux-sc%2BXEM7Rj0zqm2KOPNX4JnF1QugDyE8tqRxCp%2BcA%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Looking to consume query as relationship (multiple column return)

[Michael P. McDonnell]

Hi Everybody! (Hi Dr. Nick!)

I am creating a game for the work I'm doing - and while I didn't create the 
schema - I certainly have to program against it. 
What I have is a PERSON, a GAME, a PLAY and a collection of RESULT

What I'd like to do is call the "leaderboard" member of my GAME class - and 
get the read only results from the following query:
SELECT
person.display_name AS display_name,
COUNT(result.task_id) AS total_tasks_completed,
SUM(
(result.finish_dttm - result.start_dttm) - result.validation_time
) AS total_time
FROM
result
JOIN play ON (result.play_id = play.id)
JOIN person ON (play.person_id = person.id)
JOIN game ON (play.game_id = game.id)
WHERE game.id = 'fc8dd2e5-ecdb-47f4-811e-3c01ee9f4176'
GROUP BY person.display_name

Now - I know with a session object - this is easy to just call 
"query(Person.display_name...", but as an object member - how can I 
accomplish this?

I've tried:

   - Column Property - but that requires a single data point returned
   - Leaderboard(Base) class - but that requires a table
   - attempting a @property and doing a def _get_leaderboard..


Thanks in advance for any advice/help or anything one has to offer. I've 
been banging my head on this for a few days and admittedly new to 
sqlalchemy.

-- 
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/17118ace-b232-4bf4-9571-e507a58b7613%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Re: Bug? Query / Execute Mismatch When Given Duplicate Columns

[Michael Tartre]

(SQLAlchemy version is 1.1.13)

On Monday, July 2, 2018 at 7:54:22 PM UTC-4, Michael Tartre wrote:
>
> Hey there!
>
> I noticed the following odd behavior:
>
> import sqlalchemy as sa
> engine = sa.create_engine('sqlite:///tmp.db', echo=True)
> tblsa = sa.Table("mytable", sa.MetaData(), autoload=True, 
> autoload_with=engine)
> q = sa.select([tblsa.c.x, tblsa.c.x])
> with engine.connect() as conn:
> print(list(conn.execute(q)))
>
> 2018-07-02 19:45:15,890 INFO sqlalchemy.engine.base.Engine SELECT test.x 
> FROM test
> 2018-07-02 19:45:15,894 INFO sqlalchemy.engine.base.Engine ()
> [(1,), (2,), (3,), (4,), (5,)]
>
> Is that intended? I had previously assumed that the execution rows would 
> match the input, which seems like the most obvious behavior.
>
> Thanks for any clarification,
>
> Michael
>
>

-- 
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] Bug? Query / Execute Mismatch When Given Duplicate Columns

[Michael Tartre]

Hey there!

I noticed the following odd behavior:

import sqlalchemy as sa
engine = sa.create_engine('sqlite:///tmp.db', echo=True)
tblsa = sa.Table("mytable", sa.MetaData(), autoload=True, 
autoload_with=engine)
q = sa.select([tblsa.c.x, tblsa.c.x])
with engine.connect() as conn:
print(list(conn.execute(q)))

2018-07-02 19:45:15,890 INFO sqlalchemy.engine.base.Engine SELECT test.x 
FROM test
2018-07-02 19:45:15,894 INFO sqlalchemy.engine.base.Engine ()
[(1,), (2,), (3,), (4,), (5,)]

Is that intended? I had previously assumed that the execution rows would match 
the input, which seems like the most obvious behavior.

Thanks for any clarification,

Michael

-- 
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] Is accessing the ResultProxy like objects a supported feature?

[David Michael Gang]

Hi,

As explained here:
https://stackoverflow.com/a/31275607/2343743

The result set of fetchone can be accessed:

row = fetchone()

col1 = row[0] # access via integer position

col2 = row['col2'] # access via name

col3 = row[mytable.c.mycol] # access via Column object.


I accessed it like an object

row.col2 

and it worked.

Is this way supported? Can it be deprecated every version?


Thanks

-- 
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] Re: SQLAlcemy relationships that work with MySQL generate error when used with Sql Server

[michael lovett]

I think I figure this out:



down voteunaccept

In addition to mentioning the SQL Server schema names as part of the table 
args, as in:

class UserGroup(Base):
__tablename__ = 'user_group'
__table_args__ = (
{'schema': 'admn'})

The schema is named "admn".

You also have to mention the schema name in various strings in the ORM in 
which you are naming tables. Two examples I've found so far:

Foreign keys:

user_id = Column(Integer, ForeignKey('admn.user.user_id', 
ondelete="cascade", onupdate="cascade"), primary_key = True)

In relationships when you mention a table, such as a secondary table:

  users = relationship(
"User",
secondary="admn.user_group",
back_populates="groups",
cascade="all",
passive_deletes=True) 

It was this last place that was causing my mapper errors. IE as soon as I 
mentioned the schema name in secondary=... the mapper errors went away.

-- 
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] How about: Disabling relationship cascades on a per-object basis.

[Michael Elsdörfer]

So, I've been a very happy user of SQLAlchemy over many years, though maybe 
not very deeply. I certainly don't really feel qualified to contribute any 
thoughts on it's design, but I did want to bring this up, since it's 
something I have run into again and again.

I'd say that in the majority of projects that involve SQLAlchemy, sooner or 
later I find myself in a situation where I want to work with database 
objects that I don't want to save to the session. It's just so damn 
convenient to pass them around.

Here are two examples off the top of my head:

- A billing tool was supposed to generate a bunch of objects (line items 
and so on); in preview mode, I want to use the same functions to calculate 
and generate the objects, and serialize them to the browser, but I don't 
want to save them.

- In a system to check if the user has the permission to "add an object", I 
really wanted to construct the object temporarily, unsaved, and send it to 
the permission-layer, but without adding it to the session.

Now often, what I do to solve this is what I think SQLAlchemy wants me to 
do, if I understand correctly: Configure the cascades on the relationships 
accordingly. But this has some problems:

1) It's *really* hard. Each time I find myself going back to the 
documentation, and trying to figure out where stuff needs to be changed 
(what does cascade_backrefs on a backref() mean again?)

2) It's error prone. It's easy to later pull in an object through some 
other relationship; it's also hard to later figure out exactly why the 
cascades where configured in the way that they are, and which code paths 
depend on that particular cascade setting. Changing any cascade may easily 
cause side effects that are not predictable.

So thinking about it, changing the cascade settings on the relationship is 
not really what I want to do. What I really want: The cascade to work in 
most cases, *except this one time*. I find myself searching for a version 
of `session.no_autoflush`. For example:

with session.no_cascade:
   order = Order() 
   order.account = current_account

Since current_account is in the session, the order would ordinarily be 
pulled into it, too. But the decorator could prevent that. 

Or maybe:

from sqlalchemy import taint_no_cascade
order = Order()
taint_no_cascade(order)
order.account = current_account

The whole thing is probably much more complicated than that, but I desire 
this so frequently, I wanted to ask if it's feasable, or has been discussed 
before.

Thanks,

Michael

-- 
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] Selected subqueries with table inheritance don't correlate correctly

[Michael Williamson]

On Mon, 2 Oct 2017 10:00:43 -0400
Mike Bayer  wrote:

> On Mon, Oct 2, 2017 at 7:18 AM, Michael Williamson
>  wrote:
> > I'm trying to select a value using a correlated subquery that uses
> > table inheritance, but the generated query seems to leak the table
> > from the subquery into the outer query. For instance, suppose
> > employee is a table, engineer inherits from employee, and each
> > employee has a foreign key to a country. I want to get the number
> > of engineers per country, so I write:
> 
> 
> you found a bug, and it's in a method that has caused lots of bugs so
> this is not too surprising.
> https://bitbucket.org/zzzeek/sqlalchemy/issues/4103/_adjust_for_single_inheritance-screwing-up
> is added.


Thanks for the quick reply, Mike. For now, we're querying against the
base class and adding in the condition on the discriminator manually.

When using Query, is there a way to make sure that only tables
explicitly listed using `select_from` or `join` are selected from? This
would help us prevent cases where we accidentally perform a cross join,
whether as a result of bugs or an incorrectly written query.

Thanks

Michael

> >
> > engineer_count = Query([func.count(Engineer.id)]) \
> > .select_from(Engineer) \
> > .filter(Engineer.country_id == Country.id) \
> > .correlate(Country) \
> > .subquery() \
> > .as_scalar()
> >
> > print(session.query(Country.id, engineer_count).all())
> >
> > But this generates the query (note that employee is in the outer
> > FROM clauses):
> >
> > SELECT country.id AS country_id, (SELECT count(employee.id) AS
> > count_1 FROM employee
> > WHERE employee.country_id = country.id AND employee.type IN (?)) AS
> > anon_1 FROM country, employee
> > WHERE employee.type IN (?)
> >
> > whereas I'd expect:
> >
> > SELECT country.id AS country_id, (SELECT count(employee.id) AS
> > count_1 FROM employee
> > WHERE employee.country_id = country.id AND employee.type IN (?)) AS
> > anon_1 FROM country
> >
> > Am I doing something wrong? Full example below.
> >
> > Thanks
> >
> > Michael
> >
> > from sqlalchemy import Column, create_engine, ForeignKey, func,
> > Integer, String
> > from sqlalchemy.ext.declarative import declarative_base
> > from sqlalchemy.orm import Query, relationship, Session
> >
> >
> > Base = declarative_base()
> >
> >
> > class Country(Base):
> > __tablename__ = "country"
> >
> > id = Column(Integer, primary_key=True)
> > name = Column(String, nullable=False)
> >
> >
> > class Employee(Base):
> > __tablename__ = "employee"
> >
> > id = Column(Integer, primary_key=True)
> > discriminator = Column("type", String(50), nullable=False)
> > country_id = Column(Integer, ForeignKey("country.id"),
> > nullable=False) country = relationship(Country)
> >
> > __mapper_args__ = {"polymorphic_on": discriminator}
> >
> >
> > class Engineer(Employee):
> > __mapper_args__ = {"polymorphic_identity": "engineer"}
> >
> >
> > engine = create_engine("sqlite://", echo=True)
> >
> > Base.metadata.create_all(engine)
> >
> >
> > session = Session(engine)
> >
> > session.add(Country(name="United Kingdom"))
> >
> >
> > engineer_count = Query([func.count(Engineer.id)]) \
> > .select_from(Engineer) \
> > .filter(Engineer.country_id == Country.id) \
> > .correlate(Country) \
> > .subquery() \
> > .as_scalar()
> >
> > print(session.query(Country.id, engineer_count).all())
> >
> >
> > engineer_count = Query([func.count(Employee.id)]) \
> > .select_from(Employee) \
> > .filter(Employee.discriminator ==
> > Engineer.__mapper_args__["polymorphic_identity"]) \
> > .filter(Employee.country_id == Country.id) \
> > .correlate(Country) \
> > .subquery() \
> > .as_scalar()
> >
> > print(session.query(Country.id, engineer_count).all())
> >
> > --
> > 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
> &g

[sqlalchemy] Selected subqueries with table inheritance don't correlate correctly

[Michael Williamson]

I'm trying to select a value using a correlated subquery that uses table 
inheritance, but the generated query seems to leak the table from the 
subquery into the outer query. For instance, suppose employee is a table, 
engineer inherits from employee, and each employee has a foreign key to a 
country. I want to get the number of engineers per country, so I write:

engineer_count = Query([func.count(Engineer.id)]) \
.select_from(Engineer) \
.filter(Engineer.country_id == Country.id) \
.correlate(Country) \
.subquery() \
.as_scalar()

print(session.query(Country.id, engineer_count).all())

But this generates the query (note that employee is in the outer FROM 
clauses):

SELECT country.id AS country_id, (SELECT count(employee.id) AS count_1 
FROM employee 
WHERE employee.country_id = country.id AND employee.type IN (?)) AS anon_1 
FROM country, employee 
WHERE employee.type IN (?)

whereas I'd expect:

SELECT country.id AS country_id, (SELECT count(employee.id) AS count_1 
FROM employee 
WHERE employee.country_id = country.id AND employee.type IN (?)) AS anon_1 
FROM country

Am I doing something wrong? Full example below.

Thanks

Michael

from sqlalchemy import Column, create_engine, ForeignKey, func, Integer, 
String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import Query, relationship, Session


Base = declarative_base()


class Country(Base):
__tablename__ = "country"

id = Column(Integer, primary_key=True)
name = Column(String, nullable=False)


class Employee(Base):
__tablename__ = "employee"

id = Column(Integer, primary_key=True)
discriminator = Column("type", String(50), nullable=False)
country_id = Column(Integer, ForeignKey("country.id"), nullable=False)
country = relationship(Country)

__mapper_args__ = {"polymorphic_on": discriminator}


class Engineer(Employee):
__mapper_args__ = {"polymorphic_identity": "engineer"}


engine = create_engine("sqlite://", echo=True)

Base.metadata.create_all(engine)


session = Session(engine)

session.add(Country(name="United Kingdom"))


engineer_count = Query([func.count(Engineer.id)]) \
.select_from(Engineer) \
.filter(Engineer.country_id == Country.id) \
.correlate(Country) \
.subquery() \
.as_scalar()

print(session.query(Country.id, engineer_count).all())


engineer_count = Query([func.count(Employee.id)]) \
.select_from(Employee) \
.filter(Employee.discriminator == 
Engineer.__mapper_args__["polymorphic_identity"]) \
.filter(Employee.country_id == Country.id) \
.correlate(Country) \
.subquery() \
.as_scalar()

print(session.query(Country.id, engineer_count).all())

-- 
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] Using CTEs inside union()

[Michael Williamson]

On Wed, 22 Feb 2017 11:15:05 -0500
mike bayer  wrote:

> 
> 
> On 02/22/2017 10:17 AM, Michael Williamson wrote:
> > Using CTEs directly inside union() (or similar functions such as
> > intersect()) causes an error:
> >
> >
> >  query_1 = s.query(Record.id).cte()
> >  query_2 = s.query(Record.id).cte()
> >
> >  select_from = union(query_1, query_2)
> 
> what does the above intend to mean?  I don't know what SQL one would 
> expect from that.  A CTE is specifically a SELECT that goes on top of 
> another SELECT as a sort of "aliased" subquery.   a UNION doesn't
> make any sense on the "outside" of that unless you can show me.
> 
> I can see this instead:
> 
>   q1 = s.query(Record.id)
>   q2 = s.query(Record.id)
> 
>   select_from = union(q1, q2).cte()
> 
> but that's not what the above is saying.

My expectation was as in the working code I wrote: that is, select all
of the rows from the CTE.

select_from = union(
select([query_1.c.id]).select_from(query_1),
select([query_2.c.id]).select_from(query_2),
)

If the answer is "you're doing something weird, don't do that", that's
fine, I just thought I'd point out a situation where the error that was
generated didn't make it immediately clear where I'd make a mistake.

> 
> 
> 
> 
> 
> >  print(s.query(func.count(1)).select_from(select_from).all())
> >
> >
> > sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError)
> > syntax error at or near "SELECT" LINE 2: SELECT record.id AS id
> > ^
> >  [SQL: 'WITH anon_2 AS \nSELECT record.id AS id \nFROM record,
> > \nanon_3 AS \nSELECT record.id AS id \nFROM record\n SELECT
> > count(%(count_2)s) AS count_1 \nFROM (anon_2 UNION anon_3) AS
> > anon_1'] [parameters: {'count_2': 1}]
> >
> > The way I got things working was to wrap the CTE in a SELECT rather
> > than using it directly:
> >
> > select_from = union(
> >  select([query_1.c.id]).select_from(query_1),
> >  select([query_2.c.id]).select_from(query_2),
> > )
> > print(s.query(func.count(1)).select_from(select_from).all())
> >
> > However, the original error didn't make it especially obvious to me
> > what was going on. Perhaps either the API could be changed to allow
> > using the CTE directly, or a clearer error could be emitted? Or am I
> > just thinking about this all wrong?
> >
> > I also got an error when using subquery() instead of cte(), which
> > was fixed in the same way -- presumably this is the same issue?
> >
> > Thanks
> >
> > Michael
> >
> 

-- 
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] Using CTEs inside union()

[Michael Williamson]

Using CTEs directly inside union() (or similar functions such as
intersect()) causes an error:

 import os

 from sqlalchemy.event import listens_for
 from sqlalchemy import *
 from sqlalchemy.orm import *
 from sqlalchemy.ext.declarative import declarative_base

 Base = declarative_base()


 class Record(Base):
  __tablename__ = 'record'
  id = Column(Integer, primary_key=True)


 # On postgres
 e = create_engine(os.environ["TEST_DATABASE"], echo=True)

 Base.metadata.create_all(e)

 s = Session(e)
 s.commit()

 query_1 = s.query(Record.id).cte()
 query_2 = s.query(Record.id).cte()

 select_from = union(query_1, query_2)
 print(s.query(func.count(1)).select_from(select_from).all())


sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) syntax
error at or near "SELECT" LINE 2: SELECT record.id AS id 
^
 [SQL: 'WITH anon_2 AS \nSELECT record.id AS id \nFROM record,
\nanon_3 AS \nSELECT record.id AS id \nFROM record\n SELECT
count(%(count_2)s) AS count_1 \nFROM (anon_2 UNION anon_3) AS
anon_1'] [parameters: {'count_2': 1}]

The way I got things working was to wrap the CTE in a SELECT rather than
using it directly:

select_from = union(
 select([query_1.c.id]).select_from(query_1),
 select([query_2.c.id]).select_from(query_2),
)
print(s.query(func.count(1)).select_from(select_from).all())

However, the original error didn't make it especially obvious to me
what was going on. Perhaps either the API could be changed to allow
using the CTE directly, or a clearer error could be emitted? Or am I
just thinking about this all wrong?

I also got an error when using subquery() instead of cte(), which was
fixed in the same way -- presumably this is the same issue?

Thanks

Michael

-- 
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] Selecting from polymorphic tables without selecting polymorphic columns

[Michael Williamson]

Makes sense, thanks! As you say, there is a workaround that we can use
-- the only downside is having to be slightly careful when writing
queries. So nice to have, but not desperate by any means.

Thanks again

On Tue, 17 Jan 2017 10:13:41 -0500
mike bayer  wrote:

> if we make this wait for 1.2 I'm hoping 1.2 is in the early spring as
> is typical for the x.y releases.   I'm desperately trying to trim the
> scope of 1.2 (to include things like this, exclude more fundamental 
> "correctness" types of things) as all these x.y releases always
> balloon in size and I'd like to reduce the gap between them.
> 
> in this case, we have a behavior that hasn't really changed for many 
> years, a fix that would change the SQL results for an application
> that is using this pattern, and you also have a simple workaround.
> 
> It's not clear if applications that happen to be unknowingly doing
> this are experiencing the "right" behavior, because they really
> wanted to query against the base class anyway, or are experiencing
> the "wrong" behavior and not realizing it.If I did put this fix
> out in a 1.1.x, it's very likely I'll never hear about it, that is,
> probably nobody is doing this query because they realized i doesn't
> work.
> 
>  From my end it's less controversial to push it to 1.2 where people
> can expect minor behavioral changes rather than as a point release
> where it has an admittedly very low chance of breaking someone's
> application.
> 
> 
> 
> On 01/17/2017 04:19 AM, Michael Williamson wrote:
> > Thanks! Is there any plan for a 1.2 release in the near future?
> >
> > On Mon, 16 Jan 2017 12:54:05 -0500
> > mike bayer  wrote:
> >
> >> issue
> >>
> >> https://bitbucket.org/zzzeek/sqlalchemy/issues/3891/single-inh-criteria-should-be-added-for
> >>
> >> is added.   Targeted at 1.2 as it will break applications
> >> unknowingly relying upon the bug right now.
> >>
> >> For now say func.count(Manager.employee_id), e.g. put the entity in
> >> the columns clause.
> >>
> >>
> >>
> >> On 01/16/2017 12:23 PM, Michael Williamson wrote:
> >>> Hello!
> >>>
> >>> I have a use case where I want to select from a polymorphic table,
> >>> but without selecting any columns from that table. As a simple
> >>> example, consider selecting the count of all rows. When I write
> >>> something like:
> >>>
> >>> sess.query(func.count(1)).select_from(Manager).all()
> >>>
> >>> It seems to be equivalent to:
> >>>
> >>> sess.query(func.count(1)).select_from(Employee).all()
> >>>
> >>> (where Manager inherits from Employee).
> >>>
> >>> Is this intended, or is this a bug? If the former, what's the
> >>> suggested approach to writing such queries? To filter on the
> >>> discriminator explicitly?
> >>>
> >>> For reference, I was able to reproduce the issue with a test case
> >>> in test/orm/inheritance/test_single.py:
> >>>
> >>> def test_select_from_inherited_tables(self):
> >>> Manager, Engineer, Employee = (self.classes.Manager,
> >>> self.classes.Engineer, self.classes.Employee)
> >>>
> >>> sess = create_session()
> >>> m1 = Manager(name='Tom', manager_data='data1')
> >>> e1 = Engineer(name='Kurt', engineer_info='knows how to
> >>> hack') sess.add_all([m1, e1])
> >>> sess.flush()
> >>>
> >>> eq_(
> >>> sess.query(func.count(1)).select_from(Manager).all(),
> >>> [(1, )]
> >>> )
> >>>
> >>> Thanks
> >>>
> >>> Michael
> >>>
> >>
> >
> 

-- 
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] Selecting from polymorphic tables without selecting polymorphic columns

[Michael Williamson]

Thanks! Is there any plan for a 1.2 release in the near future?

On Mon, 16 Jan 2017 12:54:05 -0500
mike bayer  wrote:

> issue
> 
> https://bitbucket.org/zzzeek/sqlalchemy/issues/3891/single-inh-criteria-should-be-added-for
> 
> is added.   Targeted at 1.2 as it will break applications unknowingly 
> relying upon the bug right now.
> 
> For now say func.count(Manager.employee_id), e.g. put the entity in
> the columns clause.
> 
> 
> 
> On 01/16/2017 12:23 PM, Michael Williamson wrote:
> > Hello!
> >
> > I have a use case where I want to select from a polymorphic table,
> > but without selecting any columns from that table. As a simple
> > example, consider selecting the count of all rows. When I write
> > something like:
> >
> > sess.query(func.count(1)).select_from(Manager).all()
> >
> > It seems to be equivalent to:
> >
> > sess.query(func.count(1)).select_from(Employee).all()
> >
> > (where Manager inherits from Employee).
> >
> > Is this intended, or is this a bug? If the former, what's the
> > suggested approach to writing such queries? To filter on the
> > discriminator explicitly?
> >
> > For reference, I was able to reproduce the issue with a test case in
> > test/orm/inheritance/test_single.py:
> >
> > def test_select_from_inherited_tables(self):
> > Manager, Engineer, Employee = (self.classes.Manager,
> > self.classes.Engineer, self.classes.Employee)
> >
> > sess = create_session()
> > m1 = Manager(name='Tom', manager_data='data1')
> > e1 = Engineer(name='Kurt', engineer_info='knows how to
> > hack') sess.add_all([m1, e1])
> > sess.flush()
> >
> > eq_(
> > sess.query(func.count(1)).select_from(Manager).all(),
> > [(1, )]
> > )
> >
> > Thanks
> >
> > Michael
> >
> 

-- 
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] Selecting from polymorphic tables without selecting polymorphic columns

[Michael Williamson]

Hello!

I have a use case where I want to select from a polymorphic table, but
without selecting any columns from that table. As a simple example,
consider selecting the count of all rows. When I write something like:

sess.query(func.count(1)).select_from(Manager).all()

It seems to be equivalent to:

sess.query(func.count(1)).select_from(Employee).all()

(where Manager inherits from Employee).

Is this intended, or is this a bug? If the former, what's the suggested
approach to writing such queries? To filter on the discriminator
explicitly?

For reference, I was able to reproduce the issue with a test case in
test/orm/inheritance/test_single.py:

def test_select_from_inherited_tables(self):
Manager, Engineer, Employee = (self.classes.Manager, 
self.classes.Engineer, self.classes.Employee)

sess = create_session()
m1 = Manager(name='Tom', manager_data='data1')
e1 = Engineer(name='Kurt', engineer_info='knows how to hack')
sess.add_all([m1, e1])
sess.flush()

eq_(
sess.query(func.count(1)).select_from(Manager).all(),
    [(1, )]
)

Thanks

Michael

-- 
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] Bundles and selecting from polymorphic tables

[Michael Williamson]

Great, thanks!

On Mon, 19 Dec 2016 12:43:26 -0500
mike bayer  wrote:

> thanks, this is 
> https://bitbucket.org/zzzeek/sqlalchemy/issues/3874/bundle-does-not-provide-entities-fails-on
>  
> and the gerrit should merge today.
> 
> 
> 
> On 12/19/2016 11:58 AM, Michael Williamson wrote:
> > Hello!
> >
> > When selecting from a polymorphic table using a bundle, the query
> > seems to drop the condition on the discriminator. For instance,
> > suppose we have an Employee class that has a name column, with
> > subclasses Manager and Engineer. When I query for Manager.name, I
> > get back the names of just the managers. When I query for
> > Manager.name as part of a bundle, I get back the names of both
> > managers and engineers. Looking at the SQL, the latter query is
> > missing the WHERE clause for the discriminator altogether.
> >
> > Full example with output below. Any pointers on whether this is a
> > bug or I'm doing something wrong/weird would be appreciated.
> >
> > Thanks
> >
> > Michael
> >
> >  from sqlalchemy import *
> >  from sqlalchemy.orm import *
> >  from sqlalchemy.ext.declarative import declarative_base
> >
> >  Base = declarative_base()
> >
> >
> >  class Employee(Base):
> >   __tablename__ = 'employee'
> >   id = Column(Integer, primary_key=True)
> >   type = Column(String(50))
> >   name = Column(String(30))
> >
> >   __mapper_args__ = {
> >   'polymorphic_identity': 'employee',
> >   'polymorphic_on': type,
> >   'with_polymorphic': '*'
> >   }
> >
> >
> >  class Engineer(Employee):
> >   __mapper_args__ = {
> >   'polymorphic_identity': 'engineer',
> >   }
> >
> >
> >  class Manager(Employee):
> >   __mapper_args__ = {
> >   'polymorphic_identity': 'manager',
> >   }
> >
> >
> >  e = create_engine("sqlite://", echo=True)
> >  Base.metadata.create_all(e)
> >
> >  s = Session(e)
> >  s.add_all([
> >   Engineer(name='e1'),
> >   Engineer(name='e2'),
> >   Manager(name='m1'),
> >  ])
> >
> >  s.commit()
> >
> >  print(s.query(Manager.name).select_from(Manager).all())
> >  print(s.query(Bundle("name",
> >  Manager.name)).select_from(Manager).all())
> >
> > Output:
> >
> > 2016-12-19 16:48:39,502 INFO sqlalchemy.engine.base.Engine
> > SELECT employee.name AS employee_name FROM employee
> > WHERE employee.type IN (?)
> > 2016-12-19 16:48:39,502 INFO sqlalchemy.engine.base.Engine
> > ('manager',) [(u'm1',)]
> > 2016-12-19 16:48:39,503 INFO sqlalchemy.engine.base.Engine
> > SELECT employee.name AS employee_name FROM employee
> > 2016-12-19 16:48:39,503 INFO sqlalchemy.engine.base.Engine ()
> > [((u'e1',),), ((u'e2',),), ((u'm1',),)]
> >
> 

-- 
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] Bundles and selecting from polymorphic tables

[Michael Williamson]

Hello!

When selecting from a polymorphic table using a bundle, the query seems
to drop the condition on the discriminator. For instance, suppose we
have an Employee class that has a name column, with subclasses Manager
and Engineer. When I query for Manager.name, I get back the names of
just the managers. When I query for Manager.name as part of a bundle,
I get back the names of both managers and engineers. Looking at the SQL,
the latter query is missing the WHERE clause for the discriminator
altogether.

Full example with output below. Any pointers on whether this is a bug
or I'm doing something wrong/weird would be appreciated.

Thanks

Michael

 from sqlalchemy import *
 from sqlalchemy.orm import *
 from sqlalchemy.ext.declarative import declarative_base

 Base = declarative_base()


 class Employee(Base):
  __tablename__ = 'employee'
  id = Column(Integer, primary_key=True)
  type = Column(String(50))
  name = Column(String(30))

  __mapper_args__ = {
  'polymorphic_identity': 'employee',
  'polymorphic_on': type,
  'with_polymorphic': '*'
  }


 class Engineer(Employee):
  __mapper_args__ = {
  'polymorphic_identity': 'engineer',
  }


 class Manager(Employee):
  __mapper_args__ = {
  'polymorphic_identity': 'manager',
  }


 e = create_engine("sqlite://", echo=True)
 Base.metadata.create_all(e)

 s = Session(e)
 s.add_all([
  Engineer(name='e1'),
  Engineer(name='e2'),
  Manager(name='m1'),
 ])

 s.commit()

 print(s.query(Manager.name).select_from(Manager).all())
 print(s.query(Bundle("name",
 Manager.name)).select_from(Manager).all())

Output:

2016-12-19 16:48:39,502 INFO sqlalchemy.engine.base.Engine SELECT 
employee.name AS employee_name 
FROM employee 
WHERE employee.type IN (?)
2016-12-19 16:48:39,502 INFO sqlalchemy.engine.base.Engine ('manager',)
[(u'm1',)]
2016-12-19 16:48:39,503 INFO sqlalchemy.engine.base.Engine SELECT 
employee.name AS employee_name 
FROM employee
2016-12-19 16:48:39,503 INFO sqlalchemy.engine.base.Engine ()
[((u'e1',),), ((u'e2',),), ((u'm1',),)]

-- 
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] Re: Polymorphic properties with joined table inheritance

[Michael Williamson]

On Wed, 16 Nov 2016 12:02:41 -0500
mike bayer  wrote:

> 
> 
> On 11/16/2016 11:58 AM, Michael Williamson wrote:
> >
> > Thanks, that seems to get most of the way there. The only problem is
> > that calling .name on the instance returns None:
> >
> > print s.query(Employee).select_from(Employee).first().name
> >
> > It seems like getting rid of Employee.name and renaming manager_name
> > and engineer_name to just name works -- is there a reason that's a
> > bad idea?
> 
> whoops, forgot the column_property():
> 
> Employee.name = column_property(func.coalesce(Engineer.engineer_name, 
> Manager.manager_name))

Ah, that seems better. Would it be dangerous to rename manager_name and
engineer_name to just name (so that, for instance, you can always just
set name without being concerned what the concrete type is), or would
that cause issues?

In any case, the help is much appreciated!

> 
> the select_from() isn't needed either, I was wondering why it needed
> that:
> 
> print s.query(Employee).filter(Employee.name.in_(['e2', 'm1'])).all()
> 
> print s.query(Employee.name).all()
> 
> print s.query(Employee).select_from(Employee).first().name
> 
> output:
> 
> SELECT employee.id AS employee_id, coalesce(engineer.engineer_name, 
> manager.manager_name) AS coalesce_1, employee.type AS employee_type, 
> engineer.id AS engineer_id, engineer.engineer_name AS 
> engineer_engineer_name, manager.id AS manager_id,
> manager.manager_name AS manager_manager_name
> FROM employee LEFT OUTER JOIN engineer ON employee.id = engineer.id
> LEFT OUTER JOIN manager ON employee.id = manager.id
> WHERE coalesce(engineer.engineer_name, manager.manager_name) IN (?, ?)
> 2016-11-16 12:01:38,230 INFO sqlalchemy.engine.base.Engine ('e2',
> 'm1') [<__main__.Engineer object at 0x7fc9acf8de90>,
> <__main__.Manager object at 0x7fc9acf20050>]
> 2016-11-16 12:01:38,232 INFO sqlalchemy.engine.base.Engine SELECT 
> coalesce(engineer.engineer_name, manager.manager_name) AS coalesce_1
> FROM employee LEFT OUTER JOIN engineer ON employee.id = engineer.id
> LEFT OUTER JOIN manager ON employee.id = manager.id
> 2016-11-16 12:01:38,232 INFO sqlalchemy.engine.base.Engine ()
> [(u'e1',), (u'e2',), (u'm1',)]
> 2016-11-16 12:01:38,234 INFO sqlalchemy.engine.base.Engine SELECT 
> employee.id AS employee_id, coalesce(engineer.engineer_name, 
> manager.manager_name) AS coalesce_1, employee.type AS employee_type, 
> engineer.id AS engineer_id, engineer.engineer_name AS 
> engineer_engineer_name, manager.id AS manager_id,
> manager.manager_name AS manager_manager_name
> FROM employee LEFT OUTER JOIN engineer ON employee.id = engineer.id
> LEFT OUTER JOIN manager ON employee.id = manager.id
>   LIMIT ? OFFSET ?
> 2016-11-16 12:01:38,234 INFO sqlalchemy.engine.base.Engine (1, 0)
> e1
> 
> 
> 
> 
> >
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >>>
> >>> Joined inheritance lets you do that if you put the name on the
> >>> employee, but I'd like to keep the name on the individual
> >>> subtypes. For my actual use case, some of the columns on one of
> >>> the subtypes is calculated from other bits of SQL: in other
> >>> words, the way the columns are written out in SQL needs to differ
> >>> for each type, but I'd like to be able to write queries to select
> >>> the columns without having to explicitly write out the union each
> >>> time.
> >>>
> >>> So for better motivation, imagine if Engineer.name was defined as:
> >>>
> >>> @hybrid_property
> >>> def name(self):
> >>> return "E" + str(self.id)
> >>>
> >>> @name.expression
> >>> def name(cls):
> >>> return ("E" + cls.id.cast(String())).label("name")
> >>>
> >>> while Manager.name remains an ordinary column.
> >>>
> >>> --
> >>> 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 a

Re: [sqlalchemy] Re: Polymorphic properties with joined table inheritance

[Michael Williamson]

On Wed, 16 Nov 2016 11:38:20 -0500
mike bayer  wrote:

> 
> 
> On 11/16/2016 11:29 AM, Michael Williamson wrote:
> >> I have no idea what you are trying to accomplish.  Why not use
> >> joined inheritance normally?  When you query for Employee, you will
> >> get Engineer / Manager objects back, they will have .name.
> >>
> >> If you're trying to make it so that only one SELECT is emitted in
> >> order to get fully populated Engineer/Manager, joined inheritance
> >> already does that as well, use plain with_polymorphic for that, the
> >> SQL is constructed automatically.
> >
> > Sorry, I haven't done a good job of explaining my use-case very
> > well. I want to be able to treat the name from both Engineer and
> > Manager as the same thing on Employee, so that I can write queries
> > like:
> >
> > session.query(Employee.name)
> >
> > and also:
> >
> > session.query(Employee)
> 
> So you need Employee to be with_polymorphic in all cases and you need
> a .name attribute that is a column property of the coalesce of all
> such name fields.  See below.
> 
> from sqlalchemy import *
> from sqlalchemy.orm import *
> from sqlalchemy.ext.declarative import declarative_base
> 
> Base = declarative_base()
> 
> 
> class Employee(Base):
>  __tablename__ = 'employee'
>  id = Column(Integer, primary_key=True)
>  name = Column(String(50))
>  type = Column(String(50))
> 
>  __mapper_args__ = {
>  'polymorphic_identity': 'employee',
>  'polymorphic_on': type,
>  'with_polymorphic': '*'
>  }
> 
> 
> class Engineer(Employee):
>  __tablename__ = 'engineer'
>  id = Column(Integer, ForeignKey('employee.id'), primary_key=True)
>  engineer_name = Column(String(30))
> 
>  __mapper_args__ = {
>  'polymorphic_identity': 'engineer',
>  }
> 
> 
> class Manager(Employee):
>  __tablename__ = 'manager'
>  id = Column(Integer, ForeignKey('employee.id'), primary_key=True)
>  manager_name = Column(String(30))
> 
>  __mapper_args__ = {
>  'polymorphic_identity': 'manager',
>  }
> 
> 
> Employee.name = func.coalesce(Engineer.engineer_name,
> Manager.manager_name)
> 
> 
> e = create_engine("sqlite://", echo=True)
> Base.metadata.create_all(e)
> 
> s = Session(e)
> s.add_all([
>  Engineer(engineer_name='e1'),
>  Engineer(engineer_name='e2'),
>  Manager(manager_name='m1'),
> ])
> 
> s.commit()
> 
> print s.query(Employee).filter(Employee.name.in_(['e2', 'm1'])).all()
> 
> print s.query(Employee.name).select_from(Employee).all()
> 
> 
> query output is:
> 
> SELECT employee.id AS employee_id, employee.name AS employee_name, 
> employee.type AS employee_type, engineer.id AS engineer_id, 
> engineer.engineer_name AS engineer_engineer_name, manager.id AS 
> manager_id, manager.manager_name AS manager_manager_name
> FROM employee LEFT OUTER JOIN engineer ON employee.id = engineer.id
> LEFT OUTER JOIN manager ON employee.id = manager.id
> WHERE coalesce(engineer.engineer_name, manager.manager_name) IN (?, ?)
> 2016-11-16 11:36:26,868 INFO sqlalchemy.engine.base.Engine ('e2',
> 'm1') [<__main__.Engineer object at 0x7f4dd4407c50>,
> <__main__.Manager object at 0x7f4dd4407e10>]
> 
> 2016-11-16 11:37:30,029 INFO sqlalchemy.engine.base.Engine SELECT 
> coalesce(engineer.engineer_name, manager.manager_name) AS coalesce_1
> FROM employee LEFT OUTER JOIN engineer ON employee.id = engineer.id
> LEFT OUTER JOIN manager ON employee.id = manager.id
> 2016-11-16 11:37:30,029 INFO sqlalchemy.engine.base.Engine ()
> [(u'e1',), (u'e2',), (u'm1',)]
> 

Thanks, that seems to get most of the way there. The only problem is
that calling .name on the instance returns None:

print s.query(Employee).select_from(Employee).first().name

It seems like getting rid of Employee.name and renaming manager_name
and engineer_name to just name works -- is there a reason that's a bad
idea?

> 
> 
> 
> 
> 
> 
> 
> >
> > Joined inheritance lets you do that if you put the name on the
> > employee, but I'd like to keep the name on the individual subtypes.
> > For my actual use case, some of the columns on one of the subtypes
> > is calculated from other bits of SQL: in other words, the way the
> > columns are written out in SQL needs t

Re: [sqlalchemy] Re: Polymorphic properties with joined table inheritance

[Michael Williamson]

> I have no idea what you are trying to accomplish.  Why not use
> joined inheritance normally?  When you query for Employee, you will
> get Engineer / Manager objects back, they will have .name. 
>
> If you're trying to make it so that only one SELECT is emitted in
> order to get fully populated Engineer/Manager, joined inheritance
> already does that as well, use plain with_polymorphic for that, the
> SQL is constructed automatically. 

Sorry, I haven't done a good job of explaining my use-case very well. I
want to be able to treat the name from both Engineer and Manager as the
same thing on Employee, so that I can write queries like:

session.query(Employee.name)

and also:

session.query(Employee)

Joined inheritance lets you do that if you put the name on the
employee, but I'd like to keep the name on the individual subtypes. For
my actual use case, some of the columns on one of the subtypes is
calculated from other bits of SQL: in other words, the way the columns
are written out in SQL needs to differ for each type, but I'd like to
be able to write queries to select the columns without having to
explicitly write out the union each time.

So for better motivation, imagine if Engineer.name was defined as:

@hybrid_property
def name(self):
return "E" + str(self.id)

@name.expression
def name(cls):
return ("E" + cls.id.cast(String())).label("name")

while Manager.name remains an ordinary column.

-- 
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] Re: Polymorphic properties with joined table inheritance

[Michael Williamson]

I've managed to achieve something close to what I want by manually
setting with_polymorphic to refer to a selectable, and then assigning
columns:

column_names = ["id", "name"]
select_employees = sql.union_all(*[
sql.select(
[getattr(model, name) for name in column_names] +

[sql.literal(model.__mapper_args__["polymorphic_identity"]).label("type")]
) \
.select_from(model.__table__.join(Employee.__table__))
for model in [Manager, Engineer]
]).alias("employees")

Employee.__mapper__.with_polymorphic = ('*', select_employees)
Employee.__mapper__.polymorphic_on = select_employees.c.type
Employee.type = column_property(select_employees.c.type)
Employee.name = column_property(select_employees.c.name)

The downsides of this approach are:

* If the select doesn't cover all of the columns, the generated SQL
  seems to start doing cartesian products and getting the wrong
  results. I don't mind having to manually list all of the columns, but
  I'd prefer an exception if I miss one (rather than a cartesian
  product). Is there a way to enforce this?

* Joining onto the employee is redundant since we already have the ID
  from the foreign key on the subtypes table. If there is a way to get
  rid of this join, that would be great, but I can live with it if not.
  (Leaving it out causes a cartesian product to be generated, as if the
  IDs on the subtypes are missing.)

Full code for reference:

from __future__ import unicode_literals

from sqlalchemy import create_engine, Column, Integer, String, ForeignKey, 
sql
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import Session, column_property


Base = declarative_base()


class Employee(Base):
__tablename__ = "employee"
id = Column(Integer, primary_key=True)


class Manager(Employee):
__tablename__ = "manager"
__mapper_args__ = {
"polymorphic_identity": "manager",
}

id = Column(Integer, ForeignKey(Employee.id), primary_key=True)
name = Column(String)


class Engineer(Employee):
__tablename__ = "engineer"
__mapper_args__ = {
"polymorphic_identity": "employee",
}

id = Column(Integer, ForeignKey(Employee.id), primary_key=True)
name = Column(String)


column_names = ["id", "name"]
select_employees = sql.union_all(*[
sql.select(
[getattr(model, name) for name in column_names] +

[sql.literal(model.__mapper_args__["polymorphic_identity"]).label("type")]
) \
.select_from(model.__table__.join(Employee.__table__))
for model in [Manager, Engineer]
]).alias("employees")

Employee.__mapper__.with_polymorphic = ('*', select_employees)
Employee.__mapper__.polymorphic_on = select_employees.c.type
Employee.type = column_property(select_employees.c.type)
Employee.name = column_property(select_employees.c.name)

engine = create_engine("sqlite:///:memory:", echo=True)

Base.metadata.create_all(engine)
session = Session(engine)

session.add(Engineer(name="Jim"))
session.add(Manager(name="Jules"))
session.commit()

print(session.query(Employee).all())


On Wed, 16 Nov 2016 15:18:54 +
Michael Williamson  wrote:

> When using AbstractConcreteBase to implement inheritance with a
> separate table per type (and no common table), it seems like
> properties that are present on all of the subtypes are lifted up onto
> the super type. Is there a way to get the same behaviour when using
> joined table inheritance? For instance, at the moment, the below
> fails since Employee.name doesn't exist. Is there a way to get
> Employee.name to be automatically generated from the subtypes?
> 
> from __future__ import unicode_literals
> 
> from sqlalchemy import create_engine, Column, Integer, String,
> ForeignKey from sqlalchemy.ext.declarative import declarative_base
> from sqlalchemy.orm import Session
> 
> 
> Base = declarative_base()
> 
> 
> class Employee(Base):
> __tablename__ = "employee"
> __mapper_args__ = {
> "polymorphic_on": "discriminator",
> }
> id = Column(Integer, primary_key=True)
> discriminator = Column(String)
> 
> 
> class Manager(Employee):
> __tablename__ = "manager"
> __mapper_args__ = {
> "polymorphic_identity": "manager",
> }
> 
> id = Column(Integer, ForeignKey(Employee.id),
&

[sqlalchemy] Polymorphic properties with joined table inheritance

[Michael Williamson]

When using AbstractConcreteBase to implement inheritance with a
separate table per type (and no common table), it seems like properties
that are present on all of the subtypes are lifted up onto the super
type. Is there a way to get the same behaviour when using joined table
inheritance? For instance, at the moment, the below fails since
Employee.name doesn't exist. Is there a way to get Employee.name to be
automatically generated from the subtypes?

from __future__ import unicode_literals

from sqlalchemy import create_engine, Column, Integer, String,
ForeignKey from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import Session


Base = declarative_base()


class Employee(Base):
__tablename__ = "employee"
__mapper_args__ = {
"polymorphic_on": "discriminator",
}
id = Column(Integer, primary_key=True)
discriminator = Column(String)


class Manager(Employee):
__tablename__ = "manager"
__mapper_args__ = {
"polymorphic_identity": "manager",
}

id = Column(Integer, ForeignKey(Employee.id), primary_key=True)
name = Column(String)


class Engineer(Employee):
__tablename__ = "engineer"
__mapper_args__ = {
"polymorphic_identity": "employee",
}

id = Column(Integer, ForeignKey(Employee.id), primary_key=True)
name = Column(String)

engine = create_engine("sqlite:///:memory:", echo=True)

Base.metadata.create_all(engine)
session = Session(engine)

session.add(Engineer(name="Jim"))
session.add(Manager(name="Jules"))
session.commit()

print(session.query(Employee.name).all())


For comparison, when using AbstractConcreteBase, it's possible to
reference Employee.name:

from __future__ import unicode_literals

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base,
AbstractConcreteBase from sqlalchemy.orm import Session


Base = declarative_base()


class Employee(AbstractConcreteBase, Base):
pass


class Manager(Employee):
__tablename__ = "manager"
__mapper_args__ = {
"polymorphic_identity": "manager",
"concrete": True
}

id = Column(Integer, primary_key=True)
name = Column(String)


class Engineer(Employee):
__tablename__ = "engineer"
__mapper_args__ = {
"polymorphic_identity": "employee",
"concrete": True,
}

id = Column(Integer, primary_key=True)
name = Column(String)

engine = create_engine("sqlite:///:memory:", echo=True)

Base.metadata.create_all(engine)
session = Session(engine)

session.add(Engineer(name="Jim"))
session.add(Manager(name="Jules"))
session.commit()

print(session.query(Employee.name).all())


Thanks (both in advance for this question, and for putting up with my
other questions!)

Michael

-- 
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] Using relationship with hybrid_property

[Michael Williamson]

Is there a way to use relationship() with hybrid properties? When using
foreign() to mark the foreign key and trying to set the relationship, it
seems to treat the underlying column as the foreign key rather than the
hybrid property. For instance (apologies for triteness):

from __future__ import unicode_literals

from sqlalchemy import create_engine, func, Column, Integer, String
from sqlalchemy.ext.hybrid import hybrid_property
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import foreign, relationship, remote, Session


Base = declarative_base()

class Person(Base):
__tablename__ = "person"

id = Column(Integer, primary_key=True)
name = Column(String)


class Book(Base):
__tablename__ = "book"

id = Column(Integer, primary_key=True)
person_id = Column(String)

@hybrid_property
def author_id(self):
return int(self.person_id[1:])

@author_id.expression
def author_id(self):
return func.substr(self.person_id, 2)

@author_id.setter
def author_id(self, value):
self.person_id = "A{}".format(value)

author = relationship(
Person,
primaryjoin=lambda:
remote(Person.id) == foreign(Book.author_id),
)


engine = create_engine("sqlite:///:memory:")

Base.metadata.create_all(engine)
session = Session(engine)

bob = Person(id=42, name="Bob")
session.add(bob)
session.flush()
session.add(Book(author=bob))
session.commit()

print(session.query(Book).one().person_id)
assert session.query(Book).one().person_id == "A42"


In this case, person_id will be set to "42" rather than "A42". I just
wanted to check that was unsupported, rather than an error on my part
(or if there's an alternative that'll give similar functionality in
the ORM i.e. being able to set the relationship without the foreign ID
necessarily having been set).

Thanks

Michael

-- 
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] Setting key of hybrid_property to attribute name rather than function name

[Michael Williamson]

On Fri, 11 Nov 2016 13:27:09 -0500
mike bayer  wrote:

> 
> 
> On 11/11/2016 12:18 PM, Michael Williamson wrote:
> >
> > That still requires the repetition of the name of the attribute,
> > which I'd rather avoid. I've put together a variation on
> > hybrid_property which automatically assigns the label by scanning
> > through the class dict. It could probably do with a bit of
> > memoization and and proper handling of subtyping (i.e. checking
> > __dict__ of supertypes), but does this seem sane to you? Is there a
> > neater way?
> >
> > class hybrid_property2(hybrid_property):
> > def __get__(self, instance, owner):
> > if instance is None:
> > expression = self.expr(owner)
> > for key, value in owner.__dict__.items():
> > if value == self:
> > return expression.label(key)
> >
> > return expression
> > else:
> > return self.fget(instance)
> >
> > Thanks for the suggestions so far, Mike and Simon.
> 
> Getting the name of the attribute you're set on is not something I
> know that there's any way to do without the kind of thing you're
> doing. Decorators usually work out because fn.__name__ is there to
> give us that info.

Thanks Mike, in the end I defined my own metatype that inherits from
DeclarativeMeta, and set __name__ on each property in __new__.

> 
> 
> >
> >>
> >>
> >>
> >>
> >>
> >>>
> >>> Is there a sensible way to do this? Or am I going about the
> >>> problem the wrong way? In practice, I'm using this to define
> >>> properties backed by a JSON field. In case it makes any
> >>> different, here's the actual use-case I have:
> >>>
> >>> from __future__ import unicode_literals
> >>>
> >>> import os
> >>>
> >>> from sqlalchemy import create_engine, Column, Integer
> >>> from sqlalchemy.dialects.postgresql import JSONB
> >>> from sqlalchemy.event import listens_for
> >>> from sqlalchemy.ext.hybrid import hybrid_property
> >>> from sqlalchemy.ext.declarative import declarative_base
> >>> from sqlalchemy.orm import Session
> >>> from sqlalchemy.pool import StaticPool
> >>>
> >>>
> >>> def json_property(json_column, name, type_=None):
> >>> def instance_get(self):
> >>> return getattr(self, json_column.key)[name]
> >>>
> >>> def instance_set(self, value):
> >>> json_obj = getattr(self, json_column.key)
> >>> if json_obj is None:
> >>> setattr(self, json_column.key, {})
> >>> getattr(self, json_column.key)[name] = value
> >>>
> >>> def cls_get(cls):
> >>> expression = json_column[name]
> >>> if type_:
> >>> return expression.astext.cast(type_)
> >>> else:
> >>> return expression
> >>>
> >>> return hybrid_property(fget=instance_get, expr=cls_get,
> >>> fset=instance_set)
> >>>
> >>>
> >>> Base = declarative_base()
> >>>
> >>> class Person(Base):
> >>> __tablename__ = "person"
> >>>
> >>> id = Column(Integer, primary_key=True)
> >>> data = Column(JSONB, nullable=False)
> >>> born = json_property(data, "born", Integer())
> >>>
> >>>
> >>> engine = create_engine(os.environ["TEST_DATABASE"],
> >>> poolclass=StaticPool) engine.execute("SET search_path TO pg_temp")
> >>>
> >>> @listens_for(engine, "engine_connect")
> >>> def set_search_path(connection, branch):
> >>> connection.execute("SET search_path TO pg_temp")
> >>>
> >>>
> >>> Base.metadata.create_all(engine)
> >>> session = Session(engine)
> >>>
> >>> session.add(Person(born=1881))
> >>> session.commit()
> >>>
> >>> assert session.query(Person.born).one() == (1881, ) # Works fine
> >>> assert session.query(Person.born).one().born == 1881 # Raises
> >>> AttributeError since hybrid_property uses instance_get as the name
> >>>
> >>> --
> >>> SQLAlchemy -
> >>> The Python SQL Toolkit and 

Re: [sqlalchemy] Setting key of hybrid_property to attribute name rather than function name

[Michael Williamson]

On Fri, 11 Nov 2016 11:59:52 -0500
mike bayer  wrote:

> 
> 
> On 11/11/2016 07:20 AM, Michael Williamson wrote:
> > I'm using hybrid_property, and would like the key of the property
> > to be set to the attribute name, rather than the name of the
> > getter. This is because I'm generating a getter function based on
> > some args, rather than having the caller directly defining the
> > getter. As a minimal example:
> >
> > from __future__ import unicode_literals
> >
> > from sqlalchemy import create_engine, Column, Integer, String
> > from sqlalchemy.ext.hybrid import hybrid_property
> > from sqlalchemy.ext.declarative import declarative_base
> > from sqlalchemy.orm import Session
> >
> >
> > def prefixed_property(other_column, prefix):
> > def instance_get(self):
> > return prefix + getattr(self, other_column.key)
> >
> > return hybrid_property(fget=instance_get)
> >
> >
> > Base = declarative_base()
> >
> > class Person(Base):
> > __tablename__ = "person"
> >
> > id = Column(Integer, primary_key=True)
> > name = Column(String)
> > greeting = prefixed_property(name, "Hello ")
> >
> >
> > engine = create_engine("sqlite:///:memory:")
> >
> > Base.metadata.create_all(engine)
> > session = Session(engine)
> >
> > session.add(Person(name="Bob"))
> > session.commit()
> >
> > assert session.query(Person.greeting).one() == ("Hello Bob", )
> > assert session.query(Person.greeting).one().greeting == "Hello Bob"
> > # Fails with AttributeError since hybrid_property uses instance_get
> > as the name
> 
> this example fails because the object returned is not a Person
> instance, it is the raw value of the column returned by your hybrid
> which in this case is anonymously named, as the SQL expression is "x
> + y".   You need to give it the label of your choice for it to be
> present in the row in a particular way.  There is some logic these
> days inside of expression() that seems to get in the way, so just
> subclass or use a plain descriptor:
> 
> class prefixed_property(hybrid_property):
>  def __init__(self, other_column, prefix, labelname):
>  self.other_column = other_column
>  self.prefix = prefix
>  self.labelname = labelname
> 
>  def __get__(self, instance, owner):
>  if instance is None:
>  retval = self.prefix + getattr(owner,
> self.other_column.key) return retval.label(self.labelname)
>  else:
>  return self.prefix + getattr(instance,
> self.other_column.key)
> 
> 
> class Person(Base):
>  __tablename__ = "person"
> 
>  id = Column(Integer, primary_key=True)
>  name = Column(String)
>  greeting = prefixed_property(name, "Hello ", "greeting")
> 

That still requires the repetition of the name of the attribute, which
I'd rather avoid. I've put together a variation on hybrid_property
which automatically assigns the label by scanning through the class
dict. It could probably do with a bit of memoization and and proper
handling of subtyping (i.e. checking __dict__ of supertypes), but does
this seem sane to you? Is there a neater way?

class hybrid_property2(hybrid_property):
def __get__(self, instance, owner):
if instance is None:
expression = self.expr(owner)
for key, value in owner.__dict__.items():
if value == self:
return expression.label(key)

return expression
else:
return self.fget(instance)

Thanks for the suggestions so far, Mike and Simon.

> 
> 
> 
> 
> 
> >
> > Is there a sensible way to do this? Or am I going about the problem
> > the wrong way? In practice, I'm using this to define properties
> > backed by a JSON field. In case it makes any different, here's the
> > actual use-case I have:
> >
> > from __future__ import unicode_literals
> >
> > import os
> >
> > from sqlalchemy import create_engine, Column, Integer
> > from sqlalchemy.dialects.postgresql import JSONB
> > from sqlalchemy.event import listens_for
> > from sqlalchemy.ext.hybrid import hybrid_property
> > from sqlalchemy.ext.declarative import declarative_base
> > from sqlalchemy.orm import Session
> > from sqlalchemy.pool import StaticPool
> >
> >
> > def json_property(json_column, name, type_=None):
> > def instance_get(self):
> > return getattr(self, json_column.

Re: [sqlalchemy] Setting key of hybrid_property to attribute name rather than function name

[Michael Williamson]

On Friday, November 11, 2016 at 2:22:28 PM UTC, Simon King wrote:
>
> On Fri, Nov 11, 2016 at 1:53 PM, Michael Williamson  > wrote: 
> > 
> >> I think your code is basically fine, you've just got a mistake on the 
> >> last line. Presumably you meant to query Person, not Person.born? 
> > 
> > 
> > I want Person.born so that I don't have to get the entire object. It 
> doesn't 
> > make much difference in this example, but is quite important for us in 
> > practice when we're grabbing multiple expressions. 
> > 
> >> 
> >> hybrid_property does't care about the name of the "fget" function, it 
> >> just calls it, passing the instance as the only parameter: 
> >> 
> >> 
> >> 
> https://bitbucket.org/zzzeek/sqlalchemy/src/f2eb4aac9517a3775411c2ecf0f588ffd0d790f6/lib/sqlalchemy/ext/hybrid.py?at=master&fileviewer=file-view-default#hybrid.py-744
>  
> >> 
> > 
> > The call to util.update_wrapper sets the property to have the same name 
> as 
> > the fget function, which in turn is used when creating the SQL 
> expression 
> > (the self.__name__ expression in the comparator method), which 
> determines 
> > the label of the value in the result object. 
> > 
>
> OK, I'm probably missing something. I don't have access to PG right 
> now, so I couldn't run your code directly. Having said that, I'm 
> surprised that the comparator object is even invoked in the 
> expression: 
>
> session.query(Person.born).one().born == '1881' 
>
> ...because "session.query(Person.born).one()" returns a tuple-ish 
> object with 1 element, which won't have a "born" attribute. 
>

It's a namedtuple-ish object -- it behaves as a tuple, but you can also get 
values out by name. The comparator is invoked during construction of the 
hybrid property. Specifically, the constructor calls self.expression, which 
then calls self.comparator.
 

> Anyway, to answer your original question, would it be sufficient to 
> update the __name__ attribute of your instance_get function inside 
> json_property? 
>
> ie. 
>
> def json_property(json_column, name, type_=None): 
> def instance_get(self): 
> return getattr(self, json_column.key)[name] 
> instance_get.__name__ = name 
>

Unfortunately not: the JSON property may not have the same name as the 
attribute e.g.

year_born = json_property(data, "yearBorn", Integer())
 

> Simon 
>

-- 
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] Setting key of hybrid_property to attribute name rather than function name

[Michael Williamson]

> I think your code is basically fine, you've just got a mistake on the 
> last line. Presumably you meant to query Person, not Person.born? 
>

I want Person.born so that I don't have to get the entire object. It 
doesn't make much difference in this example, but is quite important for us 
in practice when we're grabbing multiple expressions.
 

> hybrid_property does't care about the name of the "fget" function, it 
> just calls it, passing the instance as the only parameter: 
>
>
> https://bitbucket.org/zzzeek/sqlalchemy/src/f2eb4aac9517a3775411c2ecf0f588ffd0d790f6/lib/sqlalchemy/ext/hybrid.py?at=master&fileviewer=file-view-default#hybrid.py-744
>  
>
>
The call to util.update_wrapper sets the property to have the same name as 
the fget function, which in turn is used when creating the SQL expression 
(the self.__name__ expression in the comparator method), which determines 
the label of the value in the result object.

-- 
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] Setting key of hybrid_property to attribute name rather than function name

[Michael Williamson]

I'm using hybrid_property, and would like the key of the property to be set 
to the attribute name, rather than the name of the getter. This is because 
I'm generating a getter function based on some args, rather than having the 
caller directly defining the getter. As a minimal example:

from __future__ import unicode_literals

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.hybrid import hybrid_property
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import Session


def prefixed_property(other_column, prefix):
def instance_get(self):
return prefix + getattr(self, other_column.key)

return hybrid_property(fget=instance_get)


Base = declarative_base()

class Person(Base):
__tablename__ = "person"

id = Column(Integer, primary_key=True)
name = Column(String)
greeting = prefixed_property(name, "Hello ")


engine = create_engine("sqlite:///:memory:")

Base.metadata.create_all(engine)
session = Session(engine)

session.add(Person(name="Bob"))
session.commit()

assert session.query(Person.greeting).one() == ("Hello Bob", )
assert session.query(Person.greeting).one().greeting == "Hello Bob" # Fails 
with AttributeError since hybrid_property uses instance_get as the name

Is there a sensible way to do this? Or am I going about the problem the 
wrong way? In practice, I'm using this to define properties backed by a 
JSON field. In case it makes any different, here's the actual use-case I 
have:

from __future__ import unicode_literals

import os

from sqlalchemy import create_engine, Column, Integer
from sqlalchemy.dialects.postgresql import JSONB
from sqlalchemy.event import listens_for
from sqlalchemy.ext.hybrid import hybrid_property
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import Session
from sqlalchemy.pool import StaticPool


def json_property(json_column, name, type_=None):
def instance_get(self):
return getattr(self, json_column.key)[name]

def instance_set(self, value):
json_obj = getattr(self, json_column.key)
if json_obj is None:
setattr(self, json_column.key, {})
getattr(self, json_column.key)[name] = value

def cls_get(cls):
expression = json_column[name]
if type_:
return expression.astext.cast(type_)
else:
return expression

return hybrid_property(fget=instance_get, expr=cls_get, 
fset=instance_set)


Base = declarative_base()

class Person(Base):
__tablename__ = "person"

id = Column(Integer, primary_key=True)
data = Column(JSONB, nullable=False)
born = json_property(data, "born", Integer())


engine = create_engine(os.environ["TEST_DATABASE"], poolclass=StaticPool)
engine.execute("SET search_path TO pg_temp")

@listens_for(engine, "engine_connect")
def set_search_path(connection, branch):
connection.execute("SET search_path TO pg_temp")


Base.metadata.create_all(engine)
session = Session(engine)

session.add(Person(born=1881))
session.commit()

assert session.query(Person.born).one() == (1881, ) # Works fine
assert session.query(Person.born).one().born == 1881 # Raises 
AttributeError since hybrid_property uses instance_get as the name

-- 
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] Using bulk_update_mappings when primary key column has distinct key and name

[Michael Williamson]

Thanks, much appreciated!

On Thursday, November 10, 2016 at 4:11:58 PM UTC, Mike Bayer wrote:
>
> the fix is tracking at: 
>
>
> https://bitbucket.org/zzzeek/sqlalchemy/issues/3849/bulk-update-cant-accomodate-alt-named
>  
>
>
>
> On 11/10/2016 10:33 AM, mike bayer wrote: 
> > this is a bug and the good news is that using the column names is not a 
> > workaround, so nobody would be doing that either. 
> > 
> > 
> > On 11/10/2016 07:17 AM, Michael Williamson wrote: 
> >> Using bulk_update_mappings when the primary key column has a distinct 
> >> key and name seems to cause an error. Specifically, running this code: 
> >> 
> >> from __future__ import unicode_literals 
> >> 
> >> import os 
> >> 
> >> from sqlalchemy import create_engine, Column, Integer, Unicode 
> >> from sqlalchemy.ext.declarative import declarative_base 
> >> from sqlalchemy.orm import Session, relationship 
> >> 
> >> 
> >> Base = declarative_base() 
> >> 
> >> class Person(Base): 
> >> __tablename__ = "person" 
> >> 
> >> id = Column(Integer, primary_key=True, name="person_id") 
> >> name = Column(Unicode, nullable=False) 
> >> 
> >> def __repr__(self): 
> >> return "Person(id={!r}, name={!r})".format(self.id, self.name) 
> >> 
> >> 
> >> engine = create_engine("sqlite:///:memory:") 
> >> 
> >> Base.metadata.create_all(engine) 
> >> session = Session(engine) 
> >> 
> >> person = Person(name="Bob") 
> >> session.add(person) 
> >> session.commit() 
> >> session.bulk_update_mappings(Person, [{"id": person.id, "name": 
> "Jim"}]) 
> >> session.commit() 
> >> 
> >> assert session.query(Person).get(person.id).name == "Jim" 
> >> 
> >> produces the error: 
> >> 
> >> sqlalchemy.exc.StatementError: (sqlalchemy.exc.InvalidRequestError) A 
> >> value is required for bind parameter 'person_person_id' [SQL: u'UPDATE 
> >> person SET person_id=?, name=? WHERE person.person_id = ?'] 
> [parameters: 
> >> [{'person_id': 1, 'name': u'Jim'}]] 
> >> 
> >> Am I missing something, or is this a bug? Or just not an intended 
> >> use-case of bulk_updating_mappings? Setting the name of the column to 
> >> "id" seems to work, as does setting the name column to have a distinct 
> >> name. 
> >> 
> >> -- 
> >> 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  
> >> <mailto:sqlalchemy+unsubscr...@googlegroups.com >. 
> >> To post to this group, send email to sqlal...@googlegroups.com 
>  
> >> <mailto: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.

[sqlalchemy] Using bulk_update_mappings when primary key column has distinct key and name

[Michael Williamson]

Using bulk_update_mappings when the primary key column has a distinct key 
and name seems to cause an error. Specifically, running this code:

from __future__ import unicode_literals

import os

from sqlalchemy import create_engine, Column, Integer, Unicode
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import Session, relationship


Base = declarative_base()

class Person(Base):
__tablename__ = "person"

id = Column(Integer, primary_key=True, name="person_id")
name = Column(Unicode, nullable=False)

def __repr__(self):
return "Person(id={!r}, name={!r})".format(self.id, self.name)


engine = create_engine("sqlite:///:memory:")

Base.metadata.create_all(engine)
session = Session(engine)

person = Person(name="Bob")
session.add(person)
session.commit()
session.bulk_update_mappings(Person, [{"id": person.id, "name": "Jim"}])
session.commit()

assert session.query(Person).get(person.id).name == "Jim"

produces the error:

sqlalchemy.exc.StatementError: (sqlalchemy.exc.InvalidRequestError) A value 
is required for bind parameter 'person_person_id' [SQL: u'UPDATE person SET 
person_id=?, name=? WHERE person.person_id = ?'] [parameters: 
[{'person_id': 1, 'name': u'Jim'}]]

Am I missing something, or is this a bug? Or just not an intended use-case 
of bulk_updating_mappings? Setting the name of the column to "id" seems to 
work, as does setting the name column to have a distinct name.

-- 
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] Delegating to _declarative_constructor

[Michael Williamson]

On Monday, July 25, 2016 at 2:29:21 PM UTC+1, Mike Bayer wrote:
>
>
>
> On 07/25/2016 09:12 AM, Michael Williamson wrote: 
> > Hello! For one of our models, I've defined a custom implementation for 
> > `__init__`. Since it should largely behave the same as the default 
> > constructor, it delegates to `_declarative_constructor`, and then runs 
> > some extra code. However, this feels like relying on some of the 
> > internals of SQLAlchemy. Is there a preferred way to implement this? 
>
> I don't think this is documented but the constructor is applied just to 
> the Base class.  So you can get to it via super: 
>
>
> class A(Base): 
>  __tablename__ = 'a' 
>  id = Column(Integer, primary_key=True) 
>  data = Column(String(50)) 
>
>  def __init__(self, foo, **kw): 
>  self.foo = foo 
>  super(A, self).__init__(**kw) 
>
>
Perfect, thanks!
 

>
>
> > 
> > Thanks 
> > 
> > Michael 
> > 
> > -- 
> > 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  
> > <mailto:sqlalchemy+unsubscr...@googlegroups.com >. 
> > To post to this group, send email to sqlal...@googlegroups.com 
>  
> > <mailto:sqlal...@googlegroups.com >. 
> > Visit this group at https://groups.google.com/group/sqlalchemy. 
> > For more options, visit https://groups.google.com/d/optout. 
>

-- 
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] Delegating to _declarative_constructor

[Michael Williamson]

Hello! For one of our models, I've defined a custom implementation for 
`__init__`. Since it should largely behave the same as the default 
constructor, it delegates to `_declarative_constructor`, and then runs some 
extra code. However, this feels like relying on some of the internals of 
SQLAlchemy. Is there a preferred way to implement this?

Thanks

Michael

-- 
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] with_session and subquery loading

[Michael Williamson]

On Tuesday, June 28, 2016 at 2:57:08 PM UTC+1, Mike Bayer wrote:
>
>
>
> On 06/28/2016 05:28 AM, mic...@healx3.com  wrote: 
> > Hello! I sometimes find it more convenient to create to construct a 
> > query without a session, and then to add the session at the end using 
> > `with_session`. In particular, when constructing non-trivial queries, 
> > not having to pass the session around means one less argument to manage. 
> > However, this doesn't seem to work when using subquery loading, 
> > resulting in the error `AssertionError: Subquery session doesn't refer 
> > to that of our context. Are there broken context caching schemes being 
> > used?`. 
> > 
> > Is there a sensible way to get the subqueries to use the same session, 
> > or is the answer just to always construct queries with the session? 
>
>
> that assertion only applies to when the internal QueryContext is being 
> re-used, as is the case with baked query loading.  The QueryContext 
> itself created during the _compile_context() phase will contain 
> references to the Session within the subquery eager loaders.   As long 
> as you don't do anything with your original query other than call 
> with_session(), there's no error I can reproduce, e.g.: 
>
> from sqlalchemy import * 
> from sqlalchemy.orm import * 
> from sqlalchemy.ext.declarative import declarative_base 
>
> Base = declarative_base() 
>
>
> class A(Base): 
>  __tablename__ = 'a' 
>  id = Column(Integer, primary_key=True) 
>  bs = relationship("B") 
>
> class B(Base): 
>  __tablename__ = 'b' 
>  id = Column(Integer, primary_key=True) 
>  a_id = Column(ForeignKey('a.id')) 
>
> e = create_engine("sqlite://", echo=True) 
> Base.metadata.create_all(e) 
>
> s = Session(e) 
> s.add_all([A(bs=[B(), B()]), A(bs=[B(), B()])]) 
> s.commit() 
>
> q1 = Session().query(A).options(subqueryload(A.bs)) 
>
> for sess in [Session(e), Session(e), Session(e)]: 
>  for a in q1.with_session(sess): 
>  print a, a.bs 
>
>
>
> Please work up an example showing how you're getting that result, thanks! 
>
>
Thanks for the quick reply, and apologies for not providing an example in 
the first place. I think I worked out the issue: we've wrapped up the 
session:

class TransactionalSession(object):
def __init__(self, session):
self._session = session

def __enter__(self):
return self

def __exit__(self, exception_type, exception, traceback):
if exception is None:
self._session.commit()
else:
self._session.rollback()
self._session.close()

def __getattr__(self, key):
return getattr(self._session, key)

so when we try running the queries with subquery loading (for instance, as 
in your example, but with `TransactionalSession(Session(e))`), the session 
on `subq` is the underlying session, while the session on `context` is our 
own wrapped session. So, presumably the saner way of doing this would be 
either to use the underlying session directly, or to subclass `Session`?

 

>
>
> > 
> > Thanks 
> > 
> > Michael 
> > 
> > -- 
> > 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  
> > <mailto:sqlalchemy+unsubscr...@googlegroups.com >. 
> > To post to this group, send email to sqlal...@googlegroups.com 
>  
> > <mailto:sqlal...@googlegroups.com >. 
> > Visit this group at https://groups.google.com/group/sqlalchemy. 
> > For more options, visit https://groups.google.com/d/optout. 
>

-- 
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] with_session and subquery loading

[michael]

Hello! I sometimes find it more convenient to create to construct a query 
without a session, and then to add the session at the end using 
`with_session`. In particular, when constructing non-trivial queries, not 
having to pass the session around means one less argument to manage. 
However, this doesn't seem to work when using subquery loading, resulting 
in the error `AssertionError: Subquery session doesn't refer to that of our 
context. Are there broken context caching schemes being used?`.

Is there a sensible way to get the subqueries to use the same session, or 
is the answer just to always construct queries with the session?

Thanks

Michael

-- 
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] Commit then Get with Galera

[Michael Wilson]

We’ve been having an interesting problem in a Galera configuration:

1. We get a session.

2. We add a row with session.add()

3. We commit with session.commit()

4. Using the SAME session, we then try and get the same row.

5. We get row not found.

We believe that what’s happening is the get is using a different pool 
connection than the add, so Galera perceives it as a 
different session, and directs it to a different instance, which may not have 
the commit() yet.

We would think that we could avoid this by using the same session, but maybe 
not. Is there a way to force the add() and get to
use the same connection across commit()?

Thanks!


-- 
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] How to correctly merge objects of type X or any subclass of type X into the session?

[Michael Naber]

That's interesting. So, if I'm trying to move instances between threads, is
it recommended that I pass between threads the instance id, and then in the
receiving thread use session.query(Person).get(instance_id), or... should I
pass the instance itself (not by ID), and then use session.merge(instance)?

My objective is that I would want to have full access to access and modify
the instance in the session of the receiving thread.

Thank you,
Michael

On Tue, Feb 16, 2016 at 11:26 AM, Mike Bayer 
wrote:

> answered
>
>
>
> On 02/16/2016 09:08 AM, Michael Naber wrote:
>
>> I would like to write code which can correctly merge objects of type X
>> or any subclass of type X into the session. I have been doing
>> session.merge(X(id=??)), which works fine for merging type X, but if the
>> object ID references an instance of any subclass of X, the merge results
>> in the discriminator being set incorrectly. Code example here:
>> http://stackoverflow.com/questions/35414057
>>
>> Any help much appreciated.
>>
>> Regards,
>> Michael
>>
>> --
>> 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
>> <mailto:sqlalchemy+unsubscr...@googlegroups.com>.
>> To post to this group, send email to sqlalchemy@googlegroups.com
>> <mailto:sqlalchemy@googlegroups.com>.
>> Visit this group at https://groups.google.com/group/sqlalchemy.
>> For more options, visit https://groups.google.com/d/optout.
>>
>
> --
> 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.
>

-- 
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] How to correctly merge objects of type X or any subclass of type X into the session?

[Michael Naber]

I would like to write code which can correctly merge objects of type X or
any subclass of type X into the session. I have been doing
session.merge(X(id=??)), which works fine for merging type X, but if the
object ID references an instance of any subclass of X, the merge results in
the discriminator being set incorrectly. Code example here:
http://stackoverflow.com/questions/35414057

Any help much appreciated.

Regards,
Michael

-- 
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] Re: best way to declare one-to-one relationships with automap and non-explicit relationships

[Michael Bayer]

On Sunday, February 14, 2016 at 4:12:36 PM UTC-5, Brian Cherinka wrote:
>
> Hi, 
>
> I'm trying to use automap a schema, and let it generate all classes and 
> relationships between my tables.  It seems to work well for all 
> relationships except for one-to-one
>  I know to set a one-to-one relationship, you must apply the uselist=True 
> keyword in relationship().  What's the best way to do that when I'm letting 
> automap generate them?  Without having to manually do it myself by removing 
> the automap generated ones, and setting them explicitly.  
>
 

you'd need to implement a generate_relationship function as described at 
http://docs.sqlalchemy.org/en/rel_1_0/orm/extensions/automap.html#custom-relationship-arguments
 
which applies the rules you want in order to establish those relationships 
that you'd like to be one-to-one.

 

 Here is what I'm trying so far, but it's not working. 
>


I don't see anything obviously wrong with it but you'd want to step through 
with pdb.set_trace() to ensure every aspect of it is doing what you'd 
expect.   Otherwise "not working" can mean lots of things.

 

>  
>  
>


> onetoones = ['file']
>
> def _gen_relationship(base, direction, return_fn, attrname, local_cls, 
> referred_cls, **kw):
> if local_cls.__table__.name in onetoones:
> kw['uselist'] = False
> # make use of the built-in function to actually return the result.
> return generate_relationship(base, direction, return_fn, attrname, 
> local_cls, referred_cls, **kw)
>
> def camelizeClassName(base, tablename, table):
> return str(tablename[0].upper() + re.sub(r'_([a-z])', lambda m: 
> m.group(1).upper(), tablename[1:]))
>
> _pluralizer = inflect.engine()
> def pluralize_collection(base, local_cls, referred_cls, constraint):
> referred_name = referred_cls.__name__
> uncamelized = re.sub(r'[A-Z]', lambda m: "_%s" % m.group(0).lower(), 
> referred_name)[1:]
> pluralized = _pluralizer.plural(uncamelized)
> return pluralized
>
> # Grabs engine
> db = DatabaseConnection()
> engine = db.engine
>
> # Selects schema and automaps it.
> metadata = MetaData(schema='mangadapdb')
> Base = automap_base(bind=engine, metadata=metadata)
>
> # Pre-define Dap class.  Necessary so automap knows to join this table to 
> a declarative base class from another schema
> class Dap(Base):
> __tablename__ = 'dap'
>
> cube_pk = Column(Integer, ForeignKey(datadb.Cube.pk))
> cube = relationship(datadb.Cube, backref='dap', uselist=False)
>
> # Prepare the base
> Base.prepare(engine, reflect=True, classname_for_table=camelizeClassName, 
> name_for_collection_relationship=pluralize_collection, 
> generate_relationship=_gen_relationship)
>
> # Explicitly declare classes
> for cl in Base.classes.keys():
> exec('{0} = Base.classes.{0}'.format(cl))
>
>

-- 
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] Use order_by when using relationship() with secondary-parameter in a many-to-many?

[Michael Bayer]

order_by, you spelled it wrong.

> On Feb 14, 2016, at 3:53 AM,   wrote:
> 
> Hi Mike
> 
>> On 2016-02-13 14:30 Mike Bayer  wrote:
>> you can order by secondary, just add it in:
>> 
>> authors = relationship("Person", secondary=ReferenceAuthor, 
>> order_by=ReferenceAuthor.c.Index)
> 
> Are you sure? I get this error about it
> 
> TypeError: relationship() got an unexpected keyword argument 'oder_by'
> 
> -- 
> 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.

-- 
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] AttributeError thrown by relationship during unit testing

[Michael Bayer]

Two options -  either use back_populates format (search the docs) to set up 
bidirectional relationships, or call configure_mappers() after the model 
classes have been defined.

> On Jan 11, 2016, at 3:47 PM, Sam Raker  wrote:
> 
> Using SQLAlchemy 1.0.9's declarative extension, I've got a many-to-one 
> relationship defined thusly:
> 
> class Article(Base):
>...
>publisher_id = schema.Column(sqltypes.Integer, 
> schema.ForeignKey('publishers.id'))
> ...
> class Publisher(Base):
>__tablename__ = 'publishers'
>...
>articles = relationship('Article', backref='publisher')
> 
> 
> I've got some code I'd like to test that looks like this:
> 
> articles = 
> session.query(Article).filter(...).join(Article.publisher).filter(Publisher.name
>  ==...)...
> 
> The code works fine, but while trying to test it with python 2.7's unittest 
> module, I keep getting 
> AttributeError: type object 'Article' has no attribute 'publisher'
> 
> How do I resolve this? I'm importing both Article and Publisher into my test 
> file. I _really_ don't want to have to connect to a local db just to get 
> instrumented attributes to work properly, and I don't want to have to 
> refactor my code to use Article.publisher_id--as it'd involve either 
> hard-coding (brittle) or two separate ORM/SQL calls (obnoxious)--just to make 
> tests work.
> 
> -- 
> 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.

-- 
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] AttributeError thrown by relationship during unit testing

[Michael Bayer]

Sent from my iPhone

> On Jan 11, 2016, at 3:47 PM, Sam Raker  wrote:
> 
> Using SQLAlchemy 1.0.9's declarative extension, I've got a many-to-one 
> relationship defined thusly:
> 
> class Article(Base):
>...
>publisher_id = schema.Column(sqltypes.Integer, 
> schema.ForeignKey('publishers.id'))
> ...
> class Publisher(Base):
>__tablename__ = 'publishers'
>...
>articles = relationship('Article', backref='publisher')
> 
> 
> I've got some code I'd like to test that looks like this:
> 
> articles = 
> session.query(Article).filter(...).join(Article.publisher).filter(Publisher.name
>  ==...)...
> 
> The code works fine, but while trying to test it with python 2.7's unittest 
> module, I keep getting 
> AttributeError: type object 'Article' has no attribute 'publisher'
> 
> How do I resolve this? I'm importing both Article and Publisher into my test 
> file. I _really_ don't want to have to connect to a local db just to get 
> instrumented attributes to work properly, and I don't want to have to 
> refactor my code to use Article.publisher_id--as it'd involve either 
> hard-coding (brittle) or two separate ORM/SQL calls (obnoxious)--just to make 
> tests work.
> 
> -- 
> 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.

-- 
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] Using Hybrids for encryption with varying keys

[Michael Wilson]

Hi,

I read with great interest these examples:

https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/SymmetricEncryption 


Which use hybrid_property or TypeDecorators to implement encryption.

My question is, now would you use these with a varying key - one that possibly 
changed per row (or primary key).

Thanks for any ideas!

-- 
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] combining column_property and TypeDecorator

[Michael Bayer]

Look into the type_coerce() function for that.

> On Nov 10, 2015, at 5:46 PM, Uri Okrent  wrote:
> 
> Seems like a simple thing to do but I can't seem to find an example.  Say I 
> have a simple column_property like the example in the docs:
> 
> fullname = column_property(firstname + " " + lastname)
> 
> 
> and I want to perform additional (trivial in this example) processing on the 
> result at the orm level like for instance:
> class FullNameWithTitle(TypeDecorator):
> impl = Text
> 
> def process_result_value(self, value, dialect):
> return "Mr. " + value
> 
> 
> Is there a simple way to specify the FullNameWithTitle type for the fullname 
> column_property?
> 
> Thanks
> -- 
> 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 http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] combining column_property and TypeDecorator

[Michael Bayer]

Sent from my iPhone

> On Nov 10, 2015, at 5:46 PM, Uri Okrent  wrote:
> 
> Seems like a simple thing to do but I can't seem to find an example.  Say I 
> have a simple column_property like the example in the docs:
> 
> fullname = column_property(firstname + " " + lastname)
> 
> 
> and I want to perform additional (trivial in this example) processing on the 
> result at the orm level like for instance:
> class FullNameWithTitle(TypeDecorator):
> impl = Text
> 
> def process_result_value(self, value, dialect):
> return "Mr. " + value
> 
> 
> Is there a simple way to specify the FullNameWithTitle type for the fullname 
> column_property?
> 
> Thanks
> -- 
> 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 http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Re: SQLAlchemy under Jython fails with Maximum Recursion Depth Exceeded

[Michael Naber]

It could be due to this:
http://sourceforge.net/p/jython/mailman/message/34131065/

On Tue, Oct 6, 2015 at 11:35 AM, Michael Naber  wrote:

> Hopefully this should be pretty straightforward. I'm running the following
> example which fails under Jython 2.7.0 with postgresql-9.4-1203.jdbc42.jar
> using sqlalchemy 1.0.8 with PostgreSQL 9.3.9 on Ubuntu 14.04. Any help
> would be much appreciated.
>
>
> Example and stack trace below:
>
>
> from sqlalchemy import create_engine, ForeignKey
> from sqlalchemy.orm import sessionmaker, scoped_session, relationship
> from sqlalchemy.ext.declarative import declarative_base
> from sqlalchemy import Column
> from sqlalchemy.types import Integer, String, Float
>
> Session = scoped_session(sessionmaker())
> Base = declarative_base()
>
> class Address(Base):
> __tablename__ = 'address'
> id = Column(Integer, primary_key=True)
> email_address = Column(String, nullable=False)
> person_id = Column(Integer, ForeignKey('person.id'))
>
>
> class Person(Base):
> __tablename__ = 'person'
> id = Column(Integer, primary_key=True)
> _rate = Column(Float)
> name = Column(String, nullable=False)
> addresses = relationship("Address", order_by="Address.id",
> backref="person")
>
> engine = 
> create_engine('postgresql+zxjdbc://michael:test123@localhost:5432/ajtest',
> echo=True)
> #engine = create_engine('postgresql://michael:test123@localhost:5432/ajtest',
> echo=True)
> Session.configure(bind=engine)
> Base.metadata.bind = engine
>
> Base.metadata.drop_all(checkfirst=True)
> Base.metadata.create_all()
>
> s = Session()
> mickeybob = Person(name='Michael')
>
> s.add(mickeybob)
> s.add(Address(email_address='t...@example.com',
>  person=mickeybob))
>
> s.commit()
>
>
> for a in s.query(Address).all():
> print a.email_address, a.person.name
>
>
>
> --
>
> Here is the error I get when running under Jython:
>
>
>
>
>
> $ jython test_sqla.py
> 2015-10-06 15:32:58,750 INFO sqlalchemy.engine.base.Engine select
> current_schema()
> 2015-10-06 15:32:58,753 INFO sqlalchemy.engine.base.Engine ()
> 2015-10-06 15:32:58,808 INFO sqlalchemy.engine.base.Engine SELECT
> CAST('test plain returns' AS VARCHAR(60)) AS anon_1
> 2015-10-06 15:32:58,813 INFO sqlalchemy.engine.base.Engine ()
> 2015-10-06 15:32:58,816 INFO sqlalchemy.engine.base.Engine SELECT
> CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
> 2015-10-06 15:32:58,819 INFO sqlalchemy.engine.base.Engine ()
> 2015-10-06 15:32:58,821 INFO sqlalchemy.engine.base.Engine show
> standard_conforming_strings
> 2015-10-06 15:32:58,822 INFO sqlalchemy.engine.base.Engine ()
> 2015-10-06 15:32:58,835 INFO sqlalchemy.engine.base.Engine select relname
> from pg_class c join pg_namespace n on n.oid=c.relnamespace where
> pg_catalog.pg_table_is_visible(c.oid) and relname=?
> 2015-10-06 15:32:58,845 INFO sqlalchemy.engine.base.Engine (u'address',)
> 2015-10-06 15:32:58,865 INFO sqlalchemy.engine.base.Engine select relname
> from pg_class c join pg_namespace n on n.oid=c.relnamespace where
> pg_catalog.pg_table_is_visible(c.oid) and relname=?
> 2015-10-06 15:32:58,874 INFO sqlalchemy.engine.base.Engine (u'person',)
> Traceback (most recent call last):
>   File "test_sqla.py", line 29, in 
> Base.metadata.drop_all(checkfirst=True)
>   File
> "/home/michael/jython2.7.0/Lib/site-packages/sqlalchemy/sql/schema.py",
> line 3711, in drop_all
> bind._run_visitor(ddl.SchemaDropper,
>   File
> "/home/michael/jython2.7.0/Lib/site-packages/sqlalchemy/engine/base.py",
> line 1856, in _run_visitor
> conn._run_visitor(visitorcallable, element, **kwargs)
>   File
> "/home/michael/jython2.7.0/Lib/site-packages/sqlalchemy/engine/base.py",
> line 1480, in _run_visitor
> visitorcallable(self.dialect, self,
>   File
> "/home/michael/jython2.7.0/Lib/site-packages/sqlalchemy/sql/visitors.py",
> line 121, in traverse_single
> return meth(obj, **kw)
>   File
> "/home/michael/jython2.7.0/Lib/site-packages/sqlalchemy/sql/ddl.py", line
> 813, in visit_metadata
> filter_fn=lambda constraint: False
>   File
> "/home/michael/jython2.7.0/Lib/site-packages/sqlalchemy/sql/ddl.py", line
> 813, in visit_metadata
> filter_fn=lambda constraint: False
>   File
> "/home/michael/jython2.7.0/Lib/site-packages/sqlalchemy/sql/ddl.py", line
> 1067, in sort_tables_and_constraints
> candidate_sort = list(
>   File
> "/home/michael/jython2.7.0/Lib/site-packages/sqlalchemy/sql/ddl.py&q

[sqlalchemy] SQLAlchemy under Jython fails with Maximum Recursion Depth Exceeded

[Michael Naber]

Hopefully this should be pretty straightforward. I'm running the following
example which fails under Jython 2.7.0 with postgresql-9.4-1203.jdbc42.jar
using sqlalchemy 1.0.8 with PostgreSQL 9.3.9 on Ubuntu 14.04. Any help
would be much appreciated.


Example and stack trace below:


from sqlalchemy import create_engine, ForeignKey
from sqlalchemy.orm import sessionmaker, scoped_session, relationship
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column
from sqlalchemy.types import Integer, String, Float

Session = scoped_session(sessionmaker())
Base = declarative_base()

class Address(Base):
__tablename__ = 'address'
id = Column(Integer, primary_key=True)
email_address = Column(String, nullable=False)
person_id = Column(Integer, ForeignKey('person.id'))


class Person(Base):
__tablename__ = 'person'
id = Column(Integer, primary_key=True)
_rate = Column(Float)
name = Column(String, nullable=False)
addresses = relationship("Address", order_by="Address.id", backref="person")

engine = 
create_engine('postgresql+zxjdbc://michael:test123@localhost:5432/ajtest',
echo=True)
#engine = create_engine('postgresql://michael:test123@localhost:5432/ajtest',
echo=True)
Session.configure(bind=engine)
Base.metadata.bind = engine

Base.metadata.drop_all(checkfirst=True)
Base.metadata.create_all()

s = Session()
mickeybob = Person(name='Michael')

s.add(mickeybob)
s.add(Address(email_address='t...@example.com',
 person=mickeybob))

s.commit()


for a in s.query(Address).all():
print a.email_address, a.person.name



--

Here is the error I get when running under Jython:





$ jython test_sqla.py
2015-10-06 15:32:58,750 INFO sqlalchemy.engine.base.Engine select
current_schema()
2015-10-06 15:32:58,753 INFO sqlalchemy.engine.base.Engine ()
2015-10-06 15:32:58,808 INFO sqlalchemy.engine.base.Engine SELECT
CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2015-10-06 15:32:58,813 INFO sqlalchemy.engine.base.Engine ()
2015-10-06 15:32:58,816 INFO sqlalchemy.engine.base.Engine SELECT
CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2015-10-06 15:32:58,819 INFO sqlalchemy.engine.base.Engine ()
2015-10-06 15:32:58,821 INFO sqlalchemy.engine.base.Engine show
standard_conforming_strings
2015-10-06 15:32:58,822 INFO sqlalchemy.engine.base.Engine ()
2015-10-06 15:32:58,835 INFO sqlalchemy.engine.base.Engine select relname
from pg_class c join pg_namespace n on n.oid=c.relnamespace where
pg_catalog.pg_table_is_visible(c.oid) and relname=?
2015-10-06 15:32:58,845 INFO sqlalchemy.engine.base.Engine (u'address',)
2015-10-06 15:32:58,865 INFO sqlalchemy.engine.base.Engine select relname
from pg_class c join pg_namespace n on n.oid=c.relnamespace where
pg_catalog.pg_table_is_visible(c.oid) and relname=?
2015-10-06 15:32:58,874 INFO sqlalchemy.engine.base.Engine (u'person',)
Traceback (most recent call last):
  File "test_sqla.py", line 29, in 
Base.metadata.drop_all(checkfirst=True)
  File
"/home/michael/jython2.7.0/Lib/site-packages/sqlalchemy/sql/schema.py",
line 3711, in drop_all
bind._run_visitor(ddl.SchemaDropper,
  File
"/home/michael/jython2.7.0/Lib/site-packages/sqlalchemy/engine/base.py",
line 1856, in _run_visitor
conn._run_visitor(visitorcallable, element, **kwargs)
  File
"/home/michael/jython2.7.0/Lib/site-packages/sqlalchemy/engine/base.py",
line 1480, in _run_visitor
visitorcallable(self.dialect, self,
  File
"/home/michael/jython2.7.0/Lib/site-packages/sqlalchemy/sql/visitors.py",
line 121, in traverse_single
return meth(obj, **kw)
  File "/home/michael/jython2.7.0/Lib/site-packages/sqlalchemy/sql/ddl.py",
line 813, in visit_metadata
filter_fn=lambda constraint: False
  File "/home/michael/jython2.7.0/Lib/site-packages/sqlalchemy/sql/ddl.py",
line 813, in visit_metadata
filter_fn=lambda constraint: False
  File "/home/michael/jython2.7.0/Lib/site-packages/sqlalchemy/sql/ddl.py",
line 1067, in sort_tables_and_constraints
candidate_sort = list(
  File "/home/michael/jython2.7.0/Lib/site-packages/sqlalchemy/sql/ddl.py",
line 1067, in sort_tables_and_constraints
candidate_sort = list(
  File
"/home/michael/jython2.7.0/Lib/site-packages/sqlalchemy/util/topological.py",
line 50, in sort
for set_ in sort_as_subsets(tuples, allitems, deterministic_order):
  File
"/home/michael/jython2.7.0/Lib/site-packages/sqlalchemy/util/topological.py",
line 39, in sort_as_subsets
todo.difference_update(output)
  File
"/home/michael/jython2.7.0/Lib/site-packages/sqlalchemy/util/_collections.py",
line 458, in difference_update
set.difference_update(self, other)
  File
"/home/michael/jython2.7.0/Lib/site-packages/sqlalchemy/util/_collections.py",
line 458, in difference_update
set.d

Re: [sqlalchemy] 10 Reasons to love SQLAlchemy

[Michael Bayer]

Thanks very much for this post, Paul!

Sent from my iPhone

> On Sep 4, 2015, at 3:43 PM, Paul Johnston  wrote:
> 
> Hi guys,
> 
> I decided to express my love of SQLAlchemy through a blog post:
> http://pajhome.org.uk/blog/10_reasons_to_love_sqlalchemy.html
> 
> Enjoy,
> 
> Paul
> -- 
> 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 http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.