database.py 2.8 KB

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