Python查询Mysql, sqlite时返回字典结构的代码

怼烎@ 2022-07-13 05:53 185阅读 0赞

## MySQLdb ##

MySQLdb默认查询结果都是返回tuple，输出时候不是很方便，必须按照0，1这样读取，无意中在网上找到简单的修改方法，就是传递一个cursors.DictCursor就行。默认程序：

import MySQLdb 
    db = MySQLdb.connect(host = ´localhost´, user = ´root´, passwd = ´123456´, db = ´test´) 
    cursor = db.cursor() 
    cursor.execute(´select * from table´) rs = cursor.fetchall() print rs

返回类似如下  
((1000L, 0L), (2000L, 0L), (3000L, 0L))  
修改后：

import MySQLdb 
    import MySQLdb.cursors 
    db = MySQLdb.connect(host = ´localhost´, user = ´root´, passwd = ´123456´, db = ´test´,cursorclass = MySQLdb.cursors.DictCursor) 
    cursor = db.cursor() 
    cursor.execute(´select * from table´) 
    rs = cursor.fetchall() 
    print rs

返回类似如下  
(\{‘age’: 0L, ‘num’: 1000L\}, \{‘age’: 0L, ‘num’: 2000L\}, \{‘age’: 0L, ‘num’: 3000L\}) 或者也可以用下面替换connect和cursor部分

db = MySQLdb.connect(host = ´localhost´, user = ´root´, passwd = ´123456´, db = ´test´) 
    cursor = conn.cursor(cursorclass = MySQLdb.cursors.DictCursor)

参考：[http://www.jb51.net/article/30597.htm][http_www.jb51.net_article_30597.htm]

或者也可以用下面替换connect和cursor部分  
db = MySQLdb.connect(host = ‘localhost’, user = ‘root’, passwd = ´123456´, db = ´test´）  
cursor = conn.cursor(cursorclass = MySQLdb.cursors.DictCursor）

## SQLite ##

1 查询记录条数  
新建一个名为query-cpu-temp.py的文件，文件内容如下。

# -*- coding: utf-8 -*- 
    import sqlite3 
    
    # 连接数据库 
    con = sqlite3.connect("cpu.db")  
    cur = con.cursor()  
    
    name = 'RPi.CPU'  
    # 查询记录总数 
    cur.execute("select count(*) from temps where name=(?);", (name, ))  
    total = cur.fetchone()  
    
    # 返回元组类型 
    print type(total) 
    print type(total[0]) 
    print total[0]

【简要说明】
    【1】cur.execute("select count(*) from temps where name=(?);", (name, )) 查询表中字段name为RPi.CPU的记录总数
    【2】cur.fetchone() 获得一条记录，返回的结果为元组类型。
    【3】total[0]，返回的结果为只有一个元素的元组类型，total[0]为记录总数，类型为Int。
    【4】返回结果

# -*- coding: utf-8 -*- 
    import sqlite3  
    
    # 连接数据库 
    con = sqlite3.connect("cpu.db")  
    cur = con.cursor()  
    
    name = 'RPi.CPU'  
    # 查询数据库，获得最近一小时的记录 
    cur.execute('''''SELECT * FROM temps WHERE name=(?) AND tdatetime > datetime('now', 'localtime', '-1 hours') ORDER BY tdatetime ASC;''', (name, ))  
    # 获得所有结果 
    rows = cur.fetchall()  
    
    for row in rows:  
        print row

【简要说明】
    【1】WHERE name=(?) AND tdatetime > datetime('now', 'localtime', '-1 hours') ，查询一小时之前的温度参数， 'localtime'表示本时区时间。
    【2】cur.fetchall() 获得符合条件的所有记录。
    【3】返回的结果为列表类型，而类表中的每个元素为元组类型

(u’RPi.CPU’, u’2014-08-04 20:07:53’, 46.5)  
(u’RPi.CPU’, u’2014-08-04 20:12:53’, 46.5)  
(u’RPi.CPU’, u’2014-08-04 20:17:53’, 46.5)  
(u’RPi.CPU’, u’2014-08-04 20:22:54’, 47.1)  
(u’RPi.CPU’, u’2014-08-04 20:27:54’, 47.1)  
(u’RPi.CPU’, u’2014-08-04 20:32:54’, 47.6)  
(u’RPi.CPU’, u’2014-08-04 20:37:54’, 46.5)  
(u’RPi.CPU’, u’2014-08-04 20:42:54’, 47.6)  
(u’RPi.CPU’, u’2014-08-04 20:47:54’, 47.1)  
(u’RPi.CPU’, u’2014-08-04 20:52:54’, 47.1)  
(u’RPi.CPU’, u’2014-08-04 20:57:54’, 47.6)  
(u’RPi.CPU’, u’2014-08-04 21:02:55’, 47.6)

3 转化为字典格式的工厂方法  
在进行网络传输的过程中，多数通过JSON数据格式进行交换，在python中字典格式能更好的转换为JSON格式。

# -*- coding: utf-8 -*- 
    import sqlite3  
    
    def dict_factory(cursor, row):  
        d = {}  
        for idx, col in enumerate(cursor.description):  
            d[col[0]] = row[idx]  
        return d  
    
    # 连接数据库 
    con = sqlite3.connect("cpu.db")  
    # 指定工厂方法 
    con.row_factory = dict_factory  
    cur = con.cursor()  
    
    name = 'RPi.CPU'  
    # 查询数据库，获得最近一小时的记录 
    cur.execute('''''SELECT * FROM temps WHERE name=(?) AND tdatetime > datetime('now', 'localtime', '-1 hours') ORDER BY tdatetime ASC;''', (name, ))  
    
    rows = cur.fetchall()  
    
    for row in rows:  
        print row

【简单说明】
    【1】def dict_factory(cursor, row): 元组类型转换为字典类型，该函数来自python sqlite说明文档。
    【2】con.row_factory = dict_factory 指定工厂方法
    【3】返回结果，请注意()变为了{}，表明返回结果为字典类型。

\{‘tdatetime’: u’2014-08-04 20:22:54’, ‘name’: u’RPi.CPU’, ‘temperature’: 47.1\}  
\{‘tdatetime’: u’2014-08-04 20:27:54’, ‘name’: u’RPi.CPU’, ‘temperature’: 47.1\}  
\{‘tdatetime’: u’2014-08-04 20:32:54’, ‘name’: u’RPi.CPU’, ‘temperature’: 47.6\}  
\{‘tdatetime’: u’2014-08-04 20:37:54’, ‘name’: u’RPi.CPU’, ‘temperature’: 46.5\}  
\{‘tdatetime’: u’2014-08-04 20:42:54’, ‘name’: u’RPi.CPU’, ‘temperature’: 47.6\}  
\{‘tdatetime’: u’2014-08-04 20:47:54’, ‘name’: u’RPi.CPU’, ‘temperature’: 47.1\}  
\{‘tdatetime’: u’2014-08-04 20:52:54’, ‘name’: u’RPi.CPU’, ‘temperature’: 47.1\}  
\{‘tdatetime’: u’2014-08-04 20:57:54’, ‘name’: u’RPi.CPU’, ‘temperature’: 47.6\}  
\{‘tdatetime’: u’2014-08-04 21:02:55’, ‘name’: u’RPi.CPU’, ‘temperature’: 47.6\}  
\{‘tdatetime’: u’2014-08-04 21:07:55’, ‘name’: u’RPi.CPU’, ‘temperature’: 47.1\}  
\{‘tdatetime’: u’2014-08-04 21:12:55’, ‘name’: u’RPi.CPU’, ‘temperature’: 47.1\}  
\{‘tdatetime’: u’2014-08-04 21:17:55’, ‘name’: u’RPi.CPU’, ‘temperature’: 47.6\}

4 总结  
【1】获得数据库记录的方法有 fetchone和fetchall。  
【2】默认情况下返回元组结果。  
【3】需要通过修改row\_factory属性，把元组类型转换为字典类型。

参考：[http://blog.csdn.net/xukai871105/article/details/38375729][http_blog.csdn.net_xukai871105_article_details_38375729]

5 row\_factory  
参考：[https://docs.python.org/2/library/sqlite3.html\#sqlite3.Connection.row\_factory][https_docs.python.org_2_library_sqlite3.html_sqlite3.Connection.row_factory]

row\_factory  
You can change this attribute to a callable that accepts the cursor and the original row as a tuple and will return the real result row. This way, you can implement more advanced ways of returning results, such as returning an object that can also access columns by name.

Example:

import sqlite3
    
    def dict_factory(cursor, row):
        d = {}
        for idx, col in enumerate(cursor.description):
            d[col[0]] = row[idx]
        return d
    
    con = sqlite3.connect(":memory:")
    con.row_factory = dict_factory
    cur = con.cursor()
    cur.execute("select 1 as a")
    print cur.fetchone()["a"]

If returning a tuple doesn’t suffice and you want name-based access to columns, you should consider setting row\_factory to the highly-optimized sqlite3.Row type. Row provides both index-based and case-insensitive name-based access to columns with almost no memory overhead. It will probably be better than your own custom dictionary-based approach or even a db\_row based solution.

[http_www.jb51.net_article_30597.htm]: http://www.jb51.net/article/30597.htm
[http_blog.csdn.net_xukai871105_article_details_38375729]: http://blog.csdn.net/xukai871105/article/details/38375729
[https_docs.python.org_2_library_sqlite3.html_sqlite3.Connection.row_factory]: https://docs.python.org/2/library/sqlite3.html#sqlite3.Connection.row_factory