I have two databases configured in 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',
}
}
The server which hosts db2 uses mysql 4.1.2. I am using Python 3.7.3 + django 2.2.5 + mysqlclient 1.4.4.
When I run python manage.py inspectdb --database db2 I get the following error:
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")
I have printed the 'query' and it is: SHOW FULL TABLES
I have tried to manually connect to the database (MySQLdb.connect + cursor) and retrieve data from a table and it worked fine.
My problem is basically the same as (Error 1064 Django inspectdb), but that guy didn't get any help. Hopefully I will.
What I am trying to accomplish is use data from db2 as foreign keys in the default. For example, I have some items described in the default database and some persons defined in db2. I would like to associate a person from db2 to an item from default. In order to do this, I thought that building a model with inspectdb would help.
The full output is:
# 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")
EDIT: I have identified the function which send the query:
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()]
This is further used in:
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
If I replace SHOW FULL TABLES with SHOW TABLES, I would also need to change the return of the function. Any ideas if I could do this in a way that would be equivalent with what the function currently returns, but without the VIEW part?
Your version of MySQL (4.1.2) is about 15 years old and not compatible with recent versions of Django.
The documentation states:
Django supports MySQL 5.6 and higher.
Please update your database server or choose a different database option to use Django.