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))
class MySqlHelper(SqlHelper):
_charset = "utf8"
maxusage = 1000
max_re_time = 50
_pool = None
def __init__(self, conn_dict):
self._parm = conn_dict
self._host = conn_dict.get("host")
self._build_pool()
def _build_pool(self):
parm = self._parm
try:
self._pool = PersistentDB(creator=pymysql,
maxusage=self.maxusage,
host=self._host,
port=int(parm.get("port")),
user=parm.get("user"),
passwd=parm.get("passwd"),
db=parm.get("db"),
charset=self._charset)
self._pool.connection()
except Exception, ex:
import traceback
print traceback.print_exc()
raise Exception("Failed init pool mes:[%s]" % str(ex))
def __init__(self, host, port, db, user, password):
self._db = PersistentDB(mysql.connector,
host=host,
user=user,
passwd=password,
db=db,
port=port)
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, dbname):
self.db = PersistentDB(sqlite3,
maxusage=None,
database=dbname,
closeable=False)
self.con = self.db.connection()
self.cur = self.con.cursor()
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 __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 __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 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, 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,
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):
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 __init__(self, config: dict):
self._default_conf = {
'creator': pymysql,
'cursorclass': DictCursor,
'charset': 'utf8mb4',
'ping': 1
}
self._db_conf = {**self._default_conf, **config}
self._pool = PersistentDB(**self._db_conf)
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 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为连接池里的最少连接数
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 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 create_task_for_meituan():
logger = utils.get_logger()
logger.info('start create task for meituan.')
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=common_settings.MYSQL_DB,
port=common_settings.MYSQL_PORT,
charset='utf8'
)
conn = mysql_pool.connection()
cur = conn.cursor()
function_number = cur.execute(
'select * from function_entrence where source="ZHAO_PIN_GOU" '
'and valid=1 and thirdFunctionCode in '
'(262, 265, 261, 257, 256, 252, 253, 250, 254, 370, 372, 371, 369)')
functions = cur.fetchall()
logger.info('the number of functions is: %s' % (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', }
deadline = datetime.datetime.now() + datetime.timedelta(days=1)
deadline = int(time.mktime(deadline.timetuple())) * 1000
city_dict = {
u'石家庄': '7',
u'邢台': '11',
u'衡水': '17',
u'保定': '12',
u'沧州': '15',
u'扬州': '66',
}
for city in [u'石家庄', u'邢台', u'衡水', u'保定', u'沧州']:
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_dict[city], "fId": int(function[5]), "pFId": int(function[2]),
"pId": int(function[8]), "id": int(function[11])}, 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)
def __getConn(self):
if self.__pool is None:
self.__pool = PersistentDB(creator=MySQLdb,
host=config["mysql"]["host"],
user=config["mysql"]["user"],
passwd=config["mysql"]["passwd"],
db=config["mysql"]["db"],
port=int(config["mysql"]["port"]))
return self.__pool.connection()
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=common_settings.MYSQL_DOWNLOAD_DB,
port=common_settings.MYSQL_PORT,
charset='utf8'
)
conn = mysql_pool.connection()
cur = conn.cursor()
city_number = cur.execute('select * from city_entrence where source="REN_CAI" and valid=1')
cities = cur.fetchall()
function_number = cur.execute('select * from function_entrence where source="REN_CAI" 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
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
city_result = []
# use_keyword = '0' if datetime.datetime.now().hour < 12 else '1'
if cities:
city_dict = {i[1]: i for i in cities}
for i in city_order:
if i in city_dict:
city_result.append(city_dict[i])
city_dict.pop(i)
city_result = city_result + city_dict.values()
for city in city_result:
for function in functions:
add_task_data = {
"callSystemID": 'morgan-rencaia-resume-1',
"source": 'REN_CAI',
"traceID": str(uuid.uuid1()),
# "executeParam": json.loads(i.strip()),
# "executeParam": json.dumps({'residence_ids': city[6], 'residence_name': city[1],
"executeParam": json.dumps({ 'function_ids3': function[8], 'function_id_name': function[7], 'residence_ids': city[6], 'residence_name': city[1],
'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 __init__(self):
"""
初始化连接
"""
try:
self._pool = PersistentDB(pymysql,
maxusage=None,
threadlocal=None,
closeable=False,
**self.mysql_account)
except Exception as e:
logging.exception(e)
def __init__(self):
self.PooL = PersistentDB(
creator=sqlite3, #使用链接数据库的模块
maxusage=None, #一个链接最多被使用的次数，None表示无限制
setsession=[], #开始会话前执行的命令
ping=0, #ping MySQL服务端,检查服务是否可用
closeable=
False, #conn.close()实际上被忽略，供下次使用，直到线程关闭，自动关闭链接，而等于True时，conn.close()真的被关闭
threadlocal=None, # 本线程独享值的对象，用于保存链接对象
database="./fund.db")
self.isopen = lambda x: x.find("开放") >= 0
self.fundnames = {}
def create_task_from_mysql():
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=common_settings.MYSQL_DB,
port=common_settings.MYSQL_PORT,
charset='utf8'
)
conn = mysql_pool.connection()
cur = conn.cursor()
city_number = cur.execute('select cityName, url, code from city_entrence where source="BOSS_HR" and valid=1')
cities = cur.fetchall()
function_number = cur.execute('select thirdFunctionCode from function_entrence where source="BOSS_HR" 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
add_task_url = common_settings.TASK_URL +common_settings.CREATE_TASK_PATH
headers = {'Content-Type': 'application/x-www-form-urlencoded;charset=UTF-8',}
deadline = datetime.datetime.now() + datetime.timedelta(days=2)
deadline = int(time.mktime(deadline.timetuple())) * 1000
task_deleted_file = open('task_deleted', 'r')
task_deleted = json.loads(task_deleted_file.readline())
task_deleted_file.close()
for city in cities:
funcs_deleted = task_deleted.get(city[0], [])
for function in functions:
if not city[1] and function[0] not in funcs_deleted:
add_task_data = {
"callSystemID": settings.project_settings['CALLSYSTEMID'],
"source": settings.project_settings['SOURCE'],
"traceID": str(uuid.uuid1()),
"executeParam": json.dumps({"city": city[2], "cityName": city[0], "jobtitle": function[0]}, ensure_ascii=False),
"taskType": settings.project_settings['TASK_TYPE'],
'deadline': deadline
}
add_task_result = utils.download(url=add_task_url, is_json=True, headers=headers, method='post', data=add_task_data)
else:
for zone in json.loads(city[1]):
add_task_data = {
"callSystemID": settings.project_settings['CALLSYSTEMID'],
"source": settings.project_settings['SOURCE'],
"traceID": str(uuid.uuid1()),
"executeParam": json.dumps({"city": city[2], "cityName": city[0]+'-'+zone, "zone": urllib.quote(zone.encode('utf8')), "jobtitle": function[0]}, ensure_ascii=False),
"taskType": settings.project_settings['TASK_TYPE'],
'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 __init__(self, params, dbutype='PooledDB'):
"""初始化连接池
params包含了数据库连接参数字典
dbutype表示DBUtils的连接类型，分为PooledDB和PersistentDB两种
"""
self.params = params
if dbutype == 'PooledDB':
self.dbpool = PooledDB(**params)
elif dbutype == 'PersistentDB':
self.dbpool = PersistentDB(**params)
else:
raise ValueError, 'Not support dbutype: %s' % repr(dbutype)
def get_mysql_client():
global mysql_pool
if not mysql_pool:
mysql_pool = PersistentDB(MySQLdb,
host=settings.AOURSE_DB_HOST,
user=settings.AOURSE_DB_USER,
passwd=settings.AOURSE_DB_PASS,
db=settings.AOURSE_DB_NAME,
port=3306,
charset='utf8')
conn = mysql_pool.connection()
cur = conn.cursor()
return conn, cur
def test3_PersistentDBConnection(self):
persist = PersistentDB(dbapi)
db = persist.connection()
db_con = db._con
self.assert_(db_con.database is None)
self.assert_(db_con.user is None)
db2 = persist.connection()
self.assertEqual(db, db2)
db3 = persist.dedicated_connection()
self.assertEqual(db, db3)
db3.close()
db2.close()
db.close()
def test2_PersistentDBClose(self): for closeable in (False, True): persist = PersistentDB(dbapi, closeable=closeable) db = persist.connection() self.assert_(db._con.valid) db.close() self.assert_(closeable ^ db._con.valid) db.close() self.assert_(closeable ^ db._con.valid) db._close() self.assert_(not db._con.valid) db._close() self.assert_(not db._con.valid)
def test3_PersistentDBConnection(self): persist = PersistentDB(dbapi) db = persist.connection() db_con = db._con self.assert_(db_con.database is None) self.assert_(db_con.user is None) db2 = persist.connection() self.assertEqual(db, db2) db3 = persist.dedicated_connection() self.assertEqual(db, db3) db3.close() db2.close() db.close()
def test2_PersistentDBClose(self):
for closeable in (False, True):
persist = PersistentDB(dbapi, closeable=closeable)
db = persist.connection()
self.assert_(db._con.valid)
db.close()
self.assert_(closeable ^ db._con.valid)
db.close()
self.assert_(closeable ^ db._con.valid)
db._close()
self.assert_(not db._con.valid)
db._close()
self.assert_(not db._con.valid)
def test9_FailedTransaction(self):
persist = PersistentDB(dbapi)
db = persist.connection()
cursor = db.cursor()
db._con.close()
cursor.execute('select test')
db.begin()
db._con.close()
self.assertRaises(dbapi.InternalError, cursor.execute, 'select test')
cursor.execute('select test')
db.begin()
db.cancel()
db._con.close()
cursor.execute('select test')
def test9_FailedTransaction(self):
persist = PersistentDB(dbapi)
db = persist.connection()
cursor = db.cursor()
db._con.close()
cursor.execute('select test')
db.begin()
db._con.close()
self.assertRaises(dbapi.InternalError, cursor.execute, 'select test')
cursor.execute('select test')
db.begin()
db.cancel()
db._con.close()
cursor.execute('select test')
def get_mysql_client():
global mysql_pool
if not mysql_pool:
mysql_pool = PersistentDB(
MySQLdb,
host='rm-2ze15h84ax219xf08.mysql.rds.aliyuncs.com',
user='******',
passwd='n4UZknFH6F',
db='autojob',
port=3306,
charset='utf8')
conn = mysql_pool.connection()
cur = conn.cursor()
return conn, cur
def test5_PersistentDBMaxUsage(self):
persist = PersistentDB(dbapi, 20)
db = persist.connection()
self.assertEqual(db._maxusage, 20)
for i in range(100):
cursor = db.cursor()
cursor.execute('select test%d' % i)
r = cursor.fetchone()
cursor.close()
self.assertEqual(r, 'test%d' % i)
self.assert_(db._con.valid)
j = i % 20 + 1
self.assertEqual(db._usage, j)
self.assertEqual(db._con.num_uses, j)
self.assertEqual(db._con.num_queries, j)
def test6_PersistentDBSetSession(self):
persist = PersistentDB(dbapi, 3, ('set datestyle',))
db = persist.connection()
self.assertEqual(db._maxusage, 3)
self.assertEqual(db._setsession_sql, ('set datestyle',))
self.assertEqual(db._con.session, ['datestyle'])
cursor = db.cursor()
cursor.execute('set test')
cursor.fetchone()
cursor.close()
for i in range(3):
self.assertEqual(db._con.session, ['datestyle', 'test'])
cursor = db.cursor()
cursor.execute('select test')
cursor.fetchone()
cursor.close()
self.assertEqual(db._con.session, ['datestyle'])
class MysqlHandle(object):
def __init__(self, **kwargs):
self._mysql_pool = PersistentDB(pymysql, **kwargs)
def query_by_sql(self, sql):
conn = self._mysql_pool.connection()
cur = conn.cursor()
try:
cur.execute(sql)
result = cur.fetchall()
return result
except Exception as e:
raise CrwyDbException(e)
finally:
cur.close()
conn.close()
def save(self, sql, data, many=False, get_last_insert_id=False):
conn = self._mysql_pool.connection()
cur = conn.cursor()
try:
if many is False:
cur.execute(sql, data)
else:
cur.executemany(sql, data)
conn.commit()
if get_last_insert_id is False:
return
cur.execute("select last_insert_id() as id")
res = cur.fetchone()
if isinstance(res, tuple):
return res[0]
elif isinstance(res, dict):
return res.get('id')
else:
return res
except Exception as e:
raise CrwyDbException(e)
finally:
cur.close()
conn.close()
def __init__(self, config):
self.dbpool = PersistentDB(
creator=MySQLdb,
db=config['db'],
host=config['host'],
port=config['port'],
user=config['user'],
passwd=config['passwd'],
charset=config['charset'],
maxusage=config['maxusage'],
# read & write timeout
read_timeout=30,
write_timeout=30,
)
class MySQLPool():
def __init__(self, config):
self.dbpool = PersistentDB(
creator=MySQLdb,
db=config['db'],
host=config['host'],
port=config['port'],
user=config['user'],
passwd=config['passwd'],
charset=config['charset'],
maxusage=config['maxusage'],
# read & write timeout
read_timeout=30,
write_timeout=30,
)
def cursor(self, conn):
return conn.cursor(MySQLdb.cursors.DictCursor)
def connect(self):
return self.dbpool.connection()
def __init__(self, **kwargs):
self._mysql_pool = PersistentDB(pymysql, **kwargs)
@classmethod
def dict_all(cls):
return cls.find_all("select * from {0}".format(ta_name(cls.__name__)))
@staticmethod
def find_all(sql):
cur = conn.cursor()
cur.execute(sql)
column_names = [d[0] for d in cur.description]
for row in cur:
yield _Model(dict(zip(column_names, row)))
# 数据库连接池
persist = PersistentDB(MySQLdb, maxusage=5, init_command='set names utf8',
host='192.168.100.243', user='******', passwd='abcd.1234',
db='dj3_cdbgame')
conn = persist.connection()
def test():
class MCareer(_Manager): # 类名与数据库表对应，自定规则
pass
s = MCareer.dict_all()
for i in s:
print i.name
if __name__ == '__main__':
import cProfile
cProfile.run("test()") # 测试性能
def connection(*args, **kwds):
kwds["maxusage"] = False
persist = DB(MySQLdb, *args, **kwds)
conn = persist.connection()
return conn
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
class exposed_Crawler(object):
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 work(self, pThread):
self.logger.info("start to work")
#Init local variables
job = None
finished = True
frList = []
t = 0
SQL1 = "INSERT INTO `crawledUID` (`uid`, `lastUpdate`) VALUES (\'%d\', \'%s\') ON DUPLICATE KEY UPDATE `lastUpdate`=values(`lastUpdate`)"
SQL2 = "INSERT IGNORE INTO `cpUID` (`uid`) VALUES (\'%d\')"
#Start the working loop
while pThread.isAlive():
try:
job = self.callback(self.id, job, finished, frList)
if job == None:
self.logger.debug("Jobs are currently unavailable, try again in 2s")
sleep(2)
continue
job = deepcopy(job)
frList = self.crawl(job)
finished = True
#Update DB when finish
self.dbCur.execute(SQL1 % (job, strftime("%Y-%m-%d %H:%M:%S", localtime())))
if frList == None:
self.dbCur.execute(SQL2 % job)
self.dbConn.commit()
self.logger.info("complete job %r" % job)
except JoinTimeout, e:
self.logger.error('JoinTimeoutException: %r' % e)
frList = None
finished = False
self.todoQueue = timeoutQueue()
self.resultQueue = Queue()
except Exception, e:
self.logger.error('Exception: %r' % e)
import MySQLdb
from DBUtils.PersistentDB import PersistentDB
persist = PersistentDB(MySQLdb,
db='mokodb',
host="localhost",
user="******",
passwd="111111",
)
DATABASE = persist.connection()
DATABASE.b_commit = True
def get_db_by_table(table_name):
return DATABASE
from sqlbean.db import connection
connection.get_db_by_table = get_db_by_table
from sqlbean.shortcut import Model
class User(Model):
class Meta:
pk = "UserId"
for i in User.where():
print i.UserId
