Deviation from object-relational mapping (pySQLFace)

[sulyokpeti at gmail.com]

On okt. 15, 09:04, J Peyret <jpey... at gmail.com> wrote:
> 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.
I guess you have not seen the examples: https://fedorahosted.org/pySQLFace/browser/examples
They help to understand what I wrote.

>
> 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?
Yes, you get the columns back without documentation for a programmer,
who does not now a thing about RBDMS.
If you are good at both python and that specific RDBMS you use, you
can make it.
XML is independent from both the programming language API-s and the
RDBMS specific 'retrieve the metadata' solutions.
This is a key to freely combine any programming language with any
RDBMS. If you put RDBMS specific features in your python code, your
solution will depend on that feature. If you don't, you loose the
feature.

>
> 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.
True. But again the programmer has to know how to use a database,
which is not always the case.

>
> 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.
First time, I made the examples this way, but the code was ugly. So I
switched to lists (positional parameters).

>
> 4.  Plain ol' SQL can be cut and pasted in a query editor and can be
> tested there.
Yes. The DB designer does it, and exports the SQL,DML with
documentation embedded in XML for the programming developers.
Excellent idea!

>
> 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.
Who knows better any DB schema changes than a DB designer who exports
that XML?
If the change has an impact on the SQL interface, the programmers have
to be alerted of course.

>
> 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".
I guess you do not have the proof of this theorem.

>
> 7.  ANSI SQL is actually quite portable, if you know what to avoid
> doing.
...and by using that, you loose the RDBMS specific features.

>
> 8.  Last, but not least.  Performance.
This is a bluff.

>
> 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?
I think there is a reason for server side programming too.

>
> 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.
Connection string is also stored in my XML.

>
> 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.
DB experts should not hide from SQL, but it is better to keep other
people away.

>
> Bottom line:  SQL is extremely dynamic in nature, even more so than
> Python.  Why shackle it to static XML files?
To develop both sides (DB design and client coding) independently.

>
> P.S.
>
> SQL Alchemy _is_ something I've been meaning to look at, because it
> seems like they also _like_ SQL.
They do not _like_ SQL. They _like_ python.