import sqlite3
import io
from PIL import Image

# 连接到SQLite数据库
conn = sqlite3.connect('/Users/alvin/memos_prod.db')
cursor = conn.cursor()

# 查询不是webp格式的图片数据
cursor.execute("SELECT id, filename, `blob`, type, size FROM resource WHERE type like 'image%'  AND type != 'image/webp'")

# 获取查询结果的所有行
rows = cursor.fetchall()

# 遍历查询结果
for row in rows:
    # 解析每一行的数据
    image_id = row[0]
    filename = row[1]
    blob = row[2]
    old_type = row[3]
    old_size = row[4]

    # 将BLOB数据转换为PIL Image对象
    image = Image.open(io.BytesIO(blob))

    # 创建一个新的内存缓冲区
    new_blob = io.BytesIO()

    # 将图片压缩为webp格式并保存到新的内存缓冲区
    image.save(new_blob, format='webp', quality=50)

    # 获取新的图片数据和相关信息
    new_blob_data = new_blob.getvalue()
    new_size = len(new_blob_data)
    new_filename = filename.rsplit('.', 1)[0] + '.webp'
    new_type = 'image/webp'

    # 日志打印
    print(f'{image_id}压缩前: {old_size}, 压缩后：{new_size}')

    # 更新数据库中的记录
    cursor.execute("UPDATE resource SET filename=?, blob=?, type=?, size=? WHERE id=?", (new_filename, new_blob_data, new_type, new_size, image_id))

# 提交事务并关闭连接
conn.commit()
conn.close()