mysql_orm模块操作数据库(17.6.29)
一、介绍
二、基本操作
创建表:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 from sqlalchemy import create_engine 2 from sqlalchemy.ext.declarative import declarative_base 3 from sqlalchemy import Column,Integer,String,ForeignKey,UniqueConstraint,Index 4 from sqlalchemy.orm import sessionmaker, relationship 5 6 engine = create_engine("mysql+pymysql://root:Www123...@127.0.0.1:3306/db_all?charset=utf8",max_overflow=5 ) #创建连接 7 #,max_overflow=5 这个参数是什么意思 8 Base = declarative_base() #指定基类 9 10 class User(Base): #创建表单 11 __tablename__ = "user" #表单名称 12 id = Column(Integer,primary_key=True) 13 name = Column(String(32),unique=True) 14 pasword = Column(String(32),index=True)
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
from sqlalchemy import create_engine from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column,Integer,String,ForeignKey,UniqueConstraint,Index from sqlalchemy.orm import sessionmaker, relationship engine = create_engine("mysql+pymysql://root:Www123...@127.0.0.1:3306/db_all?charset=utf8",max_overflow=5 ) #创建连接 #,max_overflow=5 这个参数是什么意思 Base = declarative_base() #指定基类 class User(Base): #创建表单 __tablename__ = "user" #表单名称 id = Column(Integer,primary_key=True) name = Column(String(32),unique=True) pasword = Column(String(32),index=True) Base.metadata.create_all(engine) #找到所有子类创建子类,创建一个空表单 # Base.metadata.drop_all(engine) #找到所有子类并删除 tab_values = User(name='coob2',pasword='www123...') #指定往哪个类里面添加什么内容 # 指定引擎 增删改 都要用到这些几句 实在是看不明白; Session = sessionmaker(bind=engine) session = Session() #增删改操作 session.add(tab_values) #提交 session.commit()
增:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
session.add_all([ User(name="李四", pasword='lisi'), User(name="汪五", pasword='wangwu'), ]) #提交 session.commit()
删:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
# 删 session.query(User).filter(User.id > 2).delete() session.query(User).filter_by(name = '汪五').delete() #提交 session.commit()
改:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
session.query(Users).filter(Users.id > 2).update({"name" : "nick"}) session.query(Users).filter(Users.id > 2).update({"name" : "nick", "extra":"niubily"}) session.query(Users).filter(Users.id > 2).update({Users.name: Users.name + "Suo"}, synchronize_session=False) session.query(Users).filter(Users.id > 2).update({"num": Users.num + 1}, synchronize_session="evaluate") session.commit()
查:
class User(Base): #创建表单 __tablename__ = "user" #表单名称 id = Column(Integer,primary_key=True) name = Column(String(32),unique=True) pasword = Column(String(32),index=True) #只有在类中创建这个方法才可以把查到数据由 对象 转换成 可识别数据 def __repr__(self): return "%s-%s" % (self.id, self.name) # 查 ret = session.query(User).all() print(ret)
索引:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
__table_args__ = ( UniqueConstraint('id', 'name', name='uix_id_name'), # 唯一索引 Index('ix_id_name', 'name', 'extra'), # 普通索引 )
三、连表操作
1.一对多
class User(Base): __tablename__ = "user" id = Column(Integer,primary_key=True,nullable=True) name = Column(String(32),nullable=False,default="作死",autoincrement=True) pasword = Column(String(32),index=True) #ForeignKey 指定关联表名和表列 team_id = Column(Integer,ForeignKey('team.id'))
2.多对多
class User(Base): __tablename__ = "user" id = Column(Integer,primary_key=True,nullable=True) #与其他表建立关联 part_id = Column(Integer,ForeignKey('part.id')) team_id = Column(Integer,ForeignKey('team.id'))