database.py 2.4 KB

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980
  1. # 导入:
  2. from hashlib import md5
  3. from sqlalchemy.orm import sessionmaker
  4. from sqlalchemy.ext.declarative import declarative_base
  5. from sqlalchemy import Column, Integer, String,DateTime,Text,create_engine,and_
  6. from datetime import datetime
  7. from urllib import parse
  8. # 创建对象的基类:
  9. Base = declarative_base()
  10. passowrd = parse.quote_plus('hcst@2020')
  11. # 定义User对象:
  12. class Task(Base):
  13. # 表的名字:
  14. __tablename__ = 'tb_asr_result'
  15. # 表的结构:
  16. md5 = Column(String(100))
  17. task_id = Column(Integer, primary_key=True)
  18. task_status = Column(Integer)
  19. task_result = Column(Text)
  20. word_text = Column(Text)
  21. word_split = 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(md5,task_status,task_id):
  29. condition = (1==1)
  30. if md5 != None:
  31. condition = and_(condition, Task.md5 == md5)
  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.task_result = task.task_result
  63. # 提交即保存到数据库:
  64. session.commit()
  65. # 关闭session:
  66. session.close()
  67. if __name__ == '__main__':
  68. print('test query')
  69. # task = Task(task_id = '3',task_status=1,md5='dx')
  70. # task = insert(task)
  71. task = query(None,None,1)[0]
  72. print(task.md5)
  73. #update(task)