[Zope-DB] How lunch SQL request from python script ?

[Charlie Clark]

Am 18.01.2007, 11:52 Uhr, schrieb CERETTO Thierry <t.ceretto at chu-nancy.fr>:

> select distinct c.id_risque, c.libelle_risque, c.id_fiche, c.id_portee,
> c.url_info
> from (mots a join amotrisque b on a.id_mot = b.id_mot )
>             join risques c on b.id_risque = c.id_risque
> where a.mot_MAJ like '%@@CRITERE1@@%'
> union
> select distinct c.id_risque, c.libelle_risque, c.id_fiche, c.id_portee,
> c.url_info
> from (mots a join amotrisque b on a.id_mot = b.id_mot )
>             join risques c on b.id_risque = c.id_risque
> where a.mot_MAJ like '%@@CRITERE2@@%'
> union
> ....
> *******************
> where  "%@@CRITERE1@@'" , %@@CRITERE2@@, ...,  are words filled in
> textfield (separate by a space).


If you are simply adding results of the same query but different results  
then do not use UNION!!!
Run the same query three times with different parameters and simply add  
the results together in a script.

Your ZSQL should look something like this

select distinct c.id_risque, c.libelle_risque, c.id_fiche, c.id_portee,
c.url_info

 from (mots a join amotrisque b on a.id_mot = b.id_mot )

              join risques c on b.id_risque = c.id_risque

where a.mot_MAJ like <dtml-sqlvar '%@@' + CRITERE + '@@%'> # or use  
<dtml-sqltest> if you prefer

call this from a PythonScript

criteria = ['n'import quoi', 'depardieu', 'segolene', 'sarko']

results = []
for c in criteria:
      results = context.ZSQLMethod(CRITERE=c)

This will work for fairly simple SQL but if you want to create your SQL  
dynamically (and I don't think this is such a good idea) then you should  
use a different template than ZSQL. With our mxODBC Zope DA you can pass  
your SQL directly into the execute method of a DA connection object.

Charlie