def run():
df = pd.read_csv('./cleanfile.csv', encoding='utf-8', sep=',')
df["sentiments"] = df["content"].map(
lambda c: snownlp.SnowNLP(c).sentiments)
df["keywords"] = df["content"].map(getKeyWord)
df["input_time"] = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
#engine = create_engine('mysql+pymysql://root:@127.0.0.1:3306/sina')
engine = create_engine('mysql+mysqlconnector://root:@127.0.0.1:3306/sina')
dtypedict = {
'id': Integer(),
'mid': VARCHAR(length=50),
'content': TEXT,
'uid': VARCHAR(length=15),
'area': VARCHAR(length=15),
'nick': VARCHAR(length=50),
'ip': VARCHAR(length=15),
'newsid': VARCHAR(length=50),
'time': DATETIME(),
'sentiments': DECIMAL('10,10'),
'keywords': VARCHAR(length=100),
'input_time': DATETIME(),
}
df.to_sql(name='news',
con=engine,
chunksize=100000,
if_exists='replace',
index=True,
index_label='id',
dtype=dtypedict)
class VariableMetaDefaultOfflineModel(BaseModel):
__tablename__ = 'variablemeta_default_offline'
id = Column(Integer, primary_key=True)
app = Column(CHAR(100))
name = Column(CHAR(100))
type = Column(CHAR(100))
src_variablesid = Column(CHAR(100))
src_eventid = Column(CHAR(100))
priority = Column(Integer)
properties = Column(VARCHAR(4000))
expire = Column(Integer)
ttl = Column(Integer)
internal = Column(Integer)
remark = Column(VARCHAR(1000))
last_modified = Column(Integer)
fulldata = Column(BLOB)
def to_variablemeta(self):
try:
return VariableMeta.from_json(self.fulldata)
except Exception as e:
logger.error(e)
return None
@staticmethod
def from_variablemeta(meta):
return VariableMetaDefaultOfflineModel(
app=meta.app,
name=meta.name,
type=meta.type,
src_variablesid=str(meta.src_variablesid),
src_eventid=str(meta.src_eventid),
priority=meta.priority,
properties=json.dumps(meta.get_dict()["properties"]),
expire=meta.expire,
ttl=meta.ttl,
internal=meta.internal,
remark=meta.remark,
last_modified=millis_now(),
fulldata=meta.get_json())
def __str__(self):
return str(self.__dict__)
def load_dialect_impl(self, dialect):
if dialect.name == 'sqlite':
return dialect.type_descriptor(VARCHAR(255))
elif dialect.name == 'mysql':
return dialect.type_descriptor(UUID())
elif dialect.name == 'postgresql':
return dialect.type_descriptor(UUID())
else:
return dialect.type_descriptor(UUID())
class TrendComment(Base):
__tablename__ = 'TrendCommit'
TCid = Column(Integer,primary_key=True)
TCtid = Column(Integer,ForeignKey("Trend.Tid", onupdate="CASCADE")) #动态ID
TCuid = Column(Integer,ForeignKey("User.Uid", onupdate="CASCADE")) #评论人id
TCvalid = Column(Boolean, default=1, nullable=False)
TCcreatT = Column(DateTime(timezone=True), default=func.now()) #时间
TCcontents = Column(VARCHAR(256),nullable=False)
class CaseInsensitive(FunctionElement):
"""
Function for case insensite indexes
"""
# Need this attribute so Index can processes this element
# http://stackoverflow.com/q/22154917/148781
__visit_name__ = 'notacolumn'
name = 'CaseInsensitive'
type = VARCHAR()
class Tls906Person(Base):
__tablename__ = 'tls906_person'
person_id = Column(INT, primary_key=True, default=0)
person_name = Column(NVARCHAR(500), default='')
person_name_orig_lg = Column(NVARCHAR(500), default='')
person_address = Column(NVARCHAR(1000), default='')
person_ctry_code = Column(CHAR(2), default='')
nuts = Column(VARCHAR(5), default='')
nuts_level = Column(SMALLINT, default=9)
doc_std_name_id = Column(INT, default=0)
doc_std_name = Column(NVARCHAR(500), default='')
psn_id = Column(INT, default=0)
psn_name = Column(NVARCHAR(500), default='')
psn_level = Column(SMALLINT, default=0)
psn_sector = Column(VARCHAR(50), default='')
han_id = Column(INT, default=0)
han_name = Column(NVARCHAR(500), default='')
han_harmonized = Column(INT, default=0)
class WApInfo(Base):
'''
@author:黄鑫晨
@name:约拍评论表，即每一个确定的约拍对应的表，选择人后在此加一项
'''
__tablename__ = "WApinfo"
WAIid = Column(Integer, primary_key=True)
WAImid = Column(Integer, ForeignKey('User.Uid', ondelete='CASCADE')) # 模特的Id
WAIpid = Column(Integer, ForeignKey('User.Uid', ondelete='CASCADE')) # 摄影师的id
WAImscore = Column(Integer, default=0) # 模特获得的得分
WAIpscore = Column(Integer, default=0) # 摄影师获得的得分
WAImcomment = Column(VARCHAR(128)) # 模特对摄影师的评论
WAIpcomment = Column(VARCHAR(128)) # 摄影师对模特的评论
WAImcommentT = Column(DateTime(timezone=True), default=func.now()) # 模特对摄影师的评论的时间
WAIpcommentT = Column(DateTime(timezone=True), default=func.now()) # 摄影师对模特的评论的时间
WAIappoid = Column(Integer, ForeignKey('WAppointment.WAPid', onupdate='CASCADE')) # 约拍Id
WAIvalid = Column(Boolean, default=1, nullable=False)
class Role(Base, Entity):
"""
Roles for users with privileges
"""
__tablename__ = 'Role'
id = Column('id', SMALLINT, primary_key=True, autoincrement=True)
name = Column('name', VARCHAR(10), unique=True, nullable=False)
description = Column('description', VARCHAR(500), nullable=False)
def __init__(self, id: int=None, name: str=None, description: str=None):
self.id = id
self.name = name
self.description = description
@classmethod
def get_by_name(cls, session, name: str) -> Optional['Role']:
return session.query(cls).filter_by(name=name).one_or_none()
class Customers(Base):
__tablename__ = 'CUSTOMERS'
#id = Column(Integer, primary_key = True)
id = Column(nz.SMALLINT, Sequence('USR_ID_SEQ3'), primary_key=True)
#id = Column(nz.BIGINT, Sequence('USR_ID_SEQ1'), primary_key = True)
name = Column(VARCHAR(30))
address = Column(nz.NVARCHAR(30))
email = Column(nz.NCHAR(30))
def _bind_params(bound_sql, params):
"""Bind named parameters to the given sql."""
for key, value in params.items():
if isinstance(value, str):
bound_sql = bound_sql.bindparams(
bindparam(key=key, value=value, type_=VARCHAR(None)))
else:
bound_sql = bound_sql.bindparams(bindparam(key=key, value=value))
return bound_sql
class AppointmentImage(Base):
__tablename__ = 'AppointImage'
APIapid = Column(Integer, ForeignKey("Appointment.APid",
onupdate="CASCADE"))
APIimid = Column(Integer,
ForeignKey("Image.IMid", onupdate="CASCADE"),
primary_key=True)
APIurl = Column(VARCHAR(128))
class Admin2CodeModel(Base):
__tablename__ = 'admin2_codes'
__table_args__ = (
PrimaryKeyConstraint("code", "name", "asciiname", "geoname_id"),
)
code = Column(VARCHAR(30))
name = Column(Text)
asciiname = Column(Text)
geoname_id = Column(Integer, ForeignKey("geoname.geoname_id"))
class UserPkGameAnsModel(BaseModel):
__tablename__ = 'user_pk_game_ans'
ans_id = Column(INTEGER(10, unsigned=True),
primary_key=True,
autoincrement=True)
game_type = Column(TINYINT(4))
user_id = Column(INTEGER(10, unsigned=True))
pro_id = Column(INTEGER(10, unsigned=True))
user_ans = Column(VARCHAR(20))
class Input(DeclarativeBase):
'''
An unique input.
'''
__tablename__ = 'Input'
# columns
id = Column(Integer, autoincrement=True, primary_key=True)
sha1 = Column(Unicode(255), unique=True, nullable=True)
_last_access = Column(DateTime, default=datetime.now, nullable=False)
path = Column(Unicode, nullable=True)
task_id = Column(VARCHAR(255), nullable=True)
task = relationship('Task',
uselist=False,
primaryjoin='Input.task_id == Task.task_id',
foreign_keys='Task.task_id')
parent_id = Column(Integer, ForeignKey('Input.id'), nullable=True)
children = relationship("Input")
# special methods
def __repr__(self):
return '<Input: id=%r, sha1=%r, task_id=%s>' % (self.id, self.sha1,
self.task_id)
def __unicode__(self):
return self.sha1
def _get_last_access(self):
return self._last_access.strftime(constants.date_format)
def _set_last_access(self, date):
self._last_access = date
last_access = synonym('_last_access',
descriptor=property(_get_last_access,
_set_last_access))
@property
def accessed(self):
'''
Update the field 'last_access' by the current time
'''
self._set_last_access(datetime.now())
@property
def status(self):
if self.task is None:
return 'RUNNING'
return self.task.status
@property
def traceback(self):
if self.task is None:
return ''
return self.task.traceback
class Tls209ApplnIpc(Base):
__tablename__ = 'tls209_appln_ipc'
appln_id = Column(INT, primary_key=True, default=0)
ipc_class_symbol = Column(VARCHAR(15), primary_key=True, default='')
ipc_class_level = Column(CHAR(1), default='')
ipc_version = Column(DATE, default='9999-12-31')
ipc_value = Column(CHAR(1), default='')
ipc_position = Column(CHAR(1), default='')
ipc_gener_auth = Column(CHAR(2), default='')
class UCcomment(Base):
__tablename__ = 'UCcomment'
UCCid = Column(Integer, primary_key=True)
UCcomvalid = Column(Boolean, nullable=False, default=1)
UCcommentid = Column(Integer,
ForeignKey(UserCollection.UCid, onupdate='CASCADE'))
UCcommentUserid = Column(Integer, ForeignKey(User.Uid, onupdate='CASCADE'))
UCcontent = Column(VARCHAR(128))
UCcommentTime = Column(DateTime(timezone=True), default=func.now())
class view_news_classify(Base):
'''
view_news_classify:通过以下sql语句
select wangyi_news_classify.news_id,news_title,news_url,insert_time,classify
from wangyi_news,news_classify,wangyi_news_classify
where wangyi_news.news_id = wangyi_news_classify.news_id
and news_classify.classify_id = wangyi_news_classify.classify_id
order by wangyi_news_classify.news_id asc
创建的视图
'''
# 视图名字
__tablename__ = 'view_news_classify'
# 视图结构
news_id = Column(Integer, primary_key=True)
news_title = Column(VARCHAR(600))
insert_time = Column(VARCHAR(11))
classify = Column(VARCHAR(10))
news_url = Column(VARCHAR(600))
class news_classify(Base):
'''
news_classify:存放新闻的种类以及新闻id,固定的,不更新
'''
# 表的名字
__tablename__ = 'news_classify'
# 表的结构
classify_id = Column(Integer, primary_key=True)
classify = Column(VARCHAR(10))
class User(Model, UserMixin):
__tablename__ = 'users'
id = Column('id', INT, primary_key=True)
email = Column('email', VARCHAR(50), unique=True, nullable=False)
nickname = Column('nickname', VARCHAR(32), nullable=False)
title = Column('title', VARCHAR(128), nullable=False)
sex = Column('sex', BOOLEAN, nullable=False)
passwd = Column('passwd', VARCHAR(50), nullable=False)
create_time = Column('create_time', TIMESTAMP, nullable=False)
tag_follows = relation('Tag_Follow')
def __init__(self, email, passwd, nickname=''):
self.email = email
self.passwd = md5(passwd).hexdigest()
self.nickname = nickname
self.title = ''
self.sex = 0
self.create_time = datetime.now()
def email_hash(self):
return md5(self.email.lower()).hexdigest()
def photo(self, size):
name = 'photos/%d-%d.jpg'
filename = name % (self.id, size)
if isfile(join(app.static_folder, filename)):
return filename
filename = name % (0, size)
if isfile(join(app.static_folder, filename)):
return filename
filename = name % (0, 20)
if isfile(join(app.static_folder, filename)):
return path
def tag_is_followed(self, tag):
tags = [tf.tag for tf in self.tag_follows]
return tag in tags
def __repr__(self):
return '<User %r>' % self.email
class EventModelDefault(BaseModel):
"""
新的event model
"""
__tablename__ = 'eventmodel_default'
id = Column(Integer, primary_key=True)
app = Column(CHAR(100))
name = Column(CHAR(100))
visible_name = Column(CHAR(100))
type = Column(CHAR(100))
remark = Column(VARCHAR(1000))
source = Column(VARCHAR(1000))
version = Column(CHAR(100))
properties = Column(VARCHAR(8000))
last_modified = Column(BigInteger)
def to_eventmodel(self):
result = EventModel(self.app, self.name, self.visible_name, self.type,
self.remark, json.loads(self.source), self.version,
json.loads(self.properties))
add_event_to_registry(result)
return result
@staticmethod
def from_eventmodel(model):
return EventModelDefault(
app=model.app,
name=model.name,
visible_name=model.visible_name,
type=model.type,
remark=model.remark,
source=json.dumps(model.source or list()),
version=model.version,
properties=json.dumps([
_.get_simplified_ordered_dict()
for _ in model.simplified_properties
]),
last_modified=millis_now(),
)
def __str__(self):
return str(self.__dict__)
class Task(DeclarativeBase):
__tablename__ = "celery_taskmeta"
id = Column('id',
Integer,
Seq('task_id_sequence'),
primary_key=True,
autoincrement=True)
task_id = Column(VARCHAR(255), unique=True)
status = Column(VARCHAR(50), default='PENDING')
result = Column(PickleType, nullable=True)
date_done = Column(DateTime,
default=datetime.now,
onupdate=datetime.now,
nullable=True)
traceback = Column(Text, nullable=True)
def __init__(self, task_id):
self.task_id = task_id
class Tag(Base,Addon):
__tablename__='tag'
id=Column(INT,primary_key=True)
tag=Column(VARCHAR(length=50),nullable=False)
#标签对应的单词
words=relationship('Word',secondary=WordTag,back_populates='tags',lazy=True)
#标签对应的用户
users=relationship('User',secondary=UserTag,back_populates='tags',lazy=True)
class WAcAuth(Base):
# 记录约拍伴侣发布码
__tablename__ = 'WAcAuth'
WAAid = Column(Integer, primary_key=True)
WAauth = Column(VARCHAR(32), nullable=False)
WAAacid = Column(Integer,
ForeignKey('WApCompanions.WAPCid',
onupdate='CASCADE')) # 伴侣ID
WAAused = Column(Boolean, nullable=False, default=0) # 为0则未用， 1则用过
class TrendImage(Base):
__tablename__ = 'TrendImage'
TIid = Column(Integer, primary_key=True)
TItid = Column(Integer,
ForeignKey('Trend.Tid', onupdate='CASCADE'),
primary_key=True)
TIimid = Column(Integer,
ForeignKey('Image.IMid', onupdate='CASCADE'),
primary_key=True)
TIimgurl = Column(VARCHAR(128))
class UserHomepageimg(Base):
#用户个人图片展示
__tablename__ = 'UserHomepageimg'
UHpageid = Column(Integer, primary_key=True)
UHuser = Column(Integer, ForeignKey('User.Uid', onupdate='CASCADE'))
UHimgid = Column(Integer, ForeignKey('Image.IMid', onupdate='CASCADE'))
UHpicurl = Column(VARCHAR(128))
UHpicvalid = Column(Integer, default=0)
UHheight = Column(Integer, default=0)
UHwidth = Column(Integer, default=0)
def writeInDb(self,df,db_name,table_name):
# 4、连接数据库，并写入
# '数据库类型+数据库驱动名称://用户名:口令@机器地址:端口号/数据库名'
# 注意若使用mysql+mysqlconnector 默认使用的是mysql-python（此模块已不再更新py3的版本）
# connect=create_engine('mysql+mysqlconnector://admin:admin123@localhost:3306/gridforecast')
engine = create_engine('mysql+mysqldb://admin:admin123@localhost:3306/gridforecast')
dtypedict={
'str':VARCHAR(length=4),
}
pd.io.sql.to_sql(df,table_name,engine,schema=db_name, if_exists='append',index=False,dtype=dtypedict)
class Categorie(Base):
__tablename__ = 'categorie'
id = Column(TINYINT, primary_key=True)
name = Column('categorie_Name', VARCHAR(50))
mysql_engine = 'InnoDB'
mysql_charset = 'utf8'
products = relationship('Products', back_populates='categorie')
def __repr__(self):
return f"Categorie: {self.id, self.name}"
class ActivityEntry(Base): #活动报名表
__tablename__ = 'Activityaentry'
ACEid=Column(Integer,primary_key=True)
ACEacid = Column(Integer,ForeignKey('Activity.ACid',onupdate='CASCADE')) # 活动ID
ACEregisterid = Column(Integer,ForeignKey('User.Uid',onupdate='CASCADE')) # 报名人ID
ACEregisttvilid = Column(Boolean,default=1)
ACEscore = Column(Integer,nullable=False,default=0)
ACEcomment = Column(VARCHAR(128),nullable=False,default='')
ACEregisterT = Column(DateTime(timezone=True), default=func.now())
class Store(Base):
__tablename__ = 'store'
id = Column(SmallInteger, autoincrement=True, primary_key=True)
name_store = Column('name_stores', VARCHAR(255))
mysql_engine = 'InnoDB'
mysql_charset = 'utf8'
products = relationship("Products", back_populates="store")
def __repr__(self):
return f"Store: {self.id, self.name_store}"
class SampleTable(BaseModel):
__tablename__ = 'sample'
sid = Column(VARCHAR(24), primary_key=True, nullable=False, unique=True)
tube_type = Column(VARCHAR(45))
sample_type = Column(VARCHAR(45))
pid = Column(VARCHAR(24))
def __init__(self, sid, tube_type=None, sample_type=None, pid=None):
self.sid = sid
self.tube_type = tube_type
self.sample_type = sample_type
self.pid = pid
def __repr__(self):
return 'sample information: \n'+\
'sid:'+HORIZONTAL_TABLE+self.sid+\
'tube type:'+HORIZONTAL_TABLE+self.tube_type+\
'sample type:'+HORIZONTAL_TABLE+self.sample_type+\
'pid:'+HORIZONTAL_TABLE+self.pid
