安装
$ pip install sqlalchemy
初始化表
from sqlalchemy import Column, String, Integer, create_engine, ForeignKey
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy.ext.declarative import declarative_base
# 创建对象的基类
Base = declarative_base()
# 定义Student对象
class Student(Base):
# 表的结构
id = Column(Integer(), primary_key=True)
name = Column(String(20))
# 一对多
books = relationship("Book")
# 表的名字
__tablename__= "student"
# 书籍
class Book(Base):
id = Column(Integer(), primary_key=True)
name = Column(String(20))
# “多”的一方的book表是通过外键关联到Student表的
stu_id = Column(Integer(), ForeignKey("student.id"))
__tablename__ = "book"
# 初始化数据库连接，参数解决中文编码问题
engine = create_engine("mysql+pymysql://root:123456@127.0.0.1/demo?charset=utf8")
# 创建数据表
Base.metadata.create_all(engine)
# 创建DBSession类型
DBSession = sessionmaker(bind=engine)
# 实例化session对象
session = DBSession()增加
# 创建新Student对象 student1 = Student(id=1, name="小红") student2 = Student(id=2, name="小明") # 添加到session session.add(student1) session.add(student2) # 创建书籍 book1 = Book(id=1, name="语文", stu_id=1) book2 = Book(id=2, name="数学", stu_id=1) book3 = Book(id=3, name="英语", stu_id=2) session.add_all([book1, book2, book3]) # 提交即保存到数据库 session.commit()
查询
student = session.query(Student).filter(Student.id==1).first() print(student) # <__main__.Student object at 0x103c44400> print(student.id) # 1 print(student.name) # 小红 for book in student.books: print(book.name) # 语文 # 数学
修改
student = session.query(Student).filter(Student.id==1).first() student.name = "小白" session.commit()
删除
student = session.query(Student).filter(Student.id==2).first() session.delete(student) session.commit()
善后工作
# 关闭session session.close()
