Hi Warwick,
Thank you for the detailed response.
My main application is read only. I only use SQLAlchemy Core, and I have
some helper functions that wrap boiler plate, similar to those you
mentioned. I'm starting a new write heavy application and am considering
following the approach you have outlined, instead of using the ORM.
I am curious though, if we can avoid boiler plate in Core by writing our
own wrappers, and moreover if we do not care about serializing database
results into classes but prefer to work with the named tuples, is there any
benefit to using the ORM?
>From my understanding, the ORM provides the Identity Map pattern, which
give certain benefits. For example if you query a parent and child table,
iterate over this list in memory and update each child, and then call save,
the Identity Map is smart enough to flush all the entites to disk without
you having to update row by hand. Personally I would prefer to do these
kind of manipulations in bulk SQL, so I do not at this moment see how the
Identity Map can provide a benefit for my style of programming. I'm still
learning however and would enjoy to hear what you have to say.
Thanks and best regards,
Matthew Moisen
On Thursday, April 22, 2021 at 9:08:48 PM UTC-7 warwickp wrote:
> Hi Matthew
>
> It’s not an unusual approach from my standpoint. I use a combination of
> both ORM and Core. For my core uses, I wrote a couple of generic getter
> and setter methods, wrapping up a lot of boilerplate operations. In my
> case, it looks like this;
>
> result = DBGetColumns(‘tableName’, [‘studentFirstname’, ‘studentSurname’],
> id=studentID)
>
> i.e. DBGetColumns(‘tableName’, [list of columns to return], **column:value
> to use in the where clause AND'ed together)
>
> It returns result dict of the column values, or None if no row is found.
> For my purposes, this can only be used where you expect a fetchone()
> result. A result array can not be returned at this time and I’ve never
> really needed it.
>
> In this case, the method takes the kwargs (id=studentID) and builds a
> where clause from it, based on the query it creates on the table. My
> environment has a lot of ‘built in’ context, so for example, my method can
> work out from it’s operating context the metadata to use to open the table.
> Without this context, it would look more like this;
>
> result = DBGetColumns(meta, ‘tableName’, [‘studentFirstname’,
> ‘studentSurname’], id=studentID)
>
> or maybe
>
> result = DBGetColumns(tableObject, [‘studentFirstname’, ‘studentSurname’],
> id=studentID) where tableObject is a Table() object.
>
> Of course, this is for basic selects. The where clause is always built as
> ‘AND’s. There’s no logic for an OR etc, and no joins. This could be done
> of course, but after 10 years I’ve never needed it.
>
> Finally, it can also be used to return the actual value of a single
> column, rather than a dict of multi columns. This is done by supplying the
> column name as a string, rather than a list of strings. That way, you can
> build robust code with inline queries very easily.
>
> e.g.
> if DBGetColumns(‘tableName’, ‘studentFirstname’, id=studentID) == ‘Fred’:
> print ‘Hi Fred'
>
> For update/insert, I have a similar method;
>
> Update existing row based on a match of the keys supplied as kwargs. In
> this version, if the id (kwargs) have no match, then it will raise an
> exception. The expectation is that you believe that studentID already
> exists and up want to do an update.
>
> DBSetColumns(‘tableName’, {‘studentCategory’: 1, ‘studentOtherThing’:
> ‘baa’}, id=studentID)
>
> Insert a new row specifically. i.e. I assume this does not exist. Logic
> dictates that all the mandatory columns and PK columns are included in the
> column dict. Note there are no kwargs.
>
> newStudentID = DBSetColumns(‘tableName’, {‘studentCategory’: 1,
> ‘studentOtherThing’: ‘baa’})
>
> If I included id=studentID in the kwargs, it would attempt an update, and
> if it fails it performs an insert instead. You can tell be the result
> what it did. newID for an insert, or None if an update.
>
> Finally, returning a complete row
>
> row = DBGetRow(‘tableName’, id=studentID)
>
> Simply returns an SQLA resultRow object based on building a query on the
> table, applying the kwargs as a where clause and returning 1 row, or None.
> If there is more than one match, then the first one is returned. The
> assumption is that the dev uses this with an understanding of the data.
>
> I also have a function value return’er
>
> value = DBGetFuncValue(‘tableName’, function, onColumn, **kwargs)
>
> This is useful for quickly and easily getting the MAX, MIN, AVG etc of a
> column based on a select query.
>
> e.g. (Silly example, but you can see what I mean)
>
> if DBGetFuncValue(‘students’, ‘MAX’, ‘lastTestResult’,
> studentCategoryID=23) < passingGrade:
> print “This bunch of students all failed!"
>
> I use these literally all day every day and they have proven to be a real
> asset. Recently, I wanted to support a way of raising notifications (in my
> case adding things into a Queue.Queue() when operations happened on some
> tables. I simply updated the central method and it worked, even though
> there were 1000’s of cases where it was used. :-)
>
> With all of the above, there are some more subtle use cases that I have
> not really covered. e.g. If I pass in a tuple if (‘tableName’,
> sessionObject) then the code will do the insert/update inside a transaction
> for that session, rather than a direct update etc.
>
> Hope you gain some insight and inspiration. Happy to chat 1:1 if you
> would like more info.
>
> Cheers
> Warwick
>
> On 23 Apr 2021, at 11:51 AM, mkmo...@gmail.com <mkmo...@gmail.com> wrote:
>
> Hi dAll,
>
> Some people don't like ORMs and instead use query builders like SQLAlchemy
> Core (or even raw SQL text) even for something like a CRUD application.
>
> For sake of argument, let's put aside whether or not this is a good idea.
> I'm just curious how one would go about this without copy and pasting a
> large amount of functions for every table in your data model.
>
> For example if you had two tables, School and Student, you would probably
> have functions like insert_school, insert_student, update_school,
> update_student, get_school, get_student, delete_school, delete_student, and
> etc. where the majority of the implementing code is the same except for the
> table and field names.
>
> Do people who use SQLAlchemy Core as a query builder for CRUD applications
> simply write out all these functions? Or do they try to create some kind
> of generic function that can introspect the table? Is there another
> approach I may not be considering?
>
> Thanks and best regards,
>
> Matthew Moisen
>
> --
> 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/ab775a14-4045-49d4-946a-0adc0ef887ecn%40googlegroups.com
>
> <https://groups.google.com/d/msgid/sqlalchemy/ab775a14-4045-49d4-946a-0adc0ef887ecn%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/0eff8e69-3018-4e6d-8790-a8137b842869n%40googlegroups.com.
