In python, you can quickly access or create a SQLite database by using the Sqlite3 module as mentioned above:
db_filename = r"C:\TEMP\sqllist\myDB.db"
db_is_new = not os.path.exists(db_filename)
conn = sqlite3.connect(db_filename)
cursor = conn.cursor()
print 'Need to create schema'
tableCreation = """create table data (
id integer primary key autoincrement not null,
print 'Database exists, assume schema does, too.'
Let's assume we want to store a zip file inside the database. To do this, open the reference to the file and save it as binary data. The data will be stored in the field called 'File' as created above.
zipFile = r"C:\TEMP\sqllist\extent.zip"
with open(zipFile, "rb") as input_file:
ablob = input_file.read()
cursor.execute("INSERT INTO data (File,Type,FileName) VALUES(?,'zip','" + zipFile + "')", [sqlite3.Binary(ablob)])
Here we open the zip file and using an INSERT command put the data into the data table.
Next, perform a simple SELECT statement to show that the row exists in the table:
cursor.execute("select * from data")
for row in cursor:
This will yield something like this:
, u'zip', u'C:\\TEMP\\sqllist\\extent.zip')
To get the Blob data out of the database, use the SELECT SQL statement and the open() to create a new zip file:
with open(r"c:\temp\sqllist\Output.zip", "wb") as output_file:
cursor.execute("SELECT file FROM data WHERE id = 1")
ablob = cursor.fetchone()
The code above creates a new file called 'output.zip' and writes the information from the blob field to the file. Then the script closes the database connection and cursor connection.
Easy as Π