CODEWITHSUNDEEP
pythonsqlite
thumbtackthief
thumbtackthief

Reputation: 6211

Download image to sqlite database with Python

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

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.

Upvotes: 1

Views: 3858

Answers (1)

Alex Martelli
Alex Martelli

Reputation: 881695

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

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

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.

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

So, change:

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

into:

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

Upvotes: 4

Related Questions