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
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:
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()
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)
class ConnectionPool:
pool = None
def __init__(self):
try:
min_connections = 1
max_connections = int(
os.getenv("POSTGRES_MAX_CONNECTIONS")) if os.getenv(
"POSTGRES_MAX_CONNECTIONS") is not None else 10
self.pool = SimpleConnectionPool(min_connections,
max_connections,
dbname=POSTGRES_DBNAME,
user=POSTGRES_USER,
host=POSTGRES_HOST,
password=POSTGRES_PASSWORD,
port=POSTGRES_PORT)
except Exception as e:
print(
str(e), Logs.ERROR, {
"postgresql":
"{hostname}:{port}/{dbname}".format(hostname=POSTGRES_HOST,
port=POSTGRES_PORT,
dbname=POSTGRES_DBNAME)
})
def isAvailable(self):
return (self.pool is not None)
def closeAll(self):
self.pool.closeall()
def getConnection(self):
conn = None
try:
conn = self.pool.getconn()
except PoolError:
print("error obteniendo una conexión a PostGIS, reintentando...",
Logs.ERROR)
self.pool.closeall() # close all active connections in the pool
conn = self.pool.getconn()
conn.autocommit = True
return conn
def closeConnection(self, conn):
self.pool.putconn(conn, close=True)
def runScript(self, scriptDir):
insertCounter = 0
geometryType = None
conn = self.getConnection()
for root, dirnames, filenames in os.walk(scriptDir):
for filename in filenames:
if filename[-4:] == '.sql':
scriptPath = root + "/" + filename
print("Ejecutando " + scriptPath, Logs.INFO)
with open(scriptPath, "r") as script:
# create db cursor
cursor = conn.cursor()
for query in script.read().split(
utils.QUERY_DELIMITER):
if query == "":
continue
try:
cursor.execute(query)
if query.startswith("INSERT INTO"):
insertCounter += 1
elif geometryType is None and query.startswith(
"SELECT AddGeometryColumn"):
geometryType = query.split(",")[-2][1:-1]
except Exception as e:
print(str(e), Logs.ERROR, {"query": query})
cursor.close()
print("ok", Logs.INFO,
{"inserted_features": str(insertCounter)})
self.closeConnection(conn)
return insertCounter, geometryType
class PostgresqlWrapper(object):
""" Postgresql wrapper to heroku server to upload and download music data"""
DATABASE_URL = "postgres://*****:*****@ec2-54-75-239-237.eu-west-1.compute.amazonaws.com:5432/d5jk6qjst0rku1"
MUSIC_PATH = "genres"
LOCALHOST_STING = "host='localhost' dbname='music' user='******' 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_logger(self):
ch = logging.StreamHandler() # console
formatter = logging.Formatter(
'%(asctime)s - %(name)s - %(levelname)s - %(message)s')
ch.setFormatter(formatter)
self.log = logging.getLogger(__name__)
#if (self.log.hasHandlers()):
# self.log.handlers.clear()
self.log.setLevel(logging.INFO)
self.log.addHandler(ch)
def create_table(self, clean=False):
self.log.info("Creating table")
if clean:
self.cur.execute('drop table music')
self.conn.commit()
statement = "CREATE TABLE if not exists music \
(id serial PRIMARY KEY, genre varchar(100), data BYTEA);"
self.cur.execute(statement)
self.conn.commit()
def insert_song(self, genre, song):
statement = "Insert into music(genre, data) values(%s, %s)"
self.cur.execute(statement, (genre, song))
def select_songs(self, limit=None, offset=None, genre=None):
conn = self.pool.getconn()
cur = conn.cursor()
if genre is None:
statement = "Select * from music order by id limit %s offset %s"
self.log.info("Statement %s", statement % (limit, offset))
cur.execute(statement, (limit, offset))
self.log.info("Done with %s", statement % (limit, offset))
else:
statement = "Select * from music where genre = %s order by id limit %s offset %s"
self.log.info("Statement %s", statement % (genre, limit, offset))
cur.execute(statement, (genre, limit, offset))
db_result = cur.fetchall()
cur.close()
self.pool.putconn(conn)
return db_result
def register_adapters(self):
""" Handy adapters to transalte np.array to binary and vice versa """
def _adapt_array(text):
out = io.BytesIO()
np.save(out, text)
out.seek(0)
return psycopg2.Binary(out.read())
def _typecast_array(value, cur):
if value is None:
return None
data = psycopg2.BINARY(value, cur)
bdata = io.BytesIO(data)
bdata.seek(0)
return np.load(bdata)
psycopg2.extensions.register_adapter(np.ndarray, _adapt_array)
t_array = psycopg2.extensions.new_type(psycopg2.BINARY.values, "numpy",
_typecast_array)
psycopg2.extensions.register_type(t_array)
self.log.info("Done register types")
def to_database(self, folders=None, limit=1000):
""" Process music to database """
for root, _, files in os.walk(self.MUSIC_PATH):
genre = root.split('/')[-1]
if folders is not None and genre not in folders:
continue
for i, file_ in enumerate(files):
if i == limit:
break
self.log.info("Inserting song %s", file_)
song = librosa.load(os.path.join(root, file_))[0]
self.insert_song(genre, song)
self.conn.commit()
self.close_connection()
def close_connection(self):
self.log.info("Closing connection")
self.pool.closeall()
self.cur.close()
self.conn.close()
def fetch_songs(self, count, limit=50, genres=None):
""" Fetch song in concurrent from database
limit - how many song to fetch from one thread
count - how many song to fetch
"""
self.log.info("Start fetching %s songs", count)
producer = []
iter_ = 0
offset = 0
while offset < count:
offset = limit * iter_
if genres is not None:
for genre in genres:
producer.append((limit, offset, genre))
else:
producer.append((limit, offset))
iter_ += 1
with Pool(self.conn_num) as pool:
result = pool.starmap(self.select_songs, producer)
return result
with conn.cursor() as cur:
cur.execute(
'UPDATE m2_package SET license = %s, success = %s WHERE index_id = %s',
(','.join(artifact.licenses), True, index))
conn.commit()
except:
conn.rollback()
raise
finally:
pg_pool.putconn(conn)
def start():
global stop
while not stop:
location = read_db(get_next())
if location is None:
break
try:
artifact = Artifact(location[1:])
set_licenses(location[0], artifact)
except BaseException as e:
logger.error("%s\n%s\n\n", location, e, exc_info=1)
break
start()
redis_pool.disconnect()
pg_pool.closeall()
class PgDbModel:
def __init__(self, conf: dict):
'''
:param conf: dict(
database='postgres', # 库名
user='******', # 用户
password='******', # 密码
host='127.0.0.1', # IP
port='5432', # 端口
minconn=1, # 最小连接数
maxconn=5 # 最大连接数
)
'''
# 创建连接池
self.dpool = DbPool(**conf)
# 获取连接
def get_conn(self):
return self.dpool.getconn()
# 获取游标
def get_cur(self, conn, dictcur=False):
if dictcur == True:
return conn.cursor(cursor_factory=RealDictCursor)
else:
return conn.cursor()
# 提交事务
def conn_commit(self, conn):
return conn.commit()
# 回滚事务
def conn_rollback(self, conn):
return conn.rollback()
# 回收连接
def conn_close(self, conn):
return self.dpool.putconn(conn)
# 关闭连接池
def close_all(self):
return self.dpool.closeall()
# 返回查询结果
def fetch_all(self, cur):
return cur.fetchall()
# 返回行数
def row_count(self, cur):
return cur.rowcount
# 批量操作
def executemany(self, cur, sql, data_list):
if isinstance(data_list, list):
cur.executemany(sql, data_list)
# 单条操作
def execute(self, cur, sql, data):
if data is not None:
cur.execute(sql, data)
else:
cur.execute(sql)
# 查询操作
# 慢查询日志装饰器
@slow_log(
'query', configs.settings.SLOW_LOGGER_QUERY_THRESHOLD,
slow_off=configs.settings.SLOW_LOGGER_QUERY_OFF,
log_title=configs.settings.SLOW_LOGGER_QUERY_TITLE,
timezone=configs.settings.SLOW_LOGGER_QUERY_TIMEZONE
)
def select(self, cur, sql, data=None):
self.execute(cur, sql, data)
return self.fetch_all(cur)
# 批量插入
def insert_all(self, cur, sql, data_list):
self.executemany(cur, sql, data_list)
return self.row_count(cur)
# 单条插入
def insert_one(self, cur, sql, data):
self.execute(cur, sql, data)
return self.row_count(cur)
# 批量更新
def update_all(self, cur, sql, data_list):
self.executemany(cur, sql, data_list)
return self.row_count(cur)
# 单条更新
def update_one(self, cur, sql, data):
self.execute(cur, sql, data)
return self.row_count(cur)
# 返回上次执行的SQL
def get_query(self, cur):
return cur.query.decode()
# 检校合成的SQL是否正确
def check_sql(self, cur, sql, data):
return cur.mogrify(sql, data).decode()
from psycopg2 import connect
from psycopg2.pool import SimpleConnectionPool
from contextlib import contextmanager
conn_str = 'host=127.0.0.1 dbname=ci_dev_july user=postgres password=postgres port=5432'
minconn = 1
maxconn = 8
pool = SimpleConnectionPool(minconn, maxconn, conn_str)
@contextmanager
def get_cursor():
conn = pool.getconn()
try:
yield conn.cursor()
finally:
pool.putconn(conn)
sql = "select * from datagranule limit 10"
with get_cursor() as cur:
cur.execute(sql)
rows = cur.fetchall()
print len(rows)
pool.closeall()
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 GPPool:
dbname = 'localhost'
user = '******'
host = '127.0.0.1'
password = '******'
port = 5432
gp_pool = None
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 exe_conn(self, sql):
conn = self.gp_pool.getconn() # 获取连接
cursor = conn.cursor() # 获取cursor
cursor.execute(sql) # 用于执行SQL语句
# cursor.mogrify(query) #返回生成的sql脚本, 用以查看生成的sql是否正确
conn.commit() # 没次操作都要提交
self.gp_pool.putconn(conn) # 放回连接, 防止其他程序pg无连接可用
return cursor
def fetchone_sql(self, sql):
cursor = self.exe_conn(sql)
# desc = cursor.description # cursor 的具体描述信息
fetchone = cursor.fetchone() # 获取执行结果中的一条记录
cursor.close() # 关闭当前连接的游标
return fetchone
def fetchall_sql(self, sql):
cursor = self.exe_conn(sql)
fetchall = cursor.fetchall() # 获取SQL执行结果中的所有记录，返回值是一个元组的列表，每一条记录是一个元组
cursor.close()
return fetchall
def fetchmany_sql(self, sql, size=1):
cursor = self.exe_conn(sql)
fetchall = cursor.fetchmany(size) # 获取SQL执行结果中指定条数的记录，记录数由size指定
cursor.close()
return fetchall
def exe_sql(self, sql):
cursor = self.exe_conn(sql)
cursor.close()
def close_all(self):
self.gp_pool.closeall()
