python sqlite3

import sqlite3
from datetime import datetime
#sqlite3

#初始化sqlite3 内存表
conn = sqlite3.connect(':memory:', check_same_thread=False)
#开启外键约束
conn.execute('pragma foreign_keys=ON')
#创建表 关联外键必须设置主键
sql = """CREATE TABLE IF NOT EXISTS demo(name TEXT, sex TEXT, heigth INTEGER, idt INTEGER not null primary key, time datetime)"""
#添加外键操作相关表
sql2 = """CREATE TABLE IF NOT EXISTS records(id INTEGER not null, idt INTEGER not null, test TEXT, FOREIGN KEY(idt) REFERENCES demo(idt) ON DELETE CASCADE ON UPDATE CASCADE)"""
#执行
conn.execute(sql2)
conn.execute(sql)
#提交事务
conn.commit()
#插入数据
time = datetime.now()
data = [('zhangsan', 'male', 180, 1, time), ('lisi', 'woman', 150, 2, time), ('wangwu', 'male', 190, 3, time)]
sql = 'insert into demo values(?, ?, ?, ?, ?)'

conn.executemany(sql, data)
conn.execute('insert into records values(:id, :idt, :test)', {'id': 1, 'idt': 3, 'test': 'test'})
conn.commit()
#删除测试
conn.execute('delete from demo where idt=3')
#取所有数据
sql = 'select * from records;'
cursor = conn.cursor()

cursor.execute(sql)

conn.commit()
#关联外键数据同时删除
print(cursor.fetchall())

#联合查询
#关闭连接
conn.close()