python数据库（orm方式）sqlalchemy

使用sqlalchemy最大的作用是可以忽略不同数据库的差异进行开发，学会sqlalchemy可以随时切换不同数据库，还有一点是可以

创建virtualenv环境

virtualenv venv
source venv/bin/activate

安装所需依赖

• 安装所有的可选依赖及对应功能

pip install sqlalchemy

定义数据结构和创建连接

# 导入:
from sqlalchemy import Column, String, Integer
from sqlalchemy.orm import declarative_base
from sqlalchemy import create_engine

# 创建对象的基类:
Base = declarative_base()

# 定义User对象:
class User(Base):
    # 表的名字:
    __tablename__ = 'user'

    # 表的结构:
    id = Column(Integer, primary_key=True)
    name = Column(String(20))
    age = Column(Integer)
    
# 初始化数据库连接:
engine = create_engine('sqlite:///./mysql.db')

创建表格

查看User表的table属性

User.__table__ 

输出：

Table('user', MetaData(), Column('id', String(length=20), table=<user>, primary_key=True, nullable=False), Column('name', String(length=20), table=<user>), Column('age', Integer(), table=<user>), schema=None)

截至这时，仅仅是定义了数据结构，可以通过以下指令真正在数据库中创建表

Base.metadata.create_all(engine)

创建会话

在进行数据的增删改查时候，需要先创建一个会话，使得sqlalchemy的操作同步到数据库的表。

from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine

# 初始化数据库连接:
engine = create_engine('sqlite:///./mysql.db')
# 创建DBSession类型:
DBSession = sessionmaker(bind=engine)

增

# 实例化一个DBSession
session = DBSession()
# 新增一个用户
one_user = User(name='zhangsan', age=18)
session.add(one_user)
# 实例化一个DBSession
session = DBSession()

• 这里我们新增了一个用户，此时这个数据并没有被同步的数据库中，而是处于等待的状态。
• 只有执行了 commit() 方法后，才会真正在数据表中创建数据。
• 如果我们查询数据库，则首先刷新所有待处理信息，然后立即发出查询。

session.query(User).filter_by(name='zhangsan').first() 

• 如果数据不正确，可以通过以下命令回滚

session.rollback()

• 如果数据没问题，可以commit提交到真正的数据库。

session.commit()

• 操作完毕，关闭连接

# 关闭session:
session.close()

查

在查询之前，我们先新建多几个数据，可以用以下方法批量新建数据。

session = DBSession()
user1 = User(name='lisi', age=45)
user2 = User(name='wangwu', age=34)
user3 = User(name='qianliu', age=50)
user4 = User(name='sunqi', age=30)
session.add_all([user1,user2,user3,user4])
session.commit()
session.close()

通过query方法进行查询。

session = DBSession()

print('升序')
for instance in session.query(User).order_by(User.age):
    print(instance.id,instance.name, instance.age)
    
print('降序')
for instance in session.query(User).order_by(User.age.desc()):
    print(instance.id,instance.name, instance.age)
    
session.close()

• query.filter() 过滤，需要加上class名称，如：User.id
• query.filter_by() 根据关键字过滤，如：id
• query.all() 返回列表形式的数据
• query.first() 返回第一个元素
• query.one() 有且只有一个元素时才正确返回
• query.one_or_none()，类似one，但如果没有找到结果，则不会引发错误
• query.scalar()，调用one方法，并在成功时返回行的第一列
• query.count() 计数
• query.order_by() 排序

连接查询

在进行连接查询前，需要重新定义数据结构。这里用到了一对多的模型，一个User对应多个Book。

# 导入:
from sqlalchemy import Column, String, Integer, ForeignKey
from sqlalchemy.orm import declarative_base,relationship

# 创建对象的基类:
Base = declarative_base()

# 定义User对象:
class User(Base):
    # 表的名字:
    __tablename__ = 'user'

    # 表的结构:
    id = Column(Integer, primary_key=True)
    name = Column(String(20))
    age = Column(Integer)
    # 一对多:
    books = relationship('Book')
    
# 定义Book对象:
class Book(Base):
    # 表的名字:
    __tablename__ = 'book'

    # 表的结构:
    id = Column(Integer, primary_key=True)
    name = Column(String(20))
    # “多”的一方book表是通过外键关联到user表的:
    user_id = Column(Integer, ForeignKey('user.id'))
    
# 初始化数据库连接:
engine = create_engine('sqlite:///./mysql.db')

# 创建表
Base.metadata.create_all(engine)

新建book数据

session = DBSession()
one_book = Book(name='天龙八部', user_id=1)
session.add(one_book)
session.commit()
session.close()

query.join() 连接查询

session = DBSession()
books=session.query(Book).join(User).filter(User.name=='zhangsan').all()
session.close()

for book in books:
    print(type(book.user_id))

改

• 查询

session = DBSession()
user=session.get(User,2)
print(user.name)
session.close()

• 修改

session = DBSession()
user=session.get(User,2)
user.name='lishi1'
session.commit()
session.close()

• 查询是否更改成功

session = DBSession()
user=session.get(User,2)
print(user.name)
session.close()

删

• 查询

session = DBSession()
user=session.get(User,2)
print(user.name)
session.close()

• 删除

session = DBSession()
user=session.get(User,2)
session.delete(user)
session.commit()
session.close()

• 查询是否删除成功

session = DBSession()
user=session.get(User,2)
print(user)
session.close()

一对多关系

# 导入:
from sqlalchemy import Column, String, Integer, ForeignKey
from sqlalchemy.orm import declarative_base,relationship

# 创建对象的基类:
Base = declarative_base()

# 定义User对象:
class User(Base):
    # 表的名字:
    __tablename__ = 'user'

    # 表的结构:
    id = Column(Integer, primary_key=True)
    name = Column(String(20))
    age = Column(Integer)
    # 一对多:
    books = relationship('Book',backref='user') # 记得用backref关联
    
# 定义Book对象:
class Book(Base):
    # 表的名字:
    __tablename__ = 'book'

    # 表的结构:
    id = Column(Integer, primary_key=True)
    name = Column(String(20))
    # “多”的一方book表是通过外键关联到user表的:
    user_id = Column(Integer, ForeignKey('user.id'))
    
# 初始化数据库连接:
engine = create_engine('sqlite:///./mysql.db')

# 创建表
Base.metadata.create_all(engine)

多对多关系

假设有A、B表要多对多，实质上是A --> 中间表（一对多），B --> 中间表（一对多），最终实现A、B两表的多对多关系。

• 定义数据结构

# 导入:
from sqlalchemy import Column, String, Integer, ForeignKey, Table
from sqlalchemy.orm import declarative_base,relationship
from sqlalchemy import create_engine

# 创建对象的基类:
Base = declarative_base()

# 定义中间表
teacher_student = Table(
    "teacher_student",
    Base.metadata,
    Column("teacher_id", Integer, ForeignKey("teacher.id"), nullable=False, primary_key=True),
    Column("student_id", Integer, ForeignKey("student.id"), nullable=False, primary_key=True)
)

# 定义User对象:
class Student(Base):
    # 表的名字:
    __tablename__ = 'student'

    # 表的结构:
    id = Column(Integer, primary_key=True)
    name = Column(String(20))
    # 多对多:
    teachers = relationship('Teacher',secondary=teacher_student)
    
# 定义Book对象:
class Teacher(Base):
    # 表的名字:
    __tablename__ = 'teacher'

    # 表的结构:
    id = Column(Integer, primary_key=True)
    name = Column(String(20))
    # 多对多:
    students = relationship('Student',secondary=teacher_student)
    
# 初始化数据库连接:
engine = create_engine('sqlite:///./mysql.db')

# 创建表
Base.metadata.create_all(engine)

• 增加数据

# 创建三个老师
teacher1 = Teacher(name='陈老师')
teacher2 = Teacher(name='李老师')
teacher3 = Teacher(name='王老师')

# 创建三个学生
student1 = Student(name="张三")
student2 = Student(name="李四")
student3 = Student(name="王五")

# 添加关联关系
teacher1.students = [student1,student2]
teacher2.students = [student1,student3]
teacher3.students = [student2,student3]

session = DBSession()
session.add_all([teacher1,teacher2,teacher3])
session.commit()
session.close()

• 查询

session = DBSession()
student = session.get(Student,1)
print(student.name)
print([teacher.name for teacher in student.teachers])
session.close()