新闻管理系统（二）连接池封装

封装连接池模块

• mysql_db.py 文件

#!/usr/bin/python3
# -*- coding: utf-8 -*-

import mysql.connector.pooling

__config = {
    "host":"localhost",
    "port":"3307",
    "user":"root",
    "password":"jinhua911love",
    "database":"vega"
}

try:
    pool = mysql.connector.pooling.MySQLConnectionPool(
        **__config,
        pool_size=10
    )

except Exception as e:
    print(e)

封装用户表相关操作

• user_dao.py 文件

1. 验证用户登录函数

#!/user/bin/env python
# -*- coding: utf-8 -*-

from vega.db.mysql_db import pool

class UserDao(object):
    # 验证用户登录
    def login(self, username, password):
        try:
            con = pool.get_connection()
            cursor = con.cursor()
            sql = "SELECT COUNT(*) FROM t_user WHERE username=%s " \
                  "AND password=HEX(AES_ENCRYPT(%s,'HelloWorld'));"
            cursor.execute(sql, (username, password))
            count = cursor.fetchone()[0]
            return True if count == 1 else False

        except Exception as e:
            print(e)

        finally:
            if "con" in dir():
                con.close()  # 将连接放回连接池        

2. 根据用户名查询角色的方法

    # 根据用户名查询用户角色
    def search_user_role(self, username):
        try:
            con = pool.get_connection()
            cursor = con.cursor()
            sql = "SELECT r.role FROM t_user u JOIN t_role r ON u.role_id=r.id " \
                  "WHERE u.username=%s"
            cursor.execute(sql, [username])
            role = cursor.fetchone()[0]
            return role

        except Exception as e:
            print(e)

        finally:
            if "con" in dir():
                con.close()  # 将连接放回连接池