On Oct 12, 8:19 am, [EMAIL PROTECTED] 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. -- http://mail.python.org/mailman/listinfo/python-list