先贴一些资料的链接:
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)