1. 作用
ORM(Object-Relational Mapping) 对象关系映射
2. 操作
安装mysqlclient和sqlalchemy
sudo pip3 install mysqlclient sqlalchemy
操作数据库
# sqlalchemy
# orm对象关系映射
from sqlalchemy import Column, String, create_engine, ForeignKey
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy.ext.declarative import declarative_base
# 创建对象的基类
Base = declarative_base()
# 一个object对应一个table
class Teacher(Base):
__tablename__ = 'teacher' # 表名
# 表列
id = Column(String(20), primary_key=True)
name = Column(String(20))
students = relationship('Student')
class Student(Base):
__tablename__ = 'student'
# 表列
id = Column(String(20), primary_key=True)
name = Column(String(20))
teacher_id = Column(String(20), ForeignKey('teacher.id'))
# 初始化
# '数据库类型+数据库驱动名称：//用户名：密码@主机地址：端口号/数据库名'
engine = create_engine('mysql+mysqldb://mgt:1234567@localhost:3306/test')
# 绑定数据库，创建对话系统
DBSession = sessionmaker(bind=engine)
# 创建表
Base.metadata.create_all(engine)
# 创建一个对话
session = DBSession()
# 创建新的User对象
teach_one = Teacher(id='5', name='Bob')
teach_two = Teacher(id='2', name='Candy')
stude_one = Student(id='1', name='Tommy', teacher_id='5')
stude_two = Student(id='2', name='Maria', teacher_id='5')
stude_thr = Student(id='3', name='Andy', teacher_id='2')
stude_for = Student(id='4', name='Laura', teacher_id='2')
# 添加session
session.add(teach_one)
session.commit()
session.add(teach_two)
session.add(stude_one)
session.add(stude_two)
session.add(stude_thr)
session.add(stude_for)
# 提交即保存到数据库
session.commit()
# 查询数据
user = session.query(Teacher).filter(Teacher.id=='5').one()
print(user)
# 关闭session
session.close()
