python:MySQLdb中获取字段信息



关于python的MySQLdb，资料实在太少了，能够找到的例子也很少，诸如从一个查询中得到字段信息这样简单的需求，也让我大费周折……

先贴一些资料的链接：

python MySQLdb的文档：http://mysql-python.sourceforge.net/MySQLdb-1.2.2/private/MySQLdb-module.html

MySQLdb User's Guide：http://mysql-python.sourceforge.net/MySQLdb.html

            elif isinstance(col, datetime.datetime):
                new_row.append(col.strftime('%Y-%m-%d %H:%M:%S'))
            else:

"""

下面的代码演示如何执行一个查询，并获得字段信息

"""

import sys
import MySQLdb
import json
import types
import datetime
import exceptions

def test():
    try:
        db=MySQLdb.connect(host='192.168.0.123', user='root', passwd='never_let_you_know', db='xx_db')
    except:
        print "connect to db error"
        return
    sql = 'select xx,xxx from x limit 0,3'
    db.query(sql)
    r=db.store_result()  #这个API的用法和mysql c api很类似
    out_map = {'total':r.num_rows(), 'field_count':r.num_fields()}
    fields = []
    (field_info) = r.describe()  #通过这个方法获取字段信息

#以上过程是一个非常郁闷的过程：函数返回了7个元素的item，可是这些item究竟是什么含义呢？

#终于在“Python Database API Specification v2.0”(http://www.python.org/dev/peps/pep-0249/)找到

"""

 .description
          
            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 are set to None if no
            meaningful values can be provided.

            This attribute will be None for operations that
            do not return rows or if the cursor has not had an
            operation invoked via the .execute*() method yet.
            
            The type_code can be interpreted by comparing it to the
            Type Objects specified in the section below.

"""

    for item in field_info:
        temp_dict = {'name':item[0],
                    'type_code':item[1],
                    'display_size':item[2],
                    'internal_size':item[3],
                    'precision':item[4],
                    'scale':item[5],
                    'null_ok':item[6]}
        fields.append(temp_dict)
    out_map['fields'] = fields

    #以下输出记录集的内容
    datas = []
    for i in range(r.num_rows()):
        (row,) = r.fetch_row()
        new_row = []
        for col in row:
            if type(col)==types.FloatType or \
                type(col)==types.IntType or \
                type(col)==types.LongType:
                new_row.append(col)
            elif type(col)==types.StringType:
                new_row.append(col)
            elif isinstance(col, datetime.date):
                new_row.append(col.strftime('%Y-%m-%d'))
                raise exceptions.TypeError,'unknown type:%s,value:%s' % (dir(type(col)), str(col))
        datas.append(new_row)
    out_map['root'] = datas
    print json.dumps(out_map)