def test_threaded_connections(self): with pg_simple.PgSimple() as db: self._drop_tables(db) self._create_tables(db, fill=True) threads = [] # Create new threads for i in range(20): t = PgSimpleThread(i, 'thread-' + str(i), i, self) threads.append(t) # Start new Threads for t in threads: t.start() # Wait for all threads to complete for t in threads: t.join() # Drop tables with pg_simple.PgSimple() as db: self._drop_tables(db) print "Exiting Main Thread \n"
def database_operations(self): with pg_simple.PgSimple() as db: self.test_cls._check_table(db, 'pg_t1') self.test_cls._truncate_tables(db) self.test_cls._populate_tables(db) time.sleep(1)
def query(): """ Load all users """ with pg_simple.PgSimple() as db: users = db.fetchall('users', fields=['id', 'name'], order=['id', 'ASC']) return {"content": users}
def load(id): """ Load user from id """ with pg_simple.PgSimple() as db: user = db.fetchone('users', fields=['id', 'name'], where=('id = %s', [id])) return user
def login(): bcrypt = Bcrypt(current_app) if request.method == 'GET': return render_template('login.html') # Get username from form username = request.form['username'] # Get authentication details with pg_simple.PgSimple() as db: auths = db.fetchone(userTable, fields=['password', 'api'], where=('name = %s', [username])) try: # Check password salt if bcrypt.check_password_hash(auths.password, request.form['password']): session['user'] = auths.api # API key in session session['username'] = username # username in session return redirect(url_for('stats')) except (ValueError, TypeError, AttributeError) as e: # Non-existent username pass messages("401") return redirect(url_for('authentication.login'))
def test_connection_auto_commit(self): import code import sys with pg_simple.PgSimple() as db: if sys.argv.count('--interact'): db.log = sys.stdout code.interact(local=locals()) else: self._drop_tables(db) self._create_tables(db, fill=True) with pg_simple.PgSimple() as db: try: self._check_table(db, 'pg_t1') finally: self._drop_tables(db)
def check_granule_identifier(self, granule_identifier): with pg_simple.PgSimple() as db: collection = db.fetchone(self.schema + '.product', fields=['"eoIdentifier"'], where=('"eoIdentifier" = %s', [granule_identifier])) if collection == None: return False else: return True
def wrapper(*args): arg = args[0] # Get the Record object since wrapper acquires a tuple # Authentication: check if username exists and correct API key is # provided providedKey = arg.key username = arg.username try: with pg_simple.PgSimple() as db: key = db.fetchone( 'users', fields=['api'], where=('name = %s', [username]) )[0][ 0] # delete the last zero once 24-char API keys are implemented latest = db.fetchone('data', fields=['datetime'], where=('api = %s', [key]), order=['datetime', 'DESC'])[0] if providedKey != key: return 401 except TypeError: return 401 # Parse datetime or completely reject request if incorrect (because # where would you place it on a graph?) try: d = datetime.strptime(arg.timestamp, '%Y-%m-%d,%H:%M:%S') if not isinstance(d, datetime): return 400 if d - latest < timedelta(minutes=15): return 403 except (ValueError, TypeError): return 400 # The speed data validation # 0.0 values in case of an error will still indicate connection # and point out a client-side problem with speedtest visually on a graph # These ones are separate so in case one breaks the rest is still saved try: arg.download = float(re.findall('([0-9]+\.[0-9]+)', \ arg.download)[0]) except: arg.download = 0.0 try: arg.upload = float(re.findall('([0-9]+\.[0-9]+)', arg.upload)[0]) except: arg.upload = 0.0 try: arg.ping = float(re.findall('([0-9]+\.[0-9]+)', arg.ping)[0]) except: arg.ping = 0.0 return func(arg)
def persist_product_search_params(self, dict_to_persist, collection_name): with pg_simple.PgSimple() as db: collection = db.fetchone(self.schema + '.collection', fields=['"eoIdentifier"'], where=('"eoIdentifier" = %s', [collection_name])) if collection is None: raise LookupError("ERROR: No related collection found!") dict_to_persist['"eoParentIdentifier"'] = collection.eoIdentifier row = db.insert(self.schema + '.product', data=dict_to_persist, returning='id') db.commit() return row.id
def saveToDatabase(record): with pg_simple.PgSimple() as db: db.insert( "data", { "datetime": record.timestamp, "download": record.download, "upload": record.upload, "ping": record.ping, "api": record.key, "ip": record.ip, "provider": record.provider }) db.commit() return 201
def execute_query(self, query: str, return_value: bool = True, params: list = None) -> list: logging.info(query) try: with pg_simple.PgSimple(self.connection_pool) as _db: execute = _db.execute(query, params) _db.commit() if return_value: return execute.fetchall() except Exception as error: logging.error(query) logging.exception(error) return []
def get_product_OGC_BBOX(self, granule_identifier): with pg_simple.PgSimple(nt_cursor=False) as db: result = db.execute('SELECT ST_Extent(footprint::geometry) as ext FROM ' + self.schema + '.product WHERE "eoIdentifier" = %s', [granule_identifier]) bbox = result.fetchone()[0] bbox = bbox[4:-1] bbox = bbox.replace(",", " ") bbox = bbox.split(" ", -1) if float(bbox[0]) > float(bbox[2]): tmp = bbox[0] bbox[0] = bbox[2] bbox[2] = tmp if float(bbox[1]) > float(bbox[3]): tmp = bbox[1] bbox[1] = bbox[3] bbox[3] = tmp return bbox
def test_basic_functions(self): import code import doctest import sys db = pg_simple.PgSimple() if sys.argv.count('--interact'): db.log = sys.stdout code.interact(local=locals()) else: try: # Setup tables self._drop_tables(db) self._create_tables(db, fill=True) # Run tests doctest.testmod(optionflags=doctest.ELLIPSIS) finally: # Drop tables self._drop_tables(db) self.assertEqual(True, True)
def passwordChange(): bcrypt = Bcrypt(current_app) if request.method == 'POST': # means someone wants to change password: with pg_simple.PgSimple() as db: # Fetch current password auth = db.fetchone(userTable, fields=['password'], where=('name = %s', [session['username']])) # If correct, generate hash for new one and update DB if bcrypt.check_password_hash(auth.password, request.form['oldPassword']): np = bcrypt.generate_password_hash(request.form['newPassword']) db.update(userTable, data={'password': np}, where=('name = %s', [session['username']])) db.commit() messages('200') else: # Invalid old password (hashes mismatch) messages('401') return redirect(url_for('userPanel'))
def get_products_id(self): with pg_simple.PgSimple() as db: id_list = db.fetchall(self.schema + '.product', fields=['"id"', '"eoIdentifier"']) return id_list
import pg_simple from config import host, port, database pool = pg_simple.config_pool(host=host, port=port, database=database, user="******", password="******") # logging in uses it's own initialization of PgSimple, this one is left for # `stats`. `login` page needs to reestablish connection after logging out, but # `stats` gets it only once and it doesn't get cleared in the meantime. `login` # one does though, because it gets the session cleared? db = pg_simple.PgSimple() # insert into data (datetime, download, upload, ping, api, ip, provider) values ('2017-09-20 22:50:40', 31.29, 2.78, 22.371, '3', '109.173.147.181', 'INEA');
def query(host, start_time, end_time): query, bind_params = buildquery(host, start_time, end_time) start = time.time() with pg_simple.PgSimple(CONNECTION_POOL) as db: db.execute(query, bind_params) return time.time() - start
def persist_collection(self, dict_to_persist): with pg_simple.PgSimple() as db: row = db.insert(self.schema + '.collection', data=dict_to_persist) db.commit()
def persist_product_metadata(self, xml_doc, id): with pg_simple.PgSimple() as db: db.insert(self.schema + '.product_metadata', data={'metadata':xml_doc, 'mid':id}) db.commit()
def persist_thumb(self, thumb_blob, id): with pg_simple.PgSimple() as db: db.insert(self.schema + '.product_thumb', data={'thumb':Binary(thumb_blob), 'tid':id}) db.commit()
import pg_simple import sys import datetime connection_pool = pg_simple.config_pool( database='wms', host='localhost', port='54321', user='******', password='******', expiration=120) # idle timeout = 120 seconds db = pg_simple.PgSimple(connection_pool, log=sys.stdout, log_fmt=lambda x: '>> %s' % (x if isinstance(x, str) else x.query), nt_cursor=True) # dropping and creating tables db.drop('books') db.create( 'books', ''' "id" SERIAL NOT NULL, "genre" VARCHAR(20) NOT NULL, "name" VARCHAR(40) NOT NULL, "price" MONEY NOT NULL, "published" DATE NOT NULL, "modified" TIMESTAMP(6) NOT NULL DEFAULT now() ''')
def persist_ogc_links(self, list, id): with pg_simple.PgSimple() as db: for dict in list: dict['product_id'] = id db.insert(self.schema + '.product_ogclink', data=dict) db.commit()
def update_original_package_location(self, safe_pkg_name, tile_id): with pg_simple.PgSimple() as db: db.update(self.schema + '.product', data={'"originalPackageLocation"':wf.original_package_location_path + safe_pkg_name.replace("SAFE","zip")}, where=('"eoIdentifier" = %s', [tile_id])) db.commit()
def db(): pg_simple.PgSimple._connect = lambda x: 0 return pg_simple.PgSimple(pool=None)