使用python复制sqlite3数据库，解除database disk is malfored锁定

1. copy源数据库至本地 sqlite3 source.db
   • .ouput tmp.sql
   • .dump
   • .quit

2. # 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;

   1. sqlite3 new2.db
   2. .read tmp.sql
   3. .quit
3. run RepairSQL.py5

4. # 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
   );