安装

$ pip install sqlalchemy
  
 

  
1
 

初始化表

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()

  
 

  
1
  
2
  
3
  
4
  
5
  
6
  
7
  
8
  
9
  
10
  
11
  
12
  
13
  
14
  
15
  
16
  
17
  
18
  
19
  
20
  
21
  
22
  
23
  
24
  
25
  
26
  
27
  
28
  
29
  
30
  
31
  
32
  
33
  
34
  
35
  
36
  
37
  
38
  
39
  
40
  
41
  
42
  
43
  
44
 

增加

# 创建新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()
  
 

  
1
  
2
  
3
  
4
  
5
  
6
  
7
  
8
  
9
  
10
  
11
  
12
  
13
  
14
  
15
  
16
  
17
  
18
 

查询

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)
# 语文
# 数学
  
 

  
1
  
2
  
3
  
4
  
5
  
6
  
7
  
8
  
9
  
10
  
11
  
12
  
13
  
14
 

修改

student = session.query(Student).filter(Student.id==1).first()
student.name = "小白"

session.commit()
  
 

  
1
  
2
  
3
  
4
 

删除

student = session.query(Student).filter(Student.id==2).first()
session.delete(student)

session.commit()
  
 

  
1
  
2
  
3
  
4
 

善后工作

# 关闭session
session.close()
  
 

  
1
  
2
 

相关文章： Python编程：orm之sqlalchemy模块

文章来源: pengshiyu.blog.csdn.net，作者：彭世瑜，版权归原作者所有，如需转载，请联系作者。

原文链接：pengshiyu.blog.csdn.net/article/details/80733879