class PymysqlPool(object):
def __init__(self,
host,
port,
user,
passwd,
db=None,
charset="utf8",
mincached=1,
maxcached=20):
self.pool = PooledDB(
creator=pymysql,
mincached=mincached,
maxcached=maxcached,
host=host,
port=int(port),
user=user,
passwd=passwd,
db=db,
use_unicode=
True, # use_unicode=False时conversion from bytes to Decimal is not supported
charset=charset,
cursorclass=DictCursor)
def getConn(self):
return Pymysql(self.pool)
def close(self):
self.pool.close()
class Worker(multiprocessing.Process):
def __init__(self, queue):
super(Worker, self).__init__()
self.queue = queue
self.pool = PooledDB(psycopg2,
maxconnections=1,
maxcached=1,
blocking=True,
host="localhost",
port=8888,
user="******",
database="chimera")
def __del__(self):
self.pool.close()
def run(self):
connection = pool.connection()
cursor = connection.cursor()
count = 0
for data in iter(self.queue.get, None):
pnfsid = data[1]
al = data[2]
rp = data[3]
count += 1
try:
cursor.execute(UPDATE_QUERY, (
al,
rp,
pnfsid,
))
connection.commit()
except Exception, e:
connection.rollback()
print_error("Failed to update %s %s " % (pnfsid, str(e)))
class DBConnPool():
# 实例化锁
_instance_lock = threading.Lock()
def __init__(self):
# 单例模式的初始化函数必须先判断
if(not hasattr(self, "pool")):
self.pool = PooledDB(creator=pymysql, mincached=1, maxcached=10, maxconnections=100,
blocking=True,host= "127.0.0.1", port=3306, user='******',
passwd='123456',db='yiibaidb', charset='utf8',)
def __new__(cls, *args, **kwargs):
if not hasattr(DBConnPool, "_instance"):
with DBConnPool._instance_lock:
if not hasattr(DBConnPool, "_instance"):
DBConnPool._instance = object.__new__(cls)
return DBConnPool._instance
def __del__(self):
#销毁连接池
self.pool.close()
# 获取连接
def get_connection(self):
return self.pool.connection();
class context(object):
def __init__(self, config):
logging.info('context init.................')
self.redis_pool = redis.ConnectionPool(host=config['redishost'],
port=config['redisport'],
db=config['redisdb'])
#PooledDB __del__ realese all conn
self.mysql_pool = PooledDB(creator=MySQLdb,
mincached=5,
maxcached=20,
host=config['mysqlhost'],
port=config['mysqlport'],
user=config['mysqluser'],
passwd=config['mysqlpasswd'],
db=config['mysqldb'],
use_unicode=False,
charset='utf8mb4',
cursorclass=DictCursor)
def connectionhandler(self, conntype):
if conntype == 'mysql':
return Mysql(self.mysql_pool)
elif conntype == 'redis':
return Redis(self.redis_pool)
else:
raise TypeError('context get connection handler error')
def __del__(self):
self.redis_pool.disconnect()
self.mysql_pool.close()
class MysqlPool(object):
__pool = False
__init = False
def __init__(self):
if not MysqlPool.__init:
self._conn = PooledDB(creator=pymysql, mincached=1, maxcached=20, host=config["HOST"],
port=config["DBPORT"], db=config["DBNAME"], user=config["DBUSER"],
passworld=config["DBPASSWORD"], charset=config["DBCHAR"],
use_unicode=False, cursorclass=DictCursor, autocommit=True
).connection()
def __new__(cls, *args, **kwargs):
if not cls.__pool:
cls.__pool = object.__new__(cls)
return cls.__pool
def __del__(self):
if self._conn:
self._conn.close()
def __repr__(self):
return "连接池的id是 %s" % id(self._conn)
# @staticmethod
# def __get_conn():
# if MysqlPool.__pool is None:
# MysqlPool.__pool = PooledDB(creator=pymysql,mincached=1,maxcached=20,host=config["HOST"],
# port=config["DBPORT"],db=config["DBNAME"],user=config["DBUSER"],
# passworld=config["DBPASSWORD"],charset=config["DBCHAR"],
# use_unicode=False,cursorclass=DictCursor,autocommit=True
# )
# return MysqlPool.__pool.connection()
def get_all(self,sql,params=()):
result = None
with self._conn.cursor() as cursor:
count = cursor.execute(sql,params)
if count > 0:
result = cursor.fetchallDict()
return result
def get_one(self,sql,params=()):
result = None
with self._conn.cursor() as cursor:
count = cursor.execute(sql, params)
if count > 0:
result = cursor.fetchoneDict()
return result
def get_many(self,sql,params=()):
result = None
with self._conn.cursor() as cursor:
count = cursor.execute(sql, params)
if count > 0:
result = cursor.fetchmanyDict()
return result
def insert_delete_update(self,sql,params=()):
with self._conn.cursor() as cursor:
count = cursor.execute(sql, params)
return count
class PooledConnectionPolicy(DatabaseConnectionPolicyIface):
"""This connection policy maintains a pool of connections that are doled out
as needed for each transaction. NOTE: Appropriate for multi-threaded
applications. NOTE: The connections are NOT shared concurrently between
threads.
"""
def __init__(self):
""" Consruct an instance. The instance's open() method must be
called to make it ready for acquireConnection() calls.
"""
self._logger = _getLogger(self.__class__)
self._logger.debug("Opening")
self._pool = PooledDB(**_getCommonSteadyDBArgsDict())
self._logger.info("Created %s", self.__class__.__name__)
return
def close(self):
""" Close the policy instance and its database connection pool. """
self._logger.info("Closing")
if self._pool is not None:
self._pool.close()
self._pool = None
else:
self._logger.warning("close() called, but connection policy was alredy closed")
return
def acquireConnection(self):
""" Get a connection from the pool.
Parameters:
----------------------------------------------------------------
retval: A ConnectionWrapper instance. NOTE: Caller
is responsible for calling the ConnectionWrapper
instance's release() method or use it in a context manager
expression (with ... as:) to release resources.
"""
self._logger.debug("Acquiring connection")
dbConn = self._pool.connection(shareable=False)
connWrap = ConnectionWrapper(
dbConn=dbConn, cursor=dbConn.cursor(), releaser=self._releaseConnection, logger=self._logger
)
return connWrap
def _releaseConnection(self, dbConn, cursor):
""" Release database connection and cursor; passed as a callback to
ConnectionWrapper
"""
self._logger.debug("Releasing connection")
# Close the cursor
cursor.close()
# ... then return db connection back to the pool
dbConn.close()
return
class MysqlPool():
def __init__(self,
host=None,
db=None,
user=None,
pwd=None,
port=None,
maxconnections=20):
self._pool = PooledDB(pymysql,
maxconnections=maxconnections,
maxcached=20,
host=host,
user=user,
passwd=pwd,
db=db,
port=port)
def get_pool(self):
return self._pool
def get_sql(self):
return Sql(self._pool.connection())
def close(self):
self._pool.close()
class PoolDB(object):
def __init__(self,
host,
port,
user,
passwd,
dbName,
log=None,
connect_timeout=10,
read_timeout=None,
min_conn_num=10):
self.host = host
self.port = port
self.user = user
self.passwd = passwd
self.dbName = dbName
self.log = log
self.connectTimeout = connect_timeout
self.readTimeout = read_timeout
self.minConnNum = min_conn_num
self.charset = 'utf8'
self.pool = None
def connect(self):
if self.pool == None:
self.pool = PooledDB(pymysql,
self.minConnNum,
host=self.host,
port=self.port,
user=self.user,
passwd=self.passwd,
db=self.dbName,
charset=self.charset,
connect_timeout=self.connectTimeout,
read_timeout=self.readTimeout)
print('PoolDB init finish')
def close(self):
if self.pool != None:
self.pool.close()
self.pool = None
def query(self, sql):
conn = self.pool.connection()
cur = conn.cursor(pymysql.cursors.DictCursor)
cur.execute(sql)
res = cur.fetchall()
cur.close()
conn.close()
return res
def update(self, sql):
conn = self.pool.connection()
cur = conn.cursor()
effect_rows = cur.execute(sql)
conn.commit()
lastrowId = cur.lastrowid
cur.close()
conn.close()
return effect_rows, lastrowId
def query(dbInfo,sql):
fetchResult=None
pool=None
conn=None
rowcount=0
try:
pool = PooledDB(MySQLdb,2,host=dbInfo.dbHost,user=dbInfo.userName,passwd=dbInfo.password,db=dbInfo.dbName,port=int(dbInfo.dbPort))
conn = pool.connection()
cursor=conn.cursor()
cursor.execute(sql)
fetchResult=cursor.fetchall()
rowcount=cursor.rowcount
if rowcount > 0 :
# print ("DBHelper query sql success! "+sql)
pass
if cursor is not None:
cursor.close()
except Exception as e:
logging.error('DBHelper query sql fail! sql ： %s , reason %s ' % (sql,str(e)))
traceback.print_exc()
finally:
if conn is not None:
conn.close()
if pool is not None:
pool.close()
return (rowcount,fetchResult)
def insert(dbInfo,tableName,dataDict):
pool=None
conn=None
try:
dataValues = "(" + "%s," * (len(dataDict)) + ")"
dataValues = dataValues.replace(',)', ')')
dbField = dataDict.keys()
dataTuple = tuple(dataDict.values())
dbField = str(tuple(dbField)).replace("'",'')
pool = PooledDB(MySQLdb,2,host=dbInfo.dbHost,user=dbInfo.userName,passwd=dbInfo.password,db=dbInfo.dbName,port=int(dbInfo.dbPort))
conn = pool.connection()
cursor = conn.cursor()
sql = """ insert into %s %s values %s """ % (tableName,dbField,dataValues)
params = dataTuple
cursor.execute(sql, params)
conn.commit()
cursor.close()
return 1
except Exception as e:
logging.error('DBHelper insert sql fail! sql ： %s , reason %s ' % (sql,str(e)))
traceback.print_exc()
return 0
finally:
if conn is not None:
conn.close()
if pool is not None:
pool.close()
def readSODB_FAST(g_DataQueue, g_FinishRead):
pool = PooledDB(MySQLdb,
5,
host='localhost',
user='******',
passwd='123456',
db='so',
port=3306,
charset='utf8mb4',
cursorclass=SSCursor)
batch_size = 50000 # 一次读取的记录数
i = 0
while (True):
# print(i)
conn = pool.connection()
curs = conn.cursor()
sql_query = "SELECT Title,Body FROM sodata WHERE Id >= (select Id from sodata limit {0}, 1) limit {1}".format(
str(i * batch_size), str(batch_size))
curs.execute(sql_query)
data = list(curs.fetchmany(batch_size))
# print(len(data))
if (len(data) == 0):
break
g_DataQueue.put(data)
# for ii in data:
# print ii
i += 1
curs.close()
del curs
conn.close()
pool.close()
g_FinishRead.value = 1 # 读取结束，设置标志位
class SqlPool(object):
def __init__(self, db_conf, maxcached=10, maxshared=10, maxconnections=0):
'''
Constructor
'''
self.conn = None
self.maxcached = maxcached
self.maxshared = maxshared
self.maxconnections = maxconnections
from DBUtils.PooledDB import PooledDB
self.conn_pool = PooledDB(import_db_lib(db_conf['TYPE']),
maxcached=self.maxcached,
maxshared=self.maxshared,
maxconnections=self.maxconnections,
host=db_conf['HOST'],
port=db_conf['PORT'],
user=db_conf['USER'],
passwd=db_conf['PASSWORD'],
db=db_conf['NAME'],
charset="utf8")
def get_connection(self):
try:
return self.conn_pool.connection()
except Exception as e:
print e
raise Exception("failed to connect database")
def close(self):
self.conn_pool.close()
class MysqlPipeline(object):
"""
sql:
CREATE TABLE `comments` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`goods_url` varchar(1024) COLLATE utf8mb4_general_ci DEFAULT NULL,
`goods_title` varchar(1024) COLLATE utf8mb4_general_ci DEFAULT NULL,
`nickname` varchar(40) COLLATE utf8mb4_general_ci DEFAULT NULL,
`content` varchar(1024) COLLATE utf8mb4_general_ci DEFAULT NULL,
`append_comment` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
`videos` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
`nid` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
`photos` text COLLATE utf8mb4_general_ci,
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7754 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
"""
def process_item(self, item, spider):
sql = """
INSERT INTO `comments` (`nid`, `goods_url` , `goods_title`, `nickname`, `content`, `append_comment`, `videos`, `photos`)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
"""
try:
with self.connection() as conn:
cursor = conn.cursor()
cursor.execute(sql, [
jsonify(item.get(key, '')) for key in
('shop_id,goods_url,goods_title,nickname,content,append_comment,videos,photos'
.split(','))
])
conn.commit()
except Exception as e:
_logger.error(e)
_logger.debug('异常SQL -> ', self.cursor._last_executed)
return item
def open_spider(self, spider):
_logger.debug('##########################打开数据库################### ')
from .settings import MYSQL_HOST, MYSQL_DB, MYSQL_USER, MYSQL_CHARSET, MYSQL_PASS, MYSQL_PORT
from DBUtils.PooledDB import PooledDB
self.pool = PooledDB(pymysql,
host=MYSQL_HOST,
user=MYSQL_USER,
password=MYSQL_PASS,
port=MYSQL_PORT,
db=MYSQL_DB,
charset=MYSQL_CHARSET)
def close_spider(self, spider):
_logger.debug('########################关闭数据库################## ')
self.pool.close()
@contextmanager
def connection(self):
conn = self.pool.connection()
try:
yield conn
finally:
conn.close()
class DBHelper:
# 디비 연결 정보
app = None
# 풀링 객체(디비와 커넥션 정보를 가지고 있는 객체)
connectionPool = None
# 생성자
def __init__(self,app):
self.app = app
self.initPool()
# 소멸자
def __del__(self):
self.freePool()
######################
# 커넥션 풀 생성
def initPool(self):
# 아래 코드 수행 후 connectionPool 객체는 총 100개(maxconnection 수)
# 디비와 연결 세션을 가지고 있게 된다.
self.connectionPool = PooledDB(
creator = my,
# 값1 if 조건문 else 값2
host = self.app.config.get('DB_REAL_URL')
if self.app.config.get('SERVER_RUN_MODE_IS_REAL') else
self.app.config.get('DB_TEST_URL'),
user = self.app.config.get('DB_USER'),
password = self.app.config.get('DB_PASSWORD'),
database = self.app.config.get('DB_DATABASE'),
charset = self.app.config.get('DB_CHARSET'),
maxconnections = self.app.config.get('MAX_POOL'),
autocommit = False,
blocking = False,
cursorclass = my.cursors.DictCursor
)
# 커넥션 풀 해제
def freePool(self):
# 잡아두고 있던 100개의 디비세션을 모두 반납한다.
if self.connectionPool:
self.connectionPool.close()
# 로그인
def login_sql(self, uid, upw):
row = None # 로그인 결과를 담는 변수
connection = None
try:
# 디비 연결 세션 하나 빌림
connection = self.connectionPool.connection()
with connection.cursor() as cursor:
sql = "select * from users where uid=%s and upw=%s;"
cursor.execute(sql,(uid,upw))
row = cursor.fetchone()
except Exception as e:
print('->',e)
row =None
finally:
# 연결 세션 반납
connection.close()
return row
def test05_CloseAll(self):
for threadsafety in (1, 2):
dbapi.threadsafety = threadsafety
shareable = threadsafety > 1
pool = PooledDB(dbapi, 10)
self.assertEqual(len(pool._idle_cache), 10)
pool.close()
self.assertEqual(len(pool._idle_cache), 0)
pool = PooledDB(dbapi, 10)
closed = ['no']
def close(what=closed):
what[0] = 'yes'
pool._idle_cache[7]._con.close = close
self.assertEqual(closed, ['no'])
del pool
self.assertEqual(closed, ['yes'])
pool = PooledDB(dbapi, 10, 10, 5)
self.assertEqual(len(pool._idle_cache), 10)
if shareable:
self.assertEqual(len(pool._shared_cache), 0)
cache = []
for i in range(5):
cache.append(pool.connection())
self.assertEqual(len(pool._idle_cache), 5)
if shareable:
self.assertEqual(len(pool._shared_cache), 5)
else:
self.assertEqual(len(pool._idle_cache), 5)
pool.close()
self.assertEqual(len(pool._idle_cache), 0)
if shareable:
self.assertEqual(len(pool._shared_cache), 0)
pool = PooledDB(dbapi, 10, 10, 5)
closed = []
def close_idle(what=closed):
what.append('idle')
def close_shared(what=closed):
what.append('shared')
if shareable:
cache = []
for i in range(5):
cache.append(pool.connection())
pool._shared_cache[3].con.close = close_shared
else:
pool._idle_cache[7]._con.close = close_shared
pool._idle_cache[3]._con.close = close_idle
self.assertEqual(closed, [])
del pool
if shareable:
del cache
self.assertEqual(closed, ['idle', 'shared'])
class CVESQL:
def __init__(self):
self.host = mysql_host
self.port = mysql_port
self.username = mysql_username
self.password = mysql_password
self.dbname = mysql_db
self.chrset = mysql_chrset
self.conn = PooledDB(MySQLdb,
host=self.host,
user=self.username,
passwd=self.password,
db=self.dbname,
port=int(self.port),
charset=self.chrset).connection()
def getbysql(self, sql, args):
'''
Get by sql
:param sql:
:param param:
:return:
'''
try:
cursor = self.conn.cursor()
cursor.execute(sql, args)
res = cursor.fetchall()
cursor.close()
return res
except Exception as e:
print e
return "None"
finally:
cursor.close()
def insertbysql(self, sql, args):
'''
:param sql:
:param args:
:return:
'''
try:
cursor = self.conn.cursor()
#import pdb;pdb.set_trace()
cursor.execute(sql, args)
print "Inserting data >>>", args
self.conn.commit()
except Exception as e:
print "Error encountered >>> ", e
finally:
cursor.close()
def __del__(self):
self.conn.close()
class DataBaseOperate(object):
def __init__(self):
self.__log = Log("DataBaseOperate", 'DEBUG').logger
self.__db_pool = None
def creat_db_pool(self, mysql):
user = mysql.get('MYSQL_USER')
password = mysql.get('MYSQL_PASSWD')
port = int(mysql.get('MYSQL_PORT'))
host = mysql.get('MYSQL_HOST')
self.__log.debug('创建数据库连接池：%s' % host)
self.__db_pool = PooledDB(creator=pymysql,
mincached=3,
maxcached=5,
maxshared=0,
maxconnections=20,
blocking=True,
maxusage=None,
setsession=None,
host=host,
port=port,
user=user,
db=None,
passwd=password)
self.__log.debug('创建数据库连接池完成!')
def query_data(self, sql):
con = self.__db_pool.connection()
cursor = con.cursor(cursor=pymysql.cursors.DictCursor)
try:
self.__log.error(sql)
cursor.execute(sql)
results = cursor.fetchall()
self.__log.debug(results)
for result in results:
for fields in result:
if isinstance(result[fields], datetime.datetime):
result[fields] = str(
result[fields].strftime('%Y-%m-%d %H:%M:%S'))
elif isinstance(result[fields], datetime.date):
result[fields] = str(
result[fields].strftime('%Y-%m-%d'))
elif isinstance(result[fields], decimal.Decimal):
result[fields] = float(result[fields])
return results
except Exception as e:
self.__log.error('执行sql异常：\n%s' % e)
self.__log.error(sql)
finally:
cursor.close()
con.close()
def close_db_pool(self):
self.__db_pool.close()
def test05_CloseAll(self):
for threadsafety in (1, 2):
dbapi.threadsafety = threadsafety
shareable = threadsafety > 1
pool = PooledDB(dbapi, 10)
self.assertEqual(len(pool._idle_cache), 10)
pool.close()
self.assertEqual(len(pool._idle_cache), 0)
pool = PooledDB(dbapi, 10)
closed = ['no']
def close(what=closed):
what[0] = 'yes'
pool._idle_cache[7]._con.close = close
self.assertEqual(closed, ['no'])
del pool
self.assertEqual(closed, ['yes'])
pool = PooledDB(dbapi, 10, 10, 5)
self.assertEqual(len(pool._idle_cache), 10)
if shareable:
self.assertEqual(len(pool._shared_cache), 0)
cache = []
for i in range(5):
cache.append(pool.connection())
self.assertEqual(len(pool._idle_cache), 5)
if shareable:
self.assertEqual(len(pool._shared_cache), 5)
else:
self.assertEqual(len(pool._idle_cache), 5)
pool.close()
self.assertEqual(len(pool._idle_cache), 0)
if shareable:
self.assertEqual(len(pool._shared_cache), 0)
pool = PooledDB(dbapi, 10, 10, 5)
closed = []
def close_idle(what=closed):
what.append('idle')
def close_shared(what=closed):
what.append('shared')
if shareable:
cache = []
for i in range(5):
cache.append(pool.connection())
pool._shared_cache[3].con.close = close_shared
else:
pool._idle_cache[7]._con.close = close_shared
pool._idle_cache[3]._con.close = close_idle
self.assertEqual(closed, [])
del pool
if shareable:
del cache
self.assertEqual(closed, ['idle', 'shared'])
class heimdalldb(object):
def __init__(self):
'''
Read db config
'''
config = ConfigParser.ConfigParser()
config.read('./utils/dbcfg.conf')
self.host = config.get("MYSQLDB", "host")
self.port = config.get("MYSQLDB", "port")
self.username = config.get("MYSQLDB", "username")
self.password = config.get("MYSQLDB", "password")
self.dbname = config.get("MYSQLDB", "dbname")
self.chrset = config.get("MYSQLDB", "chrset")
self.conn = PooledDB(MySQLdb,host=self.host,user=self.username,passwd=self.password,db=self.dbname,port=int(self.port),charset=self.chrset).connection()
def insertbysql(self, sql):
'''
insert by sql
'''
try:
cursor = self.conn.cursor()
cursor.execute(sql)
self.conn.commit()
except Exception as e:
return "None"
finally:
cursor.close()
def getbysql(self, sql):
'''
Get by sql
:param sql:
:param param:
:return:
'''
try:
cursor = self.conn.cursor()
cursor.execute(sql)
res = cursor.fetchall()
cursor.close()
return res
except Exception as e:
return "None"
finally:
cursor.close()
def __del__(self):
self.conn.close()
class MysqlPool:
'''A class of connect pool to Mysql Database'''
def __init__(self, args):
# mysql connect params
args["charset"] = "utf8"
args["setsession"] =['SET AUTOCOMMIT = 1']
# mysql connect pool
self.pooldb = PooledDB(MySQLdb, **args)
def __del__(self):
if self.pooldb: self.pooldb.close()
def select(self, sql, args=None):
try:
conn= self.pooldb.connection()
cur = conn.cursor()
cur.execute(sql, args)
results = cur.fetchall()
cur.close()
conn.close()
return results
except Exception as e:
Common.log('# MyDatabase select exception: %s %s' % (e, sql))
Common.log(args)
return None
def execute(self, sql, args=None):
try:
conn= self.pooldb.connection()
cur = conn.cursor()
cur.execute(sql, args)
cur.close()
conn.close()
except Exception as e:
Common.log('# MyDatabase execute exception: %s %s' % (e, sql))
Common.log(args)
def executemany(self, sql, args_list=[]):
try:
conn= self.pooldb.connection()
cur = conn.cursor()
cur.executemany(sql, args_list)
cur.close()
conn.close()
except Exception as e:
Common.log('# MyDatabase executemany exception: %s %s' % (e, sql))
Common.log(args_list)
class MysqlPool:
'''A class of connect pool to Mysql Database'''
def __init__(self, args):
# mysql connect params
args["charset"] = "utf8"
args["setsession"] = ['SET AUTOCOMMIT = 1']
# mysql connect pool
self.pooldb = PooledDB(MySQLdb, **args)
def __del__(self):
if self.pooldb: self.pooldb.close()
def select(self, sql, args=None):
try:
conn = self.pooldb.connection()
cur = conn.cursor()
cur.execute(sql, args)
results = cur.fetchall()
cur.close()
conn.close()
return results
except Exception as e:
Common.log('# MyDatabase select exception: %s %s' % (e, sql))
Common.log(args)
return None
def execute(self, sql, args=None):
try:
conn = self.pooldb.connection()
cur = conn.cursor()
cur.execute(sql, args)
cur.close()
conn.close()
except Exception as e:
Common.log('# MyDatabase execute exception: %s %s' % (e, sql))
Common.log(args)
def executemany(self, sql, args_list=[]):
try:
conn = self.pooldb.connection()
cur = conn.cursor()
cur.executemany(sql, args_list)
cur.close()
conn.close()
except Exception as e:
Common.log('# MyDatabase executemany exception: %s %s' % (e, sql))
Common.log(args_list)
class BaseDBHelper(object):
def __init__(self, db_type, cursor_type, **kwargs):
self.pool = PooledDB(db_type, **kwargs)
self.conn = self.pool.connection()
self.cr = self.conn.cursor(cursor_type)
def __del__(self):
self.close()
def commit(self):
self.conn.commit()
def close(self):
self.cr.close()
self.conn.close()
self.pool.close()
class DBUtilPooledDBDataSource(AbstractDataSource):
def __init__(self, *args, **kwargs):
super().__init__(*args, **kwargs)
from DBUtils.PooledDB import PooledDB
# self._conn_params['use_pure'] = True
self._pool = PooledDB(connector,
maxcached=8,
maxconnections=50,
**self._conn_params)
self._closed = False
def _inner_close(self):
self._pool.close()
def get_conn(self):
return self._pool.dedicated_connection()
def release_conn(self, conn):
conn.close()
class dbOperator:
def __init__(self):#填入你的数据库
self.conn = PooledDB(MySQLdb, maxusage=1, db=g_dbName, host=g_dbHost, user=g_dbUser, passwd=g_dbPasswd, charset='utf8')
def insert(self,value):
pool = self.conn.connection()
cursor = pool.cursor()
#value = [None,pid,wbid,content,date]
cursor.execute("insert into data values(%s,%s,%s,%s,%s)",value);
cursor.close()
pool.commit()
pool.close()
def insertRetweet(self,value):
pool = self.conn.connection()
cursor = pool.cursor()
#value = [None,wbid,username,content,date]
cursor.execute("insert into retweet values(%s,%s,%s,%s,%s)",value);
cursor.close()
pool.commit()
pool.close()
def updateName(self,upid,value):
pool = self.conn.connection()
cursor = pool.cursor()
#value = [None,name,wbid,content,date]
cursor.execute("UPDATE weibo_data SET name = \'%s\' WHERE wbid = \'%s\'" % (value,upid))
cursor.close()
pool.commit()
pool.close()
def selectData(self,sqlstr):
pool = self.conn.connection()
cursor = pool.cursor()
count = cursor.execute(sqlstr)
results = cursor.fetchall()
cursor.close()
pool.close()
return results
def closeDb(self):
self.conn.close()
class MysqlPool:
def __init__(self,
charset="utf8",
mincached=1,
maxcached=2,
maxconnections=100,
blocking=True,
maxshared=0):
"""
生成MySQL数据库连接池
:param charset: 使用字符集
:param mincached: 最少的空闲连接数，如果空闲连接数小于这个数，pool会创建一个新的连接
:param maxcached: 最大的空闲连接数，如果空闲连接数大于这个数，pool会关闭空闲连接
:param maxconnections: 最大的连接数
:param blocking: 当连接数达到最大的连接数时，在请求连接的时候，如果这个值是True，请求连接的程序会一直等待，
直到当前连接数小于最大连接数，如果这个值是False，会报错，
:param maxshared: 当连接数达到这个数，新请求的连接会分享已经分配出去的连接
"""
db_config = {
"host": '39.105.9.20',
"port": 3306,
"user": '******',
"passwd": 'bigdata_oil',
"db": 'cxd_data',
"charset": 'utf8'
}
self.pool = PooledDB(pymysql,
mincached=mincached,
maxcached=maxcached,
maxconnections=maxconnections,
blocking=blocking,
maxshared=maxshared,
**db_config)
def get_connection(self):
return self.pool.connection()
def close(self):
self.pool.close()
def __del__(self):
self.close()
class MySQLDBPipeline(object):
@classmethod
def from_settings(cls, settings):
return cls(settings)
def __init__(self, settings):
dbargs = dict(
host=settings['MYSQL_HOST'],
db=settings['MYSQL_DBNAME'],
user=settings['MYSQL_USER'],
passwd=settings['MYSQL_PASSWD'],
charset='utf8',
cursorclass=pymysql.cursors.DictCursor,
use_unicode=True,
)
self.pool = PooledDB(pymysql, 5, **dbargs) # 5为连接池里的最少连接数
self.table = settings['MYSQL_TABLE']
def open_spider(self, spider):
pass
# pipeline默认调用
def process_item(self, item, spider):
self._do_upinsert(item, spider)
def close_spider(self, spider):
"""Discard the database pool on spider close"""
self.pool.close()
# 将每行更新或写入数据库中
def _do_upinsert(self, item, spider):
conn = self.pool.connection() # 以后每次需要数据库连接就是用connection（）函数获取连接就好了
cur = conn.cursor()
data_dict = dict(item)
table = self.table
sql = get_i_sql(table, data_dict)
print(sql)
cur.execute(sql)
conn.commit()
def dbutils_test():
V2_CONFIG = {
'host': '12.34.12.34',
'port': 3306,
'user': '******',
'password': '******',
'db': 'v2',
'charset': 'utf8',
}
pool = PooledDB(pymysql, 50, **V2_CONFIG)
conn = pool.connection()
cur = conn.cursor()
sql = "select * from User where id=1"
cur.execute(sql)
r = cur.fetchall()
print(r)
cur.close()
conn.close()
pool.close()
class context(object):
def __init__(self,config):
logging.info('context init.................')
self.redis_pool = redis.ConnectionPool(host=config['redishost'], port=config['redisport'], db=config['redisdb'])
#PooledDB __del__ realese all conn
self.mysql_pool = PooledDB(creator=MySQLdb, mincached=5, maxcached=20,
host=config['mysqlhost'], port=config['mysqlport'], user=config['mysqluser'],
passwd=config['mysqlpasswd'],db=config['mysqldb'], use_unicode=False, charset='utf8mb4',
cursorclass=DictCursor)
def connectionhandler(self,conntype):
if conntype == 'mysql':
return Mysql(self.mysql_pool)
elif conntype == 'redis':
return Redis(self.redis_pool)
else:
raise TypeError('context get connection handler error')
def __del__(self):
self.redis_pool.disconnect()
self.mysql_pool.close()
def update(dbInfo,sql):
pool=None
conn=None
try:
pool = PooledDB(MySQLdb,2,host=dbInfo.dbHost,user=dbInfo.userName,passwd=dbInfo.password,db=dbInfo.dbName,port=int(dbInfo.dbPort))
conn = pool.connection()
cursor = conn.cursor()
cursor.execute(sql)
conn.commit()
cursor.close()
return 1
except Exception as e:
logging.error('DBHelper update sql fail! sql ： %s , reason %s ' % (sql,str(e)))
traceback.print_exc()
return 0
finally:
if conn is not None:
conn.close()
if pool is not None:
pool.close()
def dbutils_test():
V2_CONFIG = {
'host': '12.34.12.34',
'port': 3306,
'user': '******',
'password': '******',
'db': 'v2',
'charset': 'utf8',
}
pool = PooledDB(pymysql, 50, **V2_CONFIG)
conn = pool.connection()
cur = conn.cursor()
sql = "select * from User where id=1"
cur.execute(sql)
r = cur.fetchall()
print(r)
cur.close()
conn.close()
pool.close()
class Test_SQLStorePipeline(unittest.TestCase):
pipe = None
pool = None
def setUp(self):
self.pool = PooledDB(creator=pymysql,
maxcached=10,
maxshared=10,
host='localhost',
user='******',
passwd='123456',
db='test',
port=3306,
charset="utf8",
setsession=['SET AUTOCOMMIT = 1'])
self.pipe = SQLStorePipeline(self.pool)
def tearDown(self):
self.pool.close()
def truncateFictions(self):
cursor = self.pool.connection().cursor()
cursor.execute("truncate table fictions")
def countFictions(self):
cursor = self.pool.connection().cursor()
cursor.execute("select count(*) from fictions")
def bulk_insert_fictions(self):
self.truncateFictions()
fictions = []
for i in range(30):
f = FictionItem()
f["fiction_id"] = i
f["name"] = "test" + str(i)
f["url"] = FICTION_URL.format(i)
fictions.append(f)
self.pipe.bulk_insert_fictions(fictions)
class MysqlPool(object): #设置数据库连接池和初始化
"""
MYSQL数据库对象，负责产生数据库连接 , 此类中的连接采用连接池实现
获取连接对象：conn = Mysql.get_connection()
释放连接对象;conn.close()或del conn
"""
def __init__(self,mincached=5, maxcached=14,
maxconnections=244, blocking=True, maxshared=0):
"""
生成MySQL数据库连接池
:param mincached: 最少的空闲连接数，如果空闲连接数小于这个数，pool会创建一个新的连接
:param maxcached: 最大的空闲连接数，如果空闲连接数大于这个数，pool会关闭空闲连接
:param maxconnections: 最大的连接数
:param blocking: 当连接数达到最大的连接数时，在请求连接的时候，如果这个值是True，请求连接的程序会一直等待，
直到当前连接数小于最大连接数，如果这个值是False，会报错，
:param maxshared: 当连接数达到这个数，新请求的连接会分享已经分配出去的连接
"""
db_config = {
"host": '47.92.25.70',
"port": 3306,
"user": '******',
"passwd": 'Wfn031641',
"db": 'cxd_data',
"charset": 'utf8'
}
self.pool = PooledDB(pymysql, mincached=mincached, maxcached=maxcached, maxconnections=maxconnections,
blocking=blocking, maxshared=maxshared, **db_config)
def get_connection(self):
return self.pool.connection()
def close(self):
self.pool.close()
def __del__(self):
self.close()
class POOL_DB(object):
def __init__(self):
self.POOL = PooledDB(
creator=pymysql, # 使用链接数据库的模块
maxconnections=2, # 连接池允许的最大连接数，0和None表示不限制连接数
mincached=2, # 初始化时，链接池中至少创建的空闲的链接，0表示不创建
maxcached=0, # 链接池中最多闲置的链接，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='192.168.5.105',
port=3306,
user='******',
password='******',
database='test_zhengce365_collect',
charset='utf8',
)
def create_conn(self):
conn = self.POOL.connection()
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
return conn, cursor
# 关闭连接
def close_conn(self, conn, cursor):
cursor.close()
conn.close()
def close_db(self):
self.POOL.close()
class MySQLClient(object):
def __init__(self, host, port, db, \
user, passwd, max_connection, charset):
'''
self.cfg_ = ConfigParser.ConfigParser()
self.cfg_.read(conf_path)
self.host_ = self.cfg_.get("mysql", "host") or "127.0.0.1"
self.port_ = int(self.cfg_.get("mysql", "port")) or 3306
self.db_ = self.cfg_.get("mysql", "db")
self.user_ = self.cfg_.get("mysql", "user")
self.passwd_ = self.cfg_.get("mysql", "passwd")
self.max_connection_ = int(self.cfg_.get("mysql", "conneciton_")) or 1
self.charset_ = self.cfg_.get("mysql", "charset") or "utf8"
'''
self.host_ = host
self.port_ = port
self.db_ = db
self.user_ = user
self.passwd_ = passwd
self.max_connection_ = max_connection
self.charset_ = charset
self.connect()
def connect(self):
self.db_client_ = PooledDB(MySQLdb, maxusage=self.max_connection_, \
db=self.db_, host=self.host_, \
user=self.user_, passwd=self.passwd_, \
charset=self.charset_)
def __del__(self):
self.db_client_.close()
def get_connection(self):
if not self.db_client_:
self.connect()
return self.db_client_.connection()
class SaveDataPipeline(object):
"""
爬取的数据保存到数据库
"""
# spider 开启时调用
def __init__(self):
db_name = settings.get("DB_NAME")
host = settings.get("DB_HOST")
port = settings.get("DB_PORT")
user = settings.get("DB_USER")
password = settings.get("DB_PASSWORD")
self._pool = PooledDB(pymysql,
10,
60,
database=db_name,
host=host,
port=port,
user=user,
password=password,
charset="utf8")
self._insert_corp_info_sql = """insert into stg_corp_info(`id`,
`org_name`, `org_code`, `org_type`, `address`, `tyshxydm`, `nsrsbh`, `reg_number`,
`legal_rep`, `bus_status`, `reg_capital`, `create_time`, `reg_authority`, `op_period`,
`area`, `approval_date`, `bus_scope`, `industry`, `fwlk_label`, `eht_label`,
`crop_telephone`,`crop_email`,`crop_data_code`,
`insert_time`) values ( %s, %s, %s, %s, %s, %s, %s,%s,%s,
%s, %s,%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"""
self._insert_corp_address_sql = """INSERT INTO stg_corp_address(id,lat,lng,geo_hash,
precise,confidence,insert_time) VALUES (%s, %s, %s, %s, %s, %s, %s);"""
self._insert_corp_contact_sql = """INSERT INTO stg_corp_contact(corp_id,contact_name,contact)
VALUES (%s, %s, %s);"""
def open_spider(self, spider):
# 创建 org_info 表
self.create_corp_info_table()
# 创建 org_address
self.create_corp_address_table()
self.create_corp_contact_table()
def process_item(self, item, spider):
try:
conn = self._pool.connection()
with conn.cursor() as cur:
self.insert_corp_info(item, cur)
conn.commit()
self.insert_corp_address(item, cur)
conn.commit()
self.insert_corp_contact(item, cur)
conn.commit()
conn.close()
except Exception as e:
logger.error("data write db error! data: " + item)
logger.error(repr(e))
return item
# spider 关闭时调用
def close_spider(self, spider):
if self._pool is not None:
self._pool.close()
# 不存在表则创建 stg_corp_info
def create_corp_info_table(self):
create_sql = """CREATE TABLE IF NOT EXISTS `stg_corp_info` (
`id` varchar(32) NOT NULL,
`org_name` varchar(64) DEFAULT NULL,
`org_code` varchar(32) DEFAULT NULL COMMENT '机构代码',
`org_type` varchar(64) DEFAULT NULL COMMENT '企业类型',
`address` varchar(255) DEFAULT NULL COMMENT '企业地址',
`tyshxydm` varchar(64) DEFAULT NULL COMMENT '统一社会信用代码',
`nsrsbh` varchar(64) DEFAULT NULL COMMENT '纳税人识别号',
`reg_number` varchar(64) DEFAULT NULL COMMENT '注册号',
`legal_rep` varchar(16) DEFAULT NULL COMMENT '法定代表人',
`bus_status` varchar(16) DEFAULT NULL COMMENT '经营状态',
`reg_capital` varchar(32) DEFAULT NULL COMMENT '注册资本',
`create_time` date DEFAULT NULL,
`reg_authority` varchar(32) DEFAULT NULL COMMENT '登记机关',
`op_period` varchar(32) DEFAULT NULL COMMENT '经营期限',
`area` varchar(64) DEFAULT NULL COMMENT '所属地区',
`approval_date` varchar(16) DEFAULT NULL COMMENT '核准日期',
`bus_scope` varchar(1024) DEFAULT NULL COMMENT '经营范围',
`industry` varchar(1024) DEFAULT '' COMMENT '所属行业',
`fwlk_label` varchar(512) DEFAULT NULL COMMENT '前瞻标签',
`eht_label` varchar(512) DEFAULT NULL COMMENT '展会标签',
`crop_telephone` varchar(32) DEFAULT NULL COMMENT '企业电话',
`crop_email` varchar(64) DEFAULT NULL COMMENT '企业邮箱',
`crop_data_code` varchar(64) DEFAULT NULL COMMENT '企业联系方式查询代码',
`insert_time` varchar(32) DEFAULT NULL COMMENT '写入时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;"""
conn = self._pool.connection()
with conn.cursor() as cur:
cur.execute(create_sql)
conn.commit()
conn.close()
def create_corp_address_table(self):
create_sql = """CREATE TABLE IF NOT EXISTS `stg_corp_address` (
`id` varchar(32) NOT NULL,
`lat` varchar(32) DEFAULT NULL COMMENT '纬度值',
`lng` varchar(32) DEFAULT NULL COMMENT '经度值',
`geo_hash` varchar(32) DEFAULT NULL COMMENT 'geoHash算法得到的编码串',
`precise` int(11) DEFAULT NULL COMMENT '位置的附加信息，是否精确查找。1为精确查找，即准确打点；0为不精确，即模糊打点',
`confidence` int(11) DEFAULT NULL COMMENT '可信度，描述打点准确度，大于80表示误差小于100m。该字段仅作参考，返回结果准确度主要参考precise参数。',
`insert_time` varchar(32) DEFAULT NULL COMMENT '写入时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;"""
conn = self._pool.connection()
with conn.cursor() as cur:
cur.execute(create_sql)
conn.commit()
conn.close()
def create_corp_contact_table(self):
create_sql = """CREATE TABLE IF NOT EXISTS `stg_corp_contact` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`corp_id` varchar(32) NOT NULL,
`contact_name` varchar(64) DEFAULT NULL COMMENT '联系人姓名',
`contact` varchar(32) DEFAULT NULL COMMENT '机构代码',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;"""
conn = self._pool.connection()
with conn.cursor() as cur:
cur.execute(create_sql)
conn.commit()
conn.close()
# 插入数据 stg_corp_address
def insert_corp_address(self, item, cur):
if item["lat"] == "" or item["lng"] == "":
return
values = (item['id'], item['lat'], item['lng'], item['geo_hash'],
item['precise'], item['confidence'], item['insert_time'])
cur.execute(self._insert_corp_address_sql, values)
# 插入数据 stg_corp_info
def insert_corp_info(self, item, cur):
values = (item['id'], item['org_name'], item['org_code'],
item['org_type'], item['address'], item['tyshxydm'],
item['nsrsbh'], item['reg_number'], item['legal_rep'],
item['bus_status'], item['reg_capital'], item['create_time'],
item['reg_authority'], item['op_period'], item['area'],
item['approval_date'], item['bus_scope'], item['industry'],
item['forward_looking_label'], item['exhibition_label'],
item['crop_telephone'], item['crop_email'],
item['crop_data_code'], item['insert_time'])
cur.execute(self._insert_corp_info_sql, values)
# 插入数据 stg_corp_contact
def insert_corp_contact(self, item, cur):
try:
contacts = item['contacts']
if contacts is None or not isinstance(contacts,
list) or len(contacts) == 0:
return
for contact in contacts:
values = (item['id'], contact["oc_contactName"],
contact['oc_contact'])
cur.execute(self._insert_corp_contact_sql, values)
except Exception as e:
logger.error("write stg_corp_contact table error! data :" +
contacts)
logger.error(repr(e))
"type": "double",
"index": "not_analyzed"
},
"max": {
"type": "double",
"index": "not_analyzed"
},
"discountStr": {
"type": "text"
}
}
}
}
}
es = Elasticsearch()
es.indices.put_mapping(doc_type=args.docType, body=index_mappings, index=args.esIndex)
logging.info("Start Importing")
file = open("./vendor.properties")
line = file.readline()
while line:
v_info = line.replace('\n', '').split(',')
vendors[v_info[0]] = [v_info[1], v_info[2]]
line = file.readline()
import_link(pool1, pool2, es, args.esIndex, args.docType, int(args.productUnit))
t2 = datetime.datetime.now()
logging.info("Import Date Complete, the time this script using is " + str(t2 - t1))
pool1.close()
pool2.close()
class MyPymysqlPool(object):
def __init__(self,
dbhost=conf.get('mysql', 'DB_HOST'),
dbport=conf.getint('mysql', 'DB_PORT'),
dbname=conf.get('mysql', 'DB_NAME'),
dbuser=conf.get('mysql', 'DB_USER'),
dbpasswd=conf.get('mysql', 'DB_PASSWORD'),
charset='utf8',
maxconnections=20):
"""
数据库构造函数，从连接池中取出连接，并生成操作游标
:param dbhost: 数据库地址
:param dbport: 数据库端口
:param dbname: 数据库名称
:param dbuser: 数据库账号
:param dbpasswd: 数据库密码
:param charset: 数据库字符集
:param maxconnections:连接池通常允许的最大连接数，0或None表示任意数量的连接
"""
# mincached 最少的空闲连接数，如果空闲连接数小于这个数，pool会创建一个新的连接,0表示启动时没有连接
# maxcached 最大的空闲连接数，如果空闲连接数大于这个数，pool会关闭空闲连接，0或None表示无限制的池大小
# use_unicode=False, cursorclass=DictCursor（返回字典格式）
# ping:确定何时使用ping()检查连接(0=None=never,1=default=每当从池中获取时,2=创建游标时,4 =执行查询时,7=always)
self.conn = PooledDB(creator=pymysql,
mincached=0,
maxcached=5,
maxconnections=maxconnections,
host=dbhost,
port=dbport,
user=dbuser,
passwd=dbpasswd,
db=dbname,
charset=charset,
ping=4).connection()
self.cursor = self.conn.cursor()
def close_conn(self):
"""
关闭游标 ，关闭连接
"""
# 关闭游标
self.cursor.close()
# 关闭连接
self.conn.close()
def commit_query(self, query):
"""
提交查询语句
:param query: 数据库查询语句
:return: 返回执行行数
"""
# self.conn.ping(reconnect=True)
try:
# 执行sql语句
rows_num = self.cursor.execute(query)
# 提交到数据库执行
self.conn.commit()
num = int(rows_num) if rows_num not in (None, 'NoneType', 'null',
'Null', '', ' ') else 0
LOG.debug("The result rows count is : {}".format(num))
return num
except BaseException as e:
LOG.error("The mysql {0} exec sql error: {1}".format(query, e))
# 发生错误时回滚
self.conn.rollback()
finally:
self.close_conn()
def executed_one(self, query):
"""
查询一条数据
:param query: 数据库查询语句
:return: 返回字符串，默认取第一列第一行
"""
LOG.debug("The mysql exec :" + query)
num = self.commit_query(query)
result = ''
if num > 0:
result = self.cursor.fetchone()[0]
LOG.debug("The result is : {}".format(result))
return result
def executed_all(self, query):
"""
查询所有数据
:param query: 数据库查询语句
:return: 二维数组
"""
LOG.debug("The mysql exec :" + query)
num = self.commit_query(query)
result = list()
if num > 0:
result = [list(fc) for fc in self.cursor.fetchall()]
LOG.debug("The result is : {}".format(result))
return result
def executed_rows(self, query):
"""
获取执行sql的总数
:param query: 数据库查询语句
:return: 返回执行行数
"""
LOG.debug("The mysql exec :" + query)
num = self.commit_query(query)
LOG.debug("The result is :" + str(num))
return num
def executed_many(self, query, value_tuple):
"""
批量执行 SQL语句，用于批量插入或批量更新
:param query: 插入语句/更新语句
:param value_tuple: 二维元组
:return:
"""
try:
# 执行sql语句
rows_num = self.cursor.executemany(query, value_tuple)
# 提交到数据库执行
self.conn.commit()
num = int(rows_num) if rows_num else 0
LOG.debug("The result rows count is : {}".format(num))
return num
except BaseException as e:
LOG.error("The mysql {0} exec sql error: {1}".format(query, e))
# 发生错误时回滚
self.conn.rollback()
def select(self, table, column='*', *condition):
"""
查询所有数据
:param table: 需要查询的表
:param column: 需要查询的列，为空时查询所有， col1,col2
:param condition: 查询条件
:return: 返回数组,元素是字典
"""
query_list = ["SELECT", column, "FROM", table, ";"]
if len(condition) > 0:
query_list.insert(-1, 'WHERE')
query_list.insert(-1, ' AND '.join(condition))
query = ' '.join(query_list)
return self.executed_all(query)
def select_join(self, table, column, equivalent, *condition):
"""
查询有关联的表len(table.split(,)) = len(equivalent) + 1
:param table: 需要join的表，txx1,txx2,txx3
:param column: 需要查询的列名， txx1.col1,txx2.col2,...
:param equivalent: on等式 txx1.col1 = tx2.col2,txx3.col1=txx4.clo3
:param condition: 查询条件 txx.xxcol1="xx" and txx.xxcol2 = "xx"
:return: 二维数组
"""
tables, equivalents = table.split(','), equivalent.split(',')
if len(tables) == (len(equivalents) + 1):
join_sql = list()
for i in range(len(equivalents)):
if i == 0:
join_sql.extend([
tables[i], "JOIN ", tables[i + 1], "ON", equivalents[i]
])
else:
join_sql.extend(
["JOIN ", tables[i + 1], "ON ", equivalents[i]])
join_sql.append(';')
query_list = ["SELECT", column, "FROM"]
query_list.extend(join_sql)
if len(condition) > 0:
query_list.insert(-1, 'WHERE')
query_list.insert(-1, ' AND '.join(condition))
query = ' '.join(query_list)
else:
raise IndexError('table and equivalent the index not equality!')
return self.executed_all(query)
def select_col(self, table, column, *condition):
"""
单独查询某列
:param table: 需要查询的表
:param column: 需要查询的列，只能查询一列
:param condition: 查询条件
:return: 返回数组，展示一列的查询结果
"""
query_list = ["SELECT", column, "FROM", table, ";"]
if len(condition) > 0:
query_list.insert(-1, 'WHERE')
query_list.insert(-1, ' AND '.join(condition))
query = ' '.join(query_list)
return [result[0] for result in self.executed_all(query)]
def select_one(self, table, column, *condition):
"""
单独查询某个值
:param table: 需要查询的表
:param column: 需要查询的列，只能查询一列
:param condition: 查询条件
:return: 返回对应字段值，多条数据时只取第一条
"""
query_list = ["SELECT", column, "FROM", table, "LIMIT 1;"]
if len(condition) > 0:
query_list.insert(-1, 'WHERE')
query_list.insert(-1, ' AND '.join(condition))
query = ' '.join(query_list)
return self.executed_one(query)
def select_rows(self, table, column='*', *condition):
"""
查询执行行数
:param table: 需要查询的表
:param column: 需要查询的列，为空时查询所有， col1,col2
:param condition: 查询条件
:return: 返回查询条数
"""
query_list = ["SELECT", column, "FROM", table, ";"]
if len(condition) > 0:
query_list.insert(-1, 'WHERE')
query_list.insert(-1, ' AND '.join(condition))
query = ' '.join(query_list)
return self.executed_rows(query)
def insert(self, table, value_dict):
"""
插入语句
:param table: 需要插入的表
:param value_dict: 插入信息 {"列名1"：值1，"列名2"：值2}
:return: 返回执行条数
"""
values = ','.join([QUOTATION(value) for value in value_dict.values()])
clos = ','.join(value_dict.keys())
query = ' '.join([
"INSERT", "INTO ", table, "(", clos, ")", "VALUE", "(", values,
")", ";"
])
return self.executed_rows(query)
def insert_many(self, table, col_list, values_tuple):
"""
插入多条语句 'insert into test (sdf,asd,aaa) VALUES ("%s","%s","%s")', ((1,2,3),(2,3,4),(3,4,5))
:param table: 表名
:param col_list: 列名，数组或元组 (sdf,asd,aaa)
:param values_tuple: 插入值，二维元组，二级元组或数组长度必须和列名数组长度一致 ((1,2,3),(2,3,4),(3,4,5))
:return: 执行行数
"""
col_num = len(col_list)
query = ' '.join([
"INSERT", "INTO ", table, "(", ','.join(col_list), ")", "VALUE",
"(", (',%s' * col_num).lstrip(','), ")", ";"
])
LOG.debug("The mysql exec :" + query)
values_list = list()
for value in values_tuple:
if col_num != len(value):
raise IndexError(
'the index of col_list and the index of values_tuple is not equality!'
)
values_list.append(tuple(value))
return self.executed_many(query, values_list)
def update_many(self, table, col_list, values_tuple, col_condition_list):
"""
更新多条语句，元组顺序一致，查询条件在 元组最后
update user_tb set name=%s, sex=%s where id=%s and s_id=%s,
(('小孙', '男'， 2， 1),
('小白', '女'， 3， 2),
('小猪', '男'， 4， 1),
('小牛', '男'， 5， 3),
('小唐', '女'， 6， 2))
:param table: 表名
:param col_list: 列名，数组或元组 (sdf,asd,aaa)
:param values_tuple: 插入值，二维元组，二级元组或数组长度必须和列名数组长度一致 ((1,2,3),(2,3,4),(3,4,5))
:param col_condition_list: 查询条件列名
:return: 执行行数
"""
query_list = list()
query_list.append("UPDATE")
query_list.append(table)
query_list.append("SET")
query_list.append(','.join(
['{}=%s'.format(col_1) for col_1 in col_list]))
if col_condition_list:
query_list.append('WHERE')
query_list.append(' AND '.join(
['{}=%s'.format(col_2) for col_2 in col_condition_list]))
query_list.append(';')
values_list = list()
col = col_list
col.extend(col_condition_list)
col_num = len(col)
for value in values_tuple:
if col_num != len(value):
raise IndexError(
'the index of col_list and the index of values_tuple is not equality!'
)
values_list.append(tuple(value))
query = ' '.join(query_list)
LOG.debug("The mysql exec :" + query)
return self.executed_many(query, values_list)
def insert_update_many(self, table, col_list, values_tuple):
"""
插入或更新多条语句
'insert into test_tbl (id,dr) values (1,'2'),(2,'3'),...(x,'y') on duplicate key update dr=values(dr);
这个语法和适合用在需要判断记录是否存在，不存在则插入存在则更新的记录
:param table: 表名
:param col_list: 列名，数组或元组 (sdf,asd,aaa)
:param values_tuple: 插入值，二维元组，二级元组或数组长度必须和列名数组长度一致 ((1,2,3),(2,3,4),(3,4,5))
:return: 执行行数
"""
col_condition = ','.join(
['{0}=VALUES({0})'.format(t) for t in col_list])
col_num = len(col_list)
for value in values_tuple:
if col_num != len(value):
raise IndexError(
'the index of col_list and the index of values_tuple is not equality!'
)
query = [
"INSERT", "INTO ", table, "(", ','.join(col_list), ")", "VALUE",
','.join(values_tuple), 'ON', 'DUPLICATE', 'KEY', 'UPDATE',
col_condition, ";"
]
self.executed_rows(' '.join(query))
def update_case(self, table, col_list, values_tuple, col_condition,
condition_tuple, *condition):
"""
更新数据库语句
:param table: 需要更新的表
:param col_list: 更新列名列表
:param values_tuple: 更新值二维元组
:param col_condition: CASE列名
:param condition_tuple: CASE值，元组
:param condition: 查询条件
:return: 返回执行条数
"""
query_list = ["UPDATE", table, "SET"]
for values in values_tuple:
if len(col_list) != len(values):
raise IndexError(
'the index of col_list and the index of values_tuple is not equality!'
)
update_value = list()
for i in range(len(col_list)):
case_value = list()
case_value.append('{0} = CASE {1}'.format(col_list[i],
col_condition))
for value in values_tuple:
case_value.append('WHEN {0} THEN {1}'.format(
col_list[i], value[i]))
case_value.append('END')
update_value.append(' '.join(case_value))
query_list.append(','.join(update_value))
query_list.append('WHERE')
query_list.append(col_condition)
query_list.append('IN {}'.format(condition_tuple))
if len(condition) > 0:
query_list.append('AND')
query_list.append(' AND '.join(condition))
query_list.append(';')
query = ' '.join(query_list)
return self.executed_rows(query)
def update(self, table, value, *condition):
"""
更新数据库语句
:param table: 需要更新的表
:param value: 更新值 列名1=值1，列名2=值2
:param condition: 查询条件
:return: 返回执行条数
"""
query_list = ["UPDATE", table, "SET", value, ";"]
if len(condition) > 0:
query_list.insert(-1, 'WHERE')
query_list.insert(-1, ' AND '.join(condition))
query = ' '.join(query_list)
return self.executed_rows(query)
def update_dict(self, table, value_dict, *condition):
"""
更新数据库语句
:param table: 需要更新的表
:param value_dict: 更新值 {"列名1"：值1，"列名2"：值2}
:param condition: 查询条件
:return: 返回执行条数
"""
# 判断value是否需要添加引号
value_list = [
col + '=' + QUOTATION(value) for col, value in value_dict.items()
]
value = ','.join(value_list)
query_list = ["UPDATE", table, "SET", value, ";"]
if len(condition) > 0:
query_list.insert(-1, 'WHERE')
query_list.insert(-1, ' AND '.join(condition))
query = ' '.join(query_list)
return self.executed_rows(query)
def delete(self, table, condition):
"""
删除语句
:param table: 需要删除的表
:param condition: 查询条件
:return: 返回执行条数
"""
query_list = ["DELETE", "FROM", table, "WHERE", condition, ";"]
query = ' '.join(query_list)
return self.executed_rows(query)
def drop_database(self, database_name):
"""
删除数据库
:param database_name: 要删除的数据库名称
:return: 返回执行条数
"""
query_list = ['drop', 'database', database_name, ';']
query = ' '.join(query_list)
return self.executed_rows(query)
class MySQLStorageEngine(StorageEngine):
def __init__(self, host, user, passwd, db, port, charset, sql, threads, mincached, maxcached, bufferSize):
self.begin = time.time()
self.end = time.time()
self.host = host
self.user = user
self.passwd = passwd
self.db = db
self.port = port
self.charset = charset
self.sql = sql
self.threads = threads
self.bufferSize = bufferSize
self.mincached = mincached
self.maxcached = maxcached
self.maxconnections = threads
self.queue = Queue()
self.saveNum = 0
self.saveLock = Lock()
self.storeNum = 0
self.storeLock = Lock()
self.pool = PooledDB(creator = MySQLdb, mincached = self.mincached, maxcached = self.maxcached, maxconnections = self.maxconnections, blocking = True, host = self.host, user = self.user, passwd = self.passwd, db = self.db, port = self.port, charset = self.charset)
self.threadPool = [MySQLSaver(self) for index in range(0, threads)]
for thread in self.threadPool:
thread.start()
def addSaveNum(self, value):
self.saveLock.acquire()
self.saveNum = self.saveNum + value
self.saveLock.release()
def addStoreNum(self, value):
self.storeLock.acquire()
self.storeNum = self.storeNum + value
self.storeLock.release()
def save(self, values):
if values:
if isinstance(values, tuple):
self.queue.put(values)
self.addSaveNum(1)
elif isinstance(values, list):
for value in values:
if isinstance(value, tuple):
self.queue.put(value)
self.addSaveNum(1)
def close(self):
for thread in self.threadPool:
thread.requestStop()
thread.join()
self.pool.close()
self.end = time.time()
logging.info("mysql storage engine save: " + str(self.saveNum) + ", store: " + str(self.storeNum) + ", consume time: " + str(self.end - self.begin) + " s")
class CDBPool:
def __init__(self):
self.m_Pool = None
self.m_bInited = False
def __del__(self):
self.UnInit()
def Init(self, dConfig):
"""
初始化数据连接池
@param dConfig:数据库配置
@return:
"""
if self.m_bInited:
raise Exception("DBPool has inited!")
self.m_Pool = PooledDB(
MySQLdb,
5,
host=dConfig.get("host", "127.0.0.1"),
port=dConfig.get("port", 3306),
user=dConfig.get("user", "root"),
passwd=dConfig["password"],
db=dConfig["db"],
charset=dConfig.get("charset", "latin1"))
self.m_bInited = True
log.Info(u"连接数据库成功")
return True
def UnInit(self):
if not self.m_bInited:
return
if self.m_Pool:
self.m_Pool.close()
self.m_bInited = False
def GetConnection(self):
if not self.m_bInited:
return None
return self.m_Pool.connection()
def ExecSQL(self, sql):
"""
执行一段数据库修改的sql语句，插入、修改、删除等
"""
if (not sql) or (not self.m_bInited):
return
conn = self.GetConnection()
if not conn:
return
try:
cursor = conn.cursor(cursorclass=DictCursor)
cursor.execute(sql)
conn.commit()
log.Debug("ExecSQL:%s"%sql)
except Exception as ex:
log.Error("ExecSQL(%s),error:%s" % (sql, ex.args))
finally:
cursor.close()
conn.close()
def QuerySQL(self, sql):
"""
执行一段查询sql语句
"""
if (not sql) or (not self.m_bInited):
return None
conn = self.GetConnection()
ret = []
if not conn:
return None
try:
cursor = conn.cursor(cursorclass=DictCursor)
cursor.execute(sql)
ret = cursor.fetchall()
log.Debug("QuerySQL:%s"%sql)
except Exception as ex:
log.Error("QuerySQL(%s),error:%s" % (sql, ex.args))
finally:
cursor.close()
conn.close()
return ret
