import sqlite3
import os.path
import logging
base_dir = os.path.dirname(os.path.abspath(__file__))
db_path = os.path.join(base_dir, "word.db")
print(base_dir)
def create_table():
"""创建表"""
conn = sqlite3.connect(db_path)
cursor = conn.cursor()
with open("word.sql", "r", encoding="GBK") as f_r:
f = f_r.read()
cursor.executescript(f)
row = cursor.rowcount
print(row)
cursor.close()
conn.commit()
conn.close()
def insert_data(sql):
"""修改数据"""
conn = sqlite3.connect(db_path)
try:
cursor = conn.cursor()
response = cursor.execute(sql)
cursor.close()
conn.commit()
return response
except Exception as e:
logging.warning('Exception:%s' % e)
finally:
conn.close()
def select_data(sql):
"""查询数据"""
conn = sqlite3.connect(db_path)
try:
cursor = conn.cursor()
cursor.execute(sql)
response = cursor.fetchall()
cursor.close()
return response
except Exception as e:
logging.warning('Exception:%s' % e)
finally:
conn.close()
if __name__ == '__main__':
"""创建表"""
create_table()
PRAGMA foreign_keys = off;
BEGIN TRANSACTION;
DROP TABLE known;
CREATE TABLE known (id INTEGER PRIMARY KEY AUTOINCREMENT, word_id INTEGER REFERENCES word (id) NOT NULL, add_times INT (10) DEFAULT (0), create_date DATE);
DROP TABLE today;
CREATE TABLE today (id INTEGER PRIMARY KEY AUTOINCREMENT, word_id INTEGER REFERENCES word (id));
DROP TABLE word;
CREATE TABLE word (id INTEGER PRIMARY KEY AUTOINCREMENT, word VARCHAR (20) NOT NULL UNIQUE, explains VARCHAR (20) NOT NULL, phonetic VARCHAR (20) NOT NULL, examples_en VARCHAR (200), examples_cn VARCHAR (200), known_time DATETIME, add_times INT (2) DEFAULT (0), create_time DATETIME NOT NULL, is_delete BOOLEAN DEFAULT False NOT NULL);
DROP TABLE user;
CREATE TABLE user (id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR (20) UNIQUE NOT NULL DEFAULT C0C, word_num INT (4) DEFAULT (50), is_month VARCHAR (20) DEFAULT Month, head_image VARCHAR (200) NOT NULL, background_image VARCHAR (200) NOT NULL);
INSERT INTO user (id, name, word_num, is_month, head_image, background_image) VALUES (1, 'C0C', 50, 'Month', 'image/c0c.jpg', 'image/back.jpg');
COMMIT TRANSACTION;
PRAGMA foreign_keys = on;