據(jù)量Excel導(dǎo)出實(shí)戰(zhàn)：分塊、斷點(diǎn)續(xù)傳與性能優(yōu)化)
上周業(yè)務(wù)方的同事跑來(lái)找我說(shuō)能不能把庫(kù)里近一年的訂單明細(xì)導(dǎo)成 Excel 他們要自己拉透視表。我第一反應(yīng)是這還不簡(jiǎn)單pandas.read_sql接to_excel幾行代碼就收工。等真拿到生產(chǎn)環(huán)境的數(shù)據(jù)量一看一張表幾千萬(wàn)行直接全量讀取內(nèi)存根本扛不住Excel 單 Sheet 的行數(shù)上限也擺在那根本不是一個(gè)to_excel能解決的問(wèn)題。所以專門寫了這個(gè) Python 批量導(dǎo)出工具把數(shù)據(jù)庫(kù)里的數(shù)據(jù)按批次、按分表、按業(yè)務(wù)維度拆成多個(gè) Excel 文件或者在一個(gè)文件里分多個(gè) Sheet 導(dǎo)出整個(gè)過(guò)程帶進(jìn)度、帶日志、帶斷點(diǎn)續(xù)傳。這篇文章就把我的完整實(shí)現(xiàn)思路、代碼、還有踩過(guò)的坑一次說(shuō)清楚給要做報(bào)表導(dǎo)出、數(shù)據(jù)分發(fā)、運(yùn)營(yíng)取數(shù)的朋友一個(gè)可以直接落地的參考。1. 為什么簡(jiǎn)單的 Excel 導(dǎo)出落地時(shí)卻最容易翻車先說(shuō)結(jié)論單機(jī)、小數(shù)據(jù)量、一次性腳本用 pandas 一把梭確實(shí)沒(méi)問(wèn)題。但數(shù)據(jù)量一旦上來(lái)或者任務(wù)變成每天定時(shí)跑問(wèn)題就一串串往外冒。1.1 數(shù)據(jù)量和 Excel 格式本身的硬限制Excel 的.xlsx格式有個(gè)讓人頭疼的單 Sheet 上限1048576 行。很多業(yè)務(wù)表動(dòng)輒幾百萬(wàn)行直接全量導(dǎo)出必然失敗。就算沒(méi)到上限超過(guò) 20 萬(wàn)行的 Excel 文件打開(kāi)和刷新都會(huì)變得很卡業(yè)務(wù)方的電腦稍微舊一點(diǎn)打開(kāi)一次要等半分鐘。另外還有一個(gè)大家容易忽略的限制單元格內(nèi)文本不能超過(guò) 32767 個(gè)字符。數(shù)據(jù)庫(kù)里如果有大字段、備注、JSON 配置之類的長(zhǎng)文本超長(zhǎng)部分會(huì)被 Excel 靜默截?cái)鄻I(yè)務(wù)方拿到手才發(fā)現(xiàn)數(shù)據(jù)不對(duì)這時(shí)候再排查就尷尬了。所以在導(dǎo)出邏輯里必須預(yù)留字段長(zhǎng)度檢測(cè)和截?cái)嗖呗?。再加?Excel 對(duì)日期時(shí)間格式、超長(zhǎng)數(shù)字比如雪花 ID、手機(jī)號(hào)的自動(dòng)科學(xué)計(jì)數(shù)法問(wèn)題整個(gè)導(dǎo)出任務(wù)要考慮的細(xì)節(jié)一點(diǎn)不比業(yè)務(wù)功能少。1.2 常見(jiàn)方案對(duì)比為什么我選了 SQLAlchemy pandas xlsxwriter寫這個(gè)工具之前我對(duì)比了三種主流方案方案優(yōu)點(diǎn)缺點(diǎn)適用場(chǎng)景pandas.read_sqlDataFrame.to_excel代碼短上手快全量加載內(nèi)存大表直接 OOM無(wú)法精細(xì)控制 Sheet、列寬、格式小數(shù)據(jù)量一次性導(dǎo)出openpyxl直接操作單元格可精確控制格式和樣式逐單元格寫入千萬(wàn)級(jí)數(shù)據(jù)寫入速度慢到懷疑人生報(bào)表美化、模板填充SQLAlchemy流式查詢 xlsxwriter寫入內(nèi)存可控、寫入快、支持格式控制代碼相對(duì)多需要自己管理批次和類型大數(shù)據(jù)量批量導(dǎo)出生產(chǎn)環(huán)境我最終選擇的是第三種的組合但把查詢層包了一層pandas而不是完全手動(dòng)fetchmany。原因是pandas的分塊讀取、類型推斷、空值處理已經(jīng)足夠成熟沒(méi)必要重復(fù)造輪子真正需要自己控制的是分塊大小寫入方式文件切分策略這些工程層面的東西。1.3 批量導(dǎo)出到底在批量什么這個(gè)標(biāo)題里的批量其實(shí)包含三個(gè)維度批量導(dǎo)出多張表比如一個(gè)庫(kù)里有訂單表、用戶表、商品表一次任務(wù)全部導(dǎo)出成獨(dú)立文件或者合成一個(gè)多 Sheet 的 Excel。單張表數(shù)據(jù)量過(guò)大批量分塊導(dǎo)出把 500 萬(wàn)行拆成 5 個(gè)文件每個(gè) 100 萬(wàn)行避免觸發(fā) Excel 上限。增量任務(wù)每天跑只導(dǎo)出當(dāng)天新增或變化的數(shù)據(jù)而不是每天全量掃全表。這三個(gè)維度在代碼設(shè)計(jì)上是互相疊加的下面第二、三章會(huì)分別展開(kāi)。2. 核心實(shí)現(xiàn)從數(shù)據(jù)庫(kù)讀取到 Excel 落盤的完整代碼這一章直接給完整的可運(yùn)行代碼并解釋每一處關(guān)鍵設(shè)計(jì)的原因。我會(huì)用虛擬的表結(jié)構(gòu)order_records做例子表里有id、trade_date、user_id、product_name、remark等字段覆蓋了常規(guī)數(shù)字、日期、文本、長(zhǎng)文本幾種典型類型。2.1 數(shù)據(jù)庫(kù)連接用 SQLAlchemy 統(tǒng)一管理第一步是建立數(shù)據(jù)庫(kù)連接。我習(xí)慣用 SQLAlchemy 的create_engine而不是直接用某個(gè)數(shù)據(jù)庫(kù)驅(qū)動(dòng)裸連原因有兩個(gè)換數(shù)據(jù)庫(kù)不用改代碼MySQL、PostgreSQL、SQLite 都能用同一套導(dǎo)出代碼。pandas.read_sql原生支持 SQLAlchemy 引擎參數(shù)占位符也更安全。from sqlalchemy import create_engine def build_engine(db_type: str mysql, host: str 127.0.0.1, port: int 3306, username: str your_user, password: str your_pass, database: str your_db) - object: 構(gòu)建 SQLAlchemy 數(shù)據(jù)庫(kù)引擎。 如果 db_type 是 sqlite只需傳 database 路徑即可。 if db_type sqlite: return create_engine(fsqlite:///{database}, pool_pre_pingTrue) url f{db_type}://{username}:{password}{host}:{port}/{database}?charsetutf8mb4 return create_engine(url, pool_pre_pingTrue, pool_size5, pool_recycle3600)注意charsetutf8mb4這個(gè)對(duì) MySQL 很重要。utf8mb4才能完整支持 emoji 和生僻字如果漏掉數(shù)據(jù)里帶個(gè) emoji 符號(hào)寫到 Excel 里就會(huì)變成亂碼。這是我在真實(shí)項(xiàng)目中踩過(guò)的坑。pool_pre_pingTrue這個(gè)參數(shù)的意思是每次從連接池取連接之前先做一次輕量探測(cè)防止拿到一個(gè)已經(jīng)失效的連接。定時(shí)任務(wù)里數(shù)據(jù)庫(kù)連接經(jīng)常被服務(wù)端主動(dòng)斷開(kāi)加上這個(gè)參數(shù)能省掉很多莫名其妙的MySQL server has gone away報(bào)錯(cuò)。2.2 主函數(shù)按配置批量導(dǎo)出多張表到同一個(gè) Excel這個(gè)設(shè)計(jì)的核心思路是把導(dǎo)出什么表導(dǎo)出哪些列按什么條件過(guò)濾都放到配置字典里代碼本身保持通用。這樣業(yè)務(wù)上新增一張表的導(dǎo)出需求只需要加一段配置不需要改邏輯。import pandas as pd from sqlalchemy import text def export_tables_to_excel(engine, tables_config: list, output_path: str, chunksize: int 50000, sheet_limit: int 500000): tables_config: [ { sheet_name: 訂單表, query: SELECT id, trade_date, user_id, product_name, remark FROM order_records WHERE trade_date :start_date, params: {start_date: 2024-01-01}, order_by: id, }, ... ] writer pd.ExcelWriter(output_path, enginexlsxwriter) for cfg in tables_config: sheet_name cfg[sheet_name][:31] # Excel sheet 名最長(zhǎng) 31 字符 query cfg[query] params cfg.get(params, {}) order_by cfg.get(order_by, ) # 構(gòu)造帶分頁(yè)包裹的查詢后面解釋為什么這么做 paged_query wrap_query_with_row_number(query, order_by) total_exported 0 sheet_row 0 # 當(dāng)前 sheet 已寫入行數(shù) # 把分頁(yè)條件逐段傳入 offset 0 while True: page_query paged_query f LIMIT {chunksize} OFFSET {offset} df pd.read_sql(text(page_query), engine, paramsparams) if df.empty: break # 達(dá)到單個(gè) sheet 的行數(shù)上限換下一個(gè) sheet if sheet_row len(df) sheet_limit: # 先處理不滿一個(gè) sheet 的剩余部分... pass df.to_excel(writer, sheet_namesheet_name, indexFalse, startrowsheet_row 1, header(sheet_row 0)) sheet_row len(df) total_exported len(df) offset chunksize print(f[{sheet_name}] 已導(dǎo)出 {total_exported} 行偏移 {offset}) writer.close() print(f導(dǎo)出完成{output_path})這段代碼里wrap_query_with_row_number是我封裝的一個(gè)分頁(yè)函數(shù)下面單獨(dú)說(shuō)明。先解釋幾個(gè)關(guān)鍵點(diǎn)indexFalse必須寫DataFrame自帶的行索引如果不關(guān)掉會(huì)以多出來(lái)一列的方式寫進(jìn) Excel業(yè)務(wù)方會(huì)看到一列沒(méi)名字的序號(hào)非常不專業(yè)。header(sheet_row 0)控制列名只寫一次分塊導(dǎo)出到同一個(gè) sheet 時(shí)只有第一塊數(shù)據(jù)需要寫列名后面續(xù)寫只需要追加數(shù)據(jù)行。這個(gè)判斷非常關(guān)鍵不寫的話每個(gè)分塊都會(huì)重復(fù)一行字段名。startrowsheet_row 1跳過(guò)列名行第一塊從第 1 行開(kāi)始寫0 是列名行后續(xù)塊從已有數(shù)據(jù)行之后續(xù)寫。2.3 穩(wěn)定分頁(yè)用主鍵排序代替 OFFSETLIMIT ... OFFSET ...在數(shù)據(jù)量小的時(shí)候沒(méi)問(wèn)題但偏移量越大數(shù)據(jù)庫(kù)掃描越慢。更嚴(yán)重的是如果trade_date有重復(fù)值而分頁(yè)排序不穩(wěn)定同一批數(shù)據(jù)可能被重復(fù)導(dǎo)出或漏掉。我在這里用了基于排序鍵的鍵集分頁(yè)穩(wěn)定且性能好def stable_page_query(base_query: str, order_column: str, last_value, page_size: int): if last_value is None: return f{base_query} ORDER BY {order_column} LIMIT {page_size} return f{base_query} AND {order_column} :last_value ORDER BY {order_column} LIMIT {page_size}每次記錄當(dāng)前頁(yè)最后一條order_column的值下一頁(yè)用WHERE order_column last_value取數(shù)據(jù)。這樣即使數(shù)據(jù)在導(dǎo)出過(guò)程中被并發(fā)寫入也不會(huì)出現(xiàn) OFFSET 分頁(yè)那種頁(yè)與頁(yè)之間數(shù)據(jù)錯(cuò)位的問(wèn)題。當(dāng)然前提是order_column有唯一性或者至少業(yè)務(wù)上能接受排序鍵相同的數(shù)據(jù)被一起跨越的邊緣情況。實(shí)際操作中我會(huì)要求配置里必須帶一個(gè)order_by字段并且優(yōu)先用主鍵id作為排序鍵這是最穩(wěn)妥的方案。2.4 數(shù)據(jù)類型預(yù)處理防止寫進(jìn) Excel 就變味兒數(shù)據(jù)庫(kù)里的數(shù)據(jù)類型跟 Excel 單元格格式不是一一對(duì)應(yīng)的下面這幾個(gè)處理必須在寫入前完成def prepare_dataframe(df: pd.DataFrame, datetime_cols: list None, long_text_cols: list None) - pd.DataFrame: datetime_cols: 需要顯式保留日期時(shí)間的列名列表 long_text_cols: 可能超過(guò) 32767 字符的長(zhǎng)文本列 if datetime_cols: for col in datetime_cols: if col in df.columns: df[col] pd.to_datetime(df[col]).dt.strftime(%Y-%m-%d %H:%M:%S) if long_text_cols: for col in long_text_cols: if col in df.columns: df[col] df[col].astype(str).str.slice(0, 32000) # 把 id 這類超長(zhǎng)數(shù)字列顯式轉(zhuǎn)成字符串避免科學(xué)計(jì)數(shù)法 for col in df.select_dtypes(include[int64, float64]).columns: if col.lower().endswith((id, no, num)): df[col] df[col].astype(str) return df這里有三件事值得展開(kāi)日期轉(zhuǎn)字符串pandas 寫 datetime 到 Excel 時(shí)如果 xlsxwriter 的格式化沒(méi)配對(duì)單元格顯示的是一串?dāng)?shù)字Excel 日期本質(zhì)是序列號(hào)。我選擇直接在 SQL 查詢或 pandas 里把日期格式化成標(biāo)準(zhǔn)字符串一勞永逸。超長(zhǎng)文本截?cái)嘟財(cái)嗟?32000 而不是 32767留出一點(diǎn)余量避免多字節(jié)字符在邊界被截?cái)喈a(chǎn)生亂碼。長(zhǎng)數(shù)字轉(zhuǎn)字符串這招防的是手機(jī)號(hào)、雪花 ID 這類超過(guò) 15 位的純數(shù)字如果不轉(zhuǎn)字符串它們?cè)?Excel 里會(huì)變成科學(xué)計(jì)數(shù)法看起來(lái)像4.10217E18精度還丟了。2.5 完整調(diào)用示例把上面的函數(shù)串起來(lái)實(shí)際使用長(zhǎng)這樣if __name__ __main__: engine build_engine(databasebusiness_db) tables [ { sheet_name: 訂單明細(xì), query: SELECT id, trade_date, user_id, product_name, remark FROM order_records WHERE trade_date :start_date , params: {start_date: 2024-01-01}, order_by: id, }, { sheet_name: 用戶信息, query: SELECT id, user_name, register_date, level FROM user_info, params: {}, order_by: id, }, ] export_tables_to_excel( engine, tables_configtables, output_path/data/export/business_2024.xlsx, chunksize50000, sheet_limit400000, )這里我把sheet_limit設(shè)成 40 萬(wàn)而不是 Excel 上限 104 萬(wàn)是有意的。超過(guò) 40 萬(wàn)行的 sheetExcel 打開(kāi)已經(jīng)明顯卡頓與其交給業(yè)務(wù)方一個(gè)能開(kāi)但很慢的大文件不如在導(dǎo)出時(shí)就按更保守的閾值切分。3. 數(shù)據(jù)量特別大時(shí)的優(yōu)化分塊、并發(fā)與內(nèi)存控制當(dāng)單表數(shù)據(jù)達(dá)到千萬(wàn)級(jí)別前面那個(gè)單進(jìn)程、串行分塊的方案還是不夠快。這一章說(shuō)三個(gè)優(yōu)化手段按性價(jià)比排序。3.1 內(nèi)存估算為什么不能一次性全量讀取很多人寫導(dǎo)出腳本的第一版都是pd.read_sql(query, engine)一把梭幾百萬(wàn)行數(shù)據(jù)全讀進(jìn)內(nèi)存。我們來(lái)粗算一下一行訂單記錄假設(shè)有 20 個(gè)字段平均每個(gè)字段算 20 字節(jié)500 萬(wàn)行就是500萬(wàn) × 20 × 20 2億字節(jié)約 200 MB?？粗€好但 pandas 為了類型推斷會(huì)臨時(shí)復(fù)制多份副本實(shí)際峰值可能是這個(gè)數(shù)的 3 到 4 倍。再加上 DataFrame 索引、字符串對(duì)象本身需要的內(nèi)存一個(gè) 500 萬(wàn)行的 DataFrame 吃 1 GB 內(nèi)存很正常。如果服務(wù)器上還跑著其他服務(wù)這基本就是 OOM 的前奏了。所以我堅(jiān)持在任何生產(chǎn)導(dǎo)出腳本里都做分塊讀取既為了控制內(nèi)存也為了能在每塊之間打印進(jìn)度。3.2 分塊大小怎么定分塊大小chunksize是個(gè)需要平衡的參數(shù)。我自己的經(jīng)驗(yàn)公式行平均寬度小比如只有幾個(gè)整數(shù)列可以設(shè) 10 萬(wàn)到 20 萬(wàn)。行平均寬度大有長(zhǎng)文本、JSON、多字符串列建議設(shè) 2 萬(wàn)到 5 萬(wàn)。寧可塊數(shù)多也不要讓單塊內(nèi)存超過(guò) 200 MB。實(shí)際壓測(cè)時(shí)我會(huì)在分塊讀取的循環(huán)里監(jiān)控一下內(nèi)存變化import tracemalloc tracemalloc.start() # 分塊循環(huán)讀取... current, peak tracemalloc.get_traced_memory() print(f當(dāng)前內(nèi)存: {current / 1024 / 1024:.1f} MB, 峰值: {peak / 1024 / 1024:.1f} MB)這里的核心原則是單塊讀完了、寫完了內(nèi)存就該釋放。如果發(fā)現(xiàn)內(nèi)存只增不減優(yōu)先檢查是不是有人把每個(gè)分塊都append到了一個(gè)總 DataFrame 里——這是最常見(jiàn)的隱性內(nèi)存泄漏。3.3 多進(jìn)程并行不同表并行單表按 ID 范圍切分如果你的數(shù)據(jù)庫(kù)服務(wù)器 CPU 核數(shù)多可以并行導(dǎo)出多張獨(dú)立的表。Python 的多線程在 IO 密集型任務(wù)上有用但這里的主要瓶頸是數(shù)據(jù)庫(kù)查詢后的 pandas 內(nèi)存處理屬于 CPU 和內(nèi)存密集型所以用multiprocessing更合適。多進(jìn)程最簡(jiǎn)單的寫法是concurrent.futures.ProcessPoolExecutorfrom concurrent.futures import ProcessPoolExecutor, as_completed def export_one_table(cfg: dict): 導(dǎo)出單張表獨(dú)立進(jìn)程執(zhí)行 engine build_engine() # 每個(gè)進(jìn)程單獨(dú)建連接 return export_tables_to_excel( engine, [cfg], cfg[output_path], chunksizecfg.get(chunksize, 50000), sheet_limitcfg.get(sheet_limit, 400000), ) def parallel_export(all_configs: list): with ProcessPoolExecutor(max_workers4) as pool: futures [pool.submit(export_one_table, cfg) for cfg in all_configs] for future in as_completed(futures): print(future.result())注意每個(gè)子進(jìn)程必須自己創(chuàng)建數(shù)據(jù)庫(kù)連接和ExcelWriter不能把父進(jìn)程的 engine 直接傳進(jìn)去。數(shù)據(jù)庫(kù)連接不能跨進(jìn)程共享這是很多人踩過(guò)坑的地方。如果是單張表特別大多進(jìn)程思路要反過(guò)來(lái)按主鍵id的范圍切成 N 段每個(gè)進(jìn)程導(dǎo)出[id_min, id_max)區(qū)間。切分方式先查一下表的最小和最大主鍵SELECT MIN(id), MAX(id), COUNT(*) FROM order_records;然后根據(jù)總數(shù)和期望的每段行數(shù)算出切割點(diǎn)再按切割點(diǎn)生成各自的WHERE id BETWEEN ... AND ...查詢。這個(gè)方案的好處是天然支持?jǐn)帱c(diǎn)續(xù)傳——某個(gè)區(qū)間跑掛了重跑那個(gè)區(qū)間即可。3.4 寫入引擎的選擇xlsxwriter 的 write_only 模式pandas.DataFrame.to_excel支持傳入不同的 engineopenpyxl和xlsxwriter對(duì)同一份數(shù)據(jù)的寫入性能差距明顯。xlsxwriter有一個(gè) write-only 模式專門為大數(shù)據(jù)量設(shè)計(jì)workbook xlsxwriter.Workbook(output_path, {constant_memory: True}) worksheet workbook.add_worksheet(訂單明細(xì))開(kāi)啟這個(gè)模式后xlsxwriter 不會(huì)在內(nèi)存里維護(hù)完整的行數(shù)據(jù)而是邊寫邊落盤內(nèi)存占用大幅下降。唯一的不方便是不能回改已經(jīng)寫過(guò)的單元格——但導(dǎo)出任務(wù)根本不需要回改所以完全適配。在pandas.ExcelWriter里啟用方式也簡(jiǎn)單writer pd.ExcelWriter(output_path, enginexlsxwriter, engine_kwargs{options: {constant_memory: True}})我之前做過(guò)一次對(duì)比測(cè)試同樣是 80 萬(wàn)行、15 列的數(shù)據(jù)openpyxl寫入耗時(shí)接近 3 分鐘xlsxwriter的 constant_memory 模式只要 40 秒左右。這個(gè)差距在生產(chǎn)任務(wù)里非常可觀。4. 我把這些 Excel 發(fā)給業(yè)務(wù)方后踩過(guò)的那些坑代碼能跑通只是第一步。真實(shí)世界里能把文件生成出來(lái)和業(yè)務(wù)方能正常使用之間隔著好幾個(gè)大坑。這一章記錄我實(shí)際交付過(guò)程中踩過(guò)的坑和對(duì)應(yīng)的處理辦法。4.1 單 Sheet 超過(guò) 104 萬(wàn)行不是報(bào)錯(cuò)但比報(bào)錯(cuò)更麻煩有一次我導(dǎo)出一張日志表數(shù)據(jù)量 130 多萬(wàn)行我滿懷信心地等著文件生成結(jié)果確實(shí)生成了但 Excel 打開(kāi)時(shí)報(bào)文件損壞原因是超過(guò)了單 Sheet 上限。這個(gè)坑最坑的地方在于文件層面已經(jīng)寫壞了但導(dǎo)出腳本沒(méi)有任何異常提示。xlsxwriter 會(huì)把寫到 1048577 行之后的邏輯線強(qiáng)行掐掉但文件打開(kāi)前你永遠(yuǎn)不知道。我的處理辦法是在寫入循環(huán)里做一個(gè)硬檢查MAX_EXCEL_ROWS 1048576 if sheet_row len(df) MAX_EXCEL_ROWS: # 當(dāng)前 sheet 已滿另起一個(gè) sheet 或另起文件 ...更穩(wěn)妥的做法是在方案設(shè)計(jì)階段就按業(yè)務(wù)數(shù)據(jù)的增長(zhǎng)趨勢(shì)預(yù)估未來(lái)的行數(shù)提前把一個(gè)大表的數(shù)據(jù)按時(shí)間維度分文件導(dǎo)出。比如日志表就按天導(dǎo)出一天一個(gè)文件既避免行數(shù)問(wèn)題業(yè)務(wù)方拿數(shù)據(jù)也更方便。4.2 數(shù)字被顯示成科學(xué)計(jì)數(shù)法的三個(gè)來(lái)源這個(gè)問(wèn)題太常見(jiàn)了常見(jiàn)到每個(gè)做導(dǎo)出的人都會(huì)遇到。超長(zhǎng) ID19 位的雪花 IDExcel 顯示成4.10217E18而且后幾位被當(dāng)成 0原始精度丟失。手機(jī)號(hào)11 位數(shù)字理論上不超過(guò) Excel 的 15 位精度限制但很多手機(jī)號(hào)第 11 位附近會(huì)出現(xiàn)奇怪的浮點(diǎn)誤差導(dǎo)出后末尾變成12300這種。帶前導(dǎo)零的編碼比如商品編碼001234Excel 會(huì)自作主張把前導(dǎo)零省略。解決思路分兩層第一層在 SQL 查詢階段就轉(zhuǎn)成字符串。比如 MySQL 用CAST(id AS CHAR)PostgreSQL 用id::text讓數(shù)據(jù)庫(kù)返回字符串而不是數(shù)字pandas 拿到手就是字符串再寫入 Excel 就不會(huì)觸發(fā)科學(xué)計(jì)數(shù)法。第二層如果數(shù)據(jù)已經(jīng)以數(shù)字類型到了 pandas 里就用df[col] df[col].astype(str)轉(zhuǎn)一次。注意此時(shí)如果數(shù)字已經(jīng)是浮點(diǎn)型直接轉(zhuǎn)字符串會(huì)帶.0后綴要先轉(zhuǎn)int64再轉(zhuǎn)str或者用小數(shù)點(diǎn)后 0 位格式化。4.3 日期顯示成數(shù)字或 Unix 時(shí)間戳pandas 寫 datetime 到 xlsxwriter 時(shí)如果不設(shè)置單元格格式日期會(huì)顯示成45087.3456這種序列數(shù)。我一開(kāi)始沒(méi)做特殊處理業(yè)務(wù)方拿到文件后發(fā)給我一堆問(wèn)號(hào)這個(gè)數(shù)字是什么后來(lái)我總結(jié)出一個(gè)最省心的方案所有日期列在寫入前統(tǒng)一格式化成YYYY-MM-DD HH:MM:SS字符串。df[trade_date] pd.to_datetime(df[trade_date]).dt.strftime(%Y-%m-%d %H:%M:%S)字符串日期寫入 Excel 后是左對(duì)齊的文本格式雖然不能直接在 Excel 里做日期運(yùn)算但對(duì)于導(dǎo)出給業(yè)務(wù)方看這個(gè)場(chǎng)景完全夠用。如果有分析師需要日期參與計(jì)算他們自己用分列功能轉(zhuǎn)一下批量也很方便。4.4 亂碼問(wèn)題誰(shuí)都可能遇到但原因各不同亂碼的排查路徑我這幾年走得很熟練了按位置分成三種數(shù)據(jù)庫(kù)讀出來(lái)就是亂碼大概率是連接串沒(méi)加charsetutf8mb4或者表字段本身的字符集不對(duì)。pandas 階段亂碼檢查拿到的 DataFrame 里打印出來(lái)的值是正常的但寫出去是亂碼多半是 ExcelWriter 的編碼設(shè)置被動(dòng)了或者用了 CSV 輸出時(shí)沒(méi)指定utf-8-sig。Excel 打開(kāi)亂碼但代碼輸出正常這個(gè)最常見(jiàn)就是 CSV 文件沒(méi)有 BOM 頭。Excel 默認(rèn)用 GBK 編碼讀 CSV沒(méi)有 BOM 的 UTF-8 文件它識(shí)別不出來(lái)。CSV 導(dǎo)出時(shí)一定要用df.to_csv(path, indexFalse, encodingutf-8-sig)utf-8-sig會(huì)自動(dòng)帶上 BOMExcel 就能正確識(shí)別。4.5 一個(gè)慢得無(wú)法忍受的文件性能瓶頸可能在單元格格式我遇到過(guò)這么一次數(shù)據(jù)量不大才 30 萬(wàn)行但導(dǎo)出耗時(shí)非常長(zhǎng)一看代碼循環(huán)里對(duì)每個(gè)單元格調(diào)用了worksheet.write_number()后面還跟了一堆set_format()。naive 寫法的性能殺手是逐單元格操作 格式對(duì)象綁定。優(yōu)化辦法是能用pandas的批量寫入就不要自己循環(huán)寫單元格。如果要設(shè)置列寬、表頭樣式只對(duì)列名行做格式化數(shù)據(jù)行保持默認(rèn)。表頭樣式可以用worksheet.set_row(0, None, header_format)這種批量方式而不是每個(gè)單元格單獨(dú)設(shè)置。這個(gè)優(yōu)化做完同樣的 30 萬(wàn)行數(shù)據(jù)耗時(shí)從 4 分多鐘降到了 20 秒以內(nèi)效果立竿見(jiàn)影。5. 讓導(dǎo)出任務(wù)變成可運(yùn)維的定時(shí)任務(wù)斷點(diǎn)續(xù)傳與失敗重試一次性的手工導(dǎo)出很簡(jiǎn)單真正的難點(diǎn)在于把這個(gè)腳本變成每天自動(dòng)跑、掛了能恢復(fù)、跑完有日志的生產(chǎn)級(jí)定時(shí)任務(wù)。這一章是我在交付給其他團(tuán)隊(duì)后根據(jù)使用反饋迭代出的工程化改進(jìn)。5.1 斷點(diǎn)續(xù)傳以已導(dǎo)出的最大主鍵為錨點(diǎn)最樸素的斷點(diǎn)續(xù)傳方案是記錄上一次導(dǎo)出的最大主鍵值。下次任務(wù)啟動(dòng)時(shí)SQL 條件從id last_max_id開(kāi)始查而不是從全表開(kāi)頭查。def get_last_checkpoint(state_file: str) - int: try: with open(state_file, r) as f: return int(f.read().strip()) except (FileNotFoundError, ValueError): return 0 def save_checkpoint(state_file: str, last_id: int): with open(state_file, w) as f: f.write(str(last_id))這個(gè)方案有幾個(gè)前提表必須有單調(diào)遞增的主鍵或者至少有一個(gè)遞增的業(yè)務(wù)時(shí)間字段。每次任務(wù)只追加新數(shù)據(jù)不修改歷史數(shù)據(jù)。增量導(dǎo)出的數(shù)據(jù)范圍是連續(xù)的區(qū)間不存在越過(guò)空白段后還有新數(shù)據(jù)的情況。如果表結(jié)構(gòu)不滿足這些前提斷點(diǎn)續(xù)傳的意義就大打折扣這種情況建議改為按時(shí)間窗口全量重導(dǎo)或者把增量標(biāo)志位交給業(yè)務(wù)系統(tǒng)維護(hù)。5.2 失敗重試指數(shù)退避而不是立即硬沖數(shù)據(jù)庫(kù)連接抖動(dòng)、鎖競(jìng)爭(zhēng)、網(wǎng)絡(luò)閃斷這些都是定時(shí)任務(wù)最常遇到的故障類型。如果失敗后立即重試很可能在同一瞬間再次失敗。我習(xí)慣用帶指數(shù)退避的重試邏輯import time def retry_call(func, retries3, base_delay2): for attempt in range(retries): try: return func() except Exception as exc: if attempt retries - 1: raise delay base_delay * (2 ** attempt) print(f第 {attempt 1} 次失敗{delay}s 后重試: {exc}) time.sleep(delay)每次失敗的間隔按 2 秒、4 秒、8 秒指數(shù)增長(zhǎng)給數(shù)據(jù)庫(kù)留出恢復(fù)窗口。同時(shí)記錄最后失敗位置讓下一次任務(wù)可以從斷點(diǎn)繼續(xù)。5.3 日志是導(dǎo)出任務(wù)的靈魂我接手過(guò)別人的導(dǎo)出腳本代碼結(jié)尾只有一行print(done)。出問(wèn)題時(shí)完全不知道數(shù)據(jù)導(dǎo)到哪一步了、出了多少行、卡在哪個(gè)表上。我的日志規(guī)范很簡(jiǎn)單每個(gè)表開(kāi)始導(dǎo)出前記錄表名、預(yù)計(jì)數(shù)據(jù)量、開(kāi)始時(shí)間。每個(gè)分塊導(dǎo)出后記錄已導(dǎo)出行數(shù)、耗時(shí)、當(dāng)前主鍵邊界。任務(wù)結(jié)束或異常退出統(tǒng)一寫一個(gè) summary每個(gè)表導(dǎo)出了多少行、總耗時(shí)、是否有跳過(guò)和重試。import logging import time logging.basicConfig( levellogging.INFO, format%(asctime)s [%(levelname)s] %(message)s, handlers[logging.FileHandler(/data/export/export.log)] ) logger logging.getLogger(exporter) def export_with_log(engine, cfg): start time.time() total 0 logger.info(f開(kāi)始導(dǎo)出表 {cfg[sheet_name]}) # ... 分塊導(dǎo)出循環(huán) ... logger.info(f表 {cfg[sheet_name]} 完成共 {total} 行耗時(shí) {time.time() - start:.2f}s)有了這個(gè)日志任何一次任務(wù)異常都能快速定位是數(shù)據(jù)庫(kù)查詢太慢、某一行的數(shù)據(jù)格式有問(wèn)題、還是寫 Excel 時(shí)文件被占用。日志不求花哨但必須能讓接手的同事在 5 分鐘內(nèi)定位到問(wèn)題。5.4 定時(shí)調(diào)度的落地姿勢(shì)我用 cron 表達(dá)式做調(diào)度核心命令很簡(jiǎn)單0 2 * * * /usr/bin/python3 /opt/scripts/exporter.py /data/export/cron.log 21凌晨?jī)牲c(diǎn)跑業(yè)務(wù)低谷數(shù)據(jù)庫(kù)壓力小文件生成后也不影響上班時(shí)間取數(shù)。這里有個(gè)細(xì)節(jié)不要直接用python exporter.py而是寫一個(gè)啟動(dòng)腳本先激活虛擬環(huán)境再跑。項(xiàng)目依賴如果裝在全局環(huán)境某天其他人動(dòng)了依賴版本整個(gè)定時(shí)任務(wù)就無(wú)聲無(wú)息地掛了。我之前為此定了一條規(guī)矩任何導(dǎo)出任務(wù)的啟動(dòng)命令都固定為cd /opt/scripts source .venv/bin/activate python exporter.pycron 里寫全路徑不要依賴默認(rèn)環(huán)境的任何東西。定時(shí)任務(wù)跑成功后還可以加一個(gè)可選的發(fā)送通知環(huán)節(jié)把導(dǎo)出的文件上傳到公司的文件平臺(tái)或?qū)ο蟠鎯?chǔ)然后把下載鏈接發(fā)給業(yè)務(wù)群。這一步看團(tuán)隊(duì)基礎(chǔ)設(shè)施有空實(shí)現(xiàn)一下價(jià)值很大因?yàn)闃I(yè)務(wù)方不用每次找運(yùn)維或者開(kāi)發(fā)要文件了。6. 一些值得再摳一摳的細(xì)節(jié)格式美化與文件命名導(dǎo)出任務(wù)做到這里已經(jīng)是可用的生產(chǎn)工具了但要讓業(yè)務(wù)方用得舒服還有幾個(gè)小細(xì)節(jié)值得花時(shí)間優(yōu)化。6.1 列寬自適應(yīng)別讓業(yè)務(wù)方每次手動(dòng)拉列寬xlsxwriter 里可以給 Sheet 設(shè)置默認(rèn)列寬或者針對(duì)特定列設(shè)置更寬的值worksheet.set_column(A:A, 12) # ID 列 worksheet.set_column(B:B, 20) # 日期列 worksheet.set_column(C:C, 30) # 名稱列如果列很多不想一個(gè)個(gè)指定可以按列名的最大字符長(zhǎng)度估算for i, col in enumerate(df.columns): max_len max(df[col].astype(str).str.len().max(), len(col)) 2 worksheet.set_column(i, i, min(max_len, 50))這個(gè)操作對(duì)使用者體驗(yàn)提升非常明顯值得做。6.2 表頭加粗與凍結(jié)首行凍結(jié)首行這個(gè)功能幾乎是業(yè)務(wù)方最喜歡的一個(gè)。表數(shù)據(jù)一多往下拉的時(shí)候完全不知道每一列是什么凍結(jié)首行后這個(gè)痛點(diǎn)直接消失。header_format workbook.add_format({bold: True, bg_color: #D9E1F2, border: 1}) worksheet.set_row(0, None, header_format) worksheet.freeze_panes(1, 0)一次設(shè)置永久生效整個(gè)文件的專業(yè)度立刻上一個(gè)檔次。6.3 文件命名規(guī)則時(shí)間戳 業(yè)務(wù)標(biāo)識(shí)文件命名看起來(lái)小但影響的是整個(gè)交付鏈路。我經(jīng)?？吹接腥藢?dǎo)出文件叫export.xlsx結(jié)果第二天再導(dǎo)出就把昨天的文件覆蓋了或者業(yè)務(wù)方同時(shí)拿到 5 個(gè)export.xlsx根本分不清哪個(gè)是哪個(gè)。我的命名規(guī)范是業(yè)務(wù)日期_數(shù)據(jù)范圍_生成時(shí)間戳.xlsx比如order_records_20240101_20240630_20240701020000.xlsx保證文件名里能看出數(shù)據(jù)的時(shí)間范圍、生成的時(shí)間點(diǎn)且不會(huì)互相覆蓋。如果是定時(shí)增量任務(wù)這個(gè)習(xí)慣能幫你少背很多鍋。6.4 空數(shù)據(jù)表的處理寧可給空模板也別少一個(gè) Sheet業(yè)務(wù)方用 Excel 最怕的情況是今天導(dǎo)出數(shù)據(jù)恰好是 0 行腳本直接跳過(guò)了這個(gè) sheet業(yè)務(wù)方以為程序出 bug 了實(shí)際上只是沒(méi)數(shù)據(jù)。我的處理邏輯是如果查詢結(jié)果為空也創(chuàng)建 Sheet只寫入列名和一行無(wú)數(shù)據(jù)說(shuō)明。這樣業(yè)務(wù)方一眼就明白這個(gè)時(shí)間段確實(shí)沒(méi)有數(shù)據(jù)而不是工具壞了。if df.empty: empty_df pd.DataFrame(columnscfg[columns]) empty_df.to_excel(writer, sheet_namesheet_name, indexFalse)小小的一個(gè)堅(jiān)持省掉了很多不必要的需求溝通。7. 從能跑到好用的個(gè)人經(jīng)驗(yàn)總結(jié)最后說(shuō)說(shuō)我做完這個(gè)導(dǎo)出工具后的幾點(diǎn)體會(huì)。導(dǎo)出這個(gè)功能看起來(lái)是人人都能做的最小功能但真正能穩(wěn)定服務(wù)業(yè)務(wù)的導(dǎo)出工具一定不是pd.read_sql to_excel幾行代碼就算完成的。數(shù)據(jù)量邊界、格式兼容性、異?；謴?fù)、可觀測(cè)性、使用體驗(yàn)缺一不可。尤其是在數(shù)據(jù)量大、任務(wù)頻繁的場(chǎng)景下一個(gè)沒(méi)有分塊、沒(méi)有日志、沒(méi)有斷點(diǎn)的導(dǎo)出腳本遲早會(huì)變成半夜的告警電話來(lái)源。我自己的習(xí)慣是任何導(dǎo)出任務(wù)都先問(wèn)三個(gè)問(wèn)題——數(shù)據(jù)量級(jí)是多少多久跑一次誰(shuí)在用這個(gè)文件數(shù)據(jù)量決定要不要分塊頻率決定要不要斷點(diǎn)續(xù)傳和定時(shí)調(diào)度使用者的技術(shù)水平?jīng)Q定我要不要在格式上多花心思。這三個(gè)問(wèn)題問(wèn)清楚了方案就自然浮出水面。如果看完這篇你還是只想抄一個(gè)最小可用的版本那至少記得導(dǎo)出前先做數(shù)據(jù)類型預(yù)處理導(dǎo)出時(shí)分塊控制內(nèi)存導(dǎo)出后確認(rèn) sheet 行數(shù)沒(méi)超上限。這三件事做到位你的導(dǎo)出腳本就能超過(guò)大多數(shù)臨時(shí)腳本成為別人愿意持續(xù)使用的工具。 Python大數(shù)據(jù)量Excel導(dǎo)出實(shí)戰(zhàn)：分塊、斷點(diǎn)續(xù)傳與性能優(yōu)化
上周業(yè)務(wù)方的同事跑來(lái)找我說(shuō)能不能把庫(kù)里近一年的訂單明細(xì)導(dǎo)成 Excel 他們要自己拉透視表。我第一反應(yīng)是這還不簡(jiǎn)單pandas.read_sql接to_excel幾行代碼就收工。等真拿到生產(chǎn)環(huán)境的數(shù)據(jù)量一看一張表幾千萬(wàn)行直接全量讀取內(nèi)存根本扛不住Excel 單 Sheet 的行數(shù)上限也擺在那根本不是一個(gè)to_excel能解決的問(wèn)題。所以專門寫了這個(gè) Python 批量導(dǎo)出工具把數(shù)據(jù)庫(kù)里的數(shù)據(jù)按批次、按分表、按業(yè)務(wù)維度拆成多個(gè) Excel 文件或者在一個(gè)文件里分多個(gè) Sheet 導(dǎo)出整個(gè)過(guò)程帶進(jìn)度、帶日志、帶斷點(diǎn)續(xù)傳。這篇文章就把我的完整實(shí)現(xiàn)思路、代碼、還有踩過(guò)的坑一次說(shuō)清楚給要做報(bào)表導(dǎo)出、數(shù)據(jù)分發(fā)、運(yùn)營(yíng)取數(shù)的朋友一個(gè)可以直接落地的參考。1. 為什么簡(jiǎn)單的 Excel 導(dǎo)出落地時(shí)卻最容易翻車先說(shuō)結(jié)論單機(jī)、小數(shù)據(jù)量、一次性腳本用 pandas 一把梭確實(shí)沒(méi)問(wèn)題。但數(shù)據(jù)量一旦上來(lái)或者任務(wù)變成每天定時(shí)跑問(wèn)題就一串串往外冒。1.1 數(shù)據(jù)量和 Excel 格式本身的硬限制Excel 的.xlsx格式有個(gè)讓人頭疼的單 Sheet 上限1048576 行。很多業(yè)務(wù)表動(dòng)輒幾百萬(wàn)行直接全量導(dǎo)出必然失敗。就算沒(méi)到上限超過(guò) 20 萬(wàn)行的 Excel 文件打開(kāi)和刷新都會(huì)變得很卡業(yè)務(wù)方的電腦稍微舊一點(diǎn)打開(kāi)一次要等半分鐘。另外還有一個(gè)大家容易忽略的限制單元格內(nèi)文本不能超過(guò) 32767 個(gè)字符。數(shù)據(jù)庫(kù)里如果有大字段、備注、JSON 配置之類的長(zhǎng)文本超長(zhǎng)部分會(huì)被 Excel 靜默截?cái)鄻I(yè)務(wù)方拿到手才發(fā)現(xiàn)數(shù)據(jù)不對(duì)這時(shí)候再排查就尷尬了。所以在導(dǎo)出邏輯里必須預(yù)留字段長(zhǎng)度檢測(cè)和截?cái)嗖呗?。再加?Excel 對(duì)日期時(shí)間格式、超長(zhǎng)數(shù)字比如雪花 ID、手機(jī)號(hào)的自動(dòng)科學(xué)計(jì)數(shù)法問(wèn)題整個(gè)導(dǎo)出任務(wù)要考慮的細(xì)節(jié)一點(diǎn)不比業(yè)務(wù)功能少。1.2 常見(jiàn)方案對(duì)比為什么我選了 SQLAlchemy pandas xlsxwriter寫這個(gè)工具之前我對(duì)比了三種主流方案方案優(yōu)點(diǎn)缺點(diǎn)適用場(chǎng)景pandas.read_sqlDataFrame.to_excel代碼短上手快全量加載內(nèi)存大表直接 OOM無(wú)法精細(xì)控制 Sheet、列寬、格式小數(shù)據(jù)量一次性導(dǎo)出openpyxl直接操作單元格可精確控制格式和樣式逐單元格寫入千萬(wàn)級(jí)數(shù)據(jù)寫入速度慢到懷疑人生報(bào)表美化、模板填充SQLAlchemy流式查詢 xlsxwriter寫入內(nèi)存可控、寫入快、支持格式控制代碼相對(duì)多需要自己管理批次和類型大數(shù)據(jù)量批量導(dǎo)出生產(chǎn)環(huán)境我最終選擇的是第三種的組合但把查詢層包了一層pandas而不是完全手動(dòng)fetchmany。原因是pandas的分塊讀取、類型推斷、空值處理已經(jīng)足夠成熟沒(méi)必要重復(fù)造輪子真正需要自己控制的是分塊大小寫入方式文件切分策略這些工程層面的東西。1.3 批量導(dǎo)出到底在批量什么這個(gè)標(biāo)題里的批量其實(shí)包含三個(gè)維度批量導(dǎo)出多張表比如一個(gè)庫(kù)里有訂單表、用戶表、商品表一次任務(wù)全部導(dǎo)出成獨(dú)立文件或者合成一個(gè)多 Sheet 的 Excel。單張表數(shù)據(jù)量過(guò)大批量分塊導(dǎo)出把 500 萬(wàn)行拆成 5 個(gè)文件每個(gè) 100 萬(wàn)行避免觸發(fā) Excel 上限。增量任務(wù)每天跑只導(dǎo)出當(dāng)天新增或變化的數(shù)據(jù)而不是每天全量掃全表。這三個(gè)維度在代碼設(shè)計(jì)上是互相疊加的下面第二、三章會(huì)分別展開(kāi)。2. 核心實(shí)現(xiàn)從數(shù)據(jù)庫(kù)讀取到 Excel 落盤的完整代碼這一章直接給完整的可運(yùn)行代碼并解釋每一處關(guān)鍵設(shè)計(jì)的原因。我會(huì)用虛擬的表結(jié)構(gòu)order_records做例子表里有id、trade_date、user_id、product_name、remark等字段覆蓋了常規(guī)數(shù)字、日期、文本、長(zhǎng)文本幾種典型類型。2.1 數(shù)據(jù)庫(kù)連接用 SQLAlchemy 統(tǒng)一管理第一步是建立數(shù)據(jù)庫(kù)連接。我習(xí)慣用 SQLAlchemy 的create_engine而不是直接用某個(gè)數(shù)據(jù)庫(kù)驅(qū)動(dòng)裸連原因有兩個(gè)換數(shù)據(jù)庫(kù)不用改代碼MySQL、PostgreSQL、SQLite 都能用同一套導(dǎo)出代碼。pandas.read_sql原生支持 SQLAlchemy 引擎參數(shù)占位符也更安全。from sqlalchemy import create_engine def build_engine(db_type: str mysql, host: str 127.0.0.1, port: int 3306, username: str your_user, password: str your_pass, database: str your_db) - object: 構(gòu)建 SQLAlchemy 數(shù)據(jù)庫(kù)引擎。 如果 db_type 是 sqlite只需傳 database 路徑即可。 if db_type sqlite: return create_engine(fsqlite:///{database}, pool_pre_pingTrue) url f{db_type}://{username}:{password}{host}:{port}/{database}?charsetutf8mb4 return create_engine(url, pool_pre_pingTrue, pool_size5, pool_recycle3600)注意charsetutf8mb4這個(gè)對(duì) MySQL 很重要。utf8mb4才能完整支持 emoji 和生僻字如果漏掉數(shù)據(jù)里帶個(gè) emoji 符號(hào)寫到 Excel 里就會(huì)變成亂碼。這是我在真實(shí)項(xiàng)目中踩過(guò)的坑。pool_pre_pingTrue這個(gè)參數(shù)的意思是每次從連接池取連接之前先做一次輕量探測(cè)防止拿到一個(gè)已經(jīng)失效的連接。定時(shí)任務(wù)里數(shù)據(jù)庫(kù)連接經(jīng)常被服務(wù)端主動(dòng)斷開(kāi)加上這個(gè)參數(shù)能省掉很多莫名其妙的MySQL server has gone away報(bào)錯(cuò)。2.2 主函數(shù)按配置批量導(dǎo)出多張表到同一個(gè) Excel這個(gè)設(shè)計(jì)的核心思路是把導(dǎo)出什么表導(dǎo)出哪些列按什么條件過(guò)濾都放到配置字典里代碼本身保持通用。這樣業(yè)務(wù)上新增一張表的導(dǎo)出需求只需要加一段配置不需要改邏輯。import pandas as pd from sqlalchemy import text def export_tables_to_excel(engine, tables_config: list, output_path: str, chunksize: int 50000, sheet_limit: int 500000): tables_config: [ { sheet_name: 訂單表, query: SELECT id, trade_date, user_id, product_name, remark FROM order_records WHERE trade_date :start_date, params: {start_date: 2024-01-01}, order_by: id, }, ... ] writer pd.ExcelWriter(output_path, enginexlsxwriter) for cfg in tables_config: sheet_name cfg[sheet_name][:31] # Excel sheet 名最長(zhǎng) 31 字符 query cfg[query] params cfg.get(params, {}) order_by cfg.get(order_by, ) # 構(gòu)造帶分頁(yè)包裹的查詢后面解釋為什么這么做 paged_query wrap_query_with_row_number(query, order_by) total_exported 0 sheet_row 0 # 當(dāng)前 sheet 已寫入行數(shù) # 把分頁(yè)條件逐段傳入 offset 0 while True: page_query paged_query f LIMIT {chunksize} OFFSET {offset} df pd.read_sql(text(page_query), engine, paramsparams) if df.empty: break # 達(dá)到單個(gè) sheet 的行數(shù)上限換下一個(gè) sheet if sheet_row len(df) sheet_limit: # 先處理不滿一個(gè) sheet 的剩余部分... pass df.to_excel(writer, sheet_namesheet_name, indexFalse, startrowsheet_row 1, header(sheet_row 0)) sheet_row len(df) total_exported len(df) offset chunksize print(f[{sheet_name}] 已導(dǎo)出 {total_exported} 行偏移 {offset}) writer.close() print(f導(dǎo)出完成{output_path})這段代碼里wrap_query_with_row_number是我封裝的一個(gè)分頁(yè)函數(shù)下面單獨(dú)說(shuō)明。先解釋幾個(gè)關(guān)鍵點(diǎn)indexFalse必須寫DataFrame自帶的行索引如果不關(guān)掉會(huì)以多出來(lái)一列的方式寫進(jìn) Excel業(yè)務(wù)方會(huì)看到一列沒(méi)名字的序號(hào)非常不專業(yè)。header(sheet_row 0)控制列名只寫一次分塊導(dǎo)出到同一個(gè) sheet 時(shí)只有第一塊數(shù)據(jù)需要寫列名后面續(xù)寫只需要追加數(shù)據(jù)行。這個(gè)判斷非常關(guān)鍵不寫的話每個(gè)分塊都會(huì)重復(fù)一行字段名。startrowsheet_row 1跳過(guò)列名行第一塊從第 1 行開(kāi)始寫0 是列名行后續(xù)塊從已有數(shù)據(jù)行之后續(xù)寫。2.3 穩(wěn)定分頁(yè)用主鍵排序代替 OFFSETLIMIT ... OFFSET ...在數(shù)據(jù)量小的時(shí)候沒(méi)問(wèn)題但偏移量越大數(shù)據(jù)庫(kù)掃描越慢。更嚴(yán)重的是如果trade_date有重復(fù)值而分頁(yè)排序不穩(wěn)定同一批數(shù)據(jù)可能被重復(fù)導(dǎo)出或漏掉。我在這里用了基于排序鍵的鍵集分頁(yè)穩(wěn)定且性能好def stable_page_query(base_query: str, order_column: str, last_value, page_size: int): if last_value is None: return f{base_query} ORDER BY {order_column} LIMIT {page_size} return f{base_query} AND {order_column} :last_value ORDER BY {order_column} LIMIT {page_size}每次記錄當(dāng)前頁(yè)最后一條order_column的值下一頁(yè)用WHERE order_column last_value取數(shù)據(jù)。這樣即使數(shù)據(jù)在導(dǎo)出過(guò)程中被并發(fā)寫入也不會(huì)出現(xiàn) OFFSET 分頁(yè)那種頁(yè)與頁(yè)之間數(shù)據(jù)錯(cuò)位的問(wèn)題。當(dāng)然前提是order_column有唯一性或者至少業(yè)務(wù)上能接受排序鍵相同的數(shù)據(jù)被一起跨越的邊緣情況。實(shí)際操作中我會(huì)要求配置里必須帶一個(gè)order_by字段并且優(yōu)先用主鍵id作為排序鍵這是最穩(wěn)妥的方案。2.4 數(shù)據(jù)類型預(yù)處理防止寫進(jìn) Excel 就變味兒數(shù)據(jù)庫(kù)里的數(shù)據(jù)類型跟 Excel 單元格格式不是一一對(duì)應(yīng)的下面這幾個(gè)處理必須在寫入前完成def prepare_dataframe(df: pd.DataFrame, datetime_cols: list None, long_text_cols: list None) - pd.DataFrame: datetime_cols: 需要顯式保留日期時(shí)間的列名列表 long_text_cols: 可能超過(guò) 32767 字符的長(zhǎng)文本列 if datetime_cols: for col in datetime_cols: if col in df.columns: df[col] pd.to_datetime(df[col]).dt.strftime(%Y-%m-%d %H:%M:%S) if long_text_cols: for col in long_text_cols: if col in df.columns: df[col] df[col].astype(str).str.slice(0, 32000) # 把 id 這類超長(zhǎng)數(shù)字列顯式轉(zhuǎn)成字符串避免科學(xué)計(jì)數(shù)法 for col in df.select_dtypes(include[int64, float64]).columns: if col.lower().endswith((id, no, num)): df[col] df[col].astype(str) return df這里有三件事值得展開(kāi)日期轉(zhuǎn)字符串pandas 寫 datetime 到 Excel 時(shí)如果 xlsxwriter 的格式化沒(méi)配對(duì)單元格顯示的是一串?dāng)?shù)字Excel 日期本質(zhì)是序列號(hào)。我選擇直接在 SQL 查詢或 pandas 里把日期格式化成標(biāo)準(zhǔn)字符串一勞永逸。超長(zhǎng)文本截?cái)嘟財(cái)嗟?32000 而不是 32767留出一點(diǎn)余量避免多字節(jié)字符在邊界被截?cái)喈a(chǎn)生亂碼。長(zhǎng)數(shù)字轉(zhuǎn)字符串這招防的是手機(jī)號(hào)、雪花 ID 這類超過(guò) 15 位的純數(shù)字如果不轉(zhuǎn)字符串它們?cè)?Excel 里會(huì)變成科學(xué)計(jì)數(shù)法看起來(lái)像4.10217E18精度還丟了。2.5 完整調(diào)用示例把上面的函數(shù)串起來(lái)實(shí)際使用長(zhǎng)這樣if __name__ __main__: engine build_engine(databasebusiness_db) tables [ { sheet_name: 訂單明細(xì), query: SELECT id, trade_date, user_id, product_name, remark FROM order_records WHERE trade_date :start_date , params: {start_date: 2024-01-01}, order_by: id, }, { sheet_name: 用戶信息, query: SELECT id, user_name, register_date, level FROM user_info, params: {}, order_by: id, }, ] export_tables_to_excel( engine, tables_configtables, output_path/data/export/business_2024.xlsx, chunksize50000, sheet_limit400000, )這里我把sheet_limit設(shè)成 40 萬(wàn)而不是 Excel 上限 104 萬(wàn)是有意的。超過(guò) 40 萬(wàn)行的 sheetExcel 打開(kāi)已經(jīng)明顯卡頓與其交給業(yè)務(wù)方一個(gè)能開(kāi)但很慢的大文件不如在導(dǎo)出時(shí)就按更保守的閾值切分。3. 數(shù)據(jù)量特別大時(shí)的優(yōu)化分塊、并發(fā)與內(nèi)存控制當(dāng)單表數(shù)據(jù)達(dá)到千萬(wàn)級(jí)別前面那個(gè)單進(jìn)程、串行分塊的方案還是不夠快。這一章說(shuō)三個(gè)優(yōu)化手段按性價(jià)比排序。3.1 內(nèi)存估算為什么不能一次性全量讀取很多人寫導(dǎo)出腳本的第一版都是pd.read_sql(query, engine)一把梭幾百萬(wàn)行數(shù)據(jù)全讀進(jìn)內(nèi)存。我們來(lái)粗算一下一行訂單記錄假設(shè)有 20 個(gè)字段平均每個(gè)字段算 20 字節(jié)500 萬(wàn)行就是500萬(wàn) × 20 × 20 2億字節(jié)約 200 MB?？粗€好但 pandas 為了類型推斷會(huì)臨時(shí)復(fù)制多份副本實(shí)際峰值可能是這個(gè)數(shù)的 3 到 4 倍。再加上 DataFrame 索引、字符串對(duì)象本身需要的內(nèi)存一個(gè) 500 萬(wàn)行的 DataFrame 吃 1 GB 內(nèi)存很正常。如果服務(wù)器上還跑著其他服務(wù)這基本就是 OOM 的前奏了。所以我堅(jiān)持在任何生產(chǎn)導(dǎo)出腳本里都做分塊讀取既為了控制內(nèi)存也為了能在每塊之間打印進(jìn)度。3.2 分塊大小怎么定分塊大小chunksize是個(gè)需要平衡的參數(shù)。我自己的經(jīng)驗(yàn)公式行平均寬度小比如只有幾個(gè)整數(shù)列可以設(shè) 10 萬(wàn)到 20 萬(wàn)。行平均寬度大有長(zhǎng)文本、JSON、多字符串列建議設(shè) 2 萬(wàn)到 5 萬(wàn)。寧可塊數(shù)多也不要讓單塊內(nèi)存超過(guò) 200 MB。實(shí)際壓測(cè)時(shí)我會(huì)在分塊讀取的循環(huán)里監(jiān)控一下內(nèi)存變化import tracemalloc tracemalloc.start() # 分塊循環(huán)讀取... current, peak tracemalloc.get_traced_memory() print(f當(dāng)前內(nèi)存: {current / 1024 / 1024:.1f} MB, 峰值: {peak / 1024 / 1024:.1f} MB)這里的核心原則是單塊讀完了、寫完了內(nèi)存就該釋放。如果發(fā)現(xiàn)內(nèi)存只增不減優(yōu)先檢查是不是有人把每個(gè)分塊都append到了一個(gè)總 DataFrame 里——這是最常見(jiàn)的隱性內(nèi)存泄漏。3.3 多進(jìn)程并行不同表并行單表按 ID 范圍切分如果你的數(shù)據(jù)庫(kù)服務(wù)器 CPU 核數(shù)多可以并行導(dǎo)出多張獨(dú)立的表。Python 的多線程在 IO 密集型任務(wù)上有用但這里的主要瓶頸是數(shù)據(jù)庫(kù)查詢后的 pandas 內(nèi)存處理屬于 CPU 和內(nèi)存密集型所以用multiprocessing更合適。多進(jìn)程最簡(jiǎn)單的寫法是concurrent.futures.ProcessPoolExecutorfrom concurrent.futures import ProcessPoolExecutor, as_completed def export_one_table(cfg: dict): 導(dǎo)出單張表獨(dú)立進(jìn)程執(zhí)行 engine build_engine() # 每個(gè)進(jìn)程單獨(dú)建連接 return export_tables_to_excel( engine, [cfg], cfg[output_path], chunksizecfg.get(chunksize, 50000), sheet_limitcfg.get(sheet_limit, 400000), ) def parallel_export(all_configs: list): with ProcessPoolExecutor(max_workers4) as pool: futures [pool.submit(export_one_table, cfg) for cfg in all_configs] for future in as_completed(futures): print(future.result())注意每個(gè)子進(jìn)程必須自己創(chuàng)建數(shù)據(jù)庫(kù)連接和ExcelWriter不能把父進(jìn)程的 engine 直接傳進(jìn)去。數(shù)據(jù)庫(kù)連接不能跨進(jìn)程共享這是很多人踩過(guò)坑的地方。如果是單張表特別大多進(jìn)程思路要反過(guò)來(lái)按主鍵id的范圍切成 N 段每個(gè)進(jìn)程導(dǎo)出[id_min, id_max)區(qū)間。切分方式先查一下表的最小和最大主鍵SELECT MIN(id), MAX(id), COUNT(*) FROM order_records;然后根據(jù)總數(shù)和期望的每段行數(shù)算出切割點(diǎn)再按切割點(diǎn)生成各自的WHERE id BETWEEN ... AND ...查詢。這個(gè)方案的好處是天然支持?jǐn)帱c(diǎn)續(xù)傳——某個(gè)區(qū)間跑掛了重跑那個(gè)區(qū)間即可。3.4 寫入引擎的選擇xlsxwriter 的 write_only 模式pandas.DataFrame.to_excel支持傳入不同的 engineopenpyxl和xlsxwriter對(duì)同一份數(shù)據(jù)的寫入性能差距明顯。xlsxwriter有一個(gè) write-only 模式專門為大數(shù)據(jù)量設(shè)計(jì)workbook xlsxwriter.Workbook(output_path, {constant_memory: True}) worksheet workbook.add_worksheet(訂單明細(xì))開(kāi)啟這個(gè)模式后xlsxwriter 不會(huì)在內(nèi)存里維護(hù)完整的行數(shù)據(jù)而是邊寫邊落盤內(nèi)存占用大幅下降。唯一的不方便是不能回改已經(jīng)寫過(guò)的單元格——但導(dǎo)出任務(wù)根本不需要回改所以完全適配。在pandas.ExcelWriter里啟用方式也簡(jiǎn)單writer pd.ExcelWriter(output_path, enginexlsxwriter, engine_kwargs{options: {constant_memory: True}})我之前做過(guò)一次對(duì)比測(cè)試同樣是 80 萬(wàn)行、15 列的數(shù)據(jù)openpyxl寫入耗時(shí)接近 3 分鐘xlsxwriter的 constant_memory 模式只要 40 秒左右。這個(gè)差距在生產(chǎn)任務(wù)里非常可觀。4. 我把這些 Excel 發(fā)給業(yè)務(wù)方后踩過(guò)的那些坑代碼能跑通只是第一步。真實(shí)世界里能把文件生成出來(lái)和業(yè)務(wù)方能正常使用之間隔著好幾個(gè)大坑。這一章記錄我實(shí)際交付過(guò)程中踩過(guò)的坑和對(duì)應(yīng)的處理辦法。4.1 單 Sheet 超過(guò) 104 萬(wàn)行不是報(bào)錯(cuò)但比報(bào)錯(cuò)更麻煩有一次我導(dǎo)出一張日志表數(shù)據(jù)量 130 多萬(wàn)行我滿懷信心地等著文件生成結(jié)果確實(shí)生成了但 Excel 打開(kāi)時(shí)報(bào)文件損壞原因是超過(guò)了單 Sheet 上限。這個(gè)坑最坑的地方在于文件層面已經(jīng)寫壞了但導(dǎo)出腳本沒(méi)有任何異常提示。xlsxwriter 會(huì)把寫到 1048577 行之后的邏輯線強(qiáng)行掐掉但文件打開(kāi)前你永遠(yuǎn)不知道。我的處理辦法是在寫入循環(huán)里做一個(gè)硬檢查MAX_EXCEL_ROWS 1048576 if sheet_row len(df) MAX_EXCEL_ROWS: # 當(dāng)前 sheet 已滿另起一個(gè) sheet 或另起文件 ...更穩(wěn)妥的做法是在方案設(shè)計(jì)階段就按業(yè)務(wù)數(shù)據(jù)的增長(zhǎng)趨勢(shì)預(yù)估未來(lái)的行數(shù)提前把一個(gè)大表的數(shù)據(jù)按時(shí)間維度分文件導(dǎo)出。比如日志表就按天導(dǎo)出一天一個(gè)文件既避免行數(shù)問(wèn)題業(yè)務(wù)方拿數(shù)據(jù)也更方便。4.2 數(shù)字被顯示成科學(xué)計(jì)數(shù)法的三個(gè)來(lái)源這個(gè)問(wèn)題太常見(jiàn)了常見(jiàn)到每個(gè)做導(dǎo)出的人都會(huì)遇到。超長(zhǎng) ID19 位的雪花 IDExcel 顯示成4.10217E18而且后幾位被當(dāng)成 0原始精度丟失。手機(jī)號(hào)11 位數(shù)字理論上不超過(guò) Excel 的 15 位精度限制但很多手機(jī)號(hào)第 11 位附近會(huì)出現(xiàn)奇怪的浮點(diǎn)誤差導(dǎo)出后末尾變成12300這種。帶前導(dǎo)零的編碼比如商品編碼001234Excel 會(huì)自作主張把前導(dǎo)零省略。解決思路分兩層第一層在 SQL 查詢階段就轉(zhuǎn)成字符串。比如 MySQL 用CAST(id AS CHAR)PostgreSQL 用id::text讓數(shù)據(jù)庫(kù)返回字符串而不是數(shù)字pandas 拿到手就是字符串再寫入 Excel 就不會(huì)觸發(fā)科學(xué)計(jì)數(shù)法。第二層如果數(shù)據(jù)已經(jīng)以數(shù)字類型到了 pandas 里就用df[col] df[col].astype(str)轉(zhuǎn)一次。注意此時(shí)如果數(shù)字已經(jīng)是浮點(diǎn)型直接轉(zhuǎn)字符串會(huì)帶.0后綴要先轉(zhuǎn)int64再轉(zhuǎn)str或者用小數(shù)點(diǎn)后 0 位格式化。4.3 日期顯示成數(shù)字或 Unix 時(shí)間戳pandas 寫 datetime 到 xlsxwriter 時(shí)如果不設(shè)置單元格格式日期會(huì)顯示成45087.3456這種序列數(shù)。我一開(kāi)始沒(méi)做特殊處理業(yè)務(wù)方拿到文件后發(fā)給我一堆問(wèn)號(hào)這個(gè)數(shù)字是什么后來(lái)我總結(jié)出一個(gè)最省心的方案所有日期列在寫入前統(tǒng)一格式化成YYYY-MM-DD HH:MM:SS字符串。df[trade_date] pd.to_datetime(df[trade_date]).dt.strftime(%Y-%m-%d %H:%M:%S)字符串日期寫入 Excel 后是左對(duì)齊的文本格式雖然不能直接在 Excel 里做日期運(yùn)算但對(duì)于導(dǎo)出給業(yè)務(wù)方看這個(gè)場(chǎng)景完全夠用。如果有分析師需要日期參與計(jì)算他們自己用分列功能轉(zhuǎn)一下批量也很方便。4.4 亂碼問(wèn)題誰(shuí)都可能遇到但原因各不同亂碼的排查路徑我這幾年走得很熟練了按位置分成三種數(shù)據(jù)庫(kù)讀出來(lái)就是亂碼大概率是連接串沒(méi)加charsetutf8mb4或者表字段本身的字符集不對(duì)。pandas 階段亂碼檢查拿到的 DataFrame 里打印出來(lái)的值是正常的但寫出去是亂碼多半是 ExcelWriter 的編碼設(shè)置被動(dòng)了或者用了 CSV 輸出時(shí)沒(méi)指定utf-8-sig。Excel 打開(kāi)亂碼但代碼輸出正常這個(gè)最常見(jiàn)就是 CSV 文件沒(méi)有 BOM 頭。Excel 默認(rèn)用 GBK 編碼讀 CSV沒(méi)有 BOM 的 UTF-8 文件它識(shí)別不出來(lái)。CSV 導(dǎo)出時(shí)一定要用df.to_csv(path, indexFalse, encodingutf-8-sig)utf-8-sig會(huì)自動(dòng)帶上 BOMExcel 就能正確識(shí)別。4.5 一個(gè)慢得無(wú)法忍受的文件性能瓶頸可能在單元格格式我遇到過(guò)這么一次數(shù)據(jù)量不大才 30 萬(wàn)行但導(dǎo)出耗時(shí)非常長(zhǎng)一看代碼循環(huán)里對(duì)每個(gè)單元格調(diào)用了worksheet.write_number()后面還跟了一堆set_format()。naive 寫法的性能殺手是逐單元格操作 格式對(duì)象綁定。優(yōu)化辦法是能用pandas的批量寫入就不要自己循環(huán)寫單元格。如果要設(shè)置列寬、表頭樣式只對(duì)列名行做格式化數(shù)據(jù)行保持默認(rèn)。表頭樣式可以用worksheet.set_row(0, None, header_format)這種批量方式而不是每個(gè)單元格單獨(dú)設(shè)置。這個(gè)優(yōu)化做完同樣的 30 萬(wàn)行數(shù)據(jù)耗時(shí)從 4 分多鐘降到了 20 秒以內(nèi)效果立竿見(jiàn)影。5. 讓導(dǎo)出任務(wù)變成可運(yùn)維的定時(shí)任務(wù)斷點(diǎn)續(xù)傳與失敗重試一次性的手工導(dǎo)出很簡(jiǎn)單真正的難點(diǎn)在于把這個(gè)腳本變成每天自動(dòng)跑、掛了能恢復(fù)、跑完有日志的生產(chǎn)級(jí)定時(shí)任務(wù)。這一章是我在交付給其他團(tuán)隊(duì)后根據(jù)使用反饋迭代出的工程化改進(jìn)。5.1 斷點(diǎn)續(xù)傳以已導(dǎo)出的最大主鍵為錨點(diǎn)最樸素的斷點(diǎn)續(xù)傳方案是記錄上一次導(dǎo)出的最大主鍵值。下次任務(wù)啟動(dòng)時(shí)SQL 條件從id last_max_id開(kāi)始查而不是從全表開(kāi)頭查。def get_last_checkpoint(state_file: str) - int: try: with open(state_file, r) as f: return int(f.read().strip()) except (FileNotFoundError, ValueError): return 0 def save_checkpoint(state_file: str, last_id: int): with open(state_file, w) as f: f.write(str(last_id))這個(gè)方案有幾個(gè)前提表必須有單調(diào)遞增的主鍵或者至少有一個(gè)遞增的業(yè)務(wù)時(shí)間字段。每次任務(wù)只追加新數(shù)據(jù)不修改歷史數(shù)據(jù)。增量導(dǎo)出的數(shù)據(jù)范圍是連續(xù)的區(qū)間不存在越過(guò)空白段后還有新數(shù)據(jù)的情況。如果表結(jié)構(gòu)不滿足這些前提斷點(diǎn)續(xù)傳的意義就大打折扣這種情況建議改為按時(shí)間窗口全量重導(dǎo)或者把增量標(biāo)志位交給業(yè)務(wù)系統(tǒng)維護(hù)。5.2 失敗重試指數(shù)退避而不是立即硬沖數(shù)據(jù)庫(kù)連接抖動(dòng)、鎖競(jìng)爭(zhēng)、網(wǎng)絡(luò)閃斷這些都是定時(shí)任務(wù)最常遇到的故障類型。如果失敗后立即重試很可能在同一瞬間再次失敗。我習(xí)慣用帶指數(shù)退避的重試邏輯import time def retry_call(func, retries3, base_delay2): for attempt in range(retries): try: return func() except Exception as exc: if attempt retries - 1: raise delay base_delay * (2 ** attempt) print(f第 {attempt 1} 次失敗{delay}s 后重試: {exc}) time.sleep(delay)每次失敗的間隔按 2 秒、4 秒、8 秒指數(shù)增長(zhǎng)給數(shù)據(jù)庫(kù)留出恢復(fù)窗口。同時(shí)記錄最后失敗位置讓下一次任務(wù)可以從斷點(diǎn)繼續(xù)。5.3 日志是導(dǎo)出任務(wù)的靈魂我接手過(guò)別人的導(dǎo)出腳本代碼結(jié)尾只有一行print(done)。出問(wèn)題時(shí)完全不知道數(shù)據(jù)導(dǎo)到哪一步了、出了多少行、卡在哪個(gè)表上。我的日志規(guī)范很簡(jiǎn)單每個(gè)表開(kāi)始導(dǎo)出前記錄表名、預(yù)計(jì)數(shù)據(jù)量、開(kāi)始時(shí)間。每個(gè)分塊導(dǎo)出后記錄已導(dǎo)出行數(shù)、耗時(shí)、當(dāng)前主鍵邊界。任務(wù)結(jié)束或異常退出統(tǒng)一寫一個(gè) summary每個(gè)表導(dǎo)出了多少行、總耗時(shí)、是否有跳過(guò)和重試。import logging import time logging.basicConfig( levellogging.INFO, format%(asctime)s [%(levelname)s] %(message)s, handlers[logging.FileHandler(/data/export/export.log)] ) logger logging.getLogger(exporter) def export_with_log(engine, cfg): start time.time() total 0 logger.info(f開(kāi)始導(dǎo)出表 {cfg[sheet_name]}) # ... 分塊導(dǎo)出循環(huán) ... logger.info(f表 {cfg[sheet_name]} 完成共 {total} 行耗時(shí) {time.time() - start:.2f}s)有了這個(gè)日志任何一次任務(wù)異常都能快速定位是數(shù)據(jù)庫(kù)查詢太慢、某一行的數(shù)據(jù)格式有問(wèn)題、還是寫 Excel 時(shí)文件被占用。日志不求花哨但必須能讓接手的同事在 5 分鐘內(nèi)定位到問(wèn)題。5.4 定時(shí)調(diào)度的落地姿勢(shì)我用 cron 表達(dá)式做調(diào)度核心命令很簡(jiǎn)單0 2 * * * /usr/bin/python3 /opt/scripts/exporter.py /data/export/cron.log 21凌晨?jī)牲c(diǎn)跑業(yè)務(wù)低谷數(shù)據(jù)庫(kù)壓力小文件生成后也不影響上班時(shí)間取數(shù)。這里有個(gè)細(xì)節(jié)不要直接用python exporter.py而是寫一個(gè)啟動(dòng)腳本先激活虛擬環(huán)境再跑。項(xiàng)目依賴如果裝在全局環(huán)境某天其他人動(dòng)了依賴版本整個(gè)定時(shí)任務(wù)就無(wú)聲無(wú)息地掛了。我之前為此定了一條規(guī)矩任何導(dǎo)出任務(wù)的啟動(dòng)命令都固定為cd /opt/scripts source .venv/bin/activate python exporter.pycron 里寫全路徑不要依賴默認(rèn)環(huán)境的任何東西。定時(shí)任務(wù)跑成功后還可以加一個(gè)可選的發(fā)送通知環(huán)節(jié)把導(dǎo)出的文件上傳到公司的文件平臺(tái)或?qū)ο蟠鎯?chǔ)然后把下載鏈接發(fā)給業(yè)務(wù)群。這一步看團(tuán)隊(duì)基礎(chǔ)設(shè)施有空實(shí)現(xiàn)一下價(jià)值很大因?yàn)闃I(yè)務(wù)方不用每次找運(yùn)維或者開(kāi)發(fā)要文件了。6. 一些值得再摳一摳的細(xì)節(jié)格式美化與文件命名導(dǎo)出任務(wù)做到這里已經(jīng)是可用的生產(chǎn)工具了但要讓業(yè)務(wù)方用得舒服還有幾個(gè)小細(xì)節(jié)值得花時(shí)間優(yōu)化。6.1 列寬自適應(yīng)別讓業(yè)務(wù)方每次手動(dòng)拉列寬xlsxwriter 里可以給 Sheet 設(shè)置默認(rèn)列寬或者針對(duì)特定列設(shè)置更寬的值worksheet.set_column(A:A, 12) # ID 列 worksheet.set_column(B:B, 20) # 日期列 worksheet.set_column(C:C, 30) # 名稱列如果列很多不想一個(gè)個(gè)指定可以按列名的最大字符長(zhǎng)度估算for i, col in enumerate(df.columns): max_len max(df[col].astype(str).str.len().max(), len(col)) 2 worksheet.set_column(i, i, min(max_len, 50))這個(gè)操作對(duì)使用者體驗(yàn)提升非常明顯值得做。6.2 表頭加粗與凍結(jié)首行凍結(jié)首行這個(gè)功能幾乎是業(yè)務(wù)方最喜歡的一個(gè)。表數(shù)據(jù)一多往下拉的時(shí)候完全不知道每一列是什么凍結(jié)首行后這個(gè)痛點(diǎn)直接消失。header_format workbook.add_format({bold: True, bg_color: #D9E1F2, border: 1}) worksheet.set_row(0, None, header_format) worksheet.freeze_panes(1, 0)一次設(shè)置永久生效整個(gè)文件的專業(yè)度立刻上一個(gè)檔次。6.3 文件命名規(guī)則時(shí)間戳 業(yè)務(wù)標(biāo)識(shí)文件命名看起來(lái)小但影響的是整個(gè)交付鏈路。我經(jīng)?？吹接腥藢?dǎo)出文件叫export.xlsx結(jié)果第二天再導(dǎo)出就把昨天的文件覆蓋了或者業(yè)務(wù)方同時(shí)拿到 5 個(gè)export.xlsx根本分不清哪個(gè)是哪個(gè)。我的命名規(guī)范是業(yè)務(wù)日期_數(shù)據(jù)范圍_生成時(shí)間戳.xlsx比如order_records_20240101_20240630_20240701020000.xlsx保證文件名里能看出數(shù)據(jù)的時(shí)間范圍、生成的時(shí)間點(diǎn)且不會(huì)互相覆蓋。如果是定時(shí)增量任務(wù)這個(gè)習(xí)慣能幫你少背很多鍋。6.4 空數(shù)據(jù)表的處理寧可給空模板也別少一個(gè) Sheet業(yè)務(wù)方用 Excel 最怕的情況是今天導(dǎo)出數(shù)據(jù)恰好是 0 行腳本直接跳過(guò)了這個(gè) sheet業(yè)務(wù)方以為程序出 bug 了實(shí)際上只是沒(méi)數(shù)據(jù)。我的處理邏輯是如果查詢結(jié)果為空也創(chuàng)建 Sheet只寫入列名和一行無(wú)數(shù)據(jù)說(shuō)明。這樣業(yè)務(wù)方一眼就明白這個(gè)時(shí)間段確實(shí)沒(méi)有數(shù)據(jù)而不是工具壞了。if df.empty: empty_df pd.DataFrame(columnscfg[columns]) empty_df.to_excel(writer, sheet_namesheet_name, indexFalse)小小的一個(gè)堅(jiān)持省掉了很多不必要的需求溝通。7. 從能跑到好用的個(gè)人經(jīng)驗(yàn)總結(jié)最后說(shuō)說(shuō)我做完這個(gè)導(dǎo)出工具后的幾點(diǎn)體會(huì)。導(dǎo)出這個(gè)功能看起來(lái)是人人都能做的最小功能但真正能穩(wěn)定服務(wù)業(yè)務(wù)的導(dǎo)出工具一定不是pd.read_sql to_excel幾行代碼就算完成的。數(shù)據(jù)量邊界、格式兼容性、異?；謴?fù)、可觀測(cè)性、使用體驗(yàn)缺一不可。尤其是在數(shù)據(jù)量大、任務(wù)頻繁的場(chǎng)景下一個(gè)沒(méi)有分塊、沒(méi)有日志、沒(méi)有斷點(diǎn)的導(dǎo)出腳本遲早會(huì)變成半夜的告警電話來(lái)源。我自己的習(xí)慣是任何導(dǎo)出任務(wù)都先問(wèn)三個(gè)問(wèn)題——數(shù)據(jù)量級(jí)是多少多久跑一次誰(shuí)在用這個(gè)文件數(shù)據(jù)量決定要不要分塊頻率決定要不要斷點(diǎn)續(xù)傳和定時(shí)調(diào)度使用者的技術(shù)水平?jīng)Q定我要不要在格式上多花心思。這三個(gè)問(wèn)題問(wèn)清楚了方案就自然浮出水面。如果看完這篇你還是只想抄一個(gè)最小可用的版本那至少記得導(dǎo)出前先做數(shù)據(jù)類型預(yù)處理導(dǎo)出時(shí)分塊控制內(nèi)存導(dǎo)出后確認(rèn) sheet 行數(shù)沒(méi)超上限。這三件事做到位你的導(dǎo)出腳本就能超過(guò)大多數(shù)臨時(shí)腳本成為別人愿意持續(xù)使用的工具。