数据库(四): ORM, SQLAlchemy

'''
SQLAlchemy:
           1. 架构和流程
 2. 建表
 3. 操作
''' # 综述

'''
SQLAlchemy架构和操作流程:
 1. 架构
 ORM(Object Relational Mapper)
 ----------------------------------------------------------
 Schema/Types, SQL Language, Engine(Connection Pool,Dialect)
 ----------------------------------------------------------
 DBAPI (pymysql,mysqldb,mysqlconnector)
 2. 流程
 #1 通过ORM提交命令
 #2 翻译成SQL语句
 #3 调用ENGINE执行
 #3.1 获得连接
 #3.2 调用指定的DBAPI去执行SQL语句 ---> SQLAlchemy可执行纯的SQL语句: engine.execute('sql') 
''' # SQLAlchemy架构和操作流程

'''
建表:
 1. 创建模板
 from sqlalchemy.ext.declarative import declarative_base
 Base = declarative_base()
 
 2. 继承模板创建表
 Class myTable(Base)
 from sqlalchemy import Column
 数据格式有: Integer, Float, DECIMAL, String, Boolean, Date, DateTime
 表名: __tablename__ = ''
 约束有: ForeignKey(), ForeignKeyConstraint(), PrimaryKeyConstraint, UniqueConstraint(), Index()
 额外约束: __table_args__ = () 
 
 3. 创建连接engine
 from sqlalchemy import create_engine
 engine = create_engine(mysql + pymysql:// username:password@host:port/dbname)
 
 4. 调用engine建库
 Base.metadata.create_all(engine)
 # 删库
 Base.metadata.drop_all(engine) 
''' # 建表
from sqlalchemy.ext.declarative import declarative_base # 创建模板的函数
from sqlalchemy import Column,Integer,String,DATETIME # 数据类型
from sqlalchemy import ForeignKey,UniqueConstraint,Index
from sqlalchemy import create_engine # 创建引擎的函数

Base = declarative_base() # 1. 创建模板

# 创建单表 # 2. 继承模板创建表
class Business(Base):
 __tablename__ = 'business'
 id = Column(Integer, primary_key=True,autoincrement=True)
 bname = Column(String(32),nullable=False,index=True)

# 创建多对一
class Service(Base):
 __tablename__ = 'service'
 id = Column(Integer,primary_key=True,autoincrement=True)
 sname = Column(String(32),nullable=False,index=True)
 ip = Column(String(15),nullable=False)
 port = Column(Integer,nullable=False)

 business_id = Column(Integer,ForeignKey('business.id')) # 外键约束: 多对一

 __table_args = ( # 其他约束
 UniqueConstraint(ip,port,name='uix_ip_port'),
Index('ix_id_sname',id,sname)
 )

# 创建一对一
class Role(Base):
 __tablename__ = 'role'
 id = Column(Integer,primary_key=True,autoincrement=True)
 rname = Column(String(32),nullable=False,index=True)
 priv = Column(String(64),nullable=False)

 business_id = Column(Integer,ForeignKey('business.id'),unique=True) # 外键约束: 一对一

class User(Base):
 __tablename__ = 'user'
 id = Column(Integer,primary_key=True,autoincrement=True)
 uname = Column(String(32),nullable=ForeignKey,index=True)

class User2Role(Base):
 __tablename__ = 'user2role'
 id = Column(Integer,primary_key=

转载于:https://www.cnblogs.com/lancelotxly/p/10857014.html