Writing blob from SQLite to file using Python

Question

I am creating a simple script that inserts a binary file into a blob field in a SQLite database:

import sqlite3
conn = sqlite3.connect('database.db')
cursor = conn.cursor()
input_note = raw_input(_(u'Note: '))
    input_type = 'A'
    input_file = raw_input(_(u'Enter path to file: '))
        with open(input_file, 'rb') as f:
            ablob = f.read()
            f.close()
        cursor.execute("INSERT INTO notes (note, file) VALUES('"+input_note+"', ?)", [buffer(ablob)])
        conn.commit()
    conn.close()

Now, I need to write a script that grabs the contents of the blob field of a specific record and writes the binary blob to a file. In my case, I use the SQLite database to store .odt documents, so I want to grab and save them as .odt files. How do I go about that?

No error. I just want to figure out how to write a contents of a blob field to a file. — dmpop
– dmpop, Commented Jul 31, 2010 at 18:29

Eric Fortin · Accepted Answer · 2010-08-03 02:27:05Z

38

Here's a script that does read a file, put it in the database, read it from database and then write it to another file:

import sqlite3
conn = sqlite3.connect('database.db')
cursor = conn.cursor()

with open("...", "rb") as input_file:
    ablob = input_file.read()
    cursor.execute("INSERT INTO notes (id, file) VALUES(0, ?)", [sqlite3.Binary(ablob)])
    conn.commit()

with open("Output.bin", "wb") as output_file:
    cursor.execute("SELECT file FROM notes WHERE id = 0")
    ablob = cursor.fetchone()
    output_file.write(ablob[0])

cursor.close()
conn.close()

I tested it with an xml and a pdf and it worked perfectly. Try it with your odt file and see if it works.

edited Aug 3, 2010 at 2:27

answered Aug 2, 2010 at 12:14

Eric Fortin

7,6132 gold badges28 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

dmpop Over a year ago

Thank you! The code saves the blob as a file, but the file seems to be damaged, i.e., the application (OpenOffice.org Writer in my case) refuses to open it. I tried to save and extract a plain .txt file, but it came out empty. Am I doing something wrong? Thanks!

Eric Fortin Over a year ago

Run a diff tool on the original file and the saved-and-then-loaded one to see what are the differences. If you can post your db schema, I might be able to give it a try.

dmpop Over a year ago

Thank you so much for helping me out. The database schema is simple: one table a handful of fields. CREATE_SQL = \ "CREATE TABLE notes (\ id INTEGER PRIMARY KEY UNIQUE NOT NULL,\ note VARCHAR(1024),\ file BLOB,\ tags VARCHAR(256));" cursor.execute(CREATE_SQL) conn.commit() I'll try to run diff as soon as I have access to my machine. Thanks!

Eric Fortin Over a year ago

I uploaded another scripts that does what you're looking for.

Collectives™ on Stack Overflow

Writing blob from SQLite to file using Python

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related