Newbie to client/server

[John Fabiani]

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
John

 

[Larry Bates]

Use SQL "joins" to create a single "logical" record from
as many different tables as you want resulting in a single
result record that contains all the fields on your form.
If there is a one-many relationship between tables, you
will loop over all the result records.

That way you only need a single cursor.

FYI,
Larry Bates
Syscon, Inc.

 

[Leif B. Kristensen]

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