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

Maciej Wisniowski maciej.wisniowski at coig.katowice.pl
Thu Jan 18 07:46:05 EST 2007


> 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


More information about the Zope-DB mailing list