Insert binary file in SQLite database with Python
Solution 1
Not sure what is that sqlite.Binary
you're using, but, anyway, here's a working example:
import sqlite3
# let's just make an arbitrary binary file...
with open('/tmp/abin', 'wb') as f:
f.write(''.join(chr(i) for i in range(55)))
# ...and read it back into a blob
with open('/tmp/abin', 'rb') as f:
ablob = f.read()
# OK, now for the DB part: we make it...:
db = sqlite3.connect('/tmp/thedb')
db.execute('CREATE TABLE t (thebin BLOB)')
db.execute('INSERT INTO t VALUES(?)', [buffer(ablob)])
db.commit()
db.close()
# ...and read it back:
db = sqlite3.connect('/tmp/thedb')
row = db.execute('SELECT * FROM t').fetchone()
print repr(str(row[0]))
When run with Python 2.6, this code shows, as expected and desired: '\x00\x01\x02\x03\x04\x05\x06\x07\x08\t\n\x0b\x0c\r\x0e\x0f\x10\x11\x12\x13\x14\x15\x16\x17\x18\x19\x1a\x1b\x1c\x1d\x1e\x1f !"#$%&\'()*+,-./0123456'
Note the need to use buffer
to insert the blob, and str
to read it back as a string (since it uses the buffer
type as a result as well) -- if you're just going to write it to disk the latter passage would not be needed (since the write
method of files does accept buffer objects just as well as it accepts strings).
Solution 2
There are multiple problems with the given example. I will address them one by one.
- There is no error checking. We either need to use the try/except/finally
construct or use the
with
keyword. - Python methods are not like C# properties. You are not running the
execute
method, you are assigning some string to an object. (In Python, methods are objects too.) - Very important is that your code is subject to SQL Injection attacks. We should never build SQL statements using Python string operations. We should always use placeholders.
- The example is incomplete. Which leads to a tricky issue. Supposing, that there was a
CREATE TABLE
statement then a new implicit transaction would be created. And acommit
statement must be issued to save the data to the database file. In SQLite, any statement other thanSELECT
starts an implicit transaction. (Some databases, like MySQL, are in the autocommit mode by default. This is not true for SQLite.)
Here is a proper working example, which writes a LibreOffice document to a docs
table of an SQLite database:
#!/usr/bin/env python
import sqlite3
def readData():
fl = open('book.odt', 'rb')
with fl:
data = fl.read()
return data
con = sqlite3.connect('test.db')
with con:
cur = con.cursor()
cur.execute("CREATE TABLE IF NOT EXISTS docs(Data BLOB)")
data = readData()
sql = "INSERT INTO docs(Data) VALUES (?)"
cur.execute(sql, (sqlite3.Binary(data), ))
The book.odt file is located in the current working directory. We did not call the commit
method manually, since this is handled by the with
keyword behind the scenes.
Edit removed the lite alias
Solution 3
Problems:
You didn't show the full code that you ran. You shouldn't leave answerers guessing what things like
sqlite.Binary(k)
.Fundamental problem: You didn't commit your transaction. Use
conn.commit()
beforeconn.close()
.
dmpop
Updated on September 22, 2021Comments
-
dmpop over 2 years
I'm trying to write a simple Python script that inserts .odt documents into an SQLite database. Here is what I have done so far, but it doesn't seem to work:
f=open('Loremipsum.odt', 'rb') k=f.read() f.close() cursor.execute="INSERT INTO notes (note) VALUES ('%s')" %(sqlite.Binary(k)) cursor.close() conn.close()
I don't get any error messages, but as far as I can see the record is not inserted. What am I doing wrong? Also, how can I extract the stored document back? Thanks!
-
dmpop almost 14 yearsPerfect! Thank you so much for your help!
-
Thiago Padilha over 13 yearsNice example, I have a similar question (stackoverflow.com/questions/3915888/…), I would be very grateful if you could take a look at it.
-
Hadi over 11 yearsI get the following error "sqlite3.InterfaceError: Error binding parameter 7 - probably unsupported type."
-
tshepang about 11 yearsWhy access
sqlite3
aslite
? -
tshepang about 11 yearsBut you only use it once. It's too strange... I don't see a win. If we do a character count, it does not shorten the code.
-
Jan Bodnar about 11 yearsTwice, actually. This is more a matter of a personal taste. Rather than a strict rule. Say in two years, there will be sqlite4 module. And we don't need to correct all the occurrences of the module name in older code. However, such things are irrelevant in small code examples.
-
Jan Bodnar about 11 yearsBinary is an alias for Python buffer.
-
leetNightshade about 11 yearsHaving it as sqlite instead of lite would improve readability and still be "future proof" with your method. Or just have it as 'sql', as 'lite' doesn't really mean anything and 'sql' is a standard for database manipulation.
-
Gleno over 9 yearsI usually shorten it to "db", because having a number in a name feels so wrong somehow.
-
toonarmycaptain over 3 years...
k
is the data fromf.read
andsqlite.Binary
is a function in the sqlite3 module, what's to guess about that? [NB I'm not sure what sqlite.Binary does over Path.read_bytes(), but what OP is running seems evident there.] -
Kennet Celeste about 3 yearsbuffer in python3 is now memoryview