mt5mysql数据库_Django使用MySql数据库

Django默认使用的sqlite3&＃xff0c;这在实际的生产环境中是不推荐的&＃xff1b;

1. 创建数据库

Linux VM_0_15_centos 3.10.0-693.el7.x86_64 #1 SMP Tue Aug 22 21:09:27 UTC 2017 x86_64 x86_64 x86_64 GNU/Linux

1.1. 使用utf8mb4编码

mysql的utf-8编码最多只支持3个字节&＃xff0c;而移动端的一些表情都是以4个字节存储的&＃xff1b;utf8mb4是一个替代的方案&＃xff0c;建议创建数据库和表都以utf8mb4替代utf-8

1.1.1. 确定mysql的配置文件

# 系统中my.cnf文件的位置

[luizyao&＃64;VM_0_15_centos ~]$ locate my.cnf

/etc/my.cnf

/etc/my.cnf.d

/etc/my.cnf.d/client.cnf

/etc/my.cnf.d/mysql-clients.cnf

/etc/my.cnf.d/server.cnf

# mysql启动时&＃xff0c;读取配置文件的目录顺序

[luizyao&＃64;VM_0_15_centos ~]$ mysql --help | head -n 7

mysql Ver 15.1 Distrib 5.5.56-MariaDB, for Linux (x86_64) using readline 5.1

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Usage: mysql [OPTIONS] [database]

Default options are read from the following files in the given order:

/etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf

1.1.2. 修改配置文件

/etc/my.cnf

[client]

default-character-set &＃61; utf8mb4

[mysql]

default-character-set &＃61; utf8mb4

[mysqld]

# Settings user and group are ignored when systemd is used.

# If you need to run mysqld under a different user or group,

# customize your systemd unit file for mariadb according to the

# instructions in http://fedoraproject.org/wiki/Systemd

character-set-client-handshake &＃61; FALSE

character-set-server &＃61; utf8mb4

collation-server &＃61; utf8mb4_unicode_ci

init_connect&＃61;&＃39;SET NAMES utf8mb4&＃39;

1.1.3. 重启数据库服务&＃xff0c;检查相关字段

# 保证character_set_client、character_set_connection、character_set_database、character_set_results和character_set_server的值一定是utf8mb4

MariaDB [(none)]> SHOW VARIABLES WHERE Variable_name LIKE &＃39;character_set_%&＃39; OR Variable_name LIKE &＃39;collation%&＃39;;

&＃43;--------------------------&＃43;----------------------------&＃43;

| Variable_name | Value |

&＃43;--------------------------&＃43;----------------------------&＃43;

| character_set_client | utf8mb4 |

| character_set_connection | utf8mb4 |

| character_set_database | utf8mb4 |

| character_set_filesystem | binary |

| character_set_results | utf8mb4 |

| character_set_server | utf8mb4 |

| character_set_system | utf8 |

| character_sets_dir | /usr/share/mysql/charsets/ |

| collation_connection | utf8mb4_unicode_ci |

| collation_database | utf8mb4_unicode_ci |

| collation_server | utf8mb4_unicode_ci |

&＃43;--------------------------&＃43;----------------------------&＃43;

11 rows in set (0.02 sec)

1.1.4. 新建数据库

MariaDB [(none)]> create database blogproject;

Query OK, 1 row affected (0.01 sec)

--查看blogproject创建时候使用的编码&＃xff0c;回显中注释的部分可以看出&＃xff0c;使用的是utf8mb4编码

MariaDB [mysql]> show create database blogproject;

&＃43;-------------&＃43;----------------------------------------------------------------------------------------------------&＃43;

| Database | Create Database |

&＃43;-------------&＃43;----------------------------------------------------------------------------------------------------&＃43;

| blogproject | CREATE DATABASE &＃96;blogproject&＃96; /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */ |

&＃43;-------------&＃43;----------------------------------------------------------------------------------------------------&＃43;

1 row in set (0.00 sec)

1.2. 使用已经存在的数据库

1.2.1. 修改已有数据库的编码

MariaDB [(none)]> alter database blogproject character set utf8mb4;

1.3. 为Django项目新建一个数据库用户

-- 赋予这个新用户增删改查等权限&＃xff0c;不授予drop的权限&＃xff1b;并且&＃xff0c;只允许本地客户端登陆&＃xff1b;

MariaDB [mysql]> grant alter,create,delete,index,insert,select,update,trigger on blogproject.* to &＃64;localhost identified by &＃39;&＃39;;

Query OK, 0 rows affected (0.04 sec)

MariaDB [mysql]> flush privileges;

Query OK, 0 rows affected (0.03 sec)

-- 检查权限&＃xff0c;秘密默认是加密存储

MariaDB [blogproject]> show grants for &＃64;localhost;

&＃43;----------------------------------------------------------------------------------------------------------------&＃43;

| Grants for &＃64;localhost |

&＃43;----------------------------------------------------------------------------------------------------------------&＃43;

| GRANT USAGE ON *.* TO &＃39;&＃39;&＃64;&＃39;localhost&＃39; IDENTIFIED BY PASSWORD &＃39;*5102144CA406FC026831D796EA07645447677551&＃39; |

| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, INDEX, ALTER, TRIGGER ON &＃96;blogproject&＃96;.* TO &＃39;&＃39;&＃64;&＃39;localhost&＃39; |

&＃43;----------------------------------------------------------------------------------------------------------------&＃43;

2 rows in set (0.00 sec)

2. 修改Django的配置

2.1. 修改settings.py中数据库相关

DATABASES &＃61; {

# &＃39;default&＃39;: {

# &＃39;ENGINE&＃39;: &＃39;django.db.backends.sqlite3&＃39;,

# &＃39;NAME&＃39;: os.path.join(BASE_DIR, &＃39;db.sqlite3&＃39;),

# }

&＃39;default&＃39;: {

&＃39;ENGINE&＃39;: &＃39;django.db.backends.mysql&＃39;,

&＃39;NAME&＃39;: &＃39;blogproject&＃39;,

&＃39;USER&＃39;: &＃39;&＃39;,

&＃39;PASSWORD&＃39;: &＃39;&＃39;,

&＃39;HOST&＃39;: &＃39;&＃39;,

&＃39;PORT&＃39;: &＃39;3306&＃39;, # 默认的服务端口号

&＃39;OPTIONS&＃39;: {

# 存储引擎启用严格模式&＃xff0c;非法数据值被拒绝

&＃39;init_command&＃39;: "SET sql_mode&＃61;&＃39;STRICT_TRANS_TABLES&＃39;",

&＃39;charset&＃39;: &＃39;utf8mb4&＃39;,

},

}

}

Darwin luizyaodeMacBook-Air.local 18.6.0 Darwin Kernel Version 18.6.0: Thu Apr 25 23:16:27 PDT 2019; root:xnu-4903.261.4~2/RELEASE_X86_64 x86_64

2.2.1. 安装mysql-connector-c

luizyaodeMacBook-Air:~ luizyao$ brew install mysql-connector-c

&＃61;&＃61;> Downloading https://mirrors.ustc.edu.cn/homebrew-bottles/bottles/mysql-conne

######################################################################## 100.0%

&＃61;&＃61;> Pouring mysql-connector-c-6.1.11.mojave.bottle.tar.gz

&＃x1f37a; /usr/local/Cellar/mysql-connector-c/6.1.11: 79 files, 15.3MB

2.2.2. 修复mysql-connector-c在mac os的python3的bug

/usr/local/Cellar/mysql-connector-c/6.1.11/bin/mysql_config

# Create options

libs&＃61;"-L$pkglibdir"

libs&＃61;"$libs -l "

修改为

# Create options

libs&＃61;"-L$pkglibdir"

libs&＃61;"$libs -lmysqlclient -lssl -lcrypto"

2.2.3. 使用pip安装mysqlclient

[luizyaodeMacBook-Air:django-blog luizyao$ pip3 install mysqlclient

2.2.4. 使用pipenv安装mysqlclient

这个时候会报错&＃xff0c;因为&＃xff1a;because Apple has deprecated use of OpenSSL in favor of its own TLS and crypto libraries.

luizyaodeMacBook-Air:django-blog luizyao$ brew info openssl

openssl: stable 1.0.2s (bottled) [keg-only]

SSL/TLS cryptography library

https://openssl.org/

/usr/local/Cellar/openssl/1.0.2s (1,795 files, 12.0MB)

Poured from bottle on 2019-06-22 at 13:16:17

From: https://mirrors.ustc.edu.cn/homebrew-core.git/Formula/openssl.rb

&＃61;&＃61;> Caveats

A CA file has been bootstrapped using certificates from the SystemRoots

keychain. To add additional certificates (e.g. the certificates added in

the System keychain), place .pem files in

/usr/local/etc/openssl/certs

and run

/usr/local/opt/openssl/bin/c_rehash

openssl is keg-only, which means it was not symlinked into /usr/local,

because Apple has deprecated use of OpenSSL in favor of its own TLS and crypto libraries.

If you need to have openssl first in your PATH run:

echo &＃39;export PATH&＃61;"/usr/local/opt/openssl/bin:$PATH"&＃39; >> ~/.bash_profile

For compilers to find openssl you may need to set:

export LDFLAGS&＃61;"-L/usr/local/opt/openssl/lib"

export CPPFLAGS&＃61;"-I/usr/local/opt/openssl/include"

&＃61;&＃61;> Analytics

install: 490,905 (30 days), 1,748,362 (90 days), 6,591,368 (365 days)

install_on_request: 59,162 (30 days), 234,123 (90 days), 884,807 (365 days)

build_error: 0 (30 days)

根据提示做如下操作

luizyaodeMacBook-Air:django-blog luizyao$ echo &＃39;export PATH&＃61;"/usr/local/opt/openssl/bin:$PATH"&＃39; >> ~/.bash_profile

luizyaodeMacBook-Air:django-blog luizyao$ source ~/.bash_profile

luizyaodeMacBook-Air:django-blog luizyao$ export LDFLAGS&＃61;"-L/usr/local/opt/openssl/lib"

luizyaodeMacBook-Air:django-blog luizyao$ export CPPFLAGS&＃61;"-I/usr/local/opt/openssl/include"

再安装mysqlclient&＃xff0c;就能成功了

luizyaodeMacBook-Air:django-blog luizyao$ pipenv install mysqlclient

Installing mysqlclient…

Adding mysqlclient to Pipfile&＃39;s [packages]…

✔ Installation Succeeded

Pipfile.lock (cee3a5) out of date, updating to (79d06d)…

Locking [dev-packages] dependencies…

✔ Success!

Locking [packages] dependencies…

✔ Success!

Updated Pipfile.lock (cee3a5)!

Installing dependencies from Pipfile.lock (cee3a5)…

&＃x1f40d; ▉▉▉▉▉▉▉▉▉▉▉▉▉▉▉▉▉▉▉▉▉▉▉▉▉▉▉▉▉▉▉▉ 4/4 — 00:00:01

To activate this project&＃39;s virtualenv, run pipenv shell.

Alternatively, run a command inside the virtualenv with pipenv run.

2.3. 执行migrate操作

[luizyaodeMacBook-Air:django-blog luizyao$ pipenv run python manage.py migrate

Operations to perform:

Apply all migrations: admin, auth, blog, contenttypes, sessions

Running migrations:

Applying contenttypes.0001_initial... OK

Applying auth.0001_initial... OK

Applying admin.0001_initial... OK

Applying admin.0002_logentry_remove_auto_add... OK

Applying admin.0003_logentry_add_action_flag_choices... OK

Applying contenttypes.0002_remove_content_type_name... OK

Applying auth.0002_alter_permission_name_max_length... OK

Applying auth.0003_alter_user_email_max_length... OK

Applying auth.0004_alter_user_username_opts... OK

Applying auth.0005_alter_user_last_login_null... OK

Applying auth.0006_require_contenttypes_0002... OK

Applying auth.0007_alter_validators_add_error_messages... OK

Applying auth.0008_alter_user_username_max_length... OK

Applying auth.0009_alter_user_last_name_max_length... OK

Applying auth.0010_alter_group_name_max_length... OK

Applying auth.0011_update_proxy_permissions... OK

Applying blog.0001_initial... OK

Applying sessions.0001_initial... OK

只有Applying blog.0001_initial... OK是和我们自己模型相关的&＃xff0c;其他的是Django系统自带的一些模型&＃xff0c; 我们可以进一步的查看数据库到底做了什么操作&＃xff1b;

luizyaodeMacBook-Air:django-blog luizyao$ pipenv run python manage.py sqlmigrate blog 0001

BEGIN;

--

-- Create model Category

--

CREATE TABLE &＃96;blog_category&＃96; (&＃96;id&＃96; integer AUTO_INCREMENT NOT NULL PRIMARY KEY, &＃96;name&＃96; varchar(100) NOT NULL);

--

-- Create model Tag

--

CREATE TABLE &＃96;blog_tag&＃96; (&＃96;id&＃96; integer AUTO_INCREMENT NOT NULL PRIMARY KEY, &＃96;name&＃96; varchar(100) NOT NULL);

--

-- Create model Post

--

CREATE TABLE &＃96;blog_post&＃96; (&＃96;id&＃96; integer AUTO_INCREMENT NOT NULL PRIMARY KEY, &＃96;title&＃96; varchar(70) NOT NULL, &＃96;excerpt&＃96; varchar(200) NOT NULL, &＃96;body&＃96; longtext NOT NULL, &＃96;created_at&＃96; datetime(6) NOT NULL, &＃96;modified_at&＃96; datetime(6) NOT NULL, &＃96;author_id&＃96; integer NOT NULL, &＃96;category_id&＃96; integer NOT NULL);

CREATE TABLE &＃96;blog_post_tag&＃96; (&＃96;id&＃96; integer AUTO_INCREMENT NOT NULL PRIMARY KEY, &＃96;post_id&＃96; integer NOT NULL, &＃96;tag_id&＃96; integer NOT NULL);

ALTER TABLE &＃96;blog_post&＃96; ADD CONSTRAINT &＃96;blog_post_author_id_dd7a8485_fk_auth_user_id&＃96; FOREIGN KEY (&＃96;author_id&＃96;) REFERENCES &＃96;auth_user&＃96; (&＃96;id&＃96;);

ALTER TABLE &＃96;blog_post&＃96; ADD CONSTRAINT &＃96;blog_post_category_id_c326dbf8_fk_blog_category_id&＃96; FOREIGN KEY (&＃96;category_id&＃96;) REFERENCES &＃96;blog_category&＃96; (&＃96;id&＃96;);

ALTER TABLE &＃96;blog_post_tag&＃96; ADD CONSTRAINT &＃96;blog_post_tag_post_id_a5c00319_fk_blog_post_id&＃96; FOREIGN KEY (&＃96;post_id&＃96;) REFERENCES &＃96;blog_post&＃96; (&＃96;id&＃96;);

ALTER TABLE &＃96;blog_post_tag&＃96; ADD CONSTRAINT &＃96;blog_post_tag_tag_id_2bbd31e4_fk_blog_tag_id&＃96; FOREIGN KEY (&＃96;tag_id&＃96;) REFERENCES &＃96;blog_tag&＃96; (&＃96;id&＃96;);

ALTER TABLE &＃96;blog_post_tag&＃96; ADD CONSTRAINT &＃96;blog_post_tag_post_id_tag_id_ba2a5f83_uniq&＃96; UNIQUE (&＃96;post_id&＃96;, &＃96;tag_id&＃96;);

COMMIT;

在数据库中可以看到Django创建的具体表&＃xff1b;

MariaDB [blogproject]> show tables;

&＃43;----------------------------&＃43;

| Tables_in_blogproject |

&＃43;----------------------------&＃43;

| auth_group |

| auth_group_permissions |

| auth_permission |

| auth_user |

| auth_user_groups |

| auth_user_user_permissions |

| blog_category |

| blog_post |

| blog_post_tag |

| blog_tag |

| django_admin_log |

| django_content_type |

| django_migrations |

| django_session |

&＃43;----------------------------&＃43;

14 rows in set (0.00 sec)

2.4. 创建一个管理员用户

luizyaodeMacBook-Air:django-blog luizyao$ pipenv run python manage.py createsuperuser

用户名 (leave blank to use &＃39;luizyao&＃39;): luizyao

电子邮件地址: luizyao&＃64;163.com

Password:

Password (again):

Superuser created successfully.

在数据库中&＃xff0c;我们就可以看到这个管理员用户了

MariaDB [blogproject]> select * from auth_user;

&＃43;----&＃43;--------------------------------------------------------------------------------&＃43;------------&＃43;--------------&＃43;----------&＃43;------------&＃43;-----------&＃43;-----------------&＃43;----------&＃43;-----------&＃43;----------------------------&＃43;

| id | password | last_login | is_superuser | username | first_name | last_name | email | is_staff | is_active | date_joined |

&＃43;----&＃43;--------------------------------------------------------------------------------&＃43;------------&＃43;--------------&＃43;----------&＃43;------------&＃43;-----------&＃43;-----------------&＃43;----------&＃43;-----------&＃43;----------------------------&＃43;

| 1 | pbkdf2_sha256$150000$ViP2waofsEQU$3oNPdGxlGPmt5Nbl/lcHJli8V9j7425ZxRfqKF18E0Q&＃61; | NULL | 1 | luizyao | | | luizyao&＃64;163.com | 1 | 1 | 2019-08-25 03:49:19.667011 |

&＃43;----&＃43;--------------------------------------------------------------------------------&＃43;------------&＃43;--------------&＃43;----------&＃43;------------&＃43;-----------&＃43;-----------------&＃43;----------&＃43;-----------&＃43;----------------------------&＃43;

1 row in set (0.00 sec)