Python ThreadedConnectionPool.getconn方法代码示例

# 需要导入模块: from psycopg2.pool import ThreadedConnectionPool [as 别名]
# 或者: from psycopg2.pool.ThreadedConnectionPool import getconn [as 别名]
	def getconn(self):
		"""
		Gets connection from parent class, enables AUTOCOMMIT and returns requested connection.

		@rtype: object
		@return: connection with isolation level set to autocommit
		"""
		#calledBy = traceback.extract_stack()[-2]
		#logging.info("GETCONN - FILE: " + calledBy[0] + ", LINE: " + str(calledBy[1]) + ", METHOD: " + calledBy[2])

		conn = ThreadedConnectionPool.getconn(self)

		try:
			#conn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
			conn.cursor().execute("SELECT 1")

		except (psycopg2.OperationalError, psycopg2.InterfaceError, psycopg2.InternalError):
			key = self._rused[id(conn)]
			del self._rused[id(conn)]
			conn = psycopg2.connect(self.dsn)
			self._rused[id(conn)] = key

		if Config.hstoreEnabled == True:
			try:
				psycopg2.extras.register_hstore(conn)
			except Exception, e:
				Config.hstoreEnabled = False

# 需要导入模块: from psycopg2.pool import ThreadedConnectionPool [as 别名]
# 或者: from psycopg2.pool.ThreadedConnectionPool import getconn [as 别名]
 def _connect(self):
     global _CONNECTION_POOL
     if _CONNECTION_POOL is None:
         _CONNECTION_POOL = ThreadedConnectionPool(
             config.DB_MIN_CONNECTIONS, config.DB_MAX_CONNECTIONS,
             **config.DB_PARAMS)
     if self._connection is not None:
         raise RuntimeError("Connection still exists.")
     self._connection = _CONNECTION_POOL.getconn()
     self._connection.set_session(autocommit=True)

# 需要导入模块: from psycopg2.pool import ThreadedConnectionPool [as 别名]
# 或者: from psycopg2.pool.ThreadedConnectionPool import getconn [as 别名]
class Database:
    def __init__(self, connect_param):
        self.__connect_param = connect_param
        self.__pool = ThreadedConnectionPool(0, 10, self.__connect_param)
        # get cursor and test it
        # cur = self.cursor()
        # cur.execute('SHOW transaction_read_only')
        # standby = cur.fetchone()
        # cur.close()

    def get_connection(self):
        return self.__pool.getconn()

    def put_connection(self, connection):
        self.__pool.putconn(connection)

# 需要导入模块: from psycopg2.pool import ThreadedConnectionPool [as 别名]
# 或者: from psycopg2.pool.ThreadedConnectionPool import getconn [as 别名]
class Database():
    def __init__(self, config):
        logging.basicConfig(format='%(asctime)s %(levelname)s %(message)s', level=logging.INFO)

        self._pool = ThreadedConnectionPool(1, 10, 
                database=config['DB_DATABASE'],
                user=config['DB_USER'],
                password=config['DB_PASSWORD'],
                host=config['DB_HOST'],
                async=False)

    def get_connection(self):
        return self._pool.getconn()

    def put_away_connection(self, con):
        self._pool.putconn(con)

# 需要导入模块: from psycopg2.pool import ThreadedConnectionPool [as 别名]
# 或者: from psycopg2.pool.ThreadedConnectionPool import getconn [as 别名]
class DB:
    def __init__(self, *args, **kwargs):
        self.pool_params = (args, kwargs)
        self.pool = None

        self.campaigns = Campaigns(self)
        self.worksets = Worksets(self)
        self.tasks = Tasks(self)
        self.labels = Labels(self)
        self.logger = logging.getLogger(__name__)

    def _initialize_pool(self):
        if self.pool is None:
            logger.info("Initializing connection pool.")
            args, kwargs = self.pool_params
            self.pool = ThreadedConnectionPool(
                *args, cursor_factory=RealDictCursor, **kwargs)

    def execute(self, sql):
        with self.transaction() as transactor:
            cursor = transactor.cursor()
            cursor.execute(sql)
            return cursor

    @contextmanager
    def transaction(self):
        """Provides a transactional scope around a series of operations."""
        self._initialize_pool()
        conn = self.pool.getconn()
        try:
            yield conn
            conn.commit()
        except:
            conn.rollback()
            raise
        finally:
            self.pool.putconn(conn)

    @classmethod
    def from_config(cls, config):
        # Copy config as kwargs
        params = {k: v for k, v in config['database'].items()}
        params['minconn'] = params.get('minconn', 1)
        params['maxconn'] = params.get('maxconn', 5)

        return cls(**params)

# 需要导入模块: from psycopg2.pool import ThreadedConnectionPool [as 别名]
# 或者: from psycopg2.pool.ThreadedConnectionPool import getconn [as 别名]
class PostgresThreadPool:
    provides = ['db_connection_pool', 'postgres']
    requires_configured = ['json_settings']

    def __init__(self, settings):
        from psycopg2.pool import ThreadedConnectionPool
        dbsettings = settings['database']
        self.pool = ThreadedConnectionPool(
            minconn=1,
            maxconn=settings['database']['conn_pool_size'],
            database=dbsettings['name'],
            user=dbsettings['username'],
            password=dbsettings['password'],
            host=dbsettings['host'],
            port=dbsettings.get('port')
        )

    def getconn(self):
        return self.pool.getconn()

    def putconn(self, connection):
        return self.pool.putconn(connection)

# 需要导入模块: from psycopg2.pool import ThreadedConnectionPool [as 别名]
# 或者: from psycopg2.pool.ThreadedConnectionPool import getconn [as 别名]
class ConnectionPool(object):
    def __init__(self, conn_params, minconn=5, maxconn=5):
        self._conn_params = conn_params.copy()
        self._conn_params['minconn'] = minconn
        self._conn_params['maxconn'] = maxconn
        self._conn_pool = None

    def initialize(self):
        self._conn_pool = ThreadedConnectionPool(**self._conn_params)

    @contextmanager
    def cursor(self):
        conn = self._conn_pool.getconn()
        cursor = conn.cursor()
        try:
            yield cursor
            conn.commit()
        except Exception:
            conn.rollback()
            raise
        finally:
            self._conn_pool.putconn(conn)

# 需要导入模块: from psycopg2.pool import ThreadedConnectionPool [as 别名]
# 或者: from psycopg2.pool.ThreadedConnectionPool import getconn [as 别名]
class Database(DatabaseInterface):

    _databases = {}
    _connpool = None
    _list_cache = None
    _list_cache_timestamp = None
    _version_cache = {}

    def __new__(cls, database_name="template1"):
        if database_name in cls._databases:
            return cls._databases[database_name]
        return DatabaseInterface.__new__(cls, database_name=database_name)

    def __init__(self, database_name="template1"):
        super(Database, self).__init__(database_name=database_name)
        self._databases.setdefault(database_name, self)

    def connect(self):
        if self._connpool is not None:
            return self
        logger = logging.getLogger("database")
        logger.info('connect to "%s"' % self.database_name)
        host = CONFIG["db_host"] and "host=%s" % CONFIG["db_host"] or ""
        port = CONFIG["db_port"] and "port=%s" % CONFIG["db_port"] or ""
        name = "dbname=%s" % self.database_name
        user = CONFIG["db_user"] and "user=%s" % CONFIG["db_user"] or ""
        password = CONFIG["db_password"] and "password=%s" % CONFIG["db_password"] or ""
        minconn = int(CONFIG["db_minconn"]) or 1
        maxconn = int(CONFIG["db_maxconn"]) or 64
        dsn = "%s %s %s %s %s" % (host, port, name, user, password)
        self._connpool = ThreadedConnectionPool(minconn, maxconn, dsn)
        return self

    def cursor(self, autocommit=False, readonly=False):
        if self._connpool is None:
            self.connect()
        conn = self._connpool.getconn()
        if autocommit:
            conn.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)
        else:
            conn.set_isolation_level(ISOLATION_LEVEL_REPEATABLE_READ)
        cursor = Cursor(self._connpool, conn, self)
        if readonly:
            cursor.execute("SET TRANSACTION READ ONLY")
        return cursor

    def close(self):
        if self._connpool is None:
            return
        self._connpool.closeall()
        self._connpool = None

    def create(self, cursor, database_name):
        cursor.execute('CREATE DATABASE "' + database_name + '" ' "TEMPLATE template0 ENCODING 'unicode'")
        Database._list_cache = None

    def drop(self, cursor, database_name):
        cursor.execute('DROP DATABASE "' + database_name + '"')
        Database._list_cache = None

    def get_version(self, cursor):
        if self.database_name not in self._version_cache:
            cursor.execute("SELECT version()")
            version, = cursor.fetchone()
            self._version_cache[self.database_name] = tuple(map(int, RE_VERSION.search(version).groups()))
        return self._version_cache[self.database_name]

    @staticmethod
    def dump(database_name):
        from trytond.tools import exec_pg_command_pipe

        cmd = ["pg_dump", "--format=c", "--no-owner"]
        if CONFIG["db_user"]:
            cmd.append("--username=" + CONFIG["db_user"])
        if CONFIG["db_host"]:
            cmd.append("--host=" + CONFIG["db_host"])
        if CONFIG["db_port"]:
            cmd.append("--port=" + CONFIG["db_port"])
        cmd.append(database_name)

        pipe = exec_pg_command_pipe(*tuple(cmd))
        pipe.stdin.close()
        data = pipe.stdout.read()
        res = pipe.wait()
        if res:
            raise Exception("Couldn't dump database!")
        return data

    @staticmethod
    def restore(database_name, data):
        from trytond.tools import exec_pg_command_pipe

        database = Database().connect()
        cursor = database.cursor(autocommit=True)
        database.create(cursor, database_name)
        cursor.commit()
        cursor.close()

        cmd = ["pg_restore", "--no-owner"]
        if CONFIG["db_user"]:
#.........这里部分代码省略.........

# 需要导入模块: from psycopg2.pool import ThreadedConnectionPool [as 别名]
# 或者: from psycopg2.pool.ThreadedConnectionPool import getconn [as 别名]
class FlaskWithPool(Flask):
    """ Add some PGPool operations so we can reset from a request context when the DB is restarted on us """
    def __init__(self, name):
        Flask.__init__(self, name)
        self.resetlock = threading.Lock()
        self.pool = None

    def db_prepare(self):
        """ Check if we have the series in the URL, set the schema path if available, return an error message otherwise """
        if hasattr(g, 'db'):
            raise EnvironmentError('Database has already been prepared.  Preparing again is an error.')

        g.db = self._get_from_pool()
        if hasattr(g, 'series') and g.series:
            # Set up the schema path if we have a series
            g.seriestype = Series.type(g.series)
            if g.seriestype == Series.INVALID:
                abort(404, "%s is not a valid series" % g.series)
            with g.db.cursor() as cur:
                cur.execute("SET search_path=%s,'public'; commit; begin", (g.series,))
        else:
            g.seriestype = Series.UNKNOWN

    def db_return(self):
        """ Return a connection to the pool and clear the attribute """
        if hasattr(g, 'db'):
            self.pool.putconn(g.db) 
            del g.db # Removes 'db' from g dictionary

    def _reset_pool(self):
        """ First person here gets to reset it, others can continue on and try again """
        if self.resetlock.acquire(False):
            try:
                if self.pool and not self.pool.closed:
                    self.pool.closeall()
                # Make sure the basic database is present and create a PG connection pool
                connkeys = { 'host':self.config['DBHOST'], 'port':self.config['DBPORT'], 'user':'postgres' }
                ensure_database_created(connkeys)
                ensure_public_schema(connkeys)
                # Create a new pool of connections.  Server should support 100, leave 10 for applications
                self.pool = ThreadedConnectionPool(5, 80, cursor_factory=DictCursor, application_name="webserver", dbname="scorekeeper",
                                                         host=self.config['DBHOST'], port=self.config['DBPORT'], user=self.config['DBUSER'])
            except Exception as e:
                log.error("Error in pool create/reset: %s", str(e))
            finally:
                self.resetlock.release()

    def _get_from_pool(self):
        """ Get a database connection from the pool and make sure its connected, attempt reset once if needed """
        try:
            ret = self.pool.getconn() 
            with ret.cursor() as cur:
                cur.execute("select 1")
        except (DatabaseError, OperationalError) as e:
            log.warning("Possible database restart.  Reseting pool and trying again!")
            try: 
                self._reset_pool()
                ret = self.pool.getconn() 
                with ret.cursor() as cur:
                    cur.execute("select 1")
            except (DatabaseError, OperationalError) as e:
                raise EnvironmentError("Errors with postgresql connection pool.  Bailing")

        #log.debug("{} setup: {} connections used".format(threading.current_thread(), len(self.pool._used)))
        return ret

# 需要导入模块: from psycopg2.pool import ThreadedConnectionPool [as 别名]
# 或者: from psycopg2.pool.ThreadedConnectionPool import getconn [as 别名]
class Database(rigor.database.Database):
	""" Container for a database connection pool """

	def __init__(self, database):
		super(Database, self).__init__(database)
		register_type(psycopg2.extensions.UNICODE)
		register_uuid()
		dsn = Database.build_dsn(database)
		self._pool = ThreadedConnectionPool(config.get('database', 'min_database_connections'), config.get('database', 'max_database_connections'), dsn)

	@staticmethod
	def build_dsn(database):
		""" Builds the database connection string from config values """
		dsn = "dbname='{0}' host='{1}'".format(database, config.get('database', 'host'))
		try:
			ssl = config.getboolean('database', 'ssl')
			if ssl:
				dsn += " sslmode='require'"
		except ConfigParser.Error:
			pass
		try:
			username = config.get('database', 'username')
			dsn += " user='{0}'".format(username)
		except ConfigParser.Error:
			pass
		try:
			password = config.get('database', 'password')
			dsn += " password='{0}'".format(password)
		except ConfigParser.Error:
			pass
		return dsn

	@staticmethod
	@template
	def create(name):
		""" Creates a new database with the given name """
		return "CREATE DATABASE {0};".format(name)

	@staticmethod
	@template
	def drop(name):
		""" Drops the database with the given name """
		return "DROP DATABASE {0};".format(name)

	@staticmethod
	@template
	def clone(source, destination):
		"""
		Copies the source database to a new destination database.  This may fail if
		the source database is in active use.
		"""
		return "CREATE DATABASE {0} WITH TEMPLATE {1};".format(destination, source)

	@contextmanager
	def get_cursor(self, commit=True):
		""" Gets a cursor from a connection in the pool """
		connection = self._pool.getconn()
		cursor = connection.cursor(cursor_factory=RigorCursor)
		try:
			yield cursor
		except psycopg2.IntegrityError as error:
			exc_info = sys.exc_info()
			self.rollback(cursor)
			raise rigor.database.IntegrityError, exc_info[1], exc_info[2]
		except psycopg2.DatabaseError as error:
			exc_info = sys.exc_info()
			self.rollback(cursor)
			raise rigor.database.DatabaseError, exc_info[1], exc_info[2]
		except:
			exc_info = sys.exc_info()
			self.rollback(cursor)
			raise exc_info[0], exc_info[1], exc_info[2]
		else:
			if commit:
				self.commit(cursor)
			else:
				self.rollback(cursor)

	def _close_cursor(self, cursor):
		""" Closes a cursor and releases the connection to the pool """
		cursor.close()
		self._pool.putconn(cursor.connection)

	def commit(self, cursor):
		""" Commits the transaction, then closes the cursor """
		cursor.connection.commit()
		self._close_cursor(cursor)

	def rollback(self, cursor):
		""" Rolls back the transaction, then closes the cursor """
		cursor.connection.rollback()
		self._close_cursor(cursor)

	def __del__(self):
		self._pool.closeall()

# 需要导入模块: from psycopg2.pool import ThreadedConnectionPool [as 别名]
# 或者: from psycopg2.pool.ThreadedConnectionPool import getconn [as 别名]
class DbPool(object):
	"""DB class that makes connection transparently. Thread-safe - every
	thread get its own database connection. Not meant to be used directly,
	there is no reason to have more than one instance - global variable Db
	- in this module."""

	def __init__(self, config):
		"""Configures the Db, connection is not created yet.
		@param config: instance of config.NotaryServerConfig."""

		self.host = config.db_host
		self.port = config.db_port
		self.user = config.db_user
		self.password = config.db_password
		self.db_name = config.db_name
		self.min_connections = config.db_min_conn
		self.max_connections = config.db_max_conn

		self.pool = ThreadedConnectionPool(
			minconn = self.min_connections,
			maxconn = self.max_connections,
			host = self.host,
			port = self.port,
			user = self.user,
			password = self.password,
			database = self.db_name)

	def cursor(self, **kwargs):
		"""Creates and returns cursor for current thread's connection.
		Cursor is a "dict" cursor, so you can access the columns by
		names (not just indices), e.g.:

		cursor.execute("SELECT id, name FROM ... WHERE ...", sql_args)
		row = cursor.fetchone()
		id = row['id']
		
		Server-side cursors (named cursors) should be closed explicitly.
		
		@param kwargs: currently string parameter 'name' is supported.
		Named cursors are for server-side cursors, which
		are useful when fetching result of a large query via fetchmany()
		method. See http://initd.org/psycopg/docs/usage.html#server-side-cursors
		"""
		return self.connection().cursor(cursor_factory=DictCursor, **kwargs)
	
	def connection(self):
		"""Return connection for this thread"""
		return self.pool.getconn(id(threading.current_thread()))

	def commit(self):
		"""Commit all the commands in this transaction in this thread's
		connection. If errors (e.g. duplicate key) arose, this will
		cause transaction rollback.
		"""
		self.connection().commit()

	def rollback(self):
		"""Rollback last transaction on this thread's connection"""
		self.connection().rollback()
	
	def	putconn(self):
		"""Put back connection used by this thread. Necessary upon finishing of
		spawned threads, otherwise new threads won't get connection if the pool
		is depleted."""
		conn = self.connection()
		self.pool.putconn(conn, id(threading.current_thread()))
	
	def close(self):
		"""Close connection."""
		self.connection().close()

# 需要导入模块: from psycopg2.pool import ThreadedConnectionPool [as 别名]
# 或者: from psycopg2.pool.ThreadedConnectionPool import getconn [as 别名]
class DatabaseManager:
    """
    This class provides abstraction over underlying database.
    """
    
    def __init__(self, db_name="test_db", db_pass="", host="127.0.0.1" , port="5432"):
        self.connection_pool = ThreadedConnectionPool(10, 50, database=db_name, user="postgres", \
                                                       password=db_pass, host=host, port=port)
        self.logger = get_logger()
        

    def __execute_query(self, query):
        connection = self.connection_pool.getconn()
        cursor = connection.cursor()
        self.logger.debug("Going to execute query {}".format(query))
        try:
            cursor.execute(query)
        except ProgrammingError:
            self.logger.error("Error occurred while executing query {}".format(query))
        except IntegrityError:
            self.logger.error("Query failed. Duplicate row for query {}".format(query))
        finally:
            connection.commit()
            self.connection_pool.putconn(connection)

    """
    Inserts multiple rows in table_name. column_headers contain tuple of table headers.
    rows contain the list of tuples where each tuple has values for each rows. The values in 
    tuple are ordered according to column_headers tuple.
    """
    def insert_batch(self, table_name, column_headers, rows):
        query = "INSERT INTO {} {} VALUES {}".format(table_name, '(' + ','.join(column_headers) + ')', str(rows)[1:-1])
        self.__execute_query(query)

    """
    Updates a row(uid) with new values from column_vs_value dict.
    """
    def update(self, table_name, column_vs_value, uid):
        update_str = ''.join('{}={},'.format(key, val) for key, val in column_vs_value.items())[:-1]
        query = "UPDATE {} SET {} WHERE id = {} ".format(table_name, update_str, uid) 
        self.__execute_query(query)
    
    """
    Deletes all rows from table_name with uids. uids is a tuple.
    """
    def delete_batch(self, table_name , uids, uid_column_name='id'):
        query = "DELETE from {} WHERE {} in {}".format(table_name, uid_column_name, str(uids))
        self.__execute_query(query)
    
    """
    Returns the dict a row by uid.
    """
    def get_row(self, table_name, uid, uid_column_name='id'):
        query = "Select * from {} where {} = {}".format(table_name, uid_column_name, uid)
        connection = self.connection_pool.getconn()
        cursor = connection.cursor()
        cursor.execute(query)
        column_names = [desc[0] for desc in cursor.description]
        values = cursor.fetchall()
        result = {}
        if len(values) > 0:
            for x, y in itertools.izip(column_names, values[0]):
                result[x] = y
        self.connection_pool.putconn(connection)
        return result
    
    """
    Returns all distinct values of column_name from table_name.
    """
    def get_all_values_for_attr(self, table_name, column_name):
        query = "Select distinct {} from {}".format(column_name, table_name)
        connection = self.connection_pool.getconn()
        cursor = connection.cursor()
        cursor.execute(query)
        rows = cursor.fetchall()
        uids = [row[0] for row in rows]
        self.connection_pool.putconn(connection)
        return uids
    
    """
    Returns all rows from table_name satisfying where_clause. The number of returned rows are limited to 
    limit.
    """
    def get_all_rows(self, table_name, where_clause='1=1', limit=20, order_by=None):
        query = "Select * from {} where {} ".format(table_name, where_clause)
        if order_by:
            query = '{} order by {} desc'.format(query, order_by)
        query = '{} limit {}'.format(query, limit)
        connection = self.connection_pool.getconn()
        cursor = connection.cursor()
        cursor.execute(query)
        column_names = [desc[0] for desc in cursor.description]
        rows = cursor.fetchall()
        result = []
        for row in rows:
            result_row = {}
            for x, y in itertools.izip(column_names, row):
                result_row[x] = str(y)
            result.append(result_row)
        self.connection_pool.putconn(connection)
#.........这里部分代码省略.........

# 需要导入模块: from psycopg2.pool import ThreadedConnectionPool [as 别名]
# 或者: from psycopg2.pool.ThreadedConnectionPool import getconn [as 别名]
class Database:

    def __init__(self, host, port, dbname, dbuser, dbpass, minconn=1, maxconn=1):
        # Thread pool
        self.pool = ThreadedConnectionPool(
            minconn=minconn,
            maxconn=maxconn,
            host=host,
            database=dbname,
            user=dbuser,
            password=dbpass,
            port=port
        )
        # Base connection for initialization
        self.conn = psycopg2.connect(
            host=host,
            database=dbname,
            user=dbuser,
            password=dbpass,
            port=port
        )
        self.curs = self.conn.cursor()

    def initialize(self):
        # Initialize Database, Recreate Tables
        try:
            self.curs.execute("""CREATE TABLE Users (
                                user_id text, balance bigint)""")
        except:
            self.conn.rollback()
            self.curs.execute("""DROP TABLE Users""")
            self.curs.execute("""CREATE TABLE Users (
                                    user_id text, balance bigint)""")
        self.conn.commit()

        try:
            self.curs.execute("""CREATE TABLE Stock (
                                    stock_id text, user_id text, amount bigint)""")
        except:
            self.conn.rollback()
            self.curs.execute("""DROP TABLE Stock""")
            self.curs.execute("""CREATE TABLE Stock (
                                    stock_id text, user_id text, amount bigint)""")
        self.conn.commit()

        try:
            self.curs.execute("""CREATE TABLE PendingTrans (
                                    type text, user_id text, stock_id text, amount bigint, timestamp bigint)""")
        except:
            self.conn.rollback()
            self.curs.execute("""DROP TABLE PendingTrans""")
            self.curs.execute("""CREATE TABLE PendingTrans (
                                    type text, user_id text, stock_id text, amount bigint, timestamp bigint)""")
        self.conn.commit()

        try:
            self.curs.execute("""CREATE TABLE Trigger (
                                    type text, user_id text, stock_id text, amount bigint, trigger bigint)""")
        except:
            self.conn.rollback()
            self.curs.execute("""DROP TABLE Trigger""")
            self.curs.execute("""CREATE TABLE Trigger (
                                    type text, user_id text, stock_id text, amount bigint, trigger bigint)""")
        self.conn.commit()

        print "DB Initialized"

    # Return a Database Connection from the pool
    def get_connection(self):
        connection = self.pool.getconn()
        cursor = connection.cursor()
        return connection, cursor

    def close_connection(self, connection):
        self.pool.putconn(connection)

        # call like: select_record("Users", "id,balance", "id='jim' AND balance=200")
    def select_record(self, values, table, constraints):
        connection, cursor = self.get_connection()

        try:
            command = """SELECT %s FROM %s WHERE %s""" % (values, table, constraints)
            cursor.execute(command)
            connection.commit()
        except Exception as e:
            print 'PG Select error - ' + str(e)

        result = cursor.fetchall()
        self.close_connection(connection)

        # Format to always return a tuple of the single record, with each value.
        if len(result) > 1:
            print 'PG Select returned more than one value.'
            return (None,None)
        elif len(result) == 0:
            return (None,None)
        else:
            return result[0]

    def filter_records(self, values, table, constraints):
#.........这里部分代码省略.........

# 需要导入模块: from psycopg2.pool import ThreadedConnectionPool [as 别名]
# 或者: from psycopg2.pool.ThreadedConnectionPool import getconn [as 别名]
class Database(DatabaseInterface):

    _databases = {}
    _connpool = None
    _list_cache = None
    _list_cache_timestamp = None
    _version_cache = {}
    flavor = Flavor(ilike=True)

    def __new__(cls, database_name='template1'):
        if database_name in cls._databases:
            return cls._databases[database_name]
        return DatabaseInterface.__new__(cls, database_name=database_name)

    def __init__(self, database_name='template1'):
        super(Database, self).__init__(database_name=database_name)
        self._databases.setdefault(database_name, self)

    def connect(self):
        if self._connpool is not None:
            return self
        logger.info('connect to "%s"', self.database_name)
        uri = parse_uri(config.get('database', 'uri'))
        assert uri.scheme == 'postgresql'
        host = uri.hostname and "host=%s" % uri.hostname or ''
        port = uri.port and "port=%s" % uri.port or ''
        name = "dbname=%s" % self.database_name
        user = uri.username and "user=%s" % uri.username or ''
        password = ("password=%s" % urllib.unquote_plus(uri.password)
            if uri.password else '')
        minconn = config.getint('database', 'minconn', default=1)
        maxconn = config.getint('database', 'maxconn', default=64)
        dsn = '%s %s %s %s %s' % (host, port, name, user, password)
        self._connpool = ThreadedConnectionPool(minconn, maxconn, dsn)
        return self

    def cursor(self, autocommit=False, readonly=False):
        if self._connpool is None:
            self.connect()
        conn = self._connpool.getconn()
        if autocommit:
            conn.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)
        else:
            conn.set_isolation_level(ISOLATION_LEVEL_REPEATABLE_READ)
        cursor = Cursor(self._connpool, conn, self)
        if readonly:
            cursor.execute('SET TRANSACTION READ ONLY')
        return cursor

    def close(self):
        if self._connpool is None:
            return
        self._connpool.closeall()
        self._connpool = None

    @classmethod
    def create(cls, cursor, database_name):
        cursor.execute('CREATE DATABASE "' + database_name + '" '
            'TEMPLATE template0 ENCODING \'unicode\'')
        cls._list_cache = None

    @classmethod
    def drop(cls, cursor, database_name):
        cursor.execute('DROP DATABASE "' + database_name + '"')
        cls._list_cache = None

    def get_version(self, cursor):
        if self.database_name not in self._version_cache:
            cursor.execute('SELECT version()')
            version, = cursor.fetchone()
            self._version_cache[self.database_name] = tuple(map(int,
                RE_VERSION.search(version).groups()))
        return self._version_cache[self.database_name]

    @staticmethod
    def dump(database_name):
        from trytond.tools import exec_command_pipe

        cmd = ['pg_dump', '--format=c', '--no-owner']
        env = {}
        uri = parse_uri(config.get('database', 'uri'))
        if uri.username:
            cmd.append('--username=' + uri.username)
        if uri.hostname:
            cmd.append('--host=' + uri.hostname)
        if uri.port:
            cmd.append('--port=' + str(uri.port))
        if uri.password:
            # if db_password is set in configuration we should pass
            # an environment variable PGPASSWORD to our subprocess
            # see libpg documentation
            env['PGPASSWORD'] = uri.password
        cmd.append(database_name)

        pipe = exec_command_pipe(*tuple(cmd), env=env)
        pipe.stdin.close()
        data = pipe.stdout.read()
        res = pipe.wait()
        if res:
            raise Exception('Couldn\'t dump database!')
#.........这里部分代码省略.........

# 需要导入模块: from psycopg2.pool import ThreadedConnectionPool [as 别名]
# 或者: from psycopg2.pool.ThreadedConnectionPool import getconn [as 别名]
class PgPool:
    def __init__(self):
        logger.debug('initializing postgres threaded pool')
        self.host, self.port = None, None
        self.database, self.pool = None, None
        self.user, self.passwd = None, None
        self.pool = None

        logger.debug('Server Addr: {host}:{port}; Database: {db}; User: {user}; Password: {passwd}'.format(
            host=self.host, port=self.port,
            db=self.database, user=self.user, passwd=self.passwd
        ))

    def create_pool(self, conn_dict, limits):
        """
        Create a connection pool

        :param conn_dict: connection params dictionary
        :type conn_dict: dict
        """
        if conn_dict["Host"] is None:
            self.host = 'localhost'
        else:
            self.host = conn_dict["Host"]
        if conn_dict["Port"] is None:
            self.port = '5432'
        else:
            self.port = conn_dict["Port"]

        self.database = conn_dict["Database"]
        self.user = conn_dict["User"]
        self.passwd = conn_dict["Password"]

        conn_params = "host='{host}' dbname='{db}' user='{user}' password='{passwd}' port='{port}'".format(
            host=self.host, db=self.database, user=self.user, passwd=self.passwd, port=self.port
        )

        try:
            logger.debug('creating pool')
            self.pool = ThreadedConnectionPool(int(limits["Min"]), int(limits["Max"]), conn_params)
        except Exception as e:
            logger.exception(e.message)

    def get_conn(self):
        """
        Get a connection from pool and return connection and cursor
        :return: conn, cursor
        """
        logger.debug('getting connection from pool')
        try:
            conn = self.pool.getconn()
            cursor = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
            return conn, cursor
        except Exception as e:
            logger.exception(e.message)
            return None, None

    @staticmethod
    def execute_query(cursor, query, params):
        """
        Execute a query on database

        :param cursor: cursor object
        :param query: database query
        :type query: str
        :param params: query parameters
        :type params: tuple
        :return: query results or bool
        """
        logger.info('executing query')
        logger.debug('Cursor: {cursor}, Query: {query}'.format(
            cursor=cursor, query=query))

        try:
            if query.split()[0].lower() == 'select':
                cursor.execute(query, params)
                return cursor.fetchall()
            else:
                return cursor.execute(query, params)
        except Exception as e:
            logger.exception(e.message)
            return False

    # commit changes to db permanently
    @staticmethod
    def commit_changes(conn):
        """
        Commit changes to the databse permanently

        :param conn: connection object
        :return: bool
        """
        logger.debug('commiting changes to database')
        try:
            return conn.commit()
        except Exception as e:
            logger.exception(e.message)
            return False

    def put_conn(self, conn):
#.........这里部分代码省略.........