django - How to restore sqlite3 database file programmatically in django

Question

The user wishes to have a database backup and restore functionality through the application. They want to be able to download the database file and upload it to restore the database whenever needed. The problem is that django is already running the current DB file. I wrote the following logic to restore the database.

    folder ='./' 
if request.method == 'POST':        
    myfile = request.FILES['file']
    fs = FileSystemStorage(location=folder) 
    if myfile.name.split(".")[1] != "sqlite3":
        return JsonResponse({"res":"Please upload a database file."})
    if os.path.isfile(os.path.join(folder, "db.sqlite3")): 
        os.remove(os.path.join(folder, "db.sqlite3"))        
    filename = fs.save("db.sqlite3", myfile)
    file_url = fs.url(filename)
    return JsonResponse({"res":file_url})

I get the following error which is rightly justified:

[WinError 32] The process cannot access the file because it is being used by another process

So, is there a way I can achieve this functionality through my application?

标签： djangosqlite

Answer 1

I found a better way to create this functionality using a csv. One could store the data from the DB into a csv file and restore it when uploaded. Following is my implementation:

    def back_up_done(request):
        tables = getTableNames()
        sql3_cursor = connection.cursor()
        for table in tables:
            sql3_cursor.execute(f'SELECT * FROM {table}')
            with open('output.csv','a') as out_csv_file:
                csv_out = csv.writer(out_csv_file)
    
                for result in sql3_cursor:
                    csv_out.writerow('Null' if x is None else x for x in result)
                csv_out.writerow("|")
    
        BaseDir = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
        csv_path = os.path.join(BaseDir, 'output.csv')
        dbfile = File(open(csv_path, "rb"))
    
        response = HttpResponse(dbfile, content_type='application/csv')
        response['Content-Disposition'] = 'attachment; filename=%s' % 'backup.csv'
        response['Content-Length'] = dbfile.size
        os.remove(os.path.join("./", "output.csv"))
        return response


def back_up_restore(request):
    if request.method == 'POST':        
        DBfile = request.FILES['file']
        cursor = connection.cursor()
        
        if DBfile.name.split(".")[1] != "csv":
            messages.add_message(request, messages.ERROR, "Please upload a CSV file.")
            return redirect('back-up-db')

        tables = getTableNames()
        i = 0
        deleteColumns()
        decoded_file = DBfile.read().decode('utf-8').splitlines()
        reader = csv.reader(decoded_file)

        for row in reader:
            if len(row) != 0:
                if(row[0] == "|"):
                    i += 1
                else:
                    query = f'''Insert into {tables[i]} values({concatRowValues(row)})'''
                    
                    cursor.execute(query)
        connection.close()
        messages.add_message(request, messages.SUCCESS, "Data Restored Successfully.")
        return redirect('login')

def concatRowValues(row):
    data = ''
    for i,value in enumerate(row):
        if value == "False":
            value = '0'
        elif value == "True":
            value = '1'    
        
        if i != len(row) - 1:
            data =  f'{data}{str(value)},' if value == "Null" else f'{data}\'{str(value)}\','
        else:
            data = f'{data}{str(value)}' if value == "Null" else f'{data}\'{str(value)}\''
    return data

Where getTableNames and concatRowValues are helper functions to get the names of tables and to concatenate column values to form an executable sql statement, respectively. The "|" character is used to mark the end of a table's data.