Python Sqlalchemy 初试用

Python代码：

from sqlalchemy import and_,or_
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker,relationship,backref
from sqlalchemy import create_engine,Column, Integer, String,ForeignKey


Base = declarative_base()

class User(Base):
    # 指定本类映射到users表
    __tablename__ = 'users'
    
    # 指定id映射到id字段; id字段为整型，为主键
    id = Column(Integer, primary_key=True)
    age = Column(Integer)
    name = Column(String(20))
    fullname = Column(String(32))
    password = Column(String(32))

    # address 指定Address对应表中通过外键关联起来的数据,通过user.address 便可访问该用户的地址信息
    # user 则为反向关系 在address信息中可以反向找到改地址信息所对应的用户信息
    # 必须要有外键的存在 否则会抛出:sqlalchemy.exc.NoForeignKeysError
    address = relationship('Address', backref=backref('user'))


    def __repr__(self):
        return "姓名='%s', 全名='%s', 密码='%s',age='%s'" % (self.name, self.fullname, self.password,self.age)


class Address(Base):
    # 指定本类映射到users表
    __tablename__ = 'address'
    
    id = Column(Integer,  primary_key=True)
    user_id = Column(Integer,ForeignKey('users.id'))
    street = Column(String(64), nullable=False)
    city = Column(String(64), nullable=False)
    state = Column(String(64), nullable=False)

    def __repr__(self):
        return "省份:%s 城市:%s 街区:%s" %(self.state, self.city, self.street)



engine = create_engine('sqlite:///F:\\test\\python_ormtest.db')
Base.metadata.create_all(engine)
db_session = sessionmaker(bind=engine)

session = db_session()

user1 = User(id='1', name='test_name',fullname='XXXXXX',password='123456',age=20)
session.add(user1)
user2 = User(id='2', name='XXXXX',fullname='XXXXXX',password='123456',age=25)
session.add(user2)
address = Address(user_id='1',street='xxxx',city='xxxxx',state='zzzzzzz')
session.add(address)

# 基本查询
result = session.query(User).filter(User.name=='test_name').all()
print('User基本查询结果:{}'.format(result))

result = session.query(Address).filter(Address.user_id=='1').one() # one() all() first()
print('Address基本查询结果:{}'.format(result))

# 多条件查询
result = session.query(User).filter(User.age>10).filter(User.name=='test_name').all()
print('多条件查询:{}'.format(result))

# 模糊查询
result = session.query(User).filter(User.name.like('%test%')).all()
print('模糊查询:{}'.format(result))

# 多条件模糊查询
result = session.query(User).filter(User.age>10).filter(User.name.like('%test%')).all()
print('多条件模糊查询:{}'.format(result))

# and 查询
result = session.query(User).filter(and_(User.age>10,User.name.like('test%'))).all()
print('and查询:{}'.format(result))

# or查询
result = session.query(User).filter(or_(User.age>10,User.name.like('test%'))).all()
print('or查询:{}'.format(result))

# in查询
result = session.query(User).filter(User.age.in_([20,25])).all()
print('in查询:{}'.format(result))
result = session.query(User).filter(User.age.in_([22,25])).all()
print('in查询:{}'.format(result))

# 排序
result = session.query(User).order_by(User.age.desc()).all()
print('排序:{}'.format(result))

# relationship address使用方式
result = session.query(User).filter(User.name=='test_name').first()
print('User.address:{}'.format(result.address))

# relationship user使用方式
result = session.query(Address).filter(Address.user_id=='1').first()
print('Address.user:{}'.format(result.user))

# 联合查询
result = session.query(User,Address).filter(User.id==Address.id).filter(User.name=='test_name').first()
print('联合查询结果:{}'.format(result))

# 修改
result = session.query(User).filter(User.id=='1').first()
result.name = 'new_name'
result = session.query(User).filter(User.id=='1').first()
print('修改后的name:{}'.format(result.name))
result = session.query(User).filter(User.id=='1').update({'name': 'new_new_name'})
print('修改后的name:{}'.format(result))

# 删除
result = session.query(Address).filter(Address.user_id=='1').delete()
print('删除结果:{}'.format(result))
result = session.query(User).delete()
print('删除结果:{}'.format(result))

session.commit()