如何将MySQL结果检索到可以按索引搜索的词典中?(在Python中)

Question

我正在学习Python编程来构建一些自动化，我正在构建的脚本的一部分在对象上迭代，我希望能够在MySQL数据库中搜索相同的项目，但不太确定如何做到这一点。

使用MariaDB教程，我已经成功地检索到了我想要的行，但我不确定如何在cursor列表中搜索项目，而不必手动迭代每个结果。

这是我迄今为止的代码：

cursor = mariadb_connection.cursor(dictionary=True)
# here I get a list of items from an external service
playlists = sp.user_playlists(username)
# here I retrieve my playlists from a DB
cursor.execute("SELECT id, name, monitor FROM playlists")
rows = cursor.fetchall()
while playlists:
for i, playlist in enumerate(playlists['items']):
print("%4d %s %s" % (i + 1 + playlists['offset'], playlist['id'],  playlist['name']))
# while iterating through the playlists from the external service, I want to see if its included within my "rows" dictionary.
key = "1"
for row in rows:
print("ID: {}, Name: {}, Monitor: {}".format(row['id'],row['name'], row['monitor']))
# this does not work, there is an ID
if key in rows:
print("found key")
break
else:
print("NOT found key")

if playlists['next']:
playlists = sp.next(playlists)
else:
playlists = None

我已经就我需要帮助的领域发表了意见。

Answer 1

首先，一些注释。在您的代码中，您将key设置为常数"1"，它永远不会改变，这是不对的。从查询返回的rows将是一个列表字典。语句if key in rows:不是针对每个字典的键，而是针对每一行检查键值，这是一个完整的字典，这是不对的。这应该是if key == row['id']:。因此，几个变化应该会有所帮助：

cursor = mariadb_connection.cursor(dictionary=True)
# here I get a list of items from an external service
playlists = sp.user_playlists(username)
# here I retrieve my playlists from a DB
cursor.execute("SELECT id, name, monitor FROM playlists")
rows = cursor.fetchall()
# build a dictionary of keys:
id_dict = {row['id']: row for row in rows}
while playlists:
for i, playlist in enumerate(playlists['items']):
print("%4d %s %s" % (i + 1 + playlists['offset'], playlist['id'],  playlist['name']))
# while iterating through the playlists from the external service, I want to see if its included within my "rows" dictionary.
key = playlist['id'] # the key
if key in id_dict:
row = id_dict[key]
print("ID: {}, Name: {}, Monitor: {}".format(row['id'],row['name'], row['monitor']))
print("found key")
else:
print("NOT found key")
if playlists['next']:
playlists = sp.next(playlists)
else:
playlists = None

另一种更高效的

最初不要从数据库中查询所有播放列表：

cursor = mariadb_connection.cursor(dictionary=True)
# here I get a list of items from an external service
playlists = sp.user_playlists(username)
# here I retrieve my playlists from a DB
while playlists:
for i, playlist in enumerate(playlists['items']):
print("%4d %s %s" % (i + 1 + playlists['offset'], playlist['id'],  playlist['name']))
# while iterating through the playlists from the external service, I want to see if its included within my "rows" dictionary.
id = playlist['id']
cursor.execute("SELECT id, name, monitor FROM playlists where id = %s", (id,))
row = cursor.fetchone() # this the match, if any
if row:
print("ID: {}, Name: {}, Monitor: {}".format(row['id'],row['name'], row['monitor']))
print("found key")
else:
print("NOT found key")
if playlists['next']:
playlists = sp.next(playlists)
else:
playlists = None