mysql - python链接sql取出来的数值跟变量如何优化。

Question

类似下面的代码:

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]

Answer 1

优化代码如下

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