current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 5 down vote favorite
3

I trying to write a simple Python script that inserts .odt documents into an SQLite database. Here what I have 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!

share|improve this question
add comment

3 Answers

up vote 11 down vote accepted

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).

share|improve this answer
    
Perfect! Thank you so much for your help! –  dmpop Jul 22 '10 at 17:10
    
@dmpop, you're welcome! –  Alex Martelli Jul 22 '10 at 17:54
    
Nice example, I have a similar question (stackoverflow.com/questions/3915888/…), I would be very grateful if you could take a look at it. –  Thiado de Arruda Oct 12 '10 at 15:22
    
@Thiado, done, check your Q. –  Alex Martelli Oct 13 '10 at 21:11
    
I get the following error "sqlite3.InterfaceError: Error binding parameter 7 - probably unsupported type." –  Hadi Jan 25 '13 at 4:50
show 1 more comment
up vote 3 down vote

Problems:

  1. You didn't show the full code that you ran. You shouldn't leave answerers guessing what things like sqlite.Binary(k).

  2. Fundamental problem: You didn't commit your transaction. Use conn.commit() before conn.close().

share|improve this answer
add comment
up vote 2 down vote

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 than 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 will write a LibreOffice document to a Docs table of an SQLite database:

#!/usr/bin/python
# -*- coding: utf-8 -*-

import sqlite3 as lite

fl = open('book.odt', 'rb')

with fl:
    data = fl.read()

con = lite.connect('test.db')

with con:

    cur = con.cursor()     

    cur.execute("CREATE TABLE IF NOT EXISTS Docs(Data BLOB)")

    sql = "INSERT INTO Docs(Data) VALUES (?)" 
    cur.execute(sql, (lite.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.

share|improve this answer
1  
Why access sqlite3 as lite? –  Tshepang Mar 3 '13 at 10:53
    
This is just my style. It shortens the code a bit. –  Jan Bodnar Mar 3 '13 at 10:56
    
But 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. –  Tshepang Mar 3 '13 at 10:59
    
Twice, 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 Mar 3 '13 at 11:10
    
is kool; nice answer BTW –  Tshepang Mar 3 '13 at 11:15
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.