Python ThreadedConnectionPool.putconn方法代码示例

# 需要导入模块: from psycopg2.pool import ThreadedConnectionPool [as 别名]
# 或者: from psycopg2.pool.ThreadedConnectionPool import putconn [as 别名]
	def putconn(self, conn):
		"""
		Returns connection back to pool.

		"""

		#calledBy = traceback.extract_stack()[-2]
		#logging.info("PUTCONN - FILE: " + calledBy[0] + ", LINE: " + str(calledBy[1]) + ", METHOD: " + calledBy[2])

		ThreadedConnectionPool.putconn(self, conn)

# 需要导入模块: from psycopg2.pool import ThreadedConnectionPool [as 别名]
# 或者: from psycopg2.pool.ThreadedConnectionPool import putconn [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 putconn [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 putconn [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 putconn [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 putconn [as 别名]
class PgConnectionPool:

    def __init__(self, *args, min_conns=1, keep_conns=10, max_conns=10,
                 **kwargs):
        self._pool = ThreadedConnectionPool(
            min_conns, max_conns, *args, **kwargs)
        self._keep_conns = keep_conns

    def acquire(self):
        pool = self._pool
        conn = pool.getconn()
        pool.minconn = min(self._keep_conns, len(pool._used))
        return conn

    def release(self, conn):
        self._pool.putconn(conn)

    def close(self):
        if hasattr(self, '_pool'):
            self._pool.closeall()

    __del__ = close

# 需要导入模块: from psycopg2.pool import ThreadedConnectionPool [as 别名]
# 或者: from psycopg2.pool.ThreadedConnectionPool import putconn [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 putconn [as 别名]
class Psycopg2Backend(Backend):
    """Backend for accessing data stored in a Postgres database
    """

    display_name = "PostgreSQL"
    connection_pool = None
    auto_create_extensions = True

    def __init__(self, connection_params):
        super().__init__(connection_params)

        if self.connection_pool is None:
            self._create_connection_pool()

        if self.auto_create_extensions:
            self._create_extensions()

    def _create_connection_pool(self):
        try:
            self.connection_pool = ThreadedConnectionPool(
                1, 16, **self.connection_params)
        except Error as ex:
            raise BackendError(str(ex)) from ex

    def _create_extensions(self):
        for ext in EXTENSIONS:
            try:
                query = "CREATE EXTENSION IF NOT EXISTS {}".format(ext)
                with self.execute_sql_query(query):
                    pass
            except OperationalError:
                warnings.warn("Database is missing extension {}".format(ext))

    def create_sql_query(self, table_name, fields, filters=(),
                         group_by=None, order_by=None,
                         offset=None, limit=None,
                         use_time_sample=None):
        sql = ["SELECT", ', '.join(fields),
               "FROM", table_name]
        if use_time_sample is not None:
            sql.append("TABLESAMPLE system_time(%i)" % use_time_sample)
        if filters:
            sql.extend(["WHERE", " AND ".join(filters)])
        if group_by is not None:
            sql.extend(["GROUP BY", ", ".join(group_by)])
        if order_by is not None:
            sql.extend(["ORDER BY", ",".join(order_by)])
        if offset is not None:
            sql.extend(["OFFSET", str(offset)])
        if limit is not None:
            sql.extend(["LIMIT", str(limit)])
        return " ".join(sql)

    @contextmanager
    def execute_sql_query(self, query, params=None):
        connection = self.connection_pool.getconn()
        cur = connection.cursor()
        try:
            utfquery = cur.mogrify(query, params).decode('utf-8')
            log.debug("Executing: %s", utfquery)
            t = time()
            cur.execute(query, params)
            yield cur
            log.info("%.2f ms: %s", 1000 * (time() - t), utfquery)
        finally:
            connection.commit()
            self.connection_pool.putconn(connection)

    def quote_identifier(self, name):
        return '"%s"' % name

    def unquote_identifier(self, quoted_name):
        if quoted_name.startswith('"'):
            return quoted_name[1:len(quoted_name) - 1]
        else:
            return quoted_name

    def list_tables_query(self, schema=None):
        if schema:
            schema_clause = "AND n.nspname = '{}'".format(schema)
        else:
            schema_clause = "AND pg_catalog.pg_table_is_visible(c.oid)"
        return """SELECT n.nspname as "Schema",
                          c.relname AS "Name"
                       FROM pg_catalog.pg_class c
                  LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
                      WHERE c.relkind IN ('r','v','m','S','f','')
                        AND n.nspname <> 'pg_catalog'
                        AND n.nspname <> 'information_schema'
                        AND n.nspname !~ '^pg_toast'
                        {}
                        AND NOT c.relname LIKE '\\_\\_%'
                   ORDER BY 1;""".format(schema_clause)

    def create_variable(self, field_name, field_metadata,
                        type_hints, inspect_table=None):
        if field_name in type_hints:
            var = type_hints[field_name]
        else:
            var = self._guess_variable(field_name, field_metadata,
#.........这里部分代码省略.........

# 需要导入模块: from psycopg2.pool import ThreadedConnectionPool [as 别名]
# 或者: from psycopg2.pool.ThreadedConnectionPool import putconn [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 putconn [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 putconn [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 putconn [as 别名]

#.........这里部分代码省略.........
        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):
        """
        Put connection back to the pool

        :param conn: connection object
        :return: bool
        """
        logger.debug('putting connection {conn} back to pool'.format(conn=conn))
        try:
            return self.pool.putconn(conn)
        except Exception as e:
            logger.exception(e.message)
            return False

    def close_pool(self):
        """
        Closes connection pool
        :return: bool
        """
        logger.debug('closing connections pool')
        try:
            return self.pool.closeall()
        except Exception as e:
            logger.exception(e.message)
            return False

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

    def __init__(self, db_config, table_raw=None, max_connections=10):
        from psycopg2.pool import ThreadedConnectionPool

        self.table_raw = table_raw
        try:
            self.pool = ThreadedConnectionPool(minconn=1,
                                               maxconn=max_connections,
                                               dsn="dbname={db_name} user={db_user} host={db_host} password={db_pass}"
                                                   .format(**db_config))
        except Exception:
            logger.exception("Error in db connection")
            sys.exit(1)

        logger.debug("Connected to database: {host}".format(host=db_config['db_host']))

    @contextmanager
    def getcursor(self):
        conn = self.pool.getconn()
        try:
            yield conn.cursor()
            conn.commit()

        except Exception as e:
            conn.rollback()
            raise e.with_traceback(sys.exc_info()[2])

        finally:
            self.pool.putconn(conn)

    def insert(self, table, data_list, id_col='id'):
        """
        TODO: rename `id_col` -> `return_col`
        Create a bulk insert statement which is much faster (~2x in tests with 10k & 100k rows and 4 cols)
        for inserting data then executemany()

        TODO: Is there a limit of length the query can be? If so handle it.
        """
        # Make sure that `data_list` is a list
        if not isinstance(data_list, list):
            data_list = [data_list]

        if len(data_list) == 0:
            # No need to continue
            return []

        # Data in the list must be dicts (just check the first one)
        if not isinstance(data_list[0], dict):
            logger.critical("Data must be a list of dicts")
            # Do not return here, let the exception handle the error that will be thrown when the query runs

        try:
            with self.getcursor() as cur:
                query = "INSERT INTO {table} ({fields}) VALUES {values} RETURNING {id_col}"\
                        .format(table=table,
                                fields='{0}{1}{0}'.format('"', '", "'.join(data_list[0].keys())),
                                values=','.join(['%s'] * len(data_list)),
                                id_col=id_col
                                )
                query = cur.mogrify(query, [tuple(v.values()) for v in data_list])
                cur.execute(query)

                return [t[0] for t in cur.fetchall()]

        except Exception as e:
            logger.exception("Error inserting data")
            logger.debug("Error inserting data: {data}".format(data=data_list))
            raise e.with_traceback(sys.exc_info()[2])

    def update(self, table, data_list, matched_field=None, return_col='id'):
        """
        Create a bulk insert statement which is much faster (~2x in tests with 10k & 100k rows and 4 cols)
        for inserting data then executemany()

        TODO: Is there a limit of length the query can be? If so handle it.
        """
        if matched_field is None:
            # Assume the id field
            logger.info("Matched field not defined, assuming the `id` field")
            matched_field = 'id'

        # Make sure that `data_list` is a list
        if not isinstance(data_list, list):
            data_list = [data_list]

        if len(data_list) == 0:
            # No need to continue
            return []

        # Data in the list must be dicts (just check the first one)
        if not isinstance(data_list[0], dict):
            logger.critical("Data must be a list of dicts")
            # Do not return here, let the exception handle the error that will be thrown when the query runs

        try:
            with self.getcursor() as cur:
                query_list = []
                # TODO: change to return data from the database, not just what you passed in
                return_list = []
#.........这里部分代码省略.........

# 需要导入模块: from psycopg2.pool import ThreadedConnectionPool [as 别名]
# 或者: from psycopg2.pool.ThreadedConnectionPool import putconn [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 putconn [as 别名]
class Database(DatabaseInterface):

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

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

    def __init__(self, name='template1'):
        super(Database, self).__init__(name=name)
        self._databases.setdefault(name, self)
        self._search_path = None
        self._current_user = None

    @classmethod
    def dsn(cls, 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" % name
        user = uri.username and "user=%s" % uri.username or ''
        password = ("password=%s" % urllib.unquote_plus(uri.password)
            if uri.password else '')
        return '%s %s %s %s %s' % (host, port, name, user, password)

    def connect(self):
        if self._connpool is not None:
            return self
        logger.info('connect to "%s"', self.name)
        minconn = config.getint('database', 'minconn', default=1)
        maxconn = config.getint('database', 'maxconn', default=64)
        self._connpool = ThreadedConnectionPool(
            minconn, maxconn, self.dsn(self.name))
        return self

    def get_connection(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)
        if readonly:
            cursor = conn.cursor()
            cursor.execute('SET TRANSACTION READ ONLY')
        conn.cursor_factory = PerfCursor
        return conn

    def put_connection(self, connection, close=False):
        self._connpool.putconn(connection, close=close)

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

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

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

    def get_version(self, connection):
        if self.name not in self._version_cache:
            cursor = connection.cursor()
            cursor.execute('SELECT version()')
            version, = cursor.fetchone()
            self._version_cache[self.name] = tuple(map(int,
                RE_VERSION.search(version).groups()))
        return self._version_cache[self.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))
#.........这里部分代码省略.........