ProgrammingError: 1064在使用django和mysql数据库执行inspectdb时

Question

我在settings.py中配置了两个数据库：

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': 'postgres',
        'USER': 'postgres',
        'PASSWORD': 'password',
        'HOST': '127.0.0.1',
        'PORT': '5432',
    },
    'db2': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'database',
        'USER': 'user',
        'PASSWORD': 'password',
        'HOST': 'ip address',
        'PORT': 'port',
    }
}

承载db2的服务器使用MySQL4.1.2。我正在使用Python3.7.3+ django 2.2.5 +mysqlClient1.4.4。

当我运行python manage.py inspectdb --database db2时，会得到以下错误：

django.db.utils.ProgrammingError：(1064，“您的SQL >语法有错误；请检查与MySQL服务器版本相对应的手册，以获得>在第1行使用”表“附近的正确语法”)

我打印了“查询”，它是：SHOW FULL TABLES

我尝试手动连接到数据库(MySQLdb.connect +游标)，并从表中检索数据，并且运行良好。

我的问题基本上和(错误1064 Django检查Django)一样，但是那个人没有得到任何帮助。希望我会的。

我想要完成的是在默认情况下使用来自db2的数据作为外键。例如，我在默认数据库中描述了一些项，并在db2中定义了一些人。我希望将db2中的某个人与默认项相关联。为了做到这一点，我认为用inspectdb建立一个模型会有帮助。

全部产出如下：

# This is an auto-generated Django model module.
# You'll have to do the following manually to clean this up:
#   * Rearrange models' order
#   * Make sure each model has one field with primary_key=True
#   * Make sure each ForeignKey has `on_delete` set to the desired behavior.
#   * Remove `managed = False` lines if you wish to allow Django to create, modify, and delete the table
# Feel free to rename the models, but don't rename db_table values or field names.
from django.db import models
Traceback (most recent call last):
  File "C:\Users\nxf52820\OneDrive - NXP\Aplicatii\PyCharm\AMPLab\AMPLabEnv\lib\site-packages\django\db\backends\utils.py", line 82, in _execute
    return self.cursor.execute(sql)
  File "C:\Users\nxf52820\OneDrive - NXP\Aplicatii\PyCharm\AMPLab\AMPLabEnv\lib\site-packages\django\db\backends\mysql\base.py", line 71, in execute
    return self.cursor.execute(query, args)
  File "C:\Users\nxf52820\OneDrive - NXP\Aplicatii\PyCharm\AMPLab\AMPLabEnv\lib\site-packages\MySQLdb\cursors.py", line 209, in execute
    res = self._query(query)
  File "C:\Users\nxf52820\OneDrive - NXP\Aplicatii\PyCharm\AMPLab\AMPLabEnv\lib\site-packages\MySQLdb\cursors.py", line 315, in _query
    db.query(q)
  File "C:\Users\nxf52820\OneDrive - NXP\Aplicatii\PyCharm\AMPLab\AMPLabEnv\lib\site-packages\MySQLdb\connections.py", line 226, 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 'TABLES' at line 1")

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

Traceback (most recent call last):
  File "manage.py", line 21, in <module>
    main()
  File "manage.py", line 17, in main
    execute_from_command_line(sys.argv)
  File "C:\Users\nxf52820\OneDrive - NXP\Aplicatii\PyCharm\AMPLab\AMPLabEnv\lib\site-packages\django\core\management\__init__.py", line 381, in execute_from_command_line
    utility.execute()
  File "C:\Users\nxf52820\OneDrive - NXP\Aplicatii\PyCharm\AMPLab\AMPLabEnv\lib\site-packages\django\core\management\__init__.py", line 375, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "C:\Users\nxf52820\OneDrive - NXP\Aplicatii\PyCharm\AMPLab\AMPLabEnv\lib\site-packages\django\core\management\base.py", line 323, in run_from_argv
    self.execute(*args, **cmd_options)
  File "C:\Users\nxf52820\OneDrive - NXP\Aplicatii\PyCharm\AMPLab\AMPLabEnv\lib\site-packages\django\core\management\base.py", line 364, in execute
    output = self.handle(*args, **options)
  File "C:\Users\nxf52820\OneDrive - NXP\Aplicatii\PyCharm\AMPLab\AMPLabEnv\lib\site-packages\django\core\management\commands\inspectdb.py", line 34, in handle
    for line in self.handle_inspection(options):
  File "C:\Users\nxf52820\OneDrive - NXP\Aplicatii\PyCharm\AMPLab\AMPLabEnv\lib\site-packages\django\core\management\commands\inspectdb.py", line 60, in handle_inspection
    table_info = connection.introspection.get_table_list(cursor)
  File "C:\Users\nxf52820\OneDrive - NXP\Aplicatii\PyCharm\AMPLab\AMPLabEnv\lib\site-packages\django\db\backends\mysql\introspection.py", line 55, in get_table_list
    cursor.execute("SHOW FULL TABLES")
  File "C:\Users\nxf52820\OneDrive - NXP\Aplicatii\PyCharm\AMPLab\AMPLabEnv\lib\site-packages\django\db\backends\utils.py", line 99, in execute
    return super().execute(sql, params)
  File "C:\Users\nxf52820\OneDrive - NXP\Aplicatii\PyCharm\AMPLab\AMPLabEnv\lib\site-packages\django\db\backends\utils.py", line 67, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "C:\Users\nxf52820\OneDrive - NXP\Aplicatii\PyCharm\AMPLab\AMPLabEnv\lib\site-packages\django\db\backends\utils.py", line 76, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "C:\Users\nxf52820\OneDrive - NXP\Aplicatii\PyCharm\AMPLab\AMPLabEnv\lib\site-packages\django\db\backends\utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "C:\Users\nxf52820\OneDrive - NXP\Aplicatii\PyCharm\AMPLab\AMPLabEnv\lib\site-packages\django\db\utils.py", line 89, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "C:\Users\nxf52820\OneDrive - NXP\Aplicatii\PyCharm\AMPLab\AMPLabEnv\lib\site-packages\django\db\backends\utils.py", line 82, in _execute
    return self.cursor.execute(sql)
  File "C:\Users\nxf52820\OneDrive - NXP\Aplicatii\PyCharm\AMPLab\AMPLabEnv\lib\site-packages\django\db\backends\mysql\base.py", line 71, in execute
    return self.cursor.execute(query, args)
  File "C:\Users\nxf52820\OneDrive - NXP\Aplicatii\PyCharm\AMPLab\AMPLabEnv\lib\site-packages\MySQLdb\cursors.py", line 209, in execute
    res = self._query(query)
  File "C:\Users\nxf52820\OneDrive - NXP\Aplicatii\PyCharm\AMPLab\AMPLabEnv\lib\site-packages\MySQLdb\cursors.py", line 315, in _query
    db.query(q)
  File "C:\Users\nxf52820\OneDrive - NXP\Aplicatii\PyCharm\AMPLab\AMPLabEnv\lib\site-packages\MySQLdb\connections.py", line 226, 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 'TABLES' at line 1")

编辑:我已经确定了发送查询的函数：

    def get_table_list(self, cursor):
        """Return a list of table and view names in the current database."""
        cursor.execute("SHOW FULL TABLES")
        return [TableInfo(row[0], {'BASE TABLE': 't', 'VIEW': 'v'}.get(row[1]))
                for row in cursor.fetchall()]

这一点在以下方面得到进一步使用：

    def handle_inspection(self, options):
        connection = connections[options['database']]
        # 'table_name_filter' is a stealth option
        table_name_filter = options.get('table_name_filter')

        def table2model(table_name):
            return re.sub(r'[^a-zA-Z0-9]', '', table_name.title())

        with connection.cursor() as cursor:
            yield "# This is an auto-generated Django model module."
            yield "# You'll have to do the following manually to clean this up:"
            yield "#   * Rearrange models' order"
            yield "#   * Make sure each model has one field with primary_key=True"
            yield "#   * Make sure each ForeignKey has `on_delete` set to the desired behavior."
            yield (
                "#   * Remove `managed = False` lines if you wish to allow "
                "Django to create, modify, and delete the table"
            )
            yield "# Feel free to rename the models, but don't rename db_table values or field names."
            yield 'from %s import models' % self.db_module
            known_models = []
            table_info = connection.introspection.get_table_list(cursor)

            # Determine types of tables and/or views to be introspected.
            types = {'t'}
            if options['include_partitions']:
                types.add('p')
            if options['include_views']:
                types.add('v')

            for table_name in (options['table'] or sorted(info.name for info in table_info if info.type in types)):
                if table_name_filter is not None and callable(table_name_filter):
                    if not table_name_filter(table_name):
                        continue
                try:
                    try:
                        relations = connection.introspection.get_relations(cursor, table_name)
                    except NotImplementedError:
                        relations = {}
                    try:
                        constraints = connection.introspection.get_constraints(cursor, table_name)
                    except NotImplementedError:
                        constraints = {}
                    primary_key_column = connection.introspection.get_primary_key_column(cursor, table_name)
                    unique_columns = [
                        c['columns'][0] for c in constraints.values()
                        if c['unique'] and len(c['columns']) == 1
                    ]
                    table_description = connection.introspection.get_table_description(cursor, table_name)
                except Exception as e:
                    yield "# Unable to inspect table '%s'" % table_name
                    yield "# The error was: %s" % e
                    continue

                yield ''
                yield ''
                yield 'class %s(models.Model):' % table2model(table_name)
                known_models.append(table2model(table_name))
                used_column_names = []  # Holds column names used in the table so far
                column_to_field_name = {}  # Maps column names to names of model fields
                for row in table_description:
                    comment_notes = []  # Holds Field notes, to be displayed in a Python comment.
                    extra_params = OrderedDict()  # Holds Field parameters such as 'db_column'.
                    column_name = row.name
                    is_relation = column_name in relations

                    att_name, params, notes = self.normalize_col_name(
                        column_name, used_column_names, is_relation)
                    extra_params.update(params)
                    comment_notes.extend(notes)

                    used_column_names.append(att_name)
                    column_to_field_name[column_name] = att_name

                    # Add primary_key and unique, if necessary.
                    if column_name == primary_key_column:
                        extra_params['primary_key'] = True
                    elif column_name in unique_columns:
                        extra_params['unique'] = True

                    if is_relation:
                        rel_to = (
                            "self" if relations[column_name][1] == table_name
                            else table2model(relations[column_name][1])
                        )
                        if rel_to in known_models:
                            field_type = 'ForeignKey(%s' % rel_to
                        else:
                            field_type = "ForeignKey('%s'" % rel_to
                    else:
                        # Calling `get_field_type` to get the field type string and any
                        # additional parameters and notes.
                        field_type, field_params, field_notes = self.get_field_type(connection, table_name, row)
                        extra_params.update(field_params)
                        comment_notes.extend(field_notes)

                        field_type += '('

                    # Don't output 'id = meta.AutoField(primary_key=True)', because
                    # that's assumed if it doesn't exist.
                    if att_name == 'id' and extra_params == {'primary_key': True}:
                        if field_type == 'AutoField(':
                            continue
                        elif field_type == 'IntegerField(' and not connection.features.can_introspect_autofield:
                            comment_notes.append('AutoField?')

                    # Add 'null' and 'blank', if the 'null_ok' flag was present in the
                    # table description.
                    if row.null_ok:  # If it's NULL...
                        extra_params['blank'] = True
                        extra_params['null'] = True

                    field_desc = '%s = %s%s' % (
                        att_name,
                        # Custom fields will have a dotted path
                        '' if '.' in field_type else 'models.',
                        field_type,
                    )
                    if field_type.startswith('ForeignKey('):
                        field_desc += ', models.DO_NOTHING'

                    if extra_params:
                        if not field_desc.endswith('('):
                            field_desc += ', '
                        field_desc += ', '.join('%s=%r' % (k, v) for k, v in extra_params.items())
                    field_desc += ')'
                    if comment_notes:
                        field_desc += '  # ' + ' '.join(comment_notes)
                    yield '    %s' % field_desc
                is_view = any(info.name == table_name and info.type == 'v' for info in table_info)
                is_partition = any(info.name == table_name and info.type == 'p' for info in table_info)
                for meta_line in self.get_meta(table_name, constraints, column_to_field_name, is_view, is_partition):
                    yield meta_line

如果将SHOW FULL TABLES替换为SHOW TABLES，则还需要更改函数的返回。如果我能以一种与当前函数返回的方式相当的方式来完成这个任务，但没有视图部分，有什么想法吗？

Answer 1

您的MySQL版本(4.1.2)大约有15年历史，与Django的最新版本不兼容。

文档指出：

Django支持MySQL 5.6及更高版本。

请更新数据库服务器，或选择不同的数据库选项使用Django。