环境:

python2.7和python3.6都可以执行 

 

方法1：字段拼接

#!/usr/bin/python3import pymysqlimport smtplibimport datetimeimport timenow_time = datetime.datetime.now()yes_time = now_time + datetime.timedelta(days=-1)select_sql = "select id,user,host,db,command,time,state,info from tb_run_long_sql limit 1"select_mysqlserver="192.168.1.113"select_username="root"select_password= "yeemiao1117"select_dbname="db_admin"insert_mysqlserver="192.168.1.113"insert_username="root"insert_password= "yeemiao1117"insert_dbname="db_admin"def query_data() :    # 打开数据库连接    db = pymysql.connect(select_mysqlserver,select_username, select_password, select_dbname)    ##print(sqltext)    # 使用 cursor() 方法创建一个游标对象 cursor    cursor = db.cursor()    try:        cursor.execute(select_sql)        result_data = cursor.fetchall()    except Exception as e:        print(e)    db.close()    return result_datadef insert_data (querydata)  :    db = pymysql.connect(select_mysqlserver,select_username, select_password, select_dbname)    insert_sql="""insert into tb_run_long_sql_readonly(id,user,host,db,command,time,state,info) values (%s,%s,%s,%s,%s,%s,%s,%s)"""    cursor = db.cursor()    for row in querydata:        try:            l_id=row[0]            l_user="'" + row[1]+"'"            l_host="'" + row[2]+"'"            l_db="'"+ row[3] +"'"            l_command="'" + row[4]+ "'"            l_time=row[5]            l_state="'"+ row[6] + "'"            l_info="'" + str(row[7]) + "'"            insersql="insert into tb_run_long_sql_readonly ( id,user,host,db,command,time,state,info) values (%s,%s,%s,%s,%s,%s,%s)" % (l_id, l_user, l_host, l_db, l_command, l_time, l_state,l_info)            print(insersql)            ##cursor.execute("insert into tb_run_long_sql_readonly(id,user,host,db,command,time,state,info) values (%s,%s,%s,%s,%s,%s,%s,%s)"  %  (l_id,l_user,l_host,l_db,l_command,l_time,l_state,l_info))        except Exception as e:            print(e)    db.commit()    db.closeif __name__ == '__main__' :    querydata=query_data()    insert_data(querydata)

 

方法2:直接写入

#!/usr/bin/python3import pymysqlimport smtplibimport datetimeimport timenow_time = datetime.datetime.now()yes_time = now_time + datetime.timedelta(days=-1)select_sql = "select id,user,host,db,command,time,state,info from tb_run_long_sql where time>0 and info is not null and user not in ('dmladmin') and db not in ('db_admin') limit 100"select_mysqlserver="192.168.1.113"select_username="root"select_password= "123456"select_dbname="db_admin"insert_mysqlserver="192.168.1.113"insert_username="root"insert_password= "123456"insert_dbname="db_admin"def query_data() :    # 打开数据库连接    db = pymysql.connect(select_mysqlserver,select_username, select_password, select_dbname)    ##print(sqltext)    # 使用 cursor() 方法创建一个游标对象 cursor    cursor = db.cursor()    try:        cursor.execute(select_sql)        result_data = cursor.fetchall()        record_cnt = len(result_data)    except Exception as e:        print(e)    db.close()    return result_data,record_cntdef insert_data (querydata) :    db = pymysql.connect(insert_mysqlserver,insert_username, insert_password, insert_dbname)    insert_sql="""insert into tb_run_long_sql_readonly(id,user,host,db,command,time,state,info) values (%s,%s,%s,%s,%s,%s,%s,%s)"""    cursor = db.cursor()    cursor.executemany(insert_sql,querydata)    db.commit()    db.closeif __name__ == '__main__' :    (querydata,record_cnt) = query_data()    if  ( record_cnt > 0) :        insert_data(querydata)