Search
Code Directory
 ASP
 ASP.NET
 C/C++
 CFML
 CGI/PERL
 Delphi
 Development
 Flash
 HTML
 Java
 JavaScript
 Pascal
 PHP
 Python
 SQL
 Tools
 Visual Basic & VB.NET
 XML
New Code
HTMLPad 2018 15.5
WeBuilder 2018 15.5
SentiMask SDK Trial 2.0.0
Track Order For Magento 2 1.0.0
Calendar 365 For Dynamics CRM 4.0
Scimbo 1.2
Odoo Furnito Theme 1.0
.Net Assemblies/WSDL Import Utility for Delphi 6.0.4.0
Devart Excel Add-in for Zendesk 1.7
Excel Add-in for Marketo 1.7
GetOrgChart 2.5.3
AnyGantt JS Gantt Charts 8.6.0
AnyChart JS Charts and Dashboards 8.6.0
Multi Vendor Ecommerce Script & Apps 4.0
Magento 2 Inventory Management Extension 1.0
Top Code
VisualNEO Web 2018.12.15
Paste phpSoftPro 1.4.1
Extreme Injector 3.7
Deals and Discounts Website Script 1.0.2
ADO.NET Provider for ExactTarget 1.0
Solid File System OS edition 5.1
Classified Ad Lister 1.0
Aglowsoft SQL Query Tools 8.2
Invoice Manager by PHPJabbers 3.0
ICPennyBid Penny Auction Script 4.0
PHP Review Script 1.0
ATN Resume Finder 2.0
ATN Site Builder 3.0
Availability Booking Calendar PHP 1.0
PHP GZ Blog Script 1.1
Report About Generator expressions for database requests
- required fields

Please enter text on the image
  



This recipe is a follow-up to #440653, which was easy to implement but very slow because the iteration required to read all the rows of a table

As suggested by Matteo Dell'Amico in a comment, it would be much better if we could write something like

query(r.name for r in plane_tbl if r.country == "France")


where the generator expression is first translated into an SQL select, so that the iteration on the instance of query only reads the rows selected by the SQL statement

The present recipe is an attempt to achieve this. The first problem is to get the source code of the generator expression. I use information from the stack frame to get the file name and the line number, then the tokenize module to read the elements of the generator expression in the source code

Then, to build the SQL statement, the source code must be parsed : this is done using the compiler package and "visitors" that walk the AST tree returned by compiler.parse and do operations on the nodes, depending on their type

Finally, once the SQL statement is built, the iteration on the query instance can start : for the first one, the SQL statement is executed ; then the iteration yields the selected rows one by one.

The items can be :
- objects, with attribute names matching those in the generator expression, except that qualified names (table.name) are converted to table_name
- dictionaries : the keys are the same as the attribute names above
- lists

For instance :
- iterating on query(name for r in plane_tbl) returns objects with an attribute name
- iterating on query(r.name for r in plane_tbl) returns objects with an attribute r_name

This is because of iteration on tables which have the same field names

query((r.name,c.name) for r in plane_tbl for c in country_tbl if r.speed > 500 )

The type of the items is set by query.return_type = object, dict or list

Back