初探操作sqlite数据库
import sqlite3
from hashlib import md5
from datetime import datetime
# 生成MD5
def make_md5(s, encoding='utf-8'):
# 返回MD5值
return md5(s.encode(encoding)).hexdigest()
# 主函数
if __name__ == '__main__':
start_time = datetime.now() # 开始时间
print(start_time)
# 链接数据库
db_path = 'D:/YJFRN/RNCore/data/data3.db' # 数据库路径
db = sqlite3.connect(db_path) # 链接数据库
cursor = db.cursor() # 获取操作游标 游标就是当前操作的行
# 需要插入的数据
ID = 0 # 初始编号
CMD1 = 1070
CMD2 = 1052
CMD3 = 1052
SID = 0
UPDATA1 = 'CAASDjUxMDE4NTEwODg4ODg4GpgBChI0MzA0MjQxOTg5MDIyNTc0MzgSB1dJTi0xMjIaDTE3Mi4xNi42MS4xMjIiETAwLTBDLTI5LT' \
'MxLTY2LTA4KgnnqILpn7bls7AwbzoSNDMwNDI0MTk4OTAyMjU3NDM4QJKnmIUGSiQKBuS4iue6vxAAGIXg+oUGIgdXSU4tMTIyKAEw' \
'heD6habBgQZQAFgBYANqBAoAEgA= '
UPDATA2 = 'CnEIARABGJOnmIUGIhLnnJ/lrp7ouqvku73miqXoraYobzISNDMwNDI0MTk4OTAyMjU3NDM4OgBCIgoG5LiK57q/EAAYheD6hQYiB1' \
'dJTi0xMjIwheD6habBgQZKAFABWgbkuIrnur9iCeeogumftuWzsA=='
UPDATA3 = 'CnEIBhABGJOnmIUGIhLnnJ/lrp7ouqvku73miqXoraYobzISNDMwNDI0MTk4OTAyMjU3NDM4OgBCIgoG5LiK57q/EAAYheD6hQYiB1' \
'dJTi0xMjIwheD6habBgQZKAFAGWgbkuIrnur9iCeeogumftuWzsA=='
DOWNDATA1 = 'CAASDjUxMDE4NTEwODg4ODg4GpYBChI0MzA0MjQxOTg5MDIyNTc0MzgSB1dJTi0xMjIaDTE3Mi4xNi42MS4xMjIiETAwLTBDLTI5' \
'LTMxLTY2LTA4KgnnqILpn7bls7AwbzoSNDMwNDI0MTk4OTAyMjU3NDM4QM2UnoUGSiIKBuS4i+e6vxAAGILg48QHIgdXSU4tMTIy' \
'MILg48SnwYEGUAFYAWADagQKABIA'
DOWNDATA2 = 'CnQIARABGM2UnoUGIhLnnJ/lrp7ouqvku73miqXoraYobzISNDMwNDI0MTk4OTAyMjU3NDM4OgBCJQoJ5raT5ays5ZqOEAAYguDj' \
'xAciB1dJTi0xMjIwguDjxKfBgQZKAFABWgbkuIvnur9iCeeogumftuWzsA=='
DOWNDATA3 = 'CnQIBhABGM2UnoUGIhLnnJ/lrp7ouqvku73miqXoraYobzISNDMwNDI0MTk4OTAyMjU3NDM4OgBCJQoJ5raT5ays5ZqOEAAYguDj' \
'xAciB1dJTi0xMjIwguDjxKfBgQZKAFAGWgbkuIvnur9iCeeogumftuWzsA=='
try:
# 查询数据
cursor.execute('select * from cmd_table')
re = cursor.fetchall()
for i in re:
print(i)
# 清空表数据
sql = "delete from cmd_table;"
cursor.execute(sql)
except BaseException as e:
print(e)
# 清空失败则创建表
sql = 'CREATE TABLE "cmd_table" (' \
'"id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, ' \
'"cmd" integer NOT NULL, ' \
'"sid" integer NOT NULL DEFAULT 0, ' \
'"data" blob NOT NULL, ' \
'"md5" TEXT NOT NULL );'
cursor.execute(sql)
print("创建表格成功")
else:
print("清空表数据成功")
# 插入N次上下机数据
for i in range(10):
# 上机数据插入3条
ID = ID + 1
MD5 = make_md5(str(ID)) # 对编号进行MD5加密
sql = "INSERT INTO cmd_table(id,cmd,sid,data,md5) VALUES(%d,%d,%d,'%s','%s');"
sql = sql % (ID, CMD2, SID, UPDATA2, MD5)
# print(sql)
cursor.execute(sql)
ID = ID + 1
MD5 = make_md5(str(ID)) # 对编号进行MD5加密
sql = "INSERT INTO cmd_table(id,cmd,sid,data,md5) VALUES(%d,%d,%d,'%s','%s');"
sql = sql % (ID, CMD2, SID, UPDATA2, MD5)
cursor.execute(sql)
ID = ID + 1
MD5 = make_md5(str(ID)) # 对编号进行MD5加密
sql = "INSERT INTO cmd_table(id,cmd,sid,data,md5) VALUES(%d,%d,%d,'%s','%s');"
sql = sql % (ID, CMD3, SID, UPDATA3, MD5)
cursor.execute(sql)
# 下机数据插入3条
ID = ID + 1
MD5 = make_md5(str(ID)) # 对编号进行MD5加密
sql = "INSERT INTO cmd_table(id,cmd,sid,data,md5) VALUES(%d,%d,%d,'%s','%s');"
sql = sql % (ID, CMD1, SID, DOWNDATA1, MD5)
cursor.execute(sql)
ID = ID + 1
MD5 = make_md5(str(ID)) # 对编号进行MD5加密
sql = "INSERT INTO cmd_table(id,cmd,sid,data,md5) VALUES(%d,%d,%d,'%s','%s');"
sql = sql % (ID, CMD2, SID, DOWNDATA2, MD5)
cursor.execute(sql)
ID = ID + 1
MD5 = make_md5(str(ID)) # 对编号进行MD5加密
sql = "INSERT INTO cmd_table(id,cmd,sid,data,md5) VALUES(%d,%d,%d,'%s','%s');"
sql = sql % (ID, CMD3, SID, DOWNDATA3, MD5)
cursor.execute(sql)
# print("已执行循环次数:" + str(i))
db.commit() # 提交SQL操作 提交后才会生效
cursor.close() # 关闭游标
db.close() # 关闭链接
end_time = datetime.now() # 结束时间
print(end_time)