有人知道我如何使用mysqldb在Python中将MySQL表格(包含大量行)转换为字典对象列表吗?
我的意思是将一组MySQL行,具有列'a'、'b'和'c',转换为一个Python对象,看起来像这样:
data = [ { 'a':'A', 'b':(2, 4), 'c':3.0 }, { 'a':'Q', 'b':(1, 4), 'c':5.0 }, { 'a':'T', 'b':(2, 8), 'c':6.1 } ]
谢谢 :)
有人知道我如何使用mysqldb在Python中将MySQL表格(包含大量行)转换为字典对象列表吗?
我的意思是将一组MySQL行,具有列'a'、'b'和'c',转换为一个Python对象,看起来像这样:
data = [ { 'a':'A', 'b':(2, 4), 'c':3.0 }, { 'a':'Q', 'b':(1, 4), 'c':5.0 }, { 'a':'T', 'b':(2, 8), 'c':6.1 } ]
MySQLdb 有一个专门的游标类 DictCursor,你可以将所需使用的游标类传递给 MySQLdb.connect():
import MySQLdb.cursors
MySQLdb.connect(host='...', cursorclass=MySQLdb.cursors.DictCursor)
import MySQLdb.cursors
后面添加 MySQLdb.connect(host='...', cursorclass=MySQLdb.cursors.DictCursor)
才能使代码正常工作。(抱歉,我忍不住了!:D) - cregoximport MySQLdb
db = MySQLdb.connect(host='...', db='...', user='...t', passwd='...')
list_cursor = db.cursor()
dict_cursor = db.cursor(MySQLdb.cursors.DictCursor)
import MySQLdb.cursors
,否则在 Python 3.4 中会引发错误。 - David Lavierimysqlclient
1.3.12不需要额外的import
语句。 - Flimm我认为使用mysql.connector比MySQLdb更容易将选择结果转换为字典,并且支持更多的Python版本:
cursor = conn.cursor(dictionary=True)
详细例子:
import mysql.connector # pip install mysql-connector-python
conn = mysql.connector.connect(host="localhost", user="user", passwd="pass", database="dbname")
cursor = conn.cursor(dictionary=True)
sql = "SELECT * FROM `table` WHERE 1"
cursor.execute(sql)
rows = cursor.fetchall()
for row in rows:
row["col"]
这是一篇古老的帖子,虽然这个答案不完全符合OP的要求,但它与其要求非常相似。它不是生成字典列表,而是生成列表字典:
我也想提供生成字典的完整代码:
import MySQLdb
import MySQLdb.cursors
dict_serv = MySQLdb.connect(host = 'localhost', user = 'root', passwd = 'mypassword', cursorclass = MySQLdb.cursors.DictCursor)
with dict_serv as c:
c.execute("USE mydb")
c.execute("SELECT col1, col2 FROM mytable WHERE id = 'X123'")
# Save the dictionary to a separate variable
init_dict = c.fetchall()
# This line builds the column headers
sql_cols = [ col[0] for col in c.description ]
# This is a list comprehension within a dictionary comprehension
# which builds the full dictionary in a single line.
sql_dict = { k : [ d[k] for d in init_dict ] for k in sql_cols }
这将产生:
{ 'col1': ['apple','banana'], 'col2':['red','yellow'] }
使用PYTHON >= 3.6; 这里是通过以下方式实现的:
注意:这里我使用了2种数据库类型ORACLE 12g和MYSQL 5.6;主服务器是ORACLE,MYSQL仅用于软件的某些部分...然后...我的连接类代码:
myp3 = 'XXXXXXXXXXXXXXXXXXXXXXXXXX PASSWD MYSQL XXXXXXXXXXXXXXXXXXXXXXXXXXX'
import cx_Oracle
import MySQLdb
import MySQLdb.cursors
class oracle(object):
def __init__(self, serverORACLE=3, MYSQL='N', serverMYSQL=3):
## ORACLE connection!
try:
if serverORACLE == 1:
self.db = cx_Oracle.connect('sys/[xxxxPASSWORDxxxxx]@[xxxxxIP/HOSTxxxxxx]:1521/DATABASE_X')
print('ORACLE [ system/[xxxxPASSWORDxxxxx]@[xxxxxIP/HOSTxxxxxx]:1521 ] ===> CONNECTED')
if serverORACLE == 2:
self.db = cx_Oracle.connect('system/[xxxxPASSWORDxxxxx]@[xxxxxIP/HOSTxxxxxx]:1521/DATABASE_X')
print('ORACLE [ system/[xxxxPASSWORDxxxxx]@[xxxxxIP/HOSTxxxxxx]:1521 ] ===> CONNECTED')
if serverORACLE == 3:
self.db = cx_Oracle.connect('userLEVEL1/[xxxxPASSWORDxxxxx]@[xxxxxIP/HOSTxxxxxx]:1521/DATABASE_X')
print('ORACLE [ userLEVEL1/[xxxxPASSWORDxxxxx]@[xxxxxIP/HOSTxxxxxx]:1521 ] ===> CONNECTED')
if serverORACLE == 4:
self.db = cx_Oracle.connect('userLEVEL2/[xxxxPASSWORDxxxxx]@[xxxxxIP/HOSTxxxxxx]:1521/DATABASE_X')
print('ORACLE [ userLEVEL2/[xxxxPASSWORDxxxxx]@[xxxxxIP/HOSTxxxxxx]:1521 ] ===> CONNECTED')
self.cursor = self.db.cursor()
except Exception as e:
count = 0
S1 = ''
for W in str(e):
S1+=W
count+=1
if count >= 40:
S1+=' \n'
count = 0
print('\n\n ORACLE DATABASE ===> CONECTION FAILED!',
'\n error - module: ', S1)
##conexao MYSQL
if MYSQL=='S':
try:
if serverMYSQL == 1:
self.dbMySQL = MySQLdb.connect(user='root', host='XXXXXX HOST XXXXX', use_unicode=True, cursorclass=MySQLdb.cursors.DictCursor,
charset='utf8', port=3306, passwd=myp3, db='XXXX')
print('MySQL [ root / XXXXXX HOST XXXXX:3306 ] ===> CONNECTED')
if serverMYSQL == 2:
self.dbMySQL = MySQLdb.connect(user='XXXX USER XXXXX', host='XXXXXX HOST XXXXX', use_unicode=True, cursorclass=MySQLdb.cursors.DictCursor,
charset='utf8', port=3306, passwd=myp3, db='XXXX')
print('MySQL [ XXXX USER XXXXX / XXXXXX HOST XXXXX:3306 ] ===> CONNECTED')
self.cursorMYSQL = self.dbMySQL.cursor()
except Exception as e:
count = 0
S1 = ''
for W in str(e):
S1+=W
count+=1
if count >= 40:
S1+=' \n'
count = 0
print('\n\n MYSQL DATABASE ===> CONECTION FAILED!',
'\n error - module: ', S1)
"""
dictionary
和/或cursor
标签。 - cregox