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))