需求分析:
连接外网的sf数据库,对本机的已经配准单号图片的数据库进行信息补全。外网数据库要求性能,及时释放连接。
方案:
pymysql释放连接不彻底,外网数据库使用DBUtils
本机使用pysql简单连接。
"""
数据库工具类
# """
import pymysql
from DBUtils.PooledDB import PooledDB, SharedDBConnection
from DBUtils.PersistentDB import PersistentDB, PersistentDBError, NotSupportedError
config = {
'host': '192.168******',
'port': 3308,
'database': '******',
'user': 'root',
'password': '********',
'charset': 'utf8'
}
#链接1 需要同步的数据库
connect1 = pymysql.Connect(
host='localhost',
port=3306,
user='root',
passwd='****',
db='******',
charset='utf8'
)
def get_db_pool(is_mult_thread):
if is_mult_thread:
poolDB = PooledDB(
# 指定数据库连接驱动
creator=pymysql,
# 连接池允许的最大连接数,0和None表示没有限制
maxconnections=3,
# 初始化时,连接池至少创建的空闲连接,0表示不创建
mincached=2,
# 连接池中空闲的最多连接数,0和None表示没有限制
maxcached=5,
# 连接池中最多共享的连接数量,0和None表示全部共享(其实没什么卵用)
maxshared=3,
# 连接池中如果没有可用共享连接后,是否阻塞等待,True表示等等,
# False表示不等待然后报错
blocking=True,
# 开始会话前执行的命令列表
setsession=[],
# ping Mysql服务器检查服务是否可用
ping=0,
**config
)
else:
poolDB = PersistentDB(
# 指定数据库连接驱动
creator=pymysql,
# 一个连接最大复用次数,0或者None表示没有限制,默认为0
maxusage=1000,
**config
)
return poolDB
def start_syg(count):
#添加信息,发件人没有
sql = "SELECT * FROM logistics where sender_name is null"
try:
cursor1.execute(sql)
# print(type(cursor1.fetchall()))
if cursor1.fetchall:
for row in cursor1.fetchall():
#print(row)
#待插入数据
package_id = row[1]
# package_pro = row[5]
# send_time =row[6]
# sender_name =row[7]
# sender_addr =row[8]
# sender_phone =row[9]
# receiver_name =row[10]
# receiver_addr =row[11]
# receiver_phone =row[12]
#查询表2信息
sql2="SELECT * FROM widetable where billNo = '%s'" % (package_id)
print(sql2)
try:
cursor2.execute(sql2)
if cursor2.fetchone:
row2 = cursor2.fetchone()
print(row2)
package_pro =str(row2[6])
send_time =str(row2[1])
sender_name =str(row2[8])
sender_addr =str(row2[12])
sender_phone =str(row2[9])
receiver_name =str(row2[13])
receiver_addr =str(row2[15])
receiver_phone =str(row2[17])
print(package_pro,sender_phone,sender_addr,sender_name,send_time,receiver_phone,receiver_addr,receiver_name)
#更新不用写括号!改了半天
sql3 = "UPDATE logistics SET package_property='%s',package_send_time='%s',sender_name='%s',sender_address='%s',sender_phone='%s',receiver_name ='%s',receiver_address='%s',receiver_phone='%s'where package_id = '%s'" % (package_pro,send_time,sender_name,sender_addr,sender_phone,receiver_name,receiver_addr,receiver_phone,package_id)
#print(sql3)
#是否抓取一下异常?
cursor1.execute(sql3)
connect1.commit()
print(11121212211111111)
count+=1
print("########################表1已更新",count,"条数据#########################")
#connect1.rollback()
except:
print("表2无匹配数据")
else:
print('目前待同步表没有内容')
except:
pass
if __name__ == '__main__':
# 以单线程的方式初始化数据库连接池
db_pool = get_db_pool(False)
# 从数据库连接池中取出一条连接
connect2 = db_pool.connection()
#两个执行体
cursor1 = connect1.cursor()
cursor2 = connect2.cursor()
#记录添加数据
count = 0
print('开始执行')
start_syg(count)
print('执行结束')
cursor1.close()
connect1.close()
cursor2.close()
connect2.close()
执行结果