SQLalchemy
ORM:
ORM框架的作用就是把数据库表的一行记录与一个对象互相做自动转换。 正确使用ORM的前提是了解关系数据库的原理。 ORM就是把数据库表的行与相应的对象建立关联，互相转换。 由于关系数据库的多个表还可以用外键实现一对多、多对多等关联，相应地， ORM框架也可以提供两个对象之间的一对多、多对多等功能。
一 单表操作（不涉及一对多，多对多）
1 #coding:utf8 2 import sqlalchemy 3 from sqlalchemy import create_engine 4 from sqlalchemy.ext.declarative import declarative_base 5 from sqlalchemy import Column, Integer, String 6 from sqlalchemy.orm import sessionmaker 7 print(sqlalchemy.__version__) 8 9 10 engine = create_engine('sqlite:///dbyuan1.db', echo=True) 11 12 Base = declarative_base()#生成一个SQLORM基类 13 14 class User(Base): 15 __tablename__ = 'users' 16 17 id = Column(Integer, primary_key=True) 18 name = Column(String) 19 fullname = Column(String) 20 password = Column(String) 21 22 def __repr__(self): 23 return "<User(name='%s', fullname='%s', password='%s')>" % ( 24 self.name, self.fullname, self.password) 25 26 Base.metadata.create_all(engine) #创建所有表结构 27 28 ed_user = User(name='xiaoyu', fullname='Xiaoyu Liu', password='123') 29 print(ed_user) 30 #这两行触发sessionmaker类下的__call__方法，return得到 Session实例，赋给变量session，所以session可以调用Session类下的add，add_all等方法 31 MySession = sessionmaker(bind=engine) 32 session = MySession() 33 34 session.add(ed_user) 35 # our_user = session.query(User).filter_by(name='ed').first() 36 # SELECT * FROM users WHERE name="ed" LIMIT 1; 37 # session.add_all([ 38 # User(name='alex', fullname='Alex Li', password='456'), 39 # User(name='alex', fullname='Alex old', password='789'), 40 # User(name='peiqi', fullname='Peiqi Wu', password='sxsxsx')]) 41 42 session.commit() 43 44 #print(">>>",session.query(User).filter_by(name='ed').first()) 45 #print(session.query(User).all()) 46 # for row in session.query(User).order_by(User.id): 47 # print(row) 48 # for row in session.query(User).filter(User.name.in_(['alex', 'wendy', 'jack'])):＃这里的名字是完全匹配 49 # print(row) 50 # for row in session.query(User).filter(~User.name.in_(['ed', 'wendy', 'jack'])): 51 # print(row) 52 #print(session.query(User).filter(User.name == 'ed').count()) 53 #from sqlalchemy import and_, or_ 54 55 # for row in session.query(User).filter(and_(User.name == 'ed', User.fullname == 'Ed Jones')): 56 # print(row) 57 # for row in session.query(User).filter(or_(User.name == 'ed', User.name == 'wendy')): 58 # print(row)
二 一对多的关联表操作
实例1:
1 #coding:utf8 2 3 import sqlalchemy 4 from sqlalchemy import create_engine 5 from sqlalchemy.ext.declarative import declarative_base 6 from sqlalchemy import Column, Integer, String,ForeignKey 7 from sqlalchemy.orm import sessionmaker,relationship 8 9 10 engine = create_engine('sqlite:///dbyuan2.db', echo=True) 11 12 Base = declarative_base() 13 14 class Father(Base): 15 __tablename__ = 'father' 16 #id = Column(Integer, primary_key=True)里的数据类型一定写整型(Integer) 17 id = Column(Integer, primary_key=True) 18 name = Column(String(20)) 19 def __repr__(self): 20 return "<Father(name='%s')>" % self.name 21 22 23 class Son(Base): 24 __tablename__ = 'son' 25 26 id = Column(Integer, primary_key=True) 27 name = Column(String(20)) 28 #ForeignKey建在多的一方 29 father_id = Column(String(20), ForeignKey('father.id')) 30 father=relationship("Father",backref="son", order_by=id) 31 def __repr__(self): 32 return "<Son(name='%s')>" % self.name 33 34 Base.metadata.create_all(engine) 35 36 Session = sessionmaker(bind=engine) 37 session = Session() 38 39 40 f1= Father(name='zhangsan') 41 f2= Father(name='lisi') 42 f3= Father(name='wangwu') 43 44 f1.son = [Son(name='zhangdasan'),Son(name='zhangersan')] 45 46 47 session.add(f1) 48 session.commit() 49 50 for u, a in session.query(Father, Son).\ 51 filter(Father.id==Son.id).\ 52 all(): 53 print u, a #<Father(name='zhangsan')> <Son(name='zhangdasan')>
实例2:
1 #__ *__ coding:utf8__*__ 2 3 from sqlalchemy import create_engine 4 from sqlalchemy.ext.declarative import declarative_base 5 from sqlalchemy import Column, Integer, String,and_,or_,ForeignKey 6 from sqlalchemy.orm import sessionmaker,relationship 7 8 Base = declarative_base() #生成一个SqlORM 基类 9 10 11 engine = create_engine('sqlite:///dbyuan3.db', echo=True) 12 13 14 class Host(Base): 15 __tablename__ ='host' 16 id = Column(Integer,primary_key=True,autoincrement=True) 17 hostname = Column(String(64),unique=True,nullable=False) 18 ip_addr = Column(String(128),unique=True,nullable=False) 19 port = Column(Integer,default=22) 20 #前提 一个主机只能属于一个组 21 group_id=Column(Integer,ForeignKey('group.id')) 22 group=relationship('Group',backref='host') 23 def __repr__(self): 24 return "id:%s hostname:%s port:%s"%(self.id,self.hostname,self.port) 25 26 27 class Group(Base): 28 __tablename__='group' 29 id=Column(Integer,primary_key=True) 30 name=Column(String(64),unique=True,nullable=False) 31 32 def __repr__(self): 33 return "id:%s hostname:%s"%(self.id,self.name) 34 35 36 Base.metadata.create_all(engine) #创建所有表结构 37 38 if __name__ == '__main__': 39 SessionCls = sessionmaker(bind=engine,autoflush=False) 40 session = SessionCls() 41 42 g1=Group(name='g1') 43 g2=Group(name='g2') 44 g3=Group(name='g3') 45 session.add_all([g1,g2,g3]) 46 session.commit() 47 48 h1 = Host(hostname='localhost',ip_addr='127.0.0.1',group_id=g1.id)#g1如果在这之前没有提交，group_id拿到的永远是一个空值 49 h2 = Host(hostname='ubuntu',ip_addr='192.168.2.243',port=20000) 50 session.add_all([h1,h2]) 51 52 session.commit() 53 54 g1=session.query(Group).filter(Group.name=='g1').first() 55 h=session.query(Host).filter(Host.hostname=='localhost').first()#注意要加上first()，否则报错，注意与all()结果的不同 56 57 print "<<<",g2 58 print ">>>",h 59 60 print(h.group.name) 61 print g1.host 62 print g1.host[0].hostname 63 64 #g2.host什么结果？（未绑定，无结果）
三 多对多的关联表操作
1 #!/usr/bin/env python 2 # -*- coding:utf-8 -*- 3 4 from sqlalchemy import create_engine,and_,or_,func,Table 5 from sqlalchemy.ext.declarative import declarative_base 6 from sqlalchemy import Column, Integer, String,ForeignKey 7 from sqlalchemy.orm import sessionmaker,relationship 8 9 Base = declarative_base() #生成一个SqlORM 基类 10 11 12 Host2Group = Table('host_2_group',Base.metadata, 13 Column('host_id',ForeignKey('host.id'),primary_key=True), 14 Column('group_id',ForeignKey('group.id'),primary_key=True),) 15 16 17 engine = create_engine('sqlite:///dbyuan4.db', echo=True) 18 19 class Host(Base): 20 __tablename__ = 'host' 21 22 id = Column(Integer,primary_key=True,autoincrement=True) 23 hostname = Column(String(64),unique=True,nullable=False) 24 ip_addr = Column(String(128),unique=True,nullable=False) 25 port = Column(Integer,default=22) 26 group = relationship('Group', 27 secondary=Host2Group, 28 backref='host_list') 29 30 #group =relationship("Group",back_populates='host_list') 31 def __repr__(self): 32 return "<id=%s,hostname=%s, ip_addr=%s>" %(self.id, 33 self.hostname, 34 self.ip_addr) 35 class Group(Base): 36 __tablename__ = 'group' 37 id = Column(Integer,primary_key=True) 38 name = Column(String(64),unique=True,nullable=False) 39 40 def __repr__(self): 41 return "<id=%s,name=%s>" %(self.id,self.name) 42 43 Base.metadata.create_all(engine) #创建所有表结构 44 45 if __name__ == '__main__': 46 SessionCls = sessionmaker(bind=engine,autoflush=False) 47 session = SessionCls() 48 49 50 g1 = Group(name='g1') 51 g2 = Group(name='g2') 52 g3 = Group(name='g3') 53 g4 = Group(name='g4') 54 session.add_all([g1,g2,g3,g4]) 55 session.commit() 56 57 58 #g4 = session.query(Group).filter(Group.name=='g4').first() 59 #h = session.query(Host).filter(Host.hostname=='localhost').update({'group_id':g4.id}) 60 #h = session.query(Host).filter(Host.hostname=='localhost').first() 61 #print("h1:",h.group.name ) 62 #print("g:",g4.host_list ) 63 64 65 h1 = Host(hostname='h1',ip_addr='192.168.1.56') 66 h2 = Host(hostname='h2',ip_addr='192.168.1.57',port=10000) 67 h3 = Host(hostname='ubuntu',ip_addr='192.168.1.58',port=10000) 68 session.add_all([h1,h2,h3]) 69 session.commit() 70 71 groups = session.query(Group).all() 72 g1 = session.query(Group).first() 73 74 h2 = session.query(Host).filter(Host.hostname=='h2').first() 75 h2.group = groups[1:-1] 76 print("===========>",h2.group) 77 78 #objs = #session.query(Host).join(Host.group).group_by(Group.name).all() 79 #objs = session.query(Host,func.count(Group.name)).\ 80 #join(Host.group).group_by(Group.name).all() 81 #print("-->objs:",objs) 82 #print("++>",obj) 83 #obj.hostname = "test server" 84 #session.delete(obj) 85 #objs = session.query(Host).filter(and_(Host.hostname.like("ub%"), Host.port > 20)).all() 86 87 session.commit()
注意：
1 Session = sessionmaker(bind=engine,autoflush=False)
2 session.add添加数据到数据后，一定要session.commit()后才能增删改查，否则结果只能为none
3 session.query(Group).filter(Group.name=='g1').first() 注意有无first()的区别
再注意：
1 关于 session.add session.query session.commit的顺序问题？
就是说在同一个会话中， insert into table (xxxx)后，可以select * from xxx;可以查询到插入的数据，只是不能在其他会话，比如我另开一个客户端去连接数据库不能查询到刚刚插入的数据。
这个数据已经到数据库。值是数据库吧这个数据给锁了。只有插入数据的那个session可以查看到，其他的session不能查看到，可以理解提交并解锁吧。
2 第三张表必须利用table创建吗？NO
3 联合唯一
4 一对多的第二个例子，如何理解去掉第一个commit后就报错的现象
sqlalchemy2
1 #!/usr/bin/env python 2 # -*- coding:utf-8 -*- 3 4 from sqlalchemy import create_engine,and_,or_,func,Table 5 from sqlalchemy.ext.declarative import declarative_base 6 from sqlalchemy import Column, Integer, String,ForeignKey 7 from sqlalchemy.orm import sessionmaker,relationship 8 9 10 11 engine = create_engine('sqlite:///dbyuanabcd4970.db', echo=True) 12 13 Base = declarative_base() #生成一个SqlORM 基类 14 15 16 class HostToGroup(Base): 17 __tablename__='host_2_group' 18 nid=Column(Integer,primary_key=True) 19 host_id=Column(Integer,ForeignKey("host.id")) 20 group_id=Column(Integer,ForeignKey("group.id")) 21 22 23 class Host(Base): 24 __tablename__ = 'host' 25 26 id = Column(Integer,primary_key=True,autoincrement=True) 27 hostname = Column(String(64),unique=True,nullable=False) 28 ip_addr = Column(String(128),unique=True,nullable=False) 29 port = Column(Integer,default=22) 30 group = relationship('Group', 31 secondary=HostToGroup.__table__, 32 backref='host_list') 33 34 #group =relationship("Group",back_populates='host_list') 35 def __repr__(self): 36 return "<id=%s,hostname=%s, ip_addr=%s>" %(self.id, 37 self.hostname, 38 self.ip_addr) 39 class Group(Base): 40 __tablename__ = 'group' 41 id = Column(Integer,primary_key=True) 42 name = Column(String(64),unique=True,nullable=False) 43 44 def __repr__(self): 45 return "<id=%s,name=%s>" %(self.id,self.name) 46 47 Base.metadata.create_all(engine) #创建所有表结构 48 49 if __name__ == '__main__': 50 SessionCls = sessionmaker(bind=engine) 51 session = SessionCls() 52 53 54 # g1 = Group(name='g1') 55 # g2 = Group(name='g2') 56 # g3 = Group(name='g3') 57 # g4 = Group(name='g4') 58 # session.add_all([g1,g2,g3,g4]) 59 # h1 = Host(hostname='h1',ip_addr='192.168.1.56') 60 # h2 = Host(hostname='h2',ip_addr='192.168.1.57',port=10000) 61 # h3 = Host(hostname='ubuntu',ip_addr='192.168.1.58',port=10000) 62 # 63 # h1.group=[g2,g4] 64 # session.add_all([h1,h2,h3]) 65 # session.commit() 66 67 68 # groups = session.query(Group).all() 69 # h2 = session.query(Host).filter(Host.hostname=='h2').first() 70 # h2.group = groups[:-1] 71 # print("===========>",h2.group) 72 73 74 75 # g4 = session.query(Group).filter(Group.name=='g4').first() 76 # print(g4) 77 # obj1 = session.query(Host).filter(Host.hostname=='h1').update({'port':444}) 78 # 79 # obj2= session.query(Host).filter(Host.hostname=='h1').first() 80 # print("h1:",obj2.group) 81 # print("g:",g4.host_list ) 82 83 84 85 86 87 session.commit()
1 #!/usr/bin/env python 2 # -*- coding:utf-8 -*- 3 4 from sqlalchemy.ext.declarative import declarative_base 5 from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index 6 from sqlalchemy.orm import sessionmaker, relationship 7 from sqlalchemy import create_engine 8 9 engine = create_engine('sqlite:///dbyuan691.db', echo=True) 10 11 Base = declarative_base() 12 13 14 class Son(Base): 15 __tablename__ = 'son' 16 id = Column(Integer, primary_key=True) 17 name = Column(String(32)) 18 age= Column(String(16)) 19 20 father_name=Column(String(20),ForeignKey('father.name')) 21 father=relationship('Father') 22 23 __table_args__ = ( 24 UniqueConstraint('id', 'name', name='uix_id_name'), 25 Index('ix_id_name', 'name', 'extra'), 26 ) 27 28 class Father(Base): 29 __tablename__ ='father' 30 31 id = Column(Integer, primary_key=True) 32 name = Column(String(32)) 33 age= Column(String(16)) 34 son=relationship('Son') 35 36 Base.metadata.create_all(engine) 37 38 39 40 41 Session = sessionmaker(bind=engine) 42 session = Session() 43 44 f1=Father(name='alvin',age=50) 45 w1=Son(name='little alvin1',age=4) 46 w2=Son(name='little alvin2',age=5) 47 48 49 50 f1.son=[w1,w2] 51 52 53 session.add(f1) 54 session.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 8 engine = create_engine('sqlite:///dbyuan674uu.db', echo=True) 9 10 Base = declarative_base() 11 12 13 14 class Men_to_Wemon(Base): 15 __tablename__ = 'men_to_wemon' 16 nid = Column(Integer, primary_key=True) 17 men_id = Column(Integer, ForeignKey('men.id')) 18 women_id = Column(Integer, ForeignKey('women.id')) 19 20 21 22 class Men(Base): 23 __tablename__ = 'men' 24 id = Column(Integer, primary_key=True) 25 name = Column(String(32)) 26 age= Column(String(16)) 27 28 29 30 class Women(Base): 31 __tablename__ ='women' 32 id = Column(Integer, primary_key=True) 33 name = Column(String(32)) 34 age= Column(String(16)) 35 bf=relationship("Men",secondary=Men_to_Wemon.__table__,backref='gf') 36 37 38 39 Base.metadata.create_all(engine) 40 41 42 43 Session = sessionmaker(bind=engine) 44 session = Session() 45 46 47 m1=Men(name='alex',age=18) 48 m2=Men(name='wusir',age=18) 49 w1=Women(name='如花',age=40) 50 w2=Women(name='铁锤',age=45) 51 52 # t1=Men_to_Wemon(men_id=1,women_id=2) 53 54 55 56 m1.gf=[w1,w2] 57 w1.bf=[m1,m2] 58 59 60 session.add_all([m1,m2,w1,w2]) 61 62 63 session.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 8 engine = create_engine('sqlite:///dbyuan67.db?') 9 10 Base=declarative_base() 11 12 13 class Men(Base): 14 __tablename__ = 'men' 15 id = Column(Integer, primary_key=True) 16 name = Column(String(32)) 17 age= Column(String(16)) 18 19 # __table_args__ = ( 20 # UniqueConstraint('id', 'name', name='uix_id_name'), 21 # Index('ix_id_name', 'name', 'extra'), 22 # ) 23 24 def __repr__(self): 25 return self.name 26 27 class Women(Base): 28 __tablename__ ='women' 29 id = Column(Integer, primary_key=True) 30 name = Column(String(32)) 31 age= Column(String(16)) 32 33 men_id=Column(String(20), ForeignKey('men.id')) 34 # def __repr__(self): 35 # return self.age 36 37 # Base.metadata.create_all(engine) 38 # Base.metadata.drop_all(engine) 39 Session = sessionmaker(bind=engine) 40 session = Session() 41 # select * from 42 # select id,name from women 43 # sql=session.query(Women).all() 44 # select * from women inner join men on women.men_id = men.id 45 sql = session.query(Women.age,Men.name).join(Men).all() 46 print(sql) 47 # print(sql) 48 # r = session.query(session.query(Women.name.label('t1'), Men.name.label('t2')).join(Men).all()).all() 49 # print(r) 50 51 # r = session.query(Women).all() 52 # print(r) 53 54 55 # m1=Men(name='alex',age=18) 56 # w1=Women(name='如花',age=40) 57 # w2=Women(name='铁锤',age=45) 58 # m1.gf=[Women(name='如花',age=40),Women(name='铁锤',age=45)] 59 60 # m1=Men(name='alex',age=18) 61 # w1=Women(name='如花',age=40,men_id = 1) 62 # w2=Women(name='铁锤',age=45,men_id = 1) 63 # session.add_all([w1,w2]) 64 # session.commit()
