flask 使用SQLAlchemy 实现建表和flask 离线脚本的实现
表的model内容如下:
import datetime from sqlalchemy import create_engine from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Integer, String, Text, ForeignKey, DateTime, UniqueConstraint, Index from sqlalchemy.orm import relationship from deploy import db class UserInfo(db.Model): """ 用户表 """ __tablename__ = 'userinfo' id = Column(Integer, primary_key=True) username = Column(String(32), unique=True) password = Column(String(64)) nickname = Column(String(32)) class Host(db.Model): """ 主机表 """ __tablename__ = 'host' id = Column(Integer, primary_key=True) hostname = Column(String(32),unique=True) port = Column(Integer) class Project(db.Model): """ 项目表 """ __tablename__ = 'project' id = Column(Integer, primary_key=True) title = Column(String(32), unique=True) name = Column(String(32), unique=True) repository = Column(String(128)) hosts = relationship('Host', secondary='project_2_host', backref='pros') class Project2Host(db.Model): """ 项目主机关系表 """ __tablename__ = 'project_2_host' id = Column(Integer, primary_key=True) project_id = Column(Integer,ForeignKey('project.id')) host_id = Column(Integer,ForeignKey('host.id')) __table_args__ = ( UniqueConstraint('project_id','host_id',name='uix_project_host'), ) class Deploy(db.Model): """ 代码发布表 """ __tablename__ = 'deploy' id = Column(Integer, primary_key=True) project_id = Column(Integer, ForeignKey('project.id')) proj = relationship("Project", backref='deps') user_id = Column(Integer, ForeignKey('userinfo.id')) version = Column(String(32)) status_choices = { 1:'未发布', 2:'已发布', 3: '发布失败', 4: '回滚', 5: '取消', } status_id = Column(Integer,default=1) deploy_type_choices = { 1: '代码', 2: 'SQL', 3: '静态文件', } deploy_type = Column(Integer,default=1) dtime = Column(DateTime) ctime = Column(DateTime,default=datetime.datetime.now) # 发布之后自动检测 ext_path = Column(String(128),nullable=True) class DeployRecord(db.Model): __tablename__ = 'deployrecord' id = Column(Integer, primary_key=True) deploy_id = Column(Integer, ForeignKey('deploy.id')) host_id = Column(Integer, ForeignKey('host.id')) status_choices = { 1: '成功', 2: '失败', } status_id = Column(Integer, default=1)
如果想创建表,需要单独建立一个文件,如下:
#!/usr/bin/python # -*- coding:utf-8 -*- """ 离线脚本 """ from deploy import create_app,db app = create_app() with app.app_context(): db.drop_all() db.create_all()
离线脚本的实现,如下:
""" 离线脚本 """ from deploy import create_app,db from deploy import models app = create_app() with app.app_context(): # 1. 增加 # obj = models.UserInfo(username='wupeiqi',password='123',nickname='武沛齐') # db.session.add(obj) # db.session.commit() # 2. 批量增加 # db.session.add_all([ # models.UserInfo(username='wupeiqi', password='123', nickname='武沛齐'), # models.UserInfo(username='hhw', password='123', nickname='黄宏伟'), # ]) # db.session.commit() # db.session.remove() # result = db.session.query(models.UserInfo).all() # db.session.remove() # 3. 添加用户和主机 # db.session.add_all([ # # models.UserInfo(username='wupeiqi', password='123', nickname='武沛齐'), # # models.UserInfo(username='hhw', password='123', nickname='黄宏伟'), # models.Host(hostname='c1.com',port=80), # models.Host(hostname='c2.com',port=80), # models.Project(title='公司官网',name='web1',repository='https://github.com/WuPeiqi/dbhot.git'), # models.Project(title='公司后台',name='web2',repository='https://github.com/WuPeiqi/dbhot.git') # ]) # db.session.commit() # db.session.remove() # db.session.add_all([ # models.Project2Host(project_id=1,host_id=1), # models.Project2Host(project_id=1,host_id=2), # models.Project2Host(project_id=2,host_id=2), # models.Project2Host(project_id=2,host_id=1), # ]) # db.session.commit() # db.session.remove() # 任务:创建两个服务器,创建一个项目,项目和服务器创建关系。 # 复杂方式 # obj1 = models.Host(hostname='c4.com',port=80) # obj2 = models.Host(hostname='c5.com',port=80) # obj3 = models.Project(title='运维平台',name='devops',repository='asdfasdf') # db.session.add(obj1) # db.session.add(obj2) # db.session.add(obj3) # db.session.commit() # # db.session.add_all([ # models.Project2Host(host_id=obj1.id,project_id=obj3.id), # models.Project2Host(host_id=obj2.id,project_id=obj3.id), # ]) # db.session.commit() # 简单访问 """ 1. 添加项目 2. 添加两个主机 3. 项目和主机的关系表中添加一个两个 """ # obj1 = models.Project(title='运维平台',name='devops111',repository='asdfasdf') # obj1.hosts = [models.Host(hostname='c10.com',port=80),models.Host(hostname='c11.com',port=80)] # db.session.add(obj1) # db.session.commit() # db.session.remove() # ################## 数据初始化 #################### """ 1. 创建两个用户 2. 创建一个项目 3. 创建两个服务器 4. 项目和服务器创建关系 """ user1 = models.UserInfo(username='wupeiqi', password='123', nickname='武沛齐') user2 = models.UserInfo(username='hhw', password='123', nickname='黄宏伟') pro1 = models.Project(title='公司官网', name='web1', repository='https://github.com/WuPeiqi/dbhot.git') pro1.hosts = [models.Host(hostname='c1.com',port=80),models.Host(hostname='c2.com',port=80)] db.session.add_all([ user1, user2, pro1 ]) db.session.commit() db.session.remove()