Hi Simon,
Thanks for responding to my post. It turns out that MyBatis can do exactly what you are saying which essentially sounds like a bulk ETL process. Again, the key difference is that MyBatis doesn’t require that the mapping be done with all the DB-specific definitions which I frankly prefer. There is a tool, the MyBatis generator, that does exactly this and I have used it when I didn’t want to write my own mapping files since the tables had hundreds of fields. In many cases, you are correct in that I was only loading data. The data was retrieved by raw SQL and could involve joins with other tables much as in a view. I just needed a data transfer mechanism to translate the SQL results to a POJO. Your experience differed in that you did need to create the tables with your Python code. I agree that SQLAlchemy is perfect for that. I created the tables ahead of time usually with command-line psql or, as you said, the tables already existed. In fact, I’d sometimes create temp tables with the schema of existing tables and I also did that with command-line psql in a Bash script. Thanks for your insights. Rgds mjg On Wednesday, August 23, 2023 at 12:49:23 PM UTC-4 Simon King wrote: > My perspective: the SQLAlchemy ORM really comes into its own when you are > making use of its Unit of Work system to load a batch of objects from the > database, manipulate those objects, and then flush your changes back to the > database. If you are only *loading* data then you don't need a lot of the > functionality of the ORM, and you might consider using SQLAlchemy Core > instead. > > Using SQLAlchemy Core to execute SQL strings is very simple: > > https://docs.sqlalchemy.org/en/20/core/connections.html#basic-usage > > You can use the objects that come back from those calls directly (they > have attributes named after the columns from the query), or you could > trivially convert them into instances of some class that you've defined. > > It sounds like the sort of work you do involves writing code to access > pre-existing databases, in which case writing SQL directly makes a lot of > sense, and you have no need for the schema-definition parts of SQLAlchemy. > But there are other classes of application for which the schema-definition > tools are very useful. I have written many applications for which the > database didn't already exist, so allowing SQLAlchemy to create the tables > was the obvious way to go (with Alembic for migrations as the schema > changed over time). SQLAlchemy also gives a certain amount of independence > from the underlying database, meaning that I can run most of my tests using > SQLite despite using Postgres or MySQL in production. > > In summary: use the right tool for the job :-) > > Simon > > > On Mon, Aug 21, 2023 at 6:48 PM Mike Graziano <mjg2...@gmail.com> wrote: > >> Hi Mike, >> >> >> >> Thanks for that info. It was just what I needed. I also want to thank >> you for your YouTube tutorials on SQLAlchemy. They are fantastic. >> >> >> >> I don’t want to make this a huge post, but I have a real pet peeve >> concerning ORMs. I come from a Java background where I used MyBatis as my >> ORM. What I love about MyBatis was >> >> >> >> - I could use raw SQL which I personally feel is superior. My >> argument here is simple: Why learn another “language” for issuing SQL >> statements when we have already spent a fair amount of time learning SQL. >> Also, raw SQL is easily testable with either command line or GUI tools? >> >> - The ORM should just use the mapped models in order to execute SQL >> using mapping that in and of themselves doesn’t/shouldn’t care about the >> tables. Unless you are creating a table with the ORM which I have found to >> be rare, the ORM shouldn’t care about the table structure other than field >> names with the possibility of aliases and data types. Why define more than >> what we need in order to populate a plain old object (POO – language >> agnostic). Why include characteristics like primary key, nullability, >> etc? Some Pydantic-like validation is handy, but can be table agnostic. >> Let’s extract the data via SQL and return POOs. In that regard, I liken >> the ORM to a Data Transfer Object (DTO). >> >> - As I have already mentioned, how often do you really use an >> application to create tables. Often, they already exist. Furthermore, it >> is just more natural to use command‑line SQL or a GUI to create the >> tables. In fact, it is not uncommon to use a GUI like PgAdmin or DBeaver >> to create the database elements that you need and then use that tool to >> derive all sorts of scripts to perform common activities such as backup, >> restore, etc. that can be scheduled. >> >> >> >> There is a very handy Java framework call BeanIO ( http://beanio.org/) >> that I feel exemplifies the points I am trying to make. With BeanIO, it is >> possible to extract data from a variety of file formats and populate >> POJOs. BeanIO is only interested in the layout of the data. It is a >> convenience framework that allows for OOP design of an application. I feel >> that MyBatis does this also. It has substantial DB integration, but >> strives to connect the POJO to the database without enforcing design. >> Using so-called Entity’s enforces a design that ORMs should not be forced >> to obey if all you are looking for is a translation from SQL to a POO. >> >> >> >> Once again, thanks for you help and sorry for my ranting, but as I’ve >> said I have a pet peeve with ORMs that are enforcing more than I think is >> necessary to translate SQL to a POO. >> >> On Thursday, August 17, 2023 at 8:04:58 PM UTC-4 Mike Bayer wrote: >> >>> the raw SQL to ORM mapping pattern has a lot of limitations but it is >>> documented at >>> https://docs.sqlalchemy.org/en/20/orm/queryguide/select.html#getting-orm-results-from-textual-statements >>> >>> . >>> >>> >>> >>> On Thu, Aug 17, 2023, at 4:26 PM, Mike Graziano wrote: >>> >>> To all, >>> >>> I am new to Python and SQLAlchemy. I was a Java developer who used the >>> MyBatis ORM. I was using PONY ORM for a while, but was concerned that >>> SQLAlchemy is the gold standard for ORMs and Python, but there is something >>> about MyBatis that I can't seem to find in SQLAlchemy, but maybe I have not >>> googled enough. >>> >>> In short, I don't want to use SQLAlchemy's select. I want to issue raw >>> SQL and have SQLAlchemy's ORM capability populate a collection of objects. >>> That may not be pythonic, but I'd like to know how to do it with >>> declarative mapping. Is it possible and, if so, I'd love to see an >>> example. >>> >>> Thx & rgds >>> >>> mjg >>> >>> >>> -- >>> 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/17383085-4a83-49d0-bf0a-1653552b7d59n%40googlegroups.com >>> >>> <https://groups.google.com/d/msgid/sqlalchemy/17383085-4a83-49d0-bf0a-1653552b7d59n%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+...@googlegroups.com. >> > To view this discussion on the web visit >> https://groups.google.com/d/msgid/sqlalchemy/105bbc06-869b-4512-ad51-5a6ab6ea3eb7n%40googlegroups.com >> >> <https://groups.google.com/d/msgid/sqlalchemy/105bbc06-869b-4512-ad51-5a6ab6ea3eb7n%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/25dad1de-19ff-4d09-b753-9e066da934abn%40googlegroups.com.
