class Status(Base):
__tablename__ = 'status'
id = Column(VARCHAR2(255), primary_key=True)
user_id = Column(VARCHAR2(255), ForeignKey('user.id'), nullable=False)
status = Column(Boolean, default=False, nullable=False)
device = Column(VARCHAR2(255), nullable=True)
def __init__(self, User, device="127.0.0.1"):
self.id = str(uuid.uuid4())
self.user_id = User.get_id()
self.status = True
self.device = device
def __repr__(self):
return str(self.status) + ':' + str(self.device)
def logout(self):
self.status = False
def login(self):
self.status = True
class User(UserMixin, Base):
__tablename__ = 'user'
id = Column(VARCHAR2(255), primary_key=True)
nickname = Column(VARCHAR2(20), unique=True, nullable=False)
password = Column(VARCHAR2(100), nullable=False)
def __init__(self, nickname, password):
self.nickname = nickname
self.password = generate_password_hash(password)
self.id = str(uuid.uuid4())
def __repr__(self):
return self.nickname
def get_id(self):
return self.id
def create_activity(self):
return Activity(self)
def login(self, device='127.0.0.1'):
users_sessions = session.query(Status).filter_by(
id=self.get_id()).all()
for users_session in users_sessions:
if users_session.device == device:
users_session.login()
return users_session
users_session = Status(self, device)
return users_session
def password_check(self, password):
return check_password_hash(self.password, password)
class BorrowerConfig(Base):
__tablename__ = 'sl$borrower_config'
__table_args__ = {u'schema': 'SLPROD'}
table_tag = 'sl$'
id = Column(NUMBER(38), primary_key=True)
name = Column(VARCHAR2(150))
content = Column(CLOB)
access_group = Column(VARCHAR2(1024))
def mapping_df_types(df):
dtypedict = {}
for i, j in zip(df.columns, df.dtypes):
if "object" in str(j):
dtypedict.update({i: VARCHAR2(4000)})
if "float" in str(j):
dtypedict.update({i: NUMBER(19, 8)})
if "int" in str(j):
dtypedict.update({i: VARCHAR2(4000)})
return dtypedict
class BroadcastMessage(Base, TrackMixin):
__tablename__ = 'FBK_BROADCAST_MSG'
id = Column(NUMBER, Sequence('FBK_BROADCAST_MSG_SEQ'), primary_key=True, name='ID')
message_creative_id = Column(VARCHAR(50), name='MESSAGE_CREATIVE_ID')
message_creative_type = Column(VARCHAR(50), name='MESSAGE_CREATIVE_TYPE')
is_already_broadcast = Column(VARCHAR(1), name='IS_ALREADY_BROADCAST')
content = Column(VARCHAR2(1000), name='CONTENT')
class T_IN_TP_D(Base):
__tablename__ = 'T_IN_TP_D'
id = Column(NUMBER, primary_key=True, nullable=False)
out_id = Column(NUMBER)
msg01 = Column(VARCHAR2(20))
msg02 = Column(DATE)
msg03 = Column(NUMBER)
def __repr__(self):
return '<T_IN_TP_D(id=%d,out_id=%d,msg01=%s,msg02=%s,msg03=%d)>' % (
self.id, self.out_id, self.msg01, str(self.msg02), self.msg03)
class Post(Base):
__tablename__ = 'post'
id = Column(VARCHAR2(255), primary_key=True)
user_id = Column(VARCHAR2(255), ForeignKey('user.id'))
body = Column(VARCHAR2(255))
date = Column(NUMBER)
def __init__(self, body, user_id):
self.id = str(uuid.uuid4())
self.date = time.time()
self.body = body
self.user_id = user_id
class T_OUT_TP_D(Base):
__tablename__ = 'T_OUT_TP_D'
id = Column(NUMBER, primary_key=True, nullable=False)
msg01 = Column(VARCHAR2(20))
msg02 = Column(DATE)
msg03 = Column(NUMBER)
sendfg = Column(VARCHAR2(2))
wbflag = Column(VARCHAR2(2))
def __repr__(self):
return '<T_OUT_TP_D(id=%d,msg01=%s,msg02=%s,msg03=%d)>' % (
self.id, self.msg01, str(self.msg02), self.msg03)
def to_json(self):
return {
'id': self.id,
'msg01': self.msg01,
'msg02': self.msg02,
'msg03': self.msg03,
'sendfg': self.sendfg,
'wbflag': self.wbflag
}
class Activity(Base):
__tablename__ = 'activity'
id = Column(VARCHAR2(255),
ForeignKey('user.id'),
primary_key=True,
nullable=False)
activity_count = Column(NUMBER, default=0)
lastActivity = Column(VARCHAR2(255))
def __init__(self, User):
self.id = User.get_id()
print("ACTIVITY_EVENT")
self.lastActivity = str(date.today())
def activity_event(self):
self.lastActivity = str(date.today())
self.activity_count += 1
class Role(Base):
__tablename__ = 'role'
id = Column(VARCHAR2(255),
ForeignKey('user.id'),
primary_key=True,
nullable=False)
role = Column(VARCHAR2(255),
default="guest",
primary_key=True,
nullable=False)
roles = ['admin', 'teacher', 'student', 'guest']
def __init__(self, User):
self.id = User.get_id()
def add_role(self, role):
if role in self.roles:
self.role = role
def check_role(self, User, role):
roles = session.query(Role).filter_by(id=User.get_id()).all()
return any(i.role == role for i in roles)
class ALM_WECHAT_LIST(Base):
__tablename__ = 'ALM_WECHAT_LIST'
ID = Column(NUMBER, primary_key=True, nullable=False)
COMPID = Column(NUMBER)
OWNERID = Column(NUMBER)
BUSTYPE = Column(VARCHAR2(5))
GENERATETIME = Column(DATE)
MESSAGE = Column(VARCHAR2(4000))
TRANSFLAG = Column(VARCHAR2(2))
USERCD = Column(VARCHAR2(20))
USERGRP = Column(VARCHAR2(10))
USERNAME = Column(VARCHAR2(20))
def __repr__(self):
return '<ALM_WECHAT_LIST(ID=%d,COMPID=%d,OWNERID=%d,BUSTYPE=%s,GENERATETIME=%s,MESSAGE=%s,TRANSFLAG=%s,USERCD=%s,USERGRP=%s,USERNAME=%s)>' % (
self.ID, self.COMPID, self.OWNERID, self.BUSTYPE,
str(self.GENERATETIME), self.MESSAGE, self.TRANSFLAG, self.USERCD,
self.USERGRP, self.USERNAME)
def is_active(cls):
return Column(VARCHAR2(1), name='IS_ACTIVE', nullable=False)
def delete_by(cls):
return Column(VARCHAR2(255), name='DELETE_BY')
def update_by(cls):
return Column(VARCHAR2(255), name='UPDATE_BY')
def create_by(cls):
return Column(VARCHAR2(255), name='CREATE_BY')
def load(filename, csv=False):
lines = open(filename).read().split('\n')
lines = list(filter(lambda x: x != '', lines))
header = list(
filter(lambda x: x, [
i if not re.match('^[1-9]', d) else None
for i, d in enumerate(lines)
]))
cols = lines[header[-1]].split(',')
rows_ = list(map(lambda x: x.split(','), lines[header[-1] + 1:]))
rows_ = list(
map(lambda x: x + [None] * (6 - len(x)) if len(x) < 6 else x, rows_))
rows = np.array(rows_)
fecha = list(filter(lambda x: re.match('^Fecha', x),
lines[:header[-1]]))[0].split(' ')[1]
sistema = list(
filter(lambda x: re.match('^Sistema', x),
lines[:header[-1]]))[0].split(' ')
sistema = ''.join(list(map(lambda x: x[0], sistema)))
meses = [('enero', '01'), ('febrero', '02'), ('marzo', '03'),
('abril', '04'), ('mayo', '05'), ('junio', '06'), ('julio', '07'),
('agosto', '08'), ('septiembre', '09'), ('octubre', '10'),
('noviembre', '11'), ('diciembre', '12')]
for m in meses:
fecha = re.sub(m[0], m[1], fecha.lower())
df = pd.DataFrame(rows, columns=cols)
col_rename = {
'Hora': 'HORA',
'Clave del nodo': 'CLAVE_NODO',
'Precio marginal local ($/MWh)': 'PML_MXN_MWH',
'Componente de energia ($/MWh)': 'ENERGIA_MXN_MWH',
'Componente de perdidas ($/MWh)': 'PERDIDAS_MXN_MWH',
'Componente de congestion ($/MWh)': 'CONGESTION_MXN_MWH'
}
df.rename(columns=col_rename, inplace=True)
df['FECHA'] = [pd.to_datetime(fecha)] * len(df)
df['SISTEMA'] = [sistema] * len(df)
datatypes = {
'SISTEMA': VARCHAR2(100),
'FECHA': DATE,
'HORA': NUMBER(28, 22),
'CLAVE_NODO': VARCHAR2(100),
'PML_MXN_MWH': NUMBER(28, 22),
'ENERGIA_MXN_MWH': NUMBER(28, 22),
'PERDIDAS_MXN_MWH': NUMBER(28, 22),
'CONGESTION_MXN_MWH': NUMBER(28, 22)
}
for i in [
'HORA', 'PML_MXN_MWH', 'ENERGIA_MXN_MWH', 'PERDIDAS_MXN_MWH',
'CONGESTION_MXN_MWH'
]:
df[i] = df[i].replace('', np.nan)
df[i] = df[i].astype(float)
df = df[[
'SISTEMA', 'FECHA', 'HORA', 'CLAVE_NODO', 'PML_MXN_MWH',
'ENERGIA_MXN_MWH', 'PERDIDAS_MXN_MWH', 'CONGESTION_MXN_MWH'
]]
if csv:
df.to_csv('forMongo/' + filename, index=False)
else:
df.to_sql('datos_precios_margloc_mda',
conn,
index=False,
if_exists='append',
dtype=datatypes)
class FUND_INFO_LIST_WORK(Base):
__tablename__ = 'FUND_INFO_LIST_WORK'
ID = Column(NUMBER, primary_key=True)
TS_CODE = Column(VARCHAR2(27))
NAME = Column(VARCHAR2(63))
MANAGEMENT = Column(VARCHAR2(27))
CUSTODIAN = Column(VARCHAR2(18))
FUND_TYPE = Column(VARCHAR2(15))
FOUND_DATE = Column(VARCHAR2(24))
DUE_DATE = Column(VARCHAR2(24))
LIST_DATE = Column(VARCHAR2(24))
ISSUE_DATE = Column(VARCHAR2(24))
DELIST_DATE = Column(VARCHAR2(24))
ISSUE_AMOUNT = Column(NUMBER)
M_FEE = Column(NUMBER)
C_FEE = Column(NUMBER)
DURATION_YEAR = Column(NUMBER)
P_VALUE = Column(NUMBER)
MIN_AMOUNT = Column(NUMBER)
EXP_RETURN = Column(VARCHAR2(30))
BENCHMARK = Column(VARCHAR2(1200))
STATUS = Column(VARCHAR2(3))
INVEST_TYPE = Column(VARCHAR2(21))
TYPE = Column(VARCHAR2(18))
TRUSTEE = Column(VARCHAR2(50))
PURC_STARTDATE = Column(VARCHAR2(24))
REDM_STARTDATE = Column(VARCHAR2(24))
MARKET = Column(VARCHAR2(3))
GETDATA_FLAG = Column(VARCHAR2(2))
def __repr__(self):
return '<ALM_WECHAT_LIST(ID=%d,TS_CODE=%s,NAME=%s)>' % (
self.ID, self.TS_CODE, self.NAME)