sqlalchemy增删改查及关系使用介绍

1. SQLAlchemy的作用

ORM对象关系映射技术

2. SQLAlchemy安装

pip install SQLAlchemy

查看SQLAlchemy版本

3. 声明模型Model

from sqlalchemy import Column,String,Integer
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class User(Base):
    __tablename__='user'
    id=Column(Integer,primary_key=True)
    name=Column(String(50))

基类Base，是一个model和数据库表管理类。
通过继承Base，可自动实现model和数据库表的关联。

4. 创建数据库表

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

from user import User
if __name__ == "__main__":
    engine=create_engine("postgresql://postgres:sj1107@localhost:5432/sampledb")
    Session=sessionmaker(bind=engine)
    session=Session()
    User.metadata.create_all(engine)

engine:数据库连接引擎
Session:一个持久的数据库连接会话
User.metadata.create_all(engine)：创建表

5. 增删改查

session是关系型数据库中的事务。

5.1 增加记录

user=User(name="shijingjing07")
session.add(user)
session.commit()

必须commit，才能真正写入数据库

5.2 删除记录

usr=session.query(User).first()
session.delete(usr)
session.commit()

5.3 更新记录

usr=session.query(User).first()
usr.name="icefinger"
session.add(usr)
session.commit()

5.4 查询记录

过滤器：

#==
usr=session.query(User).filter(User.name=="icefinger").first()
print(usr.id)
#!=
usr=session.query(User).filter(User.name!="icefinger").first()
print(usr.id)
#like
usr=session.query(User).filter(User.name.like("icefinger%")).first()
print(usr.id)
#in
usr=session.query(User).filter(User.name.in_(["icefinger","tomcat","james"])).first()
print(usr.id)
#not in
usr=session.query(User).filter(~User.name.in_(["icefinger","tomcat","james"])).first()
print(usr.id)
#is null
usr=session.query(User).filter(User.name==None).first()
print(usr.id)
usr=session.query(User).filter(User.name.is_(None)).first()
print(usr.id)
#and
from sqlalchemy import and_
usr=session.query(User).filter(and_(User.name=="icefinger",User.id=="2")).first()
print(usr.id)
#or
from sqlalchemy import or_
usr=session.query(User).filter(or_(User.name=="icefinger",User.id=="3")).first()
print(usr.id)

返回值：

#first,使用limit返回第一行
print("--first--")
usr=session.query(User).filter(or_(User.name=="icefinger",User.id=="3")).first()
print(usr.id)
#all,返回所有行
print("--all--")
usrlist=session.query(User).filter(or_(User.name=="icefinger",User.id=="3")).all()
for usr in usrlist:
    print(usr.id)
#one,返回行数只能是一条
print("--one--")
try:
    usr = session.query(User).filter(or_(User.name == "icefinger", User.id == "3")).one()
    print(usr)
except:
    print("must be one")
#one_on_none,返回行数只能是一条,或none
print("--one_or_none--")
usr = session.query(User).filter(and_(User.name == "icefinger", User.id == "2")).one_or_none()
print(usr)
#scalar，同one_on_none,返回行数只能是一条,或none
print("--scalar--")
usr = session.query(User).filter(or_(User.name == "icefinger", User.id == "2")).scalar()
print(usr)

运行结果：

统计个数：

print("--count1--")
count=session.query(User).count()
print(count)
print("--count2--")
count = session.query(func.count('*')).select_from(User).scalar()
print(count)

6. 关系-一对多

6.1 如下图所示，一个用户可能对应多个地址

from sqlalchemy import create_engine,and_,or_,func
from sqlalchemy import Table,Column,String,Integer,ForeignKey
from sqlalchemy.orm import relationship,sessionmaker
from sqlalchemy.ext.declarative import declarative_base
engine=create_engine("postgresql://postgres:sj1107@localhost:5432/sampledb")
Session=sessionmaker(bind=engine)
session = Session()
Base = declarative_base(bind=engine)
class User(Base):
    __tablename__='user'
    id=Column(Integer,primary_key=True)
    addresses=relationship('Address')
class Address(Base):
    __tablename__='address'
    id=Column(Integer,primary_key=True)
    user_id=Column(Integer,ForeignKey('user.id'))

if __name__ == "__main__":
    Base.metadata.create_all()
    u=User()
    session.add(u)
    session.commit()
    a1=Address(user_id=u.id)
    a2=Address(user_id=u.id)
    session.add(a1)
    session.add(a2)
    session.commit()
    print(u.addresses)

运行结果：

ForeignKey:外键，制定了user_id和User的关系
relationship:绑定了两个Model的联系,通过User直接得到所有的地址。

6.2 根据address获取user

address只能获得user_id，然后根据user_id获取user
能不能通过address直接获取user呢？在model里，添加relationship关系就可以了。

class User(Base):
    __tablename__='user'
    id=Column(Integer,primary_key=True)
    addresses=relationship('Address')
class Address(Base):
    __tablename__='address'
    id=Column(Integer,primary_key=True)
    user_id=Column(Integer,ForeignKey('user.id'))
    user=relationship('User')

运行结果：

6.3 上例中两个model中都添加relationship，看起来很繁琐，能不能只指定一个，另一个默认就可以访问呢？

backref参数就可以了。

class User(Base):
    __tablename__='user'
    id=Column(Integer,primary_key=True)
    addresses=relationship('Address',backref="user")
class Address(Base):
    __tablename__='address'
    id=Column(Integer,primary_key=True)
    user_id=Column(Integer,ForeignKey('user.id'))

运行结果：

7. 关系-多对多

user和address关系为多对多，即一个user对应多个address，一个address对应多个user
多对多需要中间表来关联

#定义中间表，关联多对多关系
user_address_table =Table(
    'user_address',Base.metadata,
    Column('user_id',Integer,ForeignKey('user.id')),
    Column('address_id',Integer,ForeignKey('address.id'))
)
class User(Base):
    __tablename__='user'
    id=Column(Integer,primary_key=True)
    addresses=relationship('Address',secondary=user_address_table)
class Address(Base):
    __tablename__='address'
    id=Column(Integer,primary_key=True)
    users=relationship('User',secondary=user_address_table)

if __name__ == "__main__":
    # Base.metadata.create_all()
    u1=User()
    u2=User()
    session.add(u1)
    session.add(u2)
    session.commit()
    a1=Address(users=[u1,u2])
    a2 = Address(users=[u1, u2])
    session.add(a1)
    session.add(a2)
    session.commit()
    print(u1.addresses)
    print(a1.users)
    session.delete(u1)
    print(a1.users)

运行结果：