You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.

43 lines
1.1 KiB

import sqlite3
conn = sqlite3.connect("C:\\edudb_430300000000.db")
cur = conn.cursor()
cur.execute("select name,sql from sqlite_master where type = 'table' and name not like '%_temp%'")
list = cur.fetchall()
count = 0
for a in list:
table_name = a[0]
sql = a[1].replace('REAL', 'TEXT')
tmpTable = table_name + '_temp'
sql = sql.replace(table_name, tmpTable)
# 创建表
dropSql = 'drop table if exists ' + tmpTable
cur.execute(dropSql)
cur.execute(sql)
sql = 'INSERT INTO ' + tmpTable + ' SELECT * FROM ' + table_name
cur.execute(sql)
conn.commit()
count = count + 1
# 添加recordyear字段
sql = 'ALTER TABLE '+tmpTable+' add recordyear INTEGER DEFAULT 2021'
cur.execute(sql)
conn.commit()
# 删除旧表
sql = 'drop table ' + table_name
cur.execute(sql)
conn.commit()
# 改表名
sql = 'ALTER TABLE ' + tmpTable + ' RENAME TO ' + table_name
cur.execute(sql)
conn.commit()
print("成功完成表数量:" + str(count) + ",共" + str(len(list)) + "个。")
cur.close()
conn.close()