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