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