def loaddata(date, period, rows): print(f'报告日期: {date} 期次:{period}') type_ = get_type(rows[0][0]) bgr = get_bgr(rows[2][0]) branch = get_br(rows[1][0]) print(f'分行: {branch} 报告类型: {type_} ') print(f'报告人: {bgr} 报告时间: {date}') if any([x is None for x in [type_, bgr, branch]]): raise Exception('报告格式不正确') header = [rows[i][0] for i in range(3)] contents = [(row[:2]) for row in rows[3:]] content = json.dumps({'header': header, 'content': contents}) execute('insert or replace into brreport values(?,?,?,?,?,?)', [period, type_, branch, bgr, date, content])
def loadfile(filename): def read(): for sheet in filename.worksheets: if sheet.name.endswith('年汇总'): data = sheet._cell_values if len(data) > 3 and len(data[2]) == 9: for row in data[2:]: yield [get_md5(row[4]), f'{row[0][:4]}-{row[0][5:7]}', *row[1:]] execute('delete from lzwt') sql = ( 'insert or replace into lzwt(bh,period,importance,category,branch,content,reporter,' 'reply_dept,reply,status)values(?,?,?,?,?,?,?,?,?,?)' ) executemany(sql, read())
def _(filename, *args, **kw): need_create and createtable() # 第一次执行本函数时建表 file = Path(filename) name = file.name a = fetchvalue('select mtime from LoadFile where filename=?', [name]) # 查询是否已导入 is_imported = a and a >= file.mtime # 判断是否已经导入 if not is_imported: func(filename, *args, **kw) execute( 'insert or replace into LoadFile values(?,?)', # 保存记录 [name, file.mtime]) else: print(f'{name} 已导入,忽略')
def _publish(filename, qc): hz = set(x[0] for x in fetch( 'select name from brreport where type=0 and period=?', [qc])) data = filename.sheets('分行履职报告问题表') if data: execute('delete from lzwenti where period=? and type in (0,1)', [qc]) data = tuple( procdata(data, header='分行,提出人,问题描述,答复部门,答复人,答复意见'.split(','))) for row in data: row.extend([0 if row[1] in hz else 1, qc]) r = executemany( 'insert into lzwenti(rpt_branch,rpt_name,content,reply_depart,reply_name,reply,type,period)' 'values(?,?,?,?,?,?,?,?)', data) print(f'共导入 {r.rowcount} 条数据')
def _(path: Path): if drop: execute(f'delete from {table}') if callable(proc): data = proc(path, **kw) else: data = path.iter_csv(**kw) if period: execute('insert or replace into param_period values(?,?,?)', [table, period, now() % '%F %T']) insert(table, fields=fields, data=data, method=method) if callable(exec): exec() print(f'{path.name} 导入成功')
def parse(txt): year = None anpai = [] for r in txt.splitlines(): if not year: k = YEAR.match(r) if k: year = k.groups()[0] else: if Pattern == r: anpai.append(r) with connect(): execute('insert or ignore into jqb(year,anpai) values(?,?)', [year, "\n".join(anpai)]) print(f'{year}年假期安排获取成功')
def iter_(begin, years=9): workdays = set() holidays = {} for year, anpai, ab in fetch('select * from jqb where year>=?', [begin[:4]]): for a in anpai.splitlines(): s = Pattern == a d = s.groupdict() holidays.update( zip(parsedate(d.get('fj'), year), cycle([d['name']]))) sb = d.get('sb') if sb: workdays.update(parsedate(sb, year)) year = begin[:4] obj = fetchone('select * from jqb where year=?', [begin[:4]]) if not obj: print('起始年份无对应的假期表,请先下载') exit(1) ab = obj[2] if not ab: ab = input(f'请输入 {year} 年 1 月 1 日的 AB 户标志:') ab = ab.upper() if ab not in 'AB': print('AB户标志输入不正确!') exit(2) with connect(): execute('update jqb set ab=? where year=?', [ab, year]) data = [] begin = datetime(f'{year}-1-1') for d in begin.iter(begin.add(years=years)): memo = holidays.get(d) or WEEKDAY.get(d.isoweekday()) if not memo: flag, sx, memo = '0', '0', '' elif memo.startswith('星期') and d in workdays: flag, sx, memo = '0', '0', '调休上班' else: flag, sx = '1', '2' ab_ = ab if flag == '1' else "A" if ab == 'B' else "B" if d.month == 1 and d.day == 1: if fetchone('select ab from jqb where year =?', [d.year]): with connect(): execute('update jqb set ab=? where year=?', [ab, d.year]) data.append((d % '%Y%m%d', flag, sx, memo, ab, ab_)) ab = ab_ return data
def update(): for path in Path('~/OneDrive/工作/工作档案/履职报告/处理完成').glob('营业主管*.*'): data = path.sheets(0) period = path.pname[9:16] for row in data[2:]: name = row[4] if name and not(name.endswith('部') or name.endswith('中心')): r = execute('update lzwt set reply_person=? where period=? and bh=?', [name, period, get_md5(row[2])]) if r.rowcount == 0: print(name, period, get_md5(row[2]))
def checkload(filename: str, loadfile: "function", *args, **kw) -> bool: # 检查文件是否已经导入 ''' 检查指定的文件是否已经导入数据库,如未导入执行 loadfile 函数 filename: 待导入的文件 loadfile: 导入文件的程序 *args,**kw : loadfile 除 filename 以外的参数 ''' need_create and createtable() # 第一次执行本函数时建表 file = Path(filename) name = file.name a = fetchvalue('select mtime from LoadFile where filename=?', [name]) # 查询是否已导入 is_imported = a and a >= file.mtime # 判断是否已经导入 if not is_imported: loadfile(filename, *args, **kw) with trans(): execute( 'insert or replace into LoadFile values(?,?)', # 保存记录 [name, file.mtime]) return is_imported
def load_wenti(filename): print('处理文件:', filename.pname) s = 0 for sheet in filename.worksheets: for row in sheet._cell_values[1:]: if get_md5("".join(row[7:10])) != row[10]: r = execute('update lzwt set reply_person=?,status=?,ywxq=? where bh=?', [ *row[7:10], row[0]]) s += r.rowcount if r.rowcount == 0: print('Error:', row) print(f'已更新数据:{s}条')
def load_file(): files = Path("~/Downloads").glob('会计履职报告????-??.xls*') if not files: print('当前目录无文件') else: filename = max(files) print('当前导入文件:%s' % (filename)) data = [] for rows in filename.iter_sheets(): rows = rows[-1] if len(rows) < 1: continue title = None for row in rows[1:]: if row[0]: # 防止空行出现 if title != row[0]: title = row[0] nr = [row[20:]] gh = extract(row[3], r"[a-zA-Z]{1,2}\d{4}") data.append([ title, _get_period(row[6]), row[2], gh, row[5] + row[4], row[6], row[7], row[8], row[9], row[11], row[12], row[13], row[14], row[15], row[17], row[18], row[19], nr ]) else: nr.append(row[20:]) for r in data: r[-1] = json.dumps(r[-1]) with trans(): sql = f'insert or replace into report {Values(18)}' cur = executemany(sql, data) print(f'已导入数据:{cur.rowcount}') execute('delete from yyzg') executemany(f'insert into yyzg {Values(11)}', read_yyzg()) print('已导入营业主管数据')
def loadwt(filename): print(f'导入最新处理完成的文件:{filename.name}') data = filename.sheets(0) if len(data) > 3 and len(data[2]) >= 7: rq = Period(data[0][0]).value print(f'当前导入文件日期:{rq}') count = fetchvalue('select count(reporter) from lzwt where period=?', [rq]) if count > 0: confirm = input('基准文件中已有数据,是否重新导入? Y or N') if confirm.lower() == "n": return r = execute('delete from lzwt where period=?', [rq]) print('存量数据已删除') def procline(line): print(line) line = [x.strip() for x in line] bh = get_md5(line[2]) importance = '重点问题' if '重点' in line[6] else '一般问题' dfr = "" if not (line[4].endswith('部') or line[4].endswith('中心')): dfr = line[4] line[4] = '运营管理部' nline = [bh, rq, importance, *line[:7], dfr] return nline data = filter(None, map(procline, data[2:])) sql = ( 'insert or replace into lzwt(bh,period,importance,category,branch,content,reporter,' 'reply_dept,reply,status,reply_person)values(?,?,?,?,?,?,?,?,?,?,?)' ) executemany(sql, data) s = 0 for r in fetch( 'select importance,count(period) from lzwt ' 'where period=? group by importance ', [rq]): print(*r) s += int(r[1]) print(f'共导入数据:{s:,d}')
def loaddfyj(filename): print(f'导入基准文件:{filename.name}') # execute('delete from lzwt') # print('清理已导入的数据') # excludes = set() sql = ( 'update lzwt set reply_dept=?,reply=?,status=?,importance=? where period=? and bh=?' ) s = 0 for sheet in filename.worksheets: if sheet.name in ('重点问题', '一般问题'): data = sheet._cell_values if len(data) > 3 and len(data[2]) == 8: for row in data[2:]: if row: bh = get_md5(row[3]) period = f'{row[0][:4]}-{row[0][5:7]}' r = execute(sql, [*row[5:8], sheet.name, period, bh]) s += r.rowcount if not r.rowcount: print("Error", [*row[5:8], sheet.name, period, bh]) print(f'{s} 条记录被更新') '''
dest='reset_name', nargs='?', help='重置某表,重置后可以重新导入数据') def main(**options): db_config('params') info(f'set db param') if options['list']: fprintf( '{0:10s}{1:20s}', 'select type,name from sqlite_master where type in ("table","view")order by name' ) if name := options['show']: fprint('select sql from sqlite_master where name=?', [name]) if sql := options['qsql']: fprint(' '.join(sql)) if sql := options['esql']: with connect(): sql = ' '.join(sql) info(f'execute:{sql}') r = execute(sql) print(f'{r.rowcount} 行数据受到影响') if options['load']: from .load import loadall info('开始导入参数') loadall() info('导入参数完成') if name := options['reset_name']: with connect(): r = execute('delete from LoadFile where name=?', [name]) print(f"重置 {name}", '成功' if r.rowcount else '失败')
# 作者: 黄涛 # License: GPL # Email: [email protected] # 创建:2021-07-10 11:40 from orange import Path, info, fatal, decode, Data, limit, datetime from orange.utils.sqlite import fetchvalue, execute, load def loadcheck(name: str, path: str, mtime: datetime, ver: str): checkSQL = "select count(name) from loadfile where name=? and path=? and mtime>=datetime(?)" doneSQL = "insert or replace into loadfile values(?,?,datetime(?),?)" if value := fetchvalue(checkSQL, [name, path, mtime]): raise Exception(f'文件:{path} 已导入') else: execute(doneSQL, [name, path, mtime, ver]) class Loader(): __slots__ = ('clear', 'converters', 'kwargs', 'method', 'table', 'fields', 'data') def __init__(self, table: str, fields: str, *converters, method: str = 'insert', **kw): self.table = table self.fields = fields self.clear = True