FAQ
Hi,

I have a little problem: an SQL query delivers me a list of
[(name, firstname, id, email), ...]
and I want to put this in a table, but OUT OF ORDER.

So my question is how do I crate a table like this:
firstname lastname | email | id |
which is not possible with "normal" <dtml-in>-iteration.


I strongly hope someone can help me!

Thanks in advance and bye,

Axel.

Search Discussions

  • John Hunter at Aug 6, 2002 at 6:12 pm
    "Axel" == Axel Bock <news-and-lists at the-me.de> writes:
    Axel> Hi, I have a little problem: an SQL query delivers me a list
    Axel> of [(name, firstname, id, email), ...] and I want to put
    Axel> this in a table, but OUT OF ORDER.

    Axel> So my question is how do I crate a table like this: |
    Axel> firstname lastname | email | id | which is not possible with
    Axel> "normal" <dtml-in>-iteration.

    Unless I'm misunderstanding your problem, this should not present any
    difficulties. For example, if your zope Z SQL Method is named
    'select_sql', you can create the table like so:

    <table>
    <dtml-in select_sql sort="last">
    <tr>
    <th><dtml-var first> <dtml-var last></th>
    <td><dtml-var email></td>
    <td><dtml-var id></td>
    </tr>
    </dtml-in>
    </table>

    The sort argument lets you sort on any table column, and within the
    dtml-in tags, you can reference the fields n any order you wish.

    Axel> I strongly hope someone can help me!

    And I strongly hope this helped!

    John Hunter
  • Axel Bock at Aug 6, 2002 at 10:41 pm

    On Tue, 06 Aug 2002 15:12:33 +0200, John Hunter wrote:

    "Axel" == Axel Bock <news-and-lists at the-me.de> writes:
    Axel> Hi, I have a little problem: an SQL query delivers me a list
    Axel> of [(name, firstname, id, email), ...] and I want to put Axel>
    this in a table, but OUT OF ORDER.
    Unless I'm misunderstanding your problem, this should not present any
    difficulties. For example, if your zope Z SQL Method is named
    'select_sql', you can create the table like so:
    no, i think you did well understand the problem. But I do not use ZSQL
    methods, and I am getting a list of tuples, as I said. And these are not
    so easily dereferenced (cause there are no names ... :-)

    Up to now I came up with the following solution:
    <dtml-call "REQUEST.set('something', sequence-item)">
    Which I can dereference as
    <dtml-var "something[x]">

    I don't see another way. But you can tell me in which way I can access a
    ZMySQLDatabaseconnection from within my own product, if this is not too
    much a thing (I stole code from ZSQLMethod and fiddled it until I could
    drop pure SQL queries the form SQL.query("..."))

    Thanks anyway and greetings,

    Axel.
  • John Hunter at Aug 6, 2002 at 11:24 pm
    "Axel" == Axel Bock <news-and-lists at the-me.de> writes:
    Axel> no, i think you did well understand the problem. But I do
    Axel> not use ZSQL methods, and I am getting a list of tuples, as
    Axel> I said. And these are not so easily dereferenced (cause
    Axel> there are no names ... :-)

    Axel> Up to now I came up with the following solution: <dtml-call
    Axel> "REQUEST.set('something', sequence-item)"> Which I can
    Axel> dereference as <dtml-var "something[x]">

    Axel> I don't see another way. But you can tell me in which way I
    Axel> can access a ZMySQLDatabaseconnection from within my own
    Axel> product, if this is not too much a thing (I stole code from
    Axel> ZSQLMethod and fiddled it until I could drop pure SQL
    Axel> queries the form SQL.query("..."))

    A little more info at this point would be helpful. What dbase are you
    using and how are accessing it? Are you calling it from a python
    product? If so, your dbase interface might have a mapping interface.

    For example, MySQLdb has a cursors.DictCursor class which can br used
    to get a dictionary rather than a tuple from a select call. You make
    the connection like:

    db = MySQLdb.connect(db='mydb',
    host='myhost',
    user='myuser',
    passwd='mypass',
    cursorclass=MySQLdb.cursors.DictCursor)

    mh = MySQLTable(table_name='mytable', cursor=db.cursor())

    A little more info on the dbase and python interface will help us
    steer you toward the light.

    John Hunter
  • John Hunter at Aug 7, 2002 at 1:16 pm
    "Axel" == Axel Bock <news-and-lists at the-me.de> writes:
    Axel> no, i think you did well understand the problem. But I do
    Axel> not use ZSQL methods, and I am getting a list of tuples, as
    Axel> I said. And these are not so easily dereferenced (cause
    Axel> there are no names ... :-)


    You can also work directly with tuples, and reference the elements in
    an arbitrary order, as the example below illustrates. The prefix on
    the dtml-in tag is not necessary, but I think it reads better:

    <table>
    <dtml-in "[ ('John', 'Hunter', 1234, 'jdhunter at paradise.lost'),
    ('John', 'Doe', 1235, 'billyboy at yahoo.edu'),
    ('Jane', 'Smith', 1238, 'jane at myserver.com')]"
    prefix="people">
    <tr>
    <td><dtml-var "people_item[0]"> <dtml-var "people_item[1]"></td>
    <td><dtml-var "people_item[3]"></td>
    <td><dtml-var "people_item[2]"></td>
    </tr>
    </dtml-in>
    </table>

    John Hunter
  • Axel Bock at Aug 7, 2002 at 2:51 pm

    On Tue, 06 Aug 2002 20:24:19 +0200, John Hunter wrote:


    A little more info at this point would be helpful. What dbase are you
    using and how are accessing it? Are you calling it from a python product?
    If so, your dbase interface might have a mapping interface.
    Well, no problem:
    I use a MySQL database connection through a Z Mysql Database Connection,
    and I use this from my own product.

    The code to access the database is partially stolen from Z SQL Method and
    looks like this:

    self.connection = connection
    try:
    self.dbc = getattr(self, self.connection)
    except:
    return 'error'

    To submit queries I do the following:

    DB = self.dbc()
    result = DB.query("select wuh from wobba")

    "connection" is a reference to a Z MySQL Database Connection (I think), which I got
    the following way (stolen from ... ):

    ids={}
    have_id=ids.has_key
    StringType=type('')

    while self is not None:
    if hasattr(self, 'objectValues'):
    for o in self.objectValues():
    if (hasattr(o,'_isAnSQLConnection') and o._isAnSQLConnection
    and hasattr(o,'id')):
    id=o.id
    if type(id) is not StringType: id=id()
    if not have_id(id):
    if hasattr(o,'title_and_id'): o=o.title_and_id()
    else: o=id
    ids[id]=id
    if hasattr(self, 'aq_parent'): self=self.aq_parent
    else: self=None

    ids=map(lambda item: (item[1], item[0]), ids.items())
    ids.sort()
    return ids


    Well, this is the way I do it right now.
    I'm sorry if I give you a total overkill, but I truly have no clue how
    this mechanism relly works, and I found absolutely no documentation from
    the Zope site (which is a major flaw in Zope I think ...).

    If you can comment something useful to this mess I would be quite happy,
    otherwise ... well, it works ;-))


    Bye, thanks and greetings,

    Axel.
  • John Hunter at Aug 7, 2002 at 3:09 pm
    "Axel" == Axel Bock <news-and-lists at the-me.de> writes:
    Axel> To submit queries I do the following:

    Axel> DB = self.dbc()

    If you can't use the DictCursor I suggested above (and it may be
    difficult since it looks like you are inheriting the connection but
    someone else may be able to help you here), then you can either

    1) use the tuple indexing I posted earlier

    2) Make a list dictionary in your python product and return it to
    dtml; this will make the dtml more readable.

    results = DB.query("select first,last,pid,email from wobba")
    resultDicts = []
    for result in results:
    resultDicts.append( {'first' : result[0], 'last' : result[1],
    {'pid' : result[2], 'email' : result[3]} )

    Now return resultDicts, which is a list of dictionaries.

    In dtml, you can call the method like this; note the 'mapping' keyword is
    critical here:

    <dtml-in get_result_dicts mapping>
    <dtml-var first> <dtml-var last> <dtml-var pid> <dtml-var email>
    </dtml-in>

    Good luck!
    John Hunter

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppython-list @
categoriespython
postedAug 6, '02 at 5:41p
activeAug 7, '02 at 3:09p
posts7
users2
websitepython.org

2 users in discussion

John Hunter: 4 posts Axel Bock: 3 posts

People

Translate

site design / logo © 2022 Grokbase