473,287 Members | 1,978 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

choose from a list

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

Oct 30 '07 #1
6 2128
On Oct 30, 1:03 pm, barronmo <barro...@gmail.comwrote:
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"])
>
cursor.close()
conn.close()

Thanks in advance for any help.

Mike

Oct 30 '07 #2
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
Oct 30 '07 #3
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

Oct 30 '07 #4
On Oct 30, 5:08 pm, barronmo <barro...@gmail.comwrote:
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

Oct 30 '07 #5
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
Oct 31 '07 #6
On Oct 30, 7:39?pm, barronmo <barro...@gmail.comwrote:
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.
I tried defining 'r
= ""' in the module before the function and then using it instead of
"result" but that didn't help.

Mike

Oct 31 '07 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

24
by: Chad Everett | last post by:
Hey guys, I am back. Trying to expand on a program that was given in the book I am studying. No I am not a high school or college student. Doing this on my own. and having way to much...
5
by: GS | last post by:
Hi, I have dropdown list bound to a Table, I don't want dropdown list to select first row from a table but rather to display "Choose" and when user clicks it then to display available values. How...
2
by: AA Arens | last post by:
I have two tables, one consists of company info like name and phone number. Another table where I have to fill in the contact persons. Part of the form is to choose the company he works for (From...
15
by: caca | last post by:
Hello, This is a question for the best method (in terms of performance only) to choose a random element from a list among those that satisfy a certain property. This is the setting: I need to...
1
by: =?Utf-8?B?TWFyayBIb2xsYW5kZXI=?= | last post by:
Hi All, I have registered the steep valley XP Common Controls to the GAC, But I noticed that it does not show up automatically in the Choose Toolbox Items" List. I have to specifically browse to...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.