Problems inserting file data into sqlite database using python
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 IN开发者_JAVA百科TO 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?
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`�
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
精彩评论