sqlite 쿼리에서 dict를 어떻게 얻을 수 있습니까?

sqlite 쿼리에서 dict를 어떻게 얻을 수 있습니까?

---

db = sqlite.connect("test.sqlite")
res = db.execute("select * from table")

반복을 통해 행에 해당하는 목록을 얻습니다.

for row in res:
    print row

열 이름을 얻을 수 있습니다.

col_name_list = [tuple[0] for tuple in res.description]

그러나 목록 대신 사전을 얻는 기능이나 설정이 있습니까?

{'col1': 'value', 'col2': 'value'}

아니면 스스로해야합니까?

---

문서의 예제와 같이 row_factory를 사용할 수 있습니다 .

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"]

또는 문서에서이 예제 바로 뒤에 제공되는 조언을 따르십시오.

튜플을 반환하는 것으로 충분하지 않고 열에 대한 이름 기반 액세스를 원하는 경우 row_factory를 고도로 최적화 된 sqlite3.Row 유형으로 설정하는 것을 고려해야합니다. Row는 거의 메모리 오버 헤드없이 열에 대한 인덱스 기반 및 대소 문자를 구분하지 않는 이름 기반 액세스를 모두 제공합니다. 사용자 정의 사전 기반 접근 방식이나 심지어 db_row 기반 솔루션보다 낫습니다.

---

Adam Schmideg의 답변과 Alex Martelli의 답변에 부분적으로 언급되어 있지만이 질문에 답변한다고 생각했습니다. 같은 질문을 가진 나와 같은 다른 사람들이 쉽게 답을 찾을 수 있도록.

conn = sqlite3.connect(":memory:")

#This is the important part, here we are setting row_factory property of
#connection object to sqlite3.Row(sqlite3.Row is an implementation of
#row_factory)
conn.row_factory = sqlite3.Row
c = conn.cursor()
c.execute('select * from stocks')

result = c.fetchall()
#returns a list of dictionaries, each item in list(each dictionary)
#represents a row of the table

---

sqlite3.Row 클래스를 사용하더라도 다음과 같은 형식으로 문자열 서식을 사용할 수 없습니다.

print "%(id)i - %(name)s: %(value)s" % row

이를 극복하기 위해 행을 가져와 사전으로 변환하는 도우미 함수를 사용합니다. 딕셔너리 객체가 Row 객체보다 선호 될 때만 사용합니다 (예 : Row 객체가 사전 API를 기본적으로 지원하지 않는 문자열 형식화와 같은 경우). 그러나 다른 모든 경우에는 Row 개체를 사용하십시오.

def dict_from_row(row):
    return dict(zip(row.keys(), row))       

---

에서 PEP 249 :

Question: 

   How can I construct a dictionary out of the tuples returned by
   .fetch*():

Answer:

   There are several existing tools available which provide
   helpers for this task. Most of them use the approach of using
   the column names defined in the cursor attribute .description
   as basis for the keys in the row dictionary.

   Note that the reason for not extending the DB API specification
   to also support dictionary return values for the .fetch*()
   methods is that this approach has several drawbacks:

   * Some databases don't support case-sensitive column names or
     auto-convert them to all lowercase or all uppercase
     characters.

   * Columns in the result set which are generated by the query
     (e.g.  using SQL functions) don't map to table column names
     and databases usually generate names for these columns in a
     very database specific way.

   As a result, accessing the columns through dictionary keys
   varies between databases and makes writing portable code
   impossible.

그래서 네, 스스로하십시오.

---

더 짧은 버전 :

db.row_factory = lambda c, r: dict([(col[0], r[idx]) for idx, col in enumerate(c.description)])

---

내 테스트에서 가장 빠름 :

conn.row_factory = lambda c, r: dict(zip([col[0] for col in c.description], r))
c = conn.cursor()

%timeit c.execute('SELECT * FROM table').fetchall()
19.8 µs ± 1.05 µs per loop (mean ± std. dev. of 7 runs, 100000 loops each)

대 :

conn.row_factory = lambda c, r: dict([(col[0], r[idx]) for idx, col in enumerate(c.description)])
c = conn.cursor()

%timeit c.execute('SELECT * FROM table').fetchall()
19.4 µs ± 75.6 ns per loop (mean ± std. dev. of 7 runs, 100000 loops each)

결정 :)

---

SQLite에 연결 한 후 : con = sqlite3.connect(.....)다음을 실행하는 것으로 충분합니다.

con.row_factory = sqlite3.Row

짜잔!

---

Similar like before-mentioned solutions, but most compact:

db.row_factory = lambda C, R: { c[0]: R[i] for i, c in enumerate(C.description) }

---

Or you could convert the sqlite3.Rows to a dictionary as follows. This will give a dictionary with a list for each row.

    def from_sqlite_Row_to_dict(list_with_rows):
    ''' Turn a list with sqlite3.Row objects into a dictionary'''
    d ={} # the dictionary to be filled with the row data and to be returned

    for i, row in enumerate(list_with_rows): # iterate throw the sqlite3.Row objects            
        l = [] # for each Row use a separate list
        for col in range(0, len(row)): # copy over the row date (ie. column data) to a list
            l.append(row[col])
        d[i] = l # add the list to the dictionary   
    return d

---

A generic alternative, using just three lines

def select_column_and_value(db, sql, parameters=()):
    execute = db.execute(sql, parameters)
    fetch = execute.fetchone()
    return {k[0]: v for k, v in list(zip(execute.description, fetch))}

con = sqlite3.connect('/mydatabase.db')
c = con.cursor()
print(select_column_and_value(c, 'SELECT * FROM things WHERE id=?', (id,)))

But if your query returns nothing, will result in error. In this case...

def select_column_and_value(self, sql, parameters=()):
    execute = self.execute(sql, parameters)
    fetch = execute.fetchone()

    if fetch is None:
        return {k[0]: None for k in execute.description}

    return {k[0]: v for k, v in list(zip(execute.description, fetch))}

or

def select_column_and_value(self, sql, parameters=()):
    execute = self.execute(sql, parameters)
    fetch = execute.fetchone()

    if fetch is None:
        return {}

    return {k[0]: v for k, v in list(zip(execute.description, fetch))}

---

import sqlite3

db = sqlite3.connect('mydatabase.db')
cursor = db.execute('SELECT * FROM students ORDER BY CREATE_AT')
studentList = cursor.fetchall()

columnNames = list(map(lambda x: x[0], cursor.description)) #students table column names list
studentsAssoc = {} #Assoc format is dictionary similarly


#THIS IS ASSOC PROCESS
for lineNumber, student in enumerate(studentList):
    studentsAssoc[lineNumber] = {}

    for columnNumber, value in enumerate(student):
        studentsAssoc[lineNumber][columnNames[columnNumber]] = value


print(studentsAssoc)

The result is definitely true, but I do not know the best.

---

Dictionaries in python provide arbitrary access to their elements. So any dictionary with "names" although it might be informative on one hand (a.k.a. what are the field names) "un-orders" the fields, which might be unwanted.

Best approach is to get the names in a separate list and then combine them with the results by yourself, if needed.

try:
         mycursor = self.memconn.cursor()
         mycursor.execute('''SELECT * FROM maintbl;''')
         #first get the names, because they will be lost after retrieval of rows
         names = list(map(lambda x: x[0], mycursor.description))
         manyrows = mycursor.fetchall()

         return manyrows, names

Also remember that the names, in all approaches, are the names you provided in the query, not the names in database. Exception is the SELECT * FROM

If your only concern is to get the results using a dictionary, then definitely use the conn.row_factory = sqlite3.Row (already stated in another answer).

참고URL : https://stackoverflow.com/questions/3300464/how-can-i-get-dict-from-sqlite-query