使用Django将sqlite迁移到mysql -未创建表

我是Django和web服务器的新手，已经被困了几天了。我继承了Django应用程序，python版本2.7和Django版本1.9。该应用程序之前使用默认的SQLite数据库。我想迁移到Mysql的性能原因。

我使用reset_db
我为一个用户创建了一个用户名和密码，并授予了所有权限

python manage.py dumpdata > dump.json

我将设置文件更改为使用django.db.backends.mysql
我试图加载数据使用python manage.py loaddatamakemigrations,migrate,不能用以下错误(与任何jobs命令):

(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")

我尝试了以下方法来修复它，但无济于事:

删除除init文件以外的所有迁移文件
假迁移
- 仍未用同样的错误,因为我必须跑jobs
甚至没有加载sqlite数据库，只是改变设置文件，以参考mysql，并试图让它直接从那里加载
- 使它在这里更远一点，它在mysql中创建了一些表，但不填充任何数据，有一堆错误沿着这条路径像ValueError: The database backend does not accept 0 as a value for AutoField.，但当我查看这些错误相关的帖子似乎不适用或解决问题。
删除目录中所有的.pyc文件并重试命令

我可以看到db存在，但是Django没有创建或填充表。转储。Json中有正确的数据。它用于sqlite。

我的models.py文件看起来像

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')

我的reset_db.py文件看起来像

#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])

请让我知道我可以尝试的任何其他东西，如果需要，我很乐意提供我的任何其他文件。谢谢!

最好的方法是使用fixture:

python manage.py dumpdata > application.json

然后，在更改数据库后:

python manage.py loaddata application.json

它应该工作完美，但如果你有任何麻烦尝试做dumpdata逐一为每个应用程序，如:

python manage.py dumpdata my_app > my_app.json

在某些情况下，可能会发生一些细节你不能JSONify一些表然后你需要--exclude然后到dumpdata命令，如:

python manage.py dumpdata my_app --exclude my_app.mymodel > application.json

相关内容

最新更新

热门标签：