def __init__(self, config): try: self.DataName = config['datatype'] del config['datatype'] except: self.DataName = 'MYSQL' if self.DataName == 'MYSQL': try: self.pool = mysql.connector.pooling.MySQLConnectionPool( **config) self.cnx = self.cur = None except mysql.connector.Error as err: # 这里需要记录操作日志 logging.debug(err.msg) self.cnx = None raise BaseError(701) # 与数据库连接异常 elif self.DataName == 'POSTGRESQL': try: self.pool = SimpleConnectionPool(**config) except: raise BaseError(701) # 与数据库连接异常 elif self.DataName == 'ORACLE': try: if config['NLS_LANG']: os.environ['NLS_LANG'] = config['NLS_LANG'] del config['NLS_LANG'] except: pass try: self.pool = cx_Oracle.SessionPool(**config) except: raise BaseError(701) # 与数据库连接异常
class xDB(object): def __init__(self): self.pgpool = SimpleConnectionPool(minconn=1, maxconn=10, host='localhost', database='p2pworld', user='******', password='******', port=5432) self._db = None self.trans = {} def __del__(self): self.pgpool.closeall() #执行查询,返回结果list def Query(self, sqlstr): rv = {} try: db = self.pgpool.getconn() db.autocommit = True cur = db.cursor(cursor_factory=RealDictCursor) cur.execute(sqlstr) _fetch = cur.fetchall() if len(_fetch) == 0: rv['error'] = 800 rv['result'] = 'result NULL' return rv rv['error'] = 0 rv['result'] = _fetch except Exception, e: rv['error'] = 800 rv['result'] = 'ERR: %s' % (repr(e)) finally:
def __init__(self, gp_host, gp_port, gp_dbname, gp_user, password, minconn=1, maxconn=5, multithreading=True): self.host = gp_host self.port = gp_port self.dbname = gp_dbname self.user = gp_user self.password = password if multithreading: # 可用在多线程应用程序中 self.gp_pool = ThreadedConnectionPool(minconn, maxconn, host=gp_host, port=gp_port, dbname=gp_dbname, user=gp_user, password=password) else: # 仅用于单线程应用程序中 self.gp_pool = SimpleConnectionPool(minconn, maxconn, host=gp_host, port=gp_port, dbname=gp_dbname, user=gp_user, password=password)
def __init__(self): self.pool = SimpleConnectionPool(1, 10, dbname=os.environ['INFRABOX_DATABASE_DB'], user=os.environ['INFRABOX_DATABASE_USER'], password=os.environ['INFRABOX_DATABASE_PASSWORD'], host=os.environ['INFRABOX_DATABASE_HOST'], port=os.environ['INFRABOX_DATABASE_PORT'])
class PGDbHelper(object): def __init__(self, conn_str, echo=False): self.echo = echo self.pool = SimpleConnectionPool(1, 12, conn_str) def finish(self): self.pool.closeall() @contextmanager def _get_cursor(self): conn = self.pool.getconn() # conn.autocommit = True conn.set_isolation_level(0) try: if self.echo: cur = conn.cursor(cursor_factory=LoggingCursor) else: cur = conn.cursor(cursor_factory=DictCursor) yield cur conn.commit() conn.close() finally: self.pool.putconn(conn) def insert(self, sql_string, value): try: with self._get_cursor() as cur: cur.execute(sql_string, value) except Exception, ex: logger.debug("Error while executing %s" % sql_string) logger.debug(traceback.print_exc()) raise ex
def get_conn(self, key=None): if key: if not self.pool: self.pool = SimpleConnectionPool(**config.PostgresqlDbConfig) self.conn = self.pool.getconn(key) else: self.conn = connect(**config_conn) self.conn.autocommit = False
def __init__(self): self.db_conn_pool = SimpleConnectionPool(2, 3, host=DST_DB_HOST, port=int(DST_DB_PORT), user=DST_DB_USER, password=DST_DB_PASSWORD, database=DST_DB_DATABASE)
def __init__(self, config): self.logger = logging.getLogger('postgres_connector') try: self.pool = SimpleConnectionPool(1, 10, **config) self.schema = 'entity_lookup' except Exception as ex: self.logger.exception( 'Exception occurred while connecting to the database') raise ex
def get_conn(): global POOL if not POOL: POOL = SimpleConnectionPool(POOL_SIZE, POOL_SIZE, "") conn = POOL.getconn() try: yield conn finally: POOL.putconn(conn)
def __init__(self, dbconfig): self.dbconfig = dbconfig self.db = SimpleConnectionPool(minconn=self.dbconfig['minconn'], maxconn=self.dbconfig['maxconn'], database=self.dbconfig['dbname'], user=self.dbconfig['username'], host=self.dbconfig['host'], port=self.dbconfig['port'], password=self.dbconfig['password'])
def __init__(self, conn_num=3): self.__init_logger() self.log.info("Creating pool") self.conn_num = conn_num self.conn = psycopg2.connect(self.LOCALHOST_STING) self.cur = self.conn.cursor() self.pool = SimpleConnectionPool(self.conn_num, self.conn_num + 5, self.LOCALHOST_STING) self.register_adapters() self.create_table(False)
def __init__(self, username: str, password: str, public=False): self.cp = SimpleConnectionPool(1, 5, user="******", password="******", host="sh.wtd2.top", port="5432", database="12306") if not public: self.password_check(username, password.encode('utf-8')) self.passenger_info()
class PostgresPoolWrapper: def __init__(self, postgres_dsn: str, min_connections: int = int(os.environ["MIN_DB_CONNECTIONS"]), max_connections: int = int(os.environ["MAX_DB_CONNECTIONS"])): self.postgres_pool: Optional[SimpleConnectionPool] = None self.postgres_dsn = postgres_dsn self.min_connections = min_connections self.max_connections = max_connections def init(self): """ Connects to the database and initializes connection pool """ if self.postgres_pool is not None: return try: self.postgres_pool = SimpleConnectionPool( self.min_connections, self.max_connections, self.postgres_dsn, cursor_factory=RealDictCursor) if self.postgres_pool is None: raise Exception("Unknown error") except (Exception, psycopg2.DatabaseError) as e: print(f"Failed to create Postgres connection pool: {e}") def get_conn(self) -> Iterator[RealDictConnection]: """ Yields a connection from the connection pool and returns the connection to the pool after the yield completes """ if self.postgres_pool is None: raise Exception( "Cannot get db connection before connecting to database") conn: RealDictConnection = self.postgres_pool.getconn() if conn is None: raise Exception( "Failed to get connection from Postgres connection pool") yield conn self.postgres_pool.putconn(conn) def cleanup(self): """ Closes all connections in the connection pool """ if self.postgres_pool is None: return self.postgres_pool.closeall()
def connect(self): """ Initialize database connection pool """ try: self.conn_pool = SimpleConnectionPool(minconn=1, maxconn=self.pool_max_size, user=self.user, password=self.password, host=self.host, port=self.port, database=self.database) except (Exception, psycopg2.Error) as error: logger.fatal(f'Error connecting to PostgreSQL: {error}')
def __init__(self): try: self.connectPool = SimpleConnectionPool(2, 20, host=POSTGRE_HOST, port=POSTGRE_PORT, user=POSTGRE_USER, password=POSTGRE_PASSWORD, database=POSTGRE_DATABASE, keepalives=1, keepalives_idle=30, keepalives_interval=10, keepalives_count=5) except Exception as e: print(e)
async def main(): pool = SimpleConnectionPool(1, 10, dbConnection) conn = pool.getconn() cursor = conn.cursor() async for orderbook in poll(): try: orderbook_pair = orderbook.get('result', {}).get(pair, {}) for item in orderbook_pair['bids']: print(item) print() for item in orderbook_pair['asks']: print(item) finally: conn.close()
def connect_to_db(self): db_name = self.db_name cp = configparser.ConfigParser() cp.read(self.config_file) password = cp.get(db_name, "password") user = cp.get(db_name, "user") database = cp.get(db_name, "database") host = cp.get(db_name, "host") port = cp.get(db_name, "port") kwargs = {"host":host,"password":password, "user":user,"dbname":database, "port":port} self.conn_pool = SimpleConnectionPool(1, 3, **kwargs)
def postgres_demo(request): global pg_pool # Initialize the pool lazily, in case SQL access isn't needed for this # GCF instance. Doing so minimizes the number of active SQL connections, # which helps keep your GCF instances under SQL connection limits. if not pg_pool: pg_pool = SimpleConnectionPool(1, 1, **pg_config) # Remember to close SQL resources declared while running this function. # Keep any declared in global scope (e.g. pg_pool) for later reuse. with pg_pool.getconn().cursor() as cursor: cursor.execute('SELECT NOW() as now') results = cursor.fetchone() return str(results[0])
def create_pool(): global pool if pool is None: pool = SimpleConnectionPool(app.config["MINCONN"], app.config["MAXCONN"], dsn=get_db_connection_string()) return pool
def __connect(host): """ Helper function to connect to Postgres """ global PG_POOL PG_CONFIG['host'] = host PG_POOL = SimpleConnectionPool(1, 1, **PG_CONFIG)
def api_app(config_class): """ Initialize main app object.""" app = Flask(__name__) app.config.from_object(config_class) app.logger.info(f'Creating API server with {app.config["ENV"]}') CORS(app) app.before_request_funcs = {None: [token_auth, db_connection]} app.teardown_appcontext_funcs = [shutdown_session] # for key, value in exception_handlers.items(): # app.register_error_handler(key, value) from rpserver.rider import rider_bp from rpserver.spot import spot_bp from rpserver.event import event_bp app.register_blueprint(rider_bp, url_prefix='/riders') app.register_blueprint(spot_bp, url_prefix='/spots') app.register_blueprint(event_bp, url_prefix='/events') app.db_connection_pool = SimpleConnectionPool( app.config['DB_CONNECTIONS_MIN'], app.config['DB_CONNECTIONS_MAX'], app.config['DB_SERVER_URI'], cursor_factory=DictCursor) return app
class db: params = config() # str = connect(**params) str_1 = SimpleConnectionPool(minconn=1,maxconn=5,**params) str = str_1.getconn() def __init__(self): self._conn = connect(**params) self._cur= self._conn.cursor() @property def connection(self): return self._conn @property def cursor(self): return self._cur def commit(self): self.connection.commit() def execute(self,sql): return self.cursor.execute(sql) def fetchall(self): return self.cursor.fetchall() def query(self, sql): self.cursor.execute(sql) return self.fetchall()
def openConn(): global conn global cur # conn = psycopg2.connect(database="huhula", user="******", host="roachdb", port=26257) # cs="postgresql://huhuladb00:26257/mydb?user=root&sslcert=/Users/ivasilchikov/spot/certs/client.root.crt&sslkey=/Users/ivasilchikov/spot/certs/client.root.key&sslmode=require&ssl=true" cs = "postgresql://huhuladb00,huhuladb01,huhuladb02:26257/huhula?user=huhulaman&sslcert=/home/ubuntu/spot/certs/client.huhulaman.crt&sslkey=/home/ubuntu/spot/certs/client.huhulaman.key&sslmode=require&ssl=true" # conn = psycopg2.connect(cs) global g_pool g_pool = SimpleConnectionPool(3, 7, cs) con = g_pool.getconn() con.set_session(autocommit=True) cur = con.cursor() cur.execute("show databases;") res = cur.fetchall() print "res=" + str(res)
class InfraBoxPostgresPlugin(object): name = 'ibpostgres' def __init__(self): self.pool = SimpleConnectionPool(1, 10, dbname=os.environ['INFRABOX_DATABASE_DB'], user=os.environ['INFRABOX_DATABASE_USER'], password=os.environ['INFRABOX_DATABASE_PASSWORD'], host=os.environ['INFRABOX_DATABASE_HOST'], port=os.environ['INFRABOX_DATABASE_PORT']) def apply(self, callback, context): # Test if the original callback accepts a 'conn' keyword. # Ignore it if it does not need a database connection. args = inspect.getargspec(context['callback'])[0] if 'conn' not in args: return callback def wrapper(*args, **kwargs): # Connect to the database conn = None try: conn = self.pool.getconn() except HTTPResponse, e: raise HTTPError(500, "Database Error", e) # Add the connection handle as a keyword argument. kwargs['conn'] = conn try: rv = callback(*args, **kwargs) except HTTPError, e: raise except HTTPResponse, e: raise
def __connect(host): """ Helper function to connect to Postgres """ global pg_pool pg_config['host'] = host pg_pool = SimpleConnectionPool(1, 1, **pg_config)
def __init__(self, database, host=None, port=None, user=None, password=None, client_encoding="utf8", minconn=1, maxconn=5, **kwargs): self.host = "%s:%s" % (host, port) _db_args = dict( async=True, database=database, client_encoding=client_encoding, **kwargs ) if host is not None: _db_args["host"] = host if port is not None: _db_args["port"] = port if user is not None: _db_args["user"] = user if password is not None: _db_args["password"] = password try: self._pool = SimpleConnectionPool( minconn=minconn, maxconn=maxconn, **_db_args) except Exception: logging.error("Cannot connect to PostgreSQL on %s", self.host, exc_info=True)
def __pg_pool(cls): """ ConnectionPool for Postgres governed by psycopg2. :return: """ if cls.__pg_connection_pool is None: try: connection_dialect = cls.__connection_dialects['postgresql'] dsn = "dbname='{}' user='******' host='{}' password='******' port='{}'".format( connection_dialect['database'], connection_dialect['user'], connection_dialect['host'], connection_dialect['password'], connection_dialect['port']) # connection_pool with 25 live connections. Tweak this according to convenience. cls.__pg_connection_pool = SimpleConnectionPool(1, 25, dsn=dsn) cls.connection_manager_logger.info( '[connection_manager]: PG Pool class method is invoked for first time. ' 'PG Pool will be initialized for Postgres engine of PROTON.' ) except Exception as e: cls.connection_manager_logger.info( '[connection_manager]: Error creating a PG Pool. Stack trace to follow.' ) cls.connection_manager_logger.exception(str(e)) else: cls.connection_manager_logger.info( '[connection_manager]: Request for PG Pool method is invoked subsequently. ' 'PG Pool previously initialized for all PROTON supported engines is returned.' ) return cls.__pg_connection_pool
def __init__(self,config): try : self.DataName=config['datatype'] del config['datatype'] except: self.DataName='MYSQL' if self.DataName == 'MYSQL' : try: self.pool = mysql.connector.pooling.MySQLConnectionPool(**config) self.cnx=self.cur=None except mysql.connector.Error as err: # 这里需要记录操作日志 logging.debug(err.msg) self.cnx=None raise BaseError(701) # 与数据库连接异常 elif self.DataName == 'POSTGRESQL' : try : self.pool = SimpleConnectionPool(**config) except: raise BaseError(701) # 与数据库连接异常 elif self.DataName == 'ORACLE' : try : if config['NLS_LANG'] : os.environ['NLS_LANG']=config['NLS_LANG'] del config['NLS_LANG'] except: pass try : self.pool = cx_Oracle.SessionPool(**config) except : raise BaseError(701) # 与数据库连接异常
def pool(): try: params = dbpoolconfig() pool = SimpleConnectionPool(**params) print("!!!!!!!!!!!! pool() !!!!!!!!!!!!") return pool except (Exception, psycopg2.DatabaseError) as error: print(error)
def create_pool(): global pool if pool is None: _preparedb() pool = SimpleConnectionPool(app.config["MINCONN"], app.config["MAXCONN"], dsn=db_connection_string) return pool
def connect_pool(self,host,port,user,password,database,schema='public'): try: options = f'-c search_path={schema}' pool = SimpleConnectionPool( minconn=1, maxconn=1, host=host, port=port, user=user, password=password, database=database, options=options ) self._client = pool.getconn() except Exception as e: print('error:',repr(e)) raise e
def init(self): """ Connects to the database and initializes connection pool """ if self.postgres_pool is not None: return try: self.postgres_pool = SimpleConnectionPool( self.min_connections, self.max_connections, self.postgres_dsn, cursor_factory=RealDictCursor) if self.postgres_pool is None: raise Exception("Unknown error") except (Exception, psycopg2.DatabaseError) as e: print(f"Failed to create Postgres connection pool: {e}")
def initialize(cls, **kwargs): cls.__pool = SimpleConnectionPool(1, 20, user = db_user, password = db_password, host = db_host, port = db_port, database = db_database) print("database conn initialize")
def __init__(self, dbconfig): self.dbconfig = dbconfig self.db = SimpleConnectionPool( minconn=self.dbconfig['minconn'], maxconn=self.dbconfig['maxconn'], database=self.dbconfig['dbname'], user=self.dbconfig['username'], host=self.dbconfig['host'], port=self.dbconfig['port'], password=self.dbconfig['password'])
class Datastore(object): """ the datastore interface """ def __init__(self, dbname='web', dbuser='******', dbpassw='Klofcumad1'): self.db = dbname self.dbuser = dbuser self.dbpassw = dbpassw self.pool = SimpleConnectionPool(1, 100, database=dbname, user=dbuser, password=dbpassw) def start_op(self): conn = self.pool.getconn() cur = conn.cursor() return (conn, cur) def close_op(self, conn): conn.commit() self.pool.putconn(conn) def find_user(self, id=None, email=None): assert(id != None or email != None) conn, cur = self.start_op() if id != None: cur.execute("SELECT * FROM users WHERE id=%s", [id]) if email != None: cur.execute("SELECT * FROM users WHERE email=%s", [email]) row = cur.fetchone() print(row) if row != None: return User(id=row[1], email=row[0], passw=row[2]) return None def add_user(self, email, password): assert(email != None and password != None) conn, cur = self.start_op() id = uuid() cur.execute("INSERT INTO users (id, email, password) VALUES (%s, %s, %s)", [str(id), email, generate_password_hash(password)]) self.close_op(conn) return id
class DBService: def __init__(self, dbconfig): self.dbconfig = dbconfig self.db = SimpleConnectionPool( minconn=self.dbconfig['minconn'], maxconn=self.dbconfig['maxconn'], database=self.dbconfig['dbname'], user=self.dbconfig['username'], host=self.dbconfig['host'], port=self.dbconfig['port'], password=self.dbconfig['password']) @contextmanager def get_cursor(self): con = self.db.getconn() try: yield con.cursor() con.commit() finally: self.db.putconn(con) def update(self, sql): with self.get_cursor() as cursor: logging.debug(u'Executing sql: [{0}]'.format(sql.strip())) cursor.execute(sql) def query(self, sql, rowCallback): with self.get_cursor() as cursor: logging.debug(u'Executing sql: [{0}]'.format(sql.strip())) cursor.execute(sql) for row in cursor: rowCallback(row) def query_single(self, sql): with self.get_cursor() as cursor: logging.debug(u'Executing sql: [{0}]'.format(sql.strip())) cursor.execute(sql) yield cursor.fetchone()
def __init__(self): self.pgpool = SimpleConnectionPool(minconn=1, maxconn=10, host='localhost', database='p2pworld', user='******', password='******', port=5432) self._db = None self.trans = {}
host = parsed.hostname port = parsed.port if port is None: port = '5432' # postgres default port dsn = "dbname={} host={} port={}".format(dbname, host, port) if user: dsn += ' username={}'.format(user) if password: dsn += ' password={}'.format(password) return dsn if __name__ == "__main__": # Getting dsn from console arguments # postgres://user:password@localhost:5432/test_erp if 'postgres' not in urlparse.uses_netloc: # Teach urlparse about postgres:// URLs. urlparse.uses_netloc.append('postgres') if len(sys.argv) > 1: conn_string = url_to_dsn(sys.argv[1]) else: conn_string = url_to_dsn("postgres://localhost:5432/test_erp") # creating pool pool = SimpleConnectionPool(1, 5, dsn=conn_string) for i in xrange(1,6): print "Question {}:\n\r{}".format(i, getattr(sys.modules[__name__], 'question{}'.format(i)).__doc__) conn = pool.getconn() print getattr(sys.modules[__name__], 'question{}'.format(i))(conn) pool.putconn(conn) print "="*20 pool.closeall()
class Connection(object): """""" def __init__(self, database, host=None, port=None, user=None, password=None, client_encoding="utf8", minconn=1, maxconn=5, **kwargs): self.host = "%s:%s" % (host, port) _db_args = dict( async=True, database=database, client_encoding=client_encoding, **kwargs ) if host is not None: _db_args["host"] = host if port is not None: _db_args["port"] = port if user is not None: _db_args["user"] = user if password is not None: _db_args["password"] = password try: self._pool = SimpleConnectionPool( minconn=minconn, maxconn=maxconn, **_db_args) except Exception: logging.error("Cannot connect to PostgreSQL on %s", self.host, exc_info=True) def __del__(self): self._pool.closeall() def _connect(self, callback=None): """Get an existing database connection.""" conn = self._pool.getconn() callback = functools.partial(callback, conn) Poller(conn, (callback, ))._update_handler() @gen.coroutine def _cursor(self): conn = yield gen.Task(self._connect) cursor = conn.cursor() raise gen.Return(cursor) def putconn(self, conn, close=False): self._pool.putconn(conn, close=close) @gen.coroutine def query(self, query, parameters=()): """Returns a row list for the given query and parameters.""" cursor = yield self._cursor() try: yield gen.Task(self._execute, cursor, query, parameters) column_names = [d[0] for d in cursor.description] raise gen.Return([Row(zip(column_names, row)) for row in cursor]) finally: self.putconn(cursor.connection) cursor.close() @gen.coroutine def get(self, query, parameters=()): """Returns the (singular) row returned by the given query. If the query has no results, returns None. If it has more than one result, raises an exception. """ rows = yield self.query(query, parameters) if not rows: raise gen.Return(None) elif len(rows) > 1: raise Exception("Multiple rows returned for Database.get() query") else: raise gen.Return(rows[0]) @gen.coroutine def execute(self, query, parameters=()): """Executes the given query.""" cursor = yield self._cursor() try: yield gen.Task(self._execute, cursor, query, parameters) finally: self.putconn(cursor.connection) cursor.close() def _execute(self, cursor, query, parameters, callback=None): if not isinstance(parameters, (tuple, list)): raise try: cursor.execute(query, parameters) Poller(cursor.connection, (callback,))._update_handler() except psycopg2.OperationalError: logging.error("Error connecting to PostgreSQL on %s", self.host) self.putconn(cursor.connection, close=True)
def __init__(self, dbname='web', dbuser='******', dbpassw='Klofcumad1'): self.db = dbname self.dbuser = dbuser self.dbpassw = dbpassw self.pool = SimpleConnectionPool(1, 100, database=dbname, user=dbuser, password=dbpassw)
class DB(object): def __init__(self,config): try : self.DataName=config['datatype'] del config['datatype'] except: self.DataName='MYSQL' if self.DataName == 'MYSQL' : try: self.pool = mysql.connector.pooling.MySQLConnectionPool(**config) self.cnx=self.cur=None except mysql.connector.Error as err: # 这里需要记录操作日志 logging.debug(err.msg) self.cnx=None raise BaseError(701) # 与数据库连接异常 elif self.DataName == 'POSTGRESQL' : try : self.pool = SimpleConnectionPool(**config) except: raise BaseError(701) # 与数据库连接异常 elif self.DataName == 'ORACLE' : try : if config['NLS_LANG'] : os.environ['NLS_LANG']=config['NLS_LANG'] del config['NLS_LANG'] except: pass try : self.pool = cx_Oracle.SessionPool(**config) except : raise BaseError(701) # 与数据库连接异常 def open(self): try : if self.DataName=='ORACLE' : self.__conn = self.pool.acquire() self.__cursor = self.__conn.cursor() elif self.DataName=='POSTGRESQL' : self.__conn = self.pool.getconn() self.__cursor = self.__conn.cursor() else : # 默认为Mysql self.__conn = self.pool.get_connection() self.__cursor = self.__conn.cursor(buffered=True) #self.__conn.autocommit=True self.__conn.autocommit=False self.cnx=self.__conn self.cur=self.__cursor except : raise BaseError(702) # 无法获得连接池 def close(self): #关闭游标和数据库连接 self.__conn.commit() if self.__cursor is not None: self.__cursor.close() if self.DataName == 'POSTGRESQL' : self.pool.putconn(self.__conn)#将数据库连接放回连接池中 else : self.__conn.close() def begin(self): self.__conn.autocommit=False def commit(self): self.__conn.commit() def rollback(self): self.__conn.rollback() #--------------------------------------------------------------------------- def findBySql(self,sql,params = {},limit = 0,join = 'AND',lock=False): """ 自定义sql语句查找 limit = 是否需要返回多少行 params = dict(field=value) join = 'AND | OR' """ try : cursor = self.__getCursor() sql = self.__joinWhere(sql,params,join) cursor.execute(sql,tuple(params.values())) rows = cursor.fetchmany(size=limit) if limit > 0 else cursor.fetchall() result = [dict(zip(cursor.column_names,row)) for row in rows] if rows else None return result except: raise BaseError(706) def countBySql(self,sql,params = {},join = 'AND'): # 自定义sql 统计影响行数 try: cursor = self.__getCursor() sql = self.__joinWhere(sql,params,join) cursor.execute(sql,tuple(params.values())) result = cursor.fetchone(); return result[0] if result else 0 except: raise BaseError(707) #def updateByPk(self,table,data,id,pk='id'): # # 根据主键更新,默认是id为主键 # return self.updateByAttr(table,data,{pk:id}) def deleteByAttr(self,table,params={},join='AND'): # 删除数据 try : fields = ','.join(k+'=%s' for k in params.keys()) sql = "DELETE FROM `%s` "% table sql = self.__joinWhere(sql,params,join) cursor = self.__getCursor() cursor.execute(sql,tuple(params.values())) self.__conn.commit() return cursor.rowcount #except: # raise BaseError(704) except Exception as err: raise BaseError(704,err._full_msg) def deleteByPk(self,table,id,pk='id'): # 根据主键删除,默认是id为主键 return self.deleteByAttr(table,{pk:id}) def findByAttr(self,table,criteria = {}): # 根据条件查找一条记录 return self.__query(table,criteria) def findByPk(self,table,id,pk='id'): return self.findByAttr(table,{'where':pk+'='+str(id)}) def findAllByAttr(self,table,criteria={}): # 根据条件查找记录 return self.__query(table,criteria,True) def exit(self,table,params={},join='AND'): # 判断是否存在 return self.count(table,params,join) > 0 # 公共的方法 ------------------------------------------------------------------------------------- def count(self,table,params={},join='AND'): # 根据条件统计行数 try : sql = 'SELECT COUNT(*) FROM %s' % table if params : where ,whereValues = self.__contact_where(params) sqlWhere= ' WHERE '+where if where else '' sql+=sqlWhere #sql = self.__joinWhere(sql,params,join) cursor = self.__getCursor() self.__display_Debug_IO(sql,tuple(whereValues)) #DEBUG if self.DataName=='ORACLE': cursor.execute(sql % tuple(whereValues)) else : cursor.execute(sql,tuple(whereValues)) #cursor.execute(sql,tuple(params.values())) result = cursor.fetchone(); return result[0] if result else 0 #except: # raise BaseError(707) except Exception as err: try : raise BaseError(707,err._full_msg) except : raise BaseError(707) def getToListByPk(self,table,criteria={},id=None,pk='id'): # 根据条件查找记录返回List if ('where' not in criteria) and (id is not None) : criteria['where']=pk+ "='" + str(id) + "'" return self.__query(table,criteria,isDict=False) def getAllToList(self,table,criteria={},id=None,pk='id',join='AND'): # 根据条件查找记录返回List if ('where' not in criteria) and (id is not None) : criteria['where']=pk+ "='" + str(id) + "'" return self.__query(table,criteria,all=True,isDict=False) def getToObjectByPk(self,table,criteria={},id=None,pk='id'): # 根据条件查找记录返回Object if ('where' not in criteria) and (id is not None) : criteria['where']=pk+"='"+str(id)+"'" return self.__query(table,criteria) def getAllToObject(self,table,criteria={},id=None,pk='id',join='AND'): # 根据条件查找记录返回Object if ('where' not in criteria) and (id is not None) : criteria['where']=pk+"='"+str(id)+"'" return self.__query(table,criteria,all=True) def insert(self,table,data,commit=True): # 新增一条记录 try : ''' 从data中分离含用SQL函数的字字段到funData字典中, 不含SQL函数的字段到newData ''' funData,newData=self.__split_expression(data) funFields='';funValues='' # 拼不含SQL函数的字段及值 fields = ','.join(k for k in newData.keys()) values = ','.join(("%s", ) * len(newData)) # 拼含SQL函数的字段及值 if funData : funFields = ','.join(k for k in funData.keys()) funValues =','.join( v for v in funData.values()) # 合并所有字段及值 fields += ','+funFields if funFields else '' values += ','+funValues if funValues else '' sql = 'INSERT INTO %s (%s) VALUES (%s)'%(table,fields,values) cursor = self.__getCursor() for (k,v) in newData.items() : try: if isinstance(v, str) : newData[k]="'%s'" % (v,) except : pass self.__display_Debug_IO(sql,tuple(newData.values())) #DEBUG sql= sql % tuple(newData.values()) if self.DataName=='POSTGRESQL' : sql+=' RETURNING id' cursor.execute(sql) #if self.DataName=='ORACLE': #sql= sql % tuple(newData.values()) #cursor.execute(sql) #else : #cursor.execute(sql,tuple(newData.values())) if self.DataName=='ORACLE': # 1. commit 一定要为假 # 2. Oracle Sequence 的命名规范为: [用户名.]SEQ_表名_ID # 3. 每张主表都应该有ID t_list=table.split('.') if len(t_list)>1 : SEQ_Name= t_list[0]+'.SEQ_'+t_list[1]+'_ID' else : SEQ_Name='SEQ_'+t_list[0]+'_ID' cursor.execute('SELECT %s.CURRVAL FROM dual' % SEQ_Name.upper()) result = cursor.fetchone() insert_id= result[0] if result else 0 #insert_id=cursor.rowcount elif self.DataName=='MYSQL' : insert_id = cursor.lastrowid elif self.DataName=='POSTGRESQL': item = cursor.fetchone() insert_id = item[0] if commit : self.commit() return insert_id except Exception as err: try : raise BaseError(705,err._full_msg) except : raise BaseError(705,err.args) def update(self,table,data,params={},join='AND',commit=True,lock=True): # 更新数据 try : fields,values = self.__contact_fields(data) if params : where ,whereValues = self.__contact_where(params) values.extend(whereValues) if whereValues else values sqlWhere= ' WHERE '+where if where else '' cursor = self.__getCursor() if commit : self.begin() if lock : sqlSelect="SELECT %s From %s %s for update" % (','.join(tuple(list(params.keys()))),table,sqlWhere) sqlSelect=sqlSelect % tuple(whereValues) cursor.execute(sqlSelect) # 加行锁 #cursor.execute(sqlSelect,tuple(whereValues)) # 加行锁 sqlUpdate = "UPDATE %s SET %s "% (table,fields) + sqlWhere for index,val in enumerate(values): if isinstance(val,str) : values[index]="'"+val+"'" self.__display_Debug_IO(sqlUpdate,tuple(values)) #DEBUG sqlUpdate = sqlUpdate % tuple(values) cursor.execute(sqlUpdate) #cursor.execute(sqlUpdate,tuple(values)) if commit : self.commit() return cursor.rowcount except Exception as err: try : raise BaseError(705,err._full_msg) except : raise BaseError(705,err.args) def updateByPk(self,table,data,id,pk='id',commit=True,lock=True): # 根据主键更新,默认是id为主键 return self.update(table,data,{pk:id},commit=commit,lock=lock) def delete(self,table,params={},join='AND',commit=True,lock=True): # 更新数据 try : data={} fields,values = self.__contact_fields(data) if params : where ,whereValues = self.__contact_where(params) values.extend(whereValues) if whereValues else values sqlWhere= ' WHERE '+where if where else '' cursor = self.__getCursor() if commit : self.begin() #if lock : #sqlSelect="SELECT %s From %s %s for update" % (','.join(tuple(list(params.keys()))),table,sqlWhere) #sqlSelect=sqlSelect % tuple(whereValues) #cursor.execute(sqlSelect) # 加行锁 ##cursor.execute(sqlSelect,tuple(whereValues)) # 加行锁 sqlDelete = "DELETE FROM %s %s"% (table,sqlWhere) for index,val in enumerate(values): if isinstance(val,str) : values[index]="'"+val+"'" self.__display_Debug_IO(sqlDelete,tuple(values)) #DEBUG sqlDelete = sqlDelete % tuple(values) cursor.execute(sqlDelete) #cursor.execute(sqlUpdate,tuple(values)) if commit : self.commit() return cursor.rowcount except Exception as err: try : raise BaseError(705,err._full_msg) except : raise BaseError(705,err.args) def deleteByPk(self,table,id,pk='id',commit=True,lock=True): # 根据主键更新,默认是id为主键 return self.delete(table,{pk:id},commit=commit,lock=lock) # 内部私有的方法 ------------------------------------------------------------------------------------- def __display_Debug_IO(self,sql,params) : if DEBUG : debug_now_time = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S') print('[S '+debug_now_time+' SQL:] '+(sql % params) if params else sql) def __get_connection(self): return self.pool.get_connection() def __getCursor(self): """获取游标""" if self.__cursor is None: self.__cursor = self.__conn.cursor() return self.__cursor def getCursor(self): """获取游标""" if self.__cursor is None: self.__cursor = self.__conn.cursor() return self.__cursor def __joinWhere(self,sql,params,join): # 转换params为where连接语句 if params: funParams={};newParams={};newWhere='';funWhere='' # 从params中分离含用SQL函数的字字段到Params字典中 for (k,v) in params.items(): if 'str' in str(type(v)) and '{{' == v[:2] and '}}'==v[-2:] : funParams[k]=v[2:-2] else: newParams[k]=v # 拼 newParams 条件 keys,_keys = self.__tParams(newParams) newWhere = ' AND '.join(k+'='+_k for k,_k in zip(keys,_keys)) if join == 'AND' else ' OR '.join(k+'='+_k for k,_k in zip(keys,_keys)) # 拼 funParams 条件 if funParams : funWhere = ' AND '.join(k+'='+v for k,v in funParams.items()) if join == 'AND' else ' OR '.join(k+'='+v for k,v in funParams.items()) # 拼最终的 where where=((newWhere+' AND ' if newWhere else '')+funWhere if funWhere else newWhere) if join=='AND' else ((newWhere+' OR ' if newWhere else '')+funWhere if funWhere else newWhere) #-------------------------------------- #keys,_keys = self.__tParams(params) #where = ' AND '.join(k+'='+_k for k,_k in zip(keys,_keys)) if join == 'AND' else ' OR '.join(k+'='+_k for k,_k in zip(keys,_keys)) sql+=' WHERE ' + where return sql def __tParams(self,params): keys = [k if k[:2]!='{{' else k[2:-2] for k in params.keys()] _keys = ['%s' for k in params.keys()] return keys,_keys def __query(self,table,criteria,all=False,isDict=True,join='AND'): ''' table : 表名 criteria : 查询条件dict all : 是否返回所有数据,默认为False只返回一条数据,当为真是返回所有数据 isDict : 返回格式是否为字典,默认为True ,即字典否则为数组 ''' try : if all is not True: criteria['limit'] = 1 # 只输出一条 sql,params = self.__contact_sql(table,criteria,join) #拼sql及params ''' # 当Where为多个查询条件时,拼查询条件 key 的 valuse 值 if 'where' in criteria and 'dict' in str(type(criteria['where'])) : params = criteria['where'] #params = tuple(params.values()) where ,whereValues = self.__contact_where(params) sql+= ' WHERE '+where if where else '' params=tuple(whereValues) else : params = None ''' #__contact_where(params,join='AND') cursor = self.__getCursor() self.__display_Debug_IO(sql,params) #DEBUG #if self.DataName=="ORACLE": #sql="select * from(select * from(select t.*,row_number() over(order by %s) as rownumber from(%s) t) p where p.rownumber>%s) where rownum<=%s" % () #pass cursor.execute(sql,params if params else ()) rows = cursor.fetchall() if all else cursor.fetchone() if isDict : result = [dict(zip(cursor.column_names,row)) for row in rows] if all else dict(zip(cursor.column_names,rows)) if rows else {} else : result = [row for row in rows] if all else rows if rows else [] return result except Exception as err: try : raise BaseError(706,err._full_msg) except : raise BaseError(706) def __contact_sql(self,table,criteria,join='AND'): sql = 'SELECT ' if criteria and type(criteria) is dict: #select fields if 'select' in criteria: fields = criteria['select'].split(',') sql+= ','.join(field.strip()[2:-2] if '{{' == field.strip()[:2] and '}}'==field.strip()[-2:] else field.strip() for field in fields) else: sql+=' * ' #table sql+=' FROM %s' % table #where whereValues=None if 'where' in criteria: if 'str' in str(type(criteria['where'])) : # 当值为String时,即单一Key时 sql+=' WHERE '+ criteria['where'] else : # 当值为dict时,即一组key时 params=criteria['where'] #sql+= self.__joinWhere('',params,join) #sql+=self.__contact_where(params,join) where ,whereValues = self.__contact_where(params) sql+= ' WHERE '+where if where else '' #sql=sql % tuple(whereValues) #group by if 'group' in criteria: sql+=' GROUP BY '+ criteria['group'] #having if 'having' in criteria: sql+=' HAVING '+ criteria['having'] if self.DataName=='MYSQL' : #order by if 'order' in criteria: sql+=' ORDER BY '+ criteria['order'] #limit if 'limit' in criteria: sql+=' LIMIT '+ str(criteria['limit']) #offset if 'offset' in criteria: sql+=' OFFSET '+ str(criteria['offset']) elif (self.DataName=='POSTGRESQL') : #order by if 'order' in criteria: sql+=' ORDER BY '+ criteria['order'] if 'limit' in criteria : # 取 offset,rowcount arrLimit=(str(criteria['limit']).split('limit ').pop()).split(',') strOffset = arrLimit[0] try : strRowcount = arrLimit[1] except : strOffset = '0' strRowcount = '1' sql+=' LIMIT %s OFFSET %s' %(strRowcount,strOffset) elif (self.DataName=='ORACLE') and ('limit' in criteria) : # 取 offset,rowcount arrLimit=(str(criteria['limit']).split('limit ').pop()).split(',') strOffset = arrLimit[0] try : strRowcount = arrLimit[1] except : strOffset = '0' strRowcount = '1' # 处理 order by if 'order' in criteria : strOrder = criteria['order'] else : strOrder = 'ROWNUM' # 以下Sql是针对 Oracle 的大数据查询效率 sql="select * from(select * from(select t.*,row_number() over(order by %s) as rownumber from(%s) t) p where p.rownumber>%s) where rownum<=%s" % (strOrder,sql,strOffset,strRowcount) elif (self.DataName=='ORACLE') and ('order' in criteria) : sql+=' ORDER BY '+ criteria['order'] else: sql+=' * FROM %s' % table return sql,whereValues # 将字符串和表达式分离 def __split_expression(self,data) : funData={};newData={};funFields='' # 从data中移出含用SQL函数的字字段到funData字典中 for (k,v) in data.items(): if 'str' in str(type(v)) and '{{' == v[:2] and '}}'==v[-2:] : funData[k]=v[2:-2] else : newData[k]=v return (funData,newData) # 拼Update字段 def __contact_fields(self,data) : funData,newData=self.__split_expression(data) if funData : funFields = ','.join(k+'=%s' % (v) for k,v in funData.items()) fields = ','.join(k+'=%s' for k in newData.keys()) # fields 与 funFields 合并 if funData : fields = ','.join([fields,funFields]) if fields else funFields values = list(newData.values()) return (fields,values) def __hasKeyword(self,key) : if '{{}}' in key : return True if 'in (' in key : return True if 'like ' in key : return True if '>' in key : return True if '<' in key : return True return False # 拼Where条件 def __contact_where(self,params,join='AND') : funParams,newParams=self.__split_expression(params) # 拼 newParams 条件 keys,_keys = self.__tParams(newParams) newWhere = ' AND '.join(k+'='+_k for k,_k in zip(keys,_keys)) if join == 'AND' else ' OR '.join(k+'='+_k for k,_k in zip(keys,_keys)) values = list(newParams.values()) # 拼 funParams 条件 #funWhere = ' AND '.join(('`' if k else '') +k+('`' if k else '')+ (' ' if self.__hasKeyword(v) else '=') +v for k,v in funParams.items()) if join == 'AND' else ' OR '.join('`'+k+'`'+(' ' if self.__hasKeyword(v) else '=')+v for k,v in funParams.items()) funWhere = ' AND '.join(k+ (' ' if self.__hasKeyword(v) else '=' if k else '') +v for k,v in funParams.items()) if join == 'AND' else ' OR '.join(k+(' ' if self.__hasKeyword(v) else '=' if k else '')+v for k,v in funParams.items()) # 拼最终的 where where=((newWhere+' AND ' if newWhere else '')+funWhere if funWhere else newWhere) if join=='AND' else ((newWhere+' OR ' if newWhere else '')+funWhere if funWhere else newWhere) return (where,values) def get_ids(self,list): #从getAllToList返回中提取id try: test=list[0][0] dimension=2 except: dimension=1 ids=[] if dimension>1 : for i in range(len(list)) : ids.append(str(list[i][0])) else : for i in range(len(list)) : ids.append(str(list[i])) return ','.join(ids)