Python3项目初始化3-->引入MySQL和用户显示

10、MySQL数据库安装
参考链接：https://blog.csdn.net/weixin_43914798/article/details/124118864
Linux安装MySQL5.7
[root@txy tmp]# wget -i -c http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm
[root@txy tmp]# yum install mysql57-community-release-el7-10.noarch.rpm -y
[root@txy tmp]# yum remove -y mariadb*
[root@txy tmp]# yum -y install mysql-community-server 如报错，GPG Keys are configured as: file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
[root@txy tmp]# rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2022 执行解决以上报错（秘钥过期）
启动mysql服务：
systemctl start mysqld /systemctl status mysqld /systemctl enable mysqld
查看临时密码：grep "password" /var/log/mysqld.log
登录修改mysql密码和创建远程用户
mysql -uroot -p
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'Test@123';
systemctl restart mysqld
mysql> GRANT ALL PRIVILEGES ON *.* TO 'wang'@'%' IDENTIFIED BY 'Test@123' WITH GRANT OPTION;
mysql> GRANT select,Insert,UPDATE,delete ON *.* TO 'test1'@'%' IDENTIFIED BY 'Test@123' WITH GRANT OPTION;
mysql> GRANT select,Insert,UPDATE,delete ON *.* TO 'test2'@'%' IDENTIFIED BY 'Test@123' WITH GRANT OPTION;
注意：如果部署在云上，需要打开对应安全组访问策略。
mysql安装后的相关文件在如下路径
配置文件：/etc/my.cnf
日志文件： /var/log/mysqld.log
服务启动脚本： /usr/lib/systemd/system/mysqld.service
socket文件： /var/run/mysqld/mysqld.pid

mysql操作：
库，表，行，列 --> SQL结构化查询语句
SHOW DATABASES;
CREATE DATABASE db_name DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SHOW CREATE DATABASE db_name;
use db_name;
CREATE TABLE user(
id int,
name VARCHAR(10),
age int,
sex bool,
tel VARCHAR(26)
) DEFAULT CHARSET utf8mb4;
show create table user;
DESC user;
insert user(id,name,age,sex,tel) VALUES(1,'ww', 30, 1, '13287667212');
SELECT * FROM user;
SELECT COUNT(1) FROM user;
SELECT name,age FROM user;

11、mysql查询和删除
因mysql字段类型text默认不支持有值，所有创建表值需要设置，
set global sql_mode="NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
create table user(
id int PRIMARY key auto_increment,
name varchar(32) unique not null default '',
password varchar(512) not null default '',
birthday date not null,
height float not null default 0,
weight float not null default 0,
tel varchar(32) unique not null default '',
sex bool not null default 1,
remark text not null default '',
addr varchar(512) not null default ''
)default charset utf8mb4;
insert into user(name,password,birthday,height,weight,tel,sex,remark,addr)
values
('sun', '123456', '1992-10-12', 168, 68, '13672382627', 1, '', '浙江'),
('sun1', '123456', '1989-10-15', 168, 68, '13622384627', 1, '', '广州'),
('sun2', '123456', '1944-11-12', 168, 68, '13672334227', 1, '', '浙江'),
('sun3', '123456', '1965-08-02', 168, 68, '13672384627', 2, '', '深圳'),
('wang', '123456', '1982-11-12', 168, 68, '13674484627', 1, '', '荆门'),
('ada', '123456', '1990-12-12', 168, 68, '13672432627', 0, '', '浙江');

需求：
1、查询性别为男的
2、出生日期为90后的
3、出生日期是9X的
4、出生日期为90后的男性
5、出生日期为00后的女性并且名称包含ada
SELECT * FROM user WHERE sex=1; 或SELECT * FROM user WHERE sex not in (0, 2);
SELECT * FROM user WHERE birthday >= "1990-01-01";
SELECT * FROM user WHERE birthday LIKE '199%';或SELECT * FROM user WHERE birthday BETWEEN '1990-01-01' AND '1999-12-31';
SELECT * FROM user WHERE birthday >= "1990-01-01" AND sex=1;
SELECT * FROM user WHERE name LIKE '%ada%' AND sex='0' AND birthday >= "2000-01-01";

查询：
SELECT * FROM user LIMIT 2;
SELECT * FROM user LIMIT 2 OFFSET 1;
SELECT birthday, count(*) FROM user GROUP BY birthday;
SELECT birthday, avg(weight) FROM user GROUP BY birthday;

12、Python操作数据库
(py36env) [root@txy ~]# yum install gcc gcc-c++ python3-devel mysql-devel python-devel (因是venv环境，这个python3-devel很重要)
(py36env) [root@txy ~]# pip install mysqlclient
(py36env) [root@txy ~]# python
>>> import MySQLdb
>>> conn = MySQLdb.connect(host='127.0.0.1', port=3306, user='root', passwd='Test@123', db='db_name', charset='utf8mb4')
>>> cur = conn.cursor()
>>> dir(conn)
>>> cur.execute('select * from user')
6
>>> cur.fetchone() # 每次读一行数据
>>> cur.fetchcall() # 读取全部数据

>>> cur.execute('insert into user(name) values("silence");') 插入数据
1
>>> conn.commit() 提交
>>> cur.execute('delete from user where id=13;') 删除一条数据
>>> cur.execute('update user set sex=0 where id=7;') 修改数据
>>> conn.commit()

13、用户登录使用数据库
将数据存在文件里，修改为存储在数据库里面。
创建数据库，cmdb_xxx或user2等,并插入数据。
CREATE TABLE `user2` (
`id` int primary key AUTO_INCREMENT,
`name` varchar(32) NOT NULL DEFAULT '',
`password` varchar(512) NOT NULL DEFAULT '',
`tel` varchar(32) NOT NULL DEFAULT '',
`age` int(11) NOT NULL DEFAULT '0',
`sex` bool NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO user2(name, password, tel, age, sex) VALUES
('liu', '123456', '123456', 30, 1),
('liu1', '123456', '123456', 32, 1),
('liu2', '123456', '123456', 18, 0);

python验证mysql操作语句
>>> import MySQLdb
>>> conn = MySQLdb.connect(host='127.0.0.1', port=3306, user='root', passwd='Test@123', db='db_name', charset='utf8mb4')
>>> cur = conn.cursor()
cur.execute('SELECT id,name,password,age,tel,sex FROM user2 where name="{0}" and password="{1}" LIMIT 1'.format('liu','123456'))
cur.execute('SELECT id,name,password,age,tel,sex FROM user2 where name=%s and password=%s LIMIT 1',('liu','123456'))
如上，第二种方法更安全，比较常用；第一种拼字符串，不安全。

开始代理执行，配置model，只做登录这块。
(py36env) [root@txy cmdb]# cat user/models.py # 设置变量。修改valid_login登录校验模块。
import json
import MySQLdb

DATA_FILE = 'user.data.json'
MYSQL_HOST = '101.34.54.32'
MYSQL_PORT = 3306
MYSQL_USER = 'wang'
MYSQL_PASSWORD = 'Test@123'
MYSQL_DB = 'db_name'
MYSQL_CHARSET = 'utf8'
SQL_LOGIN = 'SELECT id,name,password,age,tel,sex FROM user2 where name=%s and password=%s LIMIT 1'

def valid_login(name, password):
    conn = MySQLdb.connect(host=MYSQL_HOST, port=MYSQL_PORT,
    user=MYSQL_USER, passwd=MYSQL_PASSWORD,
    db=MYSQL_DB, charset=MYSQL_CHARSET)
    cur = conn.cursor()
    cur.execute(SQL_LOGIN, (name, password))
    result = cur.fetchone()
    print(result)
    cur.close()
    conn.close()
    return {'id': result[0], 'name': result[1]} if result else None
此时浏览器访问，通过mysql里面的用户登录是可以看到文件里面的所有用户信息的。

14、用户列表显示
(py36env) [root@txy cmdb]# cat user/models.py # 设置变量。修改get_users获取用户列表。
SQL_LIST = 'SELECT id,name,age,tel,sex FROM user2'
SQL_LIST_COLUMN = ['id', 'name', 'age', 'tel', 'sex']
def get_users():
    conn = MySQLdb.connect(host=MYSQL_HOST, port=MYSQL_PORT,
    user=MYSQL_USER, passwd=MYSQL_PASSWORD,
    db=MYSQL_DB, charset=MYSQL_CHARSET)
    cur = conn.cursor()
    cur.execute(SQL_LIST)
    result = cur.fetchall() #返回的是元组， 元组需要转dict， 不然需要修改前端
    print(result)
    cur.close()
    conn.close()
    # return result
    return [
        {'id': line[0], 'name': line[1], 'age': line[2], 'tel': line[3], 'sex': line[4]} # dict(zip(SQL_LIST_COLUMN, line))
        for line in result
    ]
前端index.html页面修改：
{% for user in users.items %} 修改为 {% for user in users %}

<a href="{% url 'user:view' %}?uid={{ user.id }}">编辑</a>
{% if request.session.user.id != user.id %}
<a href="{% url 'user:delete' %}?uid={{ user.id }}">删除</a>

浏览器可以正常看到数据库所有用户信息。

这里做个扩展：zip(元组转字典)、三木运算、MySQLdb操作
(py36env) [root@txy ~]# python
Python 3.6.8 (default, Nov 16 2020, 16:55:22)
>>> zip
<class 'zip'>
>>> zip([1,2,3,4,5],['a','b','c','d','e'])
<zip object at 0x7f529e20c3c8>
>>> list(zip([1,2,3,4,5],['a','b','c','d','e']))
[(1, 'a'), (2, 'b'), (3, 'c'), (4, 'd'), (5, 'e')]
>>> dict(_)
{1: 'a', 2: 'b', 3: 'c', 4: 'd', 5: 'e'}

>>> tuple_params = ((1, 'liu', '123456', 30, '123456', 1), (2, 'liu1', '123456', 32, '123456', 1))
>>> list_new = []
>>> for line in tuple_params:
... list_new.append({'id': line[0], 'name': line[1]})
...
>>> print(list_new)
[{'id': 1, 'name': 'liu'}, {'id': 2, 'name': 'liu1'}]

>>> import MySQLdb
>>> from MySQLdb import cursors
>>> dir(cursors)
['BaseCursor', 'Cursor', 'CursorDictRowsMixIn', 'CursorStoreResultMixIn', 'CursorTupleRowsMixIn', 'CursorUseResultMixIn', 'DictCursor', 'ProgrammingError', 'RE_INSERT_VALUES', 'SSCursor', 'SSDictCursor', '__builtins__', '__cached__', '__doc__', '__file__', '__loader__', '__name__', '__package__', '__spec__', 're']
>>> conn = MySQLdb.connect(host='127.0.0.1', port=3306, user='root', passwd='Test@123', db='db_name', charset='utf8mb4')
>>> cur = conn.cursor(cursors.DictCursor)
>>> cur.execute('select * from user2')
3
>>> cur.fetchall()
({'id': 1, 'name': 'liu', 'password': '123456', 'tel': '123456', 'age': 30, 'sex': 1}, {'id': 2, 'name': 'liu1', 'password': '123456', 'tel': '123456', 'age': 32, 'sex': 1}, {'id': 3, 'name': 'liu2', 'password': '123456', 'tel': '123456', 'age': 18, 'sex': 0})
>>> dir(conn)

扩展结束。