方便且实用,Python内置的轻量级数据库实操_sqlite
之前我们在很多项目案例中都使用了MySQL数据库。有的同学问：MySQL安装和配置太麻烦了，老是搞不定，有没有更简便的方法？
当然有咯！
Python内置的sqlite3模块就是一个轻量级的数据库，不需要独立的服务器进程。它的数据库就是一个 .db 后缀的文件，可以跨平台直接访问，非常便捷。简直就是轻量级数据的首选数据库啊！
★今天我们就来用Python实战操作一下sqlite3，通过本案例可以完全掌握sqlite3的常用操作。
”
本文主要内容：
数据读取和插入：包括原始数据的读取、插入数据库。
数据库操作：包括数据的查询、删除、导出等操作，最后上一个自动化导出的案例。
不过，在开始前需要说明的是，虽然Python内置了sqlite3模块，但它只是用来连接数据库的接口。你仍然需要确保电脑上已经有了sqlite3这个数据库软件。
如果你是Mac或者Linux系统，那么恭喜你，你的电脑上默认就装有sqlite3。Windows的同学得手动安装一下。
下载地址：https://www.sqlite.org/download.html
选择 Precompiled Binaries for Windows，下载预编译的二进制 zip 文件：sqlite-tools-win32-*.zip 和 sqlite-dll-win32-*.zip。
在你的电脑上创建文件夹 C:sqlite，并在此文件夹下解压上面两个压缩文件，将得到 sqlite3.def、sqlite3.dll 和 sqlite3.exe 文件。
添加 C:sqlite到 PATH环境变量。安装成功后，在命令提示符下，使用 sqlite3命令，将会显示如下结果：
1. 读取数据并插入数据库
本文以TXT文件为例，进行示范操作。TXT文件为单列数据，以换行符分割，文件数量60+，数据总量5万+。
程序设计思路：自动读取每个TXT文件，再依次读取每一行并插入数据库中。
1.1 源代码# -*- coding: utf-8 -*-
"""
Created on Fri Jan 17 15:06:42 2020
@author: liulu
"""
importsqlite3
importos
path = os.getcwd
files = os.listdir(path)
conn = sqlite3.connect(path+ 'db.db')
cur = conn.cursor
cur.execute( 'create table if not exists numbers (id integer primary key ,number varchar(20) NOT NULL)')
conn.commit
i = 0
forfile infiles:
iffile.split( '.')[ -1] == 'txt':
withopen(file, 'r',encoding = 'UTF-8') asf:
next(f)
forline inf:
i += 1
print( "插入第", i, "条数据:")
#print(line)
cur.execute( 'insert into numbers values(?,?)',(i,line))
conn.commit
cur.close
conn.close
print( '数据写入完成！共写入',i, ' 条数据')
1.2 运行效果
代码运行效果图 1.3 重点代码解释
1）自动获取所有TXT文件名称和路径
了解我的读者都知道，我是比较懒的，我不喜欢指定程序的路径。我写的程序都是自己找路径，自己找文件名称。感兴趣的朋友可以去我的公众号（智能演示）查看这篇文章：获取任意路径下文件名称的2种方法。
2）数据库的创建与连接
conn = sqlite3.connect(path+'db.db')
path 是程序所在路径，也是TXT文件的所在路径。这行代码的意思是：如果数据库已经存在，则连接数据库；如果数据库不存在，则先创建数据库，再连接该数据库。
3）创建表
cur.execute('create table if not exists numbers (id integer primary key ,number varchar(20) NOT NULL)')
在数据库 db.db 中创建表 numbers ,表中包含两个字段：id、number。
注意！一定不要忘了 conn.commit 保存一下！不然后面的数据是插不进去的。
4）插入数据
①优化打开方式
with open(file,'r',encoding = 'UTF-8') as f:
建议使用 with 语句来打开TXT文件。这样做的好处是，当对象会在with语句结束时会自动关闭。效率更高！不易出错。
②占位符功能
cur.execute('insert into numbers values(?,?)',(i,line))
(?,?) 起到占位符功能， (i,line)) 中的值会按位置替换到SQL语句中。
这样就实现了数据的自动插入数据库。需要几分钟时间，请耐心等待。
我们查看下 db.db 文件属性，可以看到存储了51216条数据的数据库文件大小只有1.13MB，可以说是很轻量的。
你可以将这个数据库文件复制到任意一台电脑上，连接上数据库即可进行操作，非常方便。
下面就来说一下sqlite3的常用操作。
2. 数据库操作2.1 连接数据库importsqlite3
importos
path = os.getcwd
files = os.listdir(path)
conn = sqlite3.connect(path+ 'db.db')
cur = conn.cursor
这样就连接上了刚才的数据库。 path+'db.db' 是数据库的路径及文件名，你可以手动输入路径，也可以将 .py 文件和 db.db 数据库文件放在同一目录下，系统自动计算路径，这是懒人方法。
2.2 查询数据库中有哪些表
我们似乎不知道 db.db 这个数据库中有什么，如何才能知道这个数据库中有哪些表呢？
cur.execute( "SELECT name FROM sqlite_master WHERE type='table';")
Tables=cur.fetchall
print(Tables)
# [('numbers',)]
2.3 删除数据库中的某个表
如果需要删除数据库中的某个表，可以执行以下命令：
cur.execute("drop table tablename;")
2.4 查询某个表的结构cur.execute( "PRAGMA table_info(numbers)")
print(cur.fetchall)
# [(0, 'id', 'integer', 0, None, 1), (1, 'number', 'varchar(20)', 1, None, 0)]
2.5 查询表中前50条记录cur.execute( "SELECT * from numbers limit 0,50;")
conn.commit
data = cur.fetchall
print(data)
2.6 查询表中所有记录# 5.查询表中所有记录
cur.execute( "SELECT * from numbers;")
data_all = cur.fetchall
a = len(data_all)
print( '共有 '+ str(a) + ' 条记录')
#print(data)
2.7 查询表中不重复记录cur.execute( "SELECT distinct number from numbers;")
data_distinct = cur.fetchall
b = len(data_distinct)
print( '共有 '+ str(b) + ' 条不重复记录')
#print(data_distinct)
2.8 将老表中的不重复记录插入新表# 创建一个新表
cur.execute( 'create table if not exists numbers_distinct (id integer primary key ,number varchar(20) NOT NULL)')
conn.commit
# 插入数据
i = 0
fordata indata_distinct:
i += 1
data = data[ 0]
#print(data)
cur.execute( 'insert into numbers_distinct values(?,?)',(i,data))
conn.commit
2.9 将特定结果写入文本文件（单列）cur.execute( "SELECT number from numbers_distinct limit 0,10;")
datas = cur.fetchall
#print(datas)
withopen( 'datafile1.txt', 'w') asf1:
fordata indatas:
f1.write(data[ 0])
f1.flush
注意：f1.flush 操作很重要！将缓冲区的数据写入文件中，否则文本文件为空白，导致写入失败。
2.10 将特定结果写入文本文件（多列）cur.execute( "SELECT * from numbers_distinct limit 0,10;")
datas = cur.fetchall
#print(datas)
withopen( 'datafile2.txt', 'w') asf2:
fordata indatas:
data0 = str(data[ 0]) # 将int类型转为str，否则write函数报错
data = data0 + ' '+ data[ 1] # 在两列之间以Tab键分隔
f2.write(data)
f2.flush
2.11 将特定结果写入CSV文件（多列）
写入CSV文件和写入文本文件的方法基本相同，只需要将文件后缀改为 '.csv',并将分隔符改为英文状态下逗号即可。
withopen( 'datafile3.csv', 'w') asf3:
fordata indatas:
data0 = str(data[ 0]) # 将int类型转为str，否则write函数报错
data = data0 + ','+ data[ 1] # 在两列之间以逗号键分隔
f3.write(data) # data是元组类型
f3.flush # 重要！将缓冲区的数据写入文件中
2.12 办公自动化案例
本文以：将 number_distingct 表中的数据，以每50个为一组，分别存入一个文本文件为例，演示以下Python结合sqlite3数据库的办公自动化的惊艳效果。
1）源代码
"""
源代码已发布到【智能演示】微信公众号，后台回复 sqlite3 可获取下载链接。
"""
forn inrange(int(b/ 50)+ 1):
if50*n+ 50< b: # b为数据记录总数
cur.execute( 'SELECT number from numbers_distinct limit ?,?',( 50*n, 50))
datas = cur.fetchall
#print(datas)
filename = str( 50*n) + '-'+ str( 50*n+ 50) + '.txt'
print( '正在写入：'+ filename)
withopen(filename, 'w') asf4:
fordata indatas:
f4.write(data[ 0]) # data是元组类型
f4.flush # 重要！将缓冲区的数据写入文件中
else:
filename = str( 50*n) + '-'+ str(b) + '.txt'
print( '正在写入：'+ filename)
withopen(filename, 'w') asf5: # 计算最后一个文本文件名称
cur.execute( 'SELECT number from numbers_distinct limit ?,?',( 50*n+ 50, 50))
datas = cur.fetchall
fordata indatas:
f5.write(data[ 0]) # data是元组类型
f5.flush # 重要！将缓冲区的数据写入文件中
print( '写入完成！共写入{}个TXT文件。'.format(n+ 1))
2）运行效果
导出719个TXT文件，几秒钟就完成了。
3）重点代码解释
这个案例的重点是如何计算出每个TXT文件的名称。
我们可以计算出 numbers_distinct 表中共有35926条数据。
我们可以构建这样一个函数： filename = str(50*n) + '-' + str(50*n+50) + '.txt' ，用来计算每个导出的TXT文件名称。
但是最后一个文件的名称不能通过这个方法来构建，需要作以下简单的修改： filename = str(50*n) + '-' + str(b) + '.txt'
计算好每个TXT文件名称后，直接用 write 函数写入就可以了。
快来动动手试一下吧！
作者：老牛思勉
来源： 智能演示