From: Jeff Bauer <jbauer@rubic.com> I do something similar with large text fields. Rather than use PostgreSQL's unique (non-portable) array system, I store collections of primitives as strings and use eval() when reading the fields.
aList = [1, 'a', 2.5] field_value = repr(aList) ... perform some database insert operation ... read the field back in aList = eval(field_value)
This works well for lists and dictionaries that contain other lists, dictionaries, and primitive data types. It provides a possible alternative to storing object data when you're required to use a relational database system.
Running eval on any external data source can be potentially risky. An option is to write pickles to the RDBMS. This allows storage of much more complex objects and it's harder for a third party with access to the database to write a potentially damaging pickle to a field than it is to write a potentially damaging expression. The downside is that storing a string repr of a python list or dict is still semi-searchable/readable through SQL for other operations. A stored pickle is not.
[Julian>>] I am using PostgreSQL's array system for a "categories" field in a database, it return records in a format like: '{"foo"}' '{"foo","bar"}' '{"foo"}'
I am trying to extract the list of unique categories - in this example: ["foo","bar"]
This would be a complete nuisance to do in DTML. I have decided to read the SQL method's results into an external method and edit them there - but first I need to know how to call a SQL method without the "in" DTML tag.
Do you have to store it in an array? If you can store the data in the categories field in some sort of delimited list (like comma delimited), you can use something like:: <!--#in sqlMyResults--> <H3><!--#var title--></H3> <H4>Categories:</H4> <UL> <!--#in "_.string.split(categories,',')"--> <LI><!--#var sequence-item--></LI> <!--#/in--> </UL> <HR NOSHADE> <!--#/in--> Conversely, if you had a user enter data in a form with checkboxes or a SELECT list to choose categories, assuming that you use the Zope ":list" type on the input form, you can do the following in an SQL method:: INSERT INTO something (title, categories) VALUES ( <!--#sqlvar title type=nb-->, '<!--#var "_.string.join(categories,',')"-->' ) Another (and slightly better) way to do it would be to run the string.join command in the DTML Method/Document that calls the SQL method that does the insertion and pass it in as an argument. Modify the previous SQL code to look like the following:: INSERT INTO something (title, categories) VALUES ( <!--#sqlvar title type=nb-->, <!--#sqlvar categories type=string--> ) And use the following DTML to insert the data that came in from the form:: <!--#call "sqlAddToMyTable(title=title, categories=_.string.join(categories,','))"--> In essence, this is how I've used UserDB in some consulting projects that allowed the customer to add/edit users in the system. The column being used for the users roles is a comma delimited field, and the interface presented uses checkboxes to select which roles the user has. The roles are joined using the above, or split. An extra benefit of using this is that I can use regular SQL expressions to, say, list all of the users with role "Admin" by using SQL like the following: SELECT * FROM users WHERE user_roles LIKE '%<!--#var lookup_role-->%' There's a myriad of ways of approaching this situation. I'm no mega-SQL guru, but the above approach has worked fine for me in situations where I have a simple list of objects to store/retrieve from SQL. .jPS | jeffrey@digicool.com zope, through the web. www.zope.org