I’m new to Django and web servers, and have been stuck for a few days. I inherited a Django app, python version 2.7 and Django version 1.9. The app was previously using the default SQLite databases. I want to migrate to Mysql for performance reasons.
- I loaded the data into the sqlite db using reset_db
- I installed mysqlclient
- I created a mysql db
- I created a username and password for a user and granted all permissions
- I dumped the data into a file called dump.json using
python manage.py dumpdata > dump.json - I changed the settings file to use
django.db.backends.mysql - I tried to load data using
python manage.py loaddataand withmakemigrations,migrate, it fails with the following error (with ANY manage.py command):
(eKichabiTest) ananditharaghunath@Anandithas-MacBook-Pro db-test % python manage.py migrate
Traceback (most recent call last):
File "manage.py", line 10, in <module>
execute_from_command_line(sys.argv)
File "/opt/anaconda3/envs/eKichabiTest/lib/python2.7/site-packages/django/core/management/__init__.py", line 353, in execute_from_command_line
utility.execute()
File "/opt/anaconda3/envs/eKichabiTest/lib/python2.7/site-packages/django/core/management/__init__.py", line 345, in execute
self.fetch_command(subcommand).run_from_argv(self.argv)
File "/opt/anaconda3/envs/eKichabiTest/lib/python2.7/site-packages/django/core/management/base.py", line 348, in run_from_argv
self.execute(*args, **cmd_options)
File "/opt/anaconda3/envs/eKichabiTest/lib/python2.7/site-packages/django/core/management/base.py", line 398, in execute
self.check()
File "/opt/anaconda3/envs/eKichabiTest/lib/python2.7/site-packages/django/core/management/base.py", line 426, in check
include_deployment_checks=include_deployment_checks,
File "/opt/anaconda3/envs/eKichabiTest/lib/python2.7/site-packages/django/core/checks/registry.py", line 75, in run_checks
new_errors = check(app_configs=app_configs)
File "/opt/anaconda3/envs/eKichabiTest/lib/python2.7/site-packages/django/core/checks/urls.py", line 13, in check_url_config
return check_resolver(resolver)
File "/opt/anaconda3/envs/eKichabiTest/lib/python2.7/site-packages/django/core/checks/urls.py", line 23, in check_resolver
for pattern in resolver.url_patterns:
File "/opt/anaconda3/envs/eKichabiTest/lib/python2.7/site-packages/django/utils/functional.py", line 33, in __get__
res = instance.__dict__[self.name] = self.func(instance)
File "/opt/anaconda3/envs/eKichabiTest/lib/python2.7/site-packages/django/core/urlresolvers.py", line 417, in url_patterns
patterns = getattr(self.urlconf_module, "urlpatterns", self.urlconf_module)
File "/opt/anaconda3/envs/eKichabiTest/lib/python2.7/site-packages/django/utils/functional.py", line 33, in __get__
res = instance.__dict__[self.name] = self.func(instance)
File "/opt/anaconda3/envs/eKichabiTest/lib/python2.7/site-packages/django/core/urlresolvers.py", line 410, in urlconf_module
return import_module(self.urlconf_name)
File "/opt/anaconda3/envs/eKichabiTest/lib/python2.7/importlib/__init__.py", line 37, in import_module
__import__(name)
File "/Users/ananditharaghunath/Desktop/db-test/config/urls.py", line 22, in <module>
import eKichabiDemo.views as demo
File "/Users/ananditharaghunath/Desktop/db-test/eKichabiDemo/views.py", line 11, in <module>
import ekichabi_demo
File "/Users/ananditharaghunath/Desktop/db-test/eKichabiDemo/ekichabi_demo.py", line 368, in <module>
app = HomeScreen()
File "/Users/ananditharaghunath/Desktop/db-test/eKichabiDemo/ekichabi_demo.py", line 347, in __init__
MenuHierarchyScreen([District, Village, Subvillage, Category])),
File "/Users/ananditharaghunath/Desktop/db-test/eKichabiDemo/ekichabi_demo.py", line 293, in __init__
for item in query_set:
File "/opt/anaconda3/envs/eKichabiTest/lib/python2.7/site-packages/django/db/models/query.py", line 258, in __iter__
self._fetch_all()
File "/opt/anaconda3/envs/eKichabiTest/lib/python2.7/site-packages/django/db/models/query.py", line 1074, in _fetch_all
self._result_cache = list(self.iterator())
File "/opt/anaconda3/envs/eKichabiTest/lib/python2.7/site-packages/django/db/models/query.py", line 52, in __iter__
results = compiler.execute_sql()
File "/opt/anaconda3/envs/eKichabiTest/lib/python2.7/site-packages/django/db/models/sql/compiler.py", line 848, in execute_sql
cursor.execute(sql, params)
File "/opt/anaconda3/envs/eKichabiTest/lib/python2.7/site-packages/django/db/backends/utils.py", line 79, in execute
return super(CursorDebugWrapper, self).execute(sql, params)
File "/opt/anaconda3/envs/eKichabiTest/lib/python2.7/site-packages/django/db/backends/utils.py", line 64, in execute
return self.cursor.execute(sql, params)
File "/opt/anaconda3/envs/eKichabiTest/lib/python2.7/site-packages/django/db/utils.py", line 95, in __exit__
six.reraise(dj_exc_type, dj_exc_value, traceback)
File "/opt/anaconda3/envs/eKichabiTest/lib/python2.7/site-packages/django/db/backends/utils.py", line 64, in execute
return self.cursor.execute(sql, params)
File "/opt/anaconda3/envs/eKichabiTest/lib/python2.7/site-packages/django/db/backends/mysql/base.py", line 112, in execute
return self.cursor.execute(query, args)
File "/opt/anaconda3/envs/eKichabiTest/lib/python2.7/site-packages/MySQLdb/cursors.py", line 209, in execute
res = self._query(query)
File "/opt/anaconda3/envs/eKichabiTest/lib/python2.7/site-packages/MySQLdb/cursors.py", line 315, in _query
db.query(q)
File "/opt/anaconda3/envs/eKichabiTest/lib/python2.7/site-packages/MySQLdb/connections.py", line 239, in query
_mysql.connection.query(self, query)
django.db.utils.ProgrammingError: (1146, "Table 'test629.ekichabidemo_business' doesn't exist")
I have tried the following to fix it to no avail:
- deleting all of the migration files other than the init file
- Fake migration
- Still fails with the same error, because I have to run manage.py to do that
- Not even loading the sqlite db and just changing the settings file to refer to the mysql and trying to get it to just load directly from there
- Made it a bit farther here, it creates some tables in mysql but doesn't populate any of the data, have had a bunch of errors down this path like
ValueError: The database backend does not accept 0 as a value for AutoField.but when I look these errors up the relevant posts don't seem applicable or fix the issue.
- Made it a bit farther here, it creates some tables in mysql but doesn't populate any of the data, have had a bunch of errors down this path like
- Removing all the .pyc files in the directory and retrying the commands
I can see that the db exists, but Django isn’t creating or populating the tables. Dump.json has the correct data in it. It used to work for sqlite.
My models.py file looks like
from django.db import models
from django.utils.translation import ugettext as _
class Category(models.Model):
name = models.CharField(max_length=100)
translations = {
'Hiring and Labor' : 'Kukodi au Kibarua',
'Financial Services' : 'Sekta ya Kifedha',
'Non-Agri Services' : 'Sekta Isiyo ya Kilimo',
'Transport' : 'Usafirishaji',
'Agri Processing' : 'Kuongezea Thamani Bidhaa za Kilimo',
'Merchant/Retail' : 'Wafanyabiashara wa Rejareja',
'Trading and Wholesale' : 'Wafanyabiashara wa Jumla',
'Repairs' : 'Fundi',
}
def __str__(self):
return self.translations.get(self.name,self.name)
@staticmethod
def translated_name():
return _('Category')
class District(models.Model):
name = models.CharField(max_length=100)
def __str__(self):
return self.name
@staticmethod
def translated_name():
return _('District')
class Village(models.Model):
name = models.CharField(max_length=100)
district = models.ForeignKey(District, on_delete=models.CASCADE)
def __str__(self):
return self.name
@staticmethod
def translated_name():
return _('Village')
class Subvillage(models.Model):
name = models.CharField(max_length=100)
village = models.ForeignKey(Village, on_delete=models.CASCADE)
class Meta:
ordering = ['village__name', 'name']
def __str__(self):
return "{0.village.name} - {0.name}".format(self)
@staticmethod
def translated_name():
return _('Subvillage')
class Business(models.Model):
name = models.CharField(max_length=70)
category = models.ForeignKey(Category, on_delete=models.CASCADE)
# location information
district = models.ForeignKey(District, on_delete=models.CASCADE)
village = models.ForeignKey(Village, on_delete=models.CASCADE)
subvillage = models.ForeignKey(Subvillage,
on_delete=models.CASCADE)
# phone numbers
number1 = models.CharField(max_length=15)
number2 = models.CharField(max_length=15)
number3 = models.CharField(max_length=15)
# business info
subsector1 = models.CharField(max_length=50)
subsector2 = models.CharField(max_length=50)
crop1 = models.CharField(max_length=50)
crop2 = models.CharField(max_length=50)
crop3 = models.CharField(max_length=50)
livestock1 = models.CharField(max_length=50)
livestock2 = models.CharField(max_length=50)
livestock3 = models.CharField(max_length=50)
def __str__(self):
return self.name
def description(self):
return " ".join((self.subsector1, self.subsector2))
@staticmethod
def translated_name():
return _('Business')
My reset_db.py file looks like
#python imports
import csv, sys, os, datetime
from collections import defaultdict
#
import unicodedata
# local imports
from eKichabiDemo.models import Category, District, Village, Subvillage, Business
# django imports
from django.contrib.auth.models import User
from django.core.management.base import BaseCommand, CommandError
from django.core.management import ManagementUtility
from django.conf import settings
from django.db import transaction
#from constance import config
class Command(BaseCommand):
help = 'Delete database, migrate models, and load sample data from a provided csv'
def add_arguments(self, parser):
parser.add_argument('csvpath', nargs='+')
def handle(self, *args, **options):
# delete old database
self.stdout.write('Deleting old sqlite file')
db_name = settings.DATABASES['default']['NAME']
try:
os.remove(db_name)
except OSError:
self.stdout.write("Couldn't find {} to remove".format(db_name))
# Need this to recreate the db file
utility = ManagementUtility(['reset_db.py','check'])
utility.execute()
# migrate new models
self.stdout.write("Migrating new models")
utility = ManagementUtility(['reset_db.py','migrate'])
utility.execute()
# import new data and set up admin user
self.stdout.write(self.style.MIGRATE_HEADING("Importing data"))
with transaction.atomic():
import_data(options['csvpath'][0])
set_up_admin()
self.stdout.write(self.style.SUCCESS(
'All finished; successfully initialized new database with entries'))
class importedBusiness(object):
"""super simple container for an imported business from csv"""
def __init__(self, row):
row = map(clean_ascii, row)
self.name = row[0]
self.category = row[1]
self.district = row[2]
self.village = row[3]
self.subvillage = row[4]
# currently these get the swahili names
self.subsector1 = row[12]
self.subsector2 = row[14]
self.crop1 = row[15]
self.crop2 = row[16]
self.crop3 = row[17]
self.livestock1 = row[18]
self.livestock2 = row[19]
self.livestock3 = row[20]
self.carrier1 = row[5]
self.number1 = row[6]
self.carrier2 = row[7]
self.number2 = row[8]
self.carrier3 = row[9]
self.number3 = row[10]
def __str__(self):
return "{} in {} - {}".format(self.name, self.village, self.subvillage)
class importedLocations(object):
"""super simple class to manage the hierarchy of district > village > subvillage.
data is stored in a dict:
keys are the district names, and their values are additional dicts,
whose keys are village names and values are sets of subvillage names.
"""
def __init__(self):
self.districts = defaultdict(lambda:defaultdict(set))
def add(self, district, village, subvillage):
self.districts[district][village].add(subvillage)
def __contains__(self, item):
return item in self.districts
def __iter__(self):
""" iterate over (village, dict(subvillages)) tuples """
return self.districts.iteritems()
def import_data(csvpath, ignore_header=True):
# can have handle() change ignore header if needed later
print "Importing data into database from {}".format(csvpath)
businesses = set()
locations = importedLocations()
# for convenient printing
created_counts = {'businesses':0, 'districts':0, 'villages':0, 'subvillages':0}
with open(csvpath, 'r') as csvfile:
businessreader = csv.reader(csvfile)
# import businesses into class defined above
for row in businessreader:
if ignore_header and row[0].upper() == 'FIRM_NAME':
continue # ignore headers - this is pretty sloppy
businesses.add(importedBusiness(row))
# now we're done with the file
for b in businesses:
# get villages and subvillages
locations.add(b.district, b.village, b.subvillage)
# we've got our data, now we can finally add django models
# add districts, villages and subvillages
print "\tCreating districts, villages and subvillages"
for district_name, village_dict in locations:
district = District(name=district_name)
district.save()
created_counts['districts'] += 1
for village_name, subvillages in village_dict.iteritems():
village = Village(name=village_name, district=district)
village.save()
created_counts['villages'] += 1
for subvillage_name in subvillages:
subvillage = Subvillage(name=subvillage_name, village=village)
subvillage.save()
created_counts['subvillages'] += 1
# finally, add businesses
print "\tCreating businesses"
for b in businesses:
category, _ = Category.objects.get_or_create(name=b.category)
district = District.objects.get(name=b.district)
village = Village.objects.get(name=b.village, district=district)
subvillage = Subvillage.objects.get(name=b.subvillage, village=village)
business = Business(name = b.name,
category = category,
district = district,
village = village,
subvillage = subvillage,
number1 = b.carrier1[0] + b.number1 if len(b.carrier1) > 0 else '',
number2 = b.carrier2[0] + b.number2 if len(b.carrier2) > 0 else '',
number3 = b.carrier3[0] + b.number3 if len(b.carrier3) > 0 else '',
subsector1 = b.subsector1,
subsector2 = b.subsector2,
crop1 = b.crop1,
crop2 = b.crop2,
crop3 = b.crop3,
livestock1 = b.livestock1,
livestock2 = b.livestock2,
livestock3 = b.livestock3)
business.save()
created_counts['businesses'] += 1
print "Finshed importing data:"
s = "Created {} businesses, {} villages, and {} subvillages in {} districts."
print s.format(created_counts['businesses'],
created_counts['villages'],
created_counts['subvillages'],
created_counts['districts'])
def set_up_admin():
print 'Creating Admin User - username:**********, password:************'
oscard = User.objects.create_superuser('********',email='*******',password='*********')
def clean_ascii(text):
return unicodedata.normalize('NFKD', text.decode('utf-8')).encode('ascii', 'ignore')
if __name__ == '__main__':
import_data(sys.argv[1])
Please let me know anything else I can try, happy to provide any other of my files if needed. Thanks!