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)) #