sqlalchemy数据库模块
1 #!/usr/bin/env python 2 # -*- coding:utf-8 -*- 3 from sqlalchemy.ext.declarative import declarative_base 4 from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index 5 from sqlalchemy.orm import sessionmaker, relationship 6 from sqlalchemy import create_engine 7 engine = create_engine("mysql+pymysql://root@127.0.0.1:3306/sig") #engine 引擎 8 Base = declarative_base() #declarative 声明的 base 基地 9 # 创建单表 10 class Users(Base): 11 __tablename__ = 'users' #表名 12 id = Column(Integer, primary_key=True) 13 name = Column(String(32)) 14 extra = Column(String(16)) 15 #args 传感器 16 __table_args__ = ( 17 UniqueConstraint('id', 'name', name='uix_id_name'), #Constraint 约束 unique 唯一 18 Index('ix_id_name', 'name', 'extra'), 19 ) 20 # 创建一对多 21 class Favor(Base): 22 __tablename__ = 'favor' 23 nid = Column(Integer, primary_key=True) 24 caption = Column(String(50), default='red', unique=True) 25 26 class Person(Base): 27 __tablename__ = 'person' 28 nid = Column(Integer, primary_key=True) 29 name = Column(String(32), index=True, nullable=True) 30 favor_id = Column(Integer, ForeignKey("favor.nid")) 31 32 # 创建多对多 33 class Group(Base): 34 __tablename__ = 'group' 35 id = Column(Integer, primary_key=True) 36 name = Column(String(64), unique=True, nullable=False) 37 port = Column(Integer, default=22) 38 39 40 class Server(Base): 41 __tablename__ = 'server' 42 id = Column(Integer, primary_key=True, autoincrement=True) 43 hostname = Column(String(64), unique=True, nullable=False) 44 45 46 class ServerToGroup(Base): 47 __tablename__ = 'servertogroup' 48 nid = Column(Integer, primary_key=True, autoincrement=True) 49 server_id = Column(Integer, ForeignKey('server.id')) 50 group_id = Column(Integer, ForeignKey('group.id')) 51 52 Base.metadata.create_all(engine) #metadata元数据 把创建的表格发送給数据库 53 54 55 56 Session = sessionmaker(bind=engine) #session 会话 maker制造者 sessionmaker会话者 57 session = Session() #创建一个session对象 58 59 obj = Users(name="alex0", extra='sb') 60 session.add(obj) 61 session.add_all([ 62 Users(name="alex1", extra='sb'), 63 Users(name="alex2", extra='sb'), 64 ]) 65 session.commit() #commit 把。。。托付給 把写入的内容发送給数据库
1 #!/usr/bin/env python 2 # -*- coding:utf-8 -*- 3 from sqlalchemy.ext.declarative import declarative_base 4 from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index 5 from sqlalchemy.orm import sessionmaker, relationship 6 from sqlalchemy import create_engine 7 engine = create_engine("mysql+pymysql://root@127.0.0.1:3306/sig") #engine 引擎 8 Base = declarative_base() #declarative 声明的 base 基地 9 # 创建单表 10 class Users(Base): 11 __tablename__ = 'users' #表名 12 id = Column(Integer, primary_key=True) 13 name = Column(String(32)) 14 extra = Column(String(16)) 15 num = Column(Integer) 16 #args 传感器 17 __table_args__ = ( 18 UniqueConstraint('id', 'name', name='uix_id_name'), #Constraint 约束 unique 唯一 19 Index('ix_id_name', 'name', 'extra'), 20 ) 21 22 def __repr__(self): 23 return "%s-%s" %(self.id, self.name) 24 # 创建一对多 25 class Favor(Base): 26 __tablename__ = 'favor' 27 nid = Column(Integer, primary_key=True) 28 caption = Column(String(50), default='red', unique=True) 29 def __repr__(self): 30 return "%s-%s" %(self.id, self.name) 31 32 class Person(Base): 33 __tablename__ = 'person' 34 nid = Column(Integer, primary_key=True) 35 name = Column(String(32), index=True, nullable=True) 36 favor_id = Column(Integer, ForeignKey("favor.nid")) 37 38 # 创建多对多 39 class Group(Base): 40 __tablename__ = 'group' 41 id = Column(Integer, primary_key=True) 42 name = Column(String(64), unique=True, nullable=False) 43 port = Column(Integer, default=22) 44 45 class Server(Base): 46 __tablename__ = 'server' 47 id = Column(Integer, primary_key=True, autoincrement=True) 48 hostname = Column(String(64), unique=True, nullable=False) 49 50 class ServerToGroup(Base): 51 __tablename__ = 'servertogroup' 52 nid = Column(Integer, primary_key=True, autoincrement=True) 53 server_id = Column(Integer, ForeignKey('server.id')) 54 group_id = Column(Integer, ForeignKey('group.id')) 55 56 Base.metadata.create_all(engine) #metadata元数据 把创建的表格发送給数据库 57 58 59 60 Session = sessionmaker(bind=engine) #session 会话 maker制造者 sessionmaker会话者 61 session = Session() #创建一个session对象 62 # 写入数据 63 # obj = Users(name="alex0", extra='sb') 64 # session.add(obj) 65 # session.add_all([ 66 # Users(name="alex1", extra='sb'), 67 # Users(name="alex2", extra='sb'), 68 # ]) 69 70 #删除数据 71 # session.query(Users).filter(Users.id > 2).delete() #query查询 filter过滤 72 # session.commit() 73 74 # 改数据 75 # session.query(Users).filter(Users.id < 3).update({"name" : "alxe" }) 76 # session.query(Users).filter(Users.id < 3).update({Users.name: Users.name + "199"}, synchronize_session=False) #synchronize使用同步 77 # session.query(Users).filter(Users.id < 3).update({"num": Users.num + 1}, synchronize_session="evaluate") #evaluate评价 估价 求的值 78 # session.commit() 79 80 # 查看数据 81 # ret = session.query(Users).all() 82 # ret = session.query(Users.name, Users.extra).all() 83 # ret = session.query(Users).filter_by(name='alxe199').all() 84 # ret = session.query(Users).filter_by(name='alxe199').first() 85 # print(ret) 86 session.commit() #commit 把。。。托付給 把写入的内容发送給数据库
#!/usr/bin/env python # -*- coding:utf-8 -*- from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index from sqlalchemy.orm import sessionmaker, relationship from sqlalchemy import create_engine engine = create_engine("mysql+pymysql://root@127.0.0.1:3306/sig") #engine 引擎 Base = declarative_base() #declarative 声明的 base 基地 # 创建单表 class Users(Base): __tablename__ = 'users' #表名 id = Column(Integer, primary_key=True) name = Column(String(32)) extra = Column(String(16)) num = Column(Integer) #args 传感器 __table_args__ = ( UniqueConstraint('id', 'name', name='uix_id_name'), #Constraint 约束 unique 唯一 Index('ix_id_name', 'name', 'extra'), ) def __repr__(self): return "%s-%s-%s_%s" %(self.id, self.name,self.extra,self.num) # 创建一对多 class Favor(Base): __tablename__ = 'favor' nid = Column(Integer, primary_key=True) caption = Column(String(50), default='red', unique=True) def __repr__(self): return "%s-%s" %(self.nid, self.caption) class Person(Base): __tablename__ = 'person' nid = Column(Integer, primary_key=True) name = Column(String(32), index=True, nullable=True) favor_id = Column(Integer, ForeignKey("favor.nid")) def __repr__(self): return "%s-%s-%s" %(self.nid, self.name,self.favor_id) # 创建多对多 class Group(Base): __tablename__ = 'group' id = Column(Integer, primary_key=True) name = Column(String(64), unique=True, nullable=False) port = Column(Integer, default=22) class Server(Base): __tablename__ = 'server' id = Column(Integer, primary_key=True, autoincrement=True) hostname = Column(String(64), unique=True, nullable=False) class ServerToGroup(Base): __tablename__ = 'servertogroup' nid = Column(Integer, primary_key=True, autoincrement=True) server_id = Column(Integer, ForeignKey('server.id')) group_id = Column(Integer, ForeignKey('group.id')) Base.metadata.create_all(engine) #metadata元数据 把创建的表格发送給数据库 Session = sessionmaker(bind=engine) #session 会话 maker制造者 sessionmaker会话者 session = Session() #创建一个session对象 # 写入数据 # obj = Users(name="alex0", extra='sb') # session.add(obj) # session.add_all([ # Users(name="alex1", extra='sb'), # Users(name="alex2", extra='sb'), # ]) #删除数据 # session.query(Users).filter(Users.id > 2).delete() #query查询 filter过滤 # session.commit() # 改数据 # session.query(Users).filter(Users.id < 3).update({"name" : "alxe" }) # session.query(Users).filter(Users.id < 3).update({Users.name: Users.name + "199"}, synchronize_session=False) #synchronize使用同步 # session.query(Users).filter(Users.id < 3).update({"num": Users.num + 1}, synchronize_session="evaluate") #evaluate评价 估价 求的值 # session.commit() # 查看数据 # ret = session.query(Users).all() # ret = session.query(Users.name, Users.extra).all() # ret = session.query(Users).filter_by(name='alxe199').all() # ret = session.query(Users).filter_by(name='alxe199').first() # 条件 # ret = session.query(Users).filter_by(name='alxe199').all() # ret = session.query(Users).filter(Users.id > 1, Users.name == 'eric').all() # ret = session.query(Users).filter(Users.id.between(1, 3), Users.name == 'eric').all() #between 在什么 之间 # ret = session.query(Users).filter(Users.id.in_([1,3,4])).all() # ret = session.query(Users.id).filter_by(name='eric').all() # ret = session.query(Users).filter(Users.id.in_(session.query(Users.id).filter_by(name='eric'))).all() # from sqlalchemy import and_, or_ #模块 and并且 or或者 # ret = session.query(Users).filter(and_(Users.id > 2, Users.name == 'eric')).all() # # ret = session.query(Users).filter(or_(Users.id < 4, Users.name == 'eric')).all() # ret = session.query(Users).filter( # or_( # Users.id < 2, # and_(Users.name == 'eric', Users.id > 3), # Users.extra != "" # )).all() # 通配符 # ret = session.query(Users).filter(Users.name.like('e%')).all() # ret = session.query(Users).filter(~Users.name.like('e%')).all() # ~ 符号是相反的意思， 除了这个条件 其他都成立 # 限制 # ret = session.query(Users)[1:5] #取 1-5 # 排序 # ret = session.query(Users).order_by(Users.id.desc()).all() # ret = session.query(Users).order_by(Users.name.desc(), Users.id.asc()).all() # 分组 from sqlalchemy.sql import func # ret = session.query(Users).group_by(Users.name).all() # ret = session.query( # func.max(Users.id), # func.sum(Users.id), # func.min(Users.id)).group_by(Users.name).all() # ret = session.query( # func.max(Users.id), # func.sum(Users.id), # func.min(Users.id)).group_by(Users.name).having(func.min(Users.id) >2).all() # 连表 # ret = session.query(Users, Favor).filter(Users.id == Favor.nid).all() # ret = session.query(Person).join(Favor).all() # # ret = session.query(Person).join(Favor, isouter=True).all() # 组合 # q1 = session.query(Users.name).filter(Users.id > 2) # q2 = session.query(Favor.caption).filter(Favor.nid < 2) # ret = q1.union(q2).all() # q1 = session.query(Users.name).filter(Users.id > 2) # q2 = session.query(Favor.caption).filter(Favor.nid < 2) # ret = q1.union_all(q2).all() # print(ret) session.commit() #commit 把。。。托付給 把写入的内容发送給数据库
#!/usr/bin/env python # -*- coding:utf-8 -*- from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index from sqlalchemy.orm import sessionmaker, relationship from sqlalchemy import create_engine engine = create_engine("mysql+pymysql://root@127.0.0.1:3306/sig") #engine 引擎 Base = declarative_base() #declarative 声明的 base 基地 # 创建单表 class Users(Base): __tablename__ = 'users' #表名 id = Column(Integer, primary_key=True) name = Column(String(32)) extra = Column(String(16)) num = Column(Integer) #args 传感器 __table_args__ = ( UniqueConstraint('id', 'name', name='uix_id_name'), #Constraint 约束 unique 唯一 Index('ix_id_name', 'name', 'extra'), ) def __repr__(self): return "%s-%s-%s_%s" %(self.id, self.name,self.extra,self.num) # 创建一对多 class Favor(Base): __tablename__ = 'favor' nid = Column(Integer, primary_key=True) caption = Column(String(50), default='red', unique=True) def __repr__(self): return "%s-%s" %(self.nid, self.caption) class Person(Base): __tablename__ = 'person' nid = Column(Integer, primary_key=True) name = Column(String(32), index=True, nullable=True) favor_id = Column(Integer, ForeignKey("favor.nid")) def __repr__(self): return "%s-%s-%s" %(self.nid, self.name,self.favor_id) # 创建多对多 class Group(Base): __tablename__ = 'group' id = Column(Integer, primary_key=True) name = Column(String(64), unique=True, nullable=False) port = Column(Integer, default=22) class Server(Base): __tablename__ = 'server' id = Column(Integer, primary_key=True, autoincrement=True) hostname = Column(String(64), unique=True, nullable=False) class ServerToGroup(Base): __tablename__ = 'servertogroup' nid = Column(Integer, primary_key=True, autoincrement=True) server_id = Column(Integer, ForeignKey('server.id')) group_id = Column(Integer, ForeignKey('group.id')) Base.metadata.create_all(engine) #metadata元数据 把创建的表格发送給数据库 Session = sessionmaker(bind=engine) #session 会话 maker制造者 sessionmaker会话者 session = Session() #创建一个session对象 # 写入数据 # obj = Users(name="alex0", extra='sb') # session.add(obj) # session.add_all([ # Users(name="alex1", extra='sb'), # Users(name="alex2", extra='sb'), # ]) #删除数据 # session.query(Users).filter(Users.id > 2).delete() #query查询 filter过滤 # session.commit() # 改数据 # session.query(Users).filter(Users.id < 3).update({"name" : "alxe" }) # session.query(Users).filter(Users.id < 3).update({Users.name: Users.name + "199"}, synchronize_session=False) #synchronize使用同步 # session.query(Users).filter(Users.id < 3).update({"num": Users.num + 1}, synchronize_session="evaluate") #evaluate评价 估价 求的值 # session.commit() # 查看数据 # ret = session.query(Users).all() # ret = session.query(Users.name, Users.extra).all() # ret = session.query(Users).filter_by(name='alxe199').all() # ret = session.query(Users).filter_by(name='alxe199').first() # 条件 # ret = session.query(Users).filter_by(name='alxe199').all() # ret = session.query(Users).filter(Users.id > 1, Users.name == 'eric').all() # ret = session.query(Users).filter(Users.id.between(1, 3), Users.name == 'eric').all() #between 在什么 之间 # ret = session.query(Users).filter(Users.id.in_([1,3,4])).all() # ret = session.query(Users.id).filter_by(name='eric').all() # ret = session.query(Users).filter(Users.id.in_(session.query(Users.id).filter_by(name='eric'))).all() # from sqlalchemy import and_, or_ #模块 and并且 or或者 # ret = session.query(Users).filter(and_(Users.id > 2, Users.name == 'eric')).all() # # ret = session.query(Users).filter(or_(Users.id < 4, Users.name == 'eric')).all() # ret = session.query(Users).filter( # or_( # Users.id < 2, # and_(Users.name == 'eric', Users.id > 3), # Users.extra != "" # )).all() # 通配符 # ret = session.query(Users).filter(Users.name.like('e%')).all() # ret = session.query(Users).filter(~Users.name.like('e%')).all() # ~ 符号是相反的意思， 除了这个条件 其他都成立 # 限制 # ret = session.query(Users)[1:5] #取 1-5 # 排序 # ret = session.query(Users).order_by(Users.id.desc()).all() # ret = session.query(Users).order_by(Users.name.desc(), Users.id.asc()).all() # 分组 from sqlalchemy.sql import func # ret = session.query(Users).group_by(Users.name).all() # ret = session.query( # func.max(Users.id), # func.sum(Users.id), # func.min(Users.id)).group_by(Users.name).all() # ret = session.query( # func.max(Users.id), # func.sum(Users.id), # func.min(Users.id)).group_by(Users.name).having(func.min(Users.id) >2).all() # 连表 # ret = session.query(Users, Favor).filter(Users.id == Favor.nid).all() # ret = session.query(Person).join(Favor).all() # ret = session.query(Person,Favor).join(Favor).all() # ret = session.query(Person.name('aaa'),Favor.('caption')).join(Favor).all() # 添加一个别名 # # ret = session.query(Person).join(Favor, isouter=True).all() # 组合 # q1 = session.query(Users.name).filter(Users.id > 2) # q2 = session.query(Favor.caption).filter(Favor.nid < 2) # ret = q1.union(q2).all() # q1 = session.query(Users.name).filter(Users.id > 2) # q2 = session.query(Favor.caption).filter(Favor.nid < 2) # ret = q1.union_all(q2).all() # print(ret) session.commit() #commit 把。。。托付給 把写入的内容发送給数据库
查看数据 键值对用 filter_by 查询 ret = session.query(Users).filter_by(name='alxe199').all() 条件判断用 filter ret = session.query(Users).filter(Users.id > 1, Users.name == 'eric').all()
1 from sqlalchemy.ext.declarative import declarative_base 2 from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index 3 from sqlalchemy.orm import sessionmaker, relationship 4 from sqlalchemy import create_engine 5 engine = create_engine("mysql+pymysql://root@127.0.0.1:3306/pro") #engine 引擎 6 Base = declarative_base() #declarative 声明的 base 基地 7 8 # 一对多 9 class Son(Base): 10 __tablename__ = 'son' 11 nid = Column(Integer, primary_key=True) 12 name = Column(String(32), index=True, nullable=True) 13 favor_id = Column(Integer, ForeignKey("father.nid")) 14 # 与生成表结构无关，仅用于查询方便 15 father = relationship('Father') 16 17 18 class Father(Base): 19 __tablename__ = 'father' 20 nid = Column(Integer, primary_key=True) 21 caption = Column(String(50), default='red', unique=True) 22 son = relationship('Son') 23 24 Base.metadata.create_all(engine) #metadata元数据 把创建的表格发送給数据库 25 26 Session = sessionmaker(bind=engine) #session 会话 maker制造者 sessionmaker会话者 27 session = Session() #创建一个session对象 28 29 # father类通过relationship 查son类对应的数据 30 ret = session.query(Father).filter(Father.nid<2).first() 31 print(ret.son) 32 for i in ret.son: 33 print(i.name) 34 35 # son类通过relationship 查 father类对应的数据 36 rets = session.query(Son).filter_by(nid=3).first() 37 print(rets.name) 38 print(rets.father.caption) 39 40 41 session.commit() #commit 把。。。托付給 把写入的内容发送給数据库 42 43 44 详解参照下图
1 from sqlalchemy.ext.declarative import declarative_base 2 from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index 3 from sqlalchemy.orm import sessionmaker, relationship 4 from sqlalchemy import create_engine 5 engine = create_engine("mysql+pymysql://root@127.0.0.1:3306/cole") #engine 引擎 6 Base = declarative_base() #declarative 声明的 base 基地 7 class M_W_Group(Base): 8 __tablename__ = 'm_w_group' 9 nid = Column(Integer, primary_key=True, autoincrement=True) 10 man_gid = Column(Integer, ForeignKey('man.man_id')) 11 woman_gid = Column(Integer, ForeignKey('woman.woman_id')) 12 13 class Man(Base): 14 __tablename__ = 'man' 15 man_id = Column(Integer, primary_key=True, autoincrement=True) 16 man_name = Column(String(64), unique=True, nullable=False) 17 man_age = Column(Integer) 18 19 class Woman(Base): 20 __tablename__ = 'woman' 21 woman_id = Column(Integer, primary_key=True) 22 woman_name = Column(String(64), unique=True, nullable=False) 23 woman_age = Column(Integer) 24 m_w_group = relationship('Man',secondary=M_W_Group.__table__,backref='gf') #erlationship关系 secondary第二的 backref后面的参考 25 Base.metadata.create_all(engine) 26 27 Session = sessionmaker(bind=engine) #session 会话 maker制造者 sessionmaker会话者 28 session = Session() #创建一个session对象 29 30 m1 = session.query(Man).filter_by(man_id=2).first() 31 w1 = session.query(Woman).all() 32 m1.gf=w1 33 session.add(m1) 34 35 36 session.commit() #commit 把。。。托付給 把写入的内容发送給数据库
