Re: [Tutor] Trying to use MySQLdb.cursor
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@python.org http://mail.python.org/mailman/listinfo/tutor ___ Tutor maillist - Tutor@python.org http://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] Trying to use MySQLdb.cursor
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? You shouldn't, it makes your code very vulnarable to changes in the database! Its the same principle as using 'select * from...', a bad idea in production code. And if you know which columns you are selecting you by definition know what labels to use. And another reason why its a bsad idea is that databvase columns often have weird abbreviated names that you don't want to expose users to. Alan G. ___ Tutor maillist - Tutor@python.org http://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] Trying to use MySQLdb.cursor
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? You shouldn't, it makes your code very vulnarable to changes in the database! Its the same principle as using 'select * from...', a bad idea in production code. And if you know which columns you are selecting you by definition know what labels to use. And another reason why its a bsad idea is that databvase columns often have weird abbreviated names that you don't want to expose users to. Alan G. I am just trying to write code to demonstrate this capability in Python. If I am actually in a position where I have access to the database schema, I would not do so. I agree with your comments. Vicki ___ Tutor maillist - Tutor@python.org http://mail.python.org/mailman/listinfo/tutor
[Tutor] Trying to use MySQLdb.cursor
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;) 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@python.org http://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] Trying to use MySQLdb.cursor
On Fri, 25 Mar 2005, 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?
Hi Vicki,
Yes, there's a special cursor type that, instead of returning rows of
tuples, returns rows of dictionaries. Here's an example:
##
import MySQLdb
import MySQLdb.cursors
conn = MySQLdb.connect(db='test_adb',
...cursorclass=MySQLdb.cursors.DictCursor)
cursor = conn.cursor()
cursor.execute(select * from Locus limit 10)
10L
cursor.fetchone()
{'orientation_is_5': 1, 'last_updated': DateTime object for '2005-03-24
13:48:06.00' at 402faad8, 'is_deleted': 0, 'name': 'At1g08520',
'representative_model': None, 'is_pseudogene': 0, 'last_updated_by': 1L,
'assigned_to': None, 'update_needed': None, 'gene_model_type': 0L, 'id':
1L, 'chromosome': 1L, 'locked_for_pasa': 0}
##
Hmmm.. that's a little messy. Let me clean up the output of that a bit
with the pretty printing module 'pprint':
##
import pprint
pprint.pprint(cursor.fetchone())
{'assigned_to': None,
'chromosome': 1L,
'gene_model_type': 0L,
'id': 2L,
'is_deleted': 0,
'is_pseudogene': 0,
'last_updated': DateTime object for '2005-03-24 13:48:02.00' at
403104b8,
'last_updated_by': 1L,
'locked_for_pasa': 0,
'name': 'At1g08530',
'orientation_is_5': 1,
'representative_model': None,
'update_needed': None}
##
The output's content itself is probably a bit bizarre to you (It's a gene
from the Arabidopsis plant database) But I hope the code is clear.
*grin*
If you have more questions, please feel free to ask!
___
Tutor maillist - Tutor@python.org
http://mail.python.org/mailman/listinfo/tutor
