将Sqlite3数据库挂在内存上处理

创作灵感：最近把小学生的口算题从2位数改到3位数，100以内四则运算练习（千纬数学）再次更新，选取难题-CSDN博客要不断刷题目，以前100以内的加减乘除也是这样刷出来的，代码如下：

import sqlite3
import random
from time import time
from pathlib import Path#导入必要的库resources_folder = Path(__file__).joinpath("../resources/").resolve()
db_filepath = resources_folder.joinpath("qwsx.db")
#db_filepath = '/storage/emulated/0/Pictures/qwsx.db'
#上面是数据库的存放位置,生成手机app请参考我以前的文章
#oppo版 需要用电脑调试应删除下面5行def gettid(s1,fh,s2,dan):conn = sqlite3.connect(db_filepath, timeout=10, check_same_thread=False)c = conn.cursor()#如果公式存在，提取tidcursor = c.execute("SELECT count(tid) from ys where s1 = ? and fh = ? and s2 = ?;", (s1,fh,s2,))row = cursor.fetchone()ctid = row[0]#如果公式不存在，插入公式到数据库if ctid == 0:c.execute("INSERT INTO ys(s1,fh,s2,dan,cs,cuo) VALUES (?,?,?,?,0,0);", (s1,fh,s2,dan,))conn.commit()cursor = c.execute("SELECT tid from ys where s1 = ? and fh = ? and s2 = ? order by tid desc;", (s1,fh,s2,))row = cursor.fetchone()tid = row[0] c.close()conn.close()return(tid)
#获取tid，题目的id
def settm(nd):    if nd ==1:jj = random.randint(0,1)elif nd ==2:jj = random.randint(0,3)if jj == 0:#为加法 s1 = random.randint(0,100)s2 = random.randint(0,(100 - s1))cvalue = str(s1) + "+" + str(s2) + "=" dan = s1 + s2hd = 1tid = gettid(s1,jj,s2,dan)ii = random.randint(0,4) #0为提交答案if ii == 2:cvalue = "□+" + str(s2) + "=" + str(dan) + ",□为"dan = s1elif ii == 3:cvalue = str(s1) + "+□=" + str(dan) + ",□为"dan = s2elif ii ==4 and s2 > 0:#a+0=a,a-0=a,可以是+-cvalue = str(s1) + "□" + str(s2) + "=" + str(dan) + ",□为"dan = jjhd = 4 #hd4为符号elif jj ==1:s1 = random.randint(0,100)s2 = random.randint(0,s1)cvalue = str(s1) + "-" + str(s2) + "=" dan = s1 - s2hd = 1tid = gettid(s1,jj,s2,dan)ii = random.randint(0,4) #0为提交答案if ii == 2:cvalue = "□-" + str(s2) + "=" + str(dan) + ",□为"dan = s1elif ii == 3:cvalue = str(s1) + "-□=" + str(dan) + ",□为"dan = s2elif ii ==4 and s2 > 0:#a+0=a,a-0=a,可以是+-cvalue = str(s1) + "□" + str(s2) + "=" + str(dan) + ",□为"dan = jjhd = 4 #hd4为符号elif jj ==2:#乘法s1 = random.randint(1,10)s2 = random.randint(0,int(100 / s1))cvalue = str(s1) + "×" + str(s2) + "="dan = s1 * s2hd = 1tid = gettid(s1,jj,s2,dan)ii = random.randint(0,4) #0为提交答案if ii == 2:cvalue = "□×" + str(s2) + "=" + str(dan) + ",□为"dan = s1elif ii == 3 and s2 > 0:#a*0=0,b*0=0cvalue = str(s1) + "×□=" + str(dan) + ",□为"dan = s2elif ii ==4 and s2 !=1 and s1 != 0:#a*=a,a/1=a;0*a=0,0/a=0cvalue = str(s1) + "□" + str(s2) + "=" + str(dan) + ",□为"dan = jjhd = 4 #hd4为符号elif jj ==3:s1 = random.randint(1,10)s2 = random.randint(0,int(100 / s1))s3 = s1dan = s1 * s2s1 = dans2 = s3cvalue = str(s1) + "÷" + str(s2) + "=" dan = int(s1 / s2)hd = 1tid = gettid(s1,jj,s2,dan)ii = random.randint(0,4) #0为提交答案if ii == 2:cvalue = "□÷" + str(s2) + "=" + str(dan) + ",□为"dan = s1elif ii == 3 and s1 > 0:#0/a=0cvalue = str(s1) + "÷□=" + str(dan) + ",□为"dan = s2elif ii ==4 and s2 !=1 and s1 !=0:#a*=a,a/1=a;0*a=0,0/a=0cvalue = str(s1) + "□" + str(s2) + "=" + str(dan) + ",□为"dan = jjhd = 4 #hd4为符号cid = 0return(jj,dan,hd,cid,tid,cvalue)i = 0
while i < 1000000:settm(2)i=i+1

上面代码就是刷题100万次，让电脑随机出题。实际能够存入数据库的题目只有1万条。所以当初刷题的时候没有考虑计算机的运行效率和对资源的消耗，从上面程序看，每运行一次就要从硬盘读取数据库文件一次。这次不知道要刷题多少次才能将3位数以内的算式。问下deepseek：

官网的罢工：

用下国家超算平台DeepSeek的：

最终答案：

两个1至3位数相加或相减，得数在0到999范围内的所有算式共有 998,001 条。

再加上200以内的乘法和除法：

经过上述计算，我们得出所有满足条件的算式共有 2000 个。

除法应该在2000个以内。

下面是随机生成的代码：

import sqlite3
import random
from time import time
from pathlib import Path
import datetime
#导入必要的库resources_folder = Path(__file__).joinpath("../resources/").resolve()
db_filepath = resources_folder.joinpath("qwsx.db")
#db_filepath = '/storage/emulated/0/Pictures/qwsx.db'
#上面是数据库的存放位置,生成手机app请参考我以前的文章
#oppo版 需要用电脑调试应删除下面5行
for j in range(0,10):# 连接到磁盘上的数据库disk_conn = sqlite3.connect(db_filepath)# 连接到内存数据库memory_conn = sqlite3.connect(':memory:')# 使用 backup API 将磁盘数据库复制到内存数据库disk_conn.backup(memory_conn)xt = 0def gettid(s1,fh,s2,dan):global xtconn = memory_connc = conn.cursor()#如果公式存在，提取tidcursor = c.execute("SELECT count(tid) from ys where s1 = ? and fh = ? and s2 = ?;", (s1,fh,s2,))row = cursor.fetchone()ctid = row[0]#如果公式不存在，插入公式到数据库if ctid == 0:c.execute("INSERT INTO ys(s1,fh,s2,dan,cs,cuo) VALUES (?,?,?,?,0,0);", (s1,fh,s2,dan,))conn.commit()# print(s1,fh,s2,dan)else:# print('与数据库存在相同')xt = xt + 1cursor = c.execute("SELECT tid from ys where s1 = ? and fh = ? and s2 = ? order by tid desc;", (s1,fh,s2,))row = cursor.fetchone()tid = row[0] c.close()return(tid)#获取tid，题目的iddef settm(nd):    if nd ==1:jj = random.randint(0,1)elif nd ==2:jj = random.randint(0,3)if jj == 0:#为加法 s1 = random.randint(0,999)s2 = random.randint(0,(999 - s1))cvalue = str(s1) + "+" + str(s2) + "=" dan = s1 + s2hd = 1tid = gettid(s1,jj,s2,dan)ii = random.randint(0,4) #0为提交答案if ii == 2:cvalue = "□+" + str(s2) + "=" + str(dan) + ",□为"dan = s1elif ii == 3:cvalue = str(s1) + "+□=" + str(dan) + ",□为"dan = s2elif ii == 4 and s2 > 0:#a+0=a,a-0=a,可以是+-cvalue = str(s1) + "□" + str(s2) + "=" + str(dan) + ",□为"dan = jjhd = 4 #hd4为符号elif jj ==1:s1 = random.randint(0,999)s2 = random.randint(0,s1)cvalue = str(s1) + "-" + str(s2) + "=" dan = s1 - s2hd = 1tid = gettid(s1,jj,s2,dan)ii = random.randint(0,4) #0为提交答案if ii == 2:cvalue = "□-" + str(s2) + "=" + str(dan) + ",□为"dan = s1elif ii == 3:cvalue = str(s1) + "-□=" + str(dan) + ",□为"dan = s2elif ii ==4 and s2 > 0:#a+0=a,a-0=a,可以是+-cvalue = str(s1) + "□" + str(s2) + "=" + str(dan) + ",□为"dan = jjhd = 4 #hd4为符号elif jj ==2:#乘法s1 = random.randint(1,200)s2 = random.randint(0,int(200 / s1))cvalue = str(s1) + "×" + str(s2) + "="dan = s1 * s2hd = 1tid = gettid(s1,jj,s2,dan)ii = random.randint(0,4) #0为提交答案if ii == 2:cvalue = "□×" + str(s2) + "=" + str(dan) + ",□为"dan = s1elif ii == 3 and s2 > 0:#a*0=0,b*0=0cvalue = str(s1) + "×□=" + str(dan) + ",□为"dan = s2elif ii ==4 and s2 !=1 and s1 != 0:#a*=a,a/1=a;0*a=0,0/a=0cvalue = str(s1) + "□" + str(s2) + "=" + str(dan) + ",□为"dan = jjhd = 4 #hd4为符号elif jj ==3:s1 = random.randint(1,200)s2 = random.randint(0,int(200 / s1))s3 = s1dan = s1 * s2s1 = dans2 = s3cvalue = str(s1) + "÷" + str(s2) + "=" dan = int(s1 / s2)hd = 1tid = gettid(s1,jj,s2,dan)ii = random.randint(0,4) #0为提交答案if ii == 2:cvalue = "□÷" + str(s2) + "=" + str(dan) + ",□为"dan = s1elif ii == 3 and s1 > 0:#0/a=0cvalue = str(s1) + "÷□=" + str(dan) + ",□为"dan = s2elif ii ==4 and s2 !=1 and s1 !=0:#a*=a,a/1=a;0*a=0,0/a=0cvalue = str(s1) + "□" + str(s2) + "=" + str(dan) + ",□为"dan = jjhd = 4 #hd4为符号cid = 0return(jj,dan,hd,cid,tid,cvalue)print(datetime.datetime.now())i = 0while i < 50000:settm(1)i=i+1print(f'与原来数据库存在{xt}个相同。')print(datetime.datetime.now())# 将内存数据库的内容写回磁盘数据库memory_conn.backup(disk_conn)# 关闭连接disk_conn.close()memory_conn.close()

这样要形成一个随机的表，太慢了，运行了半天：

2025-02-12 23:19:25.515728
与原来数据库存在25585个相同。
2025-02-13 00:10:52.646772
2025-02-13 00:10:52.894585
与原来数据库存在26770个相同。
2025-02-13 01:04:37.832301
2025-02-13 01:04:38.108767
与原来数据库存在27902个相同。
2025-02-13 02:01:26.172076
2025-02-13 02:01:26.429696
与原来数据库存在28961个相同。
2025-02-13 03:01:18.825697
2025-02-13 03:01:19.081742
与原来数据库存在30000个相同。
2025-02-13 04:03:55.562965
2025-02-13 04:03:55.833989
与原来数据库存在30767个相同。
2025-02-13 05:09:20.222007
2025-02-13 05:09:20.711048
与原来数据库存在31616个相同。
2025-02-13 06:16:58.876971
2025-02-13 06:16:59.169436
与原来数据库存在32288个相同。
2025-02-13 07:27:02.256963
2025-02-13 07:27:02.546013
与原来数据库存在33187个相同。
2025-02-13 08:42:14.837606
2025-02-13 08:42:15.139579
与原来数据库存在33747个相同。
2025-02-13 09:57:30.281790

而且，接下来要生成数据库不存在的公式，将越来越少。所以不如按顺序全部生成，不用1分钟就完成：

import sqlite3
import random
from time import time
from pathlib import Path
import datetime
#导入必要的库resources_folder = Path(__file__).joinpath("../resources/").resolve()
db_filepath = resources_folder.joinpath("qwsx.db")
#db_filepath = '/storage/emulated/0/Pictures/qwsx.db'
#上面是数据库的存放位置,生成手机app请参考我以前的文章
#oppo版 需要用电脑调试应删除下面5行# 连接到磁盘上的数据库
disk_conn = sqlite3.connect(db_filepath)# 连接到内存数据库
memory_conn = sqlite3.connect(':memory:')# 使用 backup API 将磁盘数据库复制到内存数据库
disk_conn.backup(memory_conn)def gettid(s1,fh,s2,dan):conn = memory_connc = conn.cursor()#如果公式存在，提取tidc.execute("INSERT INTO ysall(s1,fh,s2,dan,cs,cuo) VALUES (?,?,?,?,0,0);", (s1,fh,s2,dan,))conn.commit()c.close()return(1)
#获取tid，题目的id
def settm(jj):if jj == 0:#为加法 for s1 in range(0,1000):for s2 in range(0,(1000 - s1)):dan = s1 + s2tid = gettid(s1,jj,s2,dan)cvalue = str(s1) + "+" + str(s2) + "=" + str(dan)print(cvalue)elif jj ==1:for s1 in range(0,1000):for s2 in range(0,s1 + 1):dan = s1 - s2tid = gettid(s1,jj,s2,dan)cvalue = str(s1) + "-" + str(s2) + "=" + str(dan)print(cvalue)elif jj ==2:#乘法for s1 in range(1,201):for s2 in range(0,int(200 / s1) + 1):dan = s1 * s2tid = gettid(s1,jj,s2,dan)cvalue = str(s1) + "×" + str(s2) + "=" + str(dan)print(cvalue)elif jj ==3:ii = 0for s1 in range(1,201):# print(s1)s3 = s1for s2 in range(0,int(200 / s1) + 1):ii = ii + 1# print(s3)dan = s1 * s2ss1 = danss2 = s3sdan = int(ss1 / ss2)tid = gettid(ss1,jj,ss2,sdan)cvalue = str(ss1) + "÷" + str(ss2) + "=" + str(sdan)print(cvalue)print(ii)return(jj,dan,tid,cvalue)
print(datetime.datetime.now())
settm(3)
print(datetime.datetime.now())
# 将内存数据库的内容写回磁盘数据库
memory_conn.backup(disk_conn)# 关闭连接
disk_conn.close()
memory_conn.close()

在对这些数据进行随机写入：

# -*- coding: utf-8 -*-
"""
Created on Thu Feb 13 10:47:16 2025@author: YBK
"""
import sqlite3
import random
from pathlib import Pathresources_folder = Path(__file__).joinpath("../resources/").resolve()
db_filepath = resources_folder.joinpath("qwsx.db")
# 连接到磁盘上的数据库
disk_conn = sqlite3.connect(db_filepath)
# 连接到内存数据库
memory_conn = sqlite3.connect(':memory:')
# 使用 backup API 将磁盘数据库复制到内存数据库
disk_conn.backup(memory_conn)conn = memory_conn
c = conn.cursor()
#如果公式存在，提取tid
cursor = c.execute("SELECT tid from ysall;")
rows = cursor.fetchall()
ysshun = [row[0] for row in rows]
print(len(ysshun))
random.shuffle(ysshun)
for tid in ysshun:cursor = c.execute("SELECT s1,fh,s2,dan from ysall where tid = ?;",(tid,))row = cursor.fetchone()c.execute("INSERT INTO ys(s1,fh,s2,dan,cs,cuo) VALUES (?,?,?,?,0,0);", (row[0],row[1],row[2],row[3],))conn.commit()
c.close# 将内存数据库的内容写回磁盘数据库
memory_conn.backup(disk_conn)# 关闭连接
disk_conn.close()
memory_conn.close()

不用1分钟就能完成。

######################################################

因为我原来数据库有2位数的加减乘除算式，所以这次只是将3位数的算式加上去，为保留原有数据的内容，只需要对原来数据中没有的数据加上去就可以，为了提高速度，在生成随机列表后，对原有公式一致的tid删除即可，删除1万来行。

# -*- coding: utf-8 -*-
"""
Created on Thu Feb 13 10:47:16 2025@author: YBK
"""
import sqlite3
import random
from pathlib import Pathresources_folder = Path(__file__).joinpath("../resources/").resolve()
db_filepath = resources_folder.joinpath("qwsx.db")
# 连接到磁盘上的数据库
disk_conn = sqlite3.connect(db_filepath)
# 连接到内存数据库
memory_conn = sqlite3.connect(':memory:')
# 使用 backup API 将磁盘数据库复制到内存数据库
disk_conn.backup(memory_conn)conn = memory_conn
c = conn.cursor()
#如果公式存在，提取tid
cursor = c.execute("SELECT tid from ysall;")
rows = cursor.fetchall()
ysshun = [row[0] for row in rows]
print(len(ysshun))
random.shuffle(ysshun)
#找出所有旧数据库有的tid，在列表中删除掉
cursor = c.execute("SELECT s1,fh,s2,dan from ys;")
rows = cursor.fetchall()
for row in rows:s1 = row[0]fh = row[1]s2 = row[2]cursor = c.execute("SELECT tid from ysall where s1 = ? and fh = ? and s2 = ?;", (s1,fh,s2,))row = cursor.fetchone()if row:ctid = row[0]        ysshun.remove(ctid)print(f'删除{ctid}')else:print(f'{s1},{fh},{s2}不存在') 
#插入删除已经有的公式后没有的数据
for tid in ysshun:cursor = c.execute("SELECT s1,fh,s2,dan from ysall where tid = ?;",(tid,))row = cursor.fetchone()s1 = row[0]fh = row[1]s2 = row[2]dan = row[3]c.execute("INSERT INTO ys(s1,fh,s2,dan,cs,cuo) VALUES (?,?,?,?,0,0);", (s1,fh,s2,dan,))conn.commit()
c.close# 将内存数据库的内容写回磁盘数据库
memory_conn.backup(disk_conn)# 关闭连接
disk_conn.close()
memory_conn.close()