def delete(self, id):
user = DBConnectionsFacade.get_edusson_ds_orm_session().query(
DashUser).filter_by(user_id=id).first()
Session = sessionmaker(bind=DBConnectionsFacade.get_edusson_ds())
sess = Session()
sess.delete(user)
sess.commit()
def delete(self, id):
board = DBConnectionsFacade.get_edusson_ds_orm_session().query(
DashDashboardBoard).filter_by(board_id=id).first()
Session = sessionmaker(bind=DBConnectionsFacade.get_edusson_ds())
sess = Session()
sess.delete(board)
sess.commit()
def get_p2p_proba_from_api_log2(order_ids: list) -> pd.Series:
likes_ls = [f'(url LIKE "%%{order_id}%%")' for order_id in order_ids]
cond_likes = " OR ".join(np.array(likes_ls))
sql_query = f"""
SELECT response
FROM api_service_log
WHERE service_id = 2
AND api_user_id = 1
AND status = 200
AND ({cond_likes})
GROUP BY url;
"""
df = pd.read_sql(sql=sql_query,
con=DBConnectionsFacade.get_edusson_ds_replica())
def response_dict(s):
s = s.replace("[b\'", "").replace("\\n\']", "")
return json.loads(s)
df["response_dict"] = df.response.apply(response_dict)
df["order_id"] = df.response_dict.apply(lambda x: x["result"]["order_id"])
df["place2paid_proba"] = df.response_dict.apply(
lambda x: x["result"]["place2paid_proba"])
return df.set_index("order_id").place2paid_proba
def post(self):
sess = sessionmaker(bind=DBConnectionsFacade.get_edusson_ds())()
json_data = tornado.escape.json_decode(self.request.body)
username = json_data.get('username')
password = json_data.get('password')
boards = json_data.get('boards', [])
access_level_id = json_data.get('access_level_id')
is_active = json_data.get('is_active')
if not password:
raise MyAppException(reason='password cannot be empty',
status_code=400)
if not username:
raise MyAppException(reason='username cannot be empty',
status_code=400)
user = DashUser(username=username,
password=sha256_crypt.encrypt(password),
is_active=is_active,
access_level_id=access_level_id)
for board_id in boards:
board_access = DashUserBoardAccess(
user=user,
dashboard_id=get_analytic_dashboard(),
board_id=board_id)
user.boards.append(board_access)
sess.add(user)
sess.commit()
def upgrade():
op.create_table(
'dash_dashboard_board',
sa.Column('board_id',
TINYINT(unsigned=True),
primary_key=True,
autoincrement=True,
nullable=False),
sa.Column('name', sa.String(255), nullable=True),
sa.Column('model_tag', sa.String(255), nullable=False),
sa.Column('description', sa.Text, nullable=True),
sa.Column('is_active', TINYINT(unsigned=True), server_default='1'),
sa.Column('date_created', sa.DateTime, server_default=func.now()))
with op.batch_alter_table("dash_user_board_access") as batch_op:
batch_op.add_column(
sa.Column('board_id', TINYINT(unsigned=True), nullable=True))
# ForeignKey('dash_dashboard_board.board_id')
op.create_foreign_key('fk_dash_user_board_access_dash_dashboard_board',
'dash_user_board_access',
'dash_dashboard_board', ['board_id'], ['board_id'],
onupdate="CASCADE",
ondelete="CASCADE")
try:
op.drop_index('user_access_UNIQUE', 'dash_user_board_access')
except Exception:
pass
op.create_index('ix_user_access_board_UNIQUE',
'dash_user_board_access',
['user_id', 'dashboard_id', 'board_id'],
unique=True)
from sqlalchemy.orm import sessionmaker
from edusson_ds_main.db.connections import DBConnectionsFacade
from edusson_ds_main.db.models import DashDashboardBoard, DashUser
Session = sessionmaker(bind=DBConnectionsFacade.get_edusson_ds())
sess = Session()
log1 = DashDashboardBoard(name='test 1',
model_tag='test 1',
description='test 1')
sess.add(log1)
log2 = DashDashboardBoard(name='test 2',
model_tag='test 2',
description='test 2')
sess.add(log2)
log3 = DashDashboardBoard(name='test 3',
model_tag='test 3',
description='test 3')
sess.add(log3)
sess.commit()
def get(self, id):
id = int(id)
board = DBConnectionsFacade.get_edusson_ds_orm_session().query(
DashDashboardBoard).filter_by(board_id=id).first()
if not board:
return self.write(json.dumps({}))
self.write(json.dumps(to_dict(board)))
def get_current_dashboard_id(dash_url):
dashboard_id = None
dashboard = DBConnectionsFacade.get_edusson_ds_orm_session().query(
DashDashboard).filter_by(url=dash_url).first()
if dashboard:
dashboard_id = dashboard.dashboard_id
return dashboard_id
def save_audit_data_by_month(start_date):
to_date = (start_date + relativedelta(months=1)).strftime('%Y-%m-%d')
from_date = start_date.strftime('%Y-%m-%d')
stime = time()
df = pd.read_sql(sql=sql.format(from_date, to_date),
con=DBConnectionsFacade.get_edusson_replica())
df.to_pickle(
'/home/andrei/Python/sqlalchemy-lab/p2paid/audit_data2/{}-{}.{}.pkl'.
format(from_date, to_date, len(df)))
print("From {} to {} len: {}, time: {}".format(from_date, to_date, len(df),
time() - stime))
def make_tmp_p2p_mysql_table():
sql = """
CREATE TEMPORARY TABLE first_p2p_score
SELECT id, REPLACE(REPLACE(url, '/api/v1/orders/', ''), '?', '') as order_id, response, min(date_created) as date_created
FROM edusson_data_science.api_service_log
WHERE service_id = 2
AND api_user_id = 1
AND status = 200
AND is_success = 1
GROUP BY url ORDER BY id DESC;
"""
with DBConnectionsFacade.get_edusson_ds().connect() as conn:
conn.execute(sql)
def put(self, id):
json_data = tornado.escape.json_decode(self.request.body)
board_id = json_data.get('board_id', None)
name = json_data.get('name')
model_tag = json_data.get('model_tag')
description = json_data.get('description')
Session = sessionmaker(bind=DBConnectionsFacade.get_edusson_ds())
sess = Session()
board = sess.query(DashDashboardBoard).filter_by(board_id=id).first()
board.name = name
board.model_tag = model_tag
board.description = description
sess.commit()
def get(self, id):
id = int(id)
# user = DBConnectionsFacade.get_edusson_ds_orm_session().query(DashUser).outerjoin(DashUser.boards).filter_by(
user = DBConnectionsFacade.get_edusson_ds_orm_session().query(
DashUser).filter_by(user_id=id).first()
if not user:
raise MyAppException(reason='User cannot be found',
status_code=400)
user = to_dict(user)
user['password'] = ''
user['boards'] = filter(
lambda x: x['dashboard_id'] == get_analytic_dashboard(),
user['boards'])
user['boards'] = list(map(lambda x: x['board_id'], user['boards']))
self.write(json.dumps(user))
def post(self):
json_data = tornado.escape.json_decode(self.request.body)
board_id = json_data.get('board_id', None)
name = json_data.get('name')
model_tag = json_data.get('model_tag')
description = json_data.get('description')
Session = sessionmaker(bind=DBConnectionsFacade.get_edusson_ds())
sess = Session()
log = DashDashboardBoard(
name=name,
model_tag=model_tag,
description=description,
date_created=datetime.utcnow(),
)
sess.add(log)
sess.commit()
def run_migrations_online():
"""Run migrations in 'online' mode.
In this scenario we need to create an Engine
and associate a connection with the context.
"""
alembic_config = config.get_section(config.config_ini_section)
engine = engine_from_config(alembic_config,
prefix='sqlalchemy.',
poolclass=pool.NullPool)
with DBConnectionsFacade.get_edusson_ds().connect() as connection:
context.configure(connection=connection,
target_metadata=target_metadata)
with context.begin_transaction():
context.run_migrations()
def put(self, id):
json_data = tornado.escape.json_decode(self.request.body)
username = json_data.get('username')
password = json_data.get('password', None)
boards = json_data.get('boards', [])
access_level_id = json_data.get('access_level_id', None)
is_active = json_data.get('is_active', True)
sess = sessionmaker(bind=DBConnectionsFacade.get_edusson_ds())()
user = sess.query(DashUser).filter_by(user_id=id).first()
user_board_accesses = sess.query(DashUserBoardAccess).filter_by(
user_id=id, dashboard_id=get_analytic_dashboard()).all()
exists_ids = []
for board_access in user_board_accesses:
exists_ids.append(board_access.board_id)
if board_access.board_id not in boards:
sess.query(DashUserBoardAccess).filter_by(
user_id=id,
dashboard_id=get_analytic_dashboard(),
board_id=board_access.board_id).delete()
for board_id in boards:
if board_id not in exists_ids:
board_access = DashUserBoardAccess(
user=user,
dashboard_id=get_analytic_dashboard(),
board_id=board_id)
user.boards.append(board_access)
user.username = username
user.is_active = is_active
if password:
user.password = sha256_crypt.encrypt(password)
if access_level_id:
user.access_level_id = access_level_id
sess.commit()
def get_p2p_proba_from_api_log(order_ids: list = None) -> pd.Series:
if order_ids and len(order_ids):
sql_query = """
SELECT * FROM first_p2p_score WHERE order_id IN ({});
""".format(','.join([str(i) for i in order_ids]))
else:
sql_query = """SELECT * FROM first_p2p_score;"""
df = pd.read_sql(sql=sql_query, con=DBConnectionsFacade.get_edusson_ds())
def response_dict(s):
s = s.replace("[b\'", "").replace("\\n\']", "").replace("\\n", '')
return json.loads(s)
df["response_dict"] = df.response.apply(response_dict)
df["order_id"] = df.response_dict.apply(lambda x: x["result"]["order_id"])
df["place2paid_proba"] = df.response_dict.apply(
lambda x: x["result"]["place2paid_proba"])
return df.set_index("order_id").place2paid_proba
def drop_tmp_p2p_mysql_table():
with DBConnectionsFacade.get_edusson_ds().connect() as conn:
conn.execute("""DROP TABLE IF EXISTS first_p2p_score;""")
def add_own_encoders(conn, cursor, query, *args):
cursor.connection.encoders[np.int64] = lambda value, encoders: int(value)
cursor.connection.encoders[
np.float64] = lambda value, encoders: float(value)
cursor.connection.encoders[
pd.Timestamp] = lambda value, encoders: encoders[str](str(
value.to_pydatetime()))
cursor.connection.encoders[
pd.Timedelta] = lambda value, encoders: value.total_seconds()
cursor.connection.encoders[DBNULL] = lambda value, encoders: "NULL"
cursor.connection.encoders[np.nan] = lambda value, encoders: "NULL"
from edusson_ds_main.db.connections import DBConnectionsFacade
engine = DBConnectionsFacade.get_edusson_ds()
event.listen(engine, "before_cursor_execute", add_own_encoders)
meta = MetaData()
meta.reflect(bind=engine, only=['ds_metrics', 'ds_writer_metrics'])
DSMetrics = meta.tables['ds_metrics']
DSWriterMetrics = meta.tables['ds_writer_metrics']
logger = logging.getLogger('DB')
from compare_exists_and_non_exists import cccompare
def getdbrows(ids, metric_id, connection):
subq = alias(
"""
sql = """
SELECT *
FROM es_orders o
LEFT JOIN es_product p1 ON p1.order_id = o.order_id
LEFT JOIN es_product_type_essay pe1 ON p1.product_id = pe1.product_id
where o.test_order = 0
and o.is_first_client_order = 1
and o.order_id not in (
select distinct p.order_id from es_order_preset p where p.order_id is not null
)
"""
df_exclude = pd.read_sql(sql2, con=DBConnectionsFacade.get_edusson_replica())
df = pd.read_sql(sql, con=DBConnectionsFacade.get_edusson_replica())
df.is_paid_order.describe()
stored_df = df.copy()
# df = df[['site_id', 'order_id', 'order_total_usd', 'pages', 'deadline', 'order_date', 'is_first_client_order', 'is_easy_bidding']].drop_duplicates()[~df.order_id.isin(df_exclude.order_id.values)]
#df = df.groupby(df.index).first()
cols = []
count = 1
for column in df.columns:
if column == 'order_id':
cols.append('order_id' + str(count))
count += 1
continue
def __init__(self):
self.session = DBConnectionsFacade.get_edusson_ds_orm_session()
def get(self):
boards = DBConnectionsFacade.get_edusson_ds_orm_session().query(
DashDashboardBoard).all()
boards = [to_dict(board) for board in boards]
self.write(json.dumps(boards))
from time import time
from edusson_ds_main.db.connections import DBConnectionsFacade
from rx import Observable
from sqlalchemy import create_engine, text
# engine = create_engine()
# conn = engine.connect()
conn = DBConnectionsFacade.get_edusson_ds().connect()
sql = text('SELECT * FROM edusson_data_science.ds_orders_backup;')
def get_all_customers():
return Observable.from_(conn.execute(sql))
def check_rx():
start_time = time()
get_all_customers().subscribe(
lambda t: print(t),
on_completed=lambda: print('Completed!',
time() - start_time))
def check_normal():
start_time = time()
for row in conn.execute(sql):
print(row)
print('Completed!', time() - start_time)
df = pd.read_sql(sql=sql.format(from_date, to_date),
con=DBConnectionsFacade.get_edusson_replica())
df.to_pickle(
'/home/andrei/Python/sqlalchemy-lab/p2paid/audit_data2/{}-{}.{}.pkl'.
format(from_date, to_date, len(df)))
print("From {} to {} len: {}, time: {}".format(from_date, to_date, len(df),
time() - stime))
# while start_date < datetime.now():
# save_audit_data_by_month(start_date)
# start_date = start_date + relativedelta(months=1)
from os import listdir
from os.path import isfile, join
mypath = '/home/andrei/Python/sqlalchemy-lab/p2paid/audit_data'
audit_data_files = [f for f in listdir(mypath) if isfile(join(mypath, f))]
with DBConnectionsFacade().get_edusson_ds().connect() as conn:
conn.execute('DROP TABLE IF EXISTS es_orders_audit;')
audit_data_files = sorted(audit_data_files)
for file in audit_data_files:
df = pd.read_pickle('{}/{}'.format(mypath, file))
df = df.set_index(['order_id', 'order_date'])
df.to_sql('es_orders_audit',
con=DBConnectionsFacade().get_edusson_ds(),
if_exists='append')
print('Processed', file)
def get(self):
users = DBConnectionsFacade.get_edusson_ds_orm_session().query(
DashUser).order_by(DashUser.user_id).all()
users = [to_dict(user) for user in users]
self.write(json.dumps(users))
from sqlalchemy.orm import relationship
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy.orm import Session
db = SQLAlchemy()
from edusson_ds_main.db.connections import DBConnectionsFacade
from sqlalchemy.ext.automap import automap_base
# Base = declarative_base()
#Base = automap_base()
#metadata = Base.metadata
#Base.prepare(DBConnectionsFacade.get_edusson_ds(), reflect=True)
meta = MetaData()
meta.reflect(bind=DBConnectionsFacade.get_edusson_ds())
print(meta.tables)
DashUser = meta.tables['dash_user']
if __name__ == '__main__':
# for i in range(100):
# session = Session(DBConnectionsFacade.get_edusson_ds())
# for user in session.query(DashUser).limit(10):
# print(user.username)
# session.close()
connection = DBConnectionsFacade.get_edusson_ds().connect()
columns = [DashUser.c.user_id, DashUser.c.username]
if __name__ == '__main__':
from sqlalchemy.orm import sessionmaker
from edusson_ds_main.db.connections import DBConnectionsFacade, DB_EDUSSON_DS
from edusson_ds_main.db.models import DashDashboardBoard
DB_EDUSSON_DS.set_static_connection(pool_recycle=500,
pool_size=10,
max_overflow=0,
engine='mysql+pymysql',
host='159.69.44.90',
db='edusson_tmp_lab',
user='******',
passwd='')
enable_sql_logging()
session = sessionmaker(bind=DBConnectionsFacade.get_edusson_ds())()
# subquery = session.query(DashDashboardBoard.board_id).filter(DashDashboardBoard.model_tag == 'writer_biding_coefficient')
# q = session.query(DashUserBoardViewLog).outerjoin(DashUser.boards).order_by(DashUser.user_id)
q = session.query(DashUserBoardViewLog).join(
DashUserBoardViewLog.board).filter(
DashDashboardBoard.model_tag == 'writer_biding_coefficient')
# q = session.query(DashUserBoardViewLog).join(DashUserBoardViewLog.board).order_by(DashUser.user_id)
print(DBRepository.get_count(q))
# users = session.query(DashUser).outerjoin(DashUser.boards).order_by(DashUser.user_id).all()
# for u in users:
# print(to_dict(u))
#
