Newbie to client/server

[Leif B. Kristensen]

John Fabiani wrote:

> I don't understand how to get data from multi tables for my
> forms/windows. I have been reading how to connect and get data (using
> a SQL statement)
> from MySQL.  I think I understand.
> Now the question if I have a form (i.e. AR invoice) that requires data
> from
> more than one table (or I need multi SQL statements) where do I put
> it.  It
> appears that I can have only one cursor.  This must be wrong. I can
> understand that I can reuse the connect object but the returning
> information ends up into the same cursor.  I don't want code (just how
> to do it right) but when I do something like the following it does not
> work: Con=myconnection_string
> Cursor=con.cursor()
> Cursor.execute(somestatement)
> newCursor=con.cursor()
> newCursor.execute(somestatement)  #does not work

I'm not quite sure that I understand your problem, perhaps you should
try to state it a little more clearly. But if you want to use some
foreign key from the first query to gather some information from
another table where the same number is the primary key, this is most
easily accomplished by defining a function that fetches that
information.

For instance, I've got a main query that looks like this:

db=MySQLdb.connect(host="localhost", user="xxx", passwd="yyy", db="zzz")
cursor=db.cursor()
cursor.execute("select * from event \
                where person_id1=%d \
                order by sort_date" % (person))
result=cursor.fetchall()
                .
                .
                .
    for rec in result:
        mlb.insert(END, (get_event_name(rec[1]), \
                            # some other stuff
                            get_place(rec[7]), \
                            rec[8]))

One of the columns in the _event_ table holds a key called _place_id_
(rec[7] above), which is the primary key of the table _place_. When I
need the actual place string, I send a request to a function called
get_place():

def get_place(x):
    c=db.cursor()
    c.execute("select place_lvl1, place_lvl2, \
                place_lvl3, place_lvl4, place_lvl5 \
                from place where place_id = %d" % (x))
    res=c.fetchone()
    return ', '.join([p for p in res if p and p[0] != '-'])

In MySQL databases, you often have to string up several queries like
this to get what you want.

regards
-- 
Leif Biberg Kristensen
http://solumslekt.org/
Validare necesse est