python 读取sqlite存入文件_从SQLite的BLOB写入到文件使用Python

A clueless Python newbie needs help. I muddled through creating a simple script that inserts a binary file into a blog 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? Thanks!

解决方案

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.