1. 为什么我劝你别再手动复制粘贴Excel转SQLite的真实需求场景做数据分析或者开发的人几乎都遇到过这种尴尬手头有一份几百兆的Excel里面密密麻麻存着业务数据。你要做按条件筛选、跨表关联、增量更新Excel直接卡到风扇狂转VLOOKUP拉一次恨不得等半分钟。更麻烦的是Excel的公式和格式混在一起一旦别人在中间插了几行你的筛选结果就莫名其妙错掉。这时候把Excel转换成SQLite数据库是我个人认为最省心的解法。SQLite是什么说白了就是一个单文件的轻量级关系型数据库。它不像MySQL、PostgreSQL那样要装服务端、配账号、管端口就是一个文件你这个程序读写它那个程序也能读跨平台还不用折腾。对个人项目、本地分析、小型业务系统来说它几乎是为Excel数据二次利用量身定做的。那为什么用Python来做这个转换说实话如果你只需要转换一次用现成的GUI工具比如SQLite Expert或者DB Browser for SQLite直接导入Excel也能办到。但现实情况往往是这批Excel每周更新一次数据结构还会微调或者你有十几个Excel文件每个文件的sheet结构都不一样再或者你需要在转换过程中顺手清洗掉空行、去重、改字段名。这些需求一旦出现手动导入就变成了重复劳动。用Python写个脚本一次搞定以后更新数据直接跑一遍这才是正经路子。这篇文章就是给那些手里有Excel数据、想把它们变成数据库的同学准备的。不需要你有多深的编程底子懂基本的Python语法就行。我会把从读取Excel到建库建表、写入数据、验证结果的完整流程拆开讲连我自己踩过的坑也一并交代清楚。2. 选对工具组合pandas负责读取sqlite3负责落地2.1 只用标准库行不行答案是能但不痛快如果你只想用Python标准库不装任何第三方包理论上也能实现Excel转SQLite——前提是你的Excel是2007年之前的老格式.xls并且你愿意自己去解析二进制流。这事我年轻时候干过为了搞清楚BIFF记录格式翻了半天文档最后发现纯属浪费时间。更实际的做法是直接上pandas。pandas的read_excel()函数背后依赖的是openpyxl处理.xlsx或xlrd处理.xls它能把Excel的每一行数据读成DataFrame这个结构天生就适合映射成数据库表。反过来写数据pandas有to_sql()方法配合sqlite3模块自带的连接对象几十行代码就能把活干完。你说为什么不让openpyxl直接写SQLite当然也可以但openpyxl拿到的是一个一个的单元格对象你得自己遍历行、自己处理合并单元格、自己管数据类型非常琐碎。用pandas的好处在于它帮你把Excel行和结构化表格之间的桥搭好了你只需要关心数据本身。2.2 版本和依赖的兼容性坑我建议直接Python 3.8以上版本pandas装最新稳定版就行。但这里有一个经常有人踩的坑xlrd这个库从2.0版本开始只支持.xls老格式不再支持.xlsx了。如果你的Excel是.xlsx后缀你得装openpyxl并确保它被pandas正常调用。实操中你可能会遇到ImportError或者Missing optional dependency的报错多半就是依赖没装全。保险起见直接执行pip install pandas openpyxl sqlite3注意sqlite3是Python标准库自带的不需要单独pip安装但如果你安装的是某个精简版Python发行版比如某些嵌入式环境不排除它被裁剪掉的可能。装完后跑一句python -c import sqlite3; print(sqlite3.sqlite_version)能打印出版本号就说明环境没问题。2.3 为什么推荐用绝对路径而不是相对路径这个看起来是小事但对后面的脚本可维护性影响很大。很多人写pd.read_excel(data.xlsx)脚本放在项目根目录下能跑换个目录就报FileNotFoundError。我个人的习惯是import os BASE_DIR os.path.dirname(os.path.abspath(__file__)) EXCEL_PATH os.path.join(BASE_DIR, data, sales_2024.xlsx) DB_PATH os.path.join(BASE_DIR, data, sales.db)这样无论脚本被哪个目录的入口调用路径都不会飘。尤其在自动化任务里你很可能排一个定时任务去跑这个脚本工作目录未必是你写脚本时那个目录相对路径会坑得你怀疑人生。3. 核心转换流程从Excel文件到可查询的数据库表3.1 读取Excel时先看清sheet的脾气Excel文件里经常不止一个sheet。有些人把所有数据放一个sheet里倒也省事但更多时候第一个sheet叫汇总第二个叫明细第三个叫备注。你转换之前得先确认要处理哪些sheet。用pandas可以轻松查看所有sheet名称import pandas as pd xls pd.ExcelFile(EXCEL_PATH) print(xls.sheet_names)拿到sheet名之后最常见的选择是全部转换每个sheet变成一张表。但也有人只想要明细那个sheet。这两种需求写法不一样# 方案一只读指定sheet df pd.read_excel(EXCEL_PATH, sheet_name明细, header0) # 方案二循环读取全部sheet用字典保存 dfs pd.read_excel(EXCEL_PATH, sheet_nameNone, header0) # dfs是一个字典键是sheet名值是DataFramesheet_nameNone这个参数值得记一下它一次性把整个工作簿读进来省了多次调用IO的开销。这里还要留意一个细节Excel表格的第一行并不一定就是列名。有些人会在上面放一个大标题第二行才是字段名。这种情况下如果你不指定header1pandas就会把大标题当作第一列列名读完的数据结构整个是扭曲的。我的习惯是拿到文件先df.head()看一眼确认列名对不对再进行下一步。3.2 让DataFrame先过一遍体检读进来的DataFrame很少是干净的。空行、重复行、全Null列、类型错乱这些都是Excel数据里见怪不怪的毛病。如果直接灌进SQLite后面查询出来的结果会带你走向错误的结论。我建议在写入之前做一轮清洗几行代码的事# 删除全空行 df.dropna(howall, inplaceTrue) # 删除完全重复的行 df.drop_duplicates(inplaceTrue) # 删除全空列比如Excel里那些残留的格式列 df.dropna(axis1, howall, inplaceTrue) # 重置索引避免后面遍历时出现奇怪的index df.reset_index(dropTrue, inplaceTrue)要注意的是drop_duplicates()默认是对所有列完全相同才去重。如果你的数据有自增ID列而ID列的每个值都不同那么即便其他所有字段都一样完全重复这个条件也不成立去重不会生效。这种场景下你需要指定按哪些列去重df.drop_duplicates(subset[订单号, 客户名称], keepfirst, inplaceTrue)3.3 sqlite3连接对象与pandas的配合方式pandas的to_sql()需要一个SQLAlchemy引擎或者一个sqlite3连接对象。用sqlite3连接对象更轻不用额外装SQLAlchemy。我常用的写法是这样import sqlite3 conn sqlite3.connect(DB_PATH) cur conn.cursor() # 让pandas把DataFrame写入SQLite df.to_sql(sales_detail, conn, if_existsreplace, indexFalse) conn.commit() conn.close()这段代码里有两个参数需要解释清楚。if_existsreplace表示如果表已经存在先DROP掉再重建。优点是每次转换都是全套新数据干净利落缺点是你不能拿它做增量更新——一旦跑了就覆盖。如果你想要表存在就追加应该用if_existsappend。还有一种更精细的需求保留原表但不要产生重复数据那你得先查一下目标库里最大的ID再筛选新增部分写入。别指望一行代码搞定这种逻辑必须拆开写。indexFalse是必须的。如果忘了指定pandas会把DataFrame的行号作为一列写进表里名叫index。大部分场景这列是冗余的白白占空间。3.4 完整的最小可用脚本我把上面几步拼成一个可以直接跑的脚本供你测试import os import sqlite3 import pandas as pd BASE_DIR os.path.dirname(os.path.abspath(__file__)) EXCEL_PATH os.path.join(BASE_DIR, sales_2024.xlsx) DB_PATH os.path.join(BASE_DIR, sales.db) xls pd.ExcelFile(EXCEL_PATH) print(检测到sheet, xls.sheet_names) conn sqlite3.connect(DB_PATH) for sheet_name in xls.sheet_names: df pd.read_excel(xls, sheet_namesheet_name, header0) df.dropna(howall, inplaceTrue) df.drop_duplicates(inplaceTrue) # 把sheet名里的空格换成下划线避免SQLite里的名称歧义 table_name sheet_name.strip().replace( , _) df.to_sql(table_name, conn, if_existsreplace, indexFalse) print(f表 {table_name} 写入完成共 {len(df)} 行) conn.commit() conn.close() print(全部转换完成)这个脚本就是我日常工作的起点。在此基础上不管加多少逻辑核心骨架没变过。你能看到真正的核心代码其实没几行难点全在数据清洗和后期验证上。4. 类型映射与字段细节别让数字、日期、文本在SQLite里变形4.1 Excel文本到SQLite存储类是如何对应起来的SQLite有个特性跟MySQL这种强类型数据库不太一样它内部只有五种存储类NULL、INTEGER、REAL、TEXT、BLOB。也就是说你的日期、布尔值、金额最终都会被归类到这几个大类里。这就意味着Excel里一个看起来是日期的单元格在写入时可能变成文本也可能变成数字取决于pandas判断的类型。我在实际转换中总结了一个对应关系给你参考Excel数据类型pandas读取后的dtypeSQLite存储类备注文本objectTEXT最常见注意编码整数int64INTEGER安全小数float64REAL精度可能损失日期datetime64[ns]TEXTISO格式需要格式化百分数float64REAL注意已乘100布尔boolINTEGER0/1SQLite无独立bool公式计算结果视结果而定视结果而定读取的是缓存值这张表看起来简单真正操作时会遇到几个让人头大的情况我一个个说。4.2 日期字段最常见的变成数字灾难Excel的日期本质上是一个序列号比如1900年1月1日是12024年1月1日大约等于45292。当你用pandas读取时如果你的单元格格式不是明确的日期格式而是常规或文本那读出来的就是数字根本不是datetime对象。更坑的是这个数字你还不能直接按时间戳解析因为Excel的日期起点跟Unix时间戳不一样转换逻辑完全不同。怎么判断自己中没中招很简单打印df.dtypes如果某列显示float64或int64但里面装的全是44000多这种数字那基本可以断定是日期被读歪了。处理办法有两种第一种在read_excel时指定列类型df pd.read_excel(EXCEL_PATH, sheet_name明细, dtype{下单日期: str}) df[下单日期] pd.to_datetime(df[下单日期], errorscoerce)但这个方法只对本来就该是文本的日期列有效。如果源文件里真的是Excel序列号数字你得用pandas的to_datetime(unitD, origin1899-12-30)来转换# 处理Excel序列号日期1900-01-01对应数字1所以origin是1899-12-30 df[下单日期] pd.to_datetime(df[下单日期], unitD, origin1899-12-30)那个origin1899-12-30是Excel日期系统的历史遗留记住这个值就行不用问为什么问了就是Excel早期兼容Lotus 1-2-3的Bug流传下来的。把日期转成datetime之后写入SQLite时pandas默认会把它存成TEXT类型格式是YYYY-MM-DD HH:MM:SS。这么存有个好处排序和范围查询依然可用因为字符串比较的字典序跟时间顺序一致。但要提醒你如果你介意存储空间希望存成真正的INTEGER时间戳需要先把datetime转成整型再写df[下单日期_ts] df[下单日期].astype(int64) // 10**9除非你对空间极敏感否则我推荐存成文本ISO格式人眼可读、可调试查询效率也不差。4.3 百分数和浮点数精度金融数据要格外小心Excel里你看到的是12.50%但单元格底层的数值其实是0.125。pandas读出来会是0.125。如果你不做任何处理直接写进SQLite那么之后查询得到的是0.125不是12.5%也不是12.50%。这事在报表里尤其容易出问题——很不直观。当然也有反过来的情况Excel单元格格式显示12.50%但你在单元格里实际输的是12.5那底层数值就是12.5pandas读出来也是12.5。这说明什么说明源文件的格式并不决定底层准确值你得确认数据本身是什么。我的建议是对百分数字段要么转化成小数统一存储并在表名或注释里写明该列存的是小数比例要么就乘以100存成百分比数值查询时用printf(%.2f%%, col)格式化。不要两种混用你自己两周后回来看都会混乱。另外浮点数的精度问题在金额上要非常小心。Excel里很多金额看着是两位小数读出来可能是100.19999999999999。写进SQLite的REAL字段再经过SUM聚合误差会在几分钱上累积。这一步如果是统计金额我强烈建议先转成整数分或者用Decimal类型# 金额单位统一转成分避免浮点误差 df[amount_cents] (df[amount] * 100).round().astype(int64)数据库存整数分查询的时候再除以100。这一招看着土但在涉及钱的业务里是标准做法。5. 大数据量Excel的转换瓶颈分块读取和批量插入的取舍5.1 pandas读大Excel会内存爆炸这是真的有人拿来一个500MB的Excel文件直接pd.read_excel()然后看到内存条哀嚎了半天电脑卡成了幻灯片最后还报了MemoryError。老实说Excel这种格式本身就极不适合承载大数据单个sheet行数上限是1048576所以光从行数看Excel撑死一百万行。但一百万行不代表就安全——列数多、单元格内容长、各种格式对象叠加读进内存后膨胀10倍都不奇怪。pandas读取大文件时整个DataFrame会常驻内存。你在转换过程中如果还做各种清洗操作中间副本一多内存峰值就上去了。面对这种场景你至少有两个思路思路一用read_excel的usecols参数只读需要的列减少不必要的数据进入内存。df pd.read_excel(EXCEL_PATH, sheet_name明细, usecolsA,C:F,H)思路二分批读取Excel行。pandas的read_excel本身没有chunksize参数这点跟read_csv不一样。但你可以结合skiprows和nrows来分页读取只是需要自己控制滑动窗口chunk_size 50000 total_rows 1000000 for start in range(0, total_rows, chunk_size): df_chunk pd.read_excel( EXCEL_PATH, sheet_name明细, header0, skiprowsrange(1, start 1), # 跳过已经读过的行 nrowschunk_size ) df_chunk.to_sql(sales_detail, conn, if_existsappend, indexFalse)注意skiprowsrange(1, start1)意味着第一行列名始终保留后面的行跳过。这个滑动窗口的速度不尽如人意因为read_excel每次都要从头解析文件到skiprows指定的位置但至少它能保护内存。5.2 用executemany代替逐行INSERT速度天差地别如果你的数据源不是Excel而是JSON、CSV或者API接口你可以完全绕开pandas手动写SQL批量插入。即便你还在用pandas写to_sql其实底层也是批量执行性能尚可。但如果你非要自己写插入逻辑请务必用executemany而不是逐行execute。举个例子一万行数据逐行插入SQLite耗时大约在2到4秒。用executemany批量插入同一批数据耗时能降到0.2秒以内。差距20倍一点都不夸张。原因嘛单个INSERT是一条单独的事务每次都走一遍完整的事务提交和磁盘写回流程批量插入则可以把所有语句放在一个事务里极大减少致命IO次数。data_list df.to_records(indexFalse).tolist() placeholders ,.join([?] * len(df.columns)) col_names ,.join(df.columns) sql fINSERT INTO sales_detail ({col_names}) VALUES ({placeholders}) conn.executemany(sql, data_list)这里有个细节为了进一步提升性能可以在插入之前显式开启一个事务插完再统一提交conn.execute(BEGIN TRANSACTION) conn.executemany(sql, data_list) conn.commit()用to_sql的时候如果你发现大量行写入时速度很慢也可以考虑这个技巧——pandas默认的行为未必开了批量事务。5.3 实测数据一百万行Excel到SQLite的耗时参考为了让你心里有个底我给个实测参考数据普通配置的笔记本i5处理器16G内存。测试文件是50MB的单sheet Excel行数43万列数12列。方案耗时备注直接read_excel to_sql约18秒内存峰值约3.2GB分块读取50kto_sql append约45秒内存峰值约1.1GBread_excelexecutemany手动插入约12秒内存峰值约3.2GB看到没分块读取虽然慢了一倍有余但内存占用降了三分之二。到底是追求速度还是保住内存取决于你的机器有多扛造。如果你只是处理几万行的小文件不用纠结直接一把梭就行。6. 转换完成后的校验查询、备份与常见意外情况排查6.1 别急着删Excel先做三层数据核对脚本跑完了SQLite文件也生成了但你真的确信转换过程没问题吗数据从Excel搬到SQLite期间类型变形、行列错位、字符截断都是隐形的。我给自己定的规矩是三重校验第一重总行数核对对比Excel中每个sheet的数据行数注意别把表头算进去和SQLite对应表的COUNT(*)。df_check pd.read_excel(EXCEL_PATH, sheet_name明细, header0) excel_rows len(df_check) cur.execute(SELECT COUNT(*) FROM sales_detail) db_rows cur.fetchone()[0] assert excel_rows db_rows, f行数不一致Excel {excel_rows}DB {db_rows}第二重抽样值核对随机挑几行Excel里的原始数据跟SQLite里查出来的对比。这个动作看起来原始但能抓住很多莫名其妙的字符丢失问题。比如Excel里某个单元格是SKU-001转换后变成了SKU-1这种教训我是实实在在踩过的。第三重聚合值核对对关键数值列分别做SUMExcel侧用pandas的sum()SQLite侧用SQL的SUM()两边对账。如果差哪怕一分钱说明类型转换或精度处理出了问题。6.2 字符编码和诡异字符的处理大多数人第一次用pd.read_excel的时候不会遇到乱码问题因为.xlsx内部就是XML编码的UTF-8pandas处理得很干净。真正的问题出在原始数据本身Excel里可能混入了换行符、制表符、甚至全角空格。这些字符写入SQLite后不影响肉眼查看但在做条件匹配时会让你烦恼。比如一个客户名称是张三 结尾带了个全角空格你在查询时写WHERE name 张三查不出来。因为你肉眼看不见那个空格。解决办法是在写入前做字符清理df[客户名称] df[客户名称].astype(str).str.replace(\u3000, ).str.strip()如果文本里还混入了故意换行你可以选择保留可能是有意为之的多行文本也可以选择替换成空格df[备注] df[备注].astype(str).str.replace(\n, , regexFalse)6.3 表结构变化导致的历史数据兼容问题还有种常见场景是你以为Excel结构没变跑完脚本丢那不管了。三周后同事重新发来一份Excel说加了两个字段。你用if_existsreplace直接覆盖了表结果原来跑得好好的报表SQL全部报错因为列名对不上了。我的建议是每次转换前先把目标库的已有表结构打出来跟新数据的字段列表做一个diff。如果字段有变化自动把旧表备份成表名_backup_YYYYMMDD再建新表。这个逻辑也就十几行代码但它能防止你在生产环境里把自己坑到脚踝import datetime cur.execute(fPRAGMA table_info({table_name})) existing_cols [row[1] for row in cur.fetchall()] new_cols list(df.columns) if set(existing_cols) ! set(new_cols): backup_name f{table_name}_backup_{datetime.datetime.now():%Y%m%d} cur.execute(fALTER TABLE {table_name} RENAME TO {backup_name}) print(f表结构变化已将旧表备份为 {backup_name})6.4 终极防呆SQLite文件的VACUUM压缩写入时频繁增加、删除数据后SQLite文件内部会产生大量碎片文件体积比实际数据大不少。转换完成后执行一次VACUUM可以压缩文件把未使用的空闲页清掉。conn.execute(VACUUM)注意VACUUM不能在事务执行过程中调用所以一定要在commit之后单独执行。对大文件来说VACUUM可能要跑个几秒钟但换来的是更小的文件体积和更快的后续查询体验这笔买卖划算。7. 从转换工具到数据工作流定时增量更新的进阶思路单纯把Excel转成SQLite其实只是数据工作流的第一个环节。一旦你尝到了SQLite的甜头接下来一定会遇到增量更新的需求——Excel每周更新数据库不能每次都整体覆盖因为可能已经有其他程序在引用老数据了。我先给你一个经验判断标准如果你的Excel每次都是全量快照那就继续用replace简单粗暴最可靠如果你的Excel是追加式日志比如每天都有新订单追加到末尾那你要做的是先把新Excel里的重复部分过滤掉再append进已有表。具体过滤逻辑可以这样写# 找出库里已有最大订单号 cur.execute(SELECT MAX(订单号) FROM sales_detail) max_id cur.fetchone()[0] # 只插入大于最大订单号的数据 new_data df[df[订单号] max_id] new_data.to_sql(sales_detail, conn, if_existsappend, indexFalse)这招只有在订单号是单调递增时才能用。如果数据没有天然的自增业务主键你就得用多列联合去重来筛。我见过不少同事在Excel转SQLite这件事上绕了很多弯路。有人把Excel里的数据一张张截图手动录入到数据库管理工具里录了一晚上。有人用VBA去写数据库结果Office版本一换宏跑不了。回过头来看用Python的pandas加sqlite3组合可能是学习成本最低、结果最好维护的路径。有机会的话你可以把这个转换脚本封装成带命令行参数的版本例如传入文件路径和库路径这样以后不管手动执行还是被其他流程调度都很方便。你甚至可以用schedule库挂一个每天自动执行的定时任务把最新Excel转进SQLite。到那时候你就不是在转换数据了而是搭了一条小型数据流水线。我自己就是从这么一个小脚本起步把几份手工整理的Excel慢慢变成了好几个人都在用的数据服务收益非常大。
1. 为什么我劝你别再手动复制粘贴Excel转SQLite的真实需求场景做数据分析或者开发的人几乎都遇到过这种尴尬手头有一份几百兆的Excel里面密密麻麻存着业务数据。你要做按条件筛选、跨表关联、增量更新Excel直接卡到风扇狂转VLOOKUP拉一次恨不得等半分钟。更麻烦的是Excel的公式和格式混在一起一旦别人在中间插了几行你的筛选结果就莫名其妙错掉。这时候把Excel转换成SQLite数据库是我个人认为最省心的解法。SQLite是什么说白了就是一个单文件的轻量级关系型数据库。它不像MySQL、PostgreSQL那样要装服务端、配账号、管端口就是一个文件你这个程序读写它那个程序也能读跨平台还不用折腾。对个人项目、本地分析、小型业务系统来说它几乎是为Excel数据二次利用量身定做的。那为什么用Python来做这个转换说实话如果你只需要转换一次用现成的GUI工具比如SQLite Expert或者DB Browser for SQLite直接导入Excel也能办到。但现实情况往往是这批Excel每周更新一次数据结构还会微调或者你有十几个Excel文件每个文件的sheet结构都不一样再或者你需要在转换过程中顺手清洗掉空行、去重、改字段名。这些需求一旦出现手动导入就变成了重复劳动。用Python写个脚本一次搞定以后更新数据直接跑一遍这才是正经路子。这篇文章就是给那些手里有Excel数据、想把它们变成数据库的同学准备的。不需要你有多深的编程底子懂基本的Python语法就行。我会把从读取Excel到建库建表、写入数据、验证结果的完整流程拆开讲连我自己踩过的坑也一并交代清楚。2. 选对工具组合pandas负责读取sqlite3负责落地2.1 只用标准库行不行答案是能但不痛快如果你只想用Python标准库不装任何第三方包理论上也能实现Excel转SQLite——前提是你的Excel是2007年之前的老格式.xls并且你愿意自己去解析二进制流。这事我年轻时候干过为了搞清楚BIFF记录格式翻了半天文档最后发现纯属浪费时间。更实际的做法是直接上pandas。pandas的read_excel()函数背后依赖的是openpyxl处理.xlsx或xlrd处理.xls它能把Excel的每一行数据读成DataFrame这个结构天生就适合映射成数据库表。反过来写数据pandas有to_sql()方法配合sqlite3模块自带的连接对象几十行代码就能把活干完。你说为什么不让openpyxl直接写SQLite当然也可以但openpyxl拿到的是一个一个的单元格对象你得自己遍历行、自己处理合并单元格、自己管数据类型非常琐碎。用pandas的好处在于它帮你把Excel行和结构化表格之间的桥搭好了你只需要关心数据本身。2.2 版本和依赖的兼容性坑我建议直接Python 3.8以上版本pandas装最新稳定版就行。但这里有一个经常有人踩的坑xlrd这个库从2.0版本开始只支持.xls老格式不再支持.xlsx了。如果你的Excel是.xlsx后缀你得装openpyxl并确保它被pandas正常调用。实操中你可能会遇到ImportError或者Missing optional dependency的报错多半就是依赖没装全。保险起见直接执行pip install pandas openpyxl sqlite3注意sqlite3是Python标准库自带的不需要单独pip安装但如果你安装的是某个精简版Python发行版比如某些嵌入式环境不排除它被裁剪掉的可能。装完后跑一句python -c import sqlite3; print(sqlite3.sqlite_version)能打印出版本号就说明环境没问题。2.3 为什么推荐用绝对路径而不是相对路径这个看起来是小事但对后面的脚本可维护性影响很大。很多人写pd.read_excel(data.xlsx)脚本放在项目根目录下能跑换个目录就报FileNotFoundError。我个人的习惯是import os BASE_DIR os.path.dirname(os.path.abspath(__file__)) EXCEL_PATH os.path.join(BASE_DIR, data, sales_2024.xlsx) DB_PATH os.path.join(BASE_DIR, data, sales.db)这样无论脚本被哪个目录的入口调用路径都不会飘。尤其在自动化任务里你很可能排一个定时任务去跑这个脚本工作目录未必是你写脚本时那个目录相对路径会坑得你怀疑人生。3. 核心转换流程从Excel文件到可查询的数据库表3.1 读取Excel时先看清sheet的脾气Excel文件里经常不止一个sheet。有些人把所有数据放一个sheet里倒也省事但更多时候第一个sheet叫汇总第二个叫明细第三个叫备注。你转换之前得先确认要处理哪些sheet。用pandas可以轻松查看所有sheet名称import pandas as pd xls pd.ExcelFile(EXCEL_PATH) print(xls.sheet_names)拿到sheet名之后最常见的选择是全部转换每个sheet变成一张表。但也有人只想要明细那个sheet。这两种需求写法不一样# 方案一只读指定sheet df pd.read_excel(EXCEL_PATH, sheet_name明细, header0) # 方案二循环读取全部sheet用字典保存 dfs pd.read_excel(EXCEL_PATH, sheet_nameNone, header0) # dfs是一个字典键是sheet名值是DataFramesheet_nameNone这个参数值得记一下它一次性把整个工作簿读进来省了多次调用IO的开销。这里还要留意一个细节Excel表格的第一行并不一定就是列名。有些人会在上面放一个大标题第二行才是字段名。这种情况下如果你不指定header1pandas就会把大标题当作第一列列名读完的数据结构整个是扭曲的。我的习惯是拿到文件先df.head()看一眼确认列名对不对再进行下一步。3.2 让DataFrame先过一遍体检读进来的DataFrame很少是干净的。空行、重复行、全Null列、类型错乱这些都是Excel数据里见怪不怪的毛病。如果直接灌进SQLite后面查询出来的结果会带你走向错误的结论。我建议在写入之前做一轮清洗几行代码的事# 删除全空行 df.dropna(howall, inplaceTrue) # 删除完全重复的行 df.drop_duplicates(inplaceTrue) # 删除全空列比如Excel里那些残留的格式列 df.dropna(axis1, howall, inplaceTrue) # 重置索引避免后面遍历时出现奇怪的index df.reset_index(dropTrue, inplaceTrue)要注意的是drop_duplicates()默认是对所有列完全相同才去重。如果你的数据有自增ID列而ID列的每个值都不同那么即便其他所有字段都一样完全重复这个条件也不成立去重不会生效。这种场景下你需要指定按哪些列去重df.drop_duplicates(subset[订单号, 客户名称], keepfirst, inplaceTrue)3.3 sqlite3连接对象与pandas的配合方式pandas的to_sql()需要一个SQLAlchemy引擎或者一个sqlite3连接对象。用sqlite3连接对象更轻不用额外装SQLAlchemy。我常用的写法是这样import sqlite3 conn sqlite3.connect(DB_PATH) cur conn.cursor() # 让pandas把DataFrame写入SQLite df.to_sql(sales_detail, conn, if_existsreplace, indexFalse) conn.commit() conn.close()这段代码里有两个参数需要解释清楚。if_existsreplace表示如果表已经存在先DROP掉再重建。优点是每次转换都是全套新数据干净利落缺点是你不能拿它做增量更新——一旦跑了就覆盖。如果你想要表存在就追加应该用if_existsappend。还有一种更精细的需求保留原表但不要产生重复数据那你得先查一下目标库里最大的ID再筛选新增部分写入。别指望一行代码搞定这种逻辑必须拆开写。indexFalse是必须的。如果忘了指定pandas会把DataFrame的行号作为一列写进表里名叫index。大部分场景这列是冗余的白白占空间。3.4 完整的最小可用脚本我把上面几步拼成一个可以直接跑的脚本供你测试import os import sqlite3 import pandas as pd BASE_DIR os.path.dirname(os.path.abspath(__file__)) EXCEL_PATH os.path.join(BASE_DIR, sales_2024.xlsx) DB_PATH os.path.join(BASE_DIR, sales.db) xls pd.ExcelFile(EXCEL_PATH) print(检测到sheet, xls.sheet_names) conn sqlite3.connect(DB_PATH) for sheet_name in xls.sheet_names: df pd.read_excel(xls, sheet_namesheet_name, header0) df.dropna(howall, inplaceTrue) df.drop_duplicates(inplaceTrue) # 把sheet名里的空格换成下划线避免SQLite里的名称歧义 table_name sheet_name.strip().replace( , _) df.to_sql(table_name, conn, if_existsreplace, indexFalse) print(f表 {table_name} 写入完成共 {len(df)} 行) conn.commit() conn.close() print(全部转换完成)这个脚本就是我日常工作的起点。在此基础上不管加多少逻辑核心骨架没变过。你能看到真正的核心代码其实没几行难点全在数据清洗和后期验证上。4. 类型映射与字段细节别让数字、日期、文本在SQLite里变形4.1 Excel文本到SQLite存储类是如何对应起来的SQLite有个特性跟MySQL这种强类型数据库不太一样它内部只有五种存储类NULL、INTEGER、REAL、TEXT、BLOB。也就是说你的日期、布尔值、金额最终都会被归类到这几个大类里。这就意味着Excel里一个看起来是日期的单元格在写入时可能变成文本也可能变成数字取决于pandas判断的类型。我在实际转换中总结了一个对应关系给你参考Excel数据类型pandas读取后的dtypeSQLite存储类备注文本objectTEXT最常见注意编码整数int64INTEGER安全小数float64REAL精度可能损失日期datetime64[ns]TEXTISO格式需要格式化百分数float64REAL注意已乘100布尔boolINTEGER0/1SQLite无独立bool公式计算结果视结果而定视结果而定读取的是缓存值这张表看起来简单真正操作时会遇到几个让人头大的情况我一个个说。4.2 日期字段最常见的变成数字灾难Excel的日期本质上是一个序列号比如1900年1月1日是12024年1月1日大约等于45292。当你用pandas读取时如果你的单元格格式不是明确的日期格式而是常规或文本那读出来的就是数字根本不是datetime对象。更坑的是这个数字你还不能直接按时间戳解析因为Excel的日期起点跟Unix时间戳不一样转换逻辑完全不同。怎么判断自己中没中招很简单打印df.dtypes如果某列显示float64或int64但里面装的全是44000多这种数字那基本可以断定是日期被读歪了。处理办法有两种第一种在read_excel时指定列类型df pd.read_excel(EXCEL_PATH, sheet_name明细, dtype{下单日期: str}) df[下单日期] pd.to_datetime(df[下单日期], errorscoerce)但这个方法只对本来就该是文本的日期列有效。如果源文件里真的是Excel序列号数字你得用pandas的to_datetime(unitD, origin1899-12-30)来转换# 处理Excel序列号日期1900-01-01对应数字1所以origin是1899-12-30 df[下单日期] pd.to_datetime(df[下单日期], unitD, origin1899-12-30)那个origin1899-12-30是Excel日期系统的历史遗留记住这个值就行不用问为什么问了就是Excel早期兼容Lotus 1-2-3的Bug流传下来的。把日期转成datetime之后写入SQLite时pandas默认会把它存成TEXT类型格式是YYYY-MM-DD HH:MM:SS。这么存有个好处排序和范围查询依然可用因为字符串比较的字典序跟时间顺序一致。但要提醒你如果你介意存储空间希望存成真正的INTEGER时间戳需要先把datetime转成整型再写df[下单日期_ts] df[下单日期].astype(int64) // 10**9除非你对空间极敏感否则我推荐存成文本ISO格式人眼可读、可调试查询效率也不差。4.3 百分数和浮点数精度金融数据要格外小心Excel里你看到的是12.50%但单元格底层的数值其实是0.125。pandas读出来会是0.125。如果你不做任何处理直接写进SQLite那么之后查询得到的是0.125不是12.5%也不是12.50%。这事在报表里尤其容易出问题——很不直观。当然也有反过来的情况Excel单元格格式显示12.50%但你在单元格里实际输的是12.5那底层数值就是12.5pandas读出来也是12.5。这说明什么说明源文件的格式并不决定底层准确值你得确认数据本身是什么。我的建议是对百分数字段要么转化成小数统一存储并在表名或注释里写明该列存的是小数比例要么就乘以100存成百分比数值查询时用printf(%.2f%%, col)格式化。不要两种混用你自己两周后回来看都会混乱。另外浮点数的精度问题在金额上要非常小心。Excel里很多金额看着是两位小数读出来可能是100.19999999999999。写进SQLite的REAL字段再经过SUM聚合误差会在几分钱上累积。这一步如果是统计金额我强烈建议先转成整数分或者用Decimal类型# 金额单位统一转成分避免浮点误差 df[amount_cents] (df[amount] * 100).round().astype(int64)数据库存整数分查询的时候再除以100。这一招看着土但在涉及钱的业务里是标准做法。5. 大数据量Excel的转换瓶颈分块读取和批量插入的取舍5.1 pandas读大Excel会内存爆炸这是真的有人拿来一个500MB的Excel文件直接pd.read_excel()然后看到内存条哀嚎了半天电脑卡成了幻灯片最后还报了MemoryError。老实说Excel这种格式本身就极不适合承载大数据单个sheet行数上限是1048576所以光从行数看Excel撑死一百万行。但一百万行不代表就安全——列数多、单元格内容长、各种格式对象叠加读进内存后膨胀10倍都不奇怪。pandas读取大文件时整个DataFrame会常驻内存。你在转换过程中如果还做各种清洗操作中间副本一多内存峰值就上去了。面对这种场景你至少有两个思路思路一用read_excel的usecols参数只读需要的列减少不必要的数据进入内存。df pd.read_excel(EXCEL_PATH, sheet_name明细, usecolsA,C:F,H)思路二分批读取Excel行。pandas的read_excel本身没有chunksize参数这点跟read_csv不一样。但你可以结合skiprows和nrows来分页读取只是需要自己控制滑动窗口chunk_size 50000 total_rows 1000000 for start in range(0, total_rows, chunk_size): df_chunk pd.read_excel( EXCEL_PATH, sheet_name明细, header0, skiprowsrange(1, start 1), # 跳过已经读过的行 nrowschunk_size ) df_chunk.to_sql(sales_detail, conn, if_existsappend, indexFalse)注意skiprowsrange(1, start1)意味着第一行列名始终保留后面的行跳过。这个滑动窗口的速度不尽如人意因为read_excel每次都要从头解析文件到skiprows指定的位置但至少它能保护内存。5.2 用executemany代替逐行INSERT速度天差地别如果你的数据源不是Excel而是JSON、CSV或者API接口你可以完全绕开pandas手动写SQL批量插入。即便你还在用pandas写to_sql其实底层也是批量执行性能尚可。但如果你非要自己写插入逻辑请务必用executemany而不是逐行execute。举个例子一万行数据逐行插入SQLite耗时大约在2到4秒。用executemany批量插入同一批数据耗时能降到0.2秒以内。差距20倍一点都不夸张。原因嘛单个INSERT是一条单独的事务每次都走一遍完整的事务提交和磁盘写回流程批量插入则可以把所有语句放在一个事务里极大减少致命IO次数。data_list df.to_records(indexFalse).tolist() placeholders ,.join([?] * len(df.columns)) col_names ,.join(df.columns) sql fINSERT INTO sales_detail ({col_names}) VALUES ({placeholders}) conn.executemany(sql, data_list)这里有个细节为了进一步提升性能可以在插入之前显式开启一个事务插完再统一提交conn.execute(BEGIN TRANSACTION) conn.executemany(sql, data_list) conn.commit()用to_sql的时候如果你发现大量行写入时速度很慢也可以考虑这个技巧——pandas默认的行为未必开了批量事务。5.3 实测数据一百万行Excel到SQLite的耗时参考为了让你心里有个底我给个实测参考数据普通配置的笔记本i5处理器16G内存。测试文件是50MB的单sheet Excel行数43万列数12列。方案耗时备注直接read_excel to_sql约18秒内存峰值约3.2GB分块读取50kto_sql append约45秒内存峰值约1.1GBread_excelexecutemany手动插入约12秒内存峰值约3.2GB看到没分块读取虽然慢了一倍有余但内存占用降了三分之二。到底是追求速度还是保住内存取决于你的机器有多扛造。如果你只是处理几万行的小文件不用纠结直接一把梭就行。6. 转换完成后的校验查询、备份与常见意外情况排查6.1 别急着删Excel先做三层数据核对脚本跑完了SQLite文件也生成了但你真的确信转换过程没问题吗数据从Excel搬到SQLite期间类型变形、行列错位、字符截断都是隐形的。我给自己定的规矩是三重校验第一重总行数核对对比Excel中每个sheet的数据行数注意别把表头算进去和SQLite对应表的COUNT(*)。df_check pd.read_excel(EXCEL_PATH, sheet_name明细, header0) excel_rows len(df_check) cur.execute(SELECT COUNT(*) FROM sales_detail) db_rows cur.fetchone()[0] assert excel_rows db_rows, f行数不一致Excel {excel_rows}DB {db_rows}第二重抽样值核对随机挑几行Excel里的原始数据跟SQLite里查出来的对比。这个动作看起来原始但能抓住很多莫名其妙的字符丢失问题。比如Excel里某个单元格是SKU-001转换后变成了SKU-1这种教训我是实实在在踩过的。第三重聚合值核对对关键数值列分别做SUMExcel侧用pandas的sum()SQLite侧用SQL的SUM()两边对账。如果差哪怕一分钱说明类型转换或精度处理出了问题。6.2 字符编码和诡异字符的处理大多数人第一次用pd.read_excel的时候不会遇到乱码问题因为.xlsx内部就是XML编码的UTF-8pandas处理得很干净。真正的问题出在原始数据本身Excel里可能混入了换行符、制表符、甚至全角空格。这些字符写入SQLite后不影响肉眼查看但在做条件匹配时会让你烦恼。比如一个客户名称是张三 结尾带了个全角空格你在查询时写WHERE name 张三查不出来。因为你肉眼看不见那个空格。解决办法是在写入前做字符清理df[客户名称] df[客户名称].astype(str).str.replace(\u3000, ).str.strip()如果文本里还混入了故意换行你可以选择保留可能是有意为之的多行文本也可以选择替换成空格df[备注] df[备注].astype(str).str.replace(\n, , regexFalse)6.3 表结构变化导致的历史数据兼容问题还有种常见场景是你以为Excel结构没变跑完脚本丢那不管了。三周后同事重新发来一份Excel说加了两个字段。你用if_existsreplace直接覆盖了表结果原来跑得好好的报表SQL全部报错因为列名对不上了。我的建议是每次转换前先把目标库的已有表结构打出来跟新数据的字段列表做一个diff。如果字段有变化自动把旧表备份成表名_backup_YYYYMMDD再建新表。这个逻辑也就十几行代码但它能防止你在生产环境里把自己坑到脚踝import datetime cur.execute(fPRAGMA table_info({table_name})) existing_cols [row[1] for row in cur.fetchall()] new_cols list(df.columns) if set(existing_cols) ! set(new_cols): backup_name f{table_name}_backup_{datetime.datetime.now():%Y%m%d} cur.execute(fALTER TABLE {table_name} RENAME TO {backup_name}) print(f表结构变化已将旧表备份为 {backup_name})6.4 终极防呆SQLite文件的VACUUM压缩写入时频繁增加、删除数据后SQLite文件内部会产生大量碎片文件体积比实际数据大不少。转换完成后执行一次VACUUM可以压缩文件把未使用的空闲页清掉。conn.execute(VACUUM)注意VACUUM不能在事务执行过程中调用所以一定要在commit之后单独执行。对大文件来说VACUUM可能要跑个几秒钟但换来的是更小的文件体积和更快的后续查询体验这笔买卖划算。7. 从转换工具到数据工作流定时增量更新的进阶思路单纯把Excel转成SQLite其实只是数据工作流的第一个环节。一旦你尝到了SQLite的甜头接下来一定会遇到增量更新的需求——Excel每周更新数据库不能每次都整体覆盖因为可能已经有其他程序在引用老数据了。我先给你一个经验判断标准如果你的Excel每次都是全量快照那就继续用replace简单粗暴最可靠如果你的Excel是追加式日志比如每天都有新订单追加到末尾那你要做的是先把新Excel里的重复部分过滤掉再append进已有表。具体过滤逻辑可以这样写# 找出库里已有最大订单号 cur.execute(SELECT MAX(订单号) FROM sales_detail) max_id cur.fetchone()[0] # 只插入大于最大订单号的数据 new_data df[df[订单号] max_id] new_data.to_sql(sales_detail, conn, if_existsappend, indexFalse)这招只有在订单号是单调递增时才能用。如果数据没有天然的自增业务主键你就得用多列联合去重来筛。我见过不少同事在Excel转SQLite这件事上绕了很多弯路。有人把Excel里的数据一张张截图手动录入到数据库管理工具里录了一晚上。有人用VBA去写数据库结果Office版本一换宏跑不了。回过头来看用Python的pandas加sqlite3组合可能是学习成本最低、结果最好维护的路径。有机会的话你可以把这个转换脚本封装成带命令行参数的版本例如传入文件路径和库路径这样以后不管手动执行还是被其他流程调度都很方便。你甚至可以用schedule库挂一个每天自动执行的定时任务把最新Excel转进SQLite。到那时候你就不是在转换数据了而是搭了一条小型数据流水线。我自己就是从这么一个小脚本起步把几份手工整理的Excel慢慢变成了好几个人都在用的数据服务收益非常大。
