class Round(sqlalchemy_ext.Model):
__bind_key__ = 'game'
__tablename__ = 'SS13_round'
id = Column('id', Integer(), primary_key=True)
initialize_datetime = Column('initialize_datetime', DateTime())
start_datetime = Column('start_datetime', DateTime())
shutdown_datetime = Column('shutdown_datetime', DateTime())
end_datetime = Column('end_datetime', DateTime())
server_name = Column('server_name', String(32))
server_ip = Column('server_ip', Integer())
server_port = Column('server_port', SmallInteger())
commit_hash = Column('commit_hash', String(40))
game_mode = Column('game_mode', String(32))
game_mode_result = Column('game_mode_result', String(64))
end_state = Column('end_state', String(64))
shuttle_name = Column('shuttle_name', String(64))
map_name = Column('map_name', String(32))
station_name = Column('station_name', String(80))
@classmethod
def from_id(cls, id):
try:
return db_sesion.query(cls).filter(cls.id == id).one()
except NoResultFound:
return None
@classmethod
def get_latest(cls):
return db_sesion.query(cls).order_by(cls.id.desc()).first()
def in_progress(self):
if self.shutdown_datetime:
return False
return True
def __create_schema(engine):
global beedict, beerecord, flower, flowerdict, features, auth, admin
metadata = sqlalchemy.MetaData()
beedict = Table("beedict", metadata,
Column("bee_id", SmallInteger(), primary_key=True),
Column("bee_name", String(128)),
Column("common_name", String(256)),
Column("description", Text()),
Column("active_months", String(128)),
Column("confused", Text()),
Column("bee_pic_path", String(1024)),
Column("abdomen_list", String(256)),
Column("thorax_list", String(256)),
Column("head_list", String(256)))
beerecord = Table("beerecord", metadata,
Column("beerecord_id", Integer(), primary_key=True),
Column("bee_dict_id", SmallInteger(), ForeignKey("beedict.bee_id"), nullable=True),
Column("bee_name", String(128)),
Column("coloration_abdomen", String(4)),
Column("coloration_thorax", String(4)),
Column("coloration_head", String(4)),
Column("flower_shape", String(128)),
Column("flower_color", String(16)),
Column("time", DateTime()),
Column("loc_info", String(32)),
Column("user_id", String(64), index=True),
Column("record_pic_path", String(1024)),
Column("record_video_path", String(1024)),
Column("flower_name", String(256)),
Column("city_name", String(256)),
Column("gender", String(16)),
Column("bee_behavior", String(16)),
Column("app_version", String(16)),
Column("elevation", String(8)))
flower = Table("flower", metadata,
Column("flower_id", Integer(), primary_key=True),
Column("flower_common_name", String(128)),
Column("flower_genus", String(64)),
Column("flower_species", String(64)),
Column("flower_color", String(16)),
Column("flower_shape", String(8)))
flowerdict = Table("flowerdict", metadata,
Column("flower_id", Integer(), primary_key=True),
Column("latin_name", String(128), index=True),
Column("common_name", String(128)),
Column("main_common_name", String(128)),
Column("main_color", String(16)),
Column("colors", Text()),
Column("bloom_time", Text()),
Column("shape", String(32)))
features = Table("feature", metadata,
Column("feature_id", String(8), primary_key=True),
Column("feature_name", String(32)),
Column("feature_description", Text()),
Column("feature_pic_path", String(1024)))
auth = Table("authtable", metadata,
Column("user_id", String(512), primary_key=True),
Column("access_token", String(1024), index=True),
Column("refresh_token", String(1024), index=True),
Column("token_expiry", BigInteger()))
admin = Table("admintable", metadata,
Column("user_id", String(512), ForeignKey("authtable.user_id", ondelete="cascade"), index=True))
metadata.create_all(engine)
password = os.getenv('MYSQL_PASSWORD')
database = 'reviews'
engine = create_engine(f'mysql://{user}:{password}@{host}/{database}')
# Read movie IDs
with engine.connect() as conn:
movie_df = pd.read_sql('SELECT id AS movie_id, imdb_id FROM movies', conn, index_col='imdb_id')
s3a = s3fs.S3FileSystem()
columns=['imdb_id', 'score', 'reviewID', 'title', 'userID', 'user', 'date', 'review']
for k, folder in enumerate(folders):
# Read reviews
print(f'Reading folder ({k+1}/{len(folders)})...')
path = f's3a://{bucket}/{prefix}/{folder}'
review_df = pd.read_parquet(path, columns=columns, filesystem=s3a) \
.join(movie_df, on='imdb_id', how='inner') \
.drop(columns=['imdb_id'])
# Load reviews into database
print(f'Loading {len(review_df)} records into database...')
if_exists = 'replace' if k == 0 else 'append'
review_df.to_sql('imdb_reviews', engine, if_exists=if_exists, index=False,
dtype={'score': SmallInteger(), 'date': Date()})
# Set primary and foreign keys
if k == 0:
with engine.connect() as conn:
conn.execute('ALTER TABLE imdb_reviews ADD PRIMARY KEY (reviewID)')
conn.execute('ALTER TABLE imdb_reviews ADD FOREIGN KEY (movie_id) REFERENCES movies(id)')
conn.execute('ALTER TABLE imdb_reviews ADD INDEX (movie_id)')
class User(BaseModel,db.Model):
"""用户表"""
__tablename__='user'
USER_SEX = (
(0, 'women'),
(1, 'man'),
(2, 'secrecy')
)
id=db.Column(Integer,primary_key=True)
userName=db.Column(String(64),nullable=False,unique=True,comment='用户名')
pwd=db.Column(String(256),nullable=False,comment='密码')
nickName=db.Column(String(64),nullable=False,comment='昵称')
#需手动修改migrate表文件
sex = db.Column(ChoiceType(USER_SEX, SmallInteger()), comment='性别',default=2)
birth=db.Column(DATE,comment='生日')
portrait=db.Column(String(256), comment='头像',nullable=False)
email=db.Column(String(128),unique=True,comment='邮箱')
is_email=db.Column(Boolean,comment='认证邮箱',default=False)
province=db.Column(String(64),comment='省份')
city=db.Column(String(64),comment='城市')
home=db.Column(String(64),comment='地区')
role_id=db.Column(Integer,db.ForeignKey('role.id'))
friends_strategy_id=db.Column(db.Integer,db.ForeignKey('friends_strategy.id'),comment='添加好友策略')
#用了back_populates,另一张表也必须指明!
role = db.relationship('Role', back_populates='users',lazy='joined')
# #我的反馈
feedback=db.relationship('Feedback',lazy='dynamic',backref=db.backref('user',lazy='joined'))
#我的反馈回复
feedbackReply=db.relationship('FeedbackReply',lazy='dynamic',backref=db.backref('user',lazy='joined'))
#我创建的聊天室
MychatRooms=db.relationship('ChatRoom',lazy='dynamic',backref='founder')
# from apps.friend.model import ChatFriend
# # 我的所有好友
# friends=db.relationship('ChatFriend',lazy='dynamic',backref='user',
# foreign_keys='ChatFriend.self_id')
#lazy="dynamic",懒加载,用到时才会去查询
#lazy="select",直接查询
#lazy="joined",连表查询
#lazy="subquery": 与joined类似，但使用子子查询
#db.backref('_class',lazy="dynamic"), lazy="dynamic")
#areas = db.relationship("Area", backref="users", secondary=user_area,lazy='dynamic')
def __init__(self, **kwargs):
super(User, self).__init__(**kwargs)
self.set_role()
self.set_portrait()
self.password_hash=self.pwd
def __repr__(self):
return self.userName
@property
def platform(self):
ua = request.user_agent.platform or ''
if 'android' in ua or 'Linux' in ua:
return 0
elif 'iphone' in ua:
return 1
else:
return 2
@property
def is_admin(self):
return self.role.name == 'Administrator'
@property
def password_hash(self):
raise AttributeError('only setter attribute')
@password_hash.setter
def password_hash(self,value):
self.pwd=generate_password_hash(value)
def check_password(self,pwd):
return check_password_hash(self.pwd,pwd)
def to_dict(self,values=None):
data=super().to_dict(values)
data.pop('pwd')
data['pre']='user'
return data
def set_role(self):
"""设置默认角色"""
if self.role is None:
if self.email == current_app.config['ADMIN_EMAIL']:
self.role = Role.query.filter_by(name='Administrator').first()
else:
self.role = Role.query.filter_by(name='User').first()
def set_portrait(self):
"""设置默认头像"""
if not self.portrait:
self.portrait = 'https://img.yzcdn.cn/vant/cat.jpeg'
def can(self, permission_name):
permission = Permission.query.filter_by(name=permission_name).first()
return permission is not None and self.role is not None and permission in self.role.permissions
class OktmoObj(Base):
__tablename__ = 'oktmo'
CLS_ENUM = (
u'сф', # субъект
u'мр', # муниципальный район
u'го', # городской округ
u'гп', # городское поселение
u'сп', # сельское поселение
u'мс', # межселенная территория
u'тгфз' # внутригородская территория города федерального значения
)
id = C(BigInteger, primary_key=True)
code = C(Unicode(8)) # код ОКТМО
raw = C(Unicode(255)) # строка из ОКТМО
parent = C(Unicode(8)) # родитель с учетом группировки
parent_obj = C(Unicode(8)) # родетель без учета группировок
is_group = C(Boolean()) # это группировка ?
lvl = C(SmallInteger()) # уровень без учета группировок
cls = C(Enum(*CLS_ENUM, **{'native_enum': False})) # класс
is_subject = C(Boolean()) # это субъект?
simple_name = C(Unicode(255)) # упрощенное название
def parse(self, lookup=None):
self.id = int(self.code)
# убираем сноску с конца строки
if self.raw[-1] == '*':
self.raw = self.raw[:-1]
# код заканчивается на n нулей
zeroes = lambda n: self.code.endswith('0' * n)
# первые n cимволов кода
first = lambda n: self.code[:n]
# дополненные нулями
first_fill = lambda n: fill(self.code[:n])
p1 = int(self.code[2]) # признак 1 - 3-й разряд
p2 = int(self.code[5]) # признак 2 - 6-й разряд
g1 = int(self.code[3:5])
g2 = int(self.code[6:8])
if self.raw[-1] == '/':
self.is_group = True
if zeroes(5):
# субъекты закодированные на втором уровне
if self.code[:3] in SUBJ_AD:
self.is_group = False
self.is_subject = True
self.cls = 'сф'
self.parent = first_fill(2)
elif zeroes(4):
# группировка МР и ГО внутри авт. округов
self.parent = first_fill(3)
elif zeroes(2):
self.parent = first_fill(5)
else:
self.is_group = False
if zeroes(6):
self.lvl = 1
self.is_subject = True
self.cls = 'сф'
elif zeroes(3):
self.lvl = 2
self.parent_obj = fill(first(2))
if p1 == 8:
if g1 in range(10, 50):
self.cls = 'мр'
elif g1 in range(50, 99):
self.cls = 'го'
elif p1 == 6:
self.cls = 'мр'
elif p1 == 7:
self.cls = 'го'
elif p1 == 3:
self.cls = 'тгфз'
elif p1 == 9:
if lookup(OktmoObj, OktmoObj.code == fill(first(2) + '3')):
self.cls = 'тгфз'
elif g1 in range(11, 50):
self.cls = 'мр'
elif g1 in range(50, 100):
self.cls = 'го'
if not self.cls:
stderr.write(u'Неопознаный признак P1 %d в %s %s\n' %
(p1, self.code, self.raw))
else:
self.lvl = 3
if p2 == 1:
self.cls = 'гп'
elif p2 == 4:
self.cls = 'сп'
elif p2 == 7:
self.cls = 'мс'
if not self.cls:
stderr.write(u'Неопознаный признак P2 %d в %s %s\n' %
(p2, self.code, self.raw))
if self.lvl > 1 and not self.parent:
self.parent = fill(self.code[:{2: 3, 3: 6}[self.lvl]])
# parent_obj
if self.parent:
op = lookup(OktmoObj, OktmoObj.code == self.parent)
if not op:
stderr.write(u'Неверный родитель для %s %s\n' %
(self.code, self.raw))
elif not op.is_group:
self.parent_obj = op.code
elif op.is_group and op.parent_obj:
self.parent_obj = op.parent_obj
else:
stderr.write(u"Двойная группировка: %s %s\n" %
(self.code, self.raw))
if not self.is_group:
self.simple_name = self.raw
for r in SIMPLE_NAME_REs:
if self.cls in r[0]:
for p in r[1]:
exp = re.compile(p[0], re.UNICODE + re.IGNORECASE)
self.simple_name = exp.sub(p[1], self.simple_name)
def __init__(self,
db,
relevant_classes=DocumentClass.default_relevant_set,
load_titles=True,
load_classifications=True,
overwrite=False,
allow_doc_dups=True):
"""
Create a new DataLoader.
:param db: SQL Alchemy database connection.
:param relevant_classes: List of document classification prefix strings to treat as relevant.
:param load_titles Flag indicating whether document titles should be loaded at all
:param load_classifications Flag indicating whether document classifications should be loaded at all
:param overwrite Flag indicating if existing documents should be overwritten - results in all existing
titles, classifications, and mappings being replaced for the document!
:param allow_doc_dups: Flag indicating whether duplicate documents should be ignored
"""
logger.info("Life-sci relevant classes: {}".format(relevant_classes))
logger.info("Duplicate docs allowed? {}".format(allow_doc_dups))
self.db = db
self.relevant_classes = relevant_classes
self.load_titles = load_titles
self.load_classifications = load_classifications
self.overwrite = overwrite
self.allow_document_dups = allow_doc_dups
self.relevant_regex = re.compile('|'.join(relevant_classes))
self.metadata = MetaData()
self.doc_id_map = dict()
self.existing_chemicals = set()
# This SQL Alchemy schema is a very useful programmatic tool for manipulating and querying the SureChEMBL data.
# It's mostly used for testing, except for document insertion where 'inserted_primary_key' is used to
# avoid costly querying of document IDs
self.docs = Table(
'schembl_document', self.metadata,
Column('id',
Integer,
Sequence('schembl_document_id'),
primary_key=True), Column('scpn', String(50), unique=True),
Column('published', Date()),
Column('life_sci_relevant', SmallInteger()),
Column('assign_applic', String(4000)),
Column('family_id', Integer))
self.titles = Table(
'schembl_document_title', self.metadata,
Column('schembl_doc_id',
Integer,
ForeignKey('schembl_document.id'),
primary_key=True),
Column('lang', String(10), primary_key=True),
Column('text', Text()))
self.classes = Table(
'schembl_document_class', self.metadata,
Column('schembl_doc_id',
Integer,
ForeignKey('schembl_document.id'),
primary_key=True),
Column('class', String(100), primary_key=True),
Column('system', SmallInteger(), primary_key=True))
self.chemicals = Table('schembl_chemical', self.metadata,
Column('id', Integer, primary_key=True),
Column('mol_weight', Float()),
Column('logp', Float()),
Column('med_chem_alert', SmallInteger()),
Column('is_relevant', SmallInteger()),
Column('donor_count', SmallInteger()),
Column('acceptor_count', SmallInteger()),
Column('ring_count', SmallInteger()),
Column('rot_bond_count', SmallInteger()),
Column('corpus_count', Integer()))
self.chem_structures = Table(
'schembl_chemical_structure', self.metadata,
Column('schembl_chem_id',
Integer,
ForeignKey('schembl_chemical.id'),
primary_key=True), Column('smiles', Text()),
Column('std_inchi', Text()), Column('std_inchikey', String(27)))
self.chem_mapping = Table(
'schembl_document_chemistry', self.metadata,
Column('schembl_doc_id',
Integer,
ForeignKey('schembl_document.id'),
primary_key=True),
Column('schembl_chem_id',
Integer,
ForeignKey('schembl_chemical.id'),
primary_key=True),
Column('field', SmallInteger, primary_key=True),
Column('frequency', Integer))
# Define types for chemical structure inserts
if ("cx_oracle" in str(db.dialect)):
logger.info( "cx_oracle dialect detected, setting CLOB input types for structure INSERT statements."\
" (required for long strings inserted as part of executemany operations)" )
import cx_Oracle
self.chem_struc_types = (None, cx_Oracle.CLOB, cx_Oracle.CLOB,
None)
else:
self.chem_struc_types = None
class OkatoObj(Base):
__tablename__ = 'okato'
CLS_ENUM = (
u'адм_район', # районы субъекта
u'город', # города
u'пгт', # поселки городского типа
u'город|пгт',
u'гфз_1', # первый уровень деления ГФЗ: округа Москвы, районы Спб
u'гфз_2', # второй уровень деления ГФЗ: районы Москвы, округа Спб
u'нп',
u'сельсовет',
u'unknown',
u'гор_район' # район города, или городского округа
)
id = C(BigInteger, primary_key=True)
code = C(Unicode(11), unique=True, nullable=False) # код ОКАТО
raw = C(Unicode(255)) # строка из ОКАТО as-is
is_group = C(Boolean()) # это группировка ?
parent = C(Unicode(11)) # родитель с учетом группировок
parent_obj = C(Unicode(11)) # родитель без учета группировок
lvl = C(SmallInteger()) # уровень
cls = C(Unicode(10)) # класс
is_settlement = C(Boolean()) # это населенный пункт
is_subject = C(Boolean()) # это субъект
name = C(Unicode(100)) # имя без статусной части
status = C(Unicode(100)) # статусная часть
cl_class = None
cl_level = None
manager = None
def parse(self, lookup=None):
code = self.code
raw = self.raw
self.manager = None
# код заканчивается на n нулей
zeroes = lambda n: self.code.endswith('0' * n)
# все группировки заканчиваются на '/'
self.is_group = raw[-1] == '/'
p1 = int(code[2]) # признак 1 - разряд 3
p2 = int(code[5]) # признак 2 - разряд 6
v1 = int(code[3:5]) # разряды 4-5
v2 = int(code[6:8]) # разряды 7-8
level = None
if self.is_group:
if len(code) == 8:
if zeroes(5):
self.parent = fill(code[:2])
elif zeroes(4):
pst = int(code[3])
while True:
p = fill(code[:3] + str(pst))
print p
po = lookup(OkatoObj, OkatoObj.code == p)
if po:
self.parent = po.code
stderr.write(
"[%s] %s > [%s] %s\n" %
(self.code, self.raw, po.code, po.raw))
break
else:
pst = pst - 1
if pst < 0:
stderr.write(
u"Не удалось определить родителя для %s %s\n"
% (self.code, self.raw))
break
elif code.endswith(('00', '50')):
self.parent = code[:5] + '000'
if len(code) == 11:
assert code[-3:] == '000', 'Ошибка в группировке'
self.parent = fill(code[:8])
self.parent_obj = fill(code[:8])
elif len(code) == 8 and not self.is_group:
if zeroes(6):
self.cl_level = 1
self.parent = None
self.parent_obj = None
if zeroes(5):
# это автономный округ
self.cls = 'ао'
self.parent = fill(code[:2])
self.parent_obj = fill(code[:2])
elif zeroes(3):
self.cl_level = 2
self.parent = code[:3] + '00000'
self.parent_obj = code[:2] + '000000'
if p1 == 1:
pst = int(code[3])
while True:
p = fill(code[:3] + str(pst))
po = lookup(OkatoObj, OkatoObj.code == p)
if po and po.is_group:
self.parent = po.code
stderr.write(
"[%s] %s > [%s] %s\n" %
(self.code, self.raw, po.code, po.raw))
break
else:
pst = pst - 1
elif p1 == 2:
self.parent_obj = code[:2] + '000000'
if v1 in range(1, 60):
self.cl_class = 'адм_район'
self.parent = code[:3] + '00000'
elif v1 in range(60, 100):
self.cl_class = 'гфз_1'
self.parent = code[:3] + '60000'
elif p1 == 4:
# по описанию статуc должен зависеть от v1,
# но на московской области это не работает,
# поэтому город или пгт
# попробуем посмотреть группировку верхнего уровня
p_code = code[:3] + '00000'
parent_group = lookup(
OkatoObj, OkatoObj.code == p_code.encode('utf-8'))
pr = parent_group.raw
if pr.startswith(u'Города'):
self.cl_class = 'город'
elif pr.startswith(u'Поселки городского типа'):
self.cl_class = 'пгт'
elif p1 == 5:
# это значения признака в классификаторе не описано,
# на московской области вроде бы работает
if v1 in range(1, 60):
self.cl_class = 'город'
elif v1 in range(60, 100):
self.cl_class = 'пгт'
else:
self.cl_level = 3
self.parent = code[:7] + '0'
self.parent_obj = code[:5] + '000'
if p2 == 3:
self.cl_class = 'гор_район'
elif p2 == 5:
if v2 in range(1, 50):
self.cl_class = 'город'
elif v2 in range(50, 100):
if v1 in range(60, 100):
self.cl_class = 'гфз_2'
elif v1 in range(1, 60):
self.cl_class = 'пгт'
elif p2 == 6:
# в самарской области сюда попадают устраненные НП в Тольяти
self.cl_class = 'unknown'
elif p2 == 8:
self.cl_class = 'сельсовет'
elif len(code) == 11 and not self.is_group:
self.cl_level = 4
self.cl_class = 'нп'
self.parent = fill(code[:8], to_len=11)
self.parent_obj = fill(code[:8])
# на первом уровне все субъекты, на втором только то, что еще не успели упразднить
self.is_subject = self.cl_level == 1 or (code in SUBJ_AD)
self.is_district = len(code) == 8 and code[2] == '2' and code[
-3:] == '000' and code[3:5] <> '00'
self.is_city = len(code) == 8 and code[2] == '4' and code[
-3:] == '000' and code[3:5] <> '00'
if self.cl_class in ('город', 'пгт', 'город|пгт'):
self.is_settlement = True
if self.cl_class in ('город'):
self.name = raw
self.status = self.cl_class
elif self.cl_class in ('пгт'):
self.name = raw
self.status = "поселок городского типа"
if len(code) == 11 and not self.is_group:
# сельские НП
self.is_settlement = True
self.lvl = self.cl_level
self.cls = self.cl_class
# определяем статус
for ss in _STATUS_SEARCH:
m = ss[0].match(raw)
if m:
(self.name, self.status) = (m.group(1), ss[1])
if self.is_settlement and not self.status:
stderr.write(u"Не удалось определить статус НП %s [%s]\n" %
(self.code, self.raw))
if self.code in SUBJ_AD:
self.parent = fill(self.code[:2])
'Date': Date(), 'Schd Time Start': Time(), 'Schd Time End': Time(), 'Latitude': Float(precision=7, asdecimal=True), 'Longitude': Float(precision=7, asdecimal=True), 'Customer number': Float(precision=4), 'Customer Name': String(length=800), 'Site Name': String(length=800), 'Address 1': String(length=1200), 'Address 2': String(length=1200), 'City': String(length=500), 'State': String(length=30), 'PostCode': Integer(), 'Zone': String(length=500), 'Phone': String(length=600), 'Qty Scheduled': SmallInteger(), 'Qty Serviced': SmallInteger(), 'Serv Type': String(length=600), 'Container Type': String(length=20), 'Bin Volume': Float(precision=5), 'Status': String(length=5), 'Truck number': String(length=50), 'Route number': String(length=50), 'Generate ID': String(length=500), 'Initial Entry Date': DateTime(), 'Weight': Float(precision=5), 'Prorated Weight': Float(precision=5), 'Booking Reference 1': String(length=200), 'Booking Reference 2': String(length=200), 'Alternate Ref No 1': String(length=200), 'Alternate Ref No 2': String(length=200),
def insert_booking_df_to_stage_table_db_1(path):
print()
print(path)
# encoding='iso8859' encoding='cp1252'
df = (
pd
.read_csv(
path,
dtype={"Schd Time Start" : str, "PO": str, "Route number" : str},
encoding='iso8859')
)
df["Date"] = pd.to_datetime(df["Date"], format='%d/%m/%y')
df[['Route number', 'Weekday']] = df['Route number'].str.split('-', 1, expand=True)
db = "STAGE_TABLES_DB_1"
engine_str = f"mssql+pyodbc://SA:ploi?H8597@gordonswsmaster/{db}?driver=ODBC+Driver+17+for+SQL+Server"
engine = create_engine(engine_str)
df.to_sql(name="STAGE_TABLE_1", con=engine, schema="BOOKING", if_exists="replace", method='multi' ,dtype={
'Job No': Float(precision=5, asdecimal=True),
'Date': Date,
'Schd Time Start': String(length=350),
'Schd Time End': String(length=350),
'Latitude': Float(precision=7, asdecimal=True),
'Longitude': Float(precision=7, asdecimal=True),
'Customer number': Float(precision=4),
'Customer Name': String(length=800),
'Site Name': String(length=800),
'Address 1': String(length=1200),
'Address 2': String(length=1200),
'City': String(length=500),
'State': String(length=30),
'PostCode': Integer(),
'Zone': String(length=500),
'Phone': String(length=600),
'Qty Scheduled': SmallInteger(),
'Qty Serviced': SmallInteger(),
'Serv Type': String(length=600),
'Container Type': String(length=20),
'Bin Volume': Float(precision=5),
'Status': String(length=5),
'Truck number': String(length=50),
'Route number': String(length=50),
'Generate ID': String(length=500),
'Initial Entry Date': String(length=350),
'Weight': Float(precision=5),
'Prorated Weight': Float(precision=5),
'Booking Reference 1': String(length=200),
'Booking Reference 2': String(length=200),
'Alternate Ref No 1': String(length=200),
'Alternate Ref No 2': String(length=200),
'Alternate Service Ref 1': String(length=200),
'Alternate Service Ref 2': String(length=200),
'Notes': Text(length=8000),
'Directions': Text(length=8000),
'CheckLists': String(length=300),
'Waste Type': String(length=350),
'Tip Site': String(length=450),
'Price': Integer(),
'PO': String(length=200)
})
class PingboBetBill(Base):
#平博注单模型类
__tablename__ = 'dj_pingbobetbill'
wagerId = Column(Integer(), default=0, primary_key=True)
sport = Column(String(45), default="")
league = Column(String(45), default="")
eventName = Column(String(256), default='')
homeTeam = Column(String(45), default="")
awayTeam = Column(String(45), default="")
selection = Column(String(45), default=0)
oddsFormat = Column(Integer(), default=0)
odds = Column(Float(), default=0)
stake = Column(Float(), default=0)
betType = Column(SmallInteger(), default=0)
eventDateFm = Column(Integer(), default='')
result = Column(String(32), default='')
status = Column(String(32), default='')
toWin = Column(String(32), default='')
toRisk = Column(String(32), default='')
winLoss = Column(Float(), default='')
currencyCode = Column(String(32), default='')
userCode = Column(String(45), default='')
loginId = Column(String(45), default='')
product = Column(String(32), default='')
wagerDateFm = Column(Integer(), default=0)
agentId = Column(String(32), default='')
settleDateFm = Column(Integer(), default=0)
__table_args__ = {'mysql_charset': 'utf8mb4'}
@staticmethod
def getUpdateSql(dictBill: dict):
from lib.timehelp.timeHelp import strToTimestamp, str2TimeStamp
tbl = Base.metadata.tables["dj_pingbobetbill"]
objSql = tbl.update().where(
tbl.c.wagerId == dictBill['wagerId']).values(
wagerId=dictBill['wagerId'],
sport=dictBill['sport'],
league=dictBill["league"],
eventName=dictBill["eventName"],
homeTeam=dictBill["homeTeam"],
awayTeam=dictBill["awayTeam"],
selection=dictBill['selection'],
oddsFormat=dictBill["oddsFormat"],
odds=dictBill["odds"],
stake=dictBill["stake"],
betType=dictBill["betType"],
eventDateFm=str2TimeStamp(dictBill["eventDateFm"]),
result=dictBill['result'],
settleDateFm=0 if dictBill['settleDateFm'] is None
or dictBill['settleDateFm'] == '' else strToTimestamp(
dictBill['settleDateFm']),
status=dictBill["status"],
toWin=dictBill["toWin"],
toRisk=dictBill["toRisk"],
winLoss=dictBill['winLoss'],
currencyCode=dictBill['currencyCode'],
userCode=dictBill['userCode'],
loginId=dictBill['loginId'],
product=dictBill['product'],
wagerDateFm=strToTimestamp(dictBill['wagerDateFm']),
agentId=dictBill['agentId'])
return objSql
@staticmethod
def getSqlObj(dictBill: dict):
from lib.timehelp.timeHelp import strToTimestamp, str2TimeStamp
tbl = Base.metadata.tables["dj_pingbobetbill"]
objSql = tbl.insert().values(
wagerId=dictBill['wagerId'],
sport=dictBill['sport'],
league=dictBill["league"],
eventName=dictBill["eventName"],
homeTeam=dictBill["homeTeam"],
awayTeam=dictBill["awayTeam"],
selection=dictBill['selection'],
oddsFormat=dictBill["oddsFormat"],
odds=dictBill["odds"],
stake=dictBill["stake"],
betType=dictBill["betType"],
eventDateFm=str2TimeStamp(dictBill["eventDateFm"]),
result=dictBill['result'],
settleDateFm=0 if dictBill['settleDateFm'] is None
or dictBill['settleDateFm'] == '' else strToTimestamp(
dictBill['settleDateFm']),
status=dictBill["status"],
toWin=dictBill["toWin"],
toRisk=dictBill["toRisk"],
winLoss=dictBill['winLoss'],
currencyCode=dictBill['currencyCode'],
userCode=dictBill['userCode'],
loginId=dictBill['loginId'],
product=dictBill['product'],
wagerDateFm=strToTimestamp(dictBill['wagerDateFm']),
agentId=dictBill['agentId'])
return objSql
path = f's3a://{bucket}/{key}'
review_df = pd.read_parquet(path, columns=columns, filesystem=s3a) \
.join(match_df, on='asin', how='inner') \
.join(movie_df, on='imdb_id', how='inner') \
.drop(columns=['asin', 'imdb_id'])
review_df.reviewTime = pd.to_datetime(review_df.reviewTime,
format='%m %d, %Y')
# Load partition into database
print(f'Loading {len(review_df)} records into database...')
if_exists = 'replace' if k == 0 else 'append'
review_df.to_sql('amazon_reviews',
engine,
if_exists=if_exists,
index=False,
dtype={
'overall': SmallInteger(),
'reviewTime': Date()
})
# Set primary and foreign keys
if k == 0:
with engine.connect() as conn:
conn.execute(
'ALTER TABLE amazon_reviews ADD COLUMN `id` int(10) unsigned PRIMARY KEY AUTO_INCREMENT'
)
conn.execute(
'ALTER TABLE amazon_reviews ADD FOREIGN KEY (movie_id) REFERENCES movies(id)'
)
conn.execute('ALTER TABLE amazon_reviews ADD INDEX (movie_id)')
# Load data to database
engine = create_engine(f'mysql://{user}:{password}@{host}/{database}')
pd.DataFrame(titleTypes, columns=['name']) \
.to_sql('titleTypes', engine, if_exists='replace', index_label='id')
pd.DataFrame(genres, columns=['name']) \
.to_sql('genres', engine, if_exists='replace', index_label='id')
movies.to_sql('movies',
engine,
if_exists='replace',
index_label='id',
dtype={
'year': SmallInteger(),
'runtime': SmallInteger()
})
movie_genres.to_sql('movie_genres',
engine,
if_exists='replace',
index_label='movie_id')
# Set primary and foreign keys
with engine.connect() as conn:
conn.execute('ALTER TABLE titleTypes ADD PRIMARY KEY (id)')
conn.execute('ALTER TABLE genres ADD PRIMARY KEY (id)')
conn.execute('ALTER TABLE movies ADD PRIMARY KEY (id)')
conn.execute(
'ALTER TABLE movies ADD FOREIGN KEY (titleType) REFERENCES titleTypes(id)'
class LogVisit(db.Model):
__bind_key__ = 'matomo'
idvisit = db.Column(BigInteger(), primary_key=True)
idsite = db.Column(Integer())
idvisitor = db.Column(Binary())
visit_last_action_time = db.Column(DateTime())
config_id = db.Column(Binary())
location_ip = db.Column(Binary())
user_id = db.Column(String(200))
visit_first_action_time = db.Column(DateTime())
visit_goal_buyer = db.Column(Integer())
visit_goal_converted = db.Column(Integer())
visitor_days_since_first = db.Column(SmallInteger())
visitor_days_since_order = db.Column(SmallInteger())
visitor_returning = db.Column(Integer())
visitor_count_visits = db.Column(Integer())
visit_entry_idaction_name = db.Column(Integer())
visit_entry_idaction_url = db.Column(Integer())
visit_exit_idaction_name = db.Column(Integer())
visit_exit_idaction_url = db.Column(Integer())
visit_total_actions = db.Column(Integer())
visit_total_interactions = db.Column(SmallInteger())
visit_total_searches = db.Column(SmallInteger())
referer_keyword = db.Column(String(255))
referer_name = db.Column(String(70))
referer_type = db.Column(Integer())
referer_url = db.Column(Text())
location_browser_lang = db.Column(String(20))
config_browser_engine = db.Column(String(10))
config_browser_name = db.Column(String(10))
config_browser_version = db.Column(String(20))
config_device_brand = db.Column(String(100))
config_device_model = db.Column(String(100))
config_device_type = db.Column(Integer())
config_os = db.Column(String(3))
config_os_version = db.Column(String(100))
visit_total_events = db.Column(Integer())
visitor_localtime = db.Column(Time())
visitor_days_since_last = db.Column(SmallInteger())
config_resolution = db.Column(String(18))
config_cookie = db.Column(Integer())
config_director = db.Column(Integer())
config_flash = db.Column(Integer())
config_gears = db.Column(Integer())
config_java = db.Column(Integer())
config_pdf = db.Column(Integer())
config_quicktime = db.Column(Integer())
config_realplayer = db.Column(Integer())
config_silverlight = db.Column(Integer())
config_windowsmedia = db.Column(Integer())
visit_total_time = db.Column(Integer())
location_city = db.Column(String(255))
location_country = db.Column(String(3))
location_latitude = db.Column(Decimal(9, 6))
location_longitude = db.Column(Decimal(9, 6))
location_region = db.Column(String(2))
custom_var_k1 = db.Column(String(200))
custom_var_v1 = db.Column(String(200))
custom_var_k2 = db.Column(String(200))
custom_var_v2 = db.Column(String(200))
custom_var_k3 = db.Column(String(200))
custom_var_v3 = db.Column(String(200))
custom_var_k4 = db.Column(String(200))
custom_var_v4 = db.Column(String(200))
custom_var_k5 = db.Column(String(200))
custom_var_v5 = db.Column(String(200))
location_provider = db.Column(String(200))
location_hostname = db.Column(String(255))
example_visit_dimension = db.Column(Integer())
class Meta:
managed = False
db_table = 'log_visit'
verbose_name_plural = db_table
class Organization(CommonModel):
__tablename__ = 'organization'
id = db.Column(String, primary_key=True, default=default_uuid)
code = db.Column(String(255), nullable=True)
name = db.Column(String(), nullable=False)
unsigned_name = db.Column(String())
phone = db.Column(String(63))
address = db.Column(String())
email = db.Column(String(255))
description = db.Column(String())
tinhthanh_id = db.Column(String, ForeignKey('tinhthanh.id'), nullable=True)
tinhthanh = relationship('TinhThanh', viewonly=True)
quanhuyen_id = db.Column(String, ForeignKey('quanhuyen.id'), nullable=True)
quanhuyen = relationship('QuanHuyen')
xaphuong_id = db.Column(String, ForeignKey('xaphuong.id'), nullable=True)
xaphuong = relationship('XaPhuong')
level = db.Column(SmallInteger, nullable=True) # Trạm y tế, bệnh viện ...
parent_name = db.Column(String(255))
parent_id = db.Column(String, ForeignKey('organization.id'), nullable=True)
users = relationship('User', viewonly=True)
active = db.Column(SmallInteger(), default=0)
type_donvi = db.Column(String(255)) # donvicungung / donvinhanuoc
list_unused_medical_supplies = db.Column(JSONB())
tuyendonvi_id = db.Column(String(255),
ForeignKey('tuyendonvi.id'),
nullable=True)
tuyendonvi = relationship('TuyenDonVi')
children = relationship(
"Organization",
# cascade deletions
cascade="all, delete-orphan",
# many to one + adjacency list - remote_side
# is required to reference the 'id'
# column in the join condition.
backref=backref("parent", remote_side=id),
# children will be represented as a dictionary
# on the "id" attribute.
collection_class=attribute_mapped_collection('id'),
)
def __repr__(self):
return "Organization(id=%r, name=%r, parent_id=%r, level=%r)" % (
self.id, self.name, self.parent_id, self.level)
def __todict__(self):
return {
"id": self.id,
"code": self.code,
"name": self.name,
"parent_id": self.parent_id,
"level": self.level
}
def __toid__(self):
return self.id
def dump(self, _indent=0):
obj = self.__todict__()
#obj["tuyendonvi"] = to_dict(self.tuyendonvi)
obj["nodes"] = [c.dump() for c in self.children.values()]
return obj
def get_children_ids(self, data):
if type(data) is list:
data.append(self.id)
for r in self.children.values():
r.get_children_ids(data)
def getlistid(self):
data = []
self.get_children_ids(data)
return data
class Category(db.Model):
__tablename__ = 'categories'
category_id = Column(SmallInteger(), primary_key=True, index=True)
# revision identifiers, used by Alembic.
revision = 'c1c21b1515c7'
down_revision = '5fd694768c6c'
branch_labels = None
depends_on = None
LOCATION_DELIMETER = '.'
metadata = MetaData()
users_table = Table(
'users', metadata, Column('user_id', Integer(), nullable=False),
Column('email', String(length=256), nullable=False),
Column('name', String(collation='ru-RU-x-icu'), nullable=False),
Column('gender', Enum('female', 'male', name='gender'), nullable=False),
Column('location', String(), nullable=False),
Column('floor', SmallInteger(), nullable=False),
Column('seat', SmallInteger(), nullable=False),
PrimaryKeyConstraint('user_id', name='pk__users'),
UniqueConstraint('email', name='uq__users__email'))
def upgrade():
# Add new nullable columns
op.add_column('users', Column('floor', SmallInteger(), nullable=True))
op.add_column('users', Column('seat', SmallInteger(), nullable=True))
# Migrate data
query = users_table.update().values(floor=cast(
func.split_part(users_table.c.location, LOCATION_DELIMETER, 1),
Integer),
seat=cast(
def insert_records(path):
print(path)
df = pd.read_csv(path, dtype={
"Schd Time Start" : str,
"PO": str,
"Route number" : str,
"Notes" : str,
"Schd Time End" : str},
encoding='iso8859')
df["Date"] = pd.to_datetime(df["Date"], format='%d/%m/%y')
df[['Route number', 'Weekday']] = df['Route number'].str.split('-', 1, expand=True)
db = "STAGE_1_DB"
sch = "BOOKING_SCH_S1"
tablename = "BOOKING_TB_S1"
status = "append"
user = "******"
pwd = "Sydwaste123#"
server = "localhost"
driver = "ODBC+Driver+17+for+SQL+Server"
engine_str = f"mssql+pyodbc://{user}:{pwd}@{server}/{db}?driver={driver}"
engine = create_engine(engine_str)
try:
df.to_sql(name=tablename, con=engine, schema=sch, if_exists=status, dtype={
'Job No': Numeric(20, 5),
'Date': Date(),
'Schd Time Start': String(length=350),
'Schd Time End': String(length=350),
'Latitude': Numeric(20, 7),
'Longitude': Numeric(20, 7),
'Customer number': Numeric(20, 5),
'Customer Name': String(length=800),
'Site Name': String(length=800),
'Address 1': String(length=1200),
'Address 2': String(length=1200),
'City': String(length=500),
'State': String(length=30),
'PostCode': Integer(),
'Zone': String(length=500),
'Phone': String(length=600),
'Qty Scheduled': SmallInteger(),
'Qty Serviced': SmallInteger(),
'Serv Type': String(length=600),
'Container Type': String(length=20),
'Bin Volume': Float(precision=5),
'Status': String(length=5),
'Truck number': String(length=50),
'Route number': String(length=50),
'Generate ID': String(length=500),
'Initial Entry Date': String(length=350),
'Weight': Float(precision=5),
'Prorated Weight': Float(precision=5),
'Booking Reference 1': String(length=200),
'Booking Reference 2': String(length=200),
'Alternate Ref No 1': String(length=200),
'Alternate Ref No 2': String(length=200),
'Alternate Service Ref 1': String(length=200),
'Alternate Service Ref 2': String(length=200),
'Notes': Text(length=8000),
'Directions': Text(length=8000),
'CheckLists': String(length=300),
'Waste Type': String(length=350),
'Tip Site': String(length=450),
'Price': Numeric(8, 3),
'PO': String(length=200),
'Weekday' : SmallInteger()
})
except Exception:
print(Exception)
print(df['Job No'])
domains = Table(
'domains',
metadata,
Column('id', UUID, default=utils.generate_uuid, primary_key=True),
Column('version', Integer(), default=1, nullable=False),
Column('created_at', DateTime, default=lambda: timeutils.utcnow()),
Column('updated_at', DateTime, onupdate=lambda: timeutils.utcnow()),
Column('deleted',
CHAR(32),
nullable=False,
default='0',
server_default='0'),
Column('deleted_at', DateTime, nullable=True, default=None),
Column('shard',
SmallInteger(),
nullable=False,
default=lambda ctxt: default_shard(ctxt, 'id')),
Column('tenant_id', String(36), default=None, nullable=True),
Column('name', String(255), nullable=False),
Column('email', String(255), nullable=False),
Column('description', Unicode(160), nullable=True),
Column("type", Enum(name='type', *ZONE_TYPES), nullable=False),
Column('transferred_at', DateTime, default=None),
Column('ttl', Integer, default=CONF.default_ttl, nullable=False),
Column('serial', Integer, default=timeutils.utcnow_ts, nullable=False),
Column('refresh',
Integer,
default=CONF.default_soa_refresh,
nullable=False),
Column('retry', Integer, default=CONF.default_soa_retry, nullable=False),
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.create_table('feedback_type',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(length=64), nullable=True, comment='反馈类型'),
sa.Column('create_time', sa.DATETIME(), nullable=True, comment='创建时间'),
sa.Column('update_time', sa.DATETIME(), nullable=True, comment='修改时间'),
sa.Column('is_active', sa.Boolean(), nullable=True, comment='状态'),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('name')
)
op.create_table('permission',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(length=30), nullable=True, comment='权限名称'),
sa.Column('create_time', sa.DATETIME(), nullable=True, comment='创建时间'),
sa.Column('update_time', sa.DATETIME(), nullable=True, comment='修改时间'),
sa.Column('is_active', sa.Boolean(), nullable=True, comment='状态'),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('name')
)
op.create_table('role',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(length=30), nullable=True, comment='角色名称'),
sa.Column('create_time', sa.DATETIME(), nullable=True, comment='创建时间'),
sa.Column('update_time', sa.DATETIME(), nullable=True, comment='修改时间'),
sa.Column('is_active', sa.Boolean(), nullable=True, comment='状态'),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('name')
)
op.create_table('roles_permissions',
sa.Column('role_id', sa.Integer(), nullable=True),
sa.Column('permission_id', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(['permission_id'], ['permission.id'], ),
sa.ForeignKeyConstraint(['role_id'], ['role.id'], )
)
op.create_table('user',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('userName', sa.String(length=64), nullable=False, comment='用户名'),
sa.Column('pwd', sa.String(length=256), nullable=False, comment='密码'),
sa.Column('nickName', sa.String(length=64), nullable=False, comment='昵称'),
sa.Column('sex', sqlalchemy_utils.types.choice.ChoiceType(User.USER_SEX,SmallInteger()), nullable=True, comment='性别'),
sa.Column('birth', sa.DATE(), nullable=True, comment='生日'),
sa.Column('portrait', sa.String(length=256), nullable=False, comment='头像'),
sa.Column('email', sa.String(length=128), nullable=True, comment='邮箱'),
sa.Column('is_email', sa.Boolean(), nullable=True, comment='认证邮箱'),
sa.Column('province', sa.String(length=64), nullable=True, comment='省份'),
sa.Column('city', sa.String(length=64), nullable=True, comment='城市'),
sa.Column('home', sa.String(length=64), nullable=True, comment='地区'),
sa.Column('role_id', sa.Integer(), nullable=True),
sa.Column('create_time', sa.DATETIME(), nullable=True, comment='创建时间'),
sa.Column('update_time', sa.DATETIME(), nullable=True, comment='修改时间'),
sa.Column('is_active', sa.Boolean(), nullable=True, comment='状态'),
sa.ForeignKeyConstraint(['role_id'], ['role.id'], ),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('email'),
sa.UniqueConstraint('userName')
)
op.create_table('chat_room',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(length=64), nullable=True, comment='聊天室名称'),
sa.Column('create_user', sa.Integer(), nullable=True, comment='创建人'),
sa.Column('img', sa.String(length=256), nullable=True, comment='聊天室封面图'),
sa.Column('desc', sa.String(length=512), nullable=True, comment='聊天室简介'),
sa.Column('create_time', sa.DATETIME(), nullable=True, comment='创建时间'),
sa.Column('update_time', sa.DATETIME(), nullable=True, comment='修改时间'),
sa.Column('is_active', sa.Boolean(), nullable=True, comment='状态'),
sa.ForeignKeyConstraint(['create_user'], ['user.id'], ),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('name')
)
op.create_table('feedback_reply',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('title', sa.String(length=128), nullable=False, comment='回复标题'),
sa.Column('content', sa.TEXT(), nullable=False, comment='回复内容'),
sa.Column('user_id', sa.Integer(), nullable=False, comment='回复人'),
sa.Column('create_time', sa.DATETIME(), nullable=True, comment='创建时间'),
sa.Column('update_time', sa.DATETIME(), nullable=True, comment='修改时间'),
sa.Column('is_active', sa.Boolean(), nullable=True, comment='状态'),
sa.ForeignKeyConstraint(['user_id'], ['user.id'], ),
sa.PrimaryKeyConstraint('id')
)
op.create_table('chat_message',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('room_id', sa.Integer(), nullable=True, comment='信息发表房间'),
sa.Column('user_id', sa.String(length=256), nullable=True, comment='信息发表人'),
sa.Column('msg', sa.String(length=256), nullable=True, comment='聊天信息'),
sa.Column('create_time', sa.DATETIME(), nullable=True, comment='创建时间'),
sa.Column('update_time', sa.DATETIME(), nullable=True, comment='修改时间'),
sa.Column('is_active', sa.Boolean(), nullable=True, comment='状态'),
sa.ForeignKeyConstraint(['room_id'], ['chat_room.id'], ),
sa.PrimaryKeyConstraint('id')
)
op.create_table('feedback',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('content', sa.TEXT(), nullable=False, comment='反馈内容'),
sa.Column('contact', sa.String(length=128), nullable=True, comment='反馈联系方式'),
sa.Column('type_id', sa.Integer(), nullable=True, comment='反馈类型'),
sa.Column('user_id', sa.Integer(), nullable=True, comment='创建人'),
sa.Column('reply_id', sa.Integer(), nullable=True, comment='官方回复'),
sa.Column('image', sa.String(length=256), nullable=True, comment='反馈问题截图封面'),
sa.Column('create_time', sa.DATETIME(), nullable=True, comment='创建时间'),
sa.Column('update_time', sa.DATETIME(), nullable=True, comment='修改时间'),
sa.Column('is_active', sa.Boolean(), nullable=True, comment='状态'),
sa.ForeignKeyConstraint(['reply_id'], ['feedback_reply.id'], ),
sa.ForeignKeyConstraint(['type_id'], ['feedback_type.id'], ),
sa.ForeignKeyConstraint(['user_id'], ['user.id'], ),
sa.PrimaryKeyConstraint('id')
)
op.create_table('user_chat_room',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('user_id', sa.String(length=256), nullable=True),
sa.Column('chat_room_id', sa.Integer(), nullable=True),
sa.Column('is_create_user', sa.Boolean(), nullable=True, comment='角色'),
sa.Column('create_time', sa.DATETIME(), nullable=True, comment='创建时间'),
sa.Column('update_time', sa.DATETIME(), nullable=True, comment='修改时间'),
sa.Column('is_active', sa.Boolean(), nullable=True, comment='状态'),
sa.ForeignKeyConstraint(['chat_room_id'], ['chat_room.id'], ),
sa.PrimaryKeyConstraint('id')
)
op.create_index(op.f('ix_user_chat_room_chat_room_id'), 'user_chat_room', ['chat_room_id'], unique=False)
op.create_index(op.f('ix_user_chat_room_user_id'), 'user_chat_room', ['user_id'], unique=False)
op.create_table('feedback_image',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('url', sa.String(length=256), nullable=False, comment='反馈图片url链接'),
sa.Column('feedback_id', sa.Integer(), nullable=True, comment='反馈'),
sa.Column('create_time', sa.DATETIME(), nullable=True, comment='创建时间'),
sa.Column('update_time', sa.DATETIME(), nullable=True, comment='修改时间'),
sa.Column('is_active', sa.Boolean(), nullable=True, comment='状态'),
sa.ForeignKeyConstraint(['feedback_id'], ['feedback.id'], ),
sa.PrimaryKeyConstraint('id')
)
class Salesorder(CommonModel):
__tablename__ = 'salesorder'
salesorder_exid = db.Column(String(100), index=True,
unique=True) #id tich hop tu he thong khac
salesorder_no = db.Column(String(100))
title = db.Column(String(255))
book_time = db.Column(BigInteger)
book_day = db.Column(SmallInteger())
book_month = db.Column(SmallInteger())
book_year = db.Column(SmallInteger())
book_hour = db.Column(SmallInteger())
book_minute = db.Column(SmallInteger())
book_day_of_week = db.Column(SmallInteger())
time_perform = db.Column(String(50))
contact_id = db.Column(UUID(as_uuid=True),
ForeignKey('contact.id'),
nullable=True)
contact = db.relationship("Contact")
room_id = db.Column(UUID(as_uuid=True),
ForeignKey("room.id",
onupdate="CASCADE",
ondelete="RESTRICT"),
nullable=False)
room = db.relationship("Room")
device_id = db.Column(String(), nullable=False)
# contact_name = db.Column(String(255))
# contact_phone = db.Column(String(50))
# contact_email = db.Column(String(100))
# contact_address = db.Column(Text())
# workstation_id = db.Column(UUID(as_uuid=True), ForeignKey('workstation.id'), nullable=True)
# workstation_name = db.Column(String(255))
people_number = db.Column(Integer)
hour_schedule = db.Column(String())
unwant_order = db.Column(Boolean(), default=False)
price_adjustment = db.Column(
FLOAT(15, 8),
default=0) #https://en.wikipedia.org/wiki/Price_adjustment_(retail)
salescommission = db.Column(FLOAT(15, 8),
default=0) # hoa hong cho nhan vien ban hang
exciseduty = db.Column(FLOAT(
15, 8), default=0) # thu tieu thu dac biet, danh cho hang hoa xa xi.
taxtype = db.Column(
String(25), default="group"
) #group: thue tren toan bill - individual: thue tren tung item
tax_percent = db.Column(FLOAT(7, 3), default=0)
tax_amount = db.Column(FLOAT(15, 8), default=0)
net_amount = db.Column(FLOAT(15, 8), default=0)
amount = db.Column(FLOAT(15, 8), default=0)
discount_amount = db.Column(FLOAT(
15, 8), default=0) # tiền giảm tự túc tại cửa hàng hoặc chỉnh sửa
discount_percent = db.Column(FLOAT(7, 3), default=0)
voucher_discount_amount = db.Column(FLOAT(
15, 8), default=0) # tiền giảm theo chương trình KM
card_swipe_fee_amount = db.Column(FLOAT(15, 8), default=0) # Phí quẹt thẻ
card_swipe_fee_percent = db.Column(FLOAT(7, 3), default=0)
other_fee_percent = db.Column(FLOAT(7, 3), default=0) # Phí khác
other_fee_amount = db.Column(FLOAT(15, 8), default=0)
ship_fee_amount = db.Column(FLOAT(15, 8), default=0)
ship_partner = db.Column(String(200))
terms_conditions = db.Column(Text())
purchaseorder = db.Column(String(200))
sostatus = db.Column(
String(200), default="wait_confirm"
) # wait_confirm, confirmed, transporting, processing, finished # saleorder_status
currency_id = db.Column(UUID(as_uuid=True),
ForeignKey('currency.id'),
nullable=True)
currency_code = db.Column(String(11), nullable=True)
currency = db.relationship("Currency")
conversion_rate = db.Column(FLOAT(10, 3)) # ty gia currency
payment_method = db.Column(String(50))
payment_status = db.Column(String(20))
note = db.Column(Text())
is_delivery = db.Column(Boolean(), default=False)
delivery_address = db.Column(String(255))
provider_id = db.Column(UUID(as_uuid=True),
ForeignKey("provider.id",
onupdate="RESTRICT",
ondelete="RESTRICT"),
nullable=True)
provider = db.relationship("Provider")
service_id = db.Column(UUID(as_uuid=True),
ForeignKey("service.id",
onupdate="RESTRICT",
ondelete="RESTRICT"),
nullable=False)
service = db.relationship("Service")
salesorder_items = db.relationship("SalesorderItems",
order_by="SalesorderItems.created_at",
cascade="all, delete-orphan",
lazy='dynamic')
tenant_id = db.Column(String(),
ForeignKey("tenant.id",
onupdate="RESTRICT",
ondelete="RESTRICT"),
nullable=False)
class Role(CommonModel):
__tablename__ = 'role'
role_name = db.Column(String(100), index=True, nullable=False, unique=True)
display_name = db.Column(String(255), nullable=False)
description = db.Column(String(255))
active = db.Column(SmallInteger(), default=1)
class OrderLine(Base):
__tablename__ = 'order_lines'
id = Column(Integer(), primary_key=True)
order_id = Column(Integer(), ForeignKey('orders.id'))
item_id = Column(Integer(), ForeignKey('items.id'))
quantity = Column(SmallInteger())
def test_generate_random_value_for_smallint(self):
for i in range(100):
self.assertIn(mommy.generate_value(SmallInteger()), [0, 1])
class Player(sqlalchemy_ext.Model):
__bind_key__ = 'game'
__tablename__ = 'SS13_player'
ckey = Column('ckey', String(32), primary_key=True)
byond_key = Column('byond_key', String(32))
firstseen = Column('firstseen', DateTime())
firstseen_round_id = Column('firstseen_round_id', Integer())
lastseen = Column('lastseen', DateTime())
lastseen_round_id = Column('lastseen_round_id', Integer())
ip = Column('ip', Integer())
computerid = Column('computerid', String(32))
uuid = Column('uuid', String(64))
lastadminrank = Column('lastadminrank', String(32))
accountjoindate = Column('accountjoindate', Date())
flags = Column('flags', SmallInteger())
antag_tokens = Column('antag_tokens', SmallInteger())
metacoins = Column('metacoins', Integer())
@classmethod
def from_ckey(cls, ckey):
try:
return db_session.query(cls).filter(cls.ckey == ckey).one()
except NoResultFound:
return None
def get_connection_count(self):
return db_session.query(Connection).filter(
Connection.ckey == self.ckey).count()
def get_death_count(self):
return db_session.query(Death).filter(
Death.byondkey == self.ckey).count()
def get_round_count(self):
# We have to only query the round_id so the distinct thing will work because haha sqlalchemy
return db_session.query(
Connection.round_id).filter(Connection.ckey == self.ckey).distinct(
Connection.round_id).count()
def get_bans(self):
return query_grouped_bans().filter(Ban.ckey == self.ckey).order_by(
Ban.bantime.desc()).all()
def get_notes(self):
return db_session.query(Note).filter(
Note.targetckey == self.ckey).order_by(Note.timestamp.desc())
def get_visible_notes(self):
return self.get_notes().filter(
Note.deleted == 0,
or_(Note.expire_timestamp == None,
Note.expire_timestamp > datetime.utcnow()))
def get_role_time(self, role):
try:
time_for_role = db_session.query(RoleTime).filter(
and_(RoleTime.ckey == self.ckey, RoleTime.job == role)).one()
return time_for_role.minutes
except NoResultFound:
return 0
def get_total_playtime(self):
living_time = self.get_role_time("Living")
ghost_time = self.get_role_time("Ghost")
return living_time + ghost_time
def get_favorite_job(self):
try:
most_played_role = db_session.query(RoleTime).filter(
and_(
RoleTime.ckey == self.ckey,
RoleTime.job !=
"Living", # probably not the best way to do this but.... UGHHH
RoleTime.job != "Ghost",
RoleTime.job != "Admin",
RoleTime.job != "Mentor")).order_by(
RoleTime.minutes.desc()).first()
return most_played_role
except NoResultFound:
return None
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.create_table(
'go_ontology',
sa.Column('ontology', sa.String(length=1), nullable=False),
sa.PrimaryKeyConstraint('ontology', name=op.f('pk_go_ontology')))
op.create_table('predictor', sa.Column('name', sa.String(),
nullable=False),
sa.PrimaryKeyConstraint('name', name=op.f('pk_predictor')))
op.create_table(
'sequence', sa.Column('id', sa.String(), nullable=False),
sa.Column('sequence', sa.Text(), nullable=False),
sa.Column('length', sa.Integer(), nullable=False),
sa.Column('predictable', sa.Boolean(), nullable=True),
sa.CheckConstraint('char_length(sequence) = length',
name=op.f('ck_sequence_length')),
sa.PrimaryKeyConstraint('id', name=op.f('pk_sequence')))
op.create_table(
'disorder_prediction', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('sequence_id', sa.String(), nullable=True),
sa.Column('predictor_name', sa.String(), nullable=True),
sa.Column('is_disordered', sa.Boolean(), nullable=True),
sa.ForeignKeyConstraint(
['predictor_name'], ['predictor.name'],
name=op.f('fk_disorder_prediction_predictor_name_predictor')),
sa.ForeignKeyConstraint(
['sequence_id'], ['sequence.id'],
name=op.f('fk_disorder_prediction_sequence_id_sequence')),
sa.PrimaryKeyConstraint('id', name=op.f('pk_disorder_prediction')),
sa.UniqueConstraint(
'predictor_name',
name=op.f('uq_disorder_prediction_predictor_name')),
sa.UniqueConstraint('sequence_id',
name=op.f('uq_disorder_prediction_sequence_id')))
op.create_table(
'go_term',
sa.Column('go', sa.Integer(), autoincrement=False, nullable=False),
sa.Column('ontology', sa.String(length=1), nullable=False),
sa.ForeignKeyConstraint(['ontology'], ['go_ontology.ontology'],
name=op.f('fk_go_term_ontology_go_ontology')),
sa.PrimaryKeyConstraint('go', name=op.f('pk_go_term')))
op.create_table(
'disorder_prediction_raw',
sa.Column('pr_id', sa.Integer(), nullable=False),
sa.Column('data', sa.ARRAY(SmallInteger()), nullable=True),
sa.ForeignKeyConstraint(
['pr_id'], ['disorder_prediction.id'],
name=op.f('fk_disorder_prediction_raw_pr_id_disorder_prediction'),
ondelete='cascade'),
sa.PrimaryKeyConstraint('pr_id',
name=op.f('pk_disorder_prediction_raw')))
op.create_table(
'disorder_prediction_regions',
sa.Column('dp_id', sa.Integer(), nullable=False),
sa.Column('region',
sa.SmallInteger(),
autoincrement=False,
nullable=False),
sa.ForeignKeyConstraint(
['dp_id'], ['disorder_prediction.id'],
name=op.f(
'fk_disorder_prediction_regions_dp_id_disorder_prediction'),
ondelete='cascade'),
sa.PrimaryKeyConstraint('dp_id',
'region',
name=op.f('pk_disorder_prediction_regions')))
op.create_table(
'sequence_go_assoc',
sa.Column('sequence_id', sa.String(), nullable=False),
sa.Column('go', sa.Integer(), nullable=False),
sa.ForeignKeyConstraint(['go'], ['go_term.go'],
name=op.f('fk_sequence_go_assoc_go_go_term')),
sa.ForeignKeyConstraint(
['sequence_id'], ['sequence.id'],
name=op.f('fk_sequence_go_assoc_sequence_id_sequence')),
sa.PrimaryKeyConstraint('sequence_id',
'go',
name=op.f('pk_sequence_go_assoc')))
Column('serial', Integer, nullable=False, server_default='1'),
Column('deleted', CHAR(32), nullable=False, default='0',
server_default='0'),
Column('deleted_at', DateTime, nullable=True, default=None),
Column('description', Unicode(160), nullable=True),
Column('status', Enum(name='domains_resource_statuses',
*RESOURCE_STATUSES),
nullable=False, server_default='PENDING', default='PENDING'),
Column('action', Enum(name='domain_actions', *ACTIONS),
default='CREATE', server_default='CREATE', nullable=False),
Column('pool_id', UUID, default=None, nullable=True),
Column('reverse_name', String(255), nullable=False, server_default=''),
Column("type", Enum(name='type', *ZONE_TYPES),
server_default='PRIMARY', default='PRIMARY'),
Column('transferred_at', DateTime, default=None),
Column('shard', SmallInteger(), nullable=False),
UniqueConstraint('name', 'deleted', 'pool_id', name='unique_domain_name'),
ForeignKeyConstraint(['parent_domain_id'],
['domains.id'],
ondelete='SET NULL'),
Index('zone_deleted', 'deleted'),
Index('zone_tenant_deleted', 'tenant_id', 'deleted'),
Index('reverse_name_deleted', 'reverse_name', 'deleted'),
Index('zone_created_at', 'created_at'),
mysql_engine='InnoDB',
mysql_charset='utf8',
)
def test_integer_types(self, metadata, connection):
specs = []
for type_ in [
mysql.TINYINT,
mysql.SMALLINT,
mysql.MEDIUMINT,
mysql.INTEGER,
mysql.BIGINT,
]:
for display_width in [None, 4, 7]:
for unsigned in [False, True]:
for zerofill in [None, True]:
kw = {}
if display_width:
kw["display_width"] = display_width
if unsigned is not None:
kw["unsigned"] = unsigned
if zerofill is not None:
kw["zerofill"] = zerofill
zerofill = bool(zerofill)
source_type = type_(**kw)
if display_width is None:
display_width = {
mysql.MEDIUMINT: 9,
mysql.SMALLINT: 6,
mysql.TINYINT: 4,
mysql.INTEGER: 11,
mysql.BIGINT: 20,
}[type_]
if zerofill:
unsigned = True
expected_type = type_(
display_width=display_width,
unsigned=unsigned,
zerofill=zerofill,
)
specs.append((source_type, expected_type))
specs.extend([
(SmallInteger(), mysql.SMALLINT(display_width=6)),
(Integer(), mysql.INTEGER(display_width=11)),
(BigInteger, mysql.BIGINT(display_width=20)),
])
# TODO: mysql 8.0.19-ish doesn't consistently report
# on display_width. need to test this more accurately though
# for the cases where it does
if testing.against("mysql >= 8.0.19"):
self._run_test(metadata, connection, specs,
["unsigned", "zerofill"])
else:
self._run_test(
metadata,
connection,
specs,
["display_width", "unsigned", "zerofill"],
)
# retrieves the contents of the output stream
contents = output.getvalue()
# Copys from the stream to the weaponproperties table
cur.copy_from(output, 'weaponproperties', null="") # null values become ''
# Commits on the connection to the database
conn.commit()
# Automatically reflects the database table and stores the column types and details
# weaponProperties = Table('weaponproperties', meta, autoload = True, autoload_with = engine)
# Revises table/column details to drop the weaponId column which is auto generated by postgres
weaponProperties = Table('weaponproperties',
meta,
Column('itemtypeid', SmallInteger(), nullable=False),
Column('slotid', SmallInteger(), nullable=False),
Column('name', VARCHAR(length=50)),
Column('weight', VARCHAR(length=10)),
Column('gridsize', VARCHAR(length=10)),
Column('price', VARCHAR(length=5)),
Column('traderid', SmallInteger()),
Column('opres', SmallInteger),
Column('rarity', SmallInteger),
Column('repair', SmallInteger),
Column('firemodes', VARCHAR(length=50)),
Column('sightingrange', SmallInteger()),
Column('ergo', SmallInteger()),
Column('muzzlevelocity', VARCHAR(length=10)),
Column('effectivedistance', VARCHAR(length=6)),
Column('accuracy', SmallInteger()),
class Page(Base):
"""
Holds functions for scraping a departmental course catalog page.
"""
dept_shortcode_map, dept_name_map = map_codes()
name_dept_map = {v: k for k, v in dept_name_map}
year_map = map_years()
def __init__(self, url, neo4j_session):
"""
Args:
url:
neo4j_session:
"""
self.url = url
self.dept_code = url.split('&a=renderDept&d=')[1]
self.start_year, self.end_year = self.get_years()
self.page = html.parse(url)
self.raw_courses = self.page.xpath('//*[@class="Course"]')
if not self.raw_courses:
# no courses found, a 404 page
self.status = 404
self.dept_name = 'Does not exist at this time'
else:
self.status = 200
self.dept_name = self.name_dept_map[self.dept_code]
self.courses = []
for course in self.raw_courses:
self.courses.append(Course(self, course, neo4j_session))
# def scrape_page(self, neo4j_session):
# """
# Scrape all courses on a Bates department course catalog page.
# Args:
# url: a string url of a Bates departmental catalog page.
# session: a neo4j.GraphDatabase.driver.session object.
# Returns:
# None.
# """
# courses = self.page.xpath('//*[@class="Course"]')
# for course in courses:
# scrape_course(course, neo4j_session)
def get_years(self):
"""
Maps the urlencoded catalog year to a tuple (start_year, end_year)
Returns:
a tuple of catalog coverage: (start_year, end_year). The start year
corresponds to the falls semester year, and the end year corresponds
to the spring semester year.
"""
year = self.year_map[self.url.split('?s=')[1].split('&')[0]]
years = self.years_map[year]
return years
url = Column(String(), primary_key=True)
dept_code = Column(String(4))
dept_name = Column(String(50))
start_year = Column(SmallInteger())
end_year = Column(SmallInteger())
status = Column(String(10))
from sqlalchemy.schema import Table
import logging
from ctp import ApiStruct
from config import Config
logging.basicConfig(level=logging.DEBUG, format='%(asctime)s: %(levelname)s [%(name)s] %(message)s')
logger = logging.getLogger()
COL_NAME_DB_TYPE_DIC = {
'InstrumentID': String(30),
'TradingDay': Date,
'ExchangeID': String(8),
'InstrumentName': String(20),
'ExchangeInstID': String(30),
'ProductID': String(30),
'ProductClass': String(20),
'DeliveryYear': SmallInteger(),
'DeliveryMonth': SmallInteger(),
'CreateDate': Date,
'OpenDate': Date,
'ExpireDate': Date,
'StartDelivDate': Date,
'EndDelivDate': Date,
}
def get_col_type(col_value, col_name):
if col_name in COL_NAME_DB_TYPE_DIC:
col_type = COL_NAME_DB_TYPE_DIC[col_name]
else:
val_type = type(col_value)
if val_type == bytes: