database.py 3.6 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136
  1. from datetime import datetime, timedelta
  2. from sqlalchemy import create_engine, Column, String, ForeignKey, Integer, Index
  3. from sqlalchemy.ext.declarative import declarative_base
  4. from sqlalchemy.orm import sessionmaker
  5. from sqlalchemy.exc import IntegrityError
  6. from sqlalchemy.types import JSON
  7. engine = create_engine('sqlite:///data/qotnews.sqlite', connect_args={'timeout': 120})
  8. Session = sessionmaker(bind=engine)
  9. Base = declarative_base()
  10. class Story(Base):
  11. __tablename__ = 'stories'
  12. sid = Column(String(16), primary_key=True)
  13. ref = Column(String(16), unique=True)
  14. meta = Column(JSON)
  15. data = Column(JSON)
  16. title = Column(String)
  17. class Reflist(Base):
  18. __tablename__ = 'reflist'
  19. rid = Column(Integer, primary_key=True)
  20. ref = Column(String(16), unique=True)
  21. urlref = Column(String)
  22. sid = Column(String, ForeignKey('stories.sid'), unique=True)
  23. source = Column(String(16))
  24. def init():
  25. Base.metadata.create_all(engine)
  26. def get_story(sid):
  27. session = Session()
  28. return session.query(Story).get(sid)
  29. def put_story(story):
  30. story = story.copy()
  31. data = {}
  32. data.update(story)
  33. meta = {}
  34. meta.update(story)
  35. meta.pop('text', None)
  36. meta.pop('comments', None)
  37. try:
  38. session = Session()
  39. s = Story(
  40. sid=story['id'],
  41. ref=story['ref'],
  42. data=data,
  43. meta=meta,
  44. title=story.get('title', None),
  45. )
  46. session.merge(s)
  47. session.commit()
  48. except:
  49. session.rollback()
  50. raise
  51. finally:
  52. session.close()
  53. def get_story_by_ref(ref):
  54. session = Session()
  55. return session.query(Story).filter(Story.ref==ref).first()
  56. def get_story_by_url(url):
  57. session = Session()
  58. return session.query(Story).\
  59. filter(Story.title != None).\
  60. filter(Story.meta['url'].as_string() == url).\
  61. order_by(Story.meta['date'].desc()).\
  62. first()
  63. def get_stories_by_url(url):
  64. session = Session()
  65. return session.query(Story).\
  66. filter(Story.title != None).\
  67. filter(Story.meta['url'].as_string() == url).\
  68. order_by(Story.meta['date'].desc())
  69. def get_ref_by_sid(sid):
  70. session = Session()
  71. x = session.query(Reflist).\
  72. filter(Reflist.sid == sid).\
  73. first()
  74. return dict(ref=x.ref, sid=x.sid, source=x.source, urlref=x.urlref)
  75. def get_reflist():
  76. session = Session()
  77. q = session.query(Reflist).order_by(Reflist.rid.desc())
  78. return [dict(ref=x.ref, sid=x.sid, source=x.source, urlref=x.urlref) for x in q.all()]
  79. def get_stories(maxage=0, skip=0, limit=20):
  80. time = datetime.now().timestamp() - maxage
  81. session = Session()
  82. q = session.query(Reflist, Story.meta).\
  83. join(Story).\
  84. filter(Story.title != None).\
  85. filter(maxage == 0 or Story.meta['date'].as_integer() > time).\
  86. order_by(Story.meta['date'].desc()).\
  87. offset(skip).\
  88. limit(limit)
  89. return [x[1] for x in q]
  90. def put_ref(ref, sid, source, urlref):
  91. try:
  92. session = Session()
  93. r = Reflist(ref=ref, sid=sid, source=source, urlref=urlref)
  94. session.add(r)
  95. session.commit()
  96. except:
  97. session.rollback()
  98. raise
  99. finally:
  100. session.close()
  101. def del_ref(ref):
  102. try:
  103. session = Session()
  104. session.query(Reflist).filter(Reflist.ref==ref).delete()
  105. session.commit()
  106. except:
  107. session.rollback()
  108. raise
  109. finally:
  110. session.close()
  111. if __name__ == '__main__':
  112. init()
  113. print('add index')
  114. index = Index("story_url_index", Story.meta['url'].as_string())
  115. index.create(engine)