python之sqlalchemy
ORM:
ORM框架的作用就是把数据库表的一行记录与一个对象互相做自动转换。 正确使用ORM的前提是了解关系数据库的原理。 ORM就是把数据库表的行与相应的对象建立关联，互相转换。 由于关系数据库的多个表还可以用外键实现一对多、多对多等关联，相应地， ORM框架也可以提供两个对象之间的一对多、多对多等功能。
一 单表操作（不涉及一对多，多对多）
#coding:utf8
import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import sessionmaker
print(sqlalchemy.__version__)
engine = create_engine('sqlite:///dbyuan1.db', echo=True)
Base = declarative_base()#生成一个SQLORM基类
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
name = Column(String)
fullname = Column(String)
password = Column(String)
def __repr__(self):
return "<User(name='%s', fullname='%s', password='%s')>" % (
self.name, self.fullname, self.password)
Base.metadata.create_all(engine) #创建所有表结构
ed_user = User(name='xiaoyu', fullname='Xiaoyu Liu', password='123')
print(ed_user)
#这两行触发sessionmaker类下的__call__方法，return得到 Session实例，赋给变量session，所以session可以调用Session类下的add，add_all等方法
MySession = sessionmaker(bind=engine)
session = MySession()
session.add(ed_user)
# our_user = session.query(User).filter_by(name='ed').first()
# SELECT * FROM users WHERE name="ed" LIMIT 1;
# session.add_all([
# User(name='alex', fullname='Alex Li', password='456'),
# User(name='alex', fullname='Alex old', password='789'),
# User(name='peiqi', fullname='Peiqi Wu', password='sxsxsx')])
session.commit()
#print(">>>",session.query(User).filter_by(name='ed').first())
#print(session.query(User).all())
# for row in session.query(User).order_by(User.id):
# print(row)
# for row in session.query(User).filter(User.name.in_(['alex', 'wendy', 'jack'])):＃这里的名字是完全匹配
# print(row)
# for row in session.query(User).filter(~User.name.in_(['ed', 'wendy', 'jack'])):
# print(row)
#print(session.query(User).filter(User.name == 'ed').count())
#from sqlalchemy import and_, or_
# for row in session.query(User).filter(and_(User.name == 'ed', User.fullname == 'Ed Jones')):
# print(row)
# for row in session.query(User).filter(or_(User.name == 'ed', User.name == 'wendy')):
# print(row)
二 一对多的关联表操作
实例1:
#coding:utf8
import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String,ForeignKey
from sqlalchemy.orm import sessionmaker,relationship
engine = create_engine('sqlite:///dbyuan2.db', echo=True)
Base = declarative_base()
class Father(Base):
__tablename__ = 'father'
#id = Column(Integer, primary_key=True)里的数据类型一定写整型(Integer)
id = Column(Integer, primary_key=True)
name = Column(String(20))
def __repr__(self):
return "<Father(name='%s')>" % self.name
class Son(Base):
__tablename__ = 'son'
id = Column(Integer, primary_key=True)
name = Column(String(20))
#ForeignKey建在多的一方
father_id = Column(String(20), ForeignKey('father.id'))
father=relationship("Father",backref="son", order_by=id)
def __repr__(self):
return "<Son(name='%s')>" % self.name
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()
f1= Father(name='zhangsan')
f2= Father(name='lisi')
f3= Father(name='wangwu')
f1.son = [Son(name='zhangdasan'),Son(name='zhangersan')]
session.add(f1)
session.commit()
for u, a in session.query(Father, Son).\
filter(Father.id==Son.id).\
all():
print u, a #<Father(name='zhangsan')> <Son(name='zhangdasan')>
实例2:
#__ *__ coding:utf8__*__
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String,and_,or_,ForeignKey
from sqlalchemy.orm import sessionmaker,relationship
Base = declarative_base() #生成一个SqlORM 基类
engine = create_engine('sqlite:///dbyuan3.db', echo=True)
class Host(Base):
__tablename__ ='host'
id = Column(Integer,primary_key=True,autoincrement=True)
hostname = Column(String(64),unique=True,nullable=False)
ip_addr = Column(String(128),unique=True,nullable=False)
port = Column(Integer,default=22)
#前提 一个主机只能属于一个组
group_id=Column(Integer,ForeignKey('group.id'))
group=relationship('Group',backref='host')
def __repr__(self):
return "id:%s hostname:%s port:%s"%(self.id,self.hostname,self.port)
class Group(Base):
__tablename__='group'
id=Column(Integer,primary_key=True)
name=Column(String(64),unique=True,nullable=False)
def __repr__(self):
return "id:%s hostname:%s"%(self.id,self.name)
Base.metadata.create_all(engine) #创建所有表结构
if __name__ == '__main__':
SessionCls = sessionmaker(bind=engine,autoflush=False)
session = SessionCls()
g1=Group(name='g1')
g2=Group(name='g2')
g3=Group(name='g3')
session.add_all([g1,g2,g3])
session.commit()
h1 = Host(hostname='localhost',ip_addr='127.0.0.1',group_id=g1.id)#g1如果在这之前没有提交，group_id拿到的永远是一个空值
h2 = Host(hostname='ubuntu',ip_addr='192.168.2.243',port=20000)
session.add_all([h1,h2])
session.commit()
g1=session.query(Group).filter(Group.name=='g1').first()
h=session.query(Host).filter(Host.hostname=='localhost').first()#注意要加上first()，否则报错，注意与all()结果的不同
print "<<<",g2
print ">>>",h
print(h.group.name)
print g1.host
print g1.host[0].hostname
#g2.host什么结果？（未绑定，无结果）
三 多对多的关联表操作
#!/usr/bin/env python
# -*- coding:utf-8 -*-
from sqlalchemy import create_engine,and_,or_,func,Table
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String,ForeignKey
from sqlalchemy.orm import sessionmaker,relationship
Base = declarative_base() #生成一个SqlORM 基类
Host2Group = Table('host_2_group',Base.metadata,
Column('host_id',ForeignKey('host.id'),primary_key=True),
Column('group_id',ForeignKey('group.id'),primary_key=True),)
engine = create_engine('sqlite:///dbyuan4.db', echo=True)
class Host(Base):
__tablename__ = 'host'
id = Column(Integer,primary_key=True,autoincrement=True)
hostname = Column(String(64),unique=True,nullable=False)
ip_addr = Column(String(128),unique=True,nullable=False)
port = Column(Integer,default=22)
group = relationship('Group',
secondary=Host2Group,
backref='host_list')
#group =relationship("Group",back_populates='host_list')
def __repr__(self):
return "<id=%s,hostname=%s, ip_addr=%s>" %(self.id,
self.hostname,
self.ip_addr)
class Group(Base):
__tablename__ = 'group'
id = Column(Integer,primary_key=True)
name = Column(String(64),unique=True,nullable=False)
def __repr__(self):
return "<id=%s,name=%s>" %(self.id,self.name)
Base.metadata.create_all(engine) #创建所有表结构
if __name__ == '__main__':
SessionCls = sessionmaker(bind=engine,autoflush=False)
session = SessionCls()
g1 = Group(name='g1')
g2 = Group(name='g2')
g3 = Group(name='g3')
g4 = Group(name='g4')
session.add_all([g1,g2,g3,g4])
session.commit()
#g4 = session.query(Group).filter(Group.name=='g4').first()
#h = session.query(Host).filter(Host.hostname=='localhost').update({'group_id':g4.id})
#h = session.query(Host).filter(Host.hostname=='localhost').first()
#print("h1:",h.group.name )
#print("g:",g4.host_list )
h1 = Host(hostname='h1',ip_addr='192.168.1.56')
h2 = Host(hostname='h2',ip_addr='192.168.1.57',port=10000)
h3 = Host(hostname='ubuntu',ip_addr='192.168.1.58',port=10000)
session.add_all([h1,h2,h3])
session.commit()
groups = session.query(Group).all()
g1 = session.query(Group).first()
h2 = session.query(Host).filter(Host.hostname=='h2').first()
h2.group = groups[1:-1]
print("===========>",h2.group)
#objs = #session.query(Host).join(Host.group).group_by(Group.name).all()
#objs = session.query(Host,func.count(Group.name)).\
#join(Host.group).group_by(Group.name).all()
#print("-->objs:",objs)
#print("++>",obj)
#obj.hostname = "test server"
#session.delete(obj)
#objs = session.query(Host).filter(and_(Host.hostname.like("ub%"), Host.port > 20)).all()
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后就报错的现象
