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?


Answer: 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`�
    

