def dump_db(): """ Def used to save the data on Postgres DB/Table Note, here you can see the info as below "postgresql://*****:*****@server_running_postgres/bz" where --- Username ........: bz_user Password ........: bz_user_00 Server FQDN .....: server_running_postgres Database ........: bz --- """ print("Updating DB") db = Postgres( url='postgresql://*****:*****@server_running_postgres/bz') db.run("delete from bz_info") for item in bz_list: print("ID here: {}".format(item[1])) db.run("insert into bz_info values('" + TODAY_STR + "','" + str(item[1]) + "','" + str(item[2]) + "','" + str(item[3]) + "','" + str(item[4]) + "','" + str(item[5]) + "','" + str(item[6]) + "','" + str(item[7]) + "','" + str(item[8]) + "','" + str(item[9]) + "','" + str(item[10]) + "','" + str(item[11]) + "','" + str(item[12]) + "')")
def __init__(self, model, start, end, init_customers, growth, churn, mrr,seed): ''' Creates the behavior/utility model objects, sets internal variables to prepare for simulation, and creates the database connection :param model: name of the behavior/utility model parameters :param start: start date for simulation :param end: end date for simulation :param init_customers: how many customers to create at start date :param growth: monthly customer growth rate :param churn: monthly customer churn rate :param mrr: customer MRR ''' self.model_name=model self.start_date = start self.end_date = end self.init_customers=init_customers self.monthly_growth_rate = growth self.monthly_churn_rate = churn self.mrr=mrr self.behave_mod=GaussianBehaviorModel(self.model_name,seed) self.util_mod=UtilityModel(self.model_name,self.monthly_churn_rate,self.behave_mod) self.subscription_count = 0 self.tmp_sub_file_name = os.path.join(tempfile.gettempdir(),'{}_tmp_sub.csv'.format(self.model_name)) self.tmp_event_file_name=os.path.join(tempfile.gettempdir(),'{}_tmp_event.csv'.format(self.model_name)) self.db = Postgres("postgres://%s:%s@localhost/%s" % ( os.environ['CHURN_DB_USER'], os.environ['CHURN_DB_PASS'], os.environ['CHURN_DB'])) self.con = post.connect( database= os.environ['CHURN_DB'], user= os.environ['CHURN_DB_USER'], password=os.environ['CHURN_DB_PASS'])
def __init__(self): _user = getenv("DATABASE.USER") _secret = getenv("DATABASE.PASS") _host = getenv("DATABASE.HOST") _port = getenv("DATABASE.PORT") _dbname = getenv("DATABASE.DB") self.db = Postgres(url=f"postgresql://{_user}:{_secret}@{_host}:{_port}/{_dbname}")
def connect(): """ Connect to the PostgreSQL database server """ try: # connect to the PostgreSQL server print('Connecting to the PostgreSQL database...') # use a postgres connection string below db = Postgres( "host=localhost dbname=michaelmostachetti user=michaelmostachetti password=password" ) # This is where you will run your four queries # You can use either .one(), or .run() depending if you care # about the return values of the queries # Query 1 result1 = db.one("SELECT version();") print(result1) # Query 2 db.run("SELECT version();") # Query 3 db.run("SELECT version();") # Query 4 db.run("SELECT version();") except: print("An error occurred")
def main(): print("Reading configuration") config = read_config() print("Fetching data") raw_epc_data = get_epc_data(config.epc_data_url, True) print("Cleaning data") data = clean_data(raw_epc_data, config.data_columns) print("Writing data") # Wait for postgres to start ... should be handled at the infra level time.sleep(5) pg = Postgres(config) pg.write_data(data, config.postgres_table_name) print("Verifying data") print(pg.read_data()) print("Complete")
def setUp(self): # override self.db = Postgres(cursor_factory=self.cursor_factory) self.db.run("DROP SCHEMA IF EXISTS public CASCADE") self.db.run("CREATE SCHEMA public") self.db.run("CREATE TABLE foo (key text, value int)") self.db.run("INSERT INTO foo VALUES ('buz', 42)") self.db.run("INSERT INTO foo VALUES ('biz', 43)")
def setUp(self): # override self.db = Postgres(DATABASE_URL) self.db.run("DROP SCHEMA IF EXISTS public CASCADE") self.db.run("CREATE SCHEMA public") self.db.run("CREATE TABLE foo (bar text, baz int)") self.db.run("INSERT INTO foo VALUES ('buz', 42)") self.db.run("INSERT INTO foo VALUES ('biz', 43)")
def load_User_Me(): global STRING_DB global MY_CHAT_ID_TELEGRAM db = Postgres(STRING_DB) db.run( "INSERT INTO users (chat_id,name,time_added) VALUES ('{}','{}','{}') ON CONFLICT (chat_id) DO NOTHING ;" .format(MY_CHAT_ID_TELEGRAM, "@f126ck", "1503407762"))
class Test(unittest.TestCase): postgres = Postgres( connstring='postgresql://*****:*****@localhost:5432/test_user') def test_parse(self): connstring = 'postgresql://*****:*****@localhost:5432/test_user' user, pwd, host, port, db = self.postgres._parse(connstring) self.assertEqual(user, 'test_user') self.assertEqual(pwd, 'test') self.assertEqual(host, 'localhost') self.assertEqual(port, 5432) self.assertEqual(db, 'test_user') def test_connect(self): conn = self.postgres.get_connection() self.assertIsInstance(conn, psycopg2.extensions.connection) def test_query(self): query = """ SELECT * FROM advertising WHERE id=1 LIMIT 1 """ rows = self.postgres.execute_query(query) row_id = rows[0]['id'] self.assertIsInstance(rows, list) self.assertIsInstance(rows[0], psycopg2.extras.DictRow) self.assertEqual(row_id, 1) def test_disconnect(self): pass
def load_RSS_Feed_DB(): global STRING_DB db = Postgres(STRING_DB) selectList = db.all("SELECT * FROM feed;") allRssFeed = [item[1] for item in selectList] print("def load_RSS_Feed_DB():") print(allRssFeed) print("def load_RSS_Feed_DB():")
def setUp(self): self.db = Postgres(cache=Cache(max_size=1), cursor_factory=SimpleTupleCursor) self.db.run("DROP SCHEMA IF EXISTS public CASCADE") self.db.run("CREATE SCHEMA public") self.db.run("CREATE TABLE foo (key text, value int)") self.db.run("INSERT INTO foo VALUES ('a', 1)") self.db.run("INSERT INTO foo VALUES ('b', 2)")
def _restore_postgres(tempdir, config): ctx.logger.info('Restoring Postgres data') postgres = Postgres(config) queries = _clean_db_queries() dump_file = os.path.join(tempdir, _POSTGRES_DUMP_FILENAME) dump_file = postgres.prepend_dump(dump_file, queries) postgres.restore(dump_file) ctx.logger.debug('Postgres restored')
def __init__(self, db_url, batchsize=1000, processor_limit=5): self.db_url = db_url self.db = Postgres(url=db_url) self.batchsize = batchsize self.processor_limit = processor_limit self.process_count = 0 self.processors = [] self.offset = 0
def __init__(self, model, start, end, init_customers, seed): """ Creates the behavior/utility model objects, sets internal variables to prepare for simulation, and creates the database connection :param model: name of the behavior/utility model parameters :param start: start date for simulation :param end: end date for simulation :param init_customers: how many customers to create at start date """ self.model_name = model self.start_date = start self.end_date = end self.init_customers = init_customers self.monthly_growth_rate = 0.12 self.util_mod = UtilityModel(self.model_name) behavior_versions = glob.glob("../conf/" + self.model_name + "_*.csv") self.behavior_models = {} self.model_list = [] for b in behavior_versions: version = b[(b.find(self.model_name) + len(self.model_name) + 1):-4] if version in ("utility", "population", "country"): continue behave_mod = FatTailledBehaviorModel(self.model_name, seed, version) self.behavior_models[behave_mod.version] = behave_mod self.model_list.append(behave_mod) if len(self.behavior_models) > 1: self.population_percents = pd.read_csv( "../conf/" + self.model_name + "_population.csv", index_col=0) self.util_mod.setChurnScale(self.behavior_models, self.population_percents) self.population_picker = np.cumsum(self.population_percents) self.country_lookup = pd.read_csv("../conf/" + self.model_name + "_country.csv") self.subscription_count = 0 self.tmp_sub_file_name = os.path.join( tempfile.gettempdir(), "{}_tmp_sub.csv".format(self.model_name)) self.tmp_event_file_name = os.path.join( tempfile.gettempdir(), "{}_tmp_event.csv".format(self.model_name)) self.db = Postgres("postgres://%s:%s@localhost/%s" % ( os.environ["CHURN_DB_USER"], os.environ["CHURN_DB_PASS"], os.environ["CHURN_DB"], )) self.con = post.connect( database=os.environ["CHURN_DB"], user=os.environ["CHURN_DB_USER"], password=os.environ["CHURN_DB_PASS"], )
def Conectar(self) -> bool: try: self.con = Postgres( f"postgres://{self.user}:{self.password}@{self.host}:5432/{self.db}" ) except: return False finally: return True
def save_n_words(word_dict: dict): psql = Postgres() words = [] for item in word_dict.items(): words.append(item) psql.save_words(words) psql.close()
def __init__(self, drop_tables=False): super(PostgreSQLDB, self).__init__() if os.environ.get('DOCKERCLOUD_SERVICE_HOSTNAME', None) is not None: self.db = Postgres( u"postgres://*****:*****@postgres/hashes") else: # self.db = Postgres(u"postgres://*****:*****@localhost/postgres") self.db = Postgres( u"postgres://*****:*****@pervasivesounds.com/hashes" ) if drop_tables: self.db.run("DROP TABLE IF EXISTS %s CASCADE" % self.SONGS_TABLENAME) self.db.run("DROP TABLE IF EXISTS %s CASCADE" % self.FINGERPRINTS_TABLENAME) self.db.run(self.CREATE_SONGS_TABLE) self.db.run(self.CREATE_FINGERPRINTS_TABLE)
def main(): logger.info('Начало работы') for s in cfg.sections(): updflag = False logger.name = conf.APP_NAME + '.' + s try: conf.gc_sklitcode = cfg.get(s, 'gc_sklitcode') except configparser.NoOptionError: logger.info('Не указан код СКЛИТ в секции {}'.format(s)) pass try: host = cfg.get(s, 'host') user = cfg.get(s, 'localdb') workdir = cfg.get(s, 'workdir') with Postgres(host, user, conf.APP_NAME) as pgdb: ld = Loaders(pgdb) tmp_ttb_create(pgdb) summary = 0 for f in check_folder(workdir, get_mask_list(pgdb)): filename = workdir + os.sep + f['file'] try: count = ld.selector[f['format'].lower()](filename, f['mask']) if count - summary > 0: logger.info('Обработан файл {} строк: {}.'.format( f['file'], (count - summary))) summary = count if count > 0: updflag = True # даже если файл был пустой и из него ничего не записалось в базу - все равно перемещаем его чтоб не мешался move_parsed(workdir, f['file']) except Exception as e: logger.error( 'Произошла ошибка при попытке обработать файл {}: {}' .format(f['file'], e)) continue if updflag: logger.info( 'Обновлено {} привязок к кодам конкурентов.'.format( ld.rivalcodes_update())) logger.info('Записано {} строк в хранилище цен.'.format( ld.table_2bonus_insert())) ld.rivalconnections_update() except configparser.NoOptionError as e: logger.warning('Нет параметра {} в секции {}'.format( e.option, e.section)) continue except Exception as e: logger.error('Произошла ошибка {}'.format(e)) continue
def db(): dburl = os.environ['DATABASE_URL'] maxconn = int(os.environ['DATABASE_MAXCONN']) gittip.db = Postgres(dburl, maxconn=maxconn, strict_one=False) # register hstore type (but don't use RealDictCursor) with gittip.db.get_connection() as conn: curs = conn.cursor(cursor_factory=RegularCursor) psycopg2.extras.register_hstore(curs, globally=True, unicode=True) return gittip.db
def remove_obsevations(schema): ''' Truncate the active period and observtions tables to prepare for a new dataset generation :param schema: :return: ''' print('Removing old active_period and observation entries...') con_string = f"postgresql://localhost/{os.environ['CHURN_DB']}?user={os.environ['CHURN_DB_USER']}&password={os.environ['CHURN_DB_PASS']}" db = Postgres(con_string) db.run('truncate table %s.active_period' % schema) db.run('truncate table %s.observation' % schema)
def insert_RSS_Feed_DB(): global STRING_DB db = Postgres(STRING_DB) url = 'http://www.motorsport-total.com/rss_f1.xml' db.run( "INSERT INTO feed (url) VALUES ('{}') ON CONFLICT (url) DO NOTHING;". format(url)) url = 'http://www.motorsport-total.com/rss_motorrad_MGP.xml' db.run( "INSERT INTO feed (url) VALUES ('{}') ON CONFLICT (url) DO NOTHING;". format(url))
def init_DB(): global STRING_DB db = Postgres(STRING_DB) db.run( "CREATE TABLE IF NOT EXISTS url (id serial PRIMARY KEY, url varchar(100) unique );" ) db.run( "CREATE TABLE IF NOT EXISTS feed (id serial PRIMARY KEY, url varchar(100) unique);" ) db.run( "CREATE TABLE IF NOT EXISTS users (id serial PRIMARY KEY, chat_id int unique, name varchar(50), time_added varchar(20));" )
def _agent_key_path_in_db(config, node_id, deployment_id): postgres = Postgres(config) get_node_data = "SELECT properties FROM nodes " \ "WHERE id = '{0}' " \ "AND deployment_id = '{1}';" \ "".format(node_id, deployment_id) result = postgres.run_query(get_node_data) pickled_buffer = result['all'][0][0] properties = pickle.loads(pickled_buffer) key_path = properties['cloudify_agent']['key'] ctx.logger.debug('Agent key path in db: {0}'.format(key_path)) return key_path
def remove_obsevations(schema): ''' Truncate the active period and observtions tables to prepare for a new dataset generation :param schema: :return: ''' print('Removing old active_period and observation entries...') db = Postgres("postgres://%s:%s@localhost/%s" % (os.environ['CHURN_DB_USER'], os.environ['CHURN_DB_PASS'], os.environ['CHURN_DB'])) db.run('truncate table %s.active_period' % schema) db.run('truncate table %s.observation' % schema)
def db(): dburl = os.environ['DATABASE_URL'] maxconn = int(os.environ['DATABASE_MAXCONN']) db = Postgres(dburl, maxconn=maxconn) # register hstore type with db.get_cursor() as cursor: psycopg2.extras.register_hstore(cursor, globally=True, unicode=True) db.register_model(Community) db.register_model(Participant) return db
def sql_listing(param_dict): ''' Run a SQL listing. The sql file is loaded, and then any non-reserved keyword in the parameters is treated as a string to be replaced in the sql string. The SQL is then printed out, before newlines are removed, and then run in one of the allowed modes. The allowed modes are: run : The SQL returns no result one : The SQL should return one row result to be printed top : The SQL returns many results, print the top N (given by global print_num_rows) :param param_dict: dictionary produced by load_and_check_listing_params :return: ''' with open('../../listings/chap%d/%s.sql' % (param_dict['chapter'], param_dict['name']), 'r') as myfile: db = Postgres("postgres://%s:%s@localhost/%s" % (os.environ['CHURN_DB_USER'],os.environ['CHURN_DB_PASS'],os.environ['CHURN_DB'])) # prefix the search path onto the listing, which does not specify the schema sql = "set search_path = '%s'; " % param_dict['schema']; # load the sql file sql = sql + myfile.read() # bind any parameters that match strings in the sql param_keys = [p for p in param_dict.keys() if p not in reserved_param_keywords] for p in param_keys: sql = sql.replace(p, str(param_dict[p])) # Print the sql (then remove the newlines) print('SQL:\n----------\n'+sql+'\n----------\nRESULT:') sql = sql.replace('\n', ' ') # Run in the manner indicated by the mode if param_dict['mode'] == 'run': db.run(sql) elif param_dict['mode'] == 'one': res = db.one(sql) print(res) elif param_dict['mode'] == 'top' or param_dict['mode'] == 'save': res = db.all(sql) df = pd.DataFrame(res) if param_dict['mode'] == 'save': save_path = '../../../fight-churn-output/' + param_dict['schema'] + '/' os.makedirs(save_path,exist_ok=True) csv_path=save_path + param_dict['schema'] + '_' + param_dict['name'].replace(param_dict['prefix'],'') + '.csv' print('Saving: %s' % csv_path) df.to_csv(csv_path, index=False) else: print(df.head(print_num_rows)) else: print('Unknown run mode for SQL example') exit(-4)
def count_words() -> dict: words_dict = {} psql = Postgres() text = psql.get_posts_text() for item in text: item = clear_text(item[0]) for word in item.split(): words_dict[word] = words_dict.get(word, 0) + 1 psql.close() return words_dict
def save_posts(posts): psql = Postgres() for post in posts['items']: id = post['id'] text = post['text'] if psql.is_post_in(id): break if 'copy_history' in post: for nested_post in post['copy_history']: text += ' ' + nested_post['text'] psql.add_post(text=text, vk_id=id) psql.close()
def _dump_postgres(tempdir, config, metadata): ctx.logger.info('Dumping Postgres data') postgres = Postgres(config) destination_path = os.path.join(tempdir, _POSTGRES_DUMP_FILENAME) exclude_tables = ['snapshots', 'provider_context'] try: postgres.dump(destination_path, exclude_tables) except Exception as ex: raise NonRecoverableError('Error during dumping Postgres data, ' 'exception: {0}'.format(ex)) delete_current_execution = "DELETE FROM executions WHERE id = '{0}';"\ .format(ctx.execution_id) postgres.append_dump(destination_path, delete_current_execution)
def __init__(self, model, start, end, init_customers,seed): ''' Creates the behavior/utility model objects, sets internal variables to prepare for simulation, and creates the database connection :param model: name of the behavior/utility model parameters :param start: start date for simulation :param end: end date for simulation :param init_customers: how many customers to create at start date ''' self.model_name=model self.start_date = start self.end_date = end self.init_customers=init_customers self.monthly_growth_rate = 0.1 self.util_mod=UtilityModel(self.model_name) local_dir = f'{os.path.abspath(os.path.dirname(__file__))}/conf/' behavior_versions = glob.glob(local_dir+self.model_name+'_*.csv') self.behavior_models = {} self.model_list = [] for b in behavior_versions: version = b[(b.find(self.model_name) + len(self.model_name)+1):-4] if version in ('utility','population','country'): continue behave_mod=FatTailledBehaviorModel(self.model_name,seed,version) self.behavior_models[behave_mod.version]=behave_mod self.model_list.append(behave_mod) local_dir = f'{os.path.abspath(os.path.dirname(__file__))}/conf/' if len(self.behavior_models)>1: self.population_percents = pd.read_csv(local_dir +self.model_name + '_population.csv',index_col=0) self.util_mod.setChurnScale(self.behavior_models,self.population_percents) self.population_picker = np.cumsum(self.population_percents) self.country_lookup = pd.read_csv(local_dir +self.model_name + '_country.csv') self.subscription_count = 0 self.tmp_sub_file_name = os.path.join(tempfile.gettempdir(),'{}_tmp_sub.csv'.format(self.model_name)) self.tmp_event_file_name=os.path.join(tempfile.gettempdir(),'{}_tmp_event.csv'.format(self.model_name)) con_string = f"postgresql://localhost/{os.environ['CHURN_DB']}?user={os.environ['CHURN_DB_USER']}&password={os.environ['CHURN_DB_PASS']}" self.db = Postgres(con_string) self.con = post.connect( database= os.environ['CHURN_DB'], user= os.environ['CHURN_DB_USER'], password=os.environ['CHURN_DB_PASS'])