ill clarify that para in the reflection part On Tue, Mar 21, 2023, at 11:15 AM, Pierre Massé wrote: > Thanks for this quick answer Mike! > > Obivously, I wasted way to much energy in trying to make everything work with > table reflection... :( Let's say I gained experience from it. > > Regarding the origin of the impression I might have gotten, I'd say I tried > to read a big chunk of the docs (unified tutorial and ORM docs) prior to > moving to ORM, and got some nudges towards this understanding: > - I do not think that I read something like "__tablename__ and attribute > names must be the same like in the underlying database" anywhere (but was not > thorough in my reading of the docs). I will try to see what exception I will > get if I try to map a non-existing field :) > - In the unified tutorial > <https://docs.sqlalchemy.org/en/20/tutorial/metadata.html#table-reflection>, > I misunderstood this part: "*Table reflection refers to the process of > generating Table > <https://docs.sqlalchemy.org/en/20/core/metadata.html#sqlalchemy.schema.Table> > and related objects by reading the current state of a database. Whereas in > the previous sections we’ve been declaring Table > <https://docs.sqlalchemy.org/en/20/core/metadata.html#sqlalchemy.schema.Table>** > objects in Python and then emitting DDL to the database, the reflection > process does it in reverse.*" like if it was one of 2 choices > - And the overall quality of the other docs entries that described so well > how to "post declare" attributes, how to defer reflection, etc... that > enabled a beginner to pull of such a complex application > > Not sure if this misconception is widespread, but maybe some tweaking of the > docs may have prevented this error. I'd be happy to contribute, but have > never pull requested to an open source repo. > > Anyway, thanks for the answer, wish you a pleasant day! > > Regards, > > Pierre > Le mardi 21 mars 2023 à 13:54:59 UTC+1, Mike Bayer a écrit : >> __ >> hi - >> >> I think things would be easier if you defined your ORM mappings/ tables >> without relying upon reflection. There is no such requirement that >> reflection is used for an existing database, you just want to have ORM table >> metadata that matches the schema. the ORM/table metadata does not have to >> match the schema exactly either, it can omit columns and tables you aren't >> using. >> >> reflection is more of a utility function that can be used for some special >> cases but for a first class app /database combination it would not be >> prevalent. It's a bit curious where you might have gotten the impression >> that "reflection is mandatory when working with an existing database". >> >> On Tue, Mar 21, 2023, at 7:58 AM, Pierre Massé wrote: >>> Dear all, >>> >>> I am quite new to ORMs and SQLAlchemy, and I have a maybe somewhat naive >>> question set regarding how to build an application around a database. >>> >>> Just a few words of context (may not yield importance, but who knows): I am >>> building a mobile app, for which the server side will be an AWS Lambda >>> function serving GraphQL queries. Data persistence is achieved through a >>> hosted PostgreSQL instance. Server side code is python and "database >>> access" through SQLAlchemy. The first version of the database schema has >>> been "manually" built and populated with some test data (via simple SQL >>> queries in pgAdmin4). >>> >>> Regarding SQLAlchemy usage, first version was using Core only, but I >>> decided to move to ORM, and I got it quite hard - maybe because of poor >>> choices on my end. >>> >>> What I do, now that I have a working example: >>> - when the Lambda is fired, I import a module defining "bare" ORM classes, >>> with no attribute apart the table name - inheriting from *`Reflected`* and >>> a declarative base (using deferred reflection >>> <https://docs.sqlalchemy.org/en/20/orm/declarative_tables.html#using-deferredreflection>) >>> - engine is set up and connection established to the db >>> - *`Reflected`* class is prepared through *Reflected.prepare(engine=engine)* >>> - New attributes, relationships, etc... are added to the "bare" ORM classes >>> as *`column_property`*, after reflection (this way >>> <https://docs.sqlalchemy.org/en/20/orm/mapped_sql_expr.html#adding-column-property-to-an-existing-declarative-mapped-class>) >>> - Mapper is then ready, data is queried and mutated using sessions >>> >>> My questions are: >>> - is reflection mandatory when working with an existing database? (I think >>> this would be like an overwhelmingly prevalent case for Production >>> applications?) >>> - is it possible to have a mixed approach regarding the mapping >>> definitions: some attributes being defined in the ORM classes prior to the >>> reflection, and reflection then completes those classes with other fields >>> from the database schema? >>> - when using reflection, is the only way to define new attributes, >>> relationships, etc... to add those attributes after this reflection via >>> adding column_properties after class definition, like described above? >>> - I feel like I am losing much of the "Declarative Mapping" by working the >>> way I do, what do you think about it? >>> - overall, what could be simplified regarding the ways of working I set up? >>> >>> Some code snippets below: >>> >>> _Bare ORM class definitions:_ >>> class MessageOrm(Reflected, Base): >>> __tablename__ = "single_recipient_message" >>> >>> >>> class HeaderOrm(Reflected, Base): >>> __tablename__ = "single_recipient_message_header" >>> __ >>> _Post reflection addition of relationships and attributes_ >>> HeaderOrm.messages = relationship( >>> MessageOrm, >>> foreign_keys=[MessageOrm.header_id], >>> back_populates="header", >>> ) >>> MessageOrm.sent_by_who = column_property( >>> case( >>> (MessageOrm.sender_id == current_id, "me"), >>> else_="other", >>> ) >>> ) >>> MessageOrm.header = relationship( >>> "HeaderOrm", >>> foreign_keys=[MessageOrm.header_id], >>> back_populates="messages", >>> ) >>> __ >>> Thanks a lot! >>> >>> Regards, >>> >>> Pierre >>> __ >>> __ >>> __ >>> __ >>> __ >>> >>> >>> -- >>> 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/433f9917-7ac0-4d1b-b41e-16d8ae255d15n%40googlegroups.com >>> >>> <https://groups.google.com/d/msgid/sqlalchemy/433f9917-7ac0-4d1b-b41e-16d8ae255d15n%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/85653361-8966-4451-a95f-996ad52eb35cn%40googlegroups.com > > <https://groups.google.com/d/msgid/sqlalchemy/85653361-8966-4451-a95f-996ad52eb35cn%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/d5c48697-fa86-4ecd-bfdb-8242a72fd745%40app.fastmail.com.