DBUtils简介
DBUtils 是一套用于管理数据库连接池的Python包,为高频度高并发的数据库访问提供更好的性能,可以自动管理连接对象的创建和释放。并允许对非线程安全的数据库接口进行线程安全包装。
DBUtils提供两种外部接口:
PersistentDB :提供线程专用的数据库连接,并自动管理连接。
PooledDB :提供线程间可共享的数据库连接,并自动管理连接。
下载安装
下载地址:https://pypi.org/project/DBUtils/
# 方式1
$ wget https://pypi.org/project/DBUtils/2.0/
cd DBUtils-2.0
python setup install
# 方式2
pip install DBUtils==2.0
工作原理
官方文档:
参考博客:https://blog.csdn.net/zhaihaifei/article/details/54016939
使用方式
参考博客:https://www.cnblogs.com/zhuminghui/p/10930846.html
报错处理
- 密码加密方式不对
处理办法:
安装:cryptography
pip install cryptography -i https://pypi.tuna.tsinghua.edu.cn/simple --trusted-host pypi.tuna.tsinghua.edu.cn
- pymysql 链接数据数包1045
解决办法:
a. 看报错,如果是账号或者密码错误,检查账号密码重新输入,若不能解决见步骤b
b. ALTER USER ‘root’@‘localhost’ IDENTIFIED WITH mysql_native_password BY ‘你的密码’; 若还不能解决,见步骤c
c. 进入mysql 执行:select * from mysql.user; 查看host的类型,我的是localhost 我配置文件中写的是127.0.0.1就链接不上,把配置文件中修改为localhost就解决啦。 暂时就这么多,后续若有在补充。
mysql取数据处理
import datetime
import pymysql
from toolkit.time_measure import time_measure
class GenerateInsertSql:
def __init__(self, sql, table, host='localhost', port=3306, user='root', passwd='mima123456', db='bobmydata'):
self.sql = sql
self.host = host
self.port = port
self.user = user
self.password = passwd
self.db = db
self.table = table
@time_measure
def conn_mysql(self):
"""
:return:
"""
conn = pymysql.connect(
host=self.host,
port=self.port,
user=self.user,
passwd=self.password,
db=self.db,
charset='utf8mb4'
# use_unicode=True
)
cursor = conn.cursor()
cursor.execute(self.sql)
rows = cursor.fetchall()
filed = cursor.description
return filed, rows
@time_measure
def generate_insert_into_sql(self):
"""
:return:
"""
fileds, data = self.conn_mysql()
insert_data = []
filed_list = []
# 获取插入的字段
for f in fileds:
filed_list.append(f[0])
# 转换为元组->str并去掉引号
filed_tuple = tuple(filed_list)
# print(filed_tuple)
print("带插入数据的长度为{}".format(len(filed_tuple)))
str_filed = str(filed_tuple).replace("\'", "")
# 数据处理, 处理datetime.datetime类型的数据。转化为 YYYY-mm-dd HH:MM:SS
new_data = []
for row in data:
new_row = []
for e in row:
if isinstance(e, datetime.datetime):
e = datetime.datetime.strftime(e, "%Y-%m-%d %H:%M:%S")
new_row.append(e)
elif e is None:
e = ''
new_row.append(e)
else:
new_row.append(e)
# print("处理后的数据长度:{}".format(len(new_row)))
new_data.append(tuple(new_row))
# 拼装sql
# print(new_data)
for row in new_data:
sql = "INSERT INTO {}.{} {}VALUES {};".format(self.db.upper(), self.table.upper(), str_filed, row)
insert_data.append(sql)
return insert_data
@time_measure
def generate_sql_txt(self):
"""
:return:
"""
filename = self.table + ".txt"
data = self.generate_insert_into_sql()
print(len(data))
with open(filename, mode='w', buffering=True) as f:
for row in data:
f.write(row+"\n")
print("数据已生成,请检查当前目录下的{}".format(filename))
if __name__ == '__main__':
sql = "select * from product_product;"
table_name = "product_product"
p = GenerateInsertSql(sql=sql, table=table_name)
p.generate_sql_txt()