You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
131 lines
4.8 KiB
131 lines
4.8 KiB
# coding=utf-8
|
|
import time
|
|
import pymysql
|
|
from DBUtils.PooledDB import PooledDB,SharedDBConnection
|
|
import json
|
|
import datetime
|
|
import re
|
|
import traceback
|
|
class MySQLUtils(object):
|
|
def __init__(self, host, port, dbuser, password, database):
|
|
self.pool = PooledDB(
|
|
creator= pymysql, #使用连接数据库的模块
|
|
maxconnections= 100, #连接池允许的最大连接数，0和None表示不限制连接数
|
|
mincached= 10, #初始化时，链接池中至少创建的空闲的链接，0表示不创建
|
|
maxcached= 100, #链接池中最多闲置的链接，0和None不限制
|
|
maxshared=0,# 链接池中最多共享的链接数量，0和None表示全部共享。PS: 无用，因为pymysql和MySQLdb等模块的 threadsafety都为1，所有值无论设置为多少，_maxcached永远为0，所以永远是所有链接都共享。
|
|
blocking=True, # 连接池中如果没有可用连接后，是否阻塞等待。True，等待；False，不等待然后报错
|
|
maxusage=None, # 一个链接最多被重复使用的次数，None表示无限制
|
|
setsession=[], # 开始会话前执行的命令列表。如：["set datestyle to ...", "set time zone ..."]
|
|
ping=0, # ping MySQL服务端，检查是否服务可用。# 如：0 = None = never, 1 = default = whenever it is requested, 2 = when a cursor is created, 4 = when a query is executed, 7 = always
|
|
host=host,
|
|
port=int(port),
|
|
user=dbuser,
|
|
password=password,
|
|
database=database,
|
|
charset='utf8mb4'
|
|
)
|
|
#获取连接
|
|
def connectdb(self):
|
|
conn = self.pool.connection()
|
|
# 验证当前连接是否断开,如果断开重新连接
|
|
conn.ping(reconnect=True)
|
|
cursor = conn.cursor(pymysql.cursors.DictCursor)
|
|
return conn,cursor
|
|
|
|
'''
|
|
查询语句
|
|
返回全部内容
|
|
'''
|
|
def queryAll(self,sql):
|
|
conn,cursor = self.connectdb()
|
|
cursor.execute(sql)
|
|
results = cursor.fetchall()
|
|
conn.close()
|
|
return results
|
|
'''
|
|
查询语句
|
|
返回单条内容
|
|
'''
|
|
def queryOne(self,sql):
|
|
conn,cursor = self.connectdb()
|
|
cursor.execute(sql)
|
|
results = cursor.fetchone()
|
|
conn.close()
|
|
return results
|
|
'''
|
|
插入数据
|
|
'''
|
|
def insert(self,sql,values):
|
|
conn, cursor = self.connectdb()
|
|
try:
|
|
# 执行 SQL 语句
|
|
cursor.execute(sql, values)
|
|
# 提交事务
|
|
conn.commit()
|
|
except:
|
|
print('插入失败')
|
|
print('错误sql语句：%s' %sql)
|
|
traceback.print_exc()
|
|
conn.rollback()
|
|
finally:
|
|
conn.close()
|
|
'''
|
|
修改数据
|
|
'''
|
|
def update(self,sql):
|
|
conn, cursor = self.connectdb()
|
|
try:
|
|
cursor.execute(sql)
|
|
conn.commit()
|
|
except:
|
|
print('修改失败')
|
|
print('错误sql语句：%s' %sql)
|
|
print(traceback.print_exc())
|
|
conn.rollback()
|
|
finally:
|
|
conn.close()
|
|
|
|
'''
|
|
删除数据
|
|
'''
|
|
def delete(self,sql):
|
|
conn, cursor = self.connectdb()
|
|
try:
|
|
cursor.execute(sql)
|
|
conn.commit()
|
|
except:
|
|
print('删除失败')
|
|
print('错误sql语句：%s' %sql)
|
|
conn.rollback()
|
|
finally:
|
|
conn.close()
|
|
|
|
def get_conn_pool(host,port,username,password,db):
|
|
sqlhelper = MySQLUtils(host, port, username, password, db)
|
|
return sqlhelper
|
|
|
|
if __name__ == '__main__':
|
|
sqlhelper = MySQLUtils("172.26.11.110", 3306, "crawl", "crawl123", "kyyzgpt")
|
|
|
|
# conn = sqlhelper.pool.connection()
|
|
# cursor = conn.cursor(pymysql.cursors.DictCursor)
|
|
sql = 'select relation_id,start_id,end_id from relations where blueprint_id = 5'
|
|
print("sql:%s" %sql)
|
|
# cursor.execute(sql)
|
|
# results = cursor.fetchall()
|
|
results = sqlhelper.queryOne(sql)
|
|
print (json.dumps(results))
|
|
# if results:
|
|
# print('有数据:{}'.format(len(results)))
|
|
# for item in results:
|
|
#
|
|
# if item['sign']=='user':
|
|
# p1 = r".*(?=/video)"
|
|
# pattern1 = re.compile(p1)
|
|
# matcher1 = re.search(pattern1, item['url'])
|
|
# # attr = {'brand':item['keyword'],'project_name':'208-A国'}
|
|
# attr = {'project_name':'208-A国'}
|
|
# sql = "insert into crawl_seed_task (pageTypeID,cid,task_url,attachTag,crawl_mode,crawl_cyclicity_minute,crawl_period_hour,last_crawl_time,next_crawl_time,createTime) values(61,'youtube','{}','{}',1,720,24,'2019-11-28 12:00:00','2019-11-29 00:00:00',NOW())".format(matcher1.group(),json.dumps(attr).encode('utf-8').decode('unicode_escape'))
|
|
# sqlhelper.insert(sql)
|
|
# print('sql:%s' %sql)
|