python3 sqlite3 学习笔记（文件信息储存）

Gin_Q

1. # coding=utf-8
   
2. # python3.7.9
   
3. # Cool_Breeze
   
4. 
   
5. import sqlite3
   
6. import os
   
7. import datetime
   
8. import time
   
9. import csv
   
10. 
    
11. # 获取文件信息
    
12. def getFileInfo(path):
    
13.     absname = ''
    
14.     timestamp = 0
    
15.     mdate = ''
    
16.     for rootDir, baseDir, files in os.walk(path):
    
17.         for file in files:
    
18.             absname = os.path.join(rootDir, file)
    
19.             timestamp = time.localtime(os.path.getmtime(absname))
    
20.             yield  (None,                                                    # id
    
21.                     file,                                                    # name
    
22.                     absname,                                                 # absname
    
23.                     datetime.datetime.strptime(
    
24.                     time.strftime('%Y%m%d%H%M%S',timestamp),'%Y%m%d%H%M%S'), # mdate
    
25.                     os.path.getsize(absname)                                 # size_KB
    
26.                     )
    
27. 
    
28. # 连接数据库
    
29. conn = sqlite3.connect('fileInfo.db')
    
30. 
    
31. # 游标
    
32. cur = conn.cursor()
    
33. 
    
34. # 删除表单
    
35. # cur.execute("drop table fileInfo")
    
36. 
    
37. # 创建表单
    
38. # id 自动增长的整数
    
39. # name str
    
40. # mdate datetime
    
41. # size_KB float
    
42. 
    
43. # cur.execute("""create table fileinfo(
    
44.     # id integer PRIMARY KEY,
    
45.     # name text,
    
46.     # absname text,
    
47.     # mdate date,
    
48.     # size_KB real)""")
    
49. 
    
50. # 插入数据
    
51. # for n in getFileInfo(r'D:\GIN\py'):
    
52.     # cur.execute("insert into fileInfo values(?,?,?,?,?)", n)
    
53. 
    
54. # 查询数据
    
55. # cur.execute("select name,size_KB from fileInfo where id=1000")
    
56. # print(cur.fetchone())
    
57. 
    
58. # 导出csv
    
59. # excel 打开乱码，需要将文件编码转换为 ANSI（源文件是 utf-8）
    
60. cur.execute("select * from fileInfo")
    
61. csvHeader = ['序号', '文件名', '文件绝对路径', '文件最后修改日期', '文件大小（单位：KB）']
    
62. with open('fileInfo.csv', 'w', encoding='utf-8',  newline='') as f:
    
63.     csvf = csv.writer(f)
    
64.     csvf.writerow(csvHeader)
    
65.     for n in cur.fetchall():
    
66.         csvf.writerow(n)
    
67. 
    
68. # 关闭游标
    
69. cur.close()
    
70. # 提交修改
    
71. conn.commit()
    
72. # 关闭连接
    
73. conn.close()
    
74. 
    
75. # 笔记
    
76. 
    
77. # 查询表名
    
78. # cur.execute("select name from sqlite_master WHERE  type='table'")
    
79. # tname = cur.fetchall()[0][0]
    
80. # print(tname)
    
81. # 查询表单字段名
    
82. # cur.execute(f"PRAGMA table_info({tname})")
    
83. # for n in cur.fetchall(): print(n)
    
84. # print(conn.total_changes)
    
85. 
    
86. # 创建表
    
87. # cur.execute('''create table test1(id integer, d date, ts timestamp)''')
    
88. # cur.execute("create table test1(id integer PRIMARY KEY, d date, ts timestamp)")
    
89. # 插入数据
    
90. # cur.execute("insert into test1 values(?,?,?)",(None,datetime.datetime.now(),time.time()))
    
91. # cur.execute("insert into test1 values(?,?,?)",(None,datetime.datetime.strptime('20180627041104','%Y%m%d%H%M%S'),time.time()))
    
92. # cur.execute("insert into test1 values(?,?,?)", (None,datetime.datetime.now(),time.time()))
    
93. # batch(cur)
    
94. 
    
95. # 查询表单数据
    
96. # cur.execute("select * from test1 where ts > 1613712545 or id %3= 0 ")
    
97. # for n in cur.fetchall():
    
98.     # print(n)
    
99. 
    
100. # sqlite3 内置函数
     
101. # cur.execute("select *, max(id) from test1")
     
102. # cur.execute("select sum(id) from test1")
     
103. # sqlite3 版本
     
104. # cur.execute("select sqlite_version()")
     
105. # target = cur.fetchone()
     
106. # cur.execute("select * from test1 where id=(?)", target)
     
107. # 日期函数
     
108. # cur.execute("select strftime('%Y-%m-%d %H:%M', 'now', '+8 hours')")
     
109. 
     
110. # 转换为时间戳
     
111. # cur.execute("select d from test1 where id=1")
     
112. # d = cur.fetchone()[0]
     
113. # cur.execute("select strftime('%s', '2021-02-21 08:02') - strftime('%s', ?)", (d,))
     
114. # cur.execute("select strftime('%', ?)", cur.fetchone())
     
115. # print(cur.fetchone())
     
116. # print(cur.fetchone())
     
117. # cur.execute("select ts from test1 where id=2")
     
118. 
     
119. # glob
     
120. # cur.execute("select id, name, mdate from fileinfo where name glob '*?.py'")
     
121. 
     
122. # like
     
123. # cur.execute("select id, name, mdate from fileinfo where name like '%_.py'")
     
124. 
     
125. # limit 输出限制
     
126. # cur.execute("select id, name, mdate from fileinfo where name like '%_.py' limit 2 offset 1")
     
127. 
     
128. # order by 排序  升序或降序 [ASC | DESC]
     
129. # 注意语句顺序 where --> group by --> order by --> limit
     
130. # cur.execute("select id, size_KB from fileinfo where name like '%_.py' order by size_KB asc limit 10")
     
131. 
     
132. # group by 数据分组
     
133. # cur.execute("select id,name,sum(size_KB)as size from fileinfo where name like '_.jpeg' group by name order by size asc")
     
134. 
     
135. # having 在 GROUP BY 子句创建的分组上设置筛选条件
     
136. # 注意语句顺序 where --> group by --> having --> order by --> limit
     
137. # 找出 以一个字符开头，以.jpeg结尾的文件名，函数count统计相同名出现的次数， 然后按相同名字分组，过滤掉出现次数小于300的， 按出现次数升序
     
138. # cur.execute("select id,name,count(name) as cn from fileinfo where name like '_.jpeg' group by name having cn>300 order by cn asc")
     
139. 
     
140. # distinct 去重
     
141. # cur.execute("select count(name) from fileinfo where name glob '?.jpeg'") # 找到 3034 个文件
     
142. # cur.execute("select count(distinct name) from fileinfo where name glob '?.jpeg'") # 去重后剩下 9 个文件
     
143. 
     
144. 
     
145. # for n in cur.fetchall():
     
146.     # print(n)
     
147. #
     
148. # 更新数据
     
149. # cur.execute("select d from test1 where id=13")
     
150. # print(cur.fetchone())
     
151. # cur.execute("update test1 set d=(?) where id=13", (datetime.datetime.now(),))
     
152. # 删除表单
     
153. # cur.execute("drop table test1")
     
154. 
     
155. # delete语句
     
156. # cur.execute("delete from test1 where id=13")
     
157. # conn.commit()# saveCsv(cur)

复制代码

utf-8 转 gb18030 编码转换

1. # coding=utf-8
   
2. # python3.7.9
   
3. 
   
4. # utf-8 转 GB18030
   
5. # str.encode(encoding="utf-8", errors="strict")
   
6. 
   
7. with open('fileInfo.csv', encoding='utf-8') as f:
   
8.     with open('fileInfoANSI.csv', 'wb') as af:
   
9.         for line in f:
   
10.             af.write(line.encode('GB18030', errors='ignore'))

复制代码

BAT221110

好文要顶！