class JSONEncodedDict(TypeDecorator):
impl = VARCHAR(50)
cache_ok = True
def process_bind_param(self, value, dialect):
if value is not None:
value = json.dumps(value)
return value
def process_result_value(self, value, dialect):
if value is not None:
value = json.loads(value)
return value
def tick_insert(data):
logging.debug("tick_data_rows: %d", len(data))
dtypes = {
k: VARCHAR(32)
for k, v in data.dtypes.items() if v.name == 'object'
}
dtypes['time'] = DATETIME
with db_buffer.connect() as conn:
data.to_sql('tick_data',
conn,
if_exists="append",
index=False,
dtype=dtypes,
chunksize=None)
class Tls226PersonOrig(Base):
__tablename__ = 'tls226_person_orig'
person_orig_id = Column(INT, primary_key=True, default=0)
person_id = Column(INT, default=0)
source = Column(CHAR(5), default='')
source_version = Column(VARCHAR(10), default='')
name_freeform = Column(NVARCHAR(500), default='')
person_name_orig_lg = Column(NVARCHAR(500), default='')
last_name = Column(NVARCHAR(500), default='')
first_name = Column(NVARCHAR(500), default='')
middle_name = Column(NVARCHAR(500), default='')
address_freeform = Column(NVARCHAR(1000), default='')
address_1 = Column(NVARCHAR(500), default='')
address_2 = Column(NVARCHAR(500), default='')
address_3 = Column(NVARCHAR(500), default='')
address_4 = Column(NVARCHAR(500), default='')
address_5 = Column(NVARCHAR(500), default='')
street = Column(NVARCHAR(500), default='')
city = Column(NVARCHAR(200), default='')
state = Column(CHAR(2), default='')
person_ctry_code = Column(CHAR(2), default='')
residence_ctry_code = Column(CHAR(2), default='')
role = Column(VARCHAR(2), default='')
def write_sql_orders(data, db_table):
try:
table = data
dtype = {dtype: VARCHAR(length=255) for dtype in table.columns}
dtype[u'address'] = VARCHAR(length=2550)
dtype[u'taobaoUsername'] = VARCHAR(length=2550)
dtype[u'taobaoTradeId'] = VARCHAR(length=2550)
engine = sqlalchemy.create_engine(
'mysql://*****:*****@localhost:3306/louis?charset=utf8',
encoding='utf8')
table.to_sql(name=db_table,
con=engine,
flavor='mysql',
if_exists='replace',
index=False,
chunksize=10000,
dtype=dtype)
info = u'数据写入成功'
print info
except:
info = u'数据写入失败'
print info
return info
class JSON(TypeDecorator): """Represents any Python object as a json-encoded string. """ impl = VARCHAR(LEN_JSON) def process_bind_param(self, value, dialect): if value is not None: value = json.encode(value) return value def process_result_value(self, value, dialect): if value is not None: value = json.decode(value) return value
class ReagentInventoryTable(BaseModel):
__tablename__ = 'reagent_inventory'
node_id = Column(VARCHAR(45), primary_key=True, nullable=False)
node_type = Column(VARCHAR(45), primary_key=True, nullable=False)
reagent_name = Column(VARCHAR(45), primary_key=True, nullable=False)
reagent_type = Column(
VARCHAR(45), primary_key=True,
nullable=False) #reagent name can be All|Primary|Auxiliary
reagent_count = Column(Integer())
reagent_warn_threshold = Column(Integer())
reagent_err_threshold = Column(Integer())
expire_date_time = Column(DATETIME())
reagent_pack_lot = Column(VARCHAR(45))
updated_timestamp = Column(DATETIME())
def __init__(self,\
node_id='NodeID',\
node_type='NodeType',\
reagent_name='ReagentName',\
reagent_type='All',\
reagent_count=None,\
reagent_warn_threshold=None,\
reagent_err_threshold=None,\
expire_date_time=None,\
reagent_pack_lot=None,\
updated_timestamp=None):
self.id = id
self.node_id = node_id
self.node_type = node_type
self.reagent_name = reagent_name
self.reagent_type = reagent_type
self.reagent_count = reagent_count
self.reagent_warn_threshold = reagent_warn_threshold
self.reagent_err_threshold = reagent_err_threshold
self.expire_date_time = expire_date_time
self.reagent_pack_lot = reagent_pack_lot
self.updated_timestamp = updated_timestamp
class LogQueryModel(BaseModel):
__tablename__ = 'logquery'
id = Column(Integer, primary_key=True)
fromtime = Column(Integer)
endtime = Column(Integer)
terms = Column(VARCHAR(2000))
show_cols = Column(VARCHAR(1000))
user_id = Column(Integer)
page = Column(Integer)
total = Column(Integer)
temp_query_file = Column(CHAR(100))
last_modified = Column(Integer)
@staticmethod
def from_dict(logquery):
return LogQueryModel(fromtime=logquery['fromtime'],
endtime=logquery['endtime'],
terms=json_dumps(logquery['terms']),
show_cols=','.join(logquery['show_cols']),
user_id=logquery['user_id'],
page=logquery.get('page', 1),
total=logquery.get('total', 1),
temp_query_file=logquery.get(
'temp_query_file', ''),
last_modified=millis_now())
def to_dict(self):
return dict(id=self.id,
fromtime=self.fromtime,
endtime=self.endtime,
terms=json.loads(self.terms),
show_cols=self.show_cols.split(','),
user_id=self.user_id,
page=self.page,
total=self.total,
temp_query_file=self.temp_query_file)
class Trend(Base):
__tablename__ = 'Trend'
Tid = Column(Integer,primary_key=True)
Tuid = Column(Integer,ForeignKey("User.Uid", onupdate="CASCADE"))
TcreatT = Column(DateTime(timezone=True), default=func.now())
Tconcents = Column(VARCHAR(256),nullable=False) # 内容
Tprice = Column(Float,default=0.00,nullable=False) #价格
Tphone = Column(CHAR(32), nullable=False) # 电话，可以留不是自己的
Tvalid = Column(Boolean,default=1, nullable=False) #是否有效
TlikeN = Column(Integer,default=0,nullable=False) #点赞数
TtranN = Column(Integer,default=0,nullable=False) #转发数
TcomN = Column(Integer,default=0,nullable=False) #点赞数
Ttranid = Column(Integer,ForeignKey("User.Uid", onupdate="CASCADE")) #从哪个人那里转发的
Tstatus = Column(Integer,default=0) #0表示交易未完成，1表示交易完成了
class Activity(Base):#活动表
__tablename__ = 'Activity'
ACid = Column(Integer,nullable=False, primary_key=True)
ACsponsorid = Column(Integer,ForeignKey('User.Uid', onupdate='CASCADE')) #活动发起者
AClocation = Column(VARCHAR(128), nullable=False)
ACtitle = Column(VARCHAR(24), nullable=False) # 活动的名称？确认长度
ACtag = Column(VARCHAR(12)) # 活动的标签？确认类型
ACstartT = Column(DateTime, nullable=False)
ACendT = Column(DateTime, nullable=False)
ACjoinT = Column(DateTime) # 活动报名截止时间
ACcontent = Column(VARCHAR(128), nullable=False) # 活动介绍
ACfree = Column(Boolean)
ACprice = Column(VARCHAR(64))
ACclosed = Column(Boolean,default=1, nullable=False) # 活动是否已经结束
ACcreateT = Column(DateTime(timezone=True), default=func.now())
ACcommentnumber = Column(Integer,default=0, nullable=False)
ACmaxp = Column(Integer,nullable=False,default=0)
ACminp = Column(Integer,nullable=False,default=100)
ACscore = Column(Integer,nullable=False,default=0)
AClikenumber = Column(Integer,nullable=False,default=0)
ACvalid = Column(Boolean,nullable=False,default=1) # 活动是否已经删除
ACregistN = Column(Integer,nullable=False,default=0)
ACstatus =Column(Integer,nullable=False,default=0)
class OrganizationUserMap(Base):
__tablename__ = 'organization_user_map'
__table_args__ = {'schema': 'auth'}
id = Column(Integer, primary_key=True)
organization_id = Column(Integer, ForeignKey('auth.organization.id'))
organization = relationship("Organization",
back_populates="organization_user_maps")
user_id = Column(Integer, ForeignKey('auth.application_user.id'))
user = relationship("User", back_populates="organization_user_maps")
role_shortname = Column(VARCHAR(1))
class Competitor(Base):
"""Represent competition relationship between organizations"""
__tablename__ = 'tbl_Competitor'
id = Column(Integer, primary_key=True)
organization_id = Column(Integer,
ForeignKey('tbl_Organization.id'),
nullable=False)
competitor_url = Column(VARCHAR(255), nullable=False)
focal_company = relationship('Organization', back_populates='competitors')
def __repr__(self):
return '{} competes with {}'.format(self.focal_company.name,
self.competitor_url)
class LinkageModel(BaseInitModel, BaseModel):
__tablename__ = "phome_linkage"
linkage_id = Column(Integer, primary_key=True)
parent_id = Column(Integer)
linkage_name = Column(VARCHAR(255))
linkage_type = Column(Integer)
linkage_path = Column(VARCHAR(255))
linkage_sort = Column(SMALLINT)
lft = Column(Integer)
rgt = Column(Integer)
bakdata = Column(VARCHAR(200))
pinyin = Column(VARCHAR(50))
def exist(self, value):
'''check info is exist'''
res = self.session.query(self.__class__).filter(self.__class__.linkage_name == value).first()
if res:
return True
return False
def getQuery(self):
'''return query'''
query = self.session.query(self.__class__)
return query
class Partner(Base):
"""Represent partnership between organizations"""
__tablename__ = 'tbl_Partner'
id = Column(Integer, primary_key=True)
organization_id = Column(Integer,
ForeignKey('tbl_Organization.id'),
nullable=False)
partner_url = Column(VARCHAR(255), nullable=False)
focal_company = relationship('Organization', back_populates='partners')
def __repr__(self):
return '{} and {} are partners'.format(self.focal_company.name,
self.partner_url)
class SpeechInputDB(Base):
__tablename__ = 'speech_input'
id = Column(INT, Sequence('speech_input_id_seq'), primary_key=True)
datetime = Column(TIMESTAMP(timezone=False))
source = Column(VARCHAR(160), nullable=False, default='')
message = Column(TEXT(convert_unicode=True), default='')
confidence = Column(INT, default='')
lang = Column(VARCHAR(convert_unicode=True, length=8), default='')
audio_path = Column(TEXT(convert_unicode=True), default='')
def __repr__(self):
return "<SpeechInput(source={}, datetime={}, message={})>".format(
self.source, self.datetime, self.message)
@staticmethod
def insert(source, message, confidence, lang, audio_path):
row = SpeechInputDB(source=source,
message=message,
confidence=confidence,
lang=lang,
audio_path=audio_path,
datetime=dt.utcnow())
session.add(row)
session.commit()
class ResultFlagTable(BaseModel):
__tablename__ = 'flag'
fid = Column(Integer(), primary_key=True, nullable=False, unique=True)
code = Column(VARCHAR(24))
rid = Column(Integer())
def __init__(self, code=None, rid=None):
self.code = code
self.rid = rid
def __repr__(self):
return 'result flag information: \n'+\
'fid:'+HORIZONTAL_TABLE+self.fid+\
'flag code:'+HORIZONTAL_TABLE+self.code+\
'rid:'+HORIZONTAL_TABLE+self.rid
class ChatLogDB(Base):
__tablename__ = 'chatlog'
id = Column(INT, Sequence('chatlog_id_seq'), primary_key=True)
datetime = Column(TIMESTAMP(timezone=False))
source = Column(VARCHAR(160), nullable=False, default='')
event = Column(VARCHAR(160), nullable=False, default='')
author = Column(TEXT(convert_unicode=True), default='')
message = Column(TEXT(convert_unicode=True), default='')
msgtype = Column(TEXT(convert_unicode=True), default='')
def __repr__(self):
return "<Chatlog(source={}, datetime={}, author={}, message={})>".format(
self.source, self.datetime, self.author, self.message)
@staticmethod
def insert(source, author, message, msgtype, event=''):
row = ChatLogDB(source=source,
event=event,
author=author,
message=message,
msgtype=msgtype,
datetime=dt.utcnow())
session.add(row)
session.commit()
class RepositoryUserMap(Base):
__tablename__ = 'repository_user_map'
__table_args__ = {'schema': 'auth'}
id = Column(Integer, primary_key=True)
repository_id = Column(Integer, ForeignKey('auth.repository.id'))
repository = relationship("Repository",
back_populates="repository_user_maps")
user_id = Column(Integer, ForeignKey('auth.application_user.id'))
user = relationship("User", back_populates="repository_user_maps")
role_shortname = Column(VARCHAR(1))
def test_enum_detection(metadata):
Table("simple_items", metadata, Column("enum", VARCHAR(255)),
CheckConstraint(r"simple_items.enum IN ('A', '\'B', 'C')"))
assert (generate_code(metadata) == """\
# coding: utf-8
from sqlalchemy import Column, Enum, MetaData, Table
metadata = MetaData()
t_simple_items = Table(
'simple_items', metadata,
Column('enum', Enum('A', "\\\\'B", 'C'))
)
""")
class Resource_Tag(Model):
__tablename__ = 'resource_tags'
id = Column('id', INT, primary_key=True)
resource_id = Column('resource_id',
INT,
ForeignKey('resources.id'),
nullable=False)
tag = Column('tag', VARCHAR(50), nullable=False)
def __init__(self, resource_id, tag):
self.resource_id = resource_id
self.tag = tag
def __repr__(self):
return '<Resource_Tag %d %s>' % (self.resource_id, self.tag)
def write_into_db(self, customer_id, strategy_id, weight_hist,
weight_dates, rebalance_hist, rebalance_dates,
net_value_record):
'''
写入数据库.
'''
weight_df = pd.DataFrame(list(
zip(weight_dates, [json.dumps(weight) for weight in weight_hist])),
columns=['trade_date', 'position_weight'])
rebalance_df = pd.DataFrame(list(
zip(rebalance_dates,
[json.dumps(rebalance) for rebalance in rebalance_hist])),
columns=['trade_date', 'rebalance'])
net_value_record['customer_id'] = customer_id
weight_df['customer_id'] = customer_id
rebalance_df['customer_id'] = customer_id
net_value_record['strategy_id'] = strategy_id
weight_df['strategy_id'] = strategy_id
rebalance_df['strategy_id'] = strategy_id
conn_engine = create_engine(
'mssql+pymssql://DBadmin:[email protected]/GeniusBar')
net_value_record.reset_index().to_sql('strategy_net_value',
conn_engine,
if_exists='append',
index=False,
dtype={
'customer_id': BIGINT,
'strategy_id': VARCHAR(32),
'trade_date': VARCHAR(32),
'net_value': DECIMAL(20, 6)
})
weight_df.to_sql('strategy_weight',
conn_engine,
if_exists='append',
index=False,
dtype={
'customer_id': BIGINT,
'strategy_id': VARCHAR(32),
'trade_date': VARCHAR(32),
'position_weight': TEXT
})
rebalance_df.to_sql('strategy_rebalance',
conn_engine,
if_exists='append',
index=False,
dtype={
'customer_id': BIGINT,
'strategy_id': VARCHAR(32),
'trade_date': VARCHAR(32),
'rebalance': TEXT
})
class User(Base): # 用户表 #添加聊天专用chattoken
__tablename__ = 'User'
Uid = Column(Integer, nullable=False, primary_key=True) # 主键
Upassword = Column(VARCHAR(64), nullable=False)
Utel = Column(CHAR(32), nullable=False,unique=True)
Ualais = Column(VARCHAR(24),nullable=False,unique=True) # 昵称，可能为微信昵称
Uname = Column(VARCHAR(24)) # 真实姓名
Ulocation = Column(VARCHAR(128))
Uopenid = Column(VARCHAR(128))
Umailbox = Column(VARCHAR(32)) # 邮箱
Ubirthday = Column(DateTime)
Uscore = Column(Integer, default=0)
UregistT = Column(DateTime(timezone=True), default=func.now())
Usex = Column(Boolean, nullable=False)
Usign = Column(VARCHAR(256))
Usessionid = Column(VARCHAR(32)) #用于验证用户
Uvalid = Column(Integer, nullable=False, default=1)
def to_sql_replace(df, table, db='team_station'):
# 执行sql
engine = create_engine(
"mysql+pymysql://{}:{}@{}:{}/{}?charset={}".format(
'user', '', 'wuhan.yibai-it.com', 33061, db, 'utf8'))
conn = engine.connect()
try:
# 将数据写入到dataframe中
index_columns = [
'account_site', 'seller_sku', 'erp_sku', 'asin',
'keyword_1', 'keyword_2', 'keyword_3', 'keyword_4',
'keyword_5', 'keyword_6', 'keyword_7', 'keyword_8',
'keyword_9', 'keyword_10'
]
columns_type = VARCHAR(length=255)
index_column_type = {
column: columns_type
for column in index_columns
}
df.to_sql(table,
conn,
if_exists='replace',
index=False,
dtype=index_column_type)
# 建立索引
create_index_sql = "ALTER TABLE `%s` ADD INDEX 站点 (`%s`)," \
"ADD INDEX seller_sku (`%s`),ADD INDEX erk_sku (`%s`),ADD INDEX asin (`%s`),ADD INDEX keyword_1 (`%s`)," \
"ADD INDEX keyword_2 (`%s`),ADD INDEX keyword_3 (`%s`),ADD INDEX keyword_4 (`%s`),ADD INDEX keyword_5 (`%s`)," \
"ADD INDEX keyword_6 (`%s`),ADD INDEX keyword_7(`%s`),ADD INDEX keyword_8 (`%s`),ADD INDEX keyword_9 (`%s`)," \
"ADD INDEX keyword_10 (`%s`);" % (table,
index_columns[0], index_columns[1],
index_columns[2],
index_columns[3],
index_columns[4], index_columns[5],
index_columns[6],
index_columns[7],
index_columns[8], index_columns[9],
index_columns[10],
index_columns[11],
index_columns[12], index_columns[13])
engine.execute(create_index_sql)
except Exception as e:
print(e)
finally:
conn.close()
engine.dispose()
def sql_insert(self, df):
'''
传入dataframe插入temp表
:param df:
:return:
'''
type_res = {
'id_third': VARCHAR(length=255),
'PropertyAddress': VARCHAR(length=255),
'PropertyCommunity': VARCHAR(length=255),
'TotalFloor': VARCHAR(length=255),
'Floor': VARCHAR(length=255),
'HouseType': VARCHAR(length=255),
'BuildingSquare': VARCHAR(length=255),
'HouseDesc': VARCHAR(length=255),
'PriceUnit': VARCHAR(length=255),
}
df.to_sql('temp',
con=self.engine_third_house,
if_exists="append",
index=False,
dtype=type_res)
def mapping_df_types(df):
'''
将pandas.DataFrame中列名和预指定的类型映射起来
:param df:
:return:
'''
dtypedict = {}
for i, j in zip(df.columns, df.dtypes):
print(i)
if "object" in str(j) and i in [
'title', 'place', 'experience', 'education', 'company', 'size',
'kind', "skillname"
]:
dtypedict.update({i: VARCHAR(length=255)})
if "int" in str(j) and i in ['id', 'salary', 'skillId']:
dtypedict.update({i: Integer()})
return dtypedict
class Acquisition(Base):
"""Represent an Acquisition done by an Organization"""
__tablename__ = 'tbl_Acquisition'
id = Column(Integer, primary_key=True)
organization_id = Column(Integer,
ForeignKey('tbl_Organization.id'),
nullable=False)
acquired_organization_url = Column(VARCHAR(255), nullable=False)
date = Column(Date)
organization = relationship('Organization', back_populates='acquisitions')
def __repr__(self):
return '{} acquired by {} in {}'.format(self.acquired_organization_url,
self.organization.name,
self.date)
class Notice(Model):
__tablename__ = 'notices'
id = Column('id', INT, primary_key=True)
title = Column('title', VARCHAR(255), nullable=False)
content = Column('content', TEXT, nullable=False)
create_time = Column('create_time', TIMESTAMP, nullable=False)
tags = relation("Notice_Tag", backref=backref('notice'))
def __init__(self, title, content):
self.title = title
self.content = content
self.create_time = datetime.now()
def __repr__(self):
return '<Notice %r>' % self.title
class Investment(Base):
"""Represent an Investment a Person has done"""
__tablename__ = 'tbl_Investment'
id = Column(Integer, primary_key=True)
person_id = Column(Integer, ForeignKey('tbl_Person.id'), nullable=False)
organization_id = Column(Integer, ForeignKey('tbl_Organization.id'))
organization_url = Column(VARCHAR(255), nullable=False)
date = Column(Date)
person = relationship('Person', back_populates='investments')
organization = relationship('Organization')
def __repr__(self):
return '{} invested in {} in {}'.format(
self.person.name,
get_name_or_b(self.organization, self.organization_url), self.date)
class Word(Base,Addon):
__tablename__='word'
id=Column(INT,primary_key=True)
word=Column(VARCHAR(length=100),nullable=False)
explanation=Column(TEXT)
example=Column(TEXT)
#单词对应的标签
tags=relationship('Tag',secondary=WordTag,back_populates='words',lazy=True)
#单词的笔记
notes=relationship('Note',back_populates='word',lazy=True)
#任务进行程度
progresses=relationship('Progress',back_populates='word',lazy=True)
def asDict(self):
return {'id':self.id, 'word':self.word, 'explanation':self.explanation, 'example':self.example}
def _get_dtype(self, df=None):
if self._interface in __class__.type_mapping:
return __class__.type_mapping[self._interface]
df = df if df is not None else self._df
mapping = {}
for name, dt in df.dtypes.incomesems():
if dt is dtype('object'):
if '_date' in name:
mapping[name] = DATE()
continue
else:
max_length = df[name].apply(lambda x: 0 if x is None else len(x)).max()
mapping[name] = VARCHAR(length=max_length)
__class__.type_mapping[self._interface] = mapping
return mapping
class WSDATA(Base):
__tablename__ = 'wsdata'
id = Column(Integer, primary_key=True, nullable=False)
#status = Column(String(5))
chip_id = Column(MEDIUMTEXT)
OP1 = Column(MEDIUMTEXT)
OP2 = Column(MEDIUMTEXT)
OP3 = Column(MEDIUMTEXT)
OP4 = Column(MEDIUMTEXT)
OP5 = Column(MEDIUMTEXT)
OP89 = Column(MEDIUMTEXT)
OP90 = Column(MEDIUMTEXT)
time = Column(VARCHAR(30))
updatetime = Column(
TIMESTAMP,
server_default=text("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"),
server_onupdate=FetchedValue())
