|
13 | 13 | # 读取配置文件，请一定要配置好哦，具体的配置说明请看 run_config_template.py 文件的说明 |
14 | 14 | from run_config import oracle_connect_string, save_folder |
15 | 15 |
|
| 16 | +# 设置 Oracle 客户端库路径 |
16 | 17 | cx_Oracle.init_oracle_client(lib_dir=r"D:\Program Files\instantclient_19_3") |
17 | 18 |
|
18 | | -conn = None |
19 | | -cursor = None |
20 | | -start_time = time.time() |
21 | | - |
22 | | - |
23 | 19 | def oracle_connect(connect_string: str): |
24 | | - """连接 oracle""" |
25 | | - global conn, cursor |
26 | | - conn = cx_Oracle.connect(connect_string) |
27 | | - cursor = conn.cursor() |
28 | | - return conn, cursor |
| 20 | + """连接 Oracle 数据库""" |
| 21 | + return cx_Oracle.connect(connect_string) |
29 | 22 |
|
30 | | -def oracle_close(): |
31 | | - |
32 | | - """关闭 oracle 连接""" |
33 | | - cursor.close() |
34 | | - conn.close() |
| 23 | +def get_sql(): |
| 24 | + """获取查询语句""" |
| 25 | + with open('query.sql', 'r') as f: |
| 26 | + sql = f.read() |
| 27 | + return sql |
35 | 28 |
|
36 | | -def get_data(): |
37 | | - print(time.strftime('%Y-%m-%d %H:%M:%S'), "开始数据查询") |
| 29 | +def read_data(filename: str): |
| 30 | + """从 excel 中读取数据""" |
38 | 31 | rwb = load_workbook(filename) |
39 | 32 | sheet = rwb["Sheet1"] |
40 | | - # 遍历获取所有运单id |
41 | | - for item in sheet.rows: |
42 | | - # 取表格第一列值 |
43 | | - column = item[0].value |
44 | | - tid = [column] |
45 | | - # 转换为字符串 |
46 | | - if column is not None: |
47 | | - keyword = ''.join(tid) |
48 | | - else: |
| 33 | + data = [] |
| 34 | + for i, row in enumerate(sheet.iter_rows(values_only=True)): |
| 35 | + if i == 0: # 跳过表头 |
49 | 36 | continue |
50 | | - # print("tid数据类型", type(tid)) |
51 | | - with open('query.sql', 'r') as f: |
52 | | - sql = f.read() |
53 | | - cursor.execute(sql, query=keyword) |
54 | | - |
55 | | - # 循环遍历结果集，获取所有运单回单地址 |
56 | | - for se_data in cursor: |
57 | | - select_data = list(se_data) |
58 | | - picture_url.append(select_data) |
59 | | - # picture_url.append(tuple(select_data)) |
60 | | - print(time.strftime('%Y-%m-%d %H:%M:%S'), "数据查询完成") |
61 | | - # print("------------------------picture_url------------------------\n", picture_url) |
62 | | - |
63 | | - |
64 | | -def save_file(): |
65 | | - # 处理数据格式 |
66 | | - print(time.strftime('%Y-%m-%d %H:%M:%S'), "开始写入文件") |
| 37 | + data.append(row[0]) # 添加第一列的数据 |
| 38 | + return data |
| 39 | + |
| 40 | +def get_data(cursor, filename: str): |
| 41 | + """执行查询并获取数据""" |
| 42 | + print(f"{time.strftime('%Y-%m-%d %H:%M:%S')} 开始读取数据...") |
| 43 | + data = read_data(filename) # 从 Excel 中读取数据 |
| 44 | + print(f"共读取 {len(data)} 条数据") |
| 45 | + |
| 46 | + print(f"{time.strftime('%Y-%m-%d %H:%M:%S')} 开始获取查询语句...") |
| 47 | + sql = get_sql() # 从文件中读取查询语句 |
| 48 | + batch_size = 1000 # 每批查询的大小 |
| 49 | + results = [] |
| 50 | + |
| 51 | + print(f"{time.strftime('%Y-%m-%d %H:%M:%S')} 开始执行查询...") |
| 52 | + # 将数据分成批次 |
| 53 | + for i in range(0, len(data), batch_size): |
| 54 | + batch_data = data[i:i+batch_size] |
| 55 | + |
| 56 | + # 构建SQL查询语句 |
| 57 | + placeholders = ','.join([':%s' % (i+1) for i in range(len(batch_data))]) |
| 58 | + formatted_sql = sql.format(placeholders) |
| 59 | + |
| 60 | + # 执行查询并获取结果 |
| 61 | + print(f"执行查询，批次 {i//batch_size + 1}") |
| 62 | + cursor.execute(formatted_sql, batch_data) |
| 63 | + result = cursor.fetchall() |
| 64 | + results.extend(result) |
| 65 | + |
| 66 | + print(f"{time.strftime('%Y-%m-%d %H:%M:%S')} 查询执行完毕") |
| 67 | + return results |
| 68 | + |
| 69 | +def save_file(filename: str, data: list, headers: list): |
| 70 | + """将数据保存到 Excel 文件""" |
| 71 | + print(f"{time.strftime('%Y-%m-%d %H:%M:%S')} 开始写入文件") |
67 | 72 | wwb = load_workbook(filename) # 创建工作簿对象 |
68 | | - ws = wwb.create_sheet('Mysheet') # 创建子表 |
69 | | - ws.append(['单号', 'url']) # 添加表头 |
70 | | - for data_list in picture_url: |
71 | | - ws.append(data_list) # 每次写入一行 |
| 73 | + ws = wwb.create_sheet('newsheet') # 创建子表 |
| 74 | + ws.append(headers) |
| 75 | + for row in data: |
| 76 | + ws.append(row) |
72 | 77 | wwb.save(filename) |
73 | | - print(time.strftime('%Y-%m-%d %H:%M:%S'), "保存文件") |
74 | | - |
| 78 | + print(f"{time.strftime('%Y-%m-%d %H:%M:%S')} 保存文件") |
75 | 79 |
|
76 | 80 | if __name__ == '__main__': |
77 | | - if len(sys.argv) == 2: |
78 | | - filename = sys.argv[1] |
79 | | - # 创建一个空列表存放查询的结果 |
80 | | - picture_url = [] |
81 | | - # 连接数据库 |
82 | | - oracle_connect(oracle_connect_string) |
83 | | - get_data() |
84 | | - # 关闭数据库连接 |
85 | | - oracle_close() |
86 | | - save_file() |
| 81 | + if len(sys.argv) != 2: |
| 82 | + print(f'Usage: python {sys.argv[0]} file.xlsx') |
| 83 | + sys.exit(1) |
87 | 84 |
|
| 85 | + filename = sys.argv[1] |
| 86 | + start_time = time.time() |
| 87 | + |
| 88 | + try: |
| 89 | + with oracle_connect(oracle_connect_string) as conn: |
| 90 | + cursor = conn.cursor() |
| 91 | + data = get_data(cursor, filename) |
| 92 | + headers = ['表头1', '表头2'] # 根据需要定制表头 |
| 93 | + save_file(filename, data, headers) # 保存到原文件 |
| 94 | + except Exception as e: |
| 95 | + print(f"发生错误: {e}") |
| 96 | + finally: |
88 | 97 | end_time = time.time() |
89 | 98 | print("全部执行完毕") |
90 | | - print("耗时: {:.2f}秒".format(end_time - start_time)) |
91 | | - else: |
92 | | - print('Usage: python %s file.xlsx' % sys.argv[0]) |
| 99 | + print(f"耗时: {end_time - start_time:.2f}秒") |
| 100 | + |
| 101 | + |
0 commit comments