def test7_ThreadLocal(self):
persist = PersistentDB(dbapi)
self.assert_(isinstance(persist.thread, ThreadingLocal.local))
class threadlocal:
pass
persist = PersistentDB(dbapi, threadlocal=threadlocal)
self.assert_(isinstance(persist.thread, threadlocal))
def __init__(self, dbname):
if DB_CONFIG.has_key(dbname):
args = (0, 0, 0, 200, 0, 0, None)
if DB_CONFIG[dbname]['type'] == 'mssql':
conn_args = {
'host': DB_CONFIG[dbname]['host'],
'port': DB_CONFIG[dbname]['port'],
'user': DB_CONFIG[dbname]['user'],
'password': DB_CONFIG[dbname]['passwd'],
'database': DB_CONFIG[dbname]['db'],
'charset': 'utf8'
}
try:
#使用PooledDB的效率存在问题，执行效率远低于PersistentDB
#PersistentDB采用一个线程一个db连接，在线程不频繁创建销毁的情景下，效率更好
self._pool = PersistentDB(pymssql, maxusage = 100, **conn_args)
#self._pool = PooledDB(pymssql, *args, **conn_args)
except Exception, e:
raise u"The parameters for DBUtils is:", conn_args
elif DB_CONFIG[dbname]['type'] == 'mysql':
conn_args = {
'host': DB_CONFIG[dbname]['host'],
'port': DB_CONFIG[dbname]['port'],
'user': DB_CONFIG[dbname]['user'],
'passwd': DB_CONFIG[dbname]['passwd'],
'db': DB_CONFIG[dbname]['db'],
'charset': 'utf8',
'cursorclass': DictCursor
}
try:
self._pool = PersistentDB(MySQLdb, maxusage = 100, **conn_args)
#self._pool = PooledDB(MySQLdb, *args, **conn_args)
except Exception, e:
raise u"The parameters for DBUtils is:", conn_args
def test8_PingCheck(self):
Connection = dbapi.Connection
Connection.has_ping = True
Connection.num_pings = 0
persist = PersistentDB(dbapi, 0, None, None, 0, True)
db = persist.connection()
self.assertTrue(db._con.valid)
self.assertEqual(Connection.num_pings, 0)
db.close()
db = persist.connection()
self.assertTrue(not db._con.valid)
self.assertEqual(Connection.num_pings, 0)
persist = PersistentDB(dbapi, 0, None, None, 1, True)
db = persist.connection()
self.assertTrue(db._con.valid)
self.assertEqual(Connection.num_pings, 1)
db.close()
db = persist.connection()
self.assertTrue(db._con.valid)
self.assertEqual(Connection.num_pings, 2)
persist = PersistentDB(dbapi, 0, None, None, 2, True)
db = persist.connection()
self.assertTrue(db._con.valid)
self.assertEqual(Connection.num_pings, 2)
db.close()
db = persist.connection()
self.assertTrue(not db._con.valid)
self.assertEqual(Connection.num_pings, 2)
cursor = db.cursor()
self.assertTrue(db._con.valid)
self.assertEqual(Connection.num_pings, 3)
cursor.execute('select test')
self.assertTrue(db._con.valid)
self.assertEqual(Connection.num_pings, 3)
persist = PersistentDB(dbapi, 0, None, None, 4, True)
db = persist.connection()
self.assertTrue(db._con.valid)
self.assertEqual(Connection.num_pings, 3)
db.close()
db = persist.connection()
self.assertTrue(not db._con.valid)
self.assertEqual(Connection.num_pings, 3)
cursor = db.cursor()
db._con.close()
self.assertTrue(not db._con.valid)
self.assertEqual(Connection.num_pings, 3)
cursor.execute('select test')
self.assertTrue(db._con.valid)
self.assertEqual(Connection.num_pings, 4)
Connection.has_ping = False
Connection.num_pings = 0
def mysqldb_conn(conf):
"""
使用连接池创建mysql连接
用法是:
with mysqldb_conn(conf) as conn:
with cursor(conn) as c:
....
这种方式也可以用，不过更推荐用class MysqlConn的方式来连接mysql.
:param conf:
:return:
"""
try:
_db = PersistentDB(creator=MySQLdb,
maxusage=0,
host=conf['host'],
user=conf['user'],
passwd=conf['passwd'],
db=conf['db'],
charset='utf8')
conn = _db.connection()
yield conn
except Exception as e:
print(e)
conn.rollback()
finally:
conn.close()
def open(self):
try:
if self.use_tunnel and self.tunnel is None:
self.tunnel = TunnelThread(username=self.tunnel_username,
password=self.tunnel_password,
ssh_server=self.host,
ssh_port=self.tunnel_port,
remote_port=self.port)
self.tunnel.start()
host = "127.0.0.1"
port = self.tunnel.local_port
else:
host = self.host
port = self.port
self.dbpool = PersistentDB(creator=MySQLdb,
host=host,
port=port,
user=self.username,
passwd=self.password,
charset="utf8",
use_unicode=True,
compress=self.compression,
setsession=['SET AUTOCOMMIT = 1'])
# test connection
self.cursor().execute("SELECT 1")
except (socket.error, MySQLError) as e:
self.close()
raise e
def __pool_mysql(self,
host,
port,
database,
username,
password,
charset=None):
return PersistentDB(
creator=pymysql, # 使用的数据库模块
maxusage=None, # 一个链接最多被重复使用的次数，None表示无限制
setsession=[], # 开始回话前要执行的命令列表
ping=0, # ping mysql客户端检查是否可用
# A、False表示不关闭: conn.close()操作将被忽略,供下次使用线程关闭时,才会自动关闭链接
# B、False表示要关闭: conn.close()则关闭链接,再次调用pool.connection时就会报错,因为已经真的关闭了连接
# C、pool.steady_connection()可以获取一个新的链接
closeable=False, # 是否关闭链接
threadlocal=None, # 本线程独享值的对象，用于保存链接对象,如果链接被重置
host=host,
port=int(port),
user=username,
password=password,
db=database,
charset='utf8mb4' if charset is None else charset,
cursorclass=pymysql.cursors.DictCursor)
def make_db_pool(database, conns):
# custom connecting method, so we can set up connection config immediately
# after connecting to the server
user = database[8:].split(':')[0]
#port = database[8:].split(':')[2].split('/')[0]
db = database[8:].split('/')[1]
p_h = database[8:].split(':')[1]
passwd = p_h.split('@')[0]
host = p_h.split('@')[1].split('/')[0]
def make_conn(database, *args, **kwargs):
conn = MySQLdb.connect(host=host,
port=3306,
user=user,
passwd=passwd,
db=db,
charset='utf8',
use_unicode=False)
cur = conn.cursor()
cur.execute('set time_zone="+0:00"')
cur.close()
conn.commit()
return conn
# persistent db binds connections to threads
return PersistentDB(creator=partial(make_conn, database),
maxconnections=conns,
blocking=True)
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 __init__(self,host,user,password,port=3306,charset="utf8",pool=False,db="test"):
self.host=host
self.port=port
self.user=user
self.password=password
self.charset=charset
self.connectionPool=pool
self.db=db
try:
if self.connectionPool is True and MySQL.__pool is None:
# creator: 可以生成 DB-API 2 连接的任何函数或 DB-API 2 兼容的数据库连接模块。
# mincached : 启动时开启的空连接数量(缺省值 0 意味着开始时不创建连接)
# maxcached: 连接池使用的最多连接数量(缺省值 0 代表不限制连接池大小)
# maxshared: 最大允许的共享连接数量(缺省值 0 代表所有连接都是专用的)如果达到了最大数量，被请求为共享的连接将会被共享使用。
# maxconnections: 最大允许连接数量(缺省值 0 代表不限制)
# blocking: 设置在达到最大数量时的行为(缺省值 0 或 False 代表返回一个错误；其他代表阻塞直到连接数减少)
# maxusage: 单个连接的最大允许复用次数(缺省值 0 或 False 代表不限制的复用)。当达到最大数值时，连接会自动重新连接(关闭和重新打开)
# setsession: 一个可选的SQL命令列表用于准备每个会话，如 ["set datestyle to german", ...]
# creator 函数或可以生成连接的函数可以接受这里传入的其他参数，例如主机名、数据库、用户名、密码等。你还可以选择传入creator函数的其他参数，允许失败重连和负载均衡。
from DBUtils.PersistentDB import PersistentDB
__pool=PersistentDB(MySQLdb,host=self.host,user=self.user,passwd=self.password,port=self.port,db=self.db)
#from DBUtils.PooledDB import PooledDB
#__pool = PooledDB(MySQLdb,host=self.host,user=self.user,passwd=self.password,port=self.port)
self.conn=__pool.connection()
else:
self.conn=MySQLdb.connect(host=self.host,port=self.port,user=self.user,passwd=self.password,db=self.db)
#self.conn.autocommit(False)
#self.conn.set_character_set(self.charset)
self.cur=self.conn.cursor()
except MySQLdb.Error as e:
print("Mysql Error %d: %s" % (e.args[0], e.args[1]))
def __init__(self, serverID, threadCount, dbConfig, accountList, fetchNewJobCB):
#Init variables
self.id = deepcopy(serverID)
self.threadCount = deepcopy(threadCount)
self.dbConfig = deepcopy(dbConfig)
self.accountList = deepcopy(accountList)
self.callback = fetchNewJobCB
self.todoQueue = timeoutQueue()
self.resultQueue = Queue()
self.accountQueue = Queue()
map(lambda x: self.accountQueue.put(x), accountList)
#Init DBpool
self.persistDB = PersistentDB(MySQLdb, host=self.dbConfig['host'], user=self.dbConfig['user'], passwd=self.dbConfig['passwd'], db=self.dbConfig['name'], charset='utf8')
self.dbConn = self.persistDB.connection()
self.dbCur = self.dbConn.cursor()
#Init Logger
self.logger = logging.getLogger("main")
self.logger.info("accounts to use: %r" % self.accountList)
#Start working thread
self.thread = Thread(target = self.work, args=(currentThread(), ))
self.thread.start()
#Init threads for work
numAcPThread = len(self.accountList) / self.threadCount
for _ in xrange(self.threadCount):
assignedAccounts = map(lambda x: self.accountList.pop(), xrange(numAcPThread))
thread = Thread(target = subworkerProcessing, args=(_+1, self.persistDB, self.todoQueue, self.resultQueue, assignedAccounts, currentThread(), ))
thread.start()
def connect_db():
'''Establishes DB connection'''
return PersistentDB(
creator = pymysql, # the rest keyword arguments belong to pymysql
user = creds.DBUser, password = creds.DBPass, database = creds.DBName,
autocommit = True, charset = 'utf8mb4',
cursorclass = pymysql.cursors.DictCursor)
def __init__(self, is_mult_thread):
filename_dir = os.path.join(os.path.dirname(os.path.dirname(__file__)),
'config')
filename = os.path.join(filename_dir, "db.ini")
#读取db配置信息
self.host = Read_Config(filename).get_value('Database_config', 'host')
self.user = Read_Config(filename).get_value('Database_config', 'user')
self.password = Read_Config(filename).get_value(
'Database_config', 'password')
#端口需要转为整形
self.port = int(
Read_Config(filename).get_value('Database_config', 'port'))
self.db_name = Read_Config(filename).get_value('Database_config',
'db_name')
self.charset = Read_Config(filename).get_value('Database_config',
'charset')
#判断是否采用多线程
if is_mult_thread:
#指定数据库连接驱动以及最大连接数,设置阻塞等待，以及相关配置信息
self.pooldb = PooledDB(creator=pymysql,maxconnections=5,blocking=True,host=self.host,\
user=self.user,password=self.password,port=self.port,\
database=self.db_name,charset=self.charset)
else:
# 指定数据库连接驱动以及连接最大复用次数
self.pooldb = PersistentDB(creator=pymysql,maxusage=1000,host=self.host,\
user=self.user,password=self.password,port=self.port,\
database=self.db_name,charset=self.charset)
#建立连接以及创建游标
self.db = self.pooldb.connection()
self.cur = self.db.cursor()
def __init__(self, dbname):
if DB_CONFIG.has_key(dbname):
args = (0, 0, 0, 200, 0, 0, None)
if DB_CONFIG[dbname]['type'] == 'mysql':
conn_args = {
'host': DB_CONFIG[dbname]['host'],
'port': DB_CONFIG[dbname]['port'],
'user': DB_CONFIG[dbname]['user'],
'passwd': DB_CONFIG[dbname]['passwd'],
'db': DB_CONFIG[dbname]['db'],
'charset': 'utf8',
'cursorclass': DictCursor
}
try:
#使用PooledDB的效率存在问题，执行效率远低于PersistentDB
#PersistentDB采用一个线程一个db连接，在线程不频繁创建销毁的情景下，效率更好
if DB_CONFIG[dbname].get('persistent', True):
self._pool = PersistentDB(MySQLdb,
maxusage=100,
**conn_args)
else:
self._pool = PooledDB(MySQLdb, *args, **conn_args)
except Exception:
raise u"The parameters for DBUtils is:", conn_args
else:
raise u'未支持的数据库类型(%s)' % DB_CONFIG[dbname]['dbtype']
else:
raise u'未约定的数据库连接名称(%s)' % dbname
def __init__(self,
logger,
config,
database=None,
pool_size=None,
*args,
**kwargs):
from clickhouse_driver import Client
from clickhouse_driver import dbapi as ClickHouse
self.logger = logger
self.skip_log = False
if database:
config['database'] = database
if pool_size:
config['maxconnections'] = pool_size
self.config = config
if pool_size:
self.client = PooledDB(ClickHouse, **get_config(config))
else:
self.client = PersistentDB(ClickHouse, **get_config(config))
self.driver = Client(**get_config(self.config))
def __init__(self, host, port, db, user, password):
self._db = PersistentDB(mysql.connector,
host=host,
user=user,
passwd=password,
db=db,
port=port)
def mysql_pool(config, database):
return PersistentDB(
mysql.connector,
database=database,
maxusage=None, # unlimited reuse
ping=1, # 1 = whenever it is requested
**config)
def __init__(self,
host="",
user="",
password="",
db="",
port=3306,
mincached=1,
pattern=1,
charset="utf8"):
self.host = host
if pattern == 1:
self.pool = PooledDB(pymysql,
mincached,
host=host,
user=user,
passwd=password,
db=db,
port=port,
charset=charset)
self.close_able = True
elif pattern == 2:
self.pool = PersistentDB(pymysql,
host=host,
user=user,
passwd=password,
db=db,
port=port,
charset=charset)
self.close_able = False
self._last_use_time = time.time()
def __init__(self, dbname):
self.db = PersistentDB(sqlite3,
maxusage=None,
database=dbname,
closeable=False)
self.con = self.db.connection()
self.cur = self.con.cursor()
def get_a_dbpool(maxusage=None,
setsession=None,
failures=None,
ping=1,
closeable=False,
threadlocal=None):
db_type = __get_dbtype()
kw = {
'db': __get_dbname(),
'host': __get_host(),
'port': __get_port(),
'user': __get_user(),
'password': __get_password()
}
if db_type == DBType.Mysql:
creater = import_module(DBType.Mysql.value)
elif db_type == DBType.Oracle:
creater = import_module(DBType.Oracle.value)
return PersistentDB(creater,
maxusage=None,
setsession=None,
failures=None,
ping=1,
closeable=False,
threadlocal=None,
**kw)
def __init__(self):
self.conn_pool = PersistentDB(creator=MySQLdb,
maxusage=100,
**DbConfig.db_config)
self.conn = self.conn_pool.connection()
self.cursor = self.conn.cursor()
self.module_name = ""
def create_task_from_mysql(use_keyword='0'):
logger = utils.get_logger()
logger.info('start create task from mysql.')
mysql_pool = PersistentDB(
MySQLdb,
host=common_settings.MYSQL_HOST,
user=common_settings.MYSQL_USER,
passwd=common_settings.MYSQL_PASSWD,
db='spider',
port=common_settings.MYSQL_PORT,
charset='utf8'
)
conn = mysql_pool.connection()
cur = conn.cursor()
# city_number = cur.execute('select code from city_entrence where source="ZHAO_PIN_GOU" and valid=1')
# cities = cur.fetchall()
function_number = cur.execute('select * from function_entrence where source="ZHAO_PIN_GOU" and valid=1')
functions = cur.fetchall()
# logger.info('the number of city and functions is:%s, %s' % (city_number, function_number))
# if not city_number or not function_number:
# return
logger.info('the number of functions is:%s, %s' % (len(city_order), function_number))
if not function_number:
return
add_task_url = common_settings.TASK_URL + common_settings.CREATE_TASK_PATH
headers = {'Content-Type': 'application/x-www-form-urlencoded;charset=UTF-8', }
today = datetime.datetime.today()
next_datetime = datetime.datetime(today.year, today.month, today.day, 0, 0, 0) + datetime.timedelta(days=1)
deadline = int(time.mktime(time.strptime(next_datetime.strftime('%Y-%m-%d %H:%M:%S'), '%Y-%m-%d %H:%M:%S'))) * 1000
# use_keyword = '0' if datetime.datetime.now().hour<12 else '1'
# city_result = []
# if cities:
# cities = [i[0] for i in cities]
# for i in city_order:
# if i in cities:
# city_result.append(i)
# cities.remove(i)
# city_result = city_result + cities
random.shuffle(city_order)
for city in city_order:
for function in functions:
add_task_data = {
"callSystemID": "morgan-zhaopingou-resume-1",
"source": 'ZHAO_PIN_GOU',
"traceID": str(uuid.uuid1()),
# "executeParam": json.loads(i.strip()),
"executeParam": json.dumps(
{"fenleiName": function[4], "pFenLeiName": function[1], "positionName": function[7],
"hopeAdressStr": city, "fId": int(function[5]), "pFId": int(function[2]), "pId": int(function[8]),
"id": int(function[11]), 'use_keyword': use_keyword}, ensure_ascii=False),
"taskType": "RESUME_FETCH",
"deadline": deadline
}
add_task_result = utils.download(url=add_task_url, is_json=True, headers=headers, method='post',
data=add_task_data)
logger.info('done.')
def open_pool(self):
self.pool = PersistentDB(
MySQLdb,
host=self.config.get(self.db_section, 'dbhost'),
user=self.config.get(self.db_section, 'dbuser'),
passwd=self.config.get(self.db_section, 'dbpwd'),
db=self.config.get(self.db_section, 'dbname'),
charset='utf8')
def conn(self):
if self._conn is not None:
return self._conn
self._conn = PersistentDB(
MySQLdb, **parse_connection_string_to_dict(
self.connection_string)).connection()
create_call_trace_table(self._conn, self.table)
return self._conn
def __init__(self):
self.pool = PersistentDB(pymysql,
host='localhost',
user='******',
passwd='root',
db='tax_calculator',
charset='utf8',
autocommit=True)
def get_mysql_client():
global mysql_pool
if not mysql_pool:
mysql_pool = PersistentDB(MySQLdb, host=common_settings.MYSQL_HOST, user=common_settings.MYSQL_USER,
passwd=common_settings.MYSQL_PASSWD, db=common_settings.MYSQL_DB,
port=common_settings.MYSQL_PORT, charset='utf8')
conn = mysql_pool.connection()
cur = conn.cursor()
return conn, cur
def __get_sqlite_conn_pool__(self, dbName, db_out_dir):
try:
dbFile = os.path.join(db_out_dir, str(dbName) + ".db")
log.info("dbFilePath:" + str(dbFile))
self.__pool__ = PersistentDB(sqlite3, maxusage=10, database=dbFile)
if not os.path.exists(dbFile):
self.__create_sqlite_tables__()
except Exception as e:
log.exception(e)
def __init__(self):
"""
初始化连接
"""
try:
self._pool = PersistentDB(creator=pg8000, maxusage=None, threadlocal=None,
setsession=[], ping=0, closeable=False, **self.postgre_account)
except Exception as e:
logging.exception(e)
def pgsql_pool(config, database):
# return psycopg2.pool.ThreadedConnectionPool(3, 5, database=database, **config)
# psycopg2.connect(database=database, **config)
return PersistentDB(
psycopg2,
database=database,
maxusage=None, # unlimited reuse
ping=1, # 1 = whenever it is requested
**config)
def connect_db():
return PersistentDB(
creator=pymysql, # the rest keyword arguments belong to pymysql
user='******',
password=config["DB_PASSWORD"],
database='quiz_server',
autocommit=True,
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor)
def CreateDBPool(self):
# 打开数据库连接池，num为连接池里的最少连接数
self.pool = PersistentDB(creator=pymysql,
host=self.host,
user=self.user,
passwd=self.passwd,
db=self.db,
port=self.port,
charset=self.charset) #5为连接池里的最少连接数
