I am a beginner in software development, I am task to develop a web-based application, in which I use Django Web Framework, that can Execute SQL queries against a MS SQL Server Database, display the results of the query and log the details into the Django database (PostgreSQL), like timestamp, user, SQL Statement, errors, results etc, it is working. But, can you guys give me how to improve this? In my template, I use Ajax, and planning to use reactJS as frontend, but I am not really into it right now due to I need to learn it first.
Objective:
(1). I want to store the results in the session, retrieve it using ajax from the session and display the results.
(2). but I want to add a unique ID using the generate_session_id() to that session data to identify that particular session data and retrieve it using ajax and display it the session data.
(3). and to also enable the user to open new tab and run another SQL query executor.
(3.1). Every time the page is reloaded or the page is ready, or the user open it in another tab, the unique session id should be different or renew the unique ID using the cleanup_old_sessions(). For now, the cleanup_old_sessions() is set to be cleaned up if the session data is 1 hour old.
(3.2). But, I want the session ID to be renewed every time.
Thank you, your reply will greatly help me and will very much appreciated.
Note: I code I have here is from research with ChatGPT 🙂
views.py:
def generate_session_id():
"""Generate a random session ID."""
return ''.join(random.choices(string.ascii_lowercase + string.digits, k=16))
@login_required(login_url='/admin/login')
def execute_sql(request):
title = 'SQL Query Executor'
# Check if session_id already exists in the session
session_id = request.session.get('current_session_id')
if not session_id:
# Generate a unique session ID for the request
session_id = generate_session_id()
request.session['current_session_id'] = session_id
request.session['current_timestamp'] = time.time()
# Cleanup old session data
cleanup_old_sessions(request)
result = None
error = None
columns = []
sql_log_id = None
row_count = 0
processing_time = None
sql_log_processing_time = None
processing_time_str = None
if request.method == 'POST':
start_time = time.time() # Record the start time
form = SQLForm(request.POST, user=request.user)
if form.is_valid():
sql = form.cleaned_data['sql_statement'].strip().lower()
connection = form.cleaned_data['connection']
save_query = form.cleaned_data['save_query']
# Update the database connection based on the database connection selected
settings.DATABASES['remote'].update({
'NAME': connection.database,
'USER': connection.username,
'PASSWORD': connection.password,
'HOST': connection.host,
'PORT': connection.port,
})
if any(sql.strip().lower().startswith(prefix) for prefix in ALLOWED_SQL_PREFIXES):
try:
with connections['remote'].cursor() as cursor:
cursor.execute(sql)
columns = [col[0] for col in cursor.description]
rows = cursor.fetchall()
result = rows
row_count = len(rows)
cols_rows = {'columns': columns, 'rows': result}
serializable_cols_rows = convert_to_serializable(cols_rows)
# # Convert result to be JSON serializable
serializable_result = convert_to_serializable(rows)
# # Save the result in session for downloading
request.session[f'result_{session_id}'] = serializable_result
request.session[f'columns_{session_id}'] = convert_to_serializable(columns)
request.session[f'row_count_{session_id}'] = row_count
request.session[f'user_{session_id}'] = convert_to_serializable(str(request.user))
# Collect user's groups
user_groups = request.user.groups.all()
user_groups_str = ', '.join(group.name for group in user_groups)
# Calculate the processing time
processing_time_raw = time.time() - start_time
# Convert the duration to a timedelta object
processing_time = timedelta(seconds=processing_time_raw)
# Convert the duration to milliseconds
milliseconds = processing_time_raw * 1000
# Compute hours, minutes, seconds, and milliseconds
hours, milliseconds = divmod(milliseconds, 3600000)
minutes, milliseconds = divmod(milliseconds, 60000)
seconds, milliseconds = divmod(milliseconds, 1000)
# Format the duration string
processing_time_str = f"{int(hours):02}:{int(minutes):02}:{int(seconds):02}:{int(milliseconds):03}"
# Save the SQL log
sql_log = SQLLog.objects.create(
user=request.user,
user_groups=user_groups_str,
connection=connection,
sql_statement=sql,
result=json.dumps(serializable_cols_rows),
row_count=row_count,
remarks='query',
processing_time=processing_time
)
# Save the query to a separate model if the checkbox is checked
if save_query:
SavedQuery.objects.create(
user=request.user,
sql_statement=sql
)
# Get SQLLog id
sql_log_id = sql_log.id
sql_log_timestamp = sql_log.timestamp
sql_log_processing_time = sql_log.processing_time
# Save SQLLog id in session
request.session[f'sql_log_id_{session_id}'] = convert_to_serializable(sql_log_id)
request.session[f'timestamp_{session_id}'] = convert_to_serializable(sql_log_timestamp)
request.session[f'processing_time_{session_id}'] = sql_log_processing_time.total_seconds()
except Exception as e:
error = str(e)
request.session[f'error_{session_id}'] = error
logger.error("SQL Execution Error: %s", error)
# Collect user's groups
user_groups = request.user.groups.all()
user_groups_str = ', '.join(group.name for group in user_groups)
# Calculate the processing time
processing_time_raw = time.time() - start_time
# Convert the duration to a timedelta object
processing_time = timedelta(seconds=processing_time_raw)
# Convert the duration to milliseconds
milliseconds = processing_time_raw * 1000
# Compute hours, minutes, seconds, and milliseconds
hours, milliseconds = divmod(milliseconds, 3600000)
minutes, milliseconds = divmod(milliseconds, 60000)
seconds, milliseconds = divmod(milliseconds, 1000)
# Format the duration string
processing_time_str = f"{int(hours):02}:{int(minutes):02}:{int(seconds):02}:{int(milliseconds):03}"
# Save the SQL log
sql_log = SQLLog.objects.create(
user=request.user,
user_groups=user_groups_str,
connection=connection,
sql_statement=sql,
error=error,
remarks='query',
processing_time=processing_time
)
# Save the query to a separate model if the checkbox is checked
if save_query:
SavedQuery.objects.create(
user=request.user,
sql_statement=sql
)
# Get SQLLog id
sql_log_id = sql_log.id
sql_log_timestamp = sql_log.timestamp
sql_log_processing_time = sql_log.processing_time
# Save SQLLog id in session
request.session[f'sql_log_id_{session_id}'] = convert_to_serializable(sql_log_id)
request.session[f'timestamp_{session_id}'] = convert_to_serializable(sql_log_timestamp)
request.session[f'processing_time_{session_id}'] = sql_log_processing_time.total_seconds()
else:
error = "Only SELECT statements are allowed."
request.session[f'error_{session_id}'] = error
logger.warning("Invalid SQL Statement: %s", sql)
# Collect user's groups
user_groups = request.user.groups.all()
user_groups_str = ', '.join(group.name for group in user_groups)
# Calculate the processing time
processing_time_raw = time.time() - start_time
# Convert the duration to a timedelta object
processing_time = timedelta(seconds=processing_time_raw)
# Convert the duration to milliseconds
milliseconds = processing_time_raw * 1000
# Compute hours, minutes, seconds, and milliseconds
hours, milliseconds = divmod(milliseconds, 3600000)
minutes, milliseconds = divmod(milliseconds, 60000)
seconds, milliseconds = divmod(milliseconds, 1000)
# Format the duration string
processing_time_str = f"{int(hours):02}:{int(minutes):02}:{int(seconds):02}:{int(milliseconds):03}"
# Save the SQL log
sql_log = SQLLog.objects.create(
user=request.user,
user_groups=user_groups_str,
connection=connection,
sql_statement=sql,
error=error,
remarks='query',
processing_time=processing_time
)
# Save the query to a separate model if the checkbox is checked
if save_query:
SavedQuery.objects.create(
user=request.user,
sql_statement=sql
)
# Get SQLLog id
sql_log_id = sql_log.id
sql_log_timestamp = sql_log.timestamp
sql_log_processing_time = sql_log.processing_time
# Save SQLLog id in session
request.session[f'sql_log_id_{session_id}'] = convert_to_serializable(sql_log_id)
request.session[f'timestamp_{session_id}'] = convert_to_serializable(sql_log_timestamp)
request.session[f'processing_time_{session_id}'] = sql_log_processing_time.total_seconds()
# Return JSON response for AJAX request
if request.headers.get('x-requested-with') == 'XMLHttpRequest':
if error:
return JsonResponse({'status': 'error', 'error': error}, status=400)
else:
return JsonResponse({
'status': 'completed',
'result': result,
'columns': columns,
'row_count': row_count,
'sql_log_id': sql_log_id,
'processing_time': processing_time_str
})
else:
form = SQLForm(user=request.user)
return render(request, 'sql_executor/sql_exec.html', {
'form': form,
'result': result,
'columns': columns,
'row_count': row_count,
'error': error,
'sql_log_id': sql_log_id,
'title': title,
'processing_time': processing_time_str,
'session_id': session_id
})
def cleanup_old_sessions(request, max_age=3600):
"""Clean up old session data older than max_age seconds."""
current_time = time.time()
for key in list(request.session.keys()):
if key.startswith('result_') or key.startswith('columns_') or key.startswith('row_count_') or key.startswith('error_') or key.startswith('processing_time_'):
session_id = key.split('_')[-1]
timestamp_key = f'current_timestamp_{session_id}'
timestamp = request.session.get(timestamp_key)
print(current_time)
print(timestamp)
if timestamp and (current_time - timestamp > max_age):
# Delete session data if it is older than max_age
del request.session[key]
del request.session[timestamp_key]
@login_required(login_url='/admin/login')
def check_status(request):
session_id = request.GET.get('session_id') # Get the session ID from the request
if not session_id:
return JsonResponse({'error': 'Session ID is required.'}, status=400)
print(f"Session_ID Check status: {session_id}")
result = request.session.get(f'result_{session_id}')
columns = request.session.get(f'columns_{session_id}')
row_count = request.session.get(f'row_count_{session_id}')
error = request.session.get(f'error_{session_id}')
sql_log_id = request.session.get(f'sql_log_id_{session_id}')
processing_time_raw = request.session.get(f'processing_time_{session_id}')
# Handle the case where processing_time_raw is None
if processing_time_raw is None:
processing_time_str = "00:00:00:000"
else:
# Convert the duration to milliseconds
milliseconds = processing_time_raw * 1000
# Compute hours, minutes, seconds, and milliseconds
hours, milliseconds = divmod(milliseconds, 3600000)
minutes, milliseconds = divmod(milliseconds, 60000)
seconds, milliseconds = divmod(milliseconds, 1000)
# Format the duration string
processing_time_str = f"{int(hours):02}:{int(minutes):02}:{int(seconds):02}:{int(milliseconds):03}"
# Limit the number of rows sent back
page = int(request.GET.get('page', 1))
page_size = 50
start = (page - 1) * page_size
end = start + page_size
paginated_result = result[start:end] if result else []
if result is not None:
status = 'completed'
elif error is not None:
status = 'error'
else:
status = 'processing'
return JsonResponse({
'status': status,
'result': paginated_result,
'columns': columns,
'row_count': row_count,
'error': error,
'page': page,
'has_more': end < row_count if row_count is not None else False,
'sql_log_id': sql_log_id,
'processing_time': processing_time_str
})
template:
{% extends "./includes/base.html" %}
{% load static %}
{% block content %}
<style>
.scrollable-div {
max-height: 700px; /* Adjust this value as needed */
overflow-y: auto;
}
.form-check-input {
margin-left: 50px;
}
</style>
<h1 class="mb-4">Execute SQL Query</h1>
<p style="color: red;"><em><strong>Note:</strong> Due to the limitations of this web-based application, extracting large amounts of data may sometimes fail. If this occurs, please adjust your query, especially if it includes a date range. For example, try narrowing the date range or reducing the amount of data requested. If you continue to experience issues, ensure you are using a compatible browser and device, or contact technical support for further assistance. Thank you.</em></p>
<form method="post" id="sql-form">
{% csrf_token %}
<input type="hidden" name="session_id" value="{{ session_id }}">
<div class="form-group">
{{ form.as_p }}
</div>
<button id="sql-submit" type="submit" class="btn btn-primary">Execute SQL Query</button>
<button id="sql-reset" type="button" class="btn btn-primary">Reset</button>
</form>
<div id="message-error" class="mt-4 alert alert-danger" style="display: none;"></div>
<div id="message" class="mt-4 alert alert-info" style="display: none;">
<div class="spinner-border" role="status">
<span class="sr-only">Loading...</span>
</div>
<span id="message-text"></span>
</div>
{% if error %}
<hr />
<div id="error-section" style="display:block;">
<h2>Error:</h2>
<pre id="error-message">{{ error }}</pre>
</div>
{% else %}
<hr />
<div id="error-section" style="display:none;">
<h2>Error:</h2>
<pre id="error-message"></pre>
</div>
{% endif %}
<div id="result-section" class="mt-4" style="display: none;">
<h2>Results:</h2>
<hr />
<p>Number of rows: <span id="row-count"></span></p>
<p>Processing time: <span id="processing-time"></span></p>
<hr />
<a id="download-result" href="#" class="btn btn-success btn-sm">Download results</a>
<a id="view-result" href="#" target="_blank" class="btn btn-success btn-sm">View details</a>
<p><code>ONLY 50 rows are displayed</code></p>
<div class="scrollable-div">
<table class="table table-striped">
<thead>
<tr id="table-header">
<th>#</th> <!-- Add '#' header -->
</tr>
</thead>
<tbody id="table-body"></tbody>
</table>
</div>
</div>
</div>
{% endblock %}
JavaScript:
<script>
$(document).ready(function() {
var statusInterval;
$('#sql-form').on('submit', function(event) {
event.preventDefault();
var statusInterval = setInterval(checkStatus, 5000);
$('#sql-submit').prop('disabled', true);
$('#message').show();
$('#message-text').text('Executing SQL query, please wait...');
// Clear previous results
$('#result-section').hide();
$('#error-section').hide();
$('#table-header').html('<th>#</th>'); // Add '#' header
$('#table-body').empty();
// Submit the form via AJAX
var formData = new FormData(this);
$.ajax({
url: '{% url "sql_executor:execute_sql" %}',
type: 'POST',
data: formData,
processData: false,
contentType: false,
success: function(data) {
if (data.status === 'error') {
displayError(data.error);
} else{
checkStatus();
}
},
error: function(xhr) {
var errorData;
try {
errorData = xhr.responseJSON;
} catch (e) {
errorData = null;
}
var errorMessage = errorData && errorData.error ? errorData.error : 'Error executing SQL query.';
displayError(errorMessage);
},
complete: function() {
clearInterval(statusInterval);
}
});
});
function checkStatus() {
$.ajax({
url: '/tools/check-status/?session_id={{session_id}}',
type: 'GET',
success: function(data) {
if (data.status === 'completed') {
clearInterval(statusInterval);
displayResult(data);
} else if (data.status === 'error') {
clearInterval(statusInterval);
displayError(data.error);
} else {
// Update UI to indicate ongoing execution
$('#message-text').text('Query execution in progress...');
}
},
error: function() {
console.error('Error checking status.');
},
complete: function() {
clearInterval(statusInterval);
}
});
}
function displayResult(data) {
$('#sql-submit').prop('disabled', false);
$('#message').hide();
var columns = data.columns;
var rows = data.result;
var rowCount = data.row_count;
var processingTime = data.processing_time;
var sqlLogId = data.sql_log_id;
$('#row-count').text(rowCount);
$('#processing-time').text(processingTime);
var headerRow = $('#table-header');
columns.forEach(function(col) {
var th = $('<th></th>').text(col);
headerRow.append(th);
});
var tableBody = $('#table-body');
rows.forEach(function(row, index) {
var tr = $('<tr></tr>');
var tdIndex = $('<td></td>').text(index + 1);
tr.append(tdIndex);
row.forEach(function(cell) {
var td = $('<td></td>').text(cell);
tr.append(td);
});
tableBody.append(tr);
});
// Update the download link with the sql_log_id
$('#download-result').attr('href', '/tools/sqllog-details/' + sqlLogId + '/?download_excel=true');
$('#view-result').attr('href', '/tools/sqllog-details/' + sqlLogId + '/');
$('#result-section').show();
}
function displayError(error) {
$('#sql-submit').prop('disabled', false);
$('#message').hide();
$('#error-message').text(error);
$('#error-section').show();
}
$('#sql-reset').on('click', function() {
location.reload();
});
});
</script>