Django站点SQLAlchemy连接SQLite生产环境报错求助

我用Django搭建了一个站点，仪表盘页面需要展示站点内的活动统计数据。本地用py manage.py runserver运行时仪表盘加载正常，但部署到CentOS 8服务器、通过Nginx和Gunicorn提供服务时出现错误。

仪表盘视图代码 #

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))

报错信息 #

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]

已做排查 #

• 打印所有表和列，确认表结构存在且正确
• 验证本地与生产环境的Python包版本完全一致

问题根源是SQLite版本兼容性：你用的COUNT(1) FILTER (WHERE ...)语法是SQLite 3.30.0及以上版本才支持的特性，但CentOS 8默认预装的SQLite版本是3.26.0，不支持这个语法，所以部署后报错。

解决办法是把FILTER语法替换成SQLite低版本支持的CASE WHEN写法，修改后的SQL如下：

SELECT sys.name, 
       COUNT(*) as `num_reviews`,
       COUNT(CASE WHEN reviewComplete=1 THEN 1 END) as `Completed`,
       COUNT(CASE WHEN reviewComplete=0 THEN 1 END) as `Incomplete`
FROM AccessReview_review 
JOIN AccessReview_System sys ON system_id=sys.id 
GROUP BY sys.name 
ORDER BY -num_reviews

把视图代码中overview_df对应的SQL替换成上面的内容即可解决问题。

额外建议：尽量使用Django的ORM来编写查询逻辑，而不是直接写原生SQL。ORM会自动适配不同数据库的语法特性，避免这类因数据库版本或类型差异导致的兼容性问题。比如用Django ORM实现同样的统计逻辑：

from django.db.models import Count, Case, When, IntegerField

overview_data = AccessReview_review.objects.values('system__name')\
    .annotate(
        num_reviews=Count('id'),
        Completed=Count(Case(When(reviewComplete=1, then=1), output_field=IntegerField())),
        Incomplete=Count(Case(When(reviewComplete=0, then=1), output_field=IntegerField()))
    )\
    .order_by('-num_reviews')

# 再把queryset转换成DataFrame
overview_df = pd.DataFrame(list(overview_data))

这样既不用关心数据库语法差异，也能享受Django ORM带来的安全和便捷性。

内容的提问来源于stack exchange，提问作者Sogetsue