[新手上路]批处理新手入门导读[视频教程]批处理基础视频教程[视频教程]VBS基础视频教程[批处理精品]批处理版照片整理器
[批处理精品]纯批处理备份&还原驱动[批处理精品]CMD命令50条不能说的秘密[在线下载]第三方命令行工具[在线帮助]VBScript / JScript 在线参考
返回列表 发帖

[技术讨论] python3 sqlite3 学习笔记(文件信息储存)

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

好文要顶!

TOP

返回列表