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 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 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 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 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 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 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 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
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]
def drop_tmp_p2p_mysql_table():
with DBConnectionsFacade.get_edusson_ds().connect() as conn:
conn.execute("""DROP TABLE IF EXISTS first_p2p_score;""")
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)
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(
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))
#