database.py 2.5 KB

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889
  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. signature = 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. word_text_md5 = Column(Text)
  22. create_time = Column(DateTime, nullable=True, default=datetime.now)
  23. update_time = Column(DateTime)
  24. # 初始化数据库连接:
  25. engine = create_engine('mysql+mysqlconnector://hcst:' + passowrd + '@139.186.165.84:3306/db_ai_word')
  26. # 创建DBSession类型:
  27. DBSession = sessionmaker(bind=engine)
  28. def query(signature, task_status, task_id):
  29. condition = (1 == 1)
  30. if signature != None:
  31. condition = and_(condition, Task.signature == signature)
  32. if task_status != None:
  33. condition = and_(condition, Task.task_status == task_status)
  34. if task_id != None:
  35. condition = and_(condition, Task.task_id == task_id)
  36. # 创建Session:
  37. session = DBSession()
  38. # 创建Query查询,filter是where条件,最后调用one()返回唯一行,如果调用all()则返回所有行:
  39. task = session.query(Task).filter(condition).order_by(Task.create_time.desc()).all()
  40. # 关闭Session:
  41. print(task)
  42. session.close()
  43. return task
  44. def insert(task: Task):
  45. # 创建session对象:
  46. session = DBSession()
  47. # 添加到session:
  48. session.add(task)
  49. session.flush()
  50. # 提交即保存到数据库:
  51. session.commit()
  52. # 关闭session:
  53. session.close()
  54. return task
  55. def update(task):
  56. # 创建session对象:
  57. session = DBSession()
  58. taskdb = session.query(Task).filter(Task.task_id == task.task_id).one()
  59. taskdb.task_status = task.task_status
  60. taskdb.word_text = task.word_text
  61. taskdb.word_split = task.word_split
  62. taskdb.word_text_md5 = task.word_text_md5
  63. taskdb.task_result = task.task_result
  64. # 提交即保存到数据库:
  65. session.commit()
  66. # 关闭session:
  67. session.close()
  68. if __name__ == '__main__':
  69. print('test query')
  70. # task = Task(task_id = '3',task_status=1,signature='dx')
  71. # task = insert(task)
  72. task = query(None, None, 1)[0]
  73. print(task.signature)
  74. # update(task)