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+unsubscr...@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/04296190-4021-4847-a204-55d36876c745%40app.fastmail.com.
