Python: Use MySQLdb to Import a MySQL Table as a Dictionary

Python: use mysqldb to import a MySQL table as a dictionary?

MySQLdb has a separate cursor class for this, the DictCursor. You can pass the cursor class you want to use to MySQLdb.connect():

import MySQLdb.cursors
MySQLdb.connect(host='...', cursorclass=MySQLdb.cursors.DictCursor)

fetching mysql data as python dictionary

dict keys have no easily predictable order. To obtain the database table fields in the order in which they appear in the database, use the cursor's description attribute:

fields = [item[0] for item in cursor.description]

---

For example,

import MySQLdb
import MySQLdb.cursors as cursors
import config

connection = MySQLdb.connect(
    host=config.HOST, user=config.USER,
    passwd=config.PASS, db=config.MYDB,
    cursorclass=cursors.DictCursor)

with connection as cursor:
    cursor.execute('DROP TABLE IF EXISTS test')
    cursor.execute("""CREATE TABLE test (foo int, bar int, baz int)""")
    cursor.execute("""INSERT INTO test (foo, bar, baz) VALUES (%s,%s,%s)""", (1,2,3))
    cursor.execute('SELECT * FROM test')
    data = cursor.fetchone()
    fields = [item[0] for item in cursor.description]

data.keys() may return the fields in any order:

    print(data.keys())
    # ['baz', 'foo', 'bar']

But fields is always ('foo', 'bar', 'baz'):

    print(fields)
    # ('foo', 'bar', 'baz')

Python, MySQL and SELECT output to dictionary with column names for keys

Please use dictionary cursor:

cursor = conn.cursor (MySQLdb.cursors.DictCursor)

How to store mySQL query as python dictionary and call later in script?

I think you're looking for the fetchall method. After you call

cur.execute("""SELECT number, empid FROM rfid """)

you would do:

rows = cur.fetchall()

and then rows would contain a list of tuples. If you specifically need it to be a dictionary, you could just do dict(rows) in this example but that only works because your query happens to return two columns. If it was more than two columns you would need something like:

rows_dict = dict([ k[0], k[1:]) for k in rows])

(see: Converting List of 3 Element Tuple to Dictionary)

If the dictionary you were imagining had column names as keys you need to look at DictCursor (Python: use mysqldb to import a MySQL table as a dictionary?).

Save Table from MySQL as dictionary in Python with MySQL Connector

I found the solution:

for row in mycursor:  f[row['JT']]=row["f"]

MySQL: pymysql or mysqldb to access a dictionary cursor functionality

Try to use pymysql.connect instead of mysql.connector.connect

# !/usr/bin/env python
import sys
import pymysql

db = pymysql.connect(
    host="localhost",
    user="xxx",
    passwd="yyy",
    database="zzz")
print(db)
mycursor = db.cursor(pymysql.cursors.DictCursor)
mycursor.execute("select * from table;")
myresult = mycursor.fetchall()
for x in myresult:
    print("row ", mycursor.rowcount, "id ", x["id"], " date ", x["date_Time"])

Read database entries from mysql in the form of dictionary in python

Set the cursor factory when you connect:

db_conn = mdb.connect(
    host="localhost", user="username", passwd="password", db="db_name",
    charset='utf8', cursorclass=mdb.cursors.DictCursor)

Python - mysqlDB, sqlite result as dictionary

David Beazley has a nice example of this in his Python Essential Reference.

I don't have the book at hand, but I think his example is something like this:

def dict_gen(curs):
    ''' From Python Essential Reference by David Beazley
    '''
    import itertools
    field_names = [d[0].lower() for d in curs.description]
    while True:
        rows = curs.fetchmany()
        if not rows: return
        for row in rows:
            yield dict(itertools.izip(field_names, row))

Sample usage:

>>> import sqlite3
>>> conn = sqlite3.connect(':memory:')
>>> c = conn.cursor()
>>> c.execute('create table test (col1,col2)')
<sqlite3.Cursor object at 0x011A96A0>
>>> c.execute("insert into test values (1,'foo')")
<sqlite3.Cursor object at 0x011A96A0>
>>> c.execute("insert into test values (2,'bar')")
<sqlite3.Cursor object at 0x011A96A0>
# `dict_gen` function code here
>>> [r for r in dict_gen(c.execute('select * from test'))]
[{'col2': u'foo', 'col1': 1}, {'col2': u'bar', 'col1': 2}]

---

Related Topics