choose from a list

sandipm sandip.more at gmail.com
Fri Nov 2 05:21:49 CET 2007



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



On Nov 1, 10:13 pm, "mensana... at aol.com" <mensana... at aol.com> wrote:
> On Nov 1, 11:54 am, barronmo <barro... at gmail.com> wrote:
>
>
>
> > 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:
>
> >>> a = long(615)
> >>> a
> 615L
> >>> print a
>
> 615
>
> Notice the L is gone when you go to use it:
>
> >>> print 'SELECT * FROM test WHERE pid=%s' % a
>
> 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
>
> > On Oct 31, 12:32 am, "mensana... at aol.com" <mensana... at aol.com> wrote:
>
> > > On Oct 30, 7:39?pm, barronmo <barro... at gmail.com> wrote:
>
> > > > 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 -





More information about the Python-list mailing list