On Monday, October 20, 2014, Vincent (CloudSuite) < vvanderl...@cloudsuite.com> wrote:
> Hi, > > 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? > I believe you are just facing that initial resistance. The moment you start using Python-SQL it is very addictive and at Openlabs We have started using it even outside tryton (in some cases replacing SQL alchemy dialects) because of the simplicity and light weight. > 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 you are writing SQL queries in strings in your software, I presume you have only 1 database flavor to worry about, and may be it is Postgres. You could do exactly the same if you leave a psycopg2 connection on your Python interactive terminal, write a method which prints your query as a string and connects and executes it on database. > > 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? > We are already taking advantage of the several benefits of Python-SQL in Nereid, which is not practical with writing sql queries in strings. A big advantage Of Tryton is the way new modules add functionality allowing simpler modules to be extended. A good example is where the nereid catalog module implements a method that returns a query used to filter products in a node. There are downstream modules which further implement their own functionality which includes sales ranking. Before Python-SQL they had to be separate methods, now we could call the super method, get the query as a python-Sql select instance and then change columns and order without reimplementing all of the query again. In addition, maintaining SQL strings in the code base was really hard and Python-SQL in most cases is pythonic and readable. My 2c. Sharoon Thomas Openlabs -- Sent from Gmail Mobile
