Problems inserting file data into sqlite database using python

Question

I'm trying to open an image file in python and add that data to an sqlite table. I created the table using: "CREATE TABLE "images" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL , "description" VARCHAR, "image" BLOB );"

I am trying to add the image to the db using:

imageFile = open(imageName, 'rb')
b = sqlite3.Binary(imageFile.read())
targetCursor.execute("INSERT INTO images (image) values(?)", (b,))
targetCursor.execute("SELECT id from images")
for id in targetCursor:
    imageid= id[0]

targetCursor.execute("INSERT INTO %s (questionID,imageID) values(?,?)" % table, (questionId, imageid))

When I print the value of 'b' it looks like binary data but when I call: 'select image from images where id = 1' I get '????' printed to the console. Anyone know what I'm doing wrong?

What version of Python are you using?

dan04
– dan04

2010-04-25 03:48:16 +00:00
Commented Apr 25, 2010 at 3:48 — dan04
– dan04, Commented Apr 25, 2010 at 3:48

Matthew Flaschen · Accepted Answer · 2010-04-25 04:08:39Z

It works for me with Python 2.6.4, pysqlite (sqlite3.version) 2.4.1, and a png test image. You have to unpack the tuple.

>>> import sqlite3                                                    
>>> conn = sqlite3.connect(":memory:")                                
>>> targetCursor = conn.cursor()                                      
>>> imageName = "blue.png"                                            
>>> imageFile = open(imageName, 'rb')                                 
>>> b = sqlite3.Binary(imageFile.read())                              
>>> print b                                                           
�PNG                                                                  
▒                                                                     
IHDR@%                                                                
      ��sRGB��� pHYs                                                  

                    ��▒tIME�
0�\"▒'S�A�:hVO\��8�}^c��"]IEND�B`�
>>> targetCursor.execute("create table images (id integer primary key, image BLOB)")
<sqlite3.Cursor object at 0xb7688e00>
>>> targetCursor.execute("insert into images (image) values(?)", (b,))
<sqlite3.Cursor object at 0xb7688e00>
>>> targetCursor.execute("SELECT image from images where id = 1")
<sqlite3.Cursor object at 0xb7688e00>
>>> for image, in targetCursor:
...     print image
...
�PNG
▒
IHDR@%
      ��sRGB��� pHYs

                    ��▒tIME�
0�\"▒'S�A�:hVO\��8�}^c��"]IEND�B`�

Tylerc230 · Accepted Answer · 2010-04-26 04:04:49Z

0

Yeah its weird, when I query the database in python, after inserting the binary data, it shows me the data was successfully inserted (it spews binary data to the screen). When I do: sqlite3 database_file.sqlite "SELECT image from images" on the command line, that's when I see the '????'. Perhaps that's just how the 'sqlite3' command prints binary data? That doesn't seem right. I'm using python 2.6.1

edited Apr 26, 2010 at 4:04

answered Apr 25, 2010 at 5:52

Tylerc230

2,8941 gold badge28 silver badges31 bronze badges

1 Comment

Tylerc230 Over a year ago

Turns out that just how sqlite3 represents binary data on the command line.

Collectives™ on Stack Overflow

Problems inserting file data into sqlite database using python

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related