Python Mysql Select Dict

Python Select Mysql 日期转换字符串 ，转换为字典

 

Python 读取出来的数据格式不是正规Json ，读取出来，直接是字典

 

Python 2.7

import MySQLdb
from MySQLdb import converters as cov
conv = cov.conversions.copy()
conv[246] = float  # convert decimals to floats
conv[10] = str  # convert dates to strings
conv[11] = str  # convert TimeDelta_or_None to strings
conv[12] = str  # convert DateTime_or_None to strings
# conv[15] = str  # convert DateTime_or_None to strings
conv[7] = str  # convert DateTime_or_None to strings
# conv[8] = str  # convert DateTime_or_None to strings

conn = MySQLdb.connect(
    host='dbhost',
    port=3306,
    user='root',
    passwd='',
    db='mysql',
    charset='utf8',
     conv=conv
)

cur = conn.cursor(cursorclass = MySQLdb.cursors .DictCursor)
cur.execute("select * from proxies_priv")
test_data = cur.fetchall()
print test_data
cur.close()
conn.close()

Python 3.+ tornado

#!/usr/bin/env python
from __future__ import print_function

import pymysql
from tornado import ioloop, gen
from tornado_mysql import pools

from pymysql import  converters as cov
# from MySQLdb import converters as cov
from tornado_mysql.cursors import DictCursor

pools.DEBUG = True
conv = cov.conversions.copy()
conv[246] = float  # convert decimals to floats
conv[10] = str  # convert dates to strings
conv[11] = str  # convert TimeDelta_or_None to strings
conv[12] = str  # convert DateTime_or_None to strings
# conv[15] = str  # convert DateTime_or_None to strings
conv[7] = str  # convert DateTime_or_None to strings

POOL = pools.Pool(
    dict(host='dbhost', port=3306, user='root', passwd='', db='mysql',
    charset='utf8',
     conv=conv ,cursorclass=DictCursor)
    ,
    max_idle_connections=1,
    max_recycle_sec=3)

@gen.coroutine
def worker(n):

    cur = yield POOL.execute("SELECT  * FROM proxies_priv limit 10")
    print(cur.fetchall())

@gen.coroutine
def main():
    workers = [worker(i) for i in range(1)]
    yield workers


ioloop.IOLoop.current().run_sync(main)
print(POOL._opened_conns)

 

输出结果

[{'User': 'root', 'Host': 'localhost', 'Proxied_host': '', 'With_grant': 1, 'Proxied_user': '', 'Timestamp': '2017-05-04 09:07:49', 'Grantor': ''}, {'User': 'root', 'Host': 'pe-stats.wondershare.cn', 'Proxied_host': '', 'With_grant': 1, 'Proxied_user': '', 'Timestamp': '2017-05-04 09:07:49', 'Grantor': ''}]