Excerpt
Base64 provides a way to encode binary image data as text that can be easily stored and retrieved from SQLite databases using Python.
SQLite is a simple, self-contained SQL database engine that is commonly used in mobile and web apps. While SQLite only supports basic data types like text, numbers, and blobs, it can also be used to store image data encoded as Base64 strings.
In this post, we’ll look at what Base64 encoded images are, why you may want to store them in SQLite, and how to save and retrieve them from a database table using Python.
What is a Base64 Encoded Image?
Base64 is an encoding scheme that converts binary data like image bytes into a plain ASCII text format. It represents each binary byte of data as a 6-bit character mapped to a character set of A-Z, a-z, 0-9, +, and /.
For example, a small 8x8 pixel PNG image encodes to a long Base64 string that looks something like:
1iVBORw0KGgoAAAANSUhEUgAAAAgAAAAICAYAAADED76LAAAABGdBTUEAALGP
2C/xhBQAAAAlwSFlzAAAOwgAADsIBFShKgAAAABh0RVh0U29mdHdhcmUAcGFp
3bnQubmV0IDQuMS41ZEdYUwAAAAF1aUNDVVVhZCGsJgAAAEdJREFUKFONy0ES
4ACAIQ7FY/P9ffNkH4lM1vA1hYGOn1ykcEw1BrOxXCAAAAABJRU5ErkJggg==
This allows the raw binary pixel data to be represented in a text format that can be handled almost anywhere.
Why Store Images in SQLite?
There are a few potential advantages to storing images in SQLite compared to the filesystem:
- SQLite natively supports BLOB data types to store binary data
- Keep images coupled to database tables and records
- Portable - entire database is a single file
- Simple to build small local apps and prototypes
The BLOB data type in SQLite maps nicely to handling the Base64 encoded text representation of an image.
Storing directly in the database can make sense for small apps where you want to keep all the data together, versus spreading it across files and directories.
Steps to Store a Base64 Image in SQLite
Let’s go through a Python example of storing a Base64 image in an SQLite table with a BLOB column type.
First, import the SQLite library:
1import sqlite3
Next, connect to an existing database file or create a new one:
1conn = sqlite3.connect('images.db')
Create a table with a BLOB column to hold the image data:
1conn.execute('''CREATE TABLE images
2 (id INTEGER PRIMARY KEY, image BLOB)''')
Take a Base64 image string and convert it to binary data using Python’s base64 module:
1import base64
2
3base64_img = 'iVBORw0KG...' # truncated example string
4image_bytes = base64.b64decode(base64_img)
Insert this binary data into the BLOB column:
1conn.execute("INSERT INTO images (image) VALUES (?)", [image_bytes])
That covers the basics of getting a Base64 image into SQLite!
Retrieving and Displaying the Images
To retrieve the images, query the table and fetch the Base64 text from the BLOB column:
1cursor = conn.execute("SELECT image FROM images")
2base64_img = cursor.fetchone()[0]
Decode this back to binary and you can write it out as an image file or display it in an app:
1image_bytes = base64.b64decode(base64_img)
2
3with open('result.png', 'wb') as out_file:
4 out_file.write(image_bytes)
Pros and Cons of Storing Images in SQLite
Some upsides to storing images in SQLite:
- Easy to build using Python and SQLite libraries
- No extra servers or infrastructure needed
- All data contained in local database file
Downsides to consider:
- Not optimized for storing tons of large images
- No compression, caching, or other optimizations
- Slow for apps with high image throughput
Overall SQLite works well for lightweight local apps with minimal image data. But for heavy media applications, a dedicated file storage service or hosted database like AWS S3 would be more robust.
Example Python Code
Here is some complete Python code that:
- Encodes a sample image to Base64
- Inserts into an SQLite table
- Retrieves and decodes the image
- Writes the result to a file
1import sqlite3
2import base64
3
4# Encode sample image
5with open('image.png', 'rb') as image_file:
6 image_bytes = image_file.read()
7 base64_img = base64.b64encode(image_bytes).decode('utf-8')
8
9# Insert into SQLite
10conn = sqlite3.connect('images.db')
11conn.execute('''CREATE TABLE images
12 (id INTEGER PRIMARY KEY, image BLOB)''')
13conn.execute("INSERT INTO images (image) VALUES (?)", [base64_img])
14
15# Retrieve from SQLite
16cursor = conn.execute("SELECT image FROM images")
17fetched = cursor.fetchone()[0]
18
19# Decode and save image
20image_bytes = base64.b64decode(fetched)
21with open('fetched_image.png', 'wb') as out_file:
22 out_file.write(image_bytes)
This saves the original image as fetched_image.png after roundtripping through Base64 encoding and SQLite.
Summary
Encoding binary image data with Base64 makes it easy to store and retrieve from SQLite databases using Python. This can be handy for small local apps where you want to keep images alongside other related data in the database.
SQLite should work fine for simple cases with a handful of small images. But for real production use cases dealing with media files and images, you would want a dedicated storage system designed for binary data at scale.
Overall, Base64 + SQLite provides a zero dependency solution for experimenting with persistent local image storage in hobby projects and prototypes.
