Python之SQLAchemy

1.ORM介绍

  ORM（object relational mapping）,对象映射关系程序。
  对python而言一切皆对象，数据库却是关系型的，保证使用习惯，通过orm将编程语言的对象模型与数据库的关系模型建立起映射关系。
  python中，最知名的ORM框架就是SQLAchemy。

2.外键关联的表

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:Aa663366***@localhost/lxl_db",
                       encoding='utf-8',echo=False)
                       
#生成orm基类
Base = declarative_base()

class User(Base):
    __tablename__ = 'user'        #表名
    id = Column(Integer,primary_key=True)
    name = Column(String(32))
    password = Column(String(64))
    
    #python 每个类总都包含一个__repr__()方法，当输出讴歌实例化队形的时候，就会     
    #调用该对象的__repr__()方法，输出该方式的返回值
    def __repr__(self):
        return "<%s:%s>"%(self.id,self.name)
 
#创建表结构
Base.metadata.create_all(engine)

#创建与数据库的会话session class，注意，这里返回给session的是一个#class，不是实例
Session_calss = sessionmaker(bind=engine)

#生成session实例，类似于cursor
Session = Session_calss()

#生成你要创建的数据对象
user_obj = User(name='alex',password="123456")

Session.add(user_obj)

#commit之后才会创建数据
Session.commit()

查询数据与删除数据

#查询
#filter查询的是一组语句，需要加上.all(),
#res = session.query(user_l2).filter_by(id=2)此时#res结果为：
#SELECT user_l2.id AS user_l2_id, user_l2.name AS user_l2_name, #user_l2.password AS user_l2_password 
#FROM user_l2 
#WHERE user_l2.id = %(id_1)s

#filter查询的是一个列表，是一组数据
res = session.query(user_l2).filter_by(id=2).all()
data = session.query(user_l2).filter(user_l2.id>1).all()
print(res,data)
res2=session.query(user_l2.name , func.count(user_l2.name)).group_by(user_l2.name).all()
print(res2)

#修改
res = session.query(user_l2).filter_by(id=2).all()
res[0].name="heiheihei"
res[0].password="buzhidao"
print(session.query(user_l2).filter_by(id=2).all())
session.commit()

#删除
res = session.query(user_l2).filter_by(id=2).first()
session.delete(res)
session.commit()

3.关联外键的表 1v1

import sqlalchemy
from sqlalchemy.engine import create_engine
from sqlalchemy import Integer, String, Column, DATE, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship

engine = create_engine("mysql+pymysql://root:Aa663366***@localhost/lxl_db", encoding='utf-8', echo=False)
Base = declarative_base()


class Student(Base):
    __tablename__ = "student4"
    id = Column(Integer, autoincrement=True, primary_key=True)
    name = Column(String(32), nullable=False)
    register_date = Column(DATE, nullable=False)

    def __repr__(self):
        return "<id:%s name:%s register_date:%s>" % (self.id, self.name, self.register_date)


class Study_record(Base):
    __tablename__ = "study_record4"
    id = Column(Integer, autoincrement=True, primary_key=True)
    #外键关联
    stu_id = Column(Integer, ForeignKey('student4.id'))
    day = Column(Integer, nullable=False)
    status = Column(String(32))
    #查询出的数据可以通过students查询student4表中的内容，student4查询出的数据可   
    #通过my_study_record反向查询study_record4中的内容
    students = relationship('Student', backref="my_study_record")

    def __repr__(self):
        return "<id:%s day:%s status:%s>" % (self.stu_id, self.day, self.status)

stu_1=Student(name="Num One",register_date="2021-1-20")
stu_2=Student(name="Num Two",register_date="2021-2-20")
stu_3=Student(name="Num Three",register_date="2020-1-20")
stu_4=Student(name="Num Four",register_date="2019-5-20")

study_1=Study_record(stu_id=1,day=1,status="Yes")
study_2=Study_record(stu_id=1,day=2,status="No")
study_3=Study_record(stu_id=1,day=3,status="Yes")
study_4=Study_record(stu_id=2,day=1,status="Yes")

Base.metadata.create_all(engine)

Session_class = sessionmaker(bind=engine)
session = Session_class()
session.add_all([stu_1,stu_2,stu_3,stu_4,study_1,study_2,study_3,study_4])
session.commit()

data = session.query(Student).filter(Student.id == 1).first()
print(data.my_study_record[0].id)

4.关联外键的表 1v多
/day12/orm_basic_fk2.py

import sqlalchemy
from sqlalchemy import create_engine, Integer, String, Column, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship

Base = declarative_base()


class Consumer(Base):
    __tablename__ = 'consumer'
    id = Column(Integer, autoincrement=True, primary_key=True)
    name = Column(String(32))

    billing_address_id = Column(Integer, ForeignKey('address.id'))
    shipping_addresses_id = Column(Integer, ForeignKey('address.id'))
    #多外键关联需要表明反向查询对应的具体字段，故需要加上foreign_keys
    billing_addresses = relationship('Address',foreign_keys=[billing_address_id])
    shipping_addresses = relationship('Address',foreign_keys=[shipping_addresses_id],backref="consume_add")
    def __repr__(self):
        return "<name:%s bill_addr:%s ship_addr:%s>"%(self.name,self.billing_addresses,self.shipping_addresses)

class Address(Base):
    __tablename__ = 'address'
    id = Column(Integer, autoincrement=True, primary_key=True)
    city = Column(String(64))
    def __repr__(self):
        return self.city


engine = create_engine("mysql+pymysql://root:Aa663366***@localhost/lxl_db", echo=False)
Base.metadata.create_all(engine)

/day12/orm_basic_fk2_addmem.py

import sqlalchemy
from sqlalchemy.orm import sessionmaker
from day12 import  orm_basic_fk2

session_class = sessionmaker(bind=orm_basic_fk2.engine)
session=session_class()

add_1=orm_basic_fk2.Address(city="HeFei")
add_2=orm_basic_fk2.Address(city="ChuZhou")
add_3=orm_basic_fk2.Address(city="NanJing")
add_4=orm_basic_fk2.Address(city="HuaiAn")
session.add_all([add_1,add_2,add_3,add_4])

cons_1=orm_basic_fk2.Consumer(name="zhangsan",billing_address_id=1,shipping_addresses_id=2)
cons_2=orm_basic_fk2.Consumer(name="lisi",billing_addresses=add_3,shipping_addresses=add_4)
cons_3=orm_basic_fk2.Consumer(name="wangwu",billing_addresses=add_3,shipping_addresses=add_3)

session.add_all([cons_2,cons_3])
session.commit()

/day12/orm_basic_fk2_select.py

from day12 import orm_basic_fk2,orm_basic_fk2_addmem

con_data=orm_basic_fk2_addmem.session.query(orm_basic_fk2.Consumer).filter(orm_basic_fk2.Consumer.id==4).first()
print(con_data)
add_data= orm_basic_fk2_addmem.session.query(orm_basic_fk2.Address).filter(orm_basic_fk2.Address.id==9).first()
print(add_data.consume_add[0].name)

5.关联外键的表 多v多
/day12/orm_basic_m2m.py

import sqlalchemy
from sqlalchemy import create_engine,Integer,String,Column,ForeignKey,DATE,Table
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship,sessionmaker

Base = declarative_base()

#关系表，由直接维护
book_m2m_author=Table('book_2m2_author',Base.metadata,
                      Column('book_id',Integer,ForeignKey('books.id')),
                      Column('author_id',Integer,ForeignKey('authors.id'))
                      )

class Authors(Base):
    __tablename__="authors"
    id=Column(Integer,autoincrement=True,primary_key=True)
    name=Column(String(32))
    def __repr__(self):
        return "author_name:%s"%self.name


class Books(Base):
    __tablename__ = "books"
    id = Column(Integer, autoincrement=True, primary_key=True)
    book_name = Column(String(64))
    pub_date=Column(DATE)
    #通过第三张表关联找到关联的对象secondary=book_m2m_author
    b_authors=relationship("Authors",secondary=book_m2m_author,backref='a_books')
    def __repr__(self):
        return "book_name:%s pub_date:%s"%(self.book_name,self.pub_date)


#想输入中文，需要在engine中写入?charset=utf8
engine = create_engine("mysql+pymysql://root:Aa663366***@localhost/lxl_db?charset=utf8", echo=False)
Base.metadata.create_all(engine)

/day12/orm_basic_m2m_api.py

from day12 import orm_basic_m2m
import sqlalchemy
from sqlalchemy.orm import relationship, sessionmaker

session_class = sessionmaker(bind=orm_basic_m2m.engine)
session = session_class()

# 添加内容

b1=orm_basic_m2m.Books(book_name="Learn Python",pub_date="2020-10-10")
b2=orm_basic_m2m.Books(book_name="Learn Linux",pub_date="2021-01-01")
b3=orm_basic_m2m.Books(book_name="Learn Mysql",pub_date="2021-02-10")
b4=orm_basic_m2m.Books(book_name="Learn C",pub_date="2021-05-01")

a1=orm_basic_m2m.Authors(name="ZhangSan")
a2=orm_basic_m2m.Authors(name="LiSi")
a3=orm_basic_m2m.Authors(name="WangWu")
a4=orm_basic_m2m.Authors(name="ZhaoLiu")

#关联关系
b1.b_authors=[a1,a2,a3]
b2.b_authors=[a2,a3,a4]
b3.b_authors=[a2,a4]
b4.b_authors=[a1]

session.add_all([b1,b2,b3,b4,a1,a2,a3,a4])
session.commit()
#直接删除作者，会同步删除book_m2m_author中的关联关系
res=session.query(orm_basic_m2m.Authors).filter(orm_basic_m2m.Authors.id==2).first()
session.delete(res)
session.commit()
print(res,res.a_books)

#通过书本仅删除作者，仅修改关系
res=session.query(orm_basic_m2m.Books).filter(orm_basic_m2m.Books.id==3).first()
res.b_authors.remove(res.b_authors[0])
session.commit()
print(res,res.b_authors)