Python编程:orm之sqlalchemy模块
【摘要】 orm英文全称object relational mapping,对象映射关系 http://www.sqlalchemy.org/
常用操作
"""
MySQL-Python mysql+mysqldb://<user>:<password>@<host>[:<port>]/<dbname>
pymy...
orm英文全称object relational mapping,对象映射关系
http://www.sqlalchemy.org/
常用操作
"""
MySQL-Python mysql+mysqldb://<user>:<password>@<host>[:<port>]/<dbname>
pymysql mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>]
MySQL-Connector mysql+mysqlconnector://<user>:<password>@<host>[:<port>]/<dbname>
cx_Oracle oracle+cx_oracle://user:pass@host:port/dbname[?key=value&key=value...]
SQLite driver://user:pass@host/database
"""
import sqlalchemy # 第三方库,需要安装
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import sessionmaker
from sqlalchemy import func
# 创建表结构
engine = create_engine("mysql+pymysql://root:123456@127.0.0.1/test", encoding="utf8", echo=False)
Base = declarative_base() #声明基类
class User(Base): __tablename__ = "user" id = Column(Integer, primary_key=True) name = Column(String(32)) # varchar(32) password = Column(String(64)) def __repr__(self): return "<%s name: %s >" %(self.id, self.name)
# Base.metadata.create_all(engine) # 创建表结构
# 创建一条数据
Session_class = sessionmaker(bind=engine) # 创建会话类
session = Session_class() # 实例化
# user_obj = User(name="Tom", password="123456") # 生成数据对象
# print(user_obj.name, user_obj.id) # id=None
# 添加数据
# session.add(user_obj)
# print(user_obj.name, user_obj.id)
#
# session.commit() # 提交事务
# 查询
data = session.query(User).filter(User.id==2).first() # 或者all()
print(data)
# 修改
data.name = "Alex"
data.password = "abc"
session.commit()
# 多条件查询
data = session.query(User).filter(User.id>2).filter(User.name =="alex").all() # 或者all()
print(data)
# 回滚
user1= User(name="xiaobai", password="xxx")
session.add(user1)
data1 = session.query(User).filter(User.name == "xiaobai").all()
print(data1)
session.rollback()
data2 = session.query(User).filter(User.name == "xiaobai").all()
print(data2)
# 统计
count = session.query(User).filter(User.name.like("a%")).count()
print(count)
# 分组
data = session.query(User.name, func.count(User.name)).group_by(User.name).all()
print(data)
- 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
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
外键关联
import sqlalchemy # 第三方库,需要安装
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, DATE, ForeignKey
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy import func
# 创建表结构
engine = create_engine("mysql+pymysql://root:123456@127.0.0.1/test", encoding="utf-8", echo=False)
Base = declarative_base() #声明基类
class Student(Base): __tablename__ = "student" id = Column(Integer, primary_key=True) name = Column(String(32), nullable=False) register_date = Column(DATE, nullable=False) def __repr__(self): return "<%s name: %s >" %(self.id, self.name)
class study_record(Base): __tablename__ = "study_record" id = Column(Integer, primary_key=True) day = Column(Integer, nullable=False) status = Column(String(32), nullable=False) stu_id = Column(Integer, ForeignKey("student.id")) # 允许在study_record表中,通过backref字段反向查出student的关联项 student = relationship("student", backref="study_record")
Base.metadata.create_all(engine)
- 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
一对多关系
# 如果一个User拥有多个Book,就可以定义一对多关系
class User(Base): __tablename__ = 'user' id = Column(String(20), primary_key=True) name = Column(String(20)) # 一对多: books = relationship('Book')
class Book(Base): __tablename__ = 'book' id = Column(String(20), primary_key=True) name = Column(String(20)) # “多”的一方的book表是通过外键关联到user表的: user_id = Column(String(20), ForeignKey('user.id'))
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
创建多外键表结构
# orm_mfk_api.py
# 为使用者提供统一的数据结构接口
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, DATE, ForeignKey
from sqlalchemy.orm import sessionmaker, relationship
Base = declarative_base()
class User(Base): __tablename__ = "user" id = Column(Integer, primary_key=True) name = Column(String(32), nullable=False) bill_address_id = Column(Integer, ForeignKey("address.id")) ship_address_id = Column(Integer, ForeignKey("address.id")) # 一对多: bill_address = relationship("Address", foreign_keys=[bill_address_id]) ship_address = relationship("Address", foreign_keys=[ship_address_id]) def __repr__(self): return "id: %s, name: %s, bill: %s, ship: %s" %( self.id, self.name, self.bill_address_id, self.ship_address_id)
class Address(Base): __tablename__ = "address" id = Column(Integer, primary_key=True) city = Column(String(32), nullable=False)
# 设置引擎,创建表
engine = create_engine("mysql+pymysql://root:123456@127.0.0.1/test", encoding="utf-8", echo=False)
Base.metadata.create_all(engine)
- 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
# 调用多外键关联的api,对数据进行增删改查
from orm_mfk_api import engine, User, Address
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(engine)
session = Session()
# 插入地址
addr1 = Address(city="beijing")
addr2 = Address(city="wuhang")
addr3 = Address(city="lanzhou")
addr4 = Address(city="dali")
# session.add_all([addr1, addr2, addr3, addr4])
# 插入用户
user1 = User(name="Tom", bill_address=addr1, ship_address=addr2)
user2 = User(name="Jack", bill_address=addr1, ship_address=addr1)
user3 = User(name="Jimi", bill_address=addr3, ship_address=addr2)
# session.add_all([user1, user2, user3])
session.commit()
# 查询
result =session.query(User).filter(User.name=="Jimi").first()
print(result)
- 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
多对多关系
# orm_m2m_api.py
# 图书与作者
#多对多关系的统一接口
from sqlalchemy import Integer, String, Column, Table, DATE, ForeignKey
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
# 处理中文字符
engine = create_engine("mysql+pymysql://root:123456@127.0.0.1/test?charset=utf8")
Base = declarative_base()
# 创建关系表,第三张表连接book和author
book2author = Table("book2author", Base.metadata, Column("book_id", Integer, ForeignKey("books.id")), Column("author_id", Integer, ForeignKey("authors.id")) )
class Author(Base): __tablename__ = "authors" id = Column(Integer,primary_key=True, autoincrement=True) name = Column(String(32)) def __repr__(self): return self.name
class Book(Base): __tablename__ = "books" id = Column(Integer, primary_key=True, autoincrement=True) name = Column(String(64)) pub_date = Column(DATE) authors = relationship("Author", secondary=book2author, backref="books") def __repr__(self): return self.name
Base.metadata.create_all(engine)
- 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
# 调用多对多接口,管理作者与图书的数据
import orm_m2m_api
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(orm_m2m_api.engine)
session = Session()
# 作者
a1 = orm_m2m_api.Author(name="Tom")
a2 = orm_m2m_api.Author(name="Jack")
a3 = orm_m2m_api.Author(name="Jimi")
a4 = orm_m2m_api.Author(name="Ben")
# 图书
b1 = orm_m2m_api.Book(name="lear python", pub_date="2018-12-13")
b2 = orm_m2m_api.Book(name="lear java", pub_date="2018-12-14")
b3 = orm_m2m_api.Book(name="lear cpp", pub_date="2018-12-15")
b4 = orm_m2m_api.Book(name="中文书籍", pub_date="2018-12-15")
# 设置图书与作者的关系
b1.authors=[a1, a2]
b2.authors=[a1, a3]
b3.authors=[a4]
# 提交数据
# session.add_all([a1, a2, a3, a4, b1, b2, b3])
# session.commit()
# 书查询作者
result = session.query(orm_m2m_api.Book).filter(orm_m2m_api.Book.id==2).first()
print(result, result.authors) # lear java [Tom, Jimi]
# 作者查询书
result = session.query(orm_m2m_api.Author).filter(orm_m2m_api.Author.id==1).first()
print(result, result.books) # Tom [lear python, lear java]
# 删除书的作者
a5 = session.query(orm_m2m_api.Author).filter(orm_m2m_api.Author.id==1).first()
b5 = session.query(orm_m2m_api.Book).filter(orm_m2m_api.Book.id==1).first()
# b5.authors.remove(a5)
# session.commit()
# 删除作者
a6 = session.query(orm_m2m_api.Author).filter(orm_m2m_api.Author.id==4).first()
session.delete(a6)
session.commit()
- 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
- 45
- 46
- 47
- 48
- 49
完整示例代码:
《学生管理系统》
https://github.com/mouday/StudentManagerSys
文章来源: pengshiyu.blog.csdn.net,作者:彭世瑜,版权归原作者所有,如需转载,请联系作者。
原文链接:pengshiyu.blog.csdn.net/article/details/79249595
【版权声明】本文为华为云社区用户转载文章,如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱:
cloudbbs@huaweicloud.com
- 点赞
- 收藏
- 关注作者
评论(0)