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.
