[Tutor] Trying to use MySQLdb.cursor

[Liam Clarke]

mySQL also has it's own metadata commands - 

http://dev.mysql.com/doc/mysql/en/getting-information.html

Looks like you want to use the DESCRIBE <table> command.



On Fri, 25 Mar 2005 06:25:52 -0500, Kent Johnson <kent37 at tds.net> wrote:
> Vicki Stanfield wrote:
> > I finally gave up and used MySQLdb to connect to my database. It connects
> > okay, and returns data, but now I have a new question. I use the code
> > below to print the data returned from my query, but I would like to make
> > labels at the top of the columns. How do I do this dynamically? I would
> > like to get the fieldnames as defined by mysql and print them before
> > printing each column. Is there a way to do this?
> >
> > Here is the relevant portion of the code:
> >
> > def getdata():
> >      conn = MySQLdb.Connect(
> >          host='localhost', user='user',
> >          passwd='password', db='sample',compress=1,
> >          cursorclass=MySQLdb.cursors.DictCursor)
> >      cursor = conn.cursor()
> >      cursor.execute("""SELECT computers.comp_location FROM computers, mice
> >                 WHERE mice.mouse_type = "USB"
> >                 AND computers.comp_location like "A%"
> >                 AND mice.mouse_comp = computers.comp_id;""")
> 
> In this case you know the name as it is in the query (comp_location). In general you can use
> cursor.description. From the DB-API docs (http://www.python.org/peps/pep-0249.html):
> 
>              This read-only attribute is a sequence of 7-item
>              sequences.  Each of these sequences contains information
>              describing one result column: (name, type_code,
>              display_size, internal_size, precision, scale,
>              null_ok). The first two items (name and type_code) are
>              mandatory, the other five are optional and must be set to
>              None if meaningfull values are not provided.
> 
> So to output a row with the column names something like this should work:
> print "<tr>"
> for col in cursor.description:
>      print '<td>%s</td>' % col[0]
> print "</tr>"
> 
> Kent
> 
> >      rows = cursor.fetchall()
> >      cursor.close()
> >      conn.close()
> >
> >      print '''
> >      <table border="1" cellpadding="5">
> >      '''
> >
> >      for row in rows:
> >           print "<tr>"
> >           for cell in row:
> >               print "<td> %s </td>" % row[cell]
> >
> >           print "</tr>"
> >
> > Thanks for helping me get going.
> > Vicki
> >
> > _______________________________________________
> > Tutor maillist  -  Tutor at python.org
> > http://mail.python.org/mailman/listinfo/tutor
> >
> 
> _______________________________________________
> Tutor maillist  -  Tutor at python.org
> http://mail.python.org/mailman/listinfo/tutor
> 


-- 
'There is only one basic human right, and that is to do as you damn well please.
And with it comes the only basic human duty, to take the consequences.