def insert_col_sql(config, table_name, col_name):
conn = sql.create_connection(config)
if (sql.table_exists(conn, table_name)) == 1:
print('the table is existe.')
sql_cmd = "alter table %s add column %s Integer" % (table_name,
col_name)
sql.exe_update(conn, sql_cmd)
def preprocess_package_data(config, bat_list, starttime, endtime):
"""
从数据库中读取原始数据，再按id分别进行处理
放入data_dict中
"""
conn = sql.create_connection(config)
data_dict = {}
for bat_id in bat_list:
start = time.time()
if (sql.table_exists(conn, bat_id) != 1):
print("There isn't a table named %s." % bat_id)
continue
sql_cmd = "select * from %s where save_time between " \
"'%s' and '%s'" % (bat_id, starttime, endtime)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
print('Reading the database...')
cursor.execute(sql_cmd)
#temp = cursor.fetchall()
rowcount = cursor.rowcount
if rowcount == 0:
print("There is no data in table named %s." % bat_id)
continue
df0 = pd.DataFrame(cursor.fetchall())
print('Finishing reading the database, and the rows of data is %d.' %
rowcount)
df0 = process_data(df0, 'sv', 'st')
data_dict[bat_id] = df0
end = time.time()
print('The process of preprocessing the %s is complete, whick takes %d '\
'secondes.' %(bat_id, (end-start)))
return data_dict
def insert_data_sql(data, config, table_name, *col):
if data.empty != True:
print('Inserting the data into the sql...')
col = list(col)
conn = sql.create_connection(config)
if (sql.table_exists(conn, table_name) != 1):
sql_cmd = "CREATE TABLE IF NOT EXISTS `%s`(\
`sys_id` VARCHAR(100) NOT NULL,\
`vin` VARCHAR(40) NOT NULL,\
PRIMARY KEY ( `sys_id` )\
)" % (table_name)
sql.create_table(conn, sql_cmd, table_name)
value = data[col]
cols = ''
for c in col:
cols += c
cols += ', '
cols = cols[:-2]
for i in range(len(value)):
ds = value.iloc[i].tolist()
vs = ''
for d in ds:
vs += "'" + str(d) + "'"
vs += ', '
vs = vs[:-2]
sql_cmd = "INSERT INTO %s (%s) VALUES (%s)" % (table_name, cols,
vs)
print('sql_cmd:' + sql_cmd)
sql.exe_update(conn, sql_cmd)
def read_bat_info(config, *kwg):
"""
读取电池基本信息
"""
conn = sql.create_connection(config)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
rows = {}
for table_name in kwg:
if 'bat_info' in table_name:
query = 'sys_id, code, c_id '
sql_cmd = 'select ' + query + 'from %s' % table_name
cursor.execute(sql_cmd)
rows[table_name] = pd.DataFrame(cursor.fetchall())
rowcount = cursor.rowcount
print(rowcount)
bat_list = rows['bat_info'][['sys_id', 'code', 'c_id']]
cursor.close()
sql.close_connection(conn)
return bat_list
def input_table_name(config):
"""
通过键盘获得要检索的数据库的表名
可以是表名前面的关键字
"""
conn = sql.create_connection(config)
exist_table_list = sql.get_table_list(conn)
table_name_list = input("please input the keyword of selected tables:")
table_name_list = table_name_list.split()
match_table_list = []
print('get the match table:\n')
for table_name in table_name_list:
for exist_table in exist_table_list:
if re.match(table_name, exist_table, re.I):
match_table_list.append(exist_table)
match_table_list = list(set(match_table_list))
if len(match_table_list) == 0:
print('no match table.')
return None
for table_name in match_table_list:
print(':%s\n' % table_name)
return match_table_list
def match_sql_data(config, table_name, condition, str_value, limit=None):
"""
获得指定条件的数据,类型为字符串
"""
print('reading a table named %s...' % table_name)
conn = sql.create_connection(config)
if (sql.table_exists(conn, table_name)) == 1:
print('the table is existe and reading...')
sql_cmd = "select * from %s where %s = '%s'"\
%(table_name, condition, str_value)
len_limit = sql.get_data_count(conn, sql_cmd)
if limit != None:
#sql_cmd += ' limit %d'%limit
len_limit = min(limit, len_limit) #获得查询条数限制值
rows = sql.read_sql_bar(conn, sql_cmd, len_limit)
df = pd.DataFrame(rows)
if len(df) > 0:
return df
else:
print('there is no data match the condition.')
return None
else:
print('the table is not exist.')
return None
def modify_data_sql(config, table_name, col_name, value):
conn = sql.create_connection(config)
sql_cmd = "update %s set %s='%d'" % (table_name, col_name, value)
print('sql_cmd:' + sql_cmd)
sql.exe_update(conn, sql_cmd)
def read_sql_data(config, table_name, **kwg):
"""
读取sql数据库中的一个表
参数可以传递start_time,end_time
no_sclae是一个数据扩展标志位，如果为1则表示数据需要扩展
扩展方式为讲读取的数据等分nclip份，形成nclip！份数据
"""
keywords = 'stime'
no_scale = True
nclip = 10
is_bar = False
if 'keywords' in kwg:
keywords = kwg['keywords']
if 'no_scale' in kwg:
no_scale = kwg['no_scale']
if 'nclip' in kwg:
nclip = kwg['nclip']
if 'is_bar' in kwg:
is_bar = kwg['is_bar']
print('reading a table named %s...' % table_name)
conn = sql.create_connection(config)
if (sql.table_exists(conn, table_name)) == 1:
print('the table is existe and reading...')
#cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
start = time.time()
if 'start_time' in kwg and 'end_time' in kwg:
start_time = kwg['start_time']
end_time = kwg['end_time']
sql_cmd = "select * from %s where %s between '%s' and '%s'"\
%(table_name, keywords, start_time, end_time)
else:
sql_cmd = "select * from %s" % table_name
len_limit = sql.get_data_count(conn, sql_cmd)
if 'limit' in kwg:
limit = kwg['limit']
if limit != None:
#sql_cmd += ' limit %d'%limit
len_limit = min(limit, len_limit) #获得查询条数限制值
print("sql_cmd: %s" % sql_cmd)
#创建进度条对象
total = 10 #默认10
if is_bar:
import processbar as pbar
bar = pbar.Processbar(total)
showbar = pbar.showbar(bar)
else:
showbar = None
rows = sql.read_sql_bar(conn, sql_cmd, len_limit, showbar, total)
if is_bar:
bar.finish()
#cursor.execute(sql_cmd)#获取数据行数
#rows = cursor.fetchall()
end = time.time()
print(
'Finished reading the data from the database which took %d seconds.'
% (end - start))
print('the length of data is : %d' % len(rows))
if len(rows) > 0:
df = pd.DataFrame(rows)
if no_scale:
return df
else:
scale = len(df)
interval = scale // nclip
data_dict = {}
for i in range(0, scale, interval):
for j in range(interval, scale, interval):
if (i + j) <= scale:
data_dict[str(i) + '_' +
str(j)] = df[i:(i + j)].copy(deep=True)
print('the first step of scaling data has been done.')
return data_dict
else:
print('there is no data in the table.')
return None
else:
print('the table is not exist.')
return None
def rw_bat_data(config, r_table_name, targe_base, id_list):
"""
读取电池详细的工作数据，并将读出的数据存入batterybase中
bat_id为数据库定义的电池编码，
cid为原系统定义的电池编号
"""
conn = sql.create_connection(config)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
rows = {}
count = {}
max_num = 5000000
query = 'save_time, soc, soh, voltageb, voltagep, current, positive_insulation_resistance, ' + \
'cathode_insulation_resistance, current_charge_quantity, current_loop_charge_quantity, ' +\
'current_discharge_quantity, current_loop_discharge_quantity, charge_times, ' + \
'loop_times, endurance_time, endurance_mileage, ' + \
'uncompress(cell_volt), uncompress(cell_temp)'#, uncompress(cell_soc)'#, ' + \
#'uncompress(cell_resistance), uncompress(cell_balance_status)'
columns = 'current', 'voltagep', 'voltageb', 'soh', 'soc', 'save_time'
"""
'positive_insulation_resistance',
'cathode_insulation_resistance', 'current_charge_quantity', 'current_loop_charge_quantity',
'current_discharge_quantity', 'current_loop_discharge_quantity', 'charge_times',
'loop_times', 'endurance_time', 'endurance_mileage']
"""
#for bat_id, cid in v.items():
start = time.time()
for i in range(len(id_list)):
bat_id = id_list.loc[i]['sys_id']
cid = id_list.loc[i]['c_id']
if cid == 21 or cid == 22 or cid == 23 or cid == 24: #测试数据
continue
sql_cmd = "select count(*) from %s where car_id=%d" % (r_table_name,
cid)
cursor.execute(sql_cmd) #获取数据行数
row = cursor.fetchall()
count[bat_id] = row[0]['count(*)']
print('The number of rows named %s-%s is %d.' %
(bat_id, cid, count[bat_id]))
sql_cmd = 'select ' + query + ' from %s where car_id=%d' % (
r_table_name, cid)
cursor.execute(sql_cmd)
while count[bat_id] > 0:
num = min(max_num, count[bat_id])
rows[r_table_name] = pd.DataFrame(cursor.fetchmany(num))
column_list = ['uncompress(cell_volt)',
'uncompress(cell_temp)'] #,
#'uncompress(cell_soc)', 'uncompress(cell_resistance)',
#'uncompress(cell_balance_status)']
rows[r_table_name] = r_sbat_data(rows[r_table_name], *column_list)
count[bat_id] = count[bat_id] - num
engine = sql.create_sql_engine(targe_base, 'root', 'wzqsql',
'localhost', '3306')
dtypedict = sql.mapping_df_types(rows[r_table_name])
column_list = rows[r_table_name].columns.tolist()
for column in columns:
column_list.remove(column)
column_list.insert(0, column)
'''
df = rows[r_table_name][columns]
rows[r_table_name] = rows[r_table_name].drop(columns, axis=1)
rows[r_table_name] = rows[r_table_name].insert(0, columns, df)
'''
rows[r_table_name] = rows[r_table_name][column_list]
rows[r_table_name].to_sql(bat_id,
engine,
index=False,
if_exists='append',
dtype=dtypedict)
print('writing the data which num is %d in a talbe named %s' %
(num, bat_id))
end = time.time()
print('The process of reading and writing data is complete, which takes' + \
'%s seconds.'%(end - start))
cursor.close()
sql.close_connection(conn)