On my site built with Django, I have a dashboard page that I need to display statistics about campaigns running on the site. When running the following code locally, the dashboard page loads correctly, but when running it on the live server served through Nginx and Gunicorn I run into an error.
The dashboard view:
def sysDashboard(request):
template = loader.get_template('AccessReview/sysdashboard.html')
context = {}
disk_engine = create_engine('sqlite:///db.sqlite3')
pio.renderers.default="svg"
print(disk_engine.table_names())
sqlQueries.info(disk_engine.table_names())
df = pd.read_sql_query('SELECT emp.pid, COUNT(*) as `num_reviews` '
'FROM AccessReview_review rev '
'JOIN AccessReview_employee emp ON rev.manager_id=emp.id '
'GROUP BY emp.pid '
'ORDER BY -num_reviews ', disk_engine)
print(df)
fig = go.Figure(
data=[go.Bar(x=df['pid'], y=df['num_reviews'])],
layout_title_text='AccessReview/Reviews per Manager').update_layout(
{'plot_bgcolor': 'rgba(102,78,98,0.5)',
'paper_bgcolor': 'rgba(102,78,98,0.5)',
'font_color': 'rgba(255,255,255,1)'
})
overview_df = pd.read_sql_query('SELECT sys.name, '
'COUNT(*) as `num_reviews`, '
'COUNT(1) FILTER (WHERE reviewComplete=1) as `Completed`, '
'COUNT(1) FILTER (WHERE reviewComplete=0) as `Incomplete` '
'FROM AccessReview_review '
'JOIN AccessReview_System sys ON system_id=sys.id '
'GROUP BY sys.name '
'ORDER BY -num_reviews', disk_engine)
print(overview_df)
overview_fig = go.Figure(
data=[go.Bar (x=overview_df['name'], y=overview_df['num_reviews'])],
layout_title_text='Campaign Overview').update_layout(
{'plot_bgcolor': 'rgba(102,78,98,0.5)',
'paper_bgcolor': 'rgba(102,78,98,0.5)',
'font_color': 'rgba(255,255,255,1)'
}, xaxis={'title':'System', 'fixedrange':True},
yaxis={'title':'Review Count', 'fixedrange':True})
context['overviewGraph'] = overview_fig.to_html()
return HttpResponse(template.render(context, request))
Like I said, this works when running the server locally using py manage.py runserver but not when accessing the site through the proxy. The only thing I can think of is that there's an issue with how I'm accessing the database. The machine I use when running locally runs Windows, but the site sits on a server running CentOS 8.
Here is the error received:
Environment:
Request Method: GET
Request URL: http://willow.charter.com/sysdashboard/
Django Version: 4.0.3
Python Version: 3.9.2
Installed Applications:
['django.contrib.admin',
'django.contrib.auth',
'django.contrib.contenttypes',
'django.contrib.sessions',
'django.contrib.messages',
'django.contrib.staticfiles',
'AccessReview.apps.AccessreviewConfig']
Installed Middleware:
['django.middleware.security.SecurityMiddleware',
'django.contrib.sessions.middleware.SessionMiddleware',
'django.middleware.common.CommonMiddleware',
'django.middleware.csrf.CsrfViewMiddleware',
'django.contrib.auth.middleware.AuthenticationMiddleware',
'django.contrib.messages.middleware.MessageMiddleware',
'django.middleware.clickjacking.XFrameOptionsMiddleware',
'django_session_timeout.middleware.SessionTimeoutMiddleware']
Traceback (most recent call last):
File "/home/User/.local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1819, in _execute_context
self.dialect.do_execute(
File "/home/User/.local/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute
cursor.execute(statement, parameters)
The above exception (near "as": syntax error) was the direct cause of the following exception:
File "/home/User/.local/lib/python3.9/site-packages/django/core/handlers/exception.py", line 55, in inner
response = get_response(request)
File "/home/User/.local/lib/python3.9/site-packages/django/core/handlers/base.py", line 197, in _get_response
response = wrapped_callback(request, *callback_args, **callback_kwargs)
File "/home/User/.local/lib/python3.9/site-packages/django/contrib/auth/decorators.py", line 23, in _wrapped_view
return view_func(request, *args, **kwargs)
File "/home/User/WebApps/ACS_Review/acs_user_review/PAR/AccessReview/views.py", line 736, in sysDashboard
overview_df = pd.read_sql_query('SELECT sys.name, '
File "/home/User/.local/lib/python3.9/site-packages/pandas/io/sql.py", line 399, in read_sql_query
return pandas_sql.read_query(
File "/home/User/.local/lib/python3.9/site-packages/pandas/io/sql.py", line 1557, in read_query
result = self.execute(*args)
File "/home/User/.local/lib/python3.9/site-packages/pandas/io/sql.py", line 1402, in execute
return self.connectable.execution_options().execute(*args, **kwargs)
File "<string>", line 2, in execute
<source code not available>
File "/home/User/.local/lib/python3.9/site-packages/sqlalchemy/util/deprecations.py", line 401, in warned
_warn_with_version(message, version, wtype, stacklevel=3)
File "/home/User/.local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 3176, in execute
return connection.execute(statement, *multiparams, **params)
File "/home/User/.local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1291, in execute
return self._exec_driver_sql(
File "/home/User/.local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1595, in _exec_driver_sql
ret = self._execute_context(
File "/home/User/.local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1862, in _execute_context
self._handle_dbapi_exception(
File "/home/User/.local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2043, in _handle_dbapi_exception
util.raise_(
File "/home/User/.local/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 208, in raise_
raise exception
File "/home/User/.local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1819, in _execute_context
self.dialect.do_execute(
File "/home/User/.local/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute
cursor.execute(statement, parameters)
Exception Type: OperationalError at /sysdashboard/
Exception Value: (sqlite3.OperationalError) near "as": syntax error
[SQL: SELECT sys.name, COUNT(*) as `num_reviews`, COUNT(1) FILTER (WHERE reviewComplete=1) as `Completed`, COUNT(1) FILTER (WHERE reviewComplete=0) as `Incomplete` FROM AccessReview_review JOIN AccessReview_System sys ON system_id=sys.id GROUP BY sys.name ORDER BY -num_reviews]
(Background on this error at: https://sqlalche.me/e/14/e3q8)
The operational error indicates that there's a problem with reading from the database rather than a SQL syntax issue. This is further validated by the fact that the view loads fine when ran locally.
Does anyone have any idea why this isn't working?
Things I've tried:
Print all tables and columns in the various dataframes to ensure they exist
Validated that all Python packages are installed from the local instance to the live instance and that they are the same version