How to Store Multimedia Files in a SQLite3 Database with Python

How to Store Multimedia Files in a SQLite3 Database with Python

If you're a developer who is building out a website that allows users to upload image files for reasons such as updating a profile picture, sharing images with other users, or utilizing other multimedia files, you will find yourself wondering, "what's the safest way to save their data?".

You could store the files directly in the server’s file system, integrate with APIs from cloud storage platforms, or you can learn how to store the information in your database!

In this tutorial, you will learn about blob (binary large object) data and why you need to convert your multimedia files to binary objects in order to store them properly in a database. Then you will learn how to retrieve the blobs and convert them back into regular files for use by your application.

Let's get started!

Tutorial requirements

• Python 3.6 or newer. If your operating system does not provide a Python interpreter, you can go to python.org to download an installer.
• SQLite3 in order to open the SQLite shell and make executable scripts.
• Some prior knowledge in SQL logic or a willingness to learn.

Set up the environment

Create a project directory in your terminal called “sqlite3_blob_data” to follow along. If you are using a Mac or Unix computer enter these commands in your terminal:

If you are on a Windows machine, enter the following commands in a prompt window:

Create a file named app.py and copy and paste the following code to import the required modules to make this project work:

You will be writing functions and learning about how Python deals with blob data in this file, but for now, you have a simple main() function that prints "Hello World!" when you enter the command python app.py in your terminal.

NOTE: Depending on what distribution of Python you are on, you might have to specify python3 instead of python.

If you haven't done so already, make sure that SQLite3 is installed. SQLite should already be installed on macOS but if there's trouble, you can refer to the SQLite Installation docs to download the appropriate zip files to extract the SQLite executable.

Set up your database

You will be setting up SQLite3, which is serverless and portable because the database is stored in a file, making it a nifty choice that's easy to get started with. Plus, there is no complex setup for Python developers because support for SQLite3 is built into the interpreter. If you decide to change your database to MySQL or PostgreSQL in the future, then transitioning from SQLite will make the process very familiar to you. You can also learn more about connecting a Twilio Python Project to SQLite3 in this article.

Start up the SQLite3 prompt on your terminal by entering the command sqlite3 app.db. This will create a database file named app.db where you can define the tables for your data to be stored.

This database will be simple and only store the full path file name in text form under file_name and the file blob, once created, under file_blob. These entries are represented by a simple id integer type. Copy and paste the following code into the SQLite3 prompt:

Hold the "Ctrl" key on your keyboard then press the lowercase "d" key at the same time to exit the SQLite 3 command prompt.

Notice that the app.db file was added to your project directory, but don't expect to be able to view the contents of the file since it's written in binary format.

Select file to insert into the database

For the purposes of this tutorial, you will choose a file on your computer and the application will convert it to blob data.

With that said, you have to copy the full path name manually from the location. I'll be using a file from the Documents folder on my computer for this tutorial, so my pathname for the image file would look like this:

Keep in mind that your file path name might look like "C:\Users\diane\Documents\<FILE_NAME_HERE>" if you are using a Windows machine. 

Feel free to use your personal images for the tutorial, however here is the picture titled DRAW_THE_OWL_MEME.png that I will be referencing throughout the article. You can download it to your Documents folder.

Head over to the app.py file and paste the following line at the bottom of the main() function so that you are prompted to enter the full file path:

Now that you have an image file to select and enter into your application, it's time to write up the functions to convert the image path name to blob data in the database.

Insert blob data to the database

Now that the database has been established, the Python application needs to implement CRUD (Create, Read, Update, Delete) operations on the database. In order to do so, you must establish a connection to the app.db file from Python.

Copy and paste the following code in the app.py file above def main() to create the insert_into_database function. This is where you can pass in the file_path_name and file_blob parameters so that they are committed into the database. After committing to the database, the last_updated_entry variable records the id of the inserted row and returns it to the main application so that you can use it to retrieve the file afterwards:

A conn object is created to use the connect() function from the SQLite3 module and print "[INFO] : Successful connection!" if it worked. This conn object also provides the Connection object that represents the app.db database.

The connection exists so that you can interact with the database using CRUD operations throughout the project. The connection will close at the end of the session, but if it failed to connect in the first place, the program will print an error and return a message at the end saying "Oh shucks, something is wrong here.".  

To prevent SQL injection attacks such as this one, the ? placeholder is used in the sql_insert_file_query so that the file_path_name and file_blob values can be added to the uploads table safely.

Understand blob data

So you set everything up to start the application and insert information into the database. However, what exactly is blob data?

When working with multimedia objects such as images, audio files, and more, the data is stored as a Binary Large OBject (BLOB). You wouldn't be able to store image files if you upload it directly to the database, thus, you need to read the file into a variable first.

This means that you'll have to define a few functions:

• convert_into_binary - the file is converted into binary immediately after the path to the file is defined.
• write_to_file - the binary data is converted back to a file that can be opened and interpreted on your computer.
• read_blob_data - the function that receives the blob data and determines the next steps for it.

Be mindful that blob data can be huge depending on what you upload. You will not have to worry so much about the size of your database now since you will just be working with images.

On that note, let's get started with writing the functions to handle blob data.

Create blob data

In this section, you will create a file_blob to temporarily store the binary data of the full file path name of the DRAW_THE_OWL_MEME.png or preferred image from earlier.

Copy and paste the following code in the app.py file above def main() to create the convert_into_binary function:

The open() function returns the file object in binary format, as indicated by the rb parameter.

The with statement is helpful here because it will automatically close the file after performing the necessary actions.

Scroll down to the def main() function and paste in the highlighted line so that you can store the returned blob:

If you are curious to know what the blob looks like, you can add the line below. Remember that blob data is huge so seeing the last 100 characters can be sufficient for you to get an idea:

Now that the blob data and full file path name are known, you can go ahead and call the insert_into_database function below and commit the first entry. Your def main() should now look like this:

Retrieve blob data from database

At this point, the full file path name and its binary code are stored in the SQLite3 database. In order to retrieve the file, and view it in its original form, you have to find the image file in the database and write the blob to a file.

For the sake of simplicity in this tutorial, you will look up the image file by the last_updated_entry. You should only have one entry in the database at this time, but if you had more, you would just pass in another number to this function.

Another connection is made to the app.db file. The sql_fetch_blob_query is executed once you pass in the value of the entry_id you are looking for. A record is received and stores the relevant values for converted_file_name and photo_binarycode respectively.

Since you stored the entire path name of the file from your Documents folder, you will want to parse the string to find the name of the file only instead of the whole path. last_slash_index is a variable that identifies the index of when your real file name begins. The final_file_name holds the resulting value of the sliced converted_file_name value.

Using the new information created from retrieving the entry from the database, you can call the function write_to_file which will be defined in the next section.

Go ahead and copy the function for read_blob_data under convert_into_binary in the app.py file:

NOTE: If you are a Windows developer, the forward slash ("/") will not apply to you. Reverse to a backslash or rewrite the code to use the os.path.split() function.

Great! Now in order to complete this project tutorial, you will write out the write_to_file function.

Convert blob data back to a file

To convert the binary data into a file, you will need to pass in the blob as well as the name of the file that is associated with the binary data. Instead of reading the binary code, Python will use the binary data to open and write the file to your project directory.

Copy and paste this last function to your app.py file right above def main():

Run the Python app to retrieve files from the SQLite3 database

Whew, that's a hefty app so far! It's almost time to test the whole application out.

Add the last line in your def main() to complete the Python file.

In the code above, you will be retrieving the image of the last entry.

Feel free to check out the GitHub repository to make sure you have everything.

In your terminal, run the command python app.py. Open the Documents folder or whichever image folder you decide to use. Copy the full file path name and paste it in the terminal when prompted.

Here's an example of what your output should look on the terminal:

Open your project directory and notice that a new file named DRAW_THE_OWL_MEME.png, or whichever file you used, has been added to your folder.

What a success! You now understand how to store a blob media file safely in a database, and retrieve it in its original form, all in one application!

What's next for blob data?

Now that you understand how to work with blob data and why it's necessary to handle media uploads, try to change this to a fully functional website where users have a better UI to upload files. Instead of having them copy and paste the full file path name, have Python handle the unique file paths for you so that your users do not have to expose their document structures.

Here are some articles you can check out to help you build out a safe application that allows users to upload image files:

• Build a site with Flask and Twilio Verify for users to upload a file
• Host a social media giveaway using Twilio WhatsApp API, Python, and SQLite3
• Add an image recognition API to check pictures sent over WhatsApp

Let me know what you're using blob data for and how you are storing file data by reaching out to me over email!

Diane Phan is a Developer Network editor on the Developer Voices team. She loves to help programmers tackle difficult challenges that might prevent them from bringing their projects to life. She can be reached at dphan [at] twilio.com or LinkedIn.