同步数据库

def __init__(self, dic_database_info):
    """
    初始化函数
    @param: model_name 模型名称，比如linear_regression, svr等
    """
    self.host = dic_database_info['host']
    self.port = dic_database_info['port']
    self.user = dic_database_info['user']
    self.passwd = dic_database_info['passwd']
    self.db1 = dic_database_info['db']
    #         print(self.db)
    self.charset = dic_database_info['charset']

def prepare_value(self, value):
    rtn = value
    if value is None or pd.isnull(rtn):
        rtn = 'NULL'
    elif isinstance(rtn, str):  # or isinstance(rtn, unicode):
        rtn = rtn.replace("'", '"').replace('\\', '')
        rtn = "'%s'" % rtn
    elif isinstance(rtn, datetime.date) or \
            isinstance(rtn, pd.Timestamp):
        rtn = "'%s'" % rtn
    return str(rtn)

def connect_database(self):
    "连接数据库"
    #         try:
    self.db = pymysql.connect(host=self.host, port=self.port, user=self.user,
                              passwd=self.passwd, db=self.db1, charset=self.charset)
    self.cursor = self.db.cursor(pymysql.cursors.DictCursor)

#         except:
#             print('connect database exception')

def close_database(self):
    """
    关闭数据库
    @param: cursor:数据库游标，db:数据名称
    """
    self.cursor.close()
    self.db.close()

def query_data(self, sql):
    """
    查询数据
    @param:  cursor:数据库游标，db:数据名称, sql:SQL语句
    """
    #         try:
    self.connect_database()
    self.cursor.execute(sql)
    query_data = self.cursor.fetchall()
    if query_data:
        df = pd.DataFrame(query_data)
    else:
        df = pd.DataFrame()
    self.close_database()
    return df

def mysql_import_data_from_df(self, df, table_name, verbose=False, replace=True):
    if len(df) == 0:
        print("mysql_import_data_from_df: empty data: %s" %
              (table_name))
        return

    list_rows = []

    def process_row(row):
        values = []
        for i in range(len(row)):
            v = row[i]
            values.append(self.prepare_value(v))
        list_rows.append('(%s)' % ','.join(values))

    df.apply(process_row, axis=1)

    list_columns = [('`%s`' % x) for x in df.columns]

    sql = '''
       replace into %s
        (%s)
        VALUES
        %s
    ''' % (
        table_name,
        ','.join(list_columns),
        ',\n'.join(list_rows)
    )
    #'replace' if replace else 'insert',

    if verbose:
        # print(sql)
        pass

    self.connect_database()
    try:
        # print(sql)
        self.cursor.execute(sql)
        self.db.commit()  # remember to commit it
    except Exception as err:
        print('Caught exception: {}'.format(err))
    else:
        self.close_database()

if name == ‘main’:
our_time=time.time()
open_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())

# 连接数据库
YYYYMM = str(time.strftime("%Y", time.localtime()))
TIME = time.strftime("%Y-%m-%d %H:00:00", time.localtime())

v1 = {'host': '', 'port': 3306, 'user': '',
           'passwd': '', 'db': '', "charset": ''}


# 这里写读库的链接
conn = pymysql.connect(**v1)



# #这里写读取数据的sql
# sql = '''select * from s_warn_prewarnig_grid'''
sql = '''select * from table'''
df = pd.read_sql(sql=sql, con=conn)

# print(sql)

# 这里写入库的链接


v2 = {'host': '', 'port': 3306, 'user': '',
           'passwd': '', 'db': '', "charset": ''}

sensor_db = DataBaseDriver(v2)


count=len(df)


# # 建议数量大于3万条的数据 进行分批写入，否则容易报错
sub_count =10
length = len(df.values)
size = math.ceil(length / sub_count)
for i in range(0, sub_count):
    start = i * size
    end = (i + 1) * size
    if i == sub_count - 1 and end != length:
        end = length
    sub_df = df[start: end]


    sensor_db.mysql_import_data_from_df(sub_df, "表", verbose=True, replace=True)
print('表名'+TIME+'数据同步完成'+'总用时：'+'启动时间为'+str(open_time)+'用时'+','+str(time.time()-our_time)+'数据条数'+str(count))