类似下面的代码:
cur = con.cursor()
cur.execute('SELECT company_info.*, mail_server.*,send_mail.* FROM `send_mail` \
LEFT JOIN company_info ON (company_info.id=send_mail.company_id) \
LEFT JOIN mail_server ON company_info.mail_server=mail_server.id \
WHERE `send_time` < CURRENT_TIMESTAMP AND retry_count < 5;')
all_data = cur.fetchall()
for n in range(len(all_data)):
print all_data[0]
company_id = int(all_data[n][1])
mail_domain = all_data[n][2]
private_dkim = all_data[n][4]
hostname = all_data[n][7]
mail_server_ip = all_data[n][8]
id = all_data[n][10]
mail_from = all_data[n][16]
mail_to = all_data[n][17]
mail_cc = all_data[n][18]
mail_bcc = all_data[n][19]
msg_id = all_data[n][20]
mail_references = all_data[n][21]
content_type = all_data[n][22]
mail_subject = all_data[n][23]
mail_body = str((all_data[n][24]))
send_time= all_data[n][27]
retry_count = all_data[n][28]
error_log = all_data[n][29]
优化代码如下
from django.db import connections
def search(sql, connection_name='default'):
dbs = connections[connection_name]
cursor = dbs.cursor()
cursor.execute(sql)
columns = [_[0].lower() for _ in cursor.description]
results = [dict(zip(columns, _)) for _ in cursor]
cursor.close()
return results
sql = """
SELECT
company_info.*,
mail_server.*,
send_mail.*
FROM `send_mail`
LEFT JOIN company_info ON company_info.id=send_mail.company_id
LEFT JOIN mail_server ON company_info.mail_server=mail_server.id
WHERE
`send_time` < CURRENT_TIMESTAMP AND retry_count < 5
"""
results = search(sql)
print results