Deviation from object-relational mapping (pySQLFace)

[J Peyret]

On Oct 12, 8:19 am, sulyokp... at gmail.com wrote:

> I would like to get some opinions on this approach.
> Thanks.

I realize I will be minority here, but...

I've never quite understood why folks want to repeat the database's
metadata in XML files.  I've gotten much better results just using
plain ol' SQL throughout, sprinkled in with generated-on-the-fly SQL.


1.  A select clause identifies what is coming back from the db in the
cursor's description.  20 lines of code shoves that in a dictionary
for each row for any result set.  'Select * from <table>' works 90% of
the time for 1 table queries.  What does XML add?

2.  Inserts and deletes are relatively trivial to derive from
INFORMATION SCHEMA lookups on any given table and templates can be
generated for them.  Updates are admittedly less trivial, but not
horribly so.

3.  Query parameters can be added by simple %(<colname>)s embedded in
the query templates.    That works great with dictionaries.  You can
extract them with a regular expression and replace them with '?' and a
list, if your DB-API flavor requires that.

4.  Plain ol' SQL can be cut and pasted in a query editor and can be
tested there.

5.  If you unit test somewhat aggressively, any db-schema changes will
result in unhappy queries dying because they don't see the columns
that they expect in the resultsets.  That keeps your Python code in
synch without feeding a layer of XML cruft.

6.  XML is plain nasty for "simple local usage" where you don't need
to communicate with a 3rd party app or module.  Conversely, XML is
great when you need to communicate data "somewhere else, potentially
with recursive and nested structures".

7.  ANSI SQL is actually quite portable, if you know what to avoid
doing.

8.  Last, but not least.  Performance.

In complex processing on a database with large volumes, the last thing
you want to do is to fetch data to your client codeline, process it
there, and spew it back to the database.  Instead you want to shoot
off series of updates/deletes/insert-selects queries to the server and
you want to rely on set-based processing rather than row-by-row
approaches.  How do ORMs+XML help here?

My biggest hassle has been managing connection strings and catching
the weird Exception structures every Python db module figures it has
to re-implement, not the SQL itself.

Granted, if this were Java, you would need special data transfer
objects to encapsulate the results.  But is not Java.  And, also
granted, I _enjoy_ coding in SQL rather than trying to hide from it,
so YMMV.

Bottom line:  SQL is extremely dynamic in nature, even more so than
Python.  Why shackle it to static XML files?

P.S.

SQL Alchemy _is_ something I've been meaning to look at, because it
seems like they also _like_ SQL.