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}')