Forums

Hi, I've got a django project that I'm using with sqlite, but I would like to start using MySQL instead. Following advice given on https://stackoverflow.com/questions/3034910/whats-the-best-way-to-migrate-a-django-db-from-sqlite-to-mysql/3036241, I've run:

python manage.py dumpdata > datadump.json

so I have a copy of the data, and I've changed my settings.py to contain:

DATABASES = {
'default': {
    'ENGINE': 'django.db.backends.mysql',
    'NAME': 'username$database_name',
    'USER': 'username',
    'PASSWORD': 'mysql_password',
    'HOST': 'mysql_hostname',
}

}

as in https://help.pythonanywhere.com/pages/UsingMySQL.

I then ran:

python manage.py migrate --run-syncdb

to try to get the tables set up, but I get the error:

File "/home/bobbyarcher/.virtualenvs/gymenv/lib/python3.7/site-packages/MySQLdb/connections.py", line 280, in query
_mysql.connection.query(self, query)
django.db.utils.ProgrammingError: (1146, "Table 'bobbyarcher$gym.Site_Settings_openinghours' doesn't exist")

The same thing happens with makemigrations.

It seems like it's assuming the tables should already exist, as opposed to having to create them in the migration, so perhaps it's assuming the old migrations have already been made. I'm a bit lost in honesty so if anyone has any idea it would be much appreciated!

If you already have migrations, I think you just need to run

python manage.py migrate

...without the "--run-syncdb" flag.

Hi Giles, that gives the same error unfortunately

Is there a step I'm missing in getting MySQL set up?

1. Create database in Databases tab
2. Change DATABASES in settings.py in django project to what's written above
3. python manage.py migrate

That should be everything you need. Can I take a look at your files? We can see them from our admin interface, but we always ask for permission first.

Yes no problem. Thanks so much.

I think the problem is that you don't have your Site_Settings app in your INSTALLED_APPS list in settings.py. Try adding it, and running the migration again.

Ooops, sorry, you do have it there -- checking further...

Perhaps the problem is that it's too far up in INSTALLED_APPS -- try moving it further down, after all of the django.xxxx ones. It might also be interesting to know what

python manage.py showmigrations

...prints out, if that doesn't work.

Tried moving it down and still getting the same error unfortunately. Also getting it for showmigrations

Thanks! I think I've worked out the underlying problem, at least. It's quite complicated, and explaining it would require posting some details of your code here, which I'd rather not do because it might possibly expose information about how it works that you'd rather keep private. I'll send you an email with the details.

Thank you so much! It is very much appreciated. I made the change you suggested and that fixed it.

Excellent, thanks for confirming! Thinking about it, I can probably post an anonymised explanation here just in case it helps other people.

A common constraint one might put into a Django app is to limit the values that a user can select in a form to a set of choices that are determined at runtime from another table in the database -- that is, from another model's objects. Normally a foreign key is the right solution, but sometimes you just want to have a simple constraint on user input rather than something at the database level.

So, some example code. We have these two models:

class Foo(models.Model):
    name = models.CharField(max_length=255)

class Bar(models.Model):
    option = models.CharField(max_length=255)

...and we want to create a Django Form that allows someone to edit a Bar object and to only be able to set its option to a value that is present as a name for a Foo object that exists at runtime.

A natural Django-esque manner to do this is to use a ChoiceField:

class BarForm(forms.Form):
    option = forms.ChoiceField(choices=[foo.name for foo in Foo.objects.all()])

This will work fine if you already have the Foo object set up and populated when you add the form to your code. But if you're starting with a fresh database, and want to run a migration to get it all set up, you have a problem. When Django starts up in order to run your migrations, it imports all of your code as a first step. Part of the code that it imports is the definition of the BarForm. But that depends on the existence of the Foo model in the database; so at import time, before the migrations have been run, it will run the Foo.objects.all(), which means that it will try to connect to the DB and read in the appropriate values -- which will fail because it hasn't got to running the migrations yet so there is no table for Foo in the DB.

The solution is to defer the getting of the list of Foo objects until it's actually needed -- that is, until you create an instance of the form, rather than when you define it. Django has a nice way to do this; the choices parameter for ChoiceField can either be an iterable object like a list, or it can be a function that returns an iterable object. So if we change the form definition to

class BarForm(forms.Form):
    option = forms.ChoiceField(choices=lambda: [foo.name for foo in Foo.objects.all()])

...then the database query will not run at import time, so the form can be imported and the migrations can be run.

This also has an extra added bonus; because in the original code the choices were read from the database at import time, if you added a new Foo object you would need to reload the website in order to pick it up in your form. Deferring the database query to form creation time means that this doesn't happen -- each time you create a new instance of the form, the database is checked and a free set of choices is read in.

Hi, Giles, I'm having the exact same problem, but not exactly sure where to incorporate the lambda expression given the idiosyncrasies of my code -- any chance you could take a look?

We can certainly take a look. What is the full error message that you're getting? That might help point to which model needs to be changed.

Thank you! Here's the error message:

django.db.utils.ProgrammingError: (1146, "Table 'dlibatique$default.core_course' doesn't exist")

Figured it out! I commented out my views (which rely on the models) and urls and set up a dummy index for each app, and then the migrations worked. Thanks!

OK -- glad to hear you worked out a solution!