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
