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

[CERETTO Thierry]

Hi and all my wishes for you for 2007,

 

 

Zope 2.8.6-final, python 2.3.5, win32, ZMySQLDA, MySQL for python.

 

In ZMI (I know, it's not very well but I'm a newbie...), I use  "Z MySQL
database connection", "Z SQL method", "Page Template", etc, that's works
fine but in "Z SQL method" I use only a simple SQL query, 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.<dtml-sqltest mot_MAJ type="string" op="like">

 

*******************

 

But now, I need to submit a dynamic SQL query 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 '%@@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).

 

 

1) From ONE argument, it's possible to generate SQL dynamic request in
"Z SQL method, if yes, how? (I think that the answer is NO but I'm not
sure)

 

2) I think to do that, I must to use a python script, I have a piece of
code (above) but I don't know to connect MySql database and I don't know
how to lunch my SQL query in python :-( (and I suppose which I can
generate a HTML results in this same python script OR it's better to
call a Page template to display results?):

 

for value in textfield.split(' '):

    sqlquery.append("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 '%%%s%%' " % value) 

sqlquery = " union ".join(sqlquery)     

 

I read many things on web, I try, but without success, example, in ZMI,
in python script object, I write this:

# "my_base" is a "Z MySQL database connection" 

bd = context.my_base()

curs = bd.connection.cursor()

 

but i get a login box ????

 

How to connect a Mysql base and run my dynamic SQL request?

 

Have you an example so that I can start and understand how this works.

 

Thank you very much!

 

A desperate French guy :-( :-(

 

Thierry

 

 

 

 

 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mail.zope.org/pipermail/zope-db/attachments/20070118/89afb15d/attachment-0001.htm