python导出insert 数据sql脚本

生产环境和测试环境不在一个网段,kettle无法实现跨网段数据导出,而Django ORM框架migrate总会生成一些系统表,公司有规定不能随便建表建库,经过这几天的学习,马马虎虎在我本地搭建了个后台,可以查看每天数据运行情况,目前问题是,生产环境数据每天更新,而我本地报表数据库只有昨天数据,因此本地搭建的后台数据就无法实时更新了,因而用到下面逻辑去解决这个问题,而且构造动态 insert values部分语句 需要将数据转化为字符串 所以我源表所有数据类型都是varchar类型

导出报表数据表的今天数据sql脚本,复制放入到本地文件夹,python脚本读取sql文件,执行sql命令进入本地数据库,实现本地和测试的数据同步

python导出insert 数据sql脚本

#coding:utf-8
import os
import pymysql
import datetime
from datetime import datetime as dt
#获取连接对象

def get_conn():
    conn = pymysql.connect(host='数据库ip', user='用户名', passwd='密码', charset='utf8', db='数据库')
    return conn    

#查询表字段  这个有其他用途
def select_fields(db, table):
    sql = '''
    select column_name from information_schema.columns
    where table_schema = '%s' AND table_name = '%s'
'''
    sql_str = sql % (db, table)
    print sql_str
    conn = get_conn()
    with conn:
        conn.select_db(db)
        cursor = conn.cursor()
        cursor.execute(sql_str)
        fields_tuples = cursor.fetchall()
        fields_list = []
        for i in range(len(fields_tuples)):
            fields_list.append(fields_tuples[i][0])
        field_str = ''
        for field in fields_list:
            data = "'" + field + "',"
            field_str += data
        print(field_str)
        # fields_str=','.join(fields_list)
    return fields_list

#查询今日运行数据
def select_today(db,table_name,create_time):
    conn=get_conn()
    with conn:
        conn.select_db(db)
        cursor = conn.cursor()
        select_sql = 'select * from %s.%s where create_time like "%s%%"' % (db,table_name,create_time)
        print select_sql
        cursor.execute(select_sql)
        data_tuple=cursor.fetchall()
        return data_tuple


if __name__ == '__main__':
    db = 'dbana'
    table = 'rpt_table_increase'
    create_time = dt.now().strftime('%Y-%m-%d')
    #field_list=select_fields(db,table)
    data_tuple = select_today(db,table,create_time)
    with open('dbana_rpt_table_increase.sql','w') as file:
    for data in data_tuple:
        data_list = []
        for i in range(len(data)):
            data_list.append(data[i])
        list2 = ["'"+str(i).decode('utf-8')+"'" for i in data_list]
        print(list2)
        values_str=','.join(list2)
        str_sql = 'insert into dbana_rpt_table_increase values(' + values_str + ');'
        print (str_sql)
        file.write(str_sql)
        file.write('\n')  

sql文件 django后台