Python内置模块sqlite3操作数据库

字段类型

插入数据

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()