python connect mysql

1. 创建数据表

 create_db_sql.py

create_fid_info_db = """
CREATE TABLE IF NOT EXISTS `device_fingerprint_info`(
	`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键 id',
	`fid` varchar(64) DEFAULT NULL COMMENT '指纹 ID',
	`user_id` varchar(20) DEFAULT NULL COMMENT '用户id',
	`uuid` text DEFAULT NULL COMMENT '设备标志ID',
	`android_id` varchar(20) DEFAULT NULL COMMENT '首次启动系统生成的随机数',
	`brand` varchar(20) DEFAULT NULL COMMENT '设备品牌',
	`create_time`timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	`update_time`timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	 PRIMARY KEY ( `id` )
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT = '指纹信息表';
"""

2. 创建数据库操作类(使用数据库连接池)

2.1 构建数据库操作类并初始化

安装 pip3 install DBUtils

python3默认安装的DBUtils是2.0版本，
pip install DBUtils==1.2

# -*- coding:utf-8 -*-

import pymysql
from DBUtils.PooledDB import PooledDB
import pandas as pd
import traceback
import logging
logger = logging.getLogger("django")

class Database:
    conn_pool = None

    def __init__(self):
        Database.getmysqlconn()  # 以静态对象的方法实现，否则容易出错

2.2 获得一个连接

@staticmethod
def getmysqlconn():
    if Database.conn_pool is None:
        # 创建数据库连接池对象
        Database.conn_pool = PooledDB(creator=pymysql, 
                mincached=20, 
                maxcached=100,  # 最大空闲数
                user="root",
                passwd="hello",
                db="db_name",
                host="10.107.2.124",
                # host = "fp.service.alidb",
                port=3306)

def get_conn(self):
    conn = Database.conn_pool.connection()
    return conn

def commit(self, conn):
    conn.commit()

def close(self, conn, cur):
    cur.close()
    conn.close()

2.3 pandas形式读取

1. 以DF格式读取数据库信息

def read_as_dataframe(self, sql):
    conn = self.get_conn()
    logger.info('running get connection and pool size: %s' % (self.conn_pool._connections))
    try:
        df = pd.read_sql(sql, conn)
        return df
    except Exception as e:
        print(traceback.format_exc())
        print("read_as_dataframe() error: " + str(e))
    finally:
        conn.close()

2. 以records格式读取数据库信息

def read_as_records(self, sql):
    df = self.read_as_dataframe(sql)
    records = df.to_dict(orient='records')
    return records

3. 以dict格式读取数据库信息

def read_first_as_dict(self, sql):
    results, desc = self.run_sql(sql)
    if results and len(results) > 0:
        return dict(zip(desc, results[0]))
    else:
        return {}

# 查询语句，返回的是一个包含tuple的list，list的元素是记录行，tuple的元素是每行记录的字段。
def run_sql(self, sql):  # 执行Sql语句函数，返回结果
    conn = self.get_conn()
    try:
        # conn.ping(reconnect=True)
        cur = conn.cursor(cursor=pymysql.cursors.DictCursor)
        cur.execute(sql)
        self.commit(conn)

        rst = cur.fetchall()  # 获得所有的查询结果
        desc = []
        # if rst and len(rst) > 0:
        #    desc = [n[0] for n in self.cur.description]
        return rst, desc
    except Exception as e:
        print(traceback.format_exc())
        print("run_sql() error: " + str(e))
        return '', ''
    finally:
        self.close(conn, cur)

3. 数据库连接池简介

        数据库连接池，主要用于防止因多线程同时对数据库进行操作而出现混乱。DBUtils是一套Python数据库连接池包，允许对非线程安全的数据库接口进行线程安全包装。为高频度高并发的数据库访问提供更好的性能，可以自动管理连接对象的创建和释放。

DBUtils提供两种外部接口：

 PersistentDB： 提供(单个)线程专用的数据库连接池，并自动管理连接；
 PooledDB： 提供进程内可共享的数据库连接(所有线程可共享)，并自动管理连接。

        实测证明 PersistentDB 的速度是最高的，但是在某些特殊情况下，数据库的连接过程可能异常缓慢，而此时的PooledDB则可以提供相对来说平均连接时间比较短的管理方式。

3.1 创建数据库连接池对象

pool = PooledDB(creator=pymysql,  # 数据库类型(数据库驱动模块)
    mincached=20,  # 初始化连接池时创建的连接数(默认为0，即初始化时不创建连接)
    maxcached=100,  # 池中空闲连接的最大数量(默认为0，即无最大数量限制，建议默认)
    blocking=True,  # 默认False，即达到最大连接数时，再取新连接将会报错，True，达到最大连接数时，新连接阻塞，等待连接数减少再连接
    ping=4,
    host='21.19.9.186', 
    port=3306, 
    user='root',
    password='hello6688',
    db='test',
    charset='utf8'
)

参数说明：

1. maxshared：池中共享连接的最大数量。默认为0，即每个连接都是专用的，不可共享(不常用，建议默认)；
2. maxconnections：被允许的最大连接数。默认为0，无最大数量限制。(视情况而定)；

3. blocking：连接数达到最大时，新连接是否可阻塞。默认False，即达到最大连接数时，再取新连接将会报错。(建议True，达到最大连接数时，新连接阻塞，等待连接数减少再连接)；

4. maxusage：连接的最大使用次数。默认0，即无使用次数限制。(建议默认)；

5. setsession：可选的SQL命令列表，可用于准备会话。(例如设置时区)；

6. reset：当连接返回到池中时，重置连接的方式。默认True，总是执行回滚。(不太清楚，建议默认)；

7. ping：确定何时使用ping()检查连接。默认1，即当连接被取走，做一次ping操作。0是从不ping，1是默认，2是当该连接创建游标时ping，4是执行sql语句时ping，7是总是ping。

3.2 简单实现

3.2.1 构建数据库连接池类

from dbutils.pooled_db import PooledDB
import configparser
import pymysql
import time

class Operation_mysql():
    def __init__(self):
        self.cofig()
        self.mysql_pool() 

    # 数据接连接池
    def mysql_pool(self):
        while True:
            try:
                self.mysql_pool_list = PooledDB(creator=pymysql,
                    mincached=20,
                    maxcached=100,
                    host=self.MYSQL_HOST, 
                    port=self.MYSQL_PORT, 
                    user=self.MYSQL_USER,
                    password=self.MYSQL_PASSWORD,
                    db=self.MYSQL_DB,
                    charset='utf8')
            except Exception as e:
                print(f'数据库链接错误{e}')
                self.mysql_pool_list = None

            if self.mysql_pool_list:
                print('数据库链接成功')
                break
            time.sleep(5)

3.2.2 获取一条数据库链接

def get_conn(self):
    conn = self.mysql_pool_list.connection()
    cur = conn.cursor()
    return conn, cur

3.2.3 关闭数据库链接

def close_conn(self, conn, cur):
    cur.close()
    conn.close()

3.2.4 查询数据库

def select_info(self, temp_sql):
   conn, cur = self.get_conn()
   try:
        cur.execute(temp_sql)
        return cur.fetchall()
   except Exception as e:
        print('数据库查询错误')
   finally:
        self.close_conn(conn, cur)

3.2.5 更新数据库

def update_info(self, temp_sql):
    conn, cur = self.get_conn()
    try:
        cur.execute(temp_sql)
        conn.commit()
        return True
    except Exception as e:
        print(f'数据库更新错误{e}')
    finally:
        self.close_conn(conn, cur)

3.2.6 读取配置文件

def cofig(self):
    self.config = configparser.RawConfigParser()
    try:
        self.config.read('setting.ini')
        self.MYSQL_HOST = self.config.get('USER', 'MYSQL_HOST').strip('\' ')
        self.MYSQL_PORT = int(self.config.get('USER', 'MYSQL_PORT').strip('\' '))
        self.MYSQL_USER = self.config.get('USER', 'MYSQL_USER').strip('\' ')
        self.MYSQL_PASSWORD = self.config.get('USER', 'MYSQL_PASSWORD').strip('\' ')
        self.MYSQL_DB = self.config.get('USER', 'MYSQL_DB').strip('\' ')
    except Exception as e:
        print(f'读取配置文件错误：{e}')