Hi, My employer is preparing to upgrade from tryton 2.4 to 3.x. So, I spent some time with Python-SQL. Disclaimer: this are my own opinions, not my employer's. Note also that I write complex queries quite a lot, therefore this long message.
I assume one of the main reasons Python-SQL was built, was to make it easier to generate dynamic SQL queries (i.e. exact columns/table joins etc. are not known before-hand). This is IMO where Python-SQL shines and I appreciate Tryton's ORM is using Python-SQL internally. I completely fail to see any advantages of why all module 'static' SQL queries have been replaced with Python-SQL code though. Am I missing something here? If I was required to work with Python-SQL, my work-flow would change into: 1) Write my SQL queries in a dedicated database tool 2) Test it runs and check the results. 3) Convert the query to Python-SQL, resulting in more lines of code, various additional python imports, creating lots of object instances, requiring me to do things manually that SQL apparently takes care of automatically (at least, I've seen production code like this: invoice_join.left.left.left.left.right... to add some joins to a specific table). All this for the same end result as the previous steps. 4) Test the Python-SQL code runs and double-check the result is the same as SQL. If I'd directly write in Python-SQL I'd miss important database tools like EXPLAIN and trouble-shooting would be (IMO) much harder and more time-consuming. To summarize: Why convert an industry standard 4GL language to a proprietary 3GL-alike construction, when the very same query itself can be written in an ANSI/ISO-standard way that should work on any DB engine without modification? I could live with this if field selection, joins, aggregate functions, etc. could be performed directly on ORM objects+fields, like other ORMs offer, but as it is now we are required to use SQL for this and in that case I prefer to write SQL. Any thoughts on this? Kind regards, Vincent
