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'])
