def main(n=3000):
dsn = os.environ['POSTGRES_DSN']
pool = ThreadedConnectionPool(1, 2, dsn=dsn)
conn = pool.getconn()
conn.autocommit = True
write_centroids_to_db(conn, n)
pool.putconn(conn)
def __new__(cls, name=_default_name):
with cls._lock:
now = datetime.now()
databases = cls._databases[os.getpid()]
for database in list(databases.values()):
if ((now - database._last_use).total_seconds() > _timeout
and database.name != name
and not database._connpool._used):
database.close()
if name in databases:
inst = databases[name]
else:
if name == _default_name:
minconn = 0
else:
minconn = _minconn
inst = DatabaseInterface.__new__(cls, name=name)
try:
inst._connpool = ThreadedConnectionPool(
minconn,
_maxconn,
**cls._connection_params(name),
cursor_factory=LoggingCursor)
logger.info('connected to "%s"', name)
except Exception:
logger.error('connection to "%s" failed',
name,
exc_info=True)
raise
else:
logger.info('connection to "%s" succeeded', name)
databases[name] = inst
inst._last_use = datetime.now()
return inst
def get_cortex_client_instance(pg_user,
pg_password,
pg_db,
pg_host='127.0.0.1',
pg_port='5432',
min_conn=0,
max_conn=3,
gc_interval_sec=15 * 60,
cortex_env="aws"):
global __cortex_client_instance
if __cortex_client_instance is not None:
return __cortex_client_instance
else:
pg_user = os.environ.get("CORTEX_CLIENT_USERNAME", pg_user)
pg_password = os.environ.get("CORTEX_CLIENT_PASSWORD", pg_password)
pg_host = os.environ.get("CORTEX_CLIENT_HOSTNAME", pg_host)
pg_port = os.environ.get("CORTEX_CLIENT_PORT", pg_port)
pg_db = os.environ.get("CORTEX_CLIENT_DATABASE", pg_db)
db_connection_pool = ThreadedConnectionPool(
minconn=min_conn,
maxconn=max_conn,
user=pg_user,
password=pg_password,
host=pg_host,
port=pg_port,
database=pg_db,
cursor_factory=NamedTupleCursor)
__cortex_client_instance = CortexClient(db_connection_pool,
gc_interval_sec, cortex_env)
return __cortex_client_instance
def __init__(self, max_pool_size: int, *, dsn):
self._pool = ThreadedConnectionPool(
1,
max_pool_size,
dsn=dsn,
cursor_factory=RealDictCursor,
)
def open_pool(self, minconns: int = 1,
maxconns: Optional[int] = None) -> None:
"""Initialises and opens a connection pool to the PostgreSQL database
(psycopg2.pool.ThreadedConnectionPool).
'minconn' new connections are created immediately. The connection pool
will support a maximum of about 'maxconn' connections.
Args:
minconns: minimum amount of available connections in the pool,
created on startup.
maxconns: maximum amount of available connections supported by
the pool. Defaults to 'minconns'.
"""
# If the pool hasn't been opened yet
if self.pool.closed:
# initialize max number of supported connections
maxconns = maxconns if maxconns is not None else minconns
# create a connection pool based on jumbo's configuration settings
self.pool = ThreadedConnectionPool(
minconns, maxconns,
host=self.config.DATABASE_HOST,
user=self.config.DATABASE_USERNAME,
password=self.config.DATABASE_PASSWORD,
port=self.config.DATABASE_PORT,
dbname=self.config.DATABASE_NAME,
sslmode='disable')
logger.info(f"Connection pool created to PostgreSQL database: "
f"{maxconns} connections available.")
def connect_to_db(connections=2):
"""
Connect to PostgreSQL and return connection
"""
minconn = connections
maxconn = connections * 2
return ThreadedConnectionPool(minconn, maxconn, DB_CONN_STRING)
def init_db_pool(db_min_connections, db_max_connections):
"""
Initializes the database connection pool required by the application to connect to the database.
"""
db = config.DATABASE
host = config.DATABASE_SERVER
user = config.DATABASE_USER
pw = config.DATABASE_PASSWORD
global connection_pool
if connection_pool is None:
logger.info(
"Initializing the database connection pool. db: '%s', user: '******', host: '%s'.",
db, user, host)
try:
connection_pool = ThreadedConnectionPool(db_min_connections,
db_max_connections,
database=db,
user=user,
password=pw,
host=host)
except psycopg2.Error as e:
logger.exception(
"Issue initializing the database connection pool. db: '%s', user: '******', host: '%s'.",
db, user, host)
raise ConnectionError(
"Issue initializing the database connection pool") from e
else:
logger.warning(
"The database connection pool has already been initialized.")
def __init__(self, min_con, max_con, *args, **kwargs):
self._pool = ThreadedConnectionPool(
min_con,
max_con,
*args,
connection_factory=NamedTupleConnection,
**kwargs)
def __new__(cls, name='template1'):
with cls._lock:
now = datetime.now()
databases = cls._databases[os.getpid()]
for database in list(databases.values()):
if ((now - database._last_use).total_seconds() > _timeout
and database.name != name
and not database._connpool._used):
database.close()
if name in databases:
inst = databases[name]
else:
if name == 'template1':
minconn = 0
else:
minconn = _minconn
inst = DatabaseInterface.__new__(cls, name=name)
logger.info('connect to "%s"', name)
inst._connpool = ThreadedConnectionPool(
minconn,
_maxconn,
**cls._connection_params(name),
cursor_factory=LoggingCursor)
databases[name] = inst
inst._last_use = datetime.now()
return inst
def __init__(
self,
table="footprints",
database_url=os.getenv("DATABASE_URL"),
geometry_column="geom",
):
if database_url is None:
raise Exception("Database URL must be provided.")
urlparse.uses_netloc.append("postgis")
urlparse.uses_netloc.append("postgres")
url = urlparse.urlparse(database_url)
self._pool = ThreadedConnectionPool(
0,
16,
database=url.path[1:],
user=url.username,
password=url.password,
host=url.hostname,
port=url.port,
)
self._log = logging.getLogger(__name__)
self.table = table
self.geometry_column = geometry_column
def create_connection_pool(cls, size):
"""Create database connections."""
# DB credentials precedence:
# module attribute > env variable > shared config (common/config.py)
cls.db_name = DB_NAME or os.getenv("POSTGRESQL_DATABASE",
cls._cfg.db_name)
cls.db_user = DB_USER or os.getenv('POSTGRESQL_USER', cls._cfg.db_user)
cls.db_pass = DB_PASS or os.getenv('POSTGRESQL_PASSWORD',
cls._cfg.db_pass)
cls.db_host = DB_HOST or os.getenv("POSTGRESQL_HOST", cls._cfg.db_host)
cls.db_port = DB_PORT or int(
os.getenv("POSTGRESQL_PORT", cls._cfg.db_port))
cls.db_ssl_mode = DB_SSL_MODE or os.getenv("POSTGRESQL_SSL_MODE",
cls._cfg.db_ssl_mode)
cls.db_ssl_root_cert_path = (DB_SSL_ROOT_CERT_PATH or os.getenv(
"POSTGRESQL_SSL_ROOT_CERT_PATH", cls._cfg.db_ssl_root_cert_path))
cls.pool_size = size
cls.pool = ThreadedConnectionPool(
size,
size,
dbname=cls.db_name,
user=cls.db_user,
password=cls.db_pass,
host=cls.db_host,
port=cls.db_port,
sslmode=cls.db_ssl_mode,
sslrootcert=cls.db_ssl_root_cert_path,
)
def create_app(config_name):
app = Flask(__name__)
current_config = config[config_name]
app.config.from_object(current_config)
current_config.init_app(app)
app.config['MAX_CONTENT_LENGTH'] = 50 * 1024 * 1024
bootstrap.init_app(app)
# attach routes and error pages here
from .main import main as main_blueprint
app.register_blueprint(main_blueprint)
from .api_1_0 import api as api_1_0_blueprint
app.register_blueprint(api_1_0_blueprint, url_prefix='/api/v1.0')
@app.before_request
def before_request():
g.conn = current_app.connection_pool.getconn()
@app.teardown_request
def teardown(exc):
g.conn.commit()
current_app.connection_pool.putconn(g.conn)
min_connections = 5
max_connections = 10
app.connection_pool = ThreadedConnectionPool(
min_connections,
max_connections,
host=app.config["DB_HOST_NAME"],
database=app.config["DB_DB_NAME"],
user=app.config["DB_USER_NAME"])
return app
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 initialize(conf):
global connection_pool
import db.user
import db.assignment
Base.metadata.create_all(engine)
connection_pool = ThreadedConnectionPool(3, 5, **conf)
execute_init_statements()
def connect_psycopg(cls,
host,
port,
username,
password,
dbname,
minConn=1,
maxConn=10):
""" connection to the ThreadedConnectionPool
:param host: hostname of database
:param port: port of database
:param username: username for connection
:param password: password for connection
:param dbname: database name for connection
:param minConn: minimum connections
:param maxConn: maximum connections
"""
try:
if is_psycopg2_importable:
# create connection pool
cls.pool = ThreadedConnectionPool(minconn=minConn,
maxconn=maxConn,
user=username,
password=password,
host=host,
port=port,
database=dbname)
else:
print("psycogp2 is not imported")
except psycopg2.DatabaseError as e:
logging.getLogger('CredentialDatabase').error(
'Could not connect to ThreadedConnectionPool: {}'.format(e))
def __init__(self, dbname='cuny_curriculum'):
""" Get the connection string from the environment and connect to the db.
Raises PoolError, disguised as a RuntimeError, if cthe connection pool is exhausted.
"""
if os.getenv('DATABASE_NAME') is not None:
dbname = os.getenv('DATABASE_NAME')
connection_string = f'dbname={dbname}'
if DEBUG:
print(connection_string, file=sys.stderr)
# Initialize a pool for this db if not available yet.
if dbname not in PgConnection._pools.keys():
pool_max = os.environ.get(
'DB_POOL_MAX') # Try to stay in Heroku hobby-basic tier limit
if pool_max is None:
pool_max = 95 # Value of max_connections in default postgresql.conf is 100
else:
pool_max = int(pool_max)
PgConnection._pools[dbname] = ThreadedConnectionPool(
2, pool_max, connection_string)
# Initialize this PgConnection instance
try:
self.dbname = dbname
self.pool = PgConnection._pools[dbname]
self.connection = self.pool.getconn()
except PoolError as pe:
raise RuntimeError(pe)
return
def ExecuteThreaded(self, statements):
"""ExecuteThreaded runs sql statements each in its own thread.
Operation: Fire up up to MAX_THREADS and queue the rest. The connection
pool object uses lazy initialization, and will be set to None when the
connection is closed. It is always initialized to MAX_THREADS.
Limitations: Function will block until all threads complete. This means
that the function cannot be executed concurrently, the assumption is
that the process runs in only one thread, or at least that it won't
attempt to run this function concurrently.
Args:
statements: must be a list of strings with two or more items.
"""
self._logger.debug("ExecuteThreaded started.")
if not self._connection_pool:
self._connection_pool = ThreadedConnectionPool(
PostgresConnection.MIN_THREADS, PostgresConnection.MAX_THREADS,
self._connection.dsn)
semaphore = threading.BoundedSemaphore(
value=PostgresConnection.MAX_THREADS)
for idx, sql in enumerate(statements):
thread = threading.Thread(target=_ModifyThreaded,
name="Thread%s" % idx,
args=(semaphore, self._connection_pool,
sql, self._logger))
thread.setDaemon(0)
thread.start()
self._logger.debug("ExecuteTheaded completed.")
def __init__(self,
url=None,
hstore=False,
log=None,
logf=None,
min=1,
max=5,
default_cursor=DictCursor):
params = urlparse(url or os.environ.get('DATABASE_URL')
or 'postgres://localhost/')
if params.scheme != 'postgres':
raise ValueError(
"Invalid connection string (postgres://user@pass:host/db?param=value)"
)
self.pool = ThreadedConnectionPool(
min,
max,
database=params.path[1:] or parse_qs(params.query).get('dbname'),
user=params.username or parse_qs(params.query).get('user'),
password=params.password or parse_qs(params.query).get('password'),
host=params.hostname or parse_qs(params.query).get('host'),
port=params.port or parse_qs(params.query).get('port'),
)
self.hstore = hstore
self.log = log
self.logf = logf or (lambda cursor: cursor.query.decode())
self.default_cursor = default_cursor
self.prepared_statement_id = 0
def __init__(self, database_config):
self.database_config = database_config
pool_config = self.database_config['pool']
conn_config = self.database_config['conn']
self.conn_pool = ThreadedConnectionPool(minconn=pool_config['min'],
maxconn=pool_config['max'],
**conn_config)
def test_context_manager(self):
"""
test using the context manager to access the pool
"""
min_connections = 1
max_connections = 5
test_number = 42
connection_pool = ThreadedConnectionPool(min_connections,
max_connections,
**_database_credentials)
test_greenlet = ContextWriteGreenlet(connection_pool, test_number, 3.0)
rollback_greenlet = ContextRollbackGreenlet(connection_pool, 3.0)
test_greenlet.start()
rollback_greenlet.start()
test_greenlet.join()
self.assertTrue(test_greenlet.successful())
rollback_greenlet.join()
self.assertTrue(rollback_greenlet.successful())
result = test_greenlet.value
self.assertEqual(result, [(test_number, )])
connection_pool.closeall()
def create_pool(self, conn_dict):
"""
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='******' password='******' port='{port}'".format(
host=self.host,
db=self.database,
user=self.user,
passwd=self.passwd,
port=self.port)
try:
self.pool = ThreadedConnectionPool(1, 50, conn_params)
except Exception as e:
print(e.message)
def test_decorator(self):
"""
test using the decorator to access the pool
"""
global _connection_pool
min_connections = 1
max_connections = 5
test_number = 42
_connection_pool = ThreadedConnectionPool(min_connections,
max_connections,
**_database_credentials)
test_greenlet = DecoratorWriteGreenlet(test_number, 3.0)
rollback_greenlet = DecoratorRollbackGreenlet(3.0)
test_greenlet.start()
rollback_greenlet.start()
test_greenlet.join()
self.assertTrue(test_greenlet.successful())
rollback_greenlet.join()
self.assertTrue(rollback_greenlet.successful())
result = test_greenlet.value
self.assertEqual(result, [(test_number, )])
_connection_pool.closeall()
def create_connection_pool(cls, size):
"""Create database connections."""
# DB credentials precedence:
# module attribute > shared config (env variables > clowder variables if available)
cls.db_name = DB_NAME or cls._cfg.db_name
cls.db_user = DB_USER or cls._cfg.db_user
cls.db_pass = DB_PASS or cls._cfg.db_pass
cls.db_host = DB_HOST or cls._cfg.db_host
cls.db_port = DB_PORT or cls._cfg.db_port
cls.db_ssl_mode = DB_SSL_MODE or os.getenv("POSTGRESQL_SSL_MODE", cls._cfg.db_ssl_mode)
cls.db_ssl_root_cert_path = (
DB_SSL_ROOT_CERT_PATH
or os.getenv("POSTGRESQL_SSL_ROOT_CERT_PATH", cls._cfg.db_ssl_root_cert_path)
)
cls.pool_size = size
cls.pool = ThreadedConnectionPool(
size,
size,
dbname=cls.db_name,
user=cls.db_user,
password=cls.db_pass,
host=cls.db_host,
port=cls.db_port,
sslmode=cls.db_ssl_mode,
sslrootcert=cls.db_ssl_root_cert_path,
)
def setup():
global pool
os.environ[
'DATABASE_URL'] = "postgres://*****:*****@ec2-174-129-32-37.compute-1.amazonaws.com:5432/dc7jh3hml9a2am"
DATABASE_URL = os.environ['DATABASE_URL']
current_app.logger.info(f"creating db connection pool")
pool = ThreadedConnectionPool(1, 4, dsn=DATABASE_URL, sslmode='require')
def __init__(self, env_path: Optional[str] = None) -> None:
"""Initializes manager to handle connections to a given PostgreSQL
database.
Args:
env_path: path where to look for the jumbo.env configuration
file. If not provided, looks for configuration file
in the working directory of the script invoking this
constructor.
Attributes:
config (jumbo.config.Config): jumbo's database configuration
settings.
pool (psycopg2.pool): connection pool to the PostgreSQL
database. Initially closed.
"""
# Database configuration settings
self.config = Config(env_path)
# initialize a placeholder connection pool
self.pool = ThreadedConnectionPool(0, 0)
self.pool.closed = True # keep it closed on construction
# Log configuration settings
logger.info(f"Jumbo Connection Manager created:\n{self.config}")
def allocate_db_connection_pool(
connection_config: DatabaseConnectionConfig,
max_connections: int = 20) -> ThreadedConnectionPool:
"""
Allocate a pool of database connections for an application
Connecting to a database can be a costly operation for stateless
applications that jump in and out of a database frequently,
like a REST APIs. To combat this, a connection pool provides a set of
persistent connections that preclude these applications from constantly
connecting and disconnecting from the database.
:param connection_config: data needed to establish a connection
:param max_connections: maximum connections allocated to the application
:return: a pool of database connections to be used by the application
"""
log_msg = (
'Allocating a pool of connections to the {db_name} database with '
'a maximum of {max_connections} connections.')
_log.info(
log_msg.format(db_name=connection_config.db_name,
max_connections=max_connections))
return ThreadedConnectionPool(minconn=1,
maxconn=max_connections,
database=connection_config.db_name,
user=connection_config.user,
password=connection_config.password,
host=connection_config.host,
port=connection_config.port,
cursor_factory=RealDictCursor)
def __init__(self,
db_info=None,
hstore=False,
log=None,
logf=None,
min=1,
max=100,
default_cursor=DictCursor):
if db_info is None:
raise ValueError("Invalid connection Params")
self.pool = ThreadedConnectionPool(
min,
max,
database=db_info['database'],
user=db_info['user'],
password=db_info['password'],
host=db_info['host'],
port=db_info['port'],
)
self.hstore = hstore
self.log = log
self.logf = logf or (lambda cursor: cursor.query.decode())
self.default_cursor = default_cursor
self.prepared_statement_id = 0
def setup():
global pool
DATABASE_URL = os.environ['DATABASE_URL']
current_app.logger.info(DATABASE_URL)
current_app.logger.info(f"creating db connection pool")
pool = ThreadedConnectionPool(1, 4, dsn=DATABASE_URL, sslmode='require')
current_app.logger.info(f"finished creading pool")
def connect(self, ctx_list=None, mark=None):
"""Connect to database."""
self.log.debug("Connecting to database: %(dbname)s as %(user)s" %
self.__params__)
try:
self.lock.acquire_lock()
try:
if not self.__conn_pool__:
connector = (
"host=%(host)s port=%(port)s user=%(user)s password=%(password)s dbname=%(dbname)s"
% self.__params__)
self.__conn_pool__ = ThreadedConnectionPool(
1, 20, connector)
finally:
self.lock.release_lock()
if not ctx_list:
ctx_list = []
ctx_list.append(self._new_ctx(mark))
self.log.debug(
"ctx:(%(mark)s, %(ctxid)s) Connected to database: %(dbname)s as %(user)s"
% {
"mark": ctx_list[-1]["mark"],
"ctxid": str(id(ctx_list[-1])),
"dbname": self.__params__["dbname"],
"user": self.__params__["user"],
})
return ctx_list
except psycopg2.Error as _error:
# We do not want our users to have to 'import psycopg2' to
# handle the module's underlying database errors
_, value, traceback = sys.exc_info()
raise self.DatabaseError(value).with_traceback(traceback)
def setUp(self):
self.createdb('quast_test')
# Run setup.sql
file_path = os.path.abspath(
os.path.join(os.path.dirname(os.path.abspath(__file__)), '..',
'sql/setup.sql'))
self.conn = psycopg2.connect("user=postgres dbname=quast_test")
self.conn.autocommit = True
with open(file_path) as f:
with self.conn.cursor() as curs:
sql = f.read()
curs.execute(sql)
# Run populate.sql
file_path = os.path.abspath(
os.path.join(os.path.dirname(os.path.abspath(__file__)), '..',
'sql/populate.sql'))
with open(file_path) as f:
with self.conn.cursor() as curs:
sql = f.read()
curs.execute(sql)
self.pool = ThreadedConnectionPool(1, 10,
"user=postgres dbname=quast_test")
