How to view the generated SQL for test models?

[Brutus Schraiber]

Hi all…

I have some models in a `test.py` file. How can I view the SQL that will be generated for them?

Something like `django-admin sql` or similar but for models defined in `test.py` not in `models.py`.

Regards,
-brutus

[Brutus Schraiber]

Wrong place to ask such things?

This doesn't seem too uncommon or complicated, or am I missing the point?

[Vijay Khemlani]

At least to me it doesn't make a lot of sense to define new models in your tests, but I also don't know the particular problem you are solving.

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users...@googlegroups.com.
To post to this group, send email to django...@googlegroups.com.
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/d74d74d4-770b-464a-bcf0-55b90424a724%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

[Michal Petrucha]

Django always expects models in the models module. If you need a
separate set of models just for tests, you'll need to set up a
separate test app with any additional test models.

Good luck,
Michal

[Brutus Schraiber]

Am Samstag, 16. Januar 2016 22:23:06 UTC+1 schrieb Vijay Khemlani:

At least to me it doesn't make a lot of sense to define new models in your tests, but I also don't know the particular problem you are solving.

I have an app, that defines a couple of abstract models and mixins in it's `models.py`, that are only used in other apps.

To test those abstract models and mixins I created concrete models (from `django.db.models.Model`), that use these abstract models and mixins, in `test.py`.

I created them in `test.py`, so that the test models don't get created in the DB outside of test running. I thought this the way to handle such things in Django?

At least for me this generally works fine. In a special case I get an `django.db.utils.ProgrammingError` from one of my test models though. To debug this, I wanted to take a look at the SQL Django generated for it.

[Michal Petrucha]

On Mon, Jan 18, 2016 at 04:25:59AM -0800, Brutus Schraiber wrote:
> Am Samstag, 16. Januar 2016 22:23:06 UTC+1 schrieb Vijay Khemlani:
> >

> > At least to me it doesn't make a lot of sense to define new models in your
> > tests, but I also don't know the particular problem you are solving.
> >
>

> I have an app, that defines a couple of *abstract models* and *mixins* in

> it's `models.py`, that are only used in other apps.
>
> To test those abstract models and mixins I created concrete models (from
> `django.db.models.Model`), that use these abstract models and mixins, in
> `test.py`.
>
> I created them in `test.py`, so that the test models don't get created in
> the DB outside of test running. I thought this the way to handle such
> things in Django?
>
> At least for me this generally works fine. In a special case I get an
> `django.db.utils.ProgrammingError` from one of my test models though. To
> debug this, I wanted to take a look at the SQL Django generated for it.

Hi Brutus,

Could you perhaps post the full traceback of the ProgrammingError? It
smells a bit like your test models don't get imported early enough to
be fully initialized before database creation happens. And this might
be caused precisely by the fact that your models are not inside
models.py.

As for your original question, unless you put all your models into
models.py of an app inside INSTALLED_APPS, or at least import them
from within such a models.py module, they won't be considered at all
when generating migrations, or when dumping out the creation SQL.

When you run a management command, ``django.setup()`` is called, which
populates the model registry by importing the models module of each
installed app, not much else. Particularly not app.tests. So there's
no way for the ``sql`` management command to be aware of your test
models.

The tests module is only imported when running tests by the test
runner (unless you import it from somewhere else in your app, but
that's not a very usual thing to do). That happens after
``django.setup()`` has finished, at which point models should already
be properly initialized, which, obviously, is not the case with models
in tests.py.

In theory, it should work – the test runner tries to import all test
modlues before setting up the test database, so in case everything is
correct, all deferred model setup actions should still finish before
creating the database. However, if there is some kind of error, they
might not, and that means it will be harder to debug those situations.

Really, do yourself a favor, create a separate app for your tests,
with its own models, and its own settings. That is the most painless
way to have additional testing models.

You can take inspiration from the following section of the docs:
https://docs.djangoproject.com/en/1.9/topics/testing/advanced/#using-the-django-test-runner-to-test-reusable-applications

Regards,

Michal

[Brutus]

Hi Michal,


# The `ProgrammingError` Exception

Am 18.01.2016 um 20:23 schrieb Michal Petrucha:
> Could you perhaps post the full traceback of the ProgrammingError?

The original exception was (full traceback at the end of the mail):

```
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 'None, None) NOT NULL)' at line 1")
```

Since the error message is kinda meh without SQL to look at, I wanted to know
how to see the SQL Django generates.

> It smells a bit like your test models don't get imported early enough to
> be fully initialized before database creation happens. And this might be
> caused precisely by the fact that your models are not inside models.py.

Having no SQL to look at, made the debugging slightly tedious, but I found the
error in the `core.PlaytimeMixinTestModel` — could narrow it down a bit thanks
to verbose mode (see the full traceback below):

A `Decimal Field` was missing two arguments that where necessary (`max_digits`
and `decimal_places`). This slipped by unnoticed, when using the SQLite
backend local but raises an Error on the MySQL DB on the staging servers.

After fixing it, the test run fine on MySQL too.


# `test.py`

> As for your original question, unless you put all your models into
> models.py of an app inside INSTALLED_APPS, or at least import them from
> within such a models.py module, they won't be considered at all when
> generating migrations, or when dumping out the creation SQL.
>
> When you run a management command, ``django.setup()`` is called, which
> populates the model registry by importing the models module of each
> installed app, not much else. Particularly not app.tests. So there's no
> way for the ``sql`` management command to be aware of your test models.
>
> The tests module is only imported when running tests by the test runner
> (unless you import it from somewhere else in your app, but that's not a
> very usual thing to do). That happens after ``django.setup()`` has
> finished, at which point models should already be properly initialized,
> which, obviously, is not the case with models in tests.py.
>
> In theory, it should work – the test runner tries to import all test
> modlues before setting up the test database, so in case everything is
> correct, all deferred model setup actions should still finish before
> creating the database. However, if there is some kind of error, they might
> not, and that means it will be harder to debug those situations.

Thanks for the detailed info. Makes some things clearer.

I wasn't aware why it worked — but it usually does. It's common style for the
team I work with to put models that are used only for testing of an in a
`test.py` file. BTW: even when the actual test are spread to a test package.

I couldn't find any documentation about it though.

> Really, do yourself a favor, create a separate app for your tests, with
> its own models, and its own settings. That is the most painless way to
> have additional testing models.
>
> You can take inspiration from the following section of the docs:
> https://docs.djangoproject.com/en/1.9/topics/testing/advanced/#using-the-django-test-runner-to-test-reusable-applications

It is quite a bit overhead — even in it's simplest form — but sounds like a
clean way to handle such cases. I will consider it in the future.

Thanks again for your answer
-brutus



# Full Traceback

```
$ django-admin test app -v 3
Creating test database for alias 'default' ('yogalessontv_test')...
Got an error creating the test database: (1007, "Can't create database
'yogalessontv_test'; database exists")
Type 'yes' if you would like to try deleting the test database
'yogalessontv_test', or 'no' to cancel: yes
Destroying old test database 'default'...
Operations to perform:
Synchronize unmigrated apps: streambox, profiles, core, suit, contact,
allauth, messages, staticfiles, debug_toolbar
Apply all migrations: admin, auth, carousel, sites, pages, socialaccount,
blog, contenttypes, account, sessions, videos
Synchronizing apps without migrations:
Running pre-migrate handlers for application core
Running pre-migrate handlers for application pages
Running pre-migrate handlers for application blog
Running pre-migrate handlers for application videos
Running pre-migrate handlers for application carousel
Running pre-migrate handlers for application suit
Running pre-migrate handlers for application admin
Running pre-migrate handlers for application auth
Running pre-migrate handlers for application contenttypes
Running pre-migrate handlers for application sessions
Running pre-migrate handlers for application sites
Running pre-migrate handlers for application allauth
Running pre-migrate handlers for application account
Running pre-migrate handlers for application socialaccount
Running pre-migrate handlers for application debug_toolbar
Creating tables...
Creating table core_trimstringsmixintestmodel
Creating table core_playtimemixintestmodel
Traceback (most recent call last):
File
"/home/xxx/virtualenv/myproject/lib/python3.4/site-packages/django/db/backends/utils.py",
line 62, in execute
return self.cursor.execute(sql)
File
"/home/xxx/virtualenv/myproject/lib/python3.4/site-packages/django/db/backends/mysql/base.py",
line 124, in execute
return self.cursor.execute(query, args)
File
"/home/xxx/virtualenv/myproject/lib/python3.4/site-packages/MySQLdb/cursors.py",
line 226, in execute
self.errorhandler(self, exc, value)
File
"/home/xxx/virtualenv/myproject/lib/python3.4/site-packages/MySQLdb/connections.py",
line 36, in defaulterrorhandler
raise errorvalue
File
"/home/xxx/virtualenv/myproject/lib/python3.4/site-packages/MySQLdb/cursors.py",
line 217, in execute
res = self._query(query)
File
"/home/xxx/virtualenv/myproject/lib/python3.4/site-packages/MySQLdb/cursors.py",
line 378, in _query
rowcount = self._do_query(q)
File
"/home/xxx/virtualenv/myproject/lib/python3.4/site-packages/MySQLdb/cursors.py",
line 341, in _do_query
db.query(q)
File
"/home/xxx/virtualenv/myproject/lib/python3.4/site-packages/MySQLdb/connections.py",
line 280, in query
_mysql.connection.query(self, query)
_mysql_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 'None, None) NOT NULL)' at line 1")

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

Traceback (most recent call last):
File "/home/xxx/virtualenv/myproject/bin/django-admin", line 11, in <module>
sys.exit(execute_from_command_line())
File
"/home/xxx/virtualenv/myproject/lib/python3.4/site-packages/django/core/management/__init__.py",
line 338, in execute_from_command_line
utility.execute()
File
"/home/xxx/virtualenv/myproject/lib/python3.4/site-packages/django/core/management/__init__.py",
line 330, in execute
self.fetch_command(subcommand).run_from_argv(self.argv)
File
"/home/xxx/virtualenv/myproject/lib/python3.4/site-packages/django/core/management/commands/test.py",
line 30, in run_from_argv
super(Command, self).run_from_argv(argv)
File
"/home/xxx/virtualenv/myproject/lib/python3.4/site-packages/django/core/management/base.py",
line 390, in run_from_argv
self.execute(*args, **cmd_options)
File
"/home/xxx/virtualenv/myproject/lib/python3.4/site-packages/django/core/management/commands/test.py",
line 74, in execute
super(Command, self).execute(*args, **options)
File
"/home/xxx/virtualenv/myproject/lib/python3.4/site-packages/django/core/management/base.py",
line 441, in execute
output = self.handle(*args, **options)
File
"/home/xxx/virtualenv/myproject/lib/python3.4/site-packages/django/core/management/commands/test.py",
line 90, in handle
failures = test_runner.run_tests(test_labels)
File
"/home/xxx/virtualenv/myproject/lib/python3.4/site-packages/django/test/runner.py",
line 210, in run_tests
old_config = self.setup_databases()
File
"/home/xxx/virtualenv/myproject/lib/python3.4/site-packages/django/test/runner.py",
line 166, in setup_databases
**kwargs
File
"/home/xxx/virtualenv/myproject/lib/python3.4/site-packages/django/test/runner.py",
line 370, in setup_databases
serialize=connection.settings_dict.get("TEST", {}).get("SERIALIZE", True),
File
"/home/xxx/virtualenv/myproject/lib/python3.4/site-packages/django/db/backends/base/creation.py",
line 368, in create_test_db
test_flush=True,
File
"/home/xxx/virtualenv/myproject/lib/python3.4/site-packages/django/core/management/__init__.py",
line 120, in call_command
return command.execute(*args, **defaults)
File
"/home/xxx/virtualenv/myproject/lib/python3.4/site-packages/django/core/management/base.py",
line 441, in execute
output = self.handle(*args, **options)
File
"/home/xxx/virtualenv/myproject/lib/python3.4/site-packages/django/core/management/commands/migrate.py",
line 179, in handle
created_models = self.sync_apps(connection, executor.loader.unmigrated_apps)
File
"/home/xxx/virtualenv/myproject/lib/python3.4/site-packages/django/core/management/commands/migrate.py",
line 309, in sync_apps
editor.create_model(model)
File
"/home/xxx/virtualenv/myproject/lib/python3.4/site-packages/django/db/backends/base/schema.py",
line 282, in create_model
self.execute(sql, params or None)
File
"/home/xxx/virtualenv/myproject/lib/python3.4/site-packages/django/db/backends/base/schema.py",
line 107, in execute
cursor.execute(sql, params)
File
"/home/xxx/virtualenv/myproject/lib/python3.4/site-packages/django/db/backends/utils.py",
line 64, in execute
return self.cursor.execute(sql, params)
File
"/home/xxx/virtualenv/myproject/lib/python3.4/site-packages/django/db/utils.py",
line 97, in __exit__
six.reraise(dj_exc_type, dj_exc_value, traceback)
File
"/home/xxx/virtualenv/myproject/lib/python3.4/site-packages/django/utils/six.py",
line 658, in reraise
raise value.with_traceback(tb)
File
"/home/xxx/virtualenv/myproject/lib/python3.4/site-packages/django/db/backends/utils.py",
line 62, in execute
return self.cursor.execute(sql)
File
"/home/xxx/virtualenv/myproject/lib/python3.4/site-packages/django/db/backends/mysql/base.py",
line 124, in execute
return self.cursor.execute(query, args)
File
"/home/xxx/virtualenv/myproject/lib/python3.4/site-packages/MySQLdb/cursors.py",
line 226, in execute
self.errorhandler(self, exc, value)
File
"/home/xxx/virtualenv/myproject/lib/python3.4/site-packages/MySQLdb/connections.py",
line 36, in defaulterrorhandler
raise errorvalue
File
"/home/xxx/virtualenv/myproject/lib/python3.4/site-packages/MySQLdb/cursors.py",
line 217, in execute
res = self._query(query)
File
"/home/xxx/virtualenv/myproject/lib/python3.4/site-packages/MySQLdb/cursors.py",
line 378, in _query
rowcount = self._do_query(q)
File
"/home/xxx/virtualenv/myproject/lib/python3.4/site-packages/MySQLdb/cursors.py",
line 341, in _do_query
db.query(q)
File
"/home/xxx/virtualenv/myproject/lib/python3.4/site-packages/MySQLdb/connections.py",
line 280, 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 'None, None) NOT NULL)' at line 1")
```

[Brutus Schraiber]

Hi Michal,


# The `ProgrammingError` Exception

Am 18.01.2016 um 20:23 schrieb Michal Petrucha:

> Could you perhaps post the full traceback of the ProgrammingError?

The original exception was (full traceback at the end of the mail):

```
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 'None, None) NOT NULL)' at line 1")
```

Since the error message is kinda meh without SQL to look at, I wanted to know how to see the SQL Django generates.

> It smells a bit like your test models don't get imported early enough to
> be fully initialized before database creation happens. And this might be
> caused precisely by the fact that your models are not inside models.py.

Having no SQL to look at, made the debugging slightly tedious, but I found the error in the `core.PlaytimeMixinTestModel` — could narrow it down a bit thanks to verbose mode (see the full traceback below):

A `Decimal Field` was missing two arguments that where necessary (`max_digits` and `decimal_places`). This slipped by unnoticed, when using the SQLite backend local but raises an Error on the MySQL DB on the staging servers.

After fixing it, the test run fine on MySQL too.


# `test.py`

> As for your original question, unless you put all your models into
> models.py of an app inside INSTALLED_APPS, or at least import them from
> within such a models.py module, they won't be considered at all when
> generating migrations, or when dumping out the creation SQL.
>
> When you run a management command, ``django.setup()`` is called, which
> populates the model registry by importing the models module of each
> installed app, not much else. Particularly not app.tests. So there's no
> way for the ``sql`` management command to be aware of your test models.
>
> The tests module is only imported when running tests by the test runner
> (unless you import it from somewhere else in your app, but that's not a
> very usual thing to do). That happens after ``django.setup()`` has
> finished, at which point models should already be properly initialized,
> which, obviously, is not the case with models in tests.py.
>
> In theory, it should work – the test runner tries to import all test
> modlues before setting up the test database, so in case everything is
> correct, all deferred model setup actions should still finish before
> creating the database. However, if there is some kind of error, they might
> not, and that means it will be harder to debug those situations.

Thanks for the detailed info. Makes some things clearer.

I wasn't aware why it worked — but it usually does. It's common style for the team I work with to put models that are used only for testing of an in a `test.py` file. BTW: even when the actual test are spread to a test package.

I couldn't find any documentation about it though.

> Really, do yourself a favor, create a separate app for your tests, with
> its own models, and its own settings. That is the most painless way to
> have additional testing models.
>
> You can take inspiration from the following section of the docs:
> https://docs.djangoproject.com/en/1.9/topics/testing/advanced/#using-the-django-test-runner-to-test-reusable-applications