Mailing List Archive: How lunch SQL request from python script ?

How lunch SQL request from python script ?

Jan 18, 2007, 2:52 AM

Post #1 of 4 (2686 views)

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

Re: How lunch SQL request from python script ? [ In reply to ]

robert at redcor

Jan 18, 2007, 3:59 AM

Post #2 of 4 (2572 views)

Permalink

-----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@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-----
_______________________________________________
Zope-DB mailing list
Zope-DB@zope.org
http://mail.zope.org/mailman/listinfo/zope-db

Re: How lunch SQL request from python script ? [ In reply to ]

maciej.wisniowski at coig

Jan 18, 2007, 4:46 AM

Post #3 of 4 (2553 views)

Permalink

> 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?):
>
1. You may read some about dtml (especially loops) and try to write your
ZSQLMethod that gets one parameter (with value of textfield.split(' ')
for example) and loops by it to generate proper SQL.

2. Create ZSQLMethod 'myzsqlmethod' that takes one sql parameter like
'myquery' and it's content is:
<dtml-var myquery>.
Then call it like:
context.myzsqlmethod(myquery=sqlquery)

where sqlquery is string generated by code below:

> 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)
>
In this case you must be very carefull because:
1. this ZSQLMethod is a high security risk, and should be secured with
proper permissions and called by script with proper proxy roles
2. there may be possiblity for SQLInjection in your script above (say
somebody will write something harmful to textfield)

>
> 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 ????
>
Recently, there were few questions about managing transactions from
ZSQLMethods etc. Search
for these posts. There were examples how to call your sql directly. In
general, you should use External
method.

--
Maciej Wisniowski
_______________________________________________
Zope-DB mailing list
Zope-DB@zope.org
http://mail.zope.org/mailman/listinfo/zope-db

Re: How lunch SQL request from python script ? [ In reply to ]

charlie at egenix

Jan 18, 2007, 5:11 AM

Post #4 of 4 (2580 views)

Permalink

Am 18.01.2007, 11:52 Uhr, schrieb CERETTO Thierry <t.ceretto@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
_______________________________________________
Zope-DB mailing list
Zope-DB@zope.org
http://mail.zope.org/mailman/listinfo/zope-db