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 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 _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)