def put(self, podcastId):
conn, cur = get_conn()
user_id = get_user_id(cur)
timestamp = request.json.get("time")
episodeGuid = request.json.get("episodeGuid")
if timestamp is None:
cur.close()
pool.putconn()
return {"data": "timestamp not included"}, 400
if not isinstance(timestamp, int):
close_conn(conn, cur)
return {"data": "timestamp must be an integer"}, 400
if episodeGuid is None:
close_conn(conn, cur)
return {"data": "episodeGuid not included"}, 400
# we're touching episodes so insert new episode (if it doesn't already exist)
cur.execute(
"""
INSERT INTO episodes (podcastId, guid)
values (%s, %s)
ON CONFLICT DO NOTHING
""", (podcastId, episodeGuid))
cur.execute(
"""
INSERT INTO listens (userId, podcastId, episodeGuid, listenDate, timestamp)
values (%s, %s, %s, now(), %s)
ON CONFLICT ON CONSTRAINT listens_pkey DO UPDATE set listenDate=now(), timestamp=%s;
""", (user_id, podcastId, episodeGuid, timestamp, timestamp))
cur.close()
conn.commit()
pool.putconn(conn)
return {}, 200
def getResultSetFromDB(funcName, params):
conn = pool.getconn()
with conn, conn.cursor(cursor_factory=RealDictCursor) as cursor:
cursor.callproc(funcName, params)
result = json.dumps(cursor.fetchall(), default=str)
pool.putconn(conn)
return result
def exe_wait_fetch(pool, query, times_exe, times_wait, times_fetch,
times_total):
print("exe wait fetch")
print("query :")
print(query)
start_time = time.perf_counter()
aconn = task_getconn(pool)
#time.sleep(1)
res_exe = task_execute(aconn, query)
acurs = res_exe[0]
times_exe.append(res_exe[1])
times = task_wait_fetch(acurs)
times_wait.append(times[0])
times_fetch.append(times[1])
pool.putconn(aconn)
end_time = time.perf_counter()
total = end_time - start_time
times_total.append(total)
def check_pool(self, pool=None):
"""checks if either its own or the provided pool is valid
Keyword Arguments:
pool {[postgre_pool.pool]} -- [pool to be checked] (default: {self.pool})
Returns:
[boolean] -- [False if exists, True if it doesn't or is invalid]
"""
if not pool:
if not self.pool:
return 1
else:
try:
test_connection = self.pool.getconn()
self.pool.putconn(test_connection)
return 0
except Exception:
return 1
else:
try:
test_connection = pool.getconn()
pool.putconn(test_connection)
return 0
except Exception:
return 1
def get_cursor():
pool = get_connection_pool()
con = pool.getconn()
try:
yield con.cursor()
finally:
pool.putconn(con)
def exe_wait_fetch_dict(pool, query, times_exe, times_wait, times_fetch,
times_total, qdict, perf, starts, ends):
print("exe wait fetch")
print("query :")
print(query)
start_time = time.perf_counter()
starts.append(start_time)
aconn = task_getconn(pool)
#time.sleep(1)
res_exe = task_execute(aconn, qdict[query])
acurs = res_exe[0]
times_exe.append(res_exe[1])
times = task_wait_fetch(acurs)
times_wait.append(times[0])
times_fetch.append(times[1])
pool.putconn(aconn)
end_time = time.perf_counter()
ends.append(end_time)
total = end_time - start_time
times_total.append(total)
perf.append(
"query : {} execution : {}s , wait : {}s, fetch : {}s, total : {}s \n".
format(query, res_exe[1], times[0], times[1], total))
def posts():
connection = pool.getconn()
cursor = connection.cursor()
props = {'currentPage': 'posts'}
base = request.args.to_dict()
base.pop('o', None)
query = "SELECT * FROM booru_posts ORDER BY added desc "
params = ()
offset = request.args.get('o') if request.args.get('o') else 0
query += "OFFSET %s "
params += (offset, )
limit = request.args.get('limit') if request.args.get(
'limit') and request.args.get('limit') <= 50 else 25
query += "LIMIT %s"
params += (limit, )
cursor.execute(query, params)
results = cursor.fetchall()
cursor.close()
if connection:
pool.putconn(connection)
response = make_response(
render_template('posts.html', props=props, results=results, base=base),
200)
response.headers[
'Cache-Control'] = 'max-age=60, public, stale-while-revalidate=2592000'
return response
def query():
req = request.get_json()
if not req:
return make_response('', 400)
try:
conn = pool.getconn()
with conn.cursor() as cur:
cur.execute(req['query'])
results = cur.fetchall()
return jsonify({
'status': 200,
'results': results
})
except (psycopg2.DataError, psycopg2.ProgrammingError, psycopg2.NotSupportedError) as e:
# User error
return jsonify({
'status': 400,
'error': e.pgerror
}), 400
except:
return jsonify({
'status': 500,
'error': str(sys.exc_info()[1])
}), 500
finally:
pool.putconn(conn)
def get_connection():
pool = get_connection_pool()
try:
connection = pool.getconn()
yield connection
finally:
pool.putconn(connection)
def close(e):
cursor = g.pop('cursor', None)
if cursor is not None:
cursor.close()
connection = g.pop('connection', None)
if connection is not None:
connection.commit()
pool.putconn(connection)
def getResultSetFromDBNoJS(funcName, params):
conn = pool.getconn()
with conn, conn.cursor(cursor_factory=RealDictCursor) as cursor:
cursor.callproc(funcName, params)
# Convert from RealDict => json => Python list
result = json.loads(json.dumps(cursor.fetchall(), default=str))
pool.putconn(conn)
return result
def modifyDB(funcName, params):
conn = pool.getconn()
with conn, conn.cursor(cursor_factory=RealDictCursor) as cursor:
cursor.callproc(funcName, params)
result = json.dumps(cursor.fetchall())
pool.putconn(conn)
# Return status and error message
return result
def cursor(): global pool con = pool.getconn() try: yield con.cursor() finally: con.commit() pool.putconn(con)
def cursor(): global pool con = pool.getconn() con.set_isolation_level(extensions.ISOLATION_LEVEL_AUTOCOMMIT) try: yield con.cursor() finally: con.commit() pool.putconn(con)
def get_conn(pool):
conn = None
try:
conn = pool.getconn()
conn.autocommit = True
yield conn
finally:
if conn:
pool.putconn(conn)
def get_conn(pool):
conn = None
try:
conn = pool.getconn()
conn.autocommit = True
yield conn
finally:
if conn:
pool.putconn(conn)
def db_test():
pool = get_pool()
conn = pool.getconn()
cursor = conn.cursor()
cursor.execute("SELECT * FROM _test")
cursor.fetchall()
cursor.close()
pool.putconn(conn)
return "OK"
def random_post():
connection = pool.getconn()
cursor = connection.cursor()
query = "SELECT service, \"user\", id FROM booru_posts WHERE random() < 0.01 LIMIT 1"
cursor.execute(query)
random = cursor.fetchall()
cursor.close()
if connection:
pool.putconn(connection)
return redirect(f'/{random[0][0]}/user/{random[0][1]}/post/{random[0][2]}')
def push_packets_to_database(handshakes: list) -> None:
if handshakes: # Is the list not empty?
connection = pool.getconn()
cursor = connection.cursor()
for handshake in handshakes:
cursor.execute("INSERT INTO Packets(mac, url, time) VALUES (%s, %s, %s)",
(handshake.mac, handshake.url, handshake.time))
connection.commit()
cursor.close()
pool.putconn(connection)
def use_pure_sql():
conn = pool.getconn()
cur = conn.cursor()
cur.execute("select * from person where person_id in ('mosky', 'andy') order by person_id")
result = cur.fetchall()
cur.close()
pool.putconn(conn)
return result
def hello_world():
result = []
connection = pool.getconn()
cur = connection.cursor()
cur.execute('select first_name, last_name from people limit 100;')
for first_name, last_name in cur.fetchall():
result.append('{} {}<br>'.format(first_name, last_name))
pool.putconn(connection)
return '<html><head></head><body>{}</body></html>'.format(
'\n'.join(result)
)
def fun(*args, **kwargs):
conn = pool.getconn()
cur = conn.cursor()
try:
ret = f(cur, *args, **kwargs)
conn.commit()
return ret
except StandardError as err:
conn.rollback()
raise err
finally:
pool.putconn(conn)
def execute_sql(pool, sql, params):
connection = pool.getconn()
if not connection:
raise Exception('[psql] Cannot acquare connection from pool')
cursor = connection.cursor()
cursor.execute(sql, params)
rows = cursor.fetchall()
cursor.close()
pool.putconn(connection)
return rows
def random_artist():
connection = pool.getconn()
cursor = connection.cursor()
query = "SELECT id, service FROM lookup WHERE service != 'discord-channel' ORDER BY random() LIMIT 1"
cursor.execute(query)
random = cursor.fetchall()
cursor.close()
if connection:
pool.putconn(connection)
if len(random) == 0:
return redirect('back')
return redirect(f'/{random[0][1]}/user/{random[0][0]}')
def getcursor(**kwargs):
pool = UnpackHelpers.get_sql_pool()
conn = pool.getconn()
try:
yield conn.cursor(**kwargs)
conn.commit()
except Exception as e:
conn.rollback()
raise
finally:
pool.putconn(conn)
def get_db_connection():
try:
global pool
pool = psycopg2.pool.ThreadedConnectionPool(1, 20,
user = app.config['DB_USER'],
password = app.config['DB_PASSWORD'],
host = app.config['DB_HOST'],
port = app.config['DB_PORT'],
database = app.config['DB_NAME'])
connection = pool.getconn()
yield connection
finally:
pool.putconn(connection)
def get_db_connection_from_pool(request_id):
url = urlparse(app.config['DATABASE_URL'])
try:
log_utils.log_database_connection_from_pool(request_id, url, "SUCCESS")
connection = pool.getconn()
yield connection
except exception.DatabaseConnectionError:
log_utils.log_database_connection_from_pool(request_id, url, "FAILURE")
raise exception.ServiceError
finally:
pool.putconn(connection)
log_utils.log_database_connection_back_to_pool(request_id, url,
"SUCCESS")
def wrapped(*p, **kw):
conn = pool.getconn()
kw[keyword] = conn.cursor()
try:
result = func(*p, **kw)
if autocommit:
conn.commit()
return result
except:
conn.rollback()
raise
finally:
pool.putconn(conn)
def wrapped(*p, **kw):
conn = pool.getconn()
kw[keyword] = conn.cursor()
try:
result = func(*p, **kw)
if autocommit:
conn.commit()
return result
except:
conn.rollback()
raise
finally:
pool.putconn(conn)
def _execute_query(query: str, json_output: bool = False, data=None) -> any:
"""
Helper function to execute any query and fetches all rows
:param query: Query string in SQL
:param json_output: True when return type is expected to be of JSON format
:return: None if query unsuccessful,
list of tuples for SELECT query
number of rows updated for UPDATE query
number of rows inserted for INSERT query
"""
conn = connect_db()
if conn:
try:
cur = conn.cursor()
if json_output:
json_query = """SELECT array_to_json(array_agg(row_to_json(t))) FROM ({}) t"""
query = json_query.format(query)
if data:
cur.execute(query, data)
else:
cur.execute(query)
logging.info('Executed: ' + query)
res = cur.rowcount
if re.fullmatch(r"^SELECT.*", query, re.IGNORECASE):
if json_output:
res = cur.fetchone()
else:
res = cur.fetchall()
logging.info('Returned: ' + str(res))
conn.commit()
cur.close()
return res
except psycopg2.ProgrammingError as e:
logging.error('Something went wrong with the query: %s', e)
return None
except psycopg2.IntegrityError as e:
logging.error('Something went wrong with the query: %s', e)
return None
except psycopg2.OperationalError as e:
logging.error('Operational error: %s', e)
return None
finally:
if conn: # if an error occurred after picking a connection
pool.putconn(conn)
return None
def postgres_session(pool):
engine = pool.getconn()
session = engine.cursor()
try:
yield session
session.close()
except Exception as e:
session.close()
engine.rollback()
raise e
finally:
session.close()
engine.commit()
pool.putconn(engine)
def execute(sql, commit=True):
try:
conn = pool.getconn()
except psycopg2.Error as e:
raise e
with conn.cursor() as cur:
try:
cur.execute(sql)
if commit:
conn.commit()
except psycopg2.Error as e:
raise e
finally:
pool.putconn(conn)
def query(sql):
rows = []
try:
conn = pool.getconn()
except psycopg2.Error as e:
raise e
with conn.cursor() as cur:
try:
cur.execute(sql)
rows = cur.fetchall()
except psycopg2.Error as e:
raise e
finally:
pool.putconn(conn)
return rows
def query_with_pool(pool, query):
# Use getconn() method to Get Connection from connection pool
ps_connection = pool.getconn()
if (ps_connection):
print("successfully recived connection from connection pool ")
results = execute_read_query(ps_connection, query)
#Use this method to release the connection object and send back ti connection pool
pool.putconn(ps_connection)
print("Put away a PostgreSQL connection")
return results
def updated_artists():
connection = pool.getconn()
cursor = connection.cursor()
props = {'currentPage': 'artists'}
query = 'WITH "posts" as (select "user", "service", max("added") from "booru_posts" group by "user", "service" order by max(added) desc limit 50) '\
'select "user", "posts"."service", "lookup"."name", "max" from "posts" inner join "lookup" on "posts"."user" = "lookup"."id"'
cursor.execute(query)
results = cursor.fetchall()
cursor.close()
if connection:
pool.putconn(connection)
response = make_response(
render_template('updated.html', props=props, results=results), 200)
response.headers[
'Cache-Control'] = 'max-age=60, public, stale-while-revalidate=2592000'
return response
def postgres_session(
pool: psycopg2.pool.SimpleConnectionPool,
) -> ContextManager[psycopg2.extensions.cursor]:
engine = pool.getconn()
session = engine.cursor()
try:
yield session
session.close()
except Exception as e:
session.close()
engine.rollback()
raise e
finally:
session.close()
engine.commit()
pool.putconn(engine)
def tr_complete(self, context):
cur = context.db
cur.connection.commit()
pool.putconn(cur.connection)
def closeConnection(con):
global pool
pool.putconn(con)
# clear old data
cur.execute("DELETE FROM word_definition WHERE wid = %s", (wid, ))
cur.execute("DELETE FROM word_variants WHERE wid = %s", (wid, ))
# request bing server for word detail
word = bing.search(word)
if None == word:
return False
# pronounce
prus = word["pronounces"].get("us")
preng = word["pronounces"].get("eng")
cur.execute("UPDATE word SET pronounce_eng = %s, pronounce_us = %s WHERE wid = %s", (preng, prus, wid))
# definitions
params = ((wid, defi["pos"], defi["def"]) for defi in word["definitions"])
cur.executemany("INSERT INTO word_definition (wid, type, definition) VALUES (%s, %s, %s)", params)
# variants
params = ((wid, v["kind"], v["word"]) for v in word["variants"])
cur.executemany("INSERT INTO word_variants (wid, kind, word) VALUES (%s, %s, %s)", params)
return True
else:
return None
conn = pool.getconn()
try:
checkandinit(conn)
except StandardError:
pass
finally:
pool.putconn(conn)
del conn
def putConnectionWithCursor(dbConn, dbCursor): dbCursor.close() pool.putconn(dbConn)
def tr_complete(self):
cur = env.context.pgcursor
cur.connection.commit()
pool.putconn(cur.connection)
