- copy源数据库至本地 sqlite3 source.db
- .ouput tmp.sql
- .dump
- .quit
-
# sql创建新表 tmp.sql PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE oprator (oprator TEXT (6), department TEXT (6)); CREATE TABLE results (id INTEGER PRIMARY KEY, drawing TEXT, proc, date DATETIME, shift, opra, qc, machine, banch, repnum, remark, item, lsl, usl, yesno, res1, res2, res3, res4, res5); COMMIT;
- sqlite3 new2.db
- .read tmp.sql
- .quit
- run RepairSQL.py5
-
# coding=utf-8 import sqlite3 import numpy as np path = r'e:\Qua.db' sql = 'SELECT * FROM results' sql2 = ("INSERT or IGNORE INTO results (drawing,proc,date,shift,opra,qc,machine,banch,repnum,remark,item,lsl,usl," "yesno,res1,res2,res3,res4,res5) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);") ''' sql2='UPDATE results set id=?' sql3='SELECT * FROM results WHERE rowid=?' sql4='DELETE FROM results WHERE ' ''' con = sqlite3.connect(path) cur = con.cursor() cur.execute(sql) ar = cur.fetchall() con.commit() con.close() # np_ar = np.array(ar) # arr = ar[100:] # print(arr) # 事先copy一份new2.db,并清空results数据 con = sqlite3.connect(r'e:\new2.db') con.execute('BEGIN IMMEDIATE') cur = con.cursor() for a in arr: x = a[1:] sql = "INSERT or IGNORE INTO results (drawing,proc,date,shift,opra,qc,machine,banch,repnum,remark,item,lsl,usl,yesno,res1,res2,res3,res4,res5) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);" cur.execute(sql, x) con.commit() con.close()
CREATE INDEX date_index ON results ( date ASC ); CREATE INDEX drawing_index ON results ( drawing ASC ); CREATE INDEX machine_index ON results ( machine ASC ); CREATE INDEX multi ON results ( proc ASC, drawing ASC, date ASC ); CREATE INDEX proc_index ON results ( proc ASC ); CREATE INDEX ProcMachineDate ON results ( proc ASC, machine ASC, date ASC );
使用python复制sqlite3数据库,解除database disk is malfored锁定
于 2023-10-11 13:48:33 首次发布