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

[robert rottermann]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Thierry,
I think all problems you describe can be solved with ZSQL easily.
the best thing for yo is to grap the documentation on how to do that.
you find it here
http://www.plope.com/Books/2_7Edition/RelationalDatabases.stx

look into it, and if you find some of your questions not answered I'll
gladly help (if I can)
robert

CERETTO Thierry schrieb:
> 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 L (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 L L
> 
>  
> 
> Thierry
> 
>  
> 
>  
> 
>  
> 
>  
> 
>  
> 
> 
> ------------------------------------------------------------------------
> 
> _______________________________________________
> Zope-DB mailing list
> Zope-DB at zope.org
> http://mail.zope.org/mailman/listinfo/zope-db

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.5 (GNU/Linux)
Comment: Using GnuPG with SUSE - http://enigmail.mozdev.org

iD8DBQFFr2ETGaryJ0T9kUYRAgeeAKCLS5QxERJYw3+Zl+pUkERhrYlTFQCdGs+j
6SaqgT3khOrNeld3jUxgLNo=
=f2MP
-----END PGP SIGNATURE-----