字段类型

字段名数据类型nametext字符串gpareal小数ageinteger整数photoblob二进制数据（如图片）birthdaydate日期（本质上是text）register timedatetime日期+时间（本质上是text）

插入数据

import sqlite3
db = sqlite3.connect('test.db')  # 连接数据库，若不存在则自动创建
cursor = db.cursor()  # 获取光标
sql = """create table if not exists student (id integer primary key,
name text, gpa real, birthday date, age integer, picture blob)"""
cursor.execute(sql)  # 执行SQL命令
mylist = [(100, '张三', '3.25', '2002-01-01', 11, None),
(101, '李四', '3.35', '2003-01-01', 17, None)]
for s in mylist:
cursor.execute('replace into student values(?,?,?,?,?,?)',
(s[0], s[1], s[2], s[3], s[4], s[5]))
db.commit()  # 真正写入数据库
cursor.close()
db.close()

查询数据

import sqlite3
db = sqlite3.connect('test.db')
cursor = db.cursor()
sql = '''select * from student'''
cursor.execute(sql)
x = cursor.fetchone()  # 获取满足条件的第一条记录
x2 = cursor.fetchall()  # 获取满足条件的所有记录
cursor.execute("select * from student where name='Jack'")
x3 = cursor.fetchone()
if x3 == None:
print("can't find Jack")
cursor.close()
db.close()

修改数据

import sqlite3
db = sqlite3.connect('test.db')
cursor = db.cursor()
sql = '''update student set gpa=?, age=? where name=?'''
cursor.execute(sql, (4.0, 22, '张三'))  # 元组三个元素分别对应三个?
db.commit()
cursor.close()
db.close()

写入二进制数据

从文件读取

import sqlite3
with open("logo.jpg", 'rb') as f:  # 二进制方式打开图片
img = f.read()
db = sqlite3.connect('test.db')
cursor = db.cursor()
sql = '''update student set picture=? where name="张三"'''
cursor.execute(sql, (img,))
db.commit()
cursor.close()
db.close()

从网络读取

import sqlite3
import requests
url_img = "https://c-ssl.dtstatic.com/uploads/blog/202205/07/20220507115721_79dc7.thumb.700_0.jpg_webp"
img_stream = requests.get(url_img, stream=True).content
db = sqlite3.connect('test.db')
cursor = db.cursor()
sql = '''update student set picture=? where name="张三"'''
cursor.execute(sql, (img_stream,))
db.commit()
cursor.close()
db.close()

读取二进制数据

import sqlite3
db = sqlite3.connect('test.db')
cursor = db.cursor()
sql = 'select name,picture from student'
cursor.execute(sql)
x = cursor.fetchall()
for p in x:
if p[1] == None:
continue
with open(p[0] + '.jpg', 'wb') as f:  # 照片写入文件
f.write(p[1])
cursor.close()
db.close()