python mysql.connector DictCursor？

python mysql.connector DictCursor?

提问by panofish

In Python mysqldbI could declare a cursor as a dictionary cursor like this:

在 Python 中，mysqldb我可以像这样将游标声明为字典游标：

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

This would enable me to reference columns in the cursorloop by name like this:

这将使我能够cursor按名称引用循环中的列，如下所示：

for row in cursor:   # Using the cursor as iterator 
    city = row["city"]
    state = row["state"]

Is it possible to create a dictionary cursor using this MySQL connector? http://dev.mysql.com/doc/connector-python/en/connector-python-example-cursor-select.html

是否可以使用此 MySQL 连接器创建字典游标？ http://dev.mysql.com/doc/connector-python/en/connector-python-example-cursor-select.html

Their example only returns a tuple.

他们的例子只返回一个元组。

I imagine the creators of MySQL would eventually do this for us?

我想 MySQL 的创建者最终会为我们做这件事吗？

采纳答案by panofish

A possible solution involves subclassing the MySQLCursorclass like this:

一个可能的解决方案是MySQLCursor像这样子类化这个类：

class MySQLCursorDict(mysql.connector.cursor.MySQLCursor):
    def _row_to_python(self, rowdata, desc=None):
        row = super(MySQLCursorDict, self)._row_to_python(rowdata, desc)
        if row:
            return dict(zip(self.column_names, row))
        return None

db = mysql.connector.connect(user='root', database='test')

cursor = db.cursor(cursor_class=MySQLCursorDict)

Now the _row_to_python()method returns a dictionaryinstead of a tuple.

现在该_row_to_python()方法返回 adictionary而不是 a tuple。

I found this on the mysql forum, and I believe it was posted by the mysql developers themselves. I hope they add it to the mysql connector package some day.

我在 mysql 论坛上找到了这个，我相信它是由 mysql 开发人员自己发布的。我希望有一天他们将它添加到 mysql 连接器包中。

I tested this and it does work.

我测试了这个，它确实有效。

UPDATE: As mentioned below by Karl M.W... this subclass is no longer needed in v2 of the mysql.connector. The mysql.connector has been updated and now you can use the following option to enable a dictionary cursor.

更新：正如 Karl MW 在下面提到的那样 .. 在 mysql.connector 的 v2 中不再需要这个子类。mysql.connector 已更新，现在您可以使用以下选项启用字典游标。

cursor = db.cursor(dictionary=True)

回答by J1MF0X

According to this article it is available by passing in 'dictionary=True' to the cursor constructor: http://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursordict.html

根据这篇文章，它可以通过将“dictionary=True”传递给游标构造函数来获得：http: //dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursordict.html

so I tried:

所以我试过：

cnx = mysql.connector.connect(database='bananas')
cursor = cnx.cursor(dictionary=True)

and got: TypeError: cursor() got an unexpected keyword argument 'dictionary'

并得到： TypeError：cursor() 得到了一个意外的关键字参数“字典”

and I tried:

我试过：

cnx = mysql.connector.connect(database='bananas')
cursor = cnx.cursor(named_tuple=True)

and got: TypeError: cursor() got an unexpected keyword argument 'named_tuple'

并得到： TypeError：cursor() 得到了一个意外的关键字参数“named_tuple”

and I tried this one too: cursor = MySQLCursorDict(cnx)

我也试过这个： cursor = MySQLCursorDict(cnx)

but to no avail. Clearly I'm on the wrong version here and I suspect we just have to be patient as the document at http://downloads.mysql.com/docs/connector-python-relnotes-en.a4.pdfsuggests these new features are in alpha phase at point of writing.

但无济于事。显然我在这里使用了错误的版本，我怀疑我们只需要耐心等待http://downloads.mysql.com/docs/connector-python-relnotes-en.a4.pdf 上的文档表明这些新功能是在写作时处于 alpha 阶段。

回答by Blairg23

This example works:

这个例子有效：

cnx = mysql.connector.connect(database='world')
cursor = cnx.cursor(dictionary=True)
cursor.execute("SELECT * FROM country WHERE Continent = 'Europe'")

print("Countries in Europe:")
for row in cursor:
    print("* {Name}".format(Name=row['Name']

Keep in mind that in this example, 'Name'is specific to the column name of the database being referenced.

请记住，在此示例中，'Name'特定于所引用数据库的列名。

Also, if you want to use stored procedures, do this instead:

此外，如果您想使用存储过程，请改为执行以下操作：

cursor.callproc(stored_procedure_name, args)
result = []
for recordset in cursor.stored_results():
    for row in recordset:
        result.append(dict(zip(recordset.column_names,row)))

where stored_procedure_nameis the name of the stored procedure to use and argsis the list of arguments for that stored procedure (leave this field empty like []if no arguments to pass in).

其中stored_procedure_name是要使用的存储过程的名称，是该存储过程args的参数列表（将此字段留空，就像[]没有要传入的参数一样）。

This is an example from the MySQLdocumentation found here: http://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursordict.html

这是MySQL此处找到的文档中的一个示例：http: //dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursordict.html

回答by KenH

Using Python 3.6.2 and MySQLdb version 1.3.10, I got this to work with:

使用 Python 3.6.2 和 MySQLdb 版本 1.3.10，我可以使用它：

import MySQLdb
import MySQLdb.cursors

...

conn = MySQLdb.connect(host='...', 
                       <connection info>, 
                       cursorclass=MySQLdb.cursors.DictCursor)

try:
    with conn.cursor() as cursor:
        query = '<SQL>'
        data = cursor.fetchall()
        for record in data:
            ... record['<field name>'] ...

finally:
    conn.close()

I'm using PyCharm, and simply dug into the MySQLdb modules connections.py and cursors.py.

我正在使用 PyCharm，并简单地挖掘了 MySQLdb 模块 connection.py 和 cursors.py。