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 <mkmoi...@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/ <http://www.sqlalchemy.org/>
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve
> <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
> <mailto:sqlalchemy+unsubscr...@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/E3920F92-FF1D-4129-AFF1-FF389FB85C62%40mushroomsys.com.
