def db_init(): connection_url = ('postgresql://' + os.environ['db_user'] + ':' + os.environ['db_password'] + '@postgres:' + os.environ['db_port'] + '/' + os.environ['db_database']) print('Initializing Postgres Object...') db = postgres.Postgres(url=connection_url) print('Ensuring timescaledb ext. is enabled') db.run("CREATE EXTENSION IF NOT EXISTS timescaledb;") print("Ensuring tables are setup properly") db.run(""" CREATE TABLE IF NOT EXISTS can ( time timestamptz NOT NULL, can_interface text NOT NULL, can_id text NOT NULL, can_data text NOT NULL);""") print("Ensuring can data table is a timescaledb hypertable") db.run(""" SELECT create_hypertable('can', 'time', if_not_exists => TRUE, migrate_data => TRUE);""") print("Finished setting up tables") return db
def create_tileset(city_code, layer_name): # Ждём, когда освободится очередь на загрузку while True: if check_queue_status() == 'OK': break else: time.sleep(60) # Создаём tileset tile_id = userName + "." + city_code + "-" + layer_name create_cmd = "tilesets create " + tile_id + " --recipe recipe.json --name " + city_code + "-" + layer_name result = run_cmd(create_cmd) # Добавляем в очередь новый слой tiles_queue.append(tile_id) # Удаляем tileset, если такой уже есть if "already exists" in json.loads(result)["message"]: del_cmd = "tilesets delete " + tile_id + " -f" run_cmd(del_cmd) run_cmd(create_cmd) # Публикуем tileset pub_cmd = "tilesets publish " + tile_id run_cmd(pub_cmd) # Загружаем связь города с вектором в БД p = postgres.Postgres() p.upload_layer(layer_name) # Удаляем файл рецепта #remove_recipe() return tile_id
def summarize_events(): db = postgres.Postgres(DB) for suffix in TABLE_SUFFIXES: db.run(Q_DAILY_DEVICES_CREATE_TABLE.format(suffix=suffix)) db.run(Q_MD_USERS_CREATE_TABLE.format(suffix=suffix)) day_first = db.one(Q_GET_FIRST_AVAILABLE_DAY.format(suffix=suffix)) # Summarize the latest days that are not yet summarized. day_from = db.one(Q_GET_FIRST_UNPROCESSED_DAY.format(suffix=suffix)) if day_from is None: day_from = day_first if day_from is None: raise RuntimeError('no events in db') day_until = db.one(Q_GET_LAST_AVAILABLE_DAY.format(suffix=suffix)) days = {"day_from": day_from, "day_until": day_until, "suffix": suffix} print "SUMMARIZING FROM", day_from, "UNTIL", day_until, "FOR SUFFIX", suffix # Update daily device activity. print " UPDATING DAILY ACTIVE DEVICES SUMMARY" db.run(Q_DAILY_DEVICES_CLEAR.format(**days)) db.run(Q_DAILY_DEVICES_SUMMARIZE.format(**days)) # Update multi-device-user assessments. print " UPDATING MULTI-DEVICE USERS SUMMARY" db.run(Q_MD_USERS_CLEAR.format(**days)) db.run(Q_MD_USERS_SUMMARIZE.format(**days)) # Expire old data print "EXPIRING", day_first, "FOR SUFFIX", suffix db.run( Q_DAILY_DEVICES_EXPIRE.format(suffix=suffix, day_first=day_first)) db.run(Q_MD_USERS_EXPIRE.format(suffix=suffix, day_first=day_first)) print "VACUUMING daily_activity_per_device{suffix} AND daily_multi_device_users{suffix}".format( suffix=suffix) db.run(Q_VACUUM_TABLES.format(suffix=suffix))
def import_events(force_reload=False): s3 = boto.s3.connect_to_region(S3_REGION).get_bucket(S3_BUCKET) db = postgres.Postgres(DB_URI) db.run(Q_DROP_CSV_TABLE) db.run(Q_CREATE_COUNTS_TABLE) days = [] for key in s3.list(prefix=S3_PREFIX): filename = path.basename(key.name) day = "-".join(filename[:-4].split("-")[-3:]) date = datetime.strptime(day, "%Y-%m-%d") if date >= COUNTS_BEGIN: if force_reload: days.append(day) else: if not db.one(Q_CHECK_FOR_DAY.format(day=day)): days.append(day) days.sort(reverse=True) print "FOUND", len(days), "DAYS" for day in days: print day print " COPYING CSV" db.run(Q_CREATE_CSV_TABLE) db.run(Q_COPY_CSV.format(day=day)) print " CLEARING" db.run(Q_CLEAR_DAY.format(day=day)) print " INSERTING" db.run(Q_INSERT_COUNTS) db.run(Q_DROP_CSV_TABLE) print "VACUUMING" db.run(Q_VACUUM_COUNTS)
def geocode_addresses_in_threads(city_code,houses,threads_num): print("Start get houses location. Time:",datetime.now()) p = postgres.Postgres() bbox_list = p.send_query("select bbox from cities where code='"+city_code+"'")[0] bbox = ','.join([str(i) for i in bbox_list]) manager = multiprocessing.Manager() results = manager.list() chunks = numpy.array_split(houses,threads_num) processes = [] for i in range(0,threads_num): print("Houses location. Part: "+str(i+1)+" len: "+str(len(chunks[i]))) p = multiprocessing.Process(target=geocode_addresses, args=(chunks[i], results, str(i+1), bbox)) processes.append(p) p.start() for process in processes: process.join() process.close() print("Finish get houses location. Time:",datetime.now()) return list(results)
def run(m, cfg): host = cfg.primaryHost loadgen = cfg.postgresClient m += host m += loadgen m += HostInfo(host) # Creating the db takes time, so we don't clean the file # system. We avoid any cruft that may be there already by # putting the DB in a subdirectory. fs = FileSystem(host, cfg.fs, clean = False) m += fs dbdir = fs.path + "0/postgres" pgPath = os.path.join(cfg.benchRoot, "postgres") pgBuild = getBuild(cfg) pgOpts = {"shared_buffers": postgres.PGVal(cfg.bufferCache, "MB")} log2NumLockPartitions = int(math.log(cfg.lockPartitions, 2)) if cfg.lockPartitions != 1 << log2NumLockPartitions: raise ValueError("numLockPartitions must be a power of 2, got %r" % cfg.numLockPartitions) pgOpts["log2_num_lock_partitions"] = log2NumLockPartitions if cfg.sleep == "sysv": pgOpts["semas_per_set"] = cfg.semasPerSet pg = postgres.Postgres(host, pgPath, pgBuild, dbdir, malloc = cfg.malloc, **pgOpts) m += postgres.InitDB(host, pg).addTrust(loadgen) m += pg if cfg.hotplug: # Because the number of cores and the number of clients is # the same, we don't strictly need hotplug m += SetCPUs(host = host, num = cfg.cores) # XXX Make configurable (at least iface name) # m += IXGBE(host, "eth0", queues = "n*NCPU/(NRX if rx else NTX)") # The ixgbe driver assigns flows to queues sequentially. # Since we only have cfg.cores flows, make sure a sequential # assignment spans all the online cores. However, this does # not spread things out if we have more queues than cores. m += IXGBE(host, "eth0", queues = "n%min(NCPU, NRX if rx else NTX)") sysmon = ExplicitSystemMonitor(host) m += sysmon for trial in range(cfg.trials): m += PostgresLoad(loadgen, trial, pg, cfg.cores, cfg.cores, cfg.rows, cfg.partitions, cfg.batchSize, cfg.randomWritePct, sysmon) m.run()
def connect_to_database(self) -> None: """ Create a New Database. """ logger.info("connecting to database") self.db = postgres.Postgres(config.database) logger.info("creating missing tabels") self.db.run( """ CREATE TABLE IF NOT EXISTS settings ( server_id BIGINT PRIMARY KEY, prefix text, modlog BIGINT ) """ )
def connect_db(): import postgres import os import sys from time import sleep from psycopg2 import OperationalError # Try to connect to the DB. It may be starting up so we should try a few timees # before failing. Currently trying every second 60 times tries = 0 maxtries = 60 sleeptime = 1 db_connected = False connectionurl = 'postgresql://' + os.environ['db_user'] + ':' + os.environ['db_password'] + \ '@postgres:' + os.environ['db_port'] + '/' + os.environ['db_database'] while (not db_connected): try: print("Attempting connection to database") db = postgres.Postgres(url=connectionurl) except OperationalError as e: if (tries < maxtries): print("Database connection attempt", tries, "failed. Database may still be starting. Sleeping", sleeptime, "s and trying again") print(e) tries = tries + 1 sleep(sleeptime) else: print("FATAL: Could not connect to db after", tries, "tries. Exiting") sys.exit(-1) else: print("Connection successful") db_connected = True setup_db_tables(db) sys.stdout.flush() return db
#Initialize postgres database if "CELL_LOG" environment variable is set to log #to database. if((log_env == 'DB') or (log_env == 'DB,CSV') or (log_env == 'CSV,DB')): import postgres global db connection_url = ('postgresql://' + os.environ['db_user'] + ':' + os.environ['db_password'] + '@postgres:' + os.environ['db_port'] + '/' + os.environ['db_database'] ) print('Initializing Postgres Object...') db = postgres.Postgres(url = connection_url) print('Ensuring timescaledb ext. is enabled') db.run("CREATE EXTENSION IF NOT EXISTS timescaledb;") print("Ensuring tables are setup properly") db.run(""" CREATE TABLE IF NOT EXISTS cell ( time timestamptz UNIQUE NOT NULL, signal int2 NOT NULL, cell_tech text NOT NULL);""") print("Ensuring cell data table is a timescaledb hypertable") db.run(""" SELECT create_hypertable('cell', 'time', if_not_exists => TRUE, migrate_data => TRUE);""")
def main(arguments: argparse.Namespace) -> ExitCode: logger.remove() logger.add(sys.stderr, level=arguments.logging_level) logger.debug("Called with {!r}", arguments) if not (arguments.postgres_connection_string and arguments.synapse_auth_token): logger.error( "Postgres connection string and Synapse auth token can not be empty" ) return ExitCode.Failure try: db = postgres.Postgres(arguments.postgres_connection_string) except (psycopg2.OperationalError, psycopg2.InternalError): logger.exception( "Connecting to database using {!r} failed:", arguments.postgres_connection_string, ) return ExitCode.Failure session = requests.session() session.headers["Authorization"] = "Bearer " + arguments.synapse_auth_token before_date = get_delta_date(int(arguments.delta)) before_date_string = before_date.isoformat(sep=" ", timespec="seconds") logger.info("Purging events up to: {} UTC", before_date_string) # Purge room history for all rooms rooms = get_room_record_ids(db) room_count = len(rooms) for index, room_id in enumerate(rooms, start=1): logger.info("({}/{}) Processing room: {!r}...", index, room_count, room_id) event_id = get_last_event_id(db, room_id, before_date) if event_id is None: logger.info( "No event ID before: {} UTC for room: {!r}, skipping", before_date_string, room_id, ) continue logger.info( "Last event ID before: {} UTC for room {!r}: {!r}", before_date_string, room_id, event_id, ) purge_id = purge_history(session, arguments.api_url, room_id, event_id) if not purge_id: logger.warning("Failed to purge room: {!r}: received no purge ID", room_id) continue logger.info("Purging room: {!r} in progress: {!r}...", room_id, purge_id) result = wait_for_purge(session, arguments.api_url, purge_id) logger.info("Purged room: {!r} with status: {!r}", room_id, result) logger.info("Purging local media older than: {} UTC...", before_date_string) local_media = get_local_media_record_ids(db, before_date) important_files = get_important_media_ids(db) # Purge local media manually old_media = set(local_media) - important_files old_media_count = len(old_media) logger.info("{} media to be cleaned...", len(old_media)) for index, media_id in enumerate(old_media, start=1): logger.info("({}/{}) Processing media: {!r}...", index, old_media_count, media_id) paths = get_local_media_paths(arguments.media_store, media_id) for path in paths: if not os.path.isfile(path): logger.debug("{!r} could not be found or is not a file", path) continue os.remove(path) delete_local_media_record(db, media_id) # Purge remote media logger.info("Purging cached remote media older than: {} UTC...", before_date_string) result = purge_remote_media(session, arguments.api_url, before_date) logger.info("Purged cached remote media: {!r}", result) return ExitCode.Success
import odo import pycomic imp.reload(pycomic) '''Uploads from .csv to comics objects, then goes into db''' # get list of comics objects from .csv df1=pd.read_csv('~/Desktop/Comics/comics_db.csv') df=df1.where((pd.notnull(df1)),None) # replace nan with Nones library=pycomic.df_to_objects(df) # initialize Database db=postgres.Postgres('postgresql://*****:*****@localhost/test') # DB table already created, you have been warned #db.run('CREATE TABLE comics (title varchar(80), volume int, issue int, issue_end int, year int, arc varchar(80), publisher varchar(30), size real, comments varchar(120), read bool, story_rank real, art_rank bool, filename varchar(80), path varchar(120));') #db.run('CREATE TABLE stupid (x varchar(90), y INT);') #db.run('COPY comics (title) FROM /Users/ksakamoto/Destop/Comics/comics_db.csv WITH csv') fil='/Users/ksakamoto/Documents/comics.csv' f=odo.resource(fil) dshape=odo.discover(f) t=odo.odo(fil, 'postgresql://*****:*****@localhost/test::comics', dshape=dshape)
';sban <UserID> <Reason (optional)> - Bans the given user without a message.\n' ';tempban <UserID> <Ban Length> <Reason (optional)> - Bans the given user with a message for the given amount of time. Ban length can be in hours or of the form "XwXdXh"\n' ';banstatus <UserID> - returns how long a tempbanned user will remain banned.\n' ';unban <UserID> - Unbans the given user.\n' ';locate <Regex> - Searches the user list for users with Names and Nicknames that match the given Regex. If the Regex given is a number, it also searches discriminators.\n' ';clear <MessageID (optional)> - Marks each message above this or the given message as read, until hitting a marked message, or a message not sent by me.' ) warninglist = [] removelist = [] mutelist = [] watchlist = {} exceptionlist = [] mention_dict = loadMentions() keywordsFile = loadKeywords() db = postgres.Postgres(url=os.environ.get('DATABASE_URL')) db.run("CREATE TABLE IF NOT EXISTS forbidden (words text)") db.run("CREATE TABLE IF NOT EXISTS vile (words text)") db.run("CREATE TABLE IF NOT EXISTS automute (words text)") db.run("CREATE TABLE IF NOT EXISTS tempbans (id bigint PRIMARY KEY, time int)") db.run( "CREATE TABLE IF NOT EXISTS usernotes (id bigint PRIMARY KEY, linkedact text, notes text)" ) db.run("CREATE TABLE IF NOT EXISTS exempteds (id bigint)") client = discord.Client() mainServer = None commandChn = None reportChn = None removeChn = None logChn = None
def initialize(self): self.args = None logger.debug("connecting to postgres") self.pg = postgres.Postgres()
stations_file.close() # Записываем результат в файл os.system("mkdir ../out/stops_distance") geopandas.GeoDataFrame(features).to_file( "../out/stops_distance/stops_distance.geojson", driver='GeoJSON') # =========================== if __name__ == '__main__': print("Start", datetime.now()) # Читаем параметры из файла параметров init() postgres = postgres.Postgres() #== Step 1: Загрузка данных по маршрутам и остановкам # # Step 1.1: Загрузка маршрутов и остановок # load_route_and_stations() # # Step 1.2: Выгрузка в формат geojson остановок и маршрутов # generate_stations_geojson() # generate_routes_geojson() # # Step 1.4: Загрузка данных в БД # postgres.upload_city() # postgres.upload_stations() # postgres.upload_routes() # postgres.upload_lnk_station_routes()
async def run(loop): print('Connecting to the database') connectionurl = 'postgresql://' + os.environ['db_user'] + ':' + os.environ['db_password'] + \ '@postgres:' + os.environ['db_port'] + '/' + os.environ['db_database'] # Try to connect to the DB. It may be starting up so we should try a few times # before failing. Currently trying every second 60 times tries = 0 maxtries = 60 sleeptime = 1 db_connected = False while not db_connected: try: db = postgres.Postgres(url=connectionurl) except OperationalError as e: if tries < maxtries: print('Database connection attempt', tries, 'failed. Database may still be starting. Sleeping', sleeptime, 's and trying again') print(e) tries = tries + 1 time.sleep(sleeptime) else: print('FATAL: Could not connect to db after', tries, 'tries. Exiting') sys.exit(-1) else: print('DB successfully connected') db_connected = True # Sleep for 2 seconds to allows gps2tsdb to create the gps table time.sleep(5) # This should be replaced query that deletes all lines from the device 'fake gps' or whatever but until # gps device is recorded this is what we got print('Truncating GPS database') db.run('TRUNCATE gps;') print('Setting up NATS') # Connect to NATS server nc = NATS() await nc.connect('nats://nats:4222') # Notifies the non-callback function that we are finished # Yeah I know it's not pythonic. Open a PR smart guy fin = asyncio.Future() async def message_handler(msg): nats_gps_point = json.loads(msg.data.decode()) nonlocal fin # Debugging #print('NATS type:', type(nats_gps_point), '\nmessage:', nats_gps_point) # Check if this is a control message signifying the end of the transmission if nats_gps_point == 'END': print('End control message received. No more gps points to check') fin.set_result(message_handler.points_received) return # Check for NAT's point print('Verifying that nats', nats_gps_point['time'], ' is in the database') rst = db.one('SELECT * FROM gps where time = %s;', (nats_gps_point['time'], )) if rst is not None and len(rst) == 3 and rst.lat == nats_gps_point[ 'lat'] and rst.lng == nats_gps_point['lon']: print('nats timestamp', nats_gps_point['time'], 'successfully entered into the database') else: print('nats GPS point', nats_gps_point, '\nwas not successfully entered into database!\ndb entry:', rst) fin.set_result(-1) sys.exit(-1) # Counter just for fun if not hasattr(message_handler, 'points_received'): message_handler.points_received = 0 # it doesn't exist yet, so initialize it message_handler.points_received = message_handler.points_received + 1 sys.stdout.flush() return print('setting up gps message callback') sid = await nc.subscribe('gps', cb=message_handler) print('gps message callback setup') sys.stdout.flush() await fin # If fin is a positive number, the test was successful and the number is the number of points # verified. If it is negative, the test failed if fin.result() > 0: print('End message received. Exiting') print('Successfully verified %d points' % fin.result()) else: print('Test failed') await nc.close()
def run(s3_prefix, event_type, temp_schema, temp_columns, perm_schema, perm_columns, id_column="uid", before_import=nop, after_day=nop, after_import=nop, day_from=None, day_until=None): def drop_temporary_table(): db.run(Q_DROP_TEMPORARY_TABLE.format(event_type=event_type)) def create_events_tables(): for rate in SAMPLE_RATES: db.run(Q_CREATE_EVENTS_TABLE.format(event_type=event_type, suffix=rate["suffix"], schema=perm_schema)) def get_max_day(): result = db.one(Q_GET_MAX_DAY.format(event_type=event_type)) if result: return datetime.strftime(result, "%Y-%m-%d") return result def is_candidate_day(day): return (not day_from or day_from <= day) and (not day_until or day_until >= day) def is_day_populated(day): return bool(db.one(Q_CHECK_FOR_DAY.format(event_type=event_type, day=day))) def get_unpopulated_days(): days = [] message = "FINDING UNPOPULATED DAYS" if day_from: message += " FROM {day_from}".format(day_from=day_from) if day_until: message += " UNTIL {day_until}".format(day_until=day_until) print message for key in s3.list(prefix=s3_prefix): filename = path.basename(key.name) # Ignore the last four characters (".csv") then join the last # three parts ("YYYY-MM-DD") of the hyphen-split string. day = "-".join(filename[:-4].split("-")[-3:]) if is_candidate_day(day) and not is_day_populated(day): days.append(day) return days def get_timestamp(which): return db.one(Q_GET_TIMESTAMP.format(which=which, event_type=event_type)) def print_timestamp(which): print " {which} timestamp".format(which=which), get_timestamp(which) def import_day(day): print day print " COPYING CSV" db.run(Q_CREATE_CSV_TABLE.format(event_type=event_type, schema=temp_schema)) s3_path = s3_uri.format(day=day) db.run(Q_COPY_CSV.format(event_type=event_type, columns=temp_columns, s3_path=s3_path, CREDENTIALS=CREDENTIALS)) print_timestamp("MIN") print_timestamp("MAX") for rate in SAMPLE_RATES: print " ", TABLE_NAMES["perm"].format(event_type=event_type, suffix=rate["suffix"]) print " CLEARING" db.run(Q_CLEAR_DAY.format(event_type=event_type, suffix=rate["suffix"], day=day)) print " INSERTING" db.run(Q_INSERT_EVENTS.format(event_type=event_type, columns=perm_columns, id_column=id_column, suffix=rate["suffix"], percent=rate["percent"], day=day, max_day=max_day, months=rate["months"])) after_day(db, day, TABLE_NAMES["temp"].format(event_type=event_type), TABLE_NAMES["perm"].format(event_type=event_type, suffix="{suffix}"), SAMPLE_RATES) drop_temporary_table() def expire_events(): for rate in SAMPLE_RATES: table_name = TABLE_NAMES["perm"].format(event_type=event_type, suffix=rate["suffix"]) print "EXPIRING", table_name, "FOR", max_day, "+", rate["months"], "MONTHS" db.run(Q_DELETE_EVENTS.format(event_type=event_type, suffix=rate["suffix"], day=max_day, months=rate["months"])) print "VACUUMING AND ANALYZING", table_name db.run(Q_VACUUM_TABLES.format(event_type=event_type, suffix=rate["suffix"])) s3 = boto.s3.connect_to_region("us-west-2").get_bucket(S3_BUCKET) db = postgres.Postgres(DB_URI) s3_uri = "s3://" + S3_BUCKET + "/" + s3_prefix + "-{day}.csv" before_import(db, SAMPLE_RATES) drop_temporary_table() create_events_tables() max_extant_day = get_max_day() if not day_from: day_from = max_extant_day unpopulated_days = get_unpopulated_days() unpopulated_days.sort(reverse=True) if max_extant_day > unpopulated_days[0]: max_day = max_extant_day else: max_day = unpopulated_days[0] print "FOUND", len(unpopulated_days), "DAYS" for day in unpopulated_days: import_day(day) expire_events() after_import(db, SAMPLE_RATES, max_day)
SMS_SEND_TEMPLATE = """<request> <authentication apikey="{}" /> <data> <message> <sendername>CoronaKram</sendername> <text encoding="utf-8"><![CDATA[{}]]></text> <recipients> <msisdn>45{}</msisdn> </recipients> </message> </data> </request>""" db = postgres.Postgres("host={} user={} password={}".format( os.getenv("DB_HOST", "db"), os.getenv("DB_USER", "postgres"), os.getenv("DB_PASSWORD"), )) class Receiver(BaseModel): phone_number: str timestamp: str = None class Message(BaseModel): name: str = None text: str flag: bool = False receiver: str = None time: str = None