database.py 2.4 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687
  1. # 导入:
  2. from datetime import datetime
  3. from urllib import parse
  4. from sqlalchemy import Column, Integer, String, DateTime, Text, create_engine, and_
  5. from sqlalchemy.ext.declarative import declarative_base
  6. from sqlalchemy.orm import sessionmaker
  7. # 创建对象的基类:
  8. Base = declarative_base()
  9. passowrd = parse.quote_plus('hcst@2020')
  10. # 定义User对象:
  11. class Task(Base):
  12. # 表的名字:
  13. __tablename__ = 'tb_asr_result'
  14. # 表的结构:
  15. md5 = Column(String(100))
  16. task_id = Column(Integer, primary_key=True)
  17. task_status = Column(Integer)
  18. task_result = Column(Text)
  19. word_text = Column(Text)
  20. word_split = Column(Text)
  21. create_time = Column(DateTime, nullable=True, default=datetime.now)
  22. update_time = Column(DateTime)
  23. # 初始化数据库连接:
  24. engine = create_engine('mysql+mysqlconnector://hcst:' + passowrd + '@139.186.165.84:3306/db_ai_word')
  25. # 创建DBSession类型:
  26. DBSession = sessionmaker(bind=engine)
  27. def query(md5, task_status, task_id):
  28. condition = (1 == 1)
  29. if md5 != None:
  30. condition = and_(condition, Task.md5 == md5)
  31. if task_status != None:
  32. condition = and_(condition, Task.task_status == task_status)
  33. if task_id != None:
  34. condition = and_(condition, Task.task_id == task_id)
  35. # 创建Session:
  36. session = DBSession()
  37. # 创建Query查询,filter是where条件,最后调用one()返回唯一行,如果调用all()则返回所有行:
  38. task = session.query(Task).filter(condition).order_by(Task.create_time.desc()).all()
  39. # 关闭Session:
  40. print(task)
  41. session.close()
  42. return task
  43. def insert(task: Task):
  44. # 创建session对象:
  45. session = DBSession()
  46. # 添加到session:
  47. session.add(task)
  48. session.flush()
  49. # 提交即保存到数据库:
  50. session.commit()
  51. # 关闭session:
  52. session.close()
  53. return task
  54. def update(task):
  55. # 创建session对象:
  56. session = DBSession()
  57. taskdb = session.query(Task).filter(Task.task_id == task.task_id).one()
  58. taskdb.task_status = task.task_status
  59. taskdb.word_text = task.word_text
  60. taskdb.word_split = task.word_split
  61. taskdb.task_result = task.task_result
  62. # 提交即保存到数据库:
  63. session.commit()
  64. # 关闭session:
  65. session.close()
  66. if __name__ == '__main__':
  67. print('test query')
  68. # task = Task(task_id = '3',task_status=1,md5='dx')
  69. # task = insert(task)
  70. task = query(None, None, 1)[0]
  71. print(task.md5)
  72. # update(task)