Sqlite3 is the python module that creates a self contained, server less, zero-configuration, and transactional SQL database.
SQLite is an embedded SQL database engine. Unlike most other SQL databases, SQLite does not have a separate server process. SQLite reads and writes directly to ordinary disk files. A complete SQL database with multiple tables, indices, triggers, and views, is contained in a single disk file. The database file format is cross-platform - you can freely copy a database between 32-bit and 64-bit systems. More can be found out about the database here.
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()
if db_is_new:
print 'Need to create schema'
tableCreation = """create table data (
id integer primary key autoincrement not null,
File blob,
Type text,
FileName text);"""
conn.execute(tableCreation)
else:
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)])
conn.commit()
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:
print row
del row
This will yield something like this:
>>> (1, , 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()
output_file.write(ablob[0])
cursor.close()
conn.close()
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 Π
