kivy 025 sqlite3 数据库

# 导入python内置的SQLite驱动:
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():
    """创建表"""
    # 数据库文件是word.db
    # 如果文件不存在，会自动在当前目录创建:
    conn = sqlite3.connect(db_path)# 连接到SQLite数据库
    cursor = conn.cursor()# 创建一个Cursor:
    with open("word.sql", "r", encoding="GBK") as f_r:
        f = f_r.read()
    # 执行一条SQL语句，创建表:
    cursor.executescript(f)
    # cursor.execute('create table user (id varchar(20) primary key, name varchar(20))')
    # 继续执行一条SQL语句，插入一条记录:
    # cursor.execute('insert into user (id, name) values (\'1\', \'Michael\')')
    # 通过rowcount获得插入的行数:
    row = cursor.rowcount
    print(row)
    
    cursor.close()# 关闭Cursor:
    conn.commit()# 提交事务:
    conn.close()# 关闭Connection:


def insert_data(sql):
    """修改数据"""
    conn = sqlite3.connect(db_path)
    try:
        cursor = conn.cursor()
        # 执行sql
        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):
    """查询数据"""
    # cursor.execute('select * from word where id=?', ('1',))
    conn = sqlite3.connect(db_path)
    try:
        cursor = conn.cursor()
        # 执行sql
        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()

--
-- ��SQLiteStudio v3.1.1 �������ļ� �ܶ� 8�� 13 14:35:08 2019
--
-- �ı����룺System
--
PRAGMA foreign_keys = off;
BEGIN TRANSACTION;

-- ��known
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);

-- ��today
DROP TABLE today;
CREATE TABLE today (id INTEGER PRIMARY KEY AUTOINCREMENT, word_id INTEGER REFERENCES word (id));

-- ��word
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;