87 lines
2.6 KiB
Python
87 lines
2.6 KiB
Python
from datetime import datetime
|
|
|
|
from sqlalchemy import create_engine, Column
|
|
from sqlalchemy.ext.declarative import declarative_base
|
|
from sqlalchemy.orm import relationship, sessionmaker
|
|
from sqlalchemy.sql.schema import ForeignKey
|
|
from sqlalchemy.sql.sqltypes import Boolean, DateTime, String, Text, Integer
|
|
|
|
|
|
engine = create_engine("sqlite:///spider.sqlite3", future=True)
|
|
Base = declarative_base()
|
|
Session = sessionmaker(engine, future=True)
|
|
|
|
|
|
class Word(Base):
|
|
__tablename__ = "word"
|
|
|
|
id = Column(Integer, primary_key=True)
|
|
spelling = Column(String(64), unique=True)
|
|
importance = Column("importance", String(32), nullable=True)
|
|
|
|
def __repr__(self) -> str:
|
|
return self.spelling
|
|
|
|
|
|
class Meaning(Base):
|
|
__tablename__ = "meaning"
|
|
|
|
id = Column(Integer, primary_key=True)
|
|
meaning = Column(String(128))
|
|
word_property = Column(String(8))
|
|
sentance = Column(String(128), nullable=True)
|
|
add_time = Column(DateTime, default=datetime.utcnow)
|
|
word_id = Column(
|
|
Integer,
|
|
ForeignKey("{0}.id".format(Word.__tablename__), ondelete="CASCADE"),
|
|
)
|
|
|
|
word = relationship("Word", backref="meanings")
|
|
|
|
def __repr__(self) -> str:
|
|
return "{} {} {}".format(self.word.spelling, self.word_property, self.meaning)
|
|
|
|
|
|
class WordData(Base):
|
|
__tablename__ = "word_data"
|
|
|
|
id = Column(Integer, primary_key=True)
|
|
word = Column(String(64), unique=True, nullable=False)
|
|
has_retrieve = Column(Boolean, default=False, nullable=False)
|
|
url = Column(String(256), nullable=True)
|
|
html = Column(Text, nullable=True)
|
|
|
|
def __repr__(self) -> str:
|
|
return "'{}' {}".format(self.word, self.has_retrieve)
|
|
|
|
|
|
def clear_word_null():
|
|
with Session() as session:
|
|
queryset = session.query(Word).filter_by(spelling=None)
|
|
for item in queryset:
|
|
session.delete(item)
|
|
session.commit()
|
|
|
|
|
|
if __name__ == "__main__":
|
|
# Base.metadata.create_all(engine)
|
|
def testcase():
|
|
with Session() as session:
|
|
the = session.query(Word).filter_by(spelling="the").one()
|
|
print(the.meanings)
|
|
|
|
# testcase()
|
|
def testcase2():
|
|
with Session() as session:
|
|
nonecase = session.query(Word).filter_by(spelling=None)
|
|
print(nonecase.count())
|
|
|
|
def testcase3():
|
|
with Session() as session:
|
|
john = session.query(Word).filter_by(spelling="john").one()
|
|
print(john.importance)
|
|
print(type(john.importance)) # => str something is wrong
|
|
|
|
# clear_word_null()
|
|
testcase3()
|