python查库写库例子

环境:

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

 

方法1：字段拼接

#!/usr/bin/python3

import pymysql
import smtplib
import datetime
import time

now_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_data

def 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.close

if __name__ == '__main__' :
    querydata=query_data()
    insert_data(querydata)

 

方法2:直接写入(多记录写入，不好的地方就是查出的字段和写入的字段要保持一致)

#!/usr/bin/python3

import pymysql
import smtplib
import datetime
import time

now_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_cnt

def 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.close

if __name__ == '__main__' :
    (querydata,record_cnt) = query_data()
    if  ( record_cnt > 0) :
        insert_data(querydata)

 

方法2：字段拼接(\"%s\"处理字段有特殊符号的，比如单引号)

# -*- coding: utf-8 -*-
import pymysql
import smtplib
import datetime
import time


now_time = datetime.datetime.now()
yes_time = now_time + datetime.timedelta(days=-1)
select_sql = "select id,user,host,db,command,time,state,info from information_schema.processlist where time>0 and info is not null and user not in ('dmladmin') and db not in ('db_admin') limit 100"
##select_sql="select id,user,host,db,command,time,state,info from db_admin.tb_run_long_sql limit 5"

select_username="myuser"
select_password= 密码
port=3306
##select_dbname="db_admin"

insert_mysqlserver="rds3.mysql.rds.aliyuncs.com"
insert_username="myuser"
insert_password= 密码
insert_dbname="db_admin"


def query_data(rds_url,username,password,port) :
    # 打开数据库连接
    db = pymysql.connect(host=rds_url,user=username, password=password,port=port)
    ##print(sqltext)
    # 使用 cursor() 方法创建一个游标对象 cursor
    cursor = db.cursor()
    try:
        cursor.execute(select_sql)
        result_data = cursor.fetchall()
        record_cnt = len(result_data)
    except Exception,e:
        print(e)
    db.close()
    return result_data,record_cnt

def insert_data (querydata,rds_flag) :
    db = pymysql.connect(insert_mysqlserver,insert_username, insert_password, insert_dbname)
    cursor = db.cursor()
    l_rds_flag=rds_flag

    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]) + "'"
            l_info=row[7]
            insertsql="insert into tb_run_long_sql_alldb(rds_flag,id,user,host,db,command,time,state,info) values (%s,%s,\"%s\",\"%s\",\"%s\",\"%s\",%s,\"%s\",\"%s\")" % \
                       (l_rds_flag,l_id, l_user, l_host, l_db, l_command, l_time, l_state,l_info)
            ##print(insertsql) ##采用\"%s\"目的是处理sql语句里包含特殊符号的sql,比如单引号
            cursor.execute(insertsql)
        except Exception, e:
            print(e)

    
    db.commit()
    db.close

if __name__ == '__main__' :

    rds_url = {'rds1':'rds1.mysql.rds.aliyuncs.com',\
               'rds2':'rds2.mysql.rds.aliyuncs.com'}

    for key in rds_url:
       if key == 'rds1':
         rds_flag=1
       if key == 'rds2':
         rds_flag=2
  
       
       url=rds_url[key]
       ##print(url)        

       (querydata,record_cnt) = query_data(url,select_username,select_password,port)
       print(record_cnt)
       if  ( record_cnt > 0) :
           insert_data(querydata,rds_flag)