django如何连接mysql数据库配置_Django连接mysql数据库配置

mysql5.0以上版本支持三种sql_mode：ANSI、TRADITIONAL和STRICT_TRANS_TABLES。

ANSI模式：宽松模式，对插入数据进行校验，如果不符合定义类型或长度，对数据类型调整或截断保存，报warning警告。

TRADITIONAL模式：严格模式，当向mysql数据库插入数据时，进行数据的严格校验，保证错误数据不能插入，报error错误。用于事物时，会进行事物的回滚。

STRICT_TRANS_TABLES模式：严格模式，进行数据的严格校验，错误数据不能插入，报error错误。

1、连接操作单数据库

django默认使用的是sqlite3数据库,settings.py

# 修改前

DATABASES = {

'default': {

'ENGINE': 'django.db.backends.sqlite3',

'NAME': os.path.join(BASE_DIR, 'db.sqlite3'),

}

}

修改后

DATABASES = {

'books': {

'ENGINE': 'django.db.backends.mysql',

'HOST':'172.20.25.1',

'PORT':'3306',

'USER':'bmanager',

'PASSWORD':'MyNewPass0!',

'NAME': 'books', # 库名

'OPTIONS': {

'init_command': "SET sql_mode='STRICT_TRANS_TABLES'",

},

}

}

设置表结构，models.py

class Books_info(models.Model):

id = models.AutoField(primary_key=True)

createtime = models.DateTimeField('创建时间', auto_now = True)

name = models.CharField(max_length=32)

price = models.IntegerField()

author = models.CharField(max_length=32)

publishing_house = models.CharField(max_length=32)

publication_time = models.IntegerField()

num = models.IntegerField()

python3 manage.py makemigrations TestModel

python3 manage.py migrate TestModel

2、连接操作多数据库

添加新库，修改settings.py

DATABASES = {

'books': {

'ENGINE': 'django.db.backends.mysql',

'HOST':'172.20.25.1',

'PORT':'3306',

'USER':'bmanager',

'PASSWORD':'MyNewPass0!',

'NAME': 'books', # 库名

'OPTIONS': {

'init_command': "SET sql_mode='STRICT_TRANS_TABLES'",

},

'users': {

'ENGINE': 'django.db.backends.mysql',

'HOST':'172.20.25.2',

'PORT':'3306',

'USER':'umanager',

'PASSWORD':'MyNewPass1!',

'NAME': 'users', # 库名

'OPTIONS': {

'init_command': "SET sql_mode='STRICT_TRANS_TABLES'",

},

}

}

添加数据库的路由，修改settings.py

DATABASE_ROUTERS = ['mysite.database_router.DatabaseAppsRouter'] # mysite项目目录名

## app指定数据库

DATABASE_APPS_MAPPING = {

# example:

# 'app_name':'database_name',

'booksModel':'books',

'usersModel':'users',

}

在settings.py同级目录下新建路由文件，database_router.py

from django.conf import settings

DATABASE_MAPPING = settings.DATABASE_APPS_MAPPING

class DatabaseAppsRouter(object):

"""

A router to control all database operations on models for different

databases.

In case an app is not set in settings.DATABASE_APPS_MAPPING, the router

will fallback to the `default` database.

Settings example:

DATABASE_APPS_MAPPING = {'app1': 'db1', 'app2': 'db2'}

"""

def db_for_read(self, model, **hints):

""""Point all read operations to the specific database."""

if model._meta.app_label in DATABASE_MAPPING:

return DATABASE_MAPPING[model._meta.app_label] return None

def db_for_write(self, model, **hints):

"""Point all write operations to the specific database."""

if model._meta.app_label in DATABASE_MAPPING:

return DATABASE_MAPPING[model._meta.app_label] return None

def allow_relation(self, obj1, obj2, **hints):

"""Allow any relation between apps that use the same database."""

db_obj1 = DATABASE_MAPPING.get(obj1._meta.app_label)

db_obj2 = DATABASE_MAPPING.get(obj2._meta.app_label)

if db_obj1 and db_obj2:

if db_obj1 == db_obj2:

return True

else:

return False

return None

def allow_syncdb(self, db, model):

"""Make sure that apps only appear in the related database."""

if db in DATABASE_MAPPING.values():

return DATABASE_MAPPING.get(model._meta.app_label) == db

elif model._meta.app_label in DATABASE_MAPPING:

return False

return None

def allow_migrate(self, db, app_label, model=None, **hints):

"""

Make sure the auth app only appears in the 'auth_db'

database.

"""

if db in DATABASE_MAPPING.values():

return DATABASE_MAPPING.get(app_label) == db

elif app_label in DATABASE_MAPPING:

return False

return None

情况一：远程数据库中表结构及数据已存在

# 拉取表结构

python3 manage.py inspectdb --database=users >> usersModel/models.py

python3 manage.py migrate TestModel --fake

情况二：远程数据库表结构及数据不存在，则需要修改配置

修改 usersModel/models.py 文件

class Users_info(models.Model):

id = models.AutoField(primary_key=True)

createtime = models.DateTimeField('创建时间', auto_now = True)

name = models.CharField(max_length=32)

age = models.IntegerField()

sex = models.CharField(max_length=10)

identification_numbers = models.IntegerField()

identity = models.CharField(max_length=32)

phone_number = models.IntegerField()

job = models.CharField(max_length=32)

address = models.CharField(max_length=128)

删除该app名字下的migrations文件

进入数据库，找到django_migrations的表，删除该app名字的所有记录。

delete from django_migrations;

创建新表

python manage.py makemigrations

python manage.py migrate

指定数据库

class Users_info(models.Model):

id = models.AutoField(primary_key=True)

createtime = models.DateTimeField('创建时间', auto_now = True)

name = models.CharField(max_length=32)

age = models.IntegerField()

sex = models.CharField(max_length=10)

identification_numbers = models.IntegerField()

identity = models.CharField(max_length=32)

phone_number = models.IntegerField()

job = models.CharField(max_length=32)

address = models.CharField(max_length=128)

class Meta:

app_label = 'users'

作者：Sunny_Future