Re: [sqlalchemy] Working with an existing database, reflection and usage of ORM Declarative Mapping

[Mike Bayer]

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 
> , 
> I misunderstood this part: "*Table reflection refers to the process of 
> generating Table 
> 
>  and related objects by reading the current state of a database. Whereas in 
> the previous sections we’ve been declaring 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 
>>> )
>>> - 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 
>>> )
>>> - 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, 

Re: [sqlalchemy] Working with an existing database, reflection and usage of ORM Declarative Mapping

[Pierre Massé]

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 
, 
I misunderstood this part: "*Table reflection refers to the process of 
generating Table 
 
and 
related objects by reading the current state of a database. Whereas in the 
previous sections we’ve been declaring 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 

)
- 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 

)
- 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 

Re: [sqlalchemy] Working with an existing database, reflection and usage of ORM Declarative Mapping

[Mike Bayer]

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 
> )
> - 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 
> )
> - 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
>  
> .

-- 
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 

[sqlalchemy] Working with an existing database, reflection and usage of ORM Declarative Mapping

[Pierre Massé]

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 

)
- 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 

)
- 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.