choose from a list

[barronmo]

I'm new to programming and even newer to Python and would be grateful
for some help on what has been a tough problem for me. The project I
am working on is an electronic medical record using MySQL/Python. I'm
currrently working on a module that looks up a patient's name based on
input from the user.

My goal is a lookup based on the first 2 or 3 letters of the patient's
last name. The matching results would appear as numbered choices so
that the user would choose only a number to then access various parts
of the patient's record. The results might look like this for user
input "smi":

1 387 John Smith
2 453 Jane Smith
3 975 Joe Smithton

Here is a copy of what I have so far, name_lookup.py:


import MySQLdb

def name_find(namefrag):

conn = MySQLdb.connect(host = "localhost",
user = "root",
passwd = "n85",
db = "meds")
cursor = conn.cursor(MySQLdb.cursors.DictCursor)
cursor.execute("SELECT patient_ID, firstname, lastname FROM
demographics WHERE lastname LIKE '"+ str(namefrag)+"%'")
results = cursor.fetchall()
for row in results:
print "%s %s %s %s" % (row["patient_ID"],
row["firstname"], row["lastname"])

cursor.close()
conn.close()


Thanks in advance for any help.

Mike

 

[mensanator]

I'm new to programming and even newer to Python and would be grateful
for some help on what has been a tough problem for me. The project I
am working on is an electronic medical record using MySQL/Python. I'm
currrently working on a module that looks up a patient's name based on
input from the user.

My goal is a lookup based on the first 2 or 3 letters of the patient's
last name. The matching results would appear as numbered choices so
that the user would choose only a number to then access various parts
of the patient's record. The results might look like this for user
input "smi":

1 387 John Smith
2 453 Jane Smith
3 975 Joe Smithton

Here is a copy of what I have so far, name_lookup.py:

import MySQLdb

def name_find(namefrag):

conn = MySQLdb.connect(host = "localhost",
user = "root",
passwd = "n85",
db = "meds")
cursor = conn.cursor(MySQLdb.cursors.DictCursor)
cursor.execute("SELECT patient_ID, firstname, lastname FROM
demographics WHERE lastname LIKE '"+ str(namefrag)+"%'")
results = cursor.fetchall()

Change this

for row in results:
print "%s %s %s %s" % (row["patient_ID"],
row["firstname"], row["lastname"])

to this

for rec,row in enumerate(results):
print "%d %s %s %s %s" %
(rec,row["patient_ID"],row["firstname"], row["lastname"])

 

[Bruno Desthuilliers]

barronmo a écrit :

I'm new to programming and even newer to Python and would be grateful
for some help on what has been a tough problem for me. The project I
am working on is an electronic medical record using MySQL/Python. I'm
currrently working on a module that looks up a patient's name based on
input from the user.

My goal is a lookup based on the first 2 or 3 letters of the patient's
last name. The matching results would appear as numbered choices so
that the user would choose only a number to then access various parts
of the patient's record. The results might look like this for user
input "smi":

1 387 John Smith
2 453 Jane Smith
3 975 Joe Smithton

Here is a copy of what I have so far, name_lookup.py:


import MySQLdb

def name_find(namefrag):

conn = MySQLdb.connect(host = "localhost",
user = "root",
passwd = "n85",
db = "meds")

Opening (and closing) a connection to the RDBMS on each and every
function is certainly not the best way to go.

cursor = conn.cursor(MySQLdb.cursors.DictCursor)
cursor.execute("SELECT patient_ID, firstname, lastname FROM
demographics WHERE lastname LIKE '"+ str(namefrag)+"%'")

Please re-read both the db-api and MySQLdb docs. You should not build
the whole query this way, but instead use (db module specific)
plaeholders and pass actual params as a tuple, ie (if I correctly
remember MySQLdb specificities):

cursor.execute(
"SELECT patient_ID, firstname, lastname FROM " \
+ " demographics WHERE lastname LIKE '%s%%'),
(namefrag, )
)

results = cursor.fetchall()
for row in results:

Some db modules let you iterate directly over the cursor. Check if it's
the case with MySQLdb. If so, you may want:

for row in cursor:

instead.

print "%s %s %s %s" % (row["patient_ID"],
row["firstname"], row["lastname"])

Python has better to offer wrt/ string formatting (assuming patient_ID
is an integer):

print "%(patient_ID)03d %(firstname)s, %(lastname)s" % row

Thanks in advance for any help.

wrt/ (what I guess is) your (very implicit) question, you may want to
have a look at enumerate(iterable):

for index, item in enumerate(cursor): #or:enumerate(cursor.fetchall()):
print i, item

HTH

 

[barronmo]

Thanks to both of you for the help. I made several of the changes you
suggested and am getting the results in the format I want, eg:

0 387 John Smith
1 453 Jane Smith
2 975 Joe Smithton

My plan at this point is, in addition to printing the results of the
query, to create a list with a matching index so the user can select a
name and I can store the patient_ID to get other parts of their
medical record. Does this sound reasonable?

My code now looks like this:


import MySQLdb

def name_find(namefrag):

conn = MySQLdb.connect(host = "localhost",
user = "root",
passwd = "Barron85",
db = "meds")
cursor = conn.cursor(MySQLdb.cursors.DictCursor)
cursor.execute("SELECT patient_ID, firstname, lastname FROM
demographics WHERE lastname LIKE '%s%%'" % (namefrag))

result = cursor.fetchall()
for index, row in enumerate(result):
print "%d %s %s %s" % (index, row["patient_ID"],
row["firstname"], row["lastname"])
#create list here

cursor.close()
conn.close()


Thanks again, Mike

 

[mensanator]

Thanks to both of you for the help. I made several of the changes you
suggested and am getting the results in the format I want, eg:

0 387 John Smith
1 453 Jane Smith
2 975 Joe Smithton

My plan at this point is, in addition to printing the results of the
query, to create a list with a matching index so the user can select a
name and I can store the patient_ID to get other parts of their
medical record. Does this sound reasonable?

Don't you already have this list, called 'result'?

Once the user selects the index (let's say in a variable indx),
can't you build a SQL query to select records from other
tables (presumably keyed by patient_ID)?

Something like (made up)

cursor.execute("""
SELECT patient_ID, lab, test, test_result
FROM labtests
WHERE patient_ID=?""",
(result[indx]['patient_ID']))

My code now looks like this:

import MySQLdb

def name_find(namefrag):

conn = MySQLdb.connect(host = "localhost",
user = "root",
passwd = "Barron85",
db = "meds")
cursor = conn.cursor(MySQLdb.cursors.DictCursor)
cursor.execute("SELECT patient_ID, firstname, lastname FROM
demographics WHERE lastname LIKE '%s%%'" % (namefrag))

result = cursor.fetchall()
for index, row in enumerate(result):
print "%d %s %s %s" % (index, row["patient_ID"],
row["firstname"], row["lastname"])
#create list here

cursor.close()
conn.close()

Thanks again, Mike

 

[barronmo]

I didn't know "result" was a list! Can all that info be stored in a
list? How do the columns work? I was curious to see what the data
looked like but I can't seem to print "result" from the prompt. Do
variables used inside functions live or die once the function
executes? If they die, how do I get around this? I tried defining 'r
= ""' in the module before the function and then using it instead of
"result" but that didn't help.

Mike

 

[mensanator]

I didn't know "result" was a list!

I don't use MySQL but that's how others work.
Each list item is a record, each record a tuple
of field values.

Can all that info be stored in a list?

If you don't fetch too many records at once.
This is a test of my word database using ODBC
and MS-ACCESS (the SQL is very simple since
all the actual work is done in MS-ACCESS, Python
is just retrieving the final results).

import dbi
import odbc
con = odbc.odbc("words")
cursor = con.cursor()
cursor.execute("SELECT * FROM signature_anagram_summary")
results = cursor.fetchall()

Here, results (the recipient of .fetchall) is a list of tuples.
The contents are:

[(9, 10, 'anoretics', '10101000100001100111000000'),
(9, 10, 'atroscine', '10101000100001100111000000'),
(9, 10, 'certosina', '10101000100001100111000000'),
(9, 10, 'creations', '10101000100001100111000000'),
(9, 10, 'narcotise', '10101000100001100111000000'),
(9, 10, 'ostracine', '10101000100001100111000000'),
(9, 10, 'reactions', '10101000100001100111000000'),
(9, 10, 'secration', '10101000100001100111000000'),
(9, 10, 'tinoceras', '10101000100001100111000000'),
(9, 10, 'tricosane', '10101000100001100111000000')]

How do the columns work?

I don't know, I don't get column names. It looked like
from your example that you can use names, I would have
to use indexes, such as results[3][2] to get 'creations'.
Maybe MySQL returns dictionaries instead of tuples.

I was curious to see what the data
looked like but I can't seem to print "result" from the prompt. Do
variables used inside functions live or die once the function
executes?

Yeah, they die. You would have to have the function return
the results list and indx, then you could use it's contents
as criteria for further queries.

So you might want to say

name_find_results,indx = name_find(namefrag)

If they die, how do I get around this?

Add 'return results,indx' to the function. Or better still,
just return the record the user selected
return results[indx]
You wouldn't need indx anymore since there's only 1 record.

 

[Dennis Lee Bieber]

cursor.execute("SELECT patient_ID, firstname, lastname FROM
demographics WHERE lastname LIKE '%s%%'" % (namefrag))

That statement will fail drastically the first time you look for a
name like: O'Carolan, O'Conner, O'Henry, etc.

Reason: you are still building the query by hand rather than letting
MySQLdb adapter build proper/safe queries.

... where lastname like 'O'Carolan%'

note the quotes?

USE:

cursor.execute(
"""select patient_id, firstname, lastname from demographics
where lastname like %s""",
(namefrag+"%",) )

The db-api adapter will properly escape and quote string
arguments... This should result in a query of:

... where lastname like 'O\'Carolan%'

--
Wulfraed Dennis Lee Bieber KD6MOG
(e-mail address removed) (e-mail address removed)
HTTP://wlfraed.home.netcom.com/
(Bestiaria Support Staff: (e-mail address removed))
HTTP://www.bestiaria.com/

 

[barronmo]

This is really remarkable. My previous experience with programming
was in VB for Applications; doing the same thing seemed much more
complicated. This little function is only about 15 lines of code and
it forms the basis for my entire application. With a few simple
modifications I'll be able to get anything out of the database with a
minimum of entries from the user.

It turns out that 'results' was a tuple of dictionaries. I got an
error trying to call the tuple; converting it to a list worked. Here
is the current function:


import MySQLdb

def name_find(namefrag):

conn = MySQLdb.connect(host = "localhost",
user = "root",
passwd = "Barron85",
db = "meds")
cursor = conn.cursor(MySQLdb.cursors.DictCursor)
cursor.execute("SELECT patient_ID, firstname, lastname FROM
demographics WHERE lastname LIKE '%s%%'" % (namefrag))

results = cursor.fetchall()
for index, row in enumerate(results):
print "%d %s %s %s" % (index, row["patient_ID"],
row["firstname"], row["lastname"])
indx = int(raw_input("Select the record you want: "))
results_list = list(results)
return results_list[indx]['patient_ID']

cursor.close()
conn.close()

This returns the patient_ID after selecting a name from the list, eg
615L. I'm not sure why the "L" is there but it shouldn't be hard to
remove. Mensanator, thanks a lot for your help. This has been quite
a lot to digest--huge leap in my understanding of Python.

Michael Barron

I didn't know "result" was alist!

I don't use MySQL but that's how others work.
Eachlistitem is a record, each record a tuple
of field values.

Can all that info be stored in alist?

If you don't fetch too many records at once.
This is a test of my word database using ODBC
and MS-ACCESS (the SQL is very simple since
all the actual work is done in MS-ACCESS, Python
is just retrieving the final results).

import dbi
import odbc
con = odbc.odbc("words")
cursor = con.cursor()
cursor.execute("SELECT * FROM signature_anagram_summary")
results = cursor.fetchall()

Here, results (the recipient of .fetchall) is alistof tuples.
The contents are:

[(9, 10, 'anoretics', '10101000100001100111000000'),
(9, 10, 'atroscine', '10101000100001100111000000'),
(9, 10, 'certosina', '10101000100001100111000000'),
(9, 10, 'creations', '10101000100001100111000000'),
(9, 10, 'narcotise', '10101000100001100111000000'),
(9, 10, 'ostracine', '10101000100001100111000000'),
(9, 10, 'reactions', '10101000100001100111000000'),
(9, 10, 'secration', '10101000100001100111000000'),
(9, 10, 'tinoceras', '10101000100001100111000000'),
(9, 10, 'tricosane', '10101000100001100111000000')]

How do the columns work?

I don't know, I don't get column names. It looked like
from your example that you can use names, I would have
to use indexes, such as results[3][2] to get 'creations'.
Maybe MySQL returns dictionaries instead of tuples.

I was curious to see what the data
looked like but I can't seem to print "result" from the prompt. Do
variables used inside functions live or die once the function
executes?

Yeah, they die. You would have to have the function return
the resultslistand indx, then you could use it's contents
as criteria for further queries.

So you might want to say

name_find_results,indx = name_find(namefrag)

If they die, how do I get around this?

Add 'return results,indx' to the function. Or better still,
just return the record the user selected
return results[indx]
You wouldn't need indx anymore since there's only 1 record.

I tried defining 'r
= ""' in the module before the function and then using it instead of
"result" but that didn't help.

Mike

 

[mensanator]

This is really remarkable. My previous experience with programming
was in VB for Applications; doing the same thing seemed much more
complicated. This little function is only about 15 lines of code and
it forms the basis for my entire application. With a few simple
modifications I'll be able to get anything out of the database with a
minimum of entries from the user.

It turns out that 'results' was a tuple of dictionaries. I got an
error trying to call the tuple; converting it to a list worked. Here
is the current function:

import MySQLdb

def name_find(namefrag):

conn = MySQLdb.connect(host = "localhost",
user = "root",
passwd = "Barron85",
db = "meds")
cursor = conn.cursor(MySQLdb.cursors.DictCursor)
cursor.execute("SELECT patient_ID, firstname, lastname FROM
demographics WHERE lastname LIKE '%s%%'" % (namefrag))

results = cursor.fetchall()
for index, row in enumerate(results):
print "%d %s %s %s" % (index, row["patient_ID"],
row["firstname"], row["lastname"])
indx = int(raw_input("Select the record you want: "))
results_list = list(results)
return results_list[indx]['patient_ID']

cursor.close()
conn.close()

This returns the patient_ID after selecting a name from the list, eg
615L. I'm not sure why the "L" is there but it shouldn't be hard to
remove.

It's a long integer. You don't have to worry about it:
615

Notice the L is gone when you go to use it:
SELECT * FROM test WHERE pid=615

Mensanator, thanks a lot for your help. This has been quite
a lot to digest--huge leap in my understanding of Python.

Michael Barron

I don't use MySQL but that's how others work.
Eachlistitem is a record, each record a tuple
of field values.

If you don't fetch too many records at once.
This is a test of my word database using ODBC
and MS-ACCESS (the SQL is very simple since
all the actual work is done in MS-ACCESS, Python
is just retrieving the final results).

import dbi
import odbc
con = odbc.odbc("words")
cursor = con.cursor()
cursor.execute("SELECT * FROM signature_anagram_summary")
results = cursor.fetchall()

Here, results (the recipient of .fetchall) is alistof tuples.
The contents are:

[(9, 10, 'anoretics', '10101000100001100111000000'),
(9, 10, 'atroscine', '10101000100001100111000000'),
(9, 10, 'certosina', '10101000100001100111000000'),
(9, 10, 'creations', '10101000100001100111000000'),
(9, 10, 'narcotise', '10101000100001100111000000'),
(9, 10, 'ostracine', '10101000100001100111000000'),
(9, 10, 'reactions', '10101000100001100111000000'),
(9, 10, 'secration', '10101000100001100111000000'),
(9, 10, 'tinoceras', '10101000100001100111000000'),
(9, 10, 'tricosane', '10101000100001100111000000')]

How do the columns work?

I don't know, I don't get column names. It looked like
from your example that you can use names, I would have
to use indexes, such as results[3][2] to get 'creations'.
Maybe MySQL returns dictionaries instead of tuples.

I was curious to see what the data
looked like but I can't seem to print "result" from the prompt. Do
variables used inside functions live or die once the function
executes?

Yeah, they die. You would have to have the function return
the resultslistand indx, then you could use it's contents
as criteria for further queries.

So you might want to say

name_find_results,indx = name_find(namefrag)

Add 'return results,indx' to the function. Or better still,
just return the record the user selected
return results[indx]
You wouldn't need indx anymore since there's only 1 record.

I tried defining 'r
= ""' in the module before the function and then using it instead of
"result" but that didn't help.
Mike- Hide quoted text -

- Show quoted text -

 

[sandipm]

one more way of connecting to sql.

MySQLdb.connect(client_flag=65536131072,cursorclass=cursors.DictCursor,host=HOST,port=3306,user=USER,passwd=PASSWD,db=DbName)
cursor = conn.cursor()

in your case, only list of dictiories will be returned but
when query/stored procedure returns more than one result set ...

cursor.execute(sql)
result = cursor.fetchall()
x = [result]
while (cursor.nextset()):
nextres =cursor.fetchall()
if nextres!= ():
x.append(nextres)


thanks
sandip

This is really remarkable. My previous experience with programming
was in VB for Applications; doing the same thing seemed much more
complicated. This little function is only about 15 lines of code and
it forms the basis for my entire application. With a few simple
modifications I'll be able to get anything out of the database with a
minimum of entries from the user.

It turns out that 'results' was a tuple of dictionaries. I got an
error trying to call the tuple; converting it to a list worked. Here
is the current function:

import MySQLdb

def name_find(namefrag):

conn = MySQLdb.connect(host = "localhost",
user = "root",
passwd = "Barron85",
db = "meds")
cursor = conn.cursor(MySQLdb.cursors.DictCursor)
cursor.execute("SELECT patient_ID, firstname, lastname FROM
demographics WHERE lastname LIKE '%s%%'" % (namefrag))

results = cursor.fetchall()
for index, row in enumerate(results):
print "%d %s %s %s" % (index, row["patient_ID"],
row["firstname"], row["lastname"])
indx = int(raw_input("Select the record you want: "))
results_list = list(results)
return results_list[indx]['patient_ID']

cursor.close()
conn.close()

This returns the patient_ID after selecting a name from the list, eg
615L. I'm not sure why the "L" is there but it shouldn't be hard to
remove.

It's a long integer. You don't have to worry about it:

615

Notice the L is gone when you go to use it:

SELECT * FROM test WHERE pid=615

Mensanator, thanks a lot for your help. This has been quite
a lot to digest--huge leap in my understanding of Python.

Michael Barron

I didn't know "result" was alist!
I don't use MySQL but that's how others work.
Eachlistitem is a record, each record a tuple
of field values.
Can all that info be stored in alist?
If you don't fetch too many records at once.
This is a test of my word database using ODBC
and MS-ACCESS (the SQL is very simple since
all the actual work is done in MS-ACCESS, Python
is just retrieving the final results).
import dbi
import odbc
con = odbc.odbc("words")
cursor = con.cursor()
cursor.execute("SELECT * FROM signature_anagram_summary")
results = cursor.fetchall()
Here, results (the recipient of .fetchall) is alistof tuples.
The contents are:
[(9, 10, 'anoretics', '10101000100001100111000000'),
(9, 10, 'atroscine', '10101000100001100111000000'),
(9, 10, 'certosina', '10101000100001100111000000'),
(9, 10, 'creations', '10101000100001100111000000'),
(9, 10, 'narcotise', '10101000100001100111000000'),
(9, 10, 'ostracine', '10101000100001100111000000'),
(9, 10, 'reactions', '10101000100001100111000000'),
(9, 10, 'secration', '10101000100001100111000000'),
(9, 10, 'tinoceras', '10101000100001100111000000'),
(9, 10, 'tricosane', '10101000100001100111000000')]
How do the columns work?
I don't know, I don't get column names. It looked like
from your example that you can use names, I would have
to use indexes, such as results[3][2] to get 'creations'.
Maybe MySQL returns dictionaries instead of tuples.
I was curious to see what the data
looked like but I can't seem to print "result" from the prompt. Do
variables used inside functions live or die once the function
executes?
Yeah, they die. You would have to have the function return
the resultslistand indx, then you could use it's contents
as criteria for further queries.
So you might want to say
name_find_results,indx = name_find(namefrag)
If they die, how do I get around this?
Add 'return results,indx' to the function. Or better still,
just return the record the user selected
return results[indx]
You wouldn't need indx anymore since there's only 1 record.
I tried defining 'r
= ""' in the module before the function and then using it instead of
"result" but that didn't help.
Mike- Hide quoted text -

- Show quoted text -