使用 Python 将图像下载到 sqlite 数据库 [英] Download image to sqlite database with Python

问题描述

我正在构建一个 iphone 应用程序，其中包含我从网站上抓取的 200 张图片，这些图片将随机显示给用户.我正在尝试使用 Python 将这些图像存储在 sqlite 数据库中.(注意:到目前为止，在我的研究中，我了解到这可能不是实现我的目标的最佳方式，我在这个链接问题中询问了这一点 在没有互联网连接的情况下使用来自数据库的 iOS 应用程序中的图像.我还在很好奇我做错了什么).

I'm building an iphone app that will include 200 images I've scraped from a website, which will be randomly shown to the user. I'm trying to store those images in a sqlite database with Python. (Note: In my research so far, I've learned that this may not be the best way to achieve my goal, and I'm asking about that in this linked question Use images in an iOS app from a database without internet connection. I'm still curious as to what I'm doing wrong).

    response = requests.get(picture_url) #url is definitely correct
    picture = sqlite3.Binary(response.content)
    con = sqlite3.connect(db_filename)
    cursor = con.cursor()
    sql = '''CREATE TABLE member_data(id integer primary key autoincrement, picture BLOB, name TEXT);'''
    cursor.execute(sql)
    sql = '''INSERT INTO member_data (picture, name) VALUES ("{}", "{}",)'''.format(photo, member_name)
    cursor.execute(sql)
    con.commit()

数据库的其余部分工作正常(文本字段).我正在使用 Sublime 并获得输出 [Decode error - output not utf-8] 但我不确定如何处理这些信息.

The rest of the database works correctly (the text fields). I'm using Sublime and getting the output [Decode error - output not utf-8] but I'm not sure what to do with that information.

推荐答案

代码中的第一个错误是在编写...:

The first mistake in your code is when you write...:

INSERT INTO member_data (picture, name, email, link)

当您刚刚创建表 member_data 具有字段 picture 和 name 但没有 email 或 link -- 然后您尝试将两个值格式化为该 SQL 字符串.我怀疑您没有向我们展示您的实际代码，而是试图简化它的错误尝试.

when you've just created table member_data to have fields picture and name but not email or link -- and then you're trying to format only two values into that SQL string. I suspect you're not showing us your actual code but a mistaken attempt at simplifying it.

你犯的一个更深层次的错误是使用字符串格式来准备你的 sql 语句，而不是你应该使用的占位符.

A much deeper mistake you're making is using string formatting to prepare your sql statement, rather than place-holders as you should.

所以，改变:

sql = '''INSERT INTO member_data (picture, name, email, link) VALUES ("{}", "{}",)'''.format(photo, member_name)
con.cursor().execute(sql)

进入:

sql = '''INSERT INTO member_data (picture, name) VALUES (?, ?)'''
cursor.execute(sql, (photo, member_name))

(你已经有了一个完美的光标，为什么还要再做一个?-)

(you already have a perfectly good cursor, why bother making another?-)

这篇关于使用 Python 将图像下载到 sqlite 数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！