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 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 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 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()
class BaseModule(object):
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 init(self):
return
def create_module(self, table_name, create_sql):
self.cursor.execute(create_sql % (table_name))
self.conn.commit()
def select(self, table_name="", field_content="*", condition=""):
if table_name == "":
return None
select_sql = "SELECT " + field_content + " FROM " + table_name
if condition != "":
select_sql += (" WHERE " + condition)
self.cursor.execute(select_sql)
return self.cursor.fetchall()
def update(self, table_name="", update_expr="", condition=""):
if table_name == "" or condition == "":
return
update_sql = "UPDATE " + table_name + " SET " + update_expr
if condition != "":
update_sql += " WHERE " + condition
self.cursor.execute(update_sql)
self.conn.commit()
class persistentdb(object):
def __init__(self,db='cloudmusic'):
self.db=db
self.Pool = PersistentDB(
creator = MySQLdb, #使用链接数据库的模块
maxusage = None, #一个链接最多被使用的次数，None表示无限制
setsession = [], #开始会话前执行的命令
ping = 0, #ping MySQL服务端,检查服务是否可用
closeable = False, #conn.close()实际上被忽略，供下次使用，直到线程关闭，自动关闭链接，而等于True时，conn.close()真的被关闭
threadlocal = None, # 本线程独享值的对象，用于保存链接对象
host = '127.0.0.1',
port = 3306,
user = '******',
passwd = '',
db = self.db,
charset = 'utf8',
cursorclass=MySQLdb.cursors.DictCursor,
)
self.connection=self.Pool.connection()
self.cursor=self.connection.cursor()
def execute(self,sql):
count=self.cursor.execute(sql)
data=self.cursor.fetchall()
self.connection.commit()
logging.info(u'fetchall count is：%s'%count)
# logging.info(u'查询结果为：%s'%str(data))
return count,data
def close(self,):
self.connection.close()
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 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 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
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 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)
class PersistentDB:
def __init__(self, creator, *args, **kwargs):
from DBUtils.PersistentDB import PersistentDB
self.pool = PersistentDB(creator, *args, **kwargs)
self._creator = creator
self._args, self._kwargs = args, kwargs
def connection(self):
connection = self.pool.connection()
return 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 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 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 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 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 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)
class MySql:
def __init__(self, creator, *args, **kwargs):
self.pool = PersistentDB(creator, *args, **kwargs)
self._creator = creator
self._args, self._kwargs = args, kwargs
def connection(self):
try:
connection = self.pool.connection()
return connection
except Exception as er:
logger(str(er))
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 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 get_mysql_client():
logger = utils.get_logger()
logger.info('get a mysql connect')
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_DOWNLOAD_DB,
port=common_settings.MYSQL_PORT,
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 __init__(self):
mysql_pool = PersistentDB(
# 使用的mysql驱动
creator=pymysql,
host='127.0.0.1',
port='root',
user='******',
password='******',
database='py_test',
charset='utf8mb4',
# 一个连接最多被重复使用的次数，None表示无限制
maxusage=None,
)
self.db_conn = mysql_pool.connection()
# 设置返回的数据类型为dict，而不是tuple
self.cursor = self.db_conn.cursor(cursor=pymysql.cursors.DictCursor)
def select_data():
config = "config.conf"
conf = ConfigParser()
conf.read(config)
pool = PersistentDB(MySQLdb,
host=conf.get('database', 'dbhost'),
user=conf.get('database', 'dbuser'),
passwd=conf.get('database', 'dbpwd'),
db=conf.get('database', 'dbname'), charset='utf8')
conn = pool.connection()
cursor = conn.cursor(MySQLdb.cursors.DictCursor)
sql = """SELECT * FROM book"""
cursor.execute(sql)
result = cursor.fetchall()
conn.commit()
cursor.close()
return result
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'])
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 MysqlPersistentDB(object):
"""
保持常量线程数且频繁使用数据库的应用，使用PersistentDB
"""
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 connect(self):
conn = self._pool.connection()
cursor = conn.cursor()
return conn, cursor
def select_data_transaction():
config = "config.conf"
conf = ConfigParser()
conf.read(config)
pool = PersistentDB(MySQLdb,
host=conf.get('database', 'dbhost'),
user=conf.get('database', 'dbuser'),
passwd=conf.get('database', 'dbpwd'),
db=conf.get('database', 'dbname'), charset='utf8')
conn = pool.connection()
cursor = conn.cursor(MySQLdb.cursors.DictCursor)
sql = """SELECT t.id_transaksi as Id, b.title as Title, t.jumlah as Qty, t.total_harga as Total_Price
FROM transaksi t JOIN book b ON t.id_barang = b.id"""
cursor.execute(sql)
result = cursor.fetchall()
conn.commit()
cursor.close()
return result
def delete_data_book_to_db(id_delete):
config = "config.conf"
conf = ConfigParser()
conf.read(config)
pool = PersistentDB(MySQLdb,
host=conf.get('database', 'dbhost'),
user=conf.get('database', 'dbuser'),
passwd=conf.get('database', 'dbpwd'),
db=conf.get('database', 'dbname'), charset='utf8')
try:
conn = pool.connection()
cursor = conn.cursor(MySQLdb.cursors.DictCursor)
sql = "DELETE FROM book WHERE id = {}".format(id_delete)
cursor.execute(sql)
conn.commit()
cursor.close()
conn.close()
except Exception:
raise
class NewLite():
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 select(self, sql):
self.cur.execute(sql)
return self.cur.fetchall()
def execsql(self, sql, param=None):
if param is None:
self.cur.execute(sql)
else:
self.cur.execute(sql, param)
self.con.commit()
def insert_data(title, category, price):
config = "config.conf"
conf = ConfigParser()
conf.read(config)
pool = PersistentDB(MySQLdb,
host=conf.get('database', 'dbhost'),
user=conf.get('database', 'dbuser'),
passwd=conf.get('database', 'dbpwd'),
db=conf.get('database', 'dbname'), charset='utf8')
try:
conn = pool.connection()
cursor = conn.cursor(MySQLdb.cursors.DictCursor)
sql = "INSERT INTO book (title, category, price) VALUES ('{0}', '{1}', {2})".format(title, category, int(price))
cursor.execute(sql)
conn.commit()
cursor.close()
conn.close()
except Exception:
raise
def get_auth(uname, pswd):
config = "config.conf"
conf = ConfigParser()
conf.read(config)
pool = PersistentDB(MySQLdb,
host=conf.get('database', 'dbhost'),
user=conf.get('database', 'dbuser'),
passwd=conf.get('database', 'dbpwd'),
db=conf.get('database', 'dbname'), charset='utf8')
conn = pool.connection()
cursor = conn.cursor(MySQLdb.cursors.DictCursor)
sql = """SELECT password FROM user WHERE username='******'""".format(uname, pswd)
cursor.execute(sql)
result = cursor.fetchone()
conn.commit()
cursor.close()
if result['password'] == pswd:
return True
else:
return False
def insert_data_transaction(id_barang, quality, total_price):
config = "config.conf"
conf = ConfigParser()
conf.read(config)
pool = PersistentDB(MySQLdb,
host=conf.get('database', 'dbhost'),
user=conf.get('database', 'dbuser'),
passwd=conf.get('database', 'dbpwd'),
db=conf.get('database', 'dbname'), charset='utf8')
try:
conn = pool.connection()
cursor = conn.cursor(MySQLdb.cursors.DictCursor)
sql = "INSERT INTO transaksi (id_barang, jumlah, total_harga) VALUES ({0}, {1}, {2})"\
.format(id_barang, quality, total_price)
cursor.execute(sql)
conn.commit()
cursor.close()
conn.close()
except Exception:
raise
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 connection(*args, **kwds):
kwds["maxusage"] = False
persist = DB(MySQLdb, *args, **kwds)
conn = persist.connection()
return conn
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
@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()") # 测试性能
