def __init__(self, acc_user, acc_asset='', db=None, table='transactions', columns=[ 'id', 'acc_user', 'acc_asset', 'nam_asset', 'rmk_asset', 'amt_trans', 'tye_asset', 'ico_asset', 'tye_flow', 'dte_trans', 'tme_trans', 'acc_asset_related', 'nam_asset_related', 'rmk_asset_related', 'ico_asset_related', 'tye_asset_related', 'cod_trans_type', 'txt_trans_type', 'txt_trans_type_sub', 'txt_remark', 'ico_trans' ]): self.db = db self.table = table self.columns = columns self.dtype_columns = {} self.vdefault_columns = {} self.acc_user = acc_user self.acc_asset = acc_asset self.nam_asset = '' self.amt_trans = 0.0 self.tye_flow = '' self.dte_trans = '' self.tme_trans = '' self.acc_asset_related = '' self.nam_asset_related = '' self.cod_trans_type = '' self.txt_trans_type = '' self.txt_trans_type_sub = '' self.txt_remark = '' if not db: self.db = DataBase()
def __init__(self, acc_user, dte_month=None, db=None, table='statistics', columns=['id', 'acc_user', 'dte_month', 'amt_income_month', 'amt_expend_month', 'amt_budget', 'amt_budget_surplus', 'amt_debt_total', 'amt_asset_total', 'amt_asset_net']): self.db = db self.table = table self.columns = columns self.acc_user = acc_user self.dte_month = dte_month if not self.db: self.db = DataBase()
def __init__(self, acc_user, db=None, table='assets', columns=[ 'id', 'acc_user', 'acc_asset', 'nam_asset', 'rmk_asset', 'tye_asset', 'amt_asset', 'ico_asset', 'boo_active' ]): self.db = db self.table = table self.columns = columns self.acc_user = acc_user self.df_asset = pd.DataFrame() if not self.db: self.db = DataBase()
def main(db=None): if not db: db = DataBase() if backup_db(db): if init_db(db): if init_data(db): return True return False
def __init__(self, acc_user, db=None, table='transtypes', columns=[ 'id', 'acc_user', 'cod_trans_type', 'txt_trans_type', 'txt_trans_type_sub', 'tye_flow', 'boo_active', 'ico_trans' ]): self.db = db self.table = table self.columns = columns self.acc_user = acc_user self.cod_trans_type = '' self.txt_trans_type = '' self.txt_trans_type_sub = '' self.tye_flow = '' self.df_transtype = pd.DataFrame() self.df_transtype_default = None self.base_path = os.path.dirname(__file__) if not self.db: self.db = DataBase()
def __init__(self, acc_user=None, pwd_user_md5=None, db=None, table='users', columns=[ 'id', 'acc_user', 'pwd_user_md5', 'nam_user', 'vlu_email', 'vlu_phone', 'vlu_openid', 'nam_nick', 'cod_gender', 'vlu_lang', 'vlu_city', 'vlu_prov', 'vlu_country', 'url_avatar' ]): self.db = db self.table = table self.columns = columns self.acc_user = acc_user self.pwd_user_md5 = pwd_user_md5 self.nam_user = '' self.vlu_email = '' self.vlu_phone = '' self.vlu_openid = '' if not self.db: self.db = DataBase()
def backup_db(db=None, batch=50000): if not db: db = DataBase() path = mkdir() flag, result = db.read('show tables') if flag: df_tables = result for table in df_tables.values[:, 0]: num_line = load_next_id(table, db) - 1 filename = os.path.join(path, '%s.csv' % table) for index in range(1 + int(num_line / batch)): offset = batch * index flag, result = db.read('select * from %s limit %s,%s' % (table, offset, batch)) if flag: result.to_csv(filename, index=False, mode='a', header=True if index == 0 else None) # 追加写入 else: print(result) else: print(result) return flag
def create_from_transactions(self, df_transactions): next_id = load_next_id() data_asset = [] nam_asset_list = df_transactions['nam_asset'].unique() for i in range(len(nam_asset_list)): id = next_id + i nam_asset = nam_asset_list[i] acc_asset = gen_short_uuid() tye_asset = '' amt_asset = 0.0 data_asset.append([ id, self.acc_user, acc_asset, nam_asset, tye_asset, amt_asset ]) self.df_asset = pd.DataFrame(data=data_asset, columns=self.columns) flag, result = DataBase().write(self.df_asset, self.table) print(flag, result) return self.acc_user
def create_from_default(self): if not self.df_transtype_default: next_id = load_next_id(self.table) df_transtype_default = pd.read_csv( os.path.join(self.base_path, file_default_transtype)) len_transtype_default = len(df_transtype_default) df_transtype_default['txt_trans_type_sub'] = [ '' ] * len_transtype_default df_transtype_default['acc_user'] = [self.acc_user ] * len_transtype_default df_transtype_default['id'] = [ next_id + i for i in range(len_transtype_default) ] flag, result = DataBase().write(df_transtype_default, self.table) if flag: self.df_transtype_default = df_transtype_default self.df_transtype = df_transtype_default return flag
class Transtype: def __init__(self, acc_user, db=None, table='transtypes', columns=[ 'id', 'acc_user', 'cod_trans_type', 'txt_trans_type', 'txt_trans_type_sub', 'tye_flow', 'boo_active', 'ico_trans' ]): self.db = db self.table = table self.columns = columns self.acc_user = acc_user self.cod_trans_type = '' self.txt_trans_type = '' self.txt_trans_type_sub = '' self.tye_flow = '' self.df_transtype = pd.DataFrame() self.df_transtype_default = None self.base_path = os.path.dirname(__file__) if not self.db: self.db = DataBase() # 包括用户自定义交易类别和默认类别 def show_transtypes(self, acc_user_default='dbuser', need_index=True): sql_show = "select * from transtypes where acc_user in ('%s', '%s') order by id asc" % ( self.acc_user, acc_user_default) flag, result = self.db.read(sql_show) if flag: if need_index: result['index'] = range(len(result)) return result return None def init_transtypes(self, df_transtypes): flag, result = self.db.write(df_transtypes, self.table) return flag, result def add_transtypes(self): pass def create_from_default(self): if not self.df_transtype_default: next_id = load_next_id(self.table) df_transtype_default = pd.read_csv( os.path.join(self.base_path, file_default_transtype)) len_transtype_default = len(df_transtype_default) df_transtype_default['txt_trans_type_sub'] = [ '' ] * len_transtype_default df_transtype_default['acc_user'] = [self.acc_user ] * len_transtype_default df_transtype_default['id'] = [ next_id + i for i in range(len_transtype_default) ] flag, result = DataBase().write(df_transtype_default, self.table) if flag: self.df_transtype_default = df_transtype_default self.df_transtype = df_transtype_default return flag def create_from_transactions(self, df_transactions): self.df_transtype = self.df_transtype_default + ''
class Transaction: def __init__(self, acc_user, acc_asset='', db=None, table='transactions', columns=[ 'id', 'acc_user', 'acc_asset', 'nam_asset', 'rmk_asset', 'amt_trans', 'tye_asset', 'ico_asset', 'tye_flow', 'dte_trans', 'tme_trans', 'acc_asset_related', 'nam_asset_related', 'rmk_asset_related', 'ico_asset_related', 'tye_asset_related', 'cod_trans_type', 'txt_trans_type', 'txt_trans_type_sub', 'txt_remark', 'ico_trans' ]): self.db = db self.table = table self.columns = columns self.dtype_columns = {} self.vdefault_columns = {} self.acc_user = acc_user self.acc_asset = acc_asset self.nam_asset = '' self.amt_trans = 0.0 self.tye_flow = '' self.dte_trans = '' self.tme_trans = '' self.acc_asset_related = '' self.nam_asset_related = '' self.cod_trans_type = '' self.txt_trans_type = '' self.txt_trans_type_sub = '' self.txt_remark = '' if not db: self.db = DataBase() def show_trans(self, acc_asset=None, id=None): sql_asset, sql_id = " ", " " if acc_asset: sql_asset = " and (acc_asset='%s' or acc_asset_related='%s') " % ( acc_asset, acc_asset) if id: sql_id = " and id=%s " % id sql_show = "select * from transactions where acc_user='******' %s %s order by dte_trans desc, id desc" % \ (self.acc_user, sql_asset, sql_id) flag, result = self.db.read(sql_show) if flag: result['index'] = range(len(result)) return result return pd.DataFrame() def add_trans(self, dict_tran, is_transaction=False): data, columns = [], [] for key, value in dict_tran.items(): columns.append(key) data.append(value) data_trans = pd.DataFrame(data=[data], columns=columns) if not is_transaction: flag, result = self.db.write(data_trans, self.table) if not flag: print(result) return flag else: return data_trans, self.table, False, 'append' def show_flow(self, month_now): sql_show = """ select tye_flow, right(dte_trans,2) as day, sum(case when tye_flow='expend' then -1*amt_trans else amt_trans end) as amount from %s where acc_user='******' and left(dte_trans,6)='%s' and tye_flow in ('income', 'expend') group by tye_flow, day order by tye_flow, day """ % (self.table, self.acc_user, month_now) flag, result = self.db.read(sql_show) if flag: return result return pd.DataFrame() def show_report(self, date, date_length): sql_show = """ select tye_flow, cod_trans_type, txt_trans_type, ico_trans, sum(case when tye_flow='expend' then -1*amt_trans else amt_trans end) as amount, count(*) as cnt from %s where acc_user='******' and left(dte_trans,%s)='%s' and tye_flow in ('income', 'expend', 'transfer') group by tye_flow, cod_trans_type, txt_trans_type, ico_trans order by tye_flow, amount desc """ % (self.table, self.acc_user, date_length, date) flag, result = self.db.read(sql_show) if flag: return result return pd.DataFrame() def export_trans(self): sql_export = """ select dte_trans as `日期`, case when amt_trans<0 then -1*amt_trans else amt_trans end as `金额`, case when tye_flow='income' then '流入' when tye_flow='expend' then '流出' else '转账' end as `资金流向`, txt_trans_type as `交易类型`, rmk_asset as `账户名称`, rmk_asset_related as `相关账户名称`, txt_remark as `备注` from %s where acc_user='******' """ % (self.table, self.acc_user) flag, result = self.db.read(sql_export) if flag: return result return pd.DataFrame() # 由于删除交易记录会有资金回流,因此一般情况下禁止直接删除记录,而不做后续处理 def delete_trans(self, id, is_transaction=True): sql_delete = "delete from %s where acc_user='******' and id=%s " % ( self.table, self.acc_user, id) if not is_transaction: flag, result = self.db.execute(sql_delete) if not flag: print(result) return flag else: return sql_delete @staticmethod def revert_dict(dic): res = {} for key, value in dic.items(): res[value] = key return res @staticmethod def date_format(date, target_format='yyyymmdd'): def date_fill(date_part): if len(date_part) == 1: date_part = '0%s' % date_part return date_part[:2] for seg in ['-', '/']: if seg in date: y, m, d = date.split(' ')[0].split(seg)[:3] m = date_fill(m) d = date_fill(d) date = '%s%s%s' % (y, m, d) break return date[:8] @staticmethod def extract_date_column(dic_db2file_columns): date_columns = [] for key, value in dic_db2file_columns.items(): if key.startswith('dte'): date_columns.append(value) return date_columns def process_column_dtype(self, table_structure, dic_db2file_columns): if not self.dtype_columns: for Field, Type in table_structure.values[:, :2]: column = Field.lower() if Type.startswith('decimal'): self.dtype_columns[column] = float self.vdefault_columns[column] = 0.0 elif Type.startswith('integer'): self.dtype_columns[column] = int self.vdefault_columns[column] = 0 else: self.dtype_columns[column] = str self.vdefault_columns[column] = '' dtype_file_columns = {} vdefault_file_columns = {} for column, file_column in dic_db2file_columns.items(): dtype_file_columns[file_column] = self.dtype_columns[column] vdefault_file_columns[file_column] = self.vdefault_columns[column] return dtype_file_columns, vdefault_file_columns def create_from_transactions(self, file_transactions, dic_db2file_columns): flag, result = self.db.read('desc %s' % self.table) if flag: df_table_structure = result date_columns = self.extract_date_column(dic_db2file_columns) dtype_file_columns, vdefault_file_columns = self.process_column_dtype( df_table_structure, dic_db2file_columns) df_data = pd.read_excel(file_transactions, dtype=dtype_file_columns)[dtype_file_columns.keys()].\ fillna(value=vdefault_file_columns) for date_column in date_columns: df_data[date_column] = df_data[date_column].apply( self.date_format) df_data = df_data.rename( columns=self.revert_dict(dic_db2file_columns)) # auto fill len_df_data = len(df_data) next_id = load_next_id(self.table, self.db) df_data['id'] = [next_id + i for i in range(len_df_data)] df_data['acc_user'] = [self.acc_user] * len_df_data for column in self.columns: if column not in df_data.columns: df_data[column] = [self.vdefault_columns[column] ] * len_df_data flag, result = self.db.write(df_data, self.table) if not flag: print(result) return flag return flag
class User: def __init__(self, acc_user=None, pwd_user_md5=None, db=None, table='users', columns=[ 'id', 'acc_user', 'pwd_user_md5', 'nam_user', 'vlu_email', 'vlu_phone', 'vlu_openid', 'nam_nick', 'cod_gender', 'vlu_lang', 'vlu_city', 'vlu_prov', 'vlu_country', 'url_avatar' ]): self.db = db self.table = table self.columns = columns self.acc_user = acc_user self.pwd_user_md5 = pwd_user_md5 self.nam_user = '' self.vlu_email = '' self.vlu_phone = '' self.vlu_openid = '' if not self.db: self.db = DataBase() def show_userinfo(self): sql_show = "select * from %s where acc_user='******' " % (self.table, self.acc_user) flag, result = self.db.read(sql_show) if not flag: print(result) return pd.DataFrame() else: return result def save_userinfo(self, userinfo, is_transaction=False): if not self.acc_user: self.acc_user = gen_short_uuid() self.pwd_user_md5 = get_md5(self.acc_user) sql_delete = "delete from %s where acc_user='******' " % (self.table, self.acc_user) columns = ['acc_user', 'pwd_user_md5', 'vlu_openid'] data = [self.acc_user, self.pwd_user_md5, userinfo['vlu_openid']] for key, column in dict_mapping.items(): columns.append(column) data.append(userinfo.get(key, '')) df_userinfo = pd.DataFrame(data=[data], columns=columns) if not is_transaction: flag, result = self.db.execute(sql_delete) if not flag: print(result) return False else: flag, result = self.db.write(df_userinfo, self.table) if not flag: print(result) return False return True else: return sql_delete, df_userinfo, self.table, False, 'append' def create(self): if not self.acc_user: self.acc_user = gen_short_uuid() self.pwd_user_md5 = get_md5(self.acc_user) columns = ['acc_user', 'pwd_user_md5', 'vlu_openid'] data = [self.acc_user, self.pwd_user_md5, self.vlu_openid] df_user = pd.DataFrame(data=[data], columns=columns) flag, result = self.db.write(df_user, self.table) if flag: return self.acc_user else: print(result) return False def user_check(self, openid): self.vlu_openid = openid sql_check = "select acc_user from %s where vlu_openid='%s' " % ( self.table, self.vlu_openid) flag, result = self.db.read(sql_check) if flag and not result.empty: self.acc_user = result['acc_user'][0] flag_new = False else: self.acc_user = self.create() flag_new = True return self.acc_user, flag_new
class Statistic: def __init__(self, acc_user, dte_month=None, db=None, table='statistics', columns=['id', 'acc_user', 'dte_month', 'amt_income_month', 'amt_expend_month', 'amt_budget', 'amt_budget_surplus', 'amt_debt_total', 'amt_asset_total', 'amt_asset_net']): self.db = db self.table = table self.columns = columns self.acc_user = acc_user self.dte_month = dte_month if not self.db: self.db = DataBase() def init_statistics(self): if not self.dte_month: self.dte_month = time.strftime("%Y%m", time.localtime()) df_statistics = pd.DataFrame( columns=['acc_user', 'dte_month'], data=[[self.acc_user, self.dte_month]]) flag, result = self.db.write(df_statistics, self.table) return flag, result # 为避免使用contab等定时工具,需要在执行statistics相关的操作之前,检查是否是新月,来决定是否清空月统计数据 def reset_statistics(self): dte_month_now = time.strftime("%Y%m", time.localtime()) sql_update = """ update %s set amt_income_month = case when dte_month='%s' then amt_income_month else 0 end, amt_expend_month = case when dte_month='%s' then amt_expend_month else 0 end, dte_month = case when dte_month='%s' then dte_month else '%s' end where acc_user='******' """ % (self.table, dte_month_now, dte_month_now, dte_month_now, dte_month_now, self.acc_user) flag, result = self.db.execute(sql_update) # print(sql_update, flag, result) if not flag: print(result) return flag def show_statistics(self): self.reset_statistics() sql_show = " select * from %s where acc_user='******' " % (self.table, self.acc_user) flag, result = self.db.read(sql_show) if not flag: print(result) return pd.DataFrame() else: return result def update_statistics(self, tye_amount, amount, cod_trans_type='', tye_asset='', tye_asset_related='', tye_update='add', is_transaction=False): """ :param tye_update: ['add', 'delete_trans', 'delete_asset'] :param tye_amount: ['income', 'expend', 'transfer', 'budget', 'asset', 'debt'] :param amount: 根据type_amount,取对应的值或者相反数 :param cod_trans_type: 交易类型代码 :param tye_asset: 资产账户类型 :param tye_asset_related: 相关联的资产账户类型 :param is_transaction: 为False则直接执行;为True则返回sql语句,待后续按照数据库事务规范执行 :return: 返回执行结果或返回适用于数据库事务的sql """ self.reset_statistics() sql_set = " amt_asset_total = amt_asset_total " if tye_update == 'add': if tye_amount == 'income': # 流入 if tye_asset == 'asset': if cod_trans_type in ('yqkDZNq7', 'xSTyQpeH'): # 退款、报销,不计入收入,但支出、预算剩余应减少 sql_set = " amt_asset_total = amt_asset_total + %s, amt_asset_net = amt_asset_net + %s, amt_expend_month = amt_expend_month + %s, amt_budget_surplus = amt_budget + amt_expend_month " % tuple([amount]*3) else: sql_set = " amt_income_month = amt_income_month + %s, amt_asset_total = amt_asset_total + %s, amt_asset_net = amt_asset_net + %s " % tuple([amount]*3) elif tye_asset == 'debt': if cod_trans_type in ('yqkDZNq7', 'xSTyQpeH'): # 退款、报销,不计入收入,但支出、预算剩余应减少 sql_set = " amt_debt_total = amt_debt_total + %s, amt_asset_net = amt_asset_net + %s, amt_expend_month = amt_expend_month + %s, amt_budget_surplus = amt_budget + amt_expend_month " % tuple([amount]*3) else: sql_set = " amt_income_month = amt_income_month + %s, amt_debt_total = amt_debt_total + %s, amt_asset_net = amt_asset_net + %s " % tuple([amount]*3) elif tye_amount == 'expend': # 流出 if tye_asset == 'asset': sql_set = " amt_expend_month = amt_expend_month + %s, amt_budget_surplus = amt_budget + amt_expend_month, amt_asset_total = amt_asset_total + %s, amt_asset_net = amt_asset_net + %s " % tuple([amount]*3) elif tye_asset == 'debt': sql_set = " amt_expend_month = amt_expend_month + %s, amt_budget_surplus = amt_budget + amt_expend_month, amt_debt_total = amt_debt_total + %s, amt_asset_net = amt_asset_net + %s " % tuple([amount]*3) elif tye_amount == 'transfer': # 转账 if tye_asset == 'asset' and tye_asset_related == 'debt': # 资产负债都减少,但数值一减一增 sql_set = " amt_asset_total = amt_asset_total - %s, amt_debt_total = amt_debt_total + %s " % tuple([amount]*2) elif tye_asset == 'debt' and tye_asset_related == 'asset': # 资产负债都增加,但数值一增一减 sql_set = " amt_asset_total = amt_asset_total + %s, amt_debt_total = amt_debt_total - %s " % tuple([amount]*2) elif tye_amount == 'budget': sql_set = " amt_budget = %s, amt_budget_surplus = %s + amt_expend_month " % tuple([amount]*2) elif tye_amount == 'asset': sql_set = " amt_asset_total = amt_asset_total + %s, amt_asset_net = amt_asset_net + %s " % tuple([amount] * 2) elif tye_amount == 'debt': sql_set = " amt_debt_total = amt_debt_total + %s, amt_asset_net = amt_asset_net + %s " % tuple([amount] * 2) elif tye_update == 'delete_trans': # 删除交易记录会引起资产账户变动 if tye_amount == 'income': # 原交易记录为流入 if cod_trans_type in ('yqkDZNq7', 'xSTyQpeH'): # 退款、报销 sql_set = " amt_asset_total = amt_asset_total - %s, amt_asset_net = amt_asset_net - %s, amt_expend_month = amt_expend_month - %s, amt_budget_surplus = amt_budget + amt_expend_month " % tuple([amount]*3) else: sql_set = " amt_income_month = amt_income_month - %s, amt_asset_total = amt_asset_total - %s, amt_asset_net = amt_asset_net - %s " % tuple([amount]*3) elif tye_amount == 'expend': # 原交易记录为流出 if tye_asset == 'asset': sql_set = " amt_expend_month = amt_expend_month - %s, amt_budget_surplus = amt_budget + amt_expend_month, amt_asset_total = amt_asset_total - %s, amt_asset_net = amt_asset_net - %s " % tuple([amount]*3) elif tye_asset == 'debt': sql_set = " amt_expend_month = amt_expend_month - %s, amt_budget_surplus = amt_budget + amt_expend_month, amt_debt_total = amt_debt_total - %s, amt_asset_net = amt_asset_net - %s " % tuple([amount]*3) elif tye_amount == 'transfer': # 原交易记录为转账 if tye_asset == 'asset' and tye_asset_related == 'debt': sql_set = " amt_asset_total = amt_asset_total + %s, amt_debt_total = amt_debt_total - %s " % tuple([amount]*2) elif tye_asset == 'debt' and tye_asset_related == 'asset': sql_set = " amt_asset_total = amt_asset_total - %s, amt_debt_total = amt_debt_total + %s " % tuple([amount]*2) elif tye_amount == 'adjust': # 原交易记录为资金调整 if tye_asset == 'asset': sql_set = " amt_asset_total = amt_asset_total - %s, amt_asset_net = amt_asset_net - %s " % tuple([amount]*2) elif tye_asset == 'debt': sql_set = " amt_debt_total = amt_debt_total - %s, amt_asset_net = amt_asset_net - %s " % tuple([amount]*2) elif tye_update == 'delete_asset': if tye_asset == 'asset': sql_set = " amt_asset_total = amt_asset_total - %s, amt_asset_net = amt_asset_net - %s " % tuple([amount]*2) elif tye_asset == 'debt': sql_set = " amt_debt_total = amt_debt_total - %s, amt_asset_net = amt_asset_net - %s " % tuple([amount] * 2) sql_update = "update %s set %s where acc_user ='******'" % (self.table, sql_set, self.acc_user) print('sql_update_statistics', sql_update) if not is_transaction: flag, result = self.db.execute(sql_update) if not flag: print(result) return flag else: return sql_update
class Asset: def __init__(self, acc_user, db=None, table='assets', columns=[ 'id', 'acc_user', 'acc_asset', 'nam_asset', 'rmk_asset', 'tye_asset', 'amt_asset', 'ico_asset', 'boo_active' ]): self.db = db self.table = table self.columns = columns self.acc_user = acc_user self.df_asset = pd.DataFrame() if not self.db: self.db = DataBase() def show_assets(self, acc_asset=None, flag_active=1, need_index=True): """ :param db: :param flag_active: 1, 0, 'all' :return: """ sql_active, sql_asset = " ", " " if flag_active in (0, 1): sql_active = " and boo_active='%s' " % flag_active if acc_asset: sql_asset = " and acc_asset='%s' " % acc_asset sql_show = "select * from assets where acc_user='******' %s %s order by id" % ( self.acc_user, sql_active, sql_asset) flag, result = self.db.read(sql_show) if flag: if need_index: result['index'] = range(len(result)) return result return pd.DataFrame() def add_assets(self, dict_asset, is_transaction=False): if not dict_asset.has_key('acc_asset'): dict_asset['acc_asset'] = gen_short_uuid() data, columns = [], [] for key, value in dict_asset.items(): columns.append(key) data.append(value) data_asset = pd.DataFrame(data=[data], columns=columns) if not is_transaction: flag, result = self.db.write(data_asset, self.table) return flag else: return data_asset, self.table, False, 'append' # 用于资产账户信息的更改 def modify_assets(self, acc_asset, df_asset, is_transaction=False): # 由于中文编码有问题,所以update改为delete+insert sql_delete = "delete from %s where acc_user='******' and acc_asset='%s'" % ( self.table, self.acc_user, acc_asset) if not is_transaction: flag, result = self.db.execute(sql_delete) if not flag: print(result) return False else: flag, result = self.db.write(df_asset, self.table) if not flag: print(result) return False return True else: return sql_delete, self.table, False, 'append' # 用于交易等相关数据库事务的更新 def update_assets(self, tye_flow, amount, acc_asset, tye_asset, acc_asset_related, tye_asset_related, tye_update='add', is_transaction=False): """ :param tye_flow: ['income', 'expend', 'transfer', 'adjust'] :param tye_update: ['add', 'delete_trans', 'delete_asset'] """ sql_update_assets, sql_update_assets_related = "", "" sql_template = "update %s set amt_asset=amt_asset %s %s where acc_user='******' and acc_asset='%s' " if tye_update == 'add': if tye_flow == 'transfer': # 账面金额一减一增 sql_update_assets = sql_template % (self.table, '-', amount, self.acc_user, acc_asset) sql_update_assets_related = sql_template % ( self.table, '+', amount, self.acc_user, acc_asset_related) elif tye_flow in ('income', 'expend'): sql_update_assets = sql_template % (self.table, '+', amount, self.acc_user, acc_asset) else: pass elif tye_update == 'delete_trans': # 删除交易记录会引起资产账户变动 if tye_flow == 'transfer': sql_update_assets = sql_template % (self.table, '+', amount, self.acc_user, acc_asset) sql_update_assets_related = sql_template % ( self.table, '-', amount, self.acc_user, acc_asset_related) elif tye_flow in ('income', 'expend', 'adjust'): sql_update_assets = sql_template % (self.table, '-', amount, self.acc_user, acc_asset) else: pass elif tye_update == 'delete_asset': pass else: pass print('sql_update_assets', sql_update_assets) print('sql_update_assets_related', sql_update_assets_related) if not is_transaction: if sql_update_assets: flag, result = self.db.execute(sql_update_assets) if not flag: print(result) return False else: if sql_update_assets_related: flag, result = self.db.execute( sql_update_assets_related) if not flag: print(result) return False return True else: return sql_update_assets, sql_update_assets_related # 由于删除账户会有资产变动,因此一般情况下禁止直接删除账户,而不做后续处理 def delete_assets(self, acc_asset, is_transaction=True): sql_delete = "delete from %s where acc_user='******' and acc_asset='%s' " % ( self.table, self.acc_user, acc_asset) if not is_transaction: flag, result = self.db.execute(sql_delete) if not flag: print(result) return flag else: return sql_delete # 适用于导入交易记录时自动创建交易类型 def create_from_transactions(self, df_transactions): next_id = load_next_id() data_asset = [] nam_asset_list = df_transactions['nam_asset'].unique() for i in range(len(nam_asset_list)): id = next_id + i nam_asset = nam_asset_list[i] acc_asset = gen_short_uuid() tye_asset = '' amt_asset = 0.0 data_asset.append([ id, self.acc_user, acc_asset, nam_asset, tye_asset, amt_asset ]) self.df_asset = pd.DataFrame(data=data_asset, columns=self.columns) flag, result = DataBase().write(self.df_asset, self.table) print(flag, result) return self.acc_user