It'll raise a django.db.utils.ProgrammingError when using different databases such as Mysql and SQLite

[LvPeng990324]

The error is： django.db.utils.ProgrammingError: (1064, 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near \'"Region_province" WHERE "Region_province"."code" = \'320382\' LIMIT 1\' at line 1')

my database conf：

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',  
        'NAME': 'jxzp',  
        'USER': 'root',  
        'PASSWORD': 'lp990324',  
        'HOST': '127.0.0.1',  
        'PORT': '3306',  
    },
    'log': {
        'ENGINE': 'django.db.backends.sqlite3',
        'NAME': BASE_DIR / 'Log' / 'log.sqlite3',
    },
    'region': {
        'ENGINE': 'django.db.backends.sqlite3',
        'NAME': BASE_DIR / 'Region' / 'region.sqlite3',
    },
    'user_files': {
        'ENGINE': 'django.db.backends.sqlite3',
        'NAME': BASE_DIR / 'UserFiles' / 'user_files.sqlite3',
    }
}
DATABASE_ROUTERS = ['jxzp_backend_python.database_router.DatabaseAppsRouter']
DATABASE_APPS_MAPPING = {
    # app_name: database_name
    'Log': 'log',
    'Region': 'region',
    'UserFiles': 'user_files',
}

from the error, we can see that sqlite syntax are using to mysql, so it errors

can this problem be solved?

Thank you

[eduzen]

Hey @LvPeng990324 maybe you could share a little bit of more context to understand the issue. When is it triggering the issue? Does it happen for all you requests or for some in particular? Maybe you can share the full traceback.

[LvPeng990324]

Hey @LvPeng990324 maybe you could share a little bit of more context to understand the issue. When is it triggering the issue? Does it happen for all you requests or for some in particular? Maybe you can share the full traceback.

Errors occur when it comes to requesting sqlite databases, not all requests will fail The full traceback is:

Internal Server Error: /api/enterprise/enterprise-detail/
Traceback (most recent call last):
  File "C:\Users\pc\AppData\Local\Programs\Python\Python38\lib\site-packages\django\db\backends\utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "C:\Users\pc\AppData\Local\Programs\Python\Python38\lib\site-packages\django\db\backends\mysql\base.py", line 73, in execute
    return self.cursor.execute(query, args)
  File "C:\Users\pc\AppData\Local\Programs\Python\Python38\lib\site-packages\MySQLdb\cursors.py", line 206, in execute
    res = self._query(query)
  File "C:\Users\pc\AppData\Local\Programs\Python\Python38\lib\site-packages\MySQLdb\cursors.py", line 319, in _query
    db.query(q)
  File "C:\Users\pc\AppData\Local\Programs\Python\Python38\lib\site-packages\MySQLdb\connections.py", line 259, in query
    _mysql.connection.query(self, query)
MySQLdb._exceptions.ProgrammingError: (1064, 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near \'"Region_province" WHERE "Region_province"."code" = \'320000\' LIMIT 1\' at line 1')

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "C:\Users\pc\AppData\Local\Programs\Python\Python38\lib\site-packages\django\core\handlers\exception.py", line 47, in inner
    response = get_response(request)
  File "C:\Users\pc\AppData\Local\Programs\Python\Python38\lib\site-packages\django\core\handlers\base.py", line 181, in _get_response
    response = wrapped_callback(request, *callback_args, **callback_kwargs)
  File "C:\Users\pc\AppData\Local\Programs\Python\Python38\lib\site-packages\django\views\decorators\csrf.py", line 54, in wrapped_view
    return view_func(*args, **kwargs)
  File "C:\Users\pc\AppData\Local\Programs\Python\Python38\lib\site-packages\django\views\generic\base.py", line 70, in view
    return self.dispatch(request, *args, **kwargs)
  File "C:\Users\pc\AppData\Local\Programs\Python\Python38\lib\site-packages\rest_framework\views.py", line 509, in dispatch
    response = self.handle_exception(exc)
  File "C:\Users\pc\AppData\Local\Programs\Python\Python38\lib\site-packages\rest_framework\views.py", line 469, in handle_exception
    self.raise_uncaught_exception(exc)
  File "C:\Users\pc\AppData\Local\Programs\Python\Python38\lib\site-packages\rest_framework\views.py", line 480, in raise_uncaught_exception
    raise exc
  File "C:\Users\pc\AppData\Local\Programs\Python\Python38\lib\site-packages\rest_framework\views.py", line 506, in dispatch
    response = handler(request, *args, **kwargs)
  File "C:\Users\pc\Desktop\jxzp\jxzp_backend_python\Enterprise\views\EnterpriseDetail.py", line 111, in get
    get_area_name(enterprise.province)[0],  # 省
  File "C:\Users\pc\Desktop\jxzp\jxzp_backend_python\Region\utils.py", line 16, in get_area_name
    if area.exists():
  File "C:\Users\pc\AppData\Local\Programs\Python\Python38\lib\site-packages\django\db\models\query.py", line 808, in exists
    return self.query.has_results(using=self.db)
  File "C:\Users\pc\AppData\Local\Programs\Python\Python38\lib\site-packages\django\db\models\sql\query.py", line 559, in has_results
    return compiler.has_results()
  File "C:\Users\pc\AppData\Local\Programs\Python\Python38\lib\site-packages\django\db\models\sql\compiler.py", line 1139, in has_results
    return bool(self.execute_sql(SINGLE))
  File "C:\Users\pc\AppData\Local\Programs\Python\Python38\lib\site-packages\silk\sql.py", line 94, in execute_sql
    query_dict['analysis'] = _explain_query(q, params)
  File "C:\Users\pc\AppData\Local\Programs\Python\Python38\lib\site-packages\silk\sql.py", line 56, in _explain_query
    cur.execute(prefixed_query, params)
  File "C:\Users\pc\AppData\Local\Programs\Python\Python38\lib\site-packages\django\db\backends\utils.py", line 98, in execute
    return super().execute(sql, params)
  File "C:\Users\pc\AppData\Local\Programs\Python\Python38\lib\site-packages\django\db\backends\utils.py", line 66, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "C:\Users\pc\AppData\Local\Programs\Python\Python38\lib\site-packages\django\db\backends\utils.py", line 75, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "C:\Users\pc\AppData\Local\Programs\Python\Python38\lib\site-packages\django\db\backends\utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "C:\Users\pc\AppData\Local\Programs\Python\Python38\lib\site-packages\django\db\utils.py", line 90, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "C:\Users\pc\AppData\Local\Programs\Python\Python38\lib\site-packages\django\db\backends\utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "C:\Users\pc\AppData\Local\Programs\Python\Python38\lib\site-packages\django\db\backends\mysql\base.py", line 73, in execute
    return self.cursor.execute(query, args)
  File "C:\Users\pc\AppData\Local\Programs\Python\Python38\lib\site-packages\MySQLdb\cursors.py", line 206, in execute
    res = self._query(query)
  File "C:\Users\pc\AppData\Local\Programs\Python\Python38\lib\site-packages\MySQLdb\cursors.py", line 319, in _query
    db.query(q)
  File "C:\Users\pc\AppData\Local\Programs\Python\Python38\lib\site-packages\MySQLdb\connections.py", line 259, in query
    _mysql.connection.query(self, query)
django.db.utils.ProgrammingError: (1064, 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near \'"Region_province" WHERE "Region_province"."code" = \'320000\' LIMIT 1\' at line 1')
[07/Feb/2022 10:16:27] "GET /api/enterprise/enterprise-detail/?enterpriseId=7 HTTP/1.1" 500 209163

[eduzen]

Hey I'm not sure if silk can handle different databases following the config in your settings with multiple types of databases.