Mailing List Archive

It is just because 'delete_this' is a string, not the list, or vice versa?

----- Original Message -----
From: "Ken Winter" <ken@sunward.org>
To: "'Zope-DB List'" <zope-db@zope.org>
Sent: Saturday, June 16, 2007 5:15 AM
Subject: [Zope-DB] Using <dtml-var>s in ZSQL methods?


Here's the definition of a ZSQL method, "people_delete_by_id.zsql":

"
<dtml-comment>
title: Method to delete People by ids given in a comma-separated list
connection_id: my_database
arguments:
id_list
</dtml-comment>

delete from person
where person_id in (<dtml-var id_list>)
"


Here's the puzzle:

When I define and test this ZSQL method through the ZMI, passing it a string
such as "9765, 10058, 11333", it indeed deletes the rows with those values
on person_id.

BUT when I invoke the same ZSQL method from a Python script with the
following code:

"
p = context.REQUEST.get('delete_this')
if p:
s = ', '.join([str(x) for x in p])
context.people_delete_by_id(id_list=s)
"

...and with a REQUEST variable 'delete_this' that comes from this snippet of
a form in a page template:

"
<form method="post" name="form2"
tal:attributes="action string:${here/absolute_url}/${template/id};">

<input type="hidden" name="form.submitted" value="1" />
<p class="error_message" tal:define="err errors/n|nothing"
tal:condition="err"><b tal:content="err" /></p>

<table>
<tr tal:repeat="person options/data"><a name="id" id="id"
tal:attributes="name person/person_id"></a>
<td tal:condition="not:options/selectall | nothing"
<input type="checkbox" name="delete_this:list:int" value=""
tal:attributes="value person/person_id"/>
</td>
...
</tr>
</table>
...
</form>
"

...and with the same three person_ids (9765, 10058, 11333) the 4th line of
the Python script evokes an error:

"
KeyError: 'id_list'
"

The core idea is that the string id_list is passed into the ZSQL method,
where it is spliced into the "delete...where" clause via the <dtml-var
id_list>) to provide the list of person_ids to delete.


Here's the question:

WHY?

~ TIA
~ Ken

P.S. I know that <dtml-var...> rather than <dtml-sqlvar...> is an
unorthodox and apparently undocumented construct to use within a ZSQL
definition. But I need it because <dtml-sqlvar...> malformats the string.
And if doing this trick is a no-no, how come it works in the ZMI test but
not when called from Python? More importantly, how can I get it to work
from Python?

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

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

Jaroslav ~

I'm pretty sure that 'delete_this' is a list (of integers), because the
lines

"
p = context.REQUEST.get('delete_this')
s = ', '.join([str(x) for x in p])
"

process it correctly into the string 's'. In other words, I think it is the
same as if p had been assigned a list in this way:

"
p = [9765, 10058, 11333]
s = ', '.join([str(x) for x in p])
"

In either case, s ends up as the string "9765, 10058, 11333". That's what
gets passed to the ZSQL method (below), and that's where the trouble seems
to be.

I will put in another message to this thread documenting some further
experiments I have done.

~ Thanks
~ Ken


> -----Original Message-----
> From: Jaroslav Lukesh [mailto:lukesh@seznam.cz]
> Sent: Saturday, June 16, 2007 1:41 AM
> To: Ken Winter; 'Zope-DB List'
> Subject: Re: [Zope-DB] Using <dtml-var>s in ZSQL methods?
>
> It is just because 'delete_this' is a string, not the list, or vice versa?
>
> ----- Original Message -----
> From: "Ken Winter" <ken@sunward.org>
> To: "'Zope-DB List'" <zope-db@zope.org>
> Sent: Saturday, June 16, 2007 5:15 AM
> Subject: [Zope-DB] Using <dtml-var>s in ZSQL methods?
>
>
> Here's the definition of a ZSQL method, "people_delete_by_id.zsql":
>
> "
> <dtml-comment>
> title: Method to delete People by ids given in a comma-separated list
> connection_id: my_database
> arguments:
> id_list
> </dtml-comment>
>
> delete from person
> where person_id in (<dtml-var id_list>)
> "
>
>
> Here's the puzzle:
>
> When I define and test this ZSQL method through the ZMI, passing it a
> string
> such as "9765, 10058, 11333", it indeed deletes the rows with those values
> on person_id.
>
> BUT when I invoke the same ZSQL method from a Python script with the
> following code:
>
> "
> p = context.REQUEST.get('delete_this')
> if p:
> s = ', '.join([str(x) for x in p])
> context.people_delete_by_id(id_list=s)
> "
>
> ...and with a REQUEST variable 'delete_this' that comes from this snippet
> of
> a form in a page template:
>
> "
> <form method="post" name="form2"
> tal:attributes="action string:${here/absolute_url}/${template/id};">
>
> <input type="hidden" name="form.submitted" value="1" />
> <p class="error_message" tal:define="err errors/n|nothing"
> tal:condition="err"><b tal:content="err" /></p>
>
> <table>
> <tr tal:repeat="person options/data"><a name="id" id="id"
> tal:attributes="name person/person_id"></a>
> <td tal:condition="not:options/selectall | nothing"
> <input type="checkbox" name="delete_this:list:int" value=""
> tal:attributes="value person/person_id"/>
> </td>
> ...
> </tr>
> </table>
> ...
> </form>
> "
>
> ...and with the same three person_ids (9765, 10058, 11333) the 4th line of
> the Python script evokes an error:
>
> "
> KeyError: 'id_list'
> "
>
> The core idea is that the string id_list is passed into the ZSQL method,
> where it is spliced into the "delete...where" clause via the <dtml-var
> id_list>) to provide the list of person_ids to delete.
>
>
> Here's the question:
>
> WHY?
>
> ~ TIA
> ~ Ken
>
> P.S. I know that <dtml-var...> rather than <dtml-sqlvar...> is an
> unorthodox and apparently undocumented construct to use within a ZSQL
> definition. But I need it because <dtml-sqlvar...> malformats the string.
> And if doing this trick is a no-no, how come it works in the ZMI test but
> not when called from Python? More importantly, how can I get it to work
> from Python?
>
> _______________________________________________
> Zope-DB mailing list
> Zope-DB@zope.org
> http://mail.zope.org/mailman/listinfo/zope-db

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

First, Jaroslav is right. One time you're passing a string and next
your passing a list. Make your choice how the API should looks like.

Second..it would be *really* helpful to see the full traceback instead
of just *Keyerror*. It's somewhat boring to ask every time for it :-)

-aj

--On 16. Juni 2007 15:19:10 -0400 Ken Winter <ken@sunward.org> wrote:

> Jaroslav ~
>
> I'm pretty sure that 'delete_this' is a list (of integers), because the
> lines
>
> "
> p = context.REQUEST.get('delete_this')
> s = ', '.join([str(x) for x in p])
> "
>
> process it correctly into the string 's'. In other words, I think it is
> the same as if p had been assigned a list in this way:
>
> "
> p = [9765, 10058, 11333]
> s = ', '.join([str(x) for x in p])
> "
>
> In either case, s ends up as the string "9765, 10058, 11333". That's what
> gets passed to the ZSQL method (below), and that's where the trouble seems
> to be.
>
> I will put in another message to this thread documenting some further
> experiments I have done.
>
> ~ Thanks
> ~ Ken
>
>
>> -----Original Message-----
>> From: Jaroslav Lukesh [mailto:lukesh@seznam.cz]
>> Sent: Saturday, June 16, 2007 1:41 AM
>> To: Ken Winter; 'Zope-DB List'
>> Subject: Re: [Zope-DB] Using <dtml-var>s in ZSQL methods?
>>
>> It is just because 'delete_this' is a string, not the list, or vice
>> versa?
>>
>> ----- Original Message -----
>> From: "Ken Winter" <ken@sunward.org>
>> To: "'Zope-DB List'" <zope-db@zope.org>
>> Sent: Saturday, June 16, 2007 5:15 AM
>> Subject: [Zope-DB] Using <dtml-var>s in ZSQL methods?
>>
>>
>> Here's the definition of a ZSQL method, "people_delete_by_id.zsql":
>>
>> "
>> <dtml-comment>
>> title: Method to delete People by ids given in a comma-separated list
>> connection_id: my_database
>> arguments:
>> id_list
>> </dtml-comment>
>>
>> delete from person
>> where person_id in (<dtml-var id_list>)
>> "
>>
>>
>> Here's the puzzle:
>>
>> When I define and test this ZSQL method through the ZMI, passing it a
>> string
>> such as "9765, 10058, 11333", it indeed deletes the rows with those
>> values on person_id.
>>
>> BUT when I invoke the same ZSQL method from a Python script with the
>> following code:
>>
>> "
>> p = context.REQUEST.get('delete_this')
>> if p:
>> s = ', '.join([str(x) for x in p])
>> context.people_delete_by_id(id_list=s)
>> "
>>
>> ...and with a REQUEST variable 'delete_this' that comes from this snippet
>> of
>> a form in a page template:
>>
>> "
>> <form method="post" name="form2"
>> tal:attributes="action string:${here/absolute_url}/${template/id};">
>>
>> <input type="hidden" name="form.submitted" value="1" />
>> <p class="error_message" tal:define="err errors/n|nothing"
>> tal:condition="err"><b tal:content="err" /></p>
>>
>> <table>
>> <tr tal:repeat="person options/data"><a name="id" id="id"
>> tal:attributes="name person/person_id"></a>
>> <td tal:condition="not:options/selectall | nothing"
>> <input type="checkbox" name="delete_this:list:int" value=""
>> tal:attributes="value person/person_id"/>
>> </td>
>> ...
>> </tr>
>> </table>
>> ...
>> </form>
>> "
>>
>> ...and with the same three person_ids (9765, 10058, 11333) the 4th line
>> of the Python script evokes an error:
>>
>> "
>> KeyError: 'id_list'
>> "
>>
>> The core idea is that the string id_list is passed into the ZSQL method,
>> where it is spliced into the "delete...where" clause via the <dtml-var
>> id_list>) to provide the list of person_ids to delete.
>>
>>
>> Here's the question:
>>
>> WHY?
>>
>> ~ TIA
>> ~ Ken
>>
>> P.S. I know that <dtml-var...> rather than <dtml-sqlvar...> is an
>> unorthodox and apparently undocumented construct to use within a ZSQL
>> definition. But I need it because <dtml-sqlvar...> malformats the
>> string. And if doing this trick is a no-no, how come it works in the ZMI
>> test but not when called from Python? More importantly, how can I get
>> it to work from Python?
>>
>> _______________________________________________
>> Zope-DB mailing list
>> Zope-DB@zope.org
>> http://mail.zope.org/mailman/listinfo/zope-db
>
> _______________________________________________
> Zope-DB mailing list
> Zope-DB@zope.org
> http://mail.zope.org/mailman/listinfo/zope-db



--
ZOPYX Ltd. & Co. KG - Charlottenstr. 37/1 - 72070 Tübingen - Germany
Web: www.zopyx.com - Email: info@zopyx.com - Phone +49 - 7071 - 793376
Registergericht: Amtsgericht Stuttgart, Handelsregister A 381535
Geschäftsführer/Gesellschafter: ZOPYX Limited, Birmingham, UK
------------------------------------------------------------------------
E-Publishing, Python, Zope & Plone development, Consulting

Am 16.06.2007, 21:19 Uhr, schrieb Ken Winter <ken@sunward.org>:

> p = [9765, 10058, 11333]
> s = ', '.join([str(x) for x in p])
> "
> In either case, s ends up as the string "9765, 10058, 11333". That's
> what
> gets passed to the ZSQL method (below), and that's where the trouble
> seems
> to be.

I find this code a bit convoluted and somewhat dangerous if you are
passing data from a web form. What's wrong with repeatedly calling a
delete_person() method that just accepts a single id as a <dtml-sqlvar>?
This stuff is coming from a web form so it probably won't be a huge list
so the speed won't matter.

If not, there is no need to explicity convert your parameters in a list
comprehension:
p = ", ".join(p)
or even
p = str(p)[1:-1]

Have you declared id_list explicitly as an argument for your ZSQL method?
Keyword arguments will be ignored by ZSQL methods unless they are
explicitly declared as arguments.

Charlie
--
Charlie Clark
eGenix.com

Professional Python Services directly from the Source
>>> Python/Zope Consulting and Support ... http://www.egenix.com/
>>> mxODBC.Zope.Database.Adapter ... http://zope.egenix.com/
>>> mxODBC, mxDateTime, mxTextTools ... http://python.egenix.com/
________________________________________________________________________

:::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,MacOSX for free ! ::::

eGenix.com Software, Skills and Services GmbH Pastor-Loeh-Str.48
D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg
Registered at Amtsgericht Duesseldorf: HRB 46611
_______________________________________________
Zope-DB mailing list
Zope-DB@zope.org
http://mail.zope.org/mailman/listinfo/zope-db

Andreas ~

Here's the whole traceback (and all the other details from my Plone error
log):

"
Time
2007-06-16 15:33
User Name
admin (admin)
Request URL

http://localhost/DAgroups/portal_factory/People/people.2007-06-16.3625931808
/people_edit
Exception Type
KeyError
Exception Value
'id_list'

Traceback (innermost last):

* Module ZPublisher.Publish, line 115, in publish
* Module ZPublisher.mapply, line 88, in mapply
* Module ZPublisher.Publish, line 41, in call_object
* Module Products.CMFPlone.FactoryTool, line 369, in __call__
* Module ZPublisher.mapply, line 88, in mapply
* Module ZPublisher.Publish, line 41, in call_object
* Module Products.CMFFormController.FSControllerPageTemplate, line 90,
in __call__
* Module Products.CMFFormController.BaseControllerPageTemplate, line 28,
in _call
* Module Products.CMFFormController.ControllerBase, line 232, in getNext
__traceback_info__: [.'id = people_edit', 'status = success',
'button=delete', 'errors={}', 'context=<People at
people.2007-06-16.3625931808>', "kwargs={'portal_status_message': 'People
allegedly deleted: 68775'}", 'next_action=None', '']
* Module Products.CMFFormController.Actions.TraverseTo, line 38, in
__call__
* Module ZPublisher.mapply, line 88, in mapply
* Module ZPublisher.Publish, line 41, in call_object
* Module Products.CMFFormController.FSControllerPythonScript, line 104,
in __call__
* Module Products.CMFFormController.Script, line 145, in __call__
* Module Products.CMFCore.FSPythonScript, line 108, in __call__
* Module Shared.DC.Scripts.Bindings, line 311, in __call__
* Module Shared.DC.Scripts.Bindings, line 348, in _bindAndExec
* Module Products.CMFCore.FSPythonScript, line 164, in _exec
* Module None, line 4, in people_delete_control
<FSControllerPythonScript at /DAgroups/people_delete_control used for
/DAgroups/portal_factory/People/people.2007-06-16.3625931808>
Line 4
* Module Shared.DC.ZRDB.DA, line 481, in __call__
<FSZSQLMethod at /DAgroups/people_delete_by_id used for
/DAgroups/portal_factory/People/people.2007-06-16.3625931808>
* Module DocumentTemplate.DT_String, line 476, in __call__

KeyError: 'id_list'

Display traceback as text
REQUEST
form
delete_this [68775]
form.button.delete 'Delete Selected People'
cookies
tree-s 'eJzTyCkw5NLIKTDiClZ3hANXW3WuAmOuRKCECUjWFEnWNTEbJGvGlQgEegAEWRAt'
__ac 'YWRtaW46cHAyMTA3'
lazy items
SESSION <bound method SessionDataManager.getSessionData of
<SessionDataManager at /session_data_manager>>
other
tree-s 'eJzTyCkw5NLIKTDiClZ3hANXW3WuAmOuRKCECUjWFEnWNTEbJGvGlQgEegAEWRAt'
__factory__info__ {'stack': ['People', 'people.2007-06-16.3625931808',
'people_edit'], 'People': <TempFolder at /DAgroups/portal_factory/People>}
URL5 'http://localhost'
URL4 'http://localhost/DAgroups'
__ac 'YWRtaW46cHAyMTA3'
URL0
'http://localhost/DAgroups/portal_factory/People/people.2007-06-16.362593180
8/people_edit'
URL3 'http://localhost/DAgroups/portal_factory'
URL2 'http://localhost/DAgroups/portal_factory/People'
AUTHENTICATION_PATH ''
AUTHENTICATED_USER <PropertiedUser 'admin'>
SERVER_URL 'http://localhost'
delete_this [68775]
form.button.delete 'Delete Selected People'
ACTUAL_URL
'http://localhost/DAgroups/portal_factory/People/people.2007-06-16.362593180
8/people_edit'
portal_status_message 'People allegedly deleted: 68775'
URL
'http://localhost/DAgroups/portal_factory/People/people.2007-06-16.362593180
8/people_edit'
PUBLISHED <FactoryTool at /DAgroups/portal_factory>
controller_state
<Products.CMFFormController.ControllerState.ControllerState object at
0x0663CEB0>
TraversalRequestNameStack []
BASE0 'http://localhost'
BASE1 'http://localhost/DAgroups'
BASE2 'http://localhost/DAgroups/portal_factory'
BASE3 'http://localhost/DAgroups/portal_factory/People'
BASE4
'http://localhost/DAgroups/portal_factory/People/people.2007-06-16.362593180
8'
BASE5
'http://localhost/DAgroups/portal_factory/People/people.2007-06-16.362593180
8/people_edit'
URL1
'http://localhost/DAgroups/portal_factory/People/people.2007-06-16.362593180
8'
URL0
http://localhost/DAgroups/portal_factory/People/people.2007-06-16.3625931808
/people_edit
URL1
http://localhost/DAgroups/portal_factory/People/people.2007-06-16.3625931808
URL2 http://localhost/DAgroups/portal_factory/People
URL3 http://localhost/DAgroups/portal_factory
URL4 http://localhost/DAgroups
URL5 http://localhost
BASE0 http://localhost
BASE1 http://localhost/DAgroups
BASE2 http://localhost/DAgroups/portal_factory
BASE3 http://localhost/DAgroups/portal_factory/People
BASE4
http://localhost/DAgroups/portal_factory/People/people.2007-06-16.3625931808
BASE5
http://localhost/DAgroups/portal_factory/People/people.2007-06-16.3625931808
/people_edit
environ
HTTP_COOKIE
'tree-s="eJzTyCkw5NLIKTDiClZ3hANXW3WuAmOuRKCECUjWFEnWNTEbJGvGlQgEegAEWRAt";
__ac="YWRtaW46cHAyMTA3"'
SERVER_SOFTWARE 'Zope/(Zope 2.9.6-final, python 2.4.3, win32) ZServer/1.1
Plone/2.5.2'
SCRIPT_NAME ''
REQUEST_METHOD 'POST'
HTTP_KEEP_ALIVE '300'
SERVER_PROTOCOL 'HTTP/1.1'
channel.creation_time 1182022394
CONNECTION_TYPE 'keep-alive'
HTTP_ACCEPT_CHARSET 'ISO-8859-1,utf-8;q=0.7,*;q=0.7'
HTTP_USER_AGENT 'Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.1)
Gecko/20061204 Firefox/2.0.0.1'
HTTP_REFERER
'http://localhost/DAgroups/portal_factory/People/people.2007-06-16.362593180
8/people_edit'
SERVER_NAME 'KenIBM'
REMOTE_ADDR '127.0.0.1'
PATH_TRANSLATED
'\\DAgroups\\portal_factory\\People\\people.2007-06-16.3625931808\\people_ed
it'
SERVER_PORT '80'
CONTENT_LENGTH '89'
HTTP_HOST 'localhost'
HTTP_ACCEPT
'text/xml,application/xml,application/xhtml+xml,text/html;q=0.9,text/plain;q
=0.8,image/png,*/*;q=0.5'
GATEWAY_INTERFACE 'CGI/1.1'
HTTP_ACCEPT_LANGUAGE 'en-us,en;q=0.5'
CONTENT_TYPE 'application/x-www-form-urlencoded'
HTTP_ACCEPT_ENCODING 'gzip,deflate'
PATH_INFO
'/DAgroups/portal_factory/People/people.2007-06-16.3625931808/people_edit'
"

Let me add that I get the same error (down to all the details) if I simply
put a single string or integer constant into the call to the ZSQL Method -
that is, if instead of this

context.people_delete_by_id(id_list=s)

the call looks like this:

context.people_delete_by_id(id_list=str(68775))
or
context.people_delete_by_id(id_list=68775)

That's why I think the problem has nothing to do with the prior processing
of the list into a string.

~ Thanks
~ Ken


> -----Original Message-----
> From: Andreas Jung [mailto:lists@zopyx.com]
> Sent: Saturday, June 16, 2007 3:27 PM
> To: Ken Winter; 'Zope-DB List'
> Subject: RE: [Zope-DB] Using <dtml-var>s in ZSQL methods?
> ...
> Second..it would be *really* helpful to see the full traceback instead
> of just *Keyerror*. It's somewhat boring to ask every time for it :-)
>
> --On 16. Juni 2007 15:19:10 -0400 Ken Winter <ken@sunward.org> wrote:
>
> > Jaroslav ~
> >
> > I'm pretty sure that 'delete_this' is a list (of integers), because the
> > lines
> >
> > "
> > p = context.REQUEST.get('delete_this')
> > s = ', '.join([str(x) for x in p])
> > "
> >
> > process it correctly into the string 's'. In other words, I think it is
> > the same as if p had been assigned a list in this way:
> >
> > "
> > p = [9765, 10058, 11333]
> > s = ', '.join([str(x) for x in p])
> > "
> >
> > In either case, s ends up as the string "9765, 10058, 11333". That's
> what
> > gets passed to the ZSQL method (below), and that's where the trouble
> seems
> > to be.
> >
> > I will put in another message to this thread documenting some further
> > experiments I have done.
> >
> > ~ Thanks
> > ~ Ken
> >
> >
> >> -----Original Message-----
> >> From: Jaroslav Lukesh [mailto:lukesh@seznam.cz]
> >> Sent: Saturday, June 16, 2007 1:41 AM
> >> To: Ken Winter; 'Zope-DB List'
> >> Subject: Re: [Zope-DB] Using <dtml-var>s in ZSQL methods?
> >>
> >> It is just because 'delete_this' is a string, not the list, or vice
> >> versa?
> >>
> >> ----- Original Message -----
> >> From: "Ken Winter" <ken@sunward.org>
> >> To: "'Zope-DB List'" <zope-db@zope.org>
> >> Sent: Saturday, June 16, 2007 5:15 AM
> >> Subject: [Zope-DB] Using <dtml-var>s in ZSQL methods?
> >>
> >>
> >> Here's the definition of a ZSQL method, "people_delete_by_id.zsql":
> >>
> >> "
> >> <dtml-comment>
> >> title: Method to delete People by ids given in a comma-separated list
> >> connection_id: my_database
> >> arguments:
> >> id_list
> >> </dtml-comment>
> >>
> >> delete from person
> >> where person_id in (<dtml-var id_list>)
> >> "
> >>
> >>
> >> Here's the puzzle:
> >>
> >> When I define and test this ZSQL method through the ZMI, passing it a
> >> string
> >> such as "9765, 10058, 11333", it indeed deletes the rows with those
> >> values on person_id.
> >>
> >> BUT when I invoke the same ZSQL method from a Python script with the
> >> following code:
> >>
> >> "
> >> p = context.REQUEST.get('delete_this')
> >> if p:
> >> s = ', '.join([str(x) for x in p])
> >> context.people_delete_by_id(id_list=s)
> >> "
> >>
> >> ...and with a REQUEST variable 'delete_this' that comes from this
> snippet
> >> of
> >> a form in a page template:
> >>
> >> "
> >> <form method="post" name="form2"
> >> tal:attributes="action string:${here/absolute_url}/${template/id};">
> >>
> >> <input type="hidden" name="form.submitted" value="1" />
> >> <p class="error_message" tal:define="err errors/n|nothing"
> >> tal:condition="err"><b tal:content="err" /></p>
> >>
> >> <table>
> >> <tr tal:repeat="person options/data"><a name="id" id="id"
> >> tal:attributes="name person/person_id"></a>
> >> <td tal:condition="not:options/selectall | nothing"
> >> <input type="checkbox" name="delete_this:list:int" value=""
> >> tal:attributes="value person/person_id"/>
> >> </td>
> >> ...
> >> </tr>
> >> </table>
> >> ...
> >> </form>
> >> "
> >>
> >> ...and with the same three person_ids (9765, 10058, 11333) the 4th line
> >> of the Python script evokes an error:
> >>
> >> "
> >> KeyError: 'id_list'
> >> "
> >>
> >> The core idea is that the string id_list is passed into the ZSQL
> method,
> >> where it is spliced into the "delete...where" clause via the <dtml-var
> >> id_list>) to provide the list of person_ids to delete.
> >>
> >>
> >> Here's the question:
> >>
> >> WHY?
> >>
> >> ~ TIA
> >> ~ Ken
> >>
> >> P.S. I know that <dtml-var...> rather than <dtml-sqlvar...> is an
> >> unorthodox and apparently undocumented construct to use within a ZSQL
> >> definition. But I need it because <dtml-sqlvar...> malformats the
> >> string. And if doing this trick is a no-no, how come it works in the
> ZMI
> >> test but not when called from Python? More importantly, how can I get
> >> it to work from Python?
> >>
> >> _______________________________________________
> >> Zope-DB mailing list
> >> Zope-DB@zope.org
> >> http://mail.zope.org/mailman/listinfo/zope-db
> >
> > _______________________________________________
> > Zope-DB mailing list
> > Zope-DB@zope.org
> > http://mail.zope.org/mailman/listinfo/zope-db
>
>
>
> --
> ZOPYX Ltd. & Co. KG - Charlottenstr. 37/1 - 72070 Tübingen - Germany
> Web: www.zopyx.com - Email: info@zopyx.com - Phone +49 - 7071 - 793376
> Registergericht: Amtsgericht Stuttgart, Handelsregister A 381535
> Geschäftsführer/Gesellschafter: ZOPYX Limited, Birmingham, UK
> ------------------------------------------------------------------------
> E-Publishing, Python, Zope & Plone development, Consulting

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

Charlie ~ I tried several experiments based on your suggestion. See results
inserted below. ~ Thanks, Ken

> -----Original Message-----
> From: Charlie Clark [mailto:charlie@egenix.com]
> Sent: Saturday, June 16, 2007 3:54 PM
> To: Ken Winter; 'Zope-DB List'
> Subject: Re: [Zope-DB] Using <dtml-var>s in ZSQL methods?
>
> Am 16.06.2007, 21:19 Uhr, schrieb Ken Winter <ken@sunward.org>:
>
> > p = [9765, 10058, 11333]
> > s = ', '.join([str(x) for x in p])
> > "
> >
> > s
> > gets passed to the ZSQL method, and that's where the trouble
> > seems
> > to be.
>
> I find this code a bit convoluted and somewhat dangerous if you are
> passing data from a web form. What's wrong with repeatedly calling a
> delete_person() method that just accepts a single id as a <dtml-sqlvar>?
> This stuff is coming from a web form so it probably won't be a huge list
> so the speed won't matter.

EXPERIMENT #1: I tried this out by rewriting this part of the .cpy script
to:

"
p = context.REQUEST.get('delete_this')
for id in p:
context.person_delete_by_id(person_id=id)
"

where person_delete_by_id is defined as follows (note that again I'm using a
<dtml-var...> rather than a <dtml-sqlvar> here):

"
<dtml-comment>
title: Method to delete one Person by id
connection_id: dhatabase
arguments:
person_id
</dtml-comment>

delete from person
where person_id = <dtml-var person_id>
"

When I tried to delete a couple of People using this I got the error
message:

"
ProgrammingError: column "none" does not exist
"

Here are the details of the error:

"
Request URL

http://localhost/DAgroups/portal_factory/People/people.2007-06-16.3625931808
/people_edit
Exception Type
ProgrammingError
Exception Value
column "none" does not exist

Traceback (innermost last):

* Module ZPublisher.Publish, line 115, in publish
* Module ZPublisher.mapply, line 88, in mapply
* Module ZPublisher.Publish, line 41, in call_object
* Module Products.CMFPlone.FactoryTool, line 369, in __call__
* Module ZPublisher.mapply, line 88, in mapply
* Module ZPublisher.Publish, line 41, in call_object
* Module Products.CMFFormController.FSControllerPageTemplate, line 90,
in __call__
* Module Products.CMFFormController.BaseControllerPageTemplate, line 28,
in _call
* Module Products.CMFFormController.ControllerBase, line 232, in getNext
__traceback_info__: [.'id = people_edit', 'status = success',
'button=delete', 'errors={}', 'context=<People at
people.2007-06-16.3625931808>', "kwargs={'portal_status_message': 'People
allegedly deleted: 68775, 69390'}", 'next_action=None', '']
* Module Products.CMFFormController.Actions.TraverseTo, line 38, in
__call__
* Module ZPublisher.mapply, line 88, in mapply
* Module ZPublisher.Publish, line 41, in call_object
* Module Products.CMFFormController.FSControllerPythonScript, line 104,
in __call__
* Module Products.CMFFormController.Script, line 145, in __call__
* Module Products.CMFCore.FSPythonScript, line 108, in __call__
* Module Shared.DC.Scripts.Bindings, line 311, in __call__
* Module Shared.DC.Scripts.Bindings, line 348, in _bindAndExec
* Module Products.CMFCore.FSPythonScript, line 164, in _exec
* Module None, line 4, in people_delete_control
<FSControllerPythonScript at /DAgroups/people_delete_control used for
/DAgroups/portal_factory/People/people.2007-06-16.3625931808>
Line 4
* Module Shared.DC.ZRDB.DA, line 495, in __call__
<FSZSQLMethod at /DAgroups/person_delete_by_id used for
/DAgroups/portal_factory/People/people.2007-06-16.3625931808>
* Module Products.ZPsycopgDA.db, line 204, in query

ProgrammingError: column "none" does not exist

REQUEST
form
delete_this [68775, 69390]
form.button.delete 'Delete Selected People'
cookies
tree-s 'eJzTyCkw5NLIKTDiClZ3hANXW3WuAmOuRKCECUjWFEnWNTEbJGvGlQgEegAEWRAt'
__ac 'YWRtaW46cHAyMTA3'
lazy items
SESSION <bound method SessionDataManager.getSessionData of
<SessionDataManager at /session_data_manager>>
other
tree-s 'eJzTyCkw5NLIKTDiClZ3hANXW3WuAmOuRKCECUjWFEnWNTEbJGvGlQgEegAEWRAt'
__factory__info__ {'stack': ['People', 'people.2007-06-16.3625931808',
'people_edit'], 'People': <TempFolder at /DAgroups/portal_factory/People>}
URL5 'http://localhost'
URL4 'http://localhost/DAgroups'
__ac 'YWRtaW46cHAyMTA3'
URL0
'http://localhost/DAgroups/portal_factory/People/people.2007-06-16.362593180
8/people_edit'
URL3 'http://localhost/DAgroups/portal_factory'
URL2 'http://localhost/DAgroups/portal_factory/People'
AUTHENTICATION_PATH ''
AUTHENTICATED_USER <PropertiedUser 'admin'>
SERVER_URL 'http://localhost'
delete_this [68775, 69390]
form.button.delete 'Delete Selected People'
ACTUAL_URL
'http://localhost/DAgroups/portal_factory/People/people.2007-06-16.362593180
8/people_edit'
portal_status_message 'People allegedly deleted: 68775, 69390'
URL
'http://localhost/DAgroups/portal_factory/People/people.2007-06-16.362593180
8/people_edit'
PUBLISHED <FactoryTool at /DAgroups/portal_factory>
controller_state
<Products.CMFFormController.ControllerState.ControllerState object at
0x066379F0>
TraversalRequestNameStack []
BASE0 'http://localhost'
BASE1 'http://localhost/DAgroups'
BASE2 'http://localhost/DAgroups/portal_factory'
BASE3 'http://localhost/DAgroups/portal_factory/People'
BASE4
'http://localhost/DAgroups/portal_factory/People/people.2007-06-16.362593180
8'
BASE5
'http://localhost/DAgroups/portal_factory/People/people.2007-06-16.362593180
8/people_edit'
URL1
'http://localhost/DAgroups/portal_factory/People/people.2007-06-16.362593180
8'
URL0
http://localhost/DAgroups/portal_factory/People/people.2007-06-16.3625931808
/people_edit
URL1
http://localhost/DAgroups/portal_factory/People/people.2007-06-16.3625931808
URL2 http://localhost/DAgroups/portal_factory/People
URL3 http://localhost/DAgroups/portal_factory
URL4 http://localhost/DAgroups
URL5 http://localhost
BASE0 http://localhost
BASE1 http://localhost/DAgroups
BASE2 http://localhost/DAgroups/portal_factory
BASE3 http://localhost/DAgroups/portal_factory/People
BASE4
http://localhost/DAgroups/portal_factory/People/people.2007-06-16.3625931808
BASE5
http://localhost/DAgroups/portal_factory/People/people.2007-06-16.3625931808
/people_edit
environ
HTTP_COOKIE
'tree-s="eJzTyCkw5NLIKTDiClZ3hANXW3WuAmOuRKCECUjWFEnWNTEbJGvGlQgEegAEWRAt";
__ac="YWRtaW46cHAyMTA3"'
SERVER_SOFTWARE 'Zope/(Zope 2.9.6-final, python 2.4.3, win32) ZServer/1.1
Plone/2.5.2'
SCRIPT_NAME ''
REQUEST_METHOD 'POST'
HTTP_KEEP_ALIVE '300'
SERVER_PROTOCOL 'HTTP/1.1'
channel.creation_time 1182027293
CONNECTION_TYPE 'keep-alive'
HTTP_ACCEPT_CHARSET 'ISO-8859-1,utf-8;q=0.7,*;q=0.7'
HTTP_USER_AGENT 'Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.1)
Gecko/20061204 Firefox/2.0.0.1'
HTTP_REFERER
'http://localhost/DAgroups/portal_factory/People/people.2007-06-16.362593180
8/people_edit'
SERVER_NAME 'KenIBM'
REMOTE_ADDR '127.0.0.1'
PATH_TRANSLATED
'\\DAgroups\\portal_factory\\People\\people.2007-06-16.3625931808\\people_ed
it'
SERVER_PORT '80'
CONTENT_LENGTH '120'
HTTP_HOST 'localhost'
HTTP_ACCEPT
'text/xml,application/xml,application/xhtml+xml,text/html;q=0.9,text/plain;q
=0.8,image/png,*/*;q=0.5'
GATEWAY_INTERFACE 'CGI/1.1'
HTTP_ACCEPT_LANGUAGE 'en-us,en;q=0.5'
CONTENT_TYPE 'application/x-www-form-urlencoded'
HTTP_ACCEPT_ENCODING 'gzip,deflate'
PATH_INFO
'/DAgroups/portal_factory/People/people.2007-06-16.3625931808/people_edit'
"

EXPERIMENT #2: I created and executed the *same* person_delete_by_id ZSQL
Method in the ZMI. It deletes the specified Person record and produces no
error.

EXPERIMENT #3: I tweaked person_delete_by_id to use the standard
<dtml-sqlvar...> construct:

"
<dtml-comment>
title: Method to delete one Person by id
connection_id: dhatabase
arguments:
person_id
</dtml-comment>

delete from person
where person_id = <dtml-sqlvar person_id type="int">
"

Called by the same .cpy script as above, this version executed without
producing an error message - but it failed to delete any People from the
database! (I'll spare you the details, but yes I did check to be sure that
person_delete_by_id was being executed, and that the 'delete_this' list
contained a couple of valid existing person_id values.)

EXPERIMENT #4: I tested the revised person_delete_by_id in the ZMI, and it
deleted People with no problem.

EXPERIMENT #5: I tried something completely different: a ZSQL method that
consists of nothing but a single <dtml-var...>. Its filesystem version
looks like this:

"
<dtml-comment>
title: Method whose whole body is passed in
connection_id: dhatabase
arguments: foo
</dtml-comment>

<dtml-var foo>
"

I rewrote the .cpy script to pass in the entire SQL statement:

"
p = context.REQUEST.get('delete_this')
if p:
s = ', '.join([str(x) for x in p])
t = "delete from person where person_id in (%s);" % s
context.empty_q(foo=t)
"

To my amazement, this worked! - no errors, and the People targeted for
deletion were indeed deleted.

Can anybody explain these results?

...
>
> Have you declared id_list explicitly as an argument for your ZSQL method?
> Keyword arguments will be ignored by ZSQL methods unless they are
> explicitly declared as arguments.

I think so. Here's the whole text of (the filesystem version of)
people_delete_by_id.zsql:

"
<dtml-comment>
title: Method to delete People by ids given in a comma-separated list
connection_id: dhatabase
arguments:
id_list
</dtml-comment>

delete from person
where person_id in (<dtml-var id_list>)
"

Isn't that a sufficient argument declaration?



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

Am 17.06.2007, 00:14 Uhr, schrieb Ken Winter <ken@sunward.org>:

> <dtml-comment>
> title: Method to delete one Person by id
> connection_id: dhatabase
> arguments:
> person_id
> </dtml-comment>
> delete from person
> where person_id = <dtml-var person_id>
> "
> When I tried to delete a couple of People using this I got the error
> message:
> "
> ProgrammingError: column "none" does not exist

You should be using <dtml-sqlvar person_id type="int"> here but in any
case no argument is being passed in: the error is coming from the DTML
rendering of a missing key argument.

I don't know about your file system product for this as all my ZSQL's live
in the ZODB but I assume all the meta-data is contained in the
<dtml-comment> section. Any chance you can test your stuff on a standard
Zope (non-filesystem) setup? I think that's where the problem is. FWIW you
might want to call the query or execute methods directly on the DA
connection object if they are supported.

Charlie
--
Charlie Clark
eGenix.com

Professional Python Services directly from the Source
>>> Python/Zope Consulting and Support ... http://www.egenix.com/
>>> mxODBC.Zope.Database.Adapter ... http://zope.egenix.com/
>>> mxODBC, mxDateTime, mxTextTools ... http://python.egenix.com/
________________________________________________________________________

:::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,MacOSX for free ! ::::

eGenix.com Software, Skills and Services GmbH Pastor-Loeh-Str.48
D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg
Registered at Amtsgericht Duesseldorf: HRB 46611
_______________________________________________
Zope-DB mailing list
Zope-DB@zope.org
http://mail.zope.org/mailman/listinfo/zope-db

> -----Original Message-----
> From: Charlie Clark [mailto:charlie@egenix.com]
> Sent: Saturday, June 16, 2007 6:24 PM
> To: Ken Winter; 'Zope-DB List'
> Subject: Re: [Zope-DB] Using <dtml-var>s in ZSQL methods?
>
...
> FWIW you
> might want to call the query or execute methods directly on the DA
> connection object if they are supported.

ZPsycopgDA is my database adapter. I haven't found any documentation of
ZPsycopgDA's methods except its code. In the code, I found a class "DB"
with a "query" method (which in turn uses "execute" and "fetchall" methods,
whose definitions I can't locate).

I tried to invoke the "query" method of my adapter (which is named
"dhatabase") with this code:

"
p = context.REQUEST.get('delete_this')
if p:
s = ', '.join([str(x) for x in p])
t = "delete from person where person_id in (%s);" % s
context.dhatabase.query(query_string=t)
"

...and got this error: "AttributeError: query" (full traceback at end of
message). What's wrong with my method call?

I also have a broader question: When one is composing dynamic SQL in Python
scripts, what are the pros and cons of executing them by going directly to
the database adapter (as suggested above) vs passing it in as the sole
argument of an "empty" ZSQL method? By "empty" ZQL method I mean something
like:

"
<dtml-comment>
title: Query whose whole body is passed in as an argument
connection_id: dhatabase
arguments: foo
</dtml-comment>

<dtml-var foo>
"

~ Thanks
~ Ken

P.S. Full details of error:

"
Time
2007-06-17 13:59
User Name
admin (admin)
Request URL

http://localhost/DAgroups/portal_factory/People/people.2007-06-17.6691253086
/people_edit
Exception Type
AttributeError
Exception Value
query

Traceback (innermost last):
* Module ZPublisher.Publish, line 115, in publish
* Module ZPublisher.mapply, line 88, in mapply
* Module ZPublisher.Publish, line 41, in call_object
* Module Products.CMFPlone.FactoryTool, line 369, in __call__
* Module ZPublisher.mapply, line 88, in mapply
* Module ZPublisher.Publish, line 41, in call_object
* Module Products.CMFFormController.FSControllerPageTemplate, line 90,
in __call__
* Module Products.CMFFormController.BaseControllerPageTemplate, line 28,
in _call
* Module Products.CMFFormController.ControllerBase, line 232, in getNext
__traceback_info__: [.'id = people_edit', 'status = success',
'button=delete', 'errors={}', 'context=<People at
people.2007-06-17.6691253086>', "kwargs={'portal_status_message': 'People
allegedly deleted: 69394, 77084'}", 'next_action=None', '']
* Module Products.CMFFormController.Actions.TraverseTo, line 38, in
__call__
* Module ZPublisher.mapply, line 88, in mapply
* Module ZPublisher.Publish, line 41, in call_object
* Module Products.CMFFormController.FSControllerPythonScript, line 104,
in __call__
* Module Products.CMFFormController.Script, line 145, in __call__
* Module Products.CMFCore.FSPythonScript, line 108, in __call__
* Module Shared.DC.Scripts.Bindings, line 311, in __call__
* Module Shared.DC.Scripts.Bindings, line 348, in _bindAndExec
* Module Products.CMFCore.FSPythonScript, line 164, in _exec
* Module None, line 6, in people_delete_control
<FSControllerPythonScript at /DAgroups/people_delete_control used for
/DAgroups/portal_factory/People/people.2007-06-17.6691253086>
Line 6

AttributeError: query

REQUEST
form
delete_this [69394, 77084]
form.button.delete 'Delete Selected People'
cookies
tree-s 'eJzTyCkw5NLIKTDiClZ3hANXW3WuAmOuRKCECUjWFEnWNTEbJGvGlQgEegAEWRAt'
__ac 'YWRtaW46cHAyMTA3'
lazy items
SESSION <bound method SessionDataManager.getSessionData of
<SessionDataManager at /session_data_manager>>
other
tree-s 'eJzTyCkw5NLIKTDiClZ3hANXW3WuAmOuRKCECUjWFEnWNTEbJGvGlQgEegAEWRAt'
__factory__info__ {'stack': ['People', 'people.2007-06-17.6691253086',
'people_edit'], 'People': <TempFolder at /DAgroups/portal_factory/People>}
URL5 'http://localhost'
URL4 'http://localhost/DAgroups'
__ac 'YWRtaW46cHAyMTA3'
URL0
'http://localhost/DAgroups/portal_factory/People/people.2007-06-17.669125308
6/people_edit'
URL3 'http://localhost/DAgroups/portal_factory'
URL2 'http://localhost/DAgroups/portal_factory/People'
AUTHENTICATION_PATH ''
AUTHENTICATED_USER <PropertiedUser 'admin'>
SERVER_URL 'http://localhost'
delete_this [69394, 77084]
form.button.delete 'Delete Selected People'
ACTUAL_URL
'http://localhost/DAgroups/portal_factory/People/people.2007-06-17.669125308
6/people_edit'
portal_status_message 'People allegedly deleted: 69394, 77084'
URL
'http://localhost/DAgroups/portal_factory/People/people.2007-06-17.669125308
6/people_edit'
PUBLISHED <FactoryTool at /DAgroups/portal_factory>
controller_state
<Products.CMFFormController.ControllerState.ControllerState object at
0x0681DB10>
TraversalRequestNameStack []
BASE0 'http://localhost'
BASE1 'http://localhost/DAgroups'
BASE2 'http://localhost/DAgroups/portal_factory'
BASE3 'http://localhost/DAgroups/portal_factory/People'
BASE4
'http://localhost/DAgroups/portal_factory/People/people.2007-06-17.669125308
6'
BASE5
'http://localhost/DAgroups/portal_factory/People/people.2007-06-17.669125308
6/people_edit'
URL1
'http://localhost/DAgroups/portal_factory/People/people.2007-06-17.669125308
6'
URL0
http://localhost/DAgroups/portal_factory/People/people.2007-06-17.6691253086
/people_edit
URL1
http://localhost/DAgroups/portal_factory/People/people.2007-06-17.6691253086
URL2 http://localhost/DAgroups/portal_factory/People
URL3 http://localhost/DAgroups/portal_factory
URL4 http://localhost/DAgroups
URL5 http://localhost
BASE0 http://localhost
BASE1 http://localhost/DAgroups
BASE2 http://localhost/DAgroups/portal_factory
BASE3 http://localhost/DAgroups/portal_factory/People
BASE4
http://localhost/DAgroups/portal_factory/People/people.2007-06-17.6691253086
BASE5
http://localhost/DAgroups/portal_factory/People/people.2007-06-17.6691253086
/people_edit
environ
HTTP_COOKIE
'tree-s="eJzTyCkw5NLIKTDiClZ3hANXW3WuAmOuRKCECUjWFEnWNTEbJGvGlQgEegAEWRAt";
__ac="YWRtaW46cHAyMTA3"'
SERVER_SOFTWARE 'Zope/(Zope 2.9.6-final, python 2.4.3, win32) ZServer/1.1
Plone/2.5.2'
SCRIPT_NAME ''
REQUEST_METHOD 'POST'
HTTP_KEEP_ALIVE '300'
SERVER_PROTOCOL 'HTTP/1.1'
channel.creation_time 1182103175
CONNECTION_TYPE 'keep-alive'
HTTP_ACCEPT_CHARSET 'ISO-8859-1,utf-8;q=0.7,*;q=0.7'
HTTP_USER_AGENT 'Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.1)
Gecko/20061204 Firefox/2.0.0.1'
HTTP_REFERER
'http://localhost/DAgroups/portal_factory/People/people.2007-06-17.669125308
6/people_read_for_edit_control?portal_status_message=People%20allegedly%20de
leted%3A%2069393'
SERVER_NAME 'KenIBM'
REMOTE_ADDR '127.0.0.1'
PATH_TRANSLATED
'\\DAgroups\\portal_factory\\People\\people.2007-06-17.6691253086\\people_ed
it'
SERVER_PORT '80'
CONTENT_LENGTH '120'
HTTP_HOST 'localhost'
HTTP_ACCEPT
'text/xml,application/xml,application/xhtml+xml,text/html;q=0.9,text/plain;q
=0.8,image/png,*/*;q=0.5'
GATEWAY_INTERFACE 'CGI/1.1'
HTTP_ACCEPT_LANGUAGE 'en-us,en;q=0.5'
CONTENT_TYPE 'application/x-www-form-urlencoded'
HTTP_ACCEPT_ENCODING 'gzip,deflate'
PATH_INFO
'/DAgroups/portal_factory/People/people.2007-06-17.6691253086/people_edit'
"

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

Ken Winter wrote at 2007-6-16 16:03 -0400:
> ...
>Exception Type
> KeyError
>Exception Value
> 'id_list'
>
>Traceback (innermost last):
> ...
> * Module Products.CMFCore.FSPythonScript, line 164, in _exec
> * Module None, line 4, in people_delete_control
> <FSControllerPythonScript at /DAgroups/people_delete_control used for
>/DAgroups/portal_factory/People/people.2007-06-16.3625931808>
> Line 4
> * Module Shared.DC.ZRDB.DA, line 481, in __call__
> <FSZSQLMethod at /DAgroups/people_delete_by_id used for
>/DAgroups/portal_factory/People/people.2007-06-16.3625931808>
> * Module DocumentTemplate.DT_String, line 476, in __call__
>
>KeyError: 'id_list'

The traceback tells you that it comes from the "FSControllerPythonScript"
"people_delete_control" and not from a DMTL object (as you have shown
us in a previous message). Almost surely, you did not call
the Z SQL Method with an "id_list" keyword parameter there.



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

> I tried to invoke the "query" method of my adapter (which is named
> "dhatabase") with this code:
>
> "
> p = context.REQUEST.get('delete_this')
> if p:
> s = ', '.join([str(x) for x in p])
> t = "delete from person where person_id in (%s);" % s
> context.dhatabase.query(query_string=t)
> "
>
> ...and got this error: "AttributeError: query" (full traceback at end of
> message). What's wrong with my method call?
>
Try:

context.dhatabase().query(query_string=t)

Assuming that 'dhatabase' is your DatabaseConnection object.



> I also have a broader question: When one is composing dynamic SQL in Python
> scripts, what are the pros and cons of executing them by going directly to
> the database adapter (as suggested above) vs passing it in as the sole
> argument of an "empty" ZSQL method? By "empty" ZQL method I mean something
> like:
I think in this case there is no difference. Both ways may be harmful
unless you're sure that it is not possible to do sql injection.

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

> P.S. I know that <dtml-var...> rather than <dtml-sqlvar...> is an
> unorthodox and apparently undocumented construct to use within a ZSQL
> definition. But I need it because <dtml-sqlvar...> malformats the string.
>
What do you mean by 'malformats the string'?
Have you tried dtml-sqltest clause? It may be used to do
'where colname in ....' statements.

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

>
> I also have a broader question: When one is composing dynamic SQL in
Python
> scripts, what are the pros and cons of executing them by going directly
to
> the database adapter (as suggested above) vs passing it in as the sole
> argument of an "empty" ZSQL method? By "empty" ZQL method I mean
something
> like:
>

There are no pros and cons. Only cons.

There is a good argument to be made that ZSQL methods are entirely
a bad idea -- that only prepared statements should be supported, as it
is far harder to break security.

But, every use of <dtml-var ...> in a ZSQL method requires that the
argument be examined and correctly SQL-Quoted. For example, what is
to keep someone from entering "13225, 12337; delete from person" in
your web form?

Further, you have greatly complicated verification and maintenance. It
no longer is enough to test the ZSQL method to be sure that it operates
as expected. You have to examine every call-point to determine what the
SQL method is doing. And you have to examine every argument to be sure
that it has been quoted properly and you aren't open to SQL injection.

Charlie has already given the best answer -- use a really simple method
like:
delete from person where person_id = <dtml-sqlvar foo type=int>, and
call it once for each person you have to delete. SQL injection is
impossible, since foo is verified to be an int just before it is used.

Now, there are times where <dtml-var ... > is unavoidable; IN clauses and
LIKE clauses are the principal ones. In either case, you really need to
verify the arguments. At the bare minimum, look at <dtml-var ...
sql_quote>.


jim penny


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

Am 18.06.2007, 16:03 Uhr, schrieb <JPenny@ykksnap-america.com>:

> There are no pros and cons. Only cons.

This is generally right, unless you use .execute(statement, (paras,)) on
the DA like you can with the mxODBC Zope DA. Although admittely this is
currently only available for ExternalMethods. Adding supporting for
.execute() in a DA is not hard. However, this should only be used
occasionally if it is important to generate your SQL and using something
like SQLalchemy isn't appropriate.

> There is a good argument to be made that ZSQL methods are entirely
> a bad idea -- that only prepared statements should be supported, as it
> is far harder to break security.

I don't think they are a bad idea in fact I find them very helpful but
they could be updated to use prepared statements with bound parameters
which require the driver to quote parameters. I've started work on a
simpleSQLTemplate which uses string.Template to support $placeholders

"SELECT id FROM table WHERE id = $value"
->
"SELECT id FROM table WHERE id = ?", (value,) # generate the SQL using the
appropriate paramstyle for the underlying DB driver.

Charlie
--
Charlie Clark
eGenix.com

Professional Python Services directly from the Source
>>> Python/Zope Consulting and Support ... http://www.egenix.com/
>>> mxODBC.Zope.Database.Adapter ... http://zope.egenix.com/
>>> mxODBC, mxDateTime, mxTextTools ... http://python.egenix.com/
________________________________________________________________________

:::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,MacOSX for free ! ::::

eGenix.com Software, Skills and Services GmbH Pastor-Loeh-Str.48
D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg
Registered at Amtsgericht Duesseldorf: HRB 46611
_______________________________________________
Zope-DB mailing list
Zope-DB@zope.org
http://mail.zope.org/mailman/listinfo/zope-db

> -----Original Message-----
> From: Maciej Wisniowski [mailto:maciej.wisniowski@coig.katowice.pl]
> Sent: Monday, June 18, 2007 1:34 AM
> To: Ken Winter
> Cc: 'Zope-DB List'
> Subject: Re: [Zope-DB] Using <dtml-var>s in ZSQL methods?
>
>
> > I tried to invoke the "query" method of my adapter (which is named
> > "dhatabase") with this code:
> >
> > "
> > p = context.REQUEST.get('delete_this')
> > if p:
> > s = ', '.join([str(x) for x in p])
> > t = "delete from person where person_id in (%s);" % s
> > context.dhatabase.query(query_string=t)
> > "
> >
> > ...and got this error: "AttributeError: query" (full traceback at end of
> > message). What's wrong with my method call?
> >
> Try:
>
> context.dhatabase().query(query_string=t)
>
> Assuming that 'dhatabase' is your DatabaseConnection object.

Thanks, Maciej. That works fine!

~ Ken

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

Thanks Charlie & Jim ~

SQL injection is a new one on me, and I'm glad to learn about it now
(painlessly) rather than later (painfully).

~ Ken


> -----Original Message-----
> From: JPenny@ykksnap-america.com [mailto:JPenny@ykksnap-america.com]
> Sent: Monday, June 18, 2007 10:03 AM
> To: Ken Winter
> Cc: 'Zope-DB List'
> Subject: RE: [Zope-DB] Using <dtml-var>s in ZSQL methods?
>
> >
> > I also have a broader question: When one is composing dynamic SQL in
> Python
> > scripts, what are the pros and cons of executing them by going directly
> to
> > the database adapter (as suggested above) vs passing it in as the sole
> > argument of an "empty" ZSQL method? By "empty" ZQL method I mean
> something
> > like:
> >
>
> There are no pros and cons. Only cons.
>
> There is a good argument to be made that ZSQL methods are entirely
> a bad idea -- that only prepared statements should be supported, as it
> is far harder to break security.
>
> But, every use of <dtml-var ...> in a ZSQL method requires that the
> argument be examined and correctly SQL-Quoted. For example, what is
> to keep someone from entering "13225, 12337; delete from person" in
> your web form?
>
> Further, you have greatly complicated verification and maintenance. It
> no longer is enough to test the ZSQL method to be sure that it operates
> as expected. You have to examine every call-point to determine what the
> SQL method is doing. And you have to examine every argument to be sure
> that it has been quoted properly and you aren't open to SQL injection.
>
> Charlie has already given the best answer -- use a really simple method
> like:
> delete from person where person_id = <dtml-sqlvar foo type=int>, and
> call it once for each person you have to delete. SQL injection is
> impossible, since foo is verified to be an int just before it is used.
>
> Now, there are times where <dtml-var ... > is unavoidable; IN clauses and
> LIKE clauses are the principal ones. In either case, you really need to
> verify the arguments. At the bare minimum, look at <dtml-var ...
> sql_quote>.
>
>
> jim penny


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

Am 18.06.2007, 22:10 Uhr, schrieb Ken Winter <ken@sunward.org>:

> Thanks Charlie & Jim ~
> SQL injection is a new one on me, and I'm glad to learn about it now
> (painlessly) rather than later (painfully).

Preventing SQL injection for non-savvy users (and letting them learn about
it later when they might understand it better) is probably the single
greatest reason for using ZSQL and Zope to integrate external RDBMS's.
It's worth remembering that it was developed before bound parameters were
generally supported and has unfortunately been somewhat neglected since.
If you're going to want to manage and reuse your SQL calls then I would
highly recommend you stick with ZSQL and <dtml-sqlvar ...> until you are
more comfortable with Zope in general: it's easy to rack up twenty or
thiry *completely* different SQL statements in a site and not need to look
at them again for a couple of years. Then, when you have to, it's
incredibly wonderful being able to review and test them individually.

Charlie
--
Charlie Clark
eGenix.com

Professional Python Services directly from the Source
>>> Python/Zope Consulting and Support ... http://www.egenix.com/
>>> mxODBC.Zope.Database.Adapter ... http://zope.egenix.com/
>>> mxODBC, mxDateTime, mxTextTools ... http://python.egenix.com/
________________________________________________________________________

:::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,MacOSX for free ! ::::

eGenix.com Software, Skills and Services GmbH Pastor-Loeh-Str.48
D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg
Registered at Amtsgericht Duesseldorf: HRB 46611
_______________________________________________
Zope-DB mailing list
Zope-DB@zope.org
http://mail.zope.org/mailman/listinfo/zope-db