Storing files in a relational database like PostgreSQL is not a recommended approach, as databases are optimized for structured data, not unstructured data like files. There are several reasons why it’s not ideal to store files in a database:
Performance: Querying and retrieving large files from a database can be slow and resource-intensive.
Scalability: As the amount of stored data grows, the database performance can degrade, which can impact the overall system performance.
Limitations on file size: Most relational databases have limits on the size of data that can be stored in a single field. If you need to store large files, you may run into size limitations.
Increased complexity: Managing files within a database adds an additional layer of complexity to your system, making it more difficult to maintain and troubleshoot.
Instead of storing files in a database, it’s recommended to use a file storage system that is optimized for handling unstructured data, such as a cloud storage service or a local file system. You can then store a reference to the file location in the database and use that reference to retrieve the file when necessary.
In any case, this post will outline how to upload a file, store the encoded data in a PostgreSQL database, and then extract that data to a Django web page if you still wish to keep files in PostgreSQL.
Steps:
- Create database table with column type bytea
- Create Django html form to upload file.
- Encode file to base64 and insert to PostgreSQL database with file extension.
- Retrieve encoded data from database, decode it, and pass to html page.
- Finally, Show file in Django HTML page.
For better view scripts can be downloaded from here.
Creating database table:
A table is created for storing two files with their extension names. User will upload the file and select the file type from a Django html form.
|
1 2 3 4 5 6 7 8 |
CREATE TABLE file_name_db ( Id serial, DataSheetUploadFile bytea, DataSheetUploadFileType CHARACTER VARYING(255), ChassisSlotUploadFile bytea, ChassisSlotUploadFileType CHARACTER VARYING(255), PRIMARY KEY(id) ); |
Create Django html form to upload file:
In setting.py of Django project app a media directory location is defined where file will be uploaded initially.
|
1 2 3 |
# for media files MEDIA_URL = '/media/' MEDIA_ROOT = os.path.join(BASE_DIR, 'media') |
HTML page showing form:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 |
{% extends 'base.html' %} {% load static %} {% block content %} <!DOCTYPE html> <html lang="en"> <!--<link rel="stylesheet" href="{% static 'css/bootstrap.css' %}">--> <body style="margin-top: 100px;"> <div class="container"> <div class="row"> <div class="col-2"></div> <div class="col-10"> <form method="post" enctype="multipart/form-data"> {% csrf_token %} <div class="form-row"> <div class="row"> <div class="col-md-4 mb-3"> <label for="formFileSm" class="form-label">Data sheet upload:</label> <select class="form-control" id="DataSheetFileType" name="DataSheetFileType"> <option>JPG</option> <option>PDF</option> </select> <input class="form-control form-control-sm" id="DataSheetFile" name="DataSheetFile" type="file" /> </div> <div class="col-md-4 mb-3"></div> <div class="col-md-4 mb-3"> <label for="formFileSm" class="form-label">Chassis/slot view file upload:</label> <select class="form-control" id="ChassisFileType" name="ChassisFileType"> <option>JPG</option> <option>PDF</option> </select> <input class="form-control form-control-sm" id="ChassisFile" name="ChassisFile" type="file" /> </div> </div> <button class="btn btn-primary" type="submit" value="OK">Submit</button> <br> <br> <br> </form> <br> <br> <br> </div> </div> </div> </body> </html> {% endblock %} |
This HTML form will submit files to our defined media directory.
In view.py we will capture the values submitted from html form. We are encoding file to base64, and inserting to PostgreSQL database with corresponding file extension names.
Retrieve encoded data from database, decode it, and pass to html page:
To recover data we need to decode it after selecting it from database. We will covert it to bytes tobytes(), and decode to UTF-8 (decode(“utf-8”)).
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
from django.shortcuts import render from django.http import HttpResponse from django.views.decorators.csrf import csrf_exempt from django.core.files.storage import FileSystemStorage import psycopg2 import base64 from base64 import b64encode from django.shortcuts import redirect # function for storing data to db def deviceModelView(request): conn = psycopg2.connect( host="", database="", user="", password="") cur = conn.cursor() ModelName = request.GET.get('ModelName','') query_model_table = "SELECT datasheetuploadfiletype,chassisslotuploadfiletype,datasheetuploadfile,chassisslotuploadfile FROM public.file_name_db where devicemodelname like '" + ModelName + "';" cur.execute(query_model_table) rows = cur.fetchall() datasheetuploadfiletype,chassisslotuploadfiletype, datasheetuploadfile, chassisslotuploadfile = '','','','' for row in rows: datasheetuploadfiletype,chassisslotuploadfiletype, datasheetuploadfile, chassisslotuploadfile = row[0],row[1],row[2].tobytes().decode("utf-8"), row[3].tobytes().decode("utf-8") break conn.close() cur.close() return render(request, "deviceModelView.html",{'datasheetuploadfiletype':datasheetuploadfiletype,'chassisslotuploadfiletype':chassisslotuploadfiletype, 'datasheetuploadfile':datasheetuploadfile, 'chassisslotuploadfile':chassisslotuploadfile}) |
Database showing that both image and pdf files are stored properly.
Finally, Show file in Django html page.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 |
# function for html form submit def deviceModelEdit(request): conn = psycopg2.connect( host="localhost", database="", user="", password="") cur = conn.cursor() if request.method == 'POST': drawing_DataSheetFile = '' drawing_ChassisFile = '' DataSheetUploadFileType = '' ChassisSlotUploadFileType = '' drawing_DataSheetFile_base_64 = '' drawing_ChassisFile_base_64 = '' try: if request.FILES['DataSheetFile']: DataSheetUploadFileType = request.POST.get('DataSheetFileType') DataSheetFile = request.FILES['DataSheetFile'] fs = FileSystemStorage() filename = fs.save(DataSheetFile.name, DataSheetFile) uploaded_file_url = fs.url(filename) # read data from a file, here 'app' is Django project name, and our media directory is inside this directory path_to_file = '/app/'+uploaded_file_url drawing_DataSheetFile = open(path_to_file, 'rb').read() drawing_DataSheetFile_base_64 = base64.b64encode(drawing_DataSheetFile) # drawing_DataSheetFile_psycopg2_bin = psycopg2.Binary(drawing_DataSheetFile) # print("Checking for ChassisFile uploaded file type.") if request.FILES['ChassisFile']: # print("ChassisFile file found") ChassisSlotUploadFileType = request.POST.get('ChassisFileType') ChassisFile = request.FILES['ChassisFile'] fs = FileSystemStorage() filename = fs.save(ChassisFile.name, ChassisFile) uploaded_file_url = fs.url(filename) # print("ChassisFile detected. filename = ",filename, " uploaded_file_url: ",uploaded_file_url) # read data from a file path_to_file = '/app/'+uploaded_file_url drawing_ChassisFile = open(path_to_file, 'rb').read() drawing_ChassisFile_base_64 = base64.b64encode(drawing_ChassisFile) # drawing_ChassisFile_psycopg2_bin = psycopg2.Binary(drawing_ChassisFile) cur.execute(""" INSERT INTO file_name_db (datasheetuploadfile,DataSheetUploadFileType, chassisslotuploadfile,ChassisSlotUploadFileType ) VALUES (%s,%s,%s,%s) """,( drawing_DataSheetFile_base_64,DataSheetUploadFileType, drawing_ChassisFile_base_64,ChassisSlotUploadFileType )) conn.commit(); conn.close() cur.close() return redirect('/deviceModel/') # returning to home page except Exception as e: print(e) return render(request, "deviceModelEdit.html") # form edit page |
Html page showing decoded file data
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
{% extends 'base.html' %} {% load static %} {% block content %} <title> Device Model </title> <body style="margin-top: 100px;"> <!-- ***** model form Area Starts ***** --> <div class="container"> <div class="row"> <div class="col-2"></div> <div class="col-10"> <div class="col-md-6 mb-3"> <div class="col-md-12 mb-3"> <p>Data sheet file: </p> {% if datasheetuploadfiletype == 'JPG' %} <img width="500" height="500" src="data:image/jpg;base64, {{ datasheetuploadfile }}"> {% elif datasheetuploadfiletype == 'PDF' %} <embed src="data:application/pdf;base64, {{ datasheetuploadfile }}" width="1000px" height="1800px" /> {% endif %} <p>Chassis/slot view file: </p> {% if chassisslotuploadfiletype == 'JPG' %} <img width="500" height="500" src="data:image/jpg;base64, {{ chassisslotuploadfile }}"> {% elif chassisslotuploadfiletype == 'PDF' %} <embed src="data:application/pdf;base64, {{ chassisslotuploadfile }}" width="1000px" height="1800px" /> {% endif %} </div> </div> <div class="col-md-12 mb-3"> <br> </div> </div> </div> </div> <!-- ***** model form Area Ends ***** --> </body> {% endblock %} |
0 Comments