安装
$ 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()
