[Zope-DB] [ANN] SQLAlchemyDA 0.3.0 released - a generic database adapter for Zope 2

2007-06-16 Thread Andreas Jung 

I am pleased to announce the first public release of

 SQLAlchemyDA V 0.3.0

Download:
-

  http://opensource.zopyx.com/software/sqlalchemyda/releases/0.3.0


What is SQLAlchemyDA?
-

SQLAlchemyDA is generic database adapter for ZSQL methods. Since it is 
based on SQLAlchemy, SQLAlchemyDA supports all databases out-of-the box 
that are supported by SQLAlchemy (Postgres, MySQL, Oracle, SQLite, MS-SQL, 
Firebird, Informix).



Changes in 0.3.0:

   - fixed some security assertions

   - added Test tab for executing SQL queries directly

   - better error handling for ZMI screens

   - no longer depending on a pre-registered SQLAlchemy wrapper.
 SQLAlchemyDA  now accepts (as any other DA) a DSN as property 'dsn'

   - DSN can be passed through the add form

   - redirect directly to Info tab after creating an instance through
 the ZMI

   - catching some low-level exceptions from the sqlite interface in
 order to make it work with SQLite

   - new properties 'transactional' and 'quoting_style'

   - improved support for Oracle and MySQL

   - SQLAlchemyDA no longer provides access to mapper
 related functionalities.
 It now acts as a DA for executing SQL statements *only*.

   - fixed unregistration code for a wrapper (hopefully works with
 Zope 2.8 or higher)

   - updated documentation


Requirements:
-

 - Zope 2.8+, Zope 3.3+

 - SQLAlchemy 0.3.X

 - z3c.sqlalchemy 1.0.5 +


License
===
SQLAlchemyDA is  licensed under the Zope Public License 2.1.
See LICENSE.txt.

This release has been funded by Renovis, SF, USA.

Andreas Jung

--
ZOPYX Ltd.  Co. KG - Charlottenstr. 37/1 - 72070 Tübingen - Germany
Web: www.zopyx.com - Email: [EMAIL PROTECTED] - 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


pgp7Di1QJn3Au.pgp
Description: PGP signature
___
Zope-DB mailing list
Zope-DB@zope.org
http://mail.zope.org/mailman/listinfo/zope-db

RE: [Zope-DB] Using dtml-vars in ZSQL methods?

2007-06-16 Thread Ken Winter 
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:[EMAIL PROTECTED]
 Sent: Saturday, June 16, 2007 1:41 AM
 To: Ken Winter; 'Zope-DB List'
 Subject: Re: [Zope-DB] Using dtml-vars in ZSQL methods?
 
 It is just because 'delete_this' is a string, not the list, or vice versa?
 
 - Original Message -
 From: Ken Winter [EMAIL PROTECTED]
 To: 'Zope-DB List' zope-db@zope.org
 Sent: Saturday, June 16, 2007 5:15 AM
 Subject: [Zope-DB] Using dtml-vars 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=errb tal:content=err //p
 
 table
 tr tal:repeat=person options/dataa 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

RE: [Zope-DB] Using dtml-vars in ZSQL methods?

2007-06-16 Thread Andreas Jung 

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 [EMAIL PROTECTED] 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:[EMAIL PROTECTED]
Sent: Saturday, June 16, 2007 1:41 AM
To: Ken Winter; 'Zope-DB List'
Subject: Re: [Zope-DB] Using dtml-vars in ZSQL methods?

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

- Original Message -
From: Ken Winter [EMAIL PROTECTED]
To: 'Zope-DB List' zope-db@zope.org
Sent: Saturday, June 16, 2007 5:15 AM
Subject: [Zope-DB] Using dtml-vars 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=errb tal:content=err //p

table
tr tal:repeat=person options/dataa 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: [EMAIL PROTECTED] - 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


pgpX6f3WaiFKL.pgp
Description: PGP signature
___
Zope-DB mailing list
Zope-DB@zope.org
http://mail.zope.org/mailman/listinfo/zope-db

RE: [Zope-DB] Using dtml-vars in ZSQL methods?

2007-06-16 Thread Ken Winter 
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
URL2http://localhost/DAgroups/portal_factory/People
URL3http://localhost/DAgroups/portal_factory
URL4http://localhost/DAgroups
URL5http://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

RE: [Zope-DB] Using dtml-vars in ZSQL methods?

2007-06-16 Thread Ken Winter 
Charlie ~ I tried several experiments based on your suggestion.  See results
inserted below. ~ Thanks, Ken

 -Original Message-
 From: Charlie Clark [mailto:[EMAIL PROTECTED]
 Sent: Saturday, June 16, 2007 3:54 PM
 To: Ken Winter; 'Zope-DB List'
 Subject: Re: [Zope-DB] Using dtml-vars in ZSQL methods?
 
 Am 16.06.2007, 21:19 Uhr, schrieb Ken Winter [EMAIL PROTECTED]:
 
  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

Re: [Zope-DB] Using dtml-vars in ZSQL methods?

2007-06-16 Thread Charlie Clark 

Am 17.06.2007, 00:14 Uhr, schrieb Ken Winter [EMAIL PROTECTED]:


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