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!
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).
There are multiple problems with the given example. I will address them one by one.
with keyword.execute method, you are assigning some string to an object. (In Python, methods are objects too.)CREATE TABLE statement then a new implicit transaction would be created. And a commit statement must be issued to save the data to the database file. In SQLite, any statement other than SELECT 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
sqlite3 as lite?
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() before conn.close().
k is the data from f.read and sqlite.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.]