Sqlite和Python -- 使用fetchone()返回一个字典?[英] Sqlite and Python -- return a dictionary using fetchone()?

本文是小编为大家收集整理的关于Sqlite和Python -- 使用fetchone()返回一个字典?的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

我在Python 2.5中使用SQLITE3.我创建了一个看起来像这样的表:

   create table votes (
      bill text,
      senator_id text,
      vote text)

我正在使用类似的东西访问它:

v_cur.execute("select * from votes")
row = v_cur.fetchone()
bill = row[0]
senator_id = row[1]
vote = row[2]

我想做的是让fetchone(或其他方法)返回字典,而不是列表,以便我可以按名称而不是位置参考字段.例如:

bill = row['bill'] 
senator_id = row['senator_id']
vote = row['vote']

我知道您可以使用MySQL进行此操作,但是有人知道如何使用sqlite做到这一点?

谢谢!

推荐答案

过去我做过的方式:

def dict_factory(cursor, row):
    d = {}
    for idx,col in enumerate(cursor.description):
        d[col[0]] = row[idx]
    return d

然后您在连接中进行设置:

from pysqlite2 import dbapi2 as sqlite
conn = sqlite.connect(...)
conn.row_factory = dict_factory

这在Pysqlite-2.4.1和Python 2.5.4.

其他推荐答案

实际上在sqlite3中有一个选项.将连接对象的row_factory成员更改为sqlite3.Row:

conn = sqlite3.connect('db', row_factory=sqlite3.Row)

conn.row_factory = sqlite3.Row

这将允许您通过名称 - 词典式访问行元素或索引访问行元素.这比创建自己的工作能力要高得多.

其他推荐答案

我最近在使用sqlite3.Row()时尝试做类似的事情.虽然sqlite3.row()非常适合提供类似词典的接口或元素之类的接口,但当我使用** kwargs在该行中进行管道时,它不起作用.因此,需要一种将其转换为词典的快速方法.我意识到只需使用itertools即可将ROW()对象转换为字典.

python 2:

db.row_factory = sqlite3.Row
dbCursor = db.cursor()
dbCursor.execute("SELECT * FROM table")
row = dbCursor.fetchone()

rowDict = dict(itertools.izip(row.keys(), row))

或python 3,更简单:

dbCursor = db.cursor()
dbCursor.execute("SELECT * FROM table")
row = dbCursor.fetchone()
rowDict = dict(zip([c[0] for c in dbCursor.description], row))

同样,您可以使用dbcursor.fetchall()命令,然后将整个行转换为for loop中的字典列表.

本文地址:https://www.itbaoku.cn/post/597458.html

问题描述

I'm using sqlite3 in python 2.5. I've created a table that looks like this:

   create table votes (
      bill text,
      senator_id text,
      vote text)

I'm accessing it with something like this:

v_cur.execute("select * from votes")
row = v_cur.fetchone()
bill = row[0]
senator_id = row[1]
vote = row[2]

What I'd like to be able to do is have fetchone (or some other method) return a dictionary, rather than a list, so that I can refer to the field by name rather than position. For example:

bill = row['bill'] 
senator_id = row['senator_id']
vote = row['vote']

I know you can do this with MySQL, but does anyone know how to do it with SQLite?

Thanks!!!

推荐答案

The way I've done this in the past:

def dict_factory(cursor, row):
    d = {}
    for idx,col in enumerate(cursor.description):
        d[col[0]] = row[idx]
    return d

Then you set it up in your connection:

from pysqlite2 import dbapi2 as sqlite
conn = sqlite.connect(...)
conn.row_factory = dict_factory

This works under pysqlite-2.4.1 and python 2.5.4.

其他推荐答案

There is actually an option for this in sqlite3. Change the row_factory member of the connection object to sqlite3.Row:

conn = sqlite3.connect('db', row_factory=sqlite3.Row)

or

conn.row_factory = sqlite3.Row

This will allow you to access row elements by name--dictionary-style--or by index. This is much more efficient than creating your own work-around.

其他推荐答案

I was recently trying to do something similar while using sqlite3.Row(). While sqlite3.Row() is great for providing a dictionary-like interface or a tuple like interface, it didn't work when I piped in the row using **kwargs. So, needed a quick way of converting it to a dictionary. I realised that the Row() object can be converted to a dictionary simply by using itertools.

Python 2:

db.row_factory = sqlite3.Row
dbCursor = db.cursor()
dbCursor.execute("SELECT * FROM table")
row = dbCursor.fetchone()

rowDict = dict(itertools.izip(row.keys(), row))

Or in Python 3, more simply:

dbCursor = db.cursor()
dbCursor.execute("SELECT * FROM table")
row = dbCursor.fetchone()
rowDict = dict(zip([c[0] for c in dbCursor.description], row))

Similarly, you can use the dbCursor.fetchall() command and convert the entire set of rows to a list of dictionaries in a for loop.