How can I get dict from sqlite query?

After you connect to SQLite: con = sqlite3.connect(.....) it is sufficient to just run:

con.row_factory = sqlite3.Row

Voila!

As mentioned by @gandalf's answer, one has to use conn.row_factory = sqlite3.Row, but the results are not directly dictionaries. One has to add an additional "cast" to dict in the last loop:

import sqlite3
conn = sqlite3.connect(":memory:")
conn.execute('create table t (a text, b text, c text)')
conn.execute('insert into t values ("aaa", "bbb", "ccc")')
conn.execute('insert into t values ("AAA", "BBB", "CCC")')
conn.row_factory = sqlite3.Row
c = conn.cursor()
c.execute('select * from t')
for r in c.fetchall():
    print(dict(r))

# {'a': 'aaa', 'b': 'bbb', 'c': 'ccc'}
# {'a': 'AAA', 'b': 'BBB', 'c': 'CCC'}

Get the results of the query

output_obj = con.execute(query)
results = output_obj.fetchall()

Option 1) Explicit Loop w/ Zip

for row in results:
    col_names = [tup[0] for tup in output_obj.description]
    row_values = [i for i in row]
    row_as_dict = dict(zip(col_names,row_values))

Option 2) Faster Loop w/ Dict Comp

for row in results:
    row_as_dict = {output_obj.description[i][0]:row[i] for i in range(len(row))}

I think you were on the right track. Let's keep this very simple and complete what you were trying to do:

import sqlite3
db = sqlite3.connect("test.sqlite3")
cur = db.cursor()
res = cur.execute("select * from table").fetchall()
data = dict(zip([c[0] for c in cur.description], res[0]))

print(data)

The downside is that .fetchall(), which is murder on your memory consumption, if your table is very large. But for trivial applications dealing with mere few thousands of rows of text and numeric columns, this simple approach is good enough.

For serious stuff, you should look into row factories, as proposed in many other answers.

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).

def getUsers(self,assoc=False):
        result = self.cursor.execute("SELECT * FROM users").fetchall()
        result_len = len(result)
        if(result_len == False): return
        if(assoc != True):
            return result
        else:
            result_formated = []
            columns = [column[0] for column in self.cursor.description]
            for row in result:
                row_dict = {}
                i = 0
                # print(result_len)
                while(i <= result_len):
                    row_dict[columns[i]] = row[i]
                    i += 1
                result_formated.append(row_dict)
            return result_formated

i'll just leave my bad code here