SQLAlchemy ORM

一、安装

pip3 install pymysql
pip3 install sqlalchemy

二、使用

1、简单使用

from sqlalchemy import create_engine
engine = create_engine('mysql+pymysql://root:123456@127.0.0.1/db1?charset=utf8')
#按照位置传值
cursor = engine.execute("insert into t1 values(%s,%s);",[(1,'egon'),(1,'egon'), 
(1,'egon')])
#按关键字传值
cursor = engine.execute('insert into t1 values(%(id)s,%(name)s);',name='egon',id=4)
print(cursor.lastrowid) #获取最后的自增长ID
cur = engine.execute('select * from t1')
cur.fetchone() #获取一行
cur.fetchmany(2) #获取多行
cur.fetchall() #获取所有行

2、创建表 和 增删改查

#类===>表
#对象==>表中的一行记录

# -*- coding: utf-8 -*-
import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, Table, MetaData, ForeignKey, func,DATE, Enum
from sqlalchemy.orm import mapper, sessionmaker

engine = create_engine("mysql+pymysql://root:123456@10.0.0.15/db2?charset=utf8", echo=True)

#method 1(create table) usually choose method 1
Base = declarative_base() #generate the ORM Base Class

class User(Base):
    __tablename__ = 'user' # table name
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(32))
    password = Column(String(64))
    register_date = Column(DATE, nullable=False)
    gender = Column(Enum('F','M'), nullable=False)

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

# Base.metadata.create_all(engine) #create a table to engine(mysql)

#method 2(create table)
# metadata = MetaData()
#
# user = Table('user', metadata,
#              Column('id', Integer, primary_key=True, autoincrement=True),
#              Column('name', String(32)),
#              Column('fullname', String(50)),
#              Column('password', String(60))
#              )
#
# class User(object):
#     def __init__(self, name, fullname, password):
#         self.name = name
#         self.fullname = fullname
#         self.password = password
#
# mapper(User, user)

# insert data
Session_class = sessionmaker(bind=engine) # create a session class to mysql,and this is a class
session = Session_class() # instantiation

user_obj = User(name='alex', password=123)
print(user_obj.id, user_obj.name, user_obj.password) #now the object is not created yet,print the id is None

session.add(user_obj) # Add the data object you want to create to this session,and create it later
print(user_obj.id, user_obj.name, user_obj.password) #It's still not created.

session.commit() #This is the only way to submit and create data

# select data
my_user_obj = session.query(User).filter_by(name='alex').first()#where
print(my_user_obj)
print(my_user_obj.id, my_user_obj.name, my_user_obj.password)
print(session.query(User.name,User.id).all() )#select all
print(session.query(User).filter(User.id.isnot()).filter(User.id<6).all()) #multi where
print(session.query(User).filter(User.name.like("al%")).count()) #count
print(session.query(func.count(User.name),User.name).group_by(User.name).all()) #func.count and group by
# update data
my_user = session.query(User).filter_by(name="alex").first()
my_user.name = "Alex Li"
session.commit()
# rollback
my_user = session.query(User).filter_by(id=1).first()
my_user.name = "Jack"
fake_user = User(name='fake', password='123')
session.add(fake_user) # insert this User obj
print(session.query(User).filter(User.name.in_(['fake','fake1'])).all()) # fake is exist
session.rollback()
print(session.query(User).filter(User.name.in_(['fake','fake1'])).all()) # fake is not exist
session.commit()

三、外键

# -*- coding: utf-8 -*-
import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, Table, MetaData, ForeignKey, func,DATE, Enum
from sqlalchemy.orm import mapper, sessionmaker, relationship

engine = create_engine("mysql+pymysql://root:123456@10.0.0.15/testdb1?charset=utf8", echo=True)

Base = declarative_base() #generate the ORM Base Class
class Student(Base):
    __tablename__ = 'student'
    sid = Column(Integer, primary_key=True, autoincrement=True)
    sname = Column(String(32), nullable=False)
    gender = Column(Enum('F','M'), nullable=False)
    register_date = Column(DATE, nullable=False)
    def __repr__(self):
        return "<Student sid:%s sname:%s gender:%s register_date:%s>"%(self.sid, self.sname, self.gender, self.register_date)
class Course(Base):
    __tablename__ = 'course'
    cid = Column(Integer, primary_key=True, autoincrement=True)
    cname = Column(String(32), nullable=False)
    def __repr__(self):
        return "<Course cid:%s cname:%s>"%(self.cid, self.cname)
class Score(Base):
    __tablename__ = 'score'
    sid = Column(Integer, primary_key=True, autoincrement=True)
    num = Column(Integer, nullable=False)
    student_id = Column(Integer, ForeignKey('student.sid'), nullable=False)
    course_id = Column(Integer, ForeignKey('course.cid'), nullable=False)

    student = relationship('Student', backref='score') #可以获取外键关联的表记录
    course = relationship('Course', backref='score') #可以获取外键关联的表记录
    def __repr__(self):
        return "<Score sid:%s student_name:%s course_name:%s num:%s>"%(self.sid, self.student.sname, self.course.cname, self.num)

# Base.metadata.create_all(engine)

Session_class = sessionmaker(bind=engine)
session = Session_class()

course_obj1 = Course(cname='python')
course_obj2 = Course(cname='java')

student_obj1 = Student(sname='ton_py', gender='M', register_date='20170101')
student_obj2 = Student(sname='ton_java', gender='F', register_date='20170102')
student_obj3 = Student(sname='ton_pj', gender='F', register_date='20170103')

# session.add_all([course_obj1, course_obj2, student_obj1, student_obj2, student_obj3])

score_obj1 = Score(num=99, student_id=1, course_id=3)
score_obj2 = Score(num=96, student_id=2, course_id=4)
score_obj3 = Score(num=95, student_id=3, course_id=3)
score_obj4 = Score(num=94, student_id=3, course_id=4)

# session.add_all([score_obj1, score_obj2, score_obj3, score_obj4])
# session.commit()

# print(session.query(Student.sname, Course.cname, Score.num).filter(Course.cid==Score.course_id).filter(Student.sid==Score.student_id).all()) #relationships select
# 注意：query 后面首个查询字段所属的表 默认代表 第一个连接表
# print(session.query(Student.sname, Course.cname, Score.num).join(Score).join(Course).all()) #join select

# relationship的使用
stu_obj_ton_pj = session.query(Student).filter(Student.sname=='ton_pj').first()
print(stu_obj_ton_pj.score) #多条记录返回一个列表

四、多外键

# -*- coding: utf-8 -*-
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, Table, MetaData, ForeignKey, func,DATE, Enum
from sqlalchemy.orm import mapper, sessionmaker, relationship

engine = create_engine("mysql+pymysql://root:123456@10.0.0.15/testdb1", encoding='utf-8', echo=True)

Base = declarative_base()

class Address(Base):
    __tablename__ = 'address'
    id = Column(Integer, primary_key=True, autoincrement=True)
    street = Column(String(32), nullable=False)
    city = Column(String(32), nullable=False)
    state = Column(String(32), nullable=False)
    def __repr__(self):
        return '<Address street:%s city:%s state:%s>'%(self.street, self.city, self.state)

class Customer(Base):
    __tablename__ = 'customer'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(32), nullable=False)
    billing_address_id = Column(Integer, ForeignKey('address.id'), nullable=False)
    shipping_address_id = Column(Integer, ForeignKey('address.id'), nullable=False)

    billing_address = relationship("Address", foreign_keys=[billing_address_id], backref='b_customer')
    shipping_address = relationship("Address", foreign_keys=[shipping_address_id], backref='s_customer')
    def __repr__(self):
        return '<Customer name:%s billing_address:%s shipping_address:%s>'%(self.name, self.billing_address, self.shipping_address)
# Base.metadata.create_all(engine)

# 注意！此时，插入数据会报错，因为当Address对象查找Customer对象时，sqlachemy不能分清Address.id对应Customer的外键字段是billing_address_id还是shipping_address_id
# 解决办法如下,这样sqlachemy就能分清哪个外键是对应哪个字段了
#     billing_address = relationship("Address", foreign_keys=[billing_address_id], backref='b_customer')
#     shipping_address = relationship("Address", foreign_keys=[shipping_address_id], backref='s_customer')

Session_class = sessionmaker(bind=engine)
session = Session_class()

addr1 = Address(street='sanyuanli', city='baiyunqu', state='guangzhou')
addr2 = Address(street='huaguanlu', city='tianhequ', state='guangzhou')
# session.add_all([addr1, addr2])
c1 = Customer(name='alex', billing_address_id=1, shipping_address_id=2)
c2 = Customer(name='egon', billing_address_id=2, shipping_address_id=2)
# session.add_all([c1, c2])
# session.commit()
# 顾客表查找对应地址表记录
# customer_egon = session.query(Customer).filter(Customer.name=='egon').first()
# customer_alex = session.query(Customer).filter(Customer.name=='alex').first()
# print(customer_egon.billing_address, )
# print(customer_alex.billing_address, )
# 通过地址表查找相应顾客表记录
addr_tianhe = session.query(Address).filter(Address.city=='baiyunqu').first()
print(addr_tianhe.b_customer)

五、多对多关联

# -*- coding: utf-8 -*-
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, Table, MetaData, ForeignKey, func,DATE, Enum
from sqlalchemy.orm import mapper, sessionmaker, relationship

engine = create_engine("mysql+pymysql://root:123456@10.0.0.15/testdb1?charset=utf8", encoding='utf-8', echo=True)

Base = declarative_base()

book_m2m_author = Table('book_m2m_author', Base.metadata,
                        Column('book_id', Integer, ForeignKey('book.id')),
                        Column('author_id', Integer, ForeignKey('author.id')),
                        )

class Book(Base):
    __tablename__ = 'book'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(32), nullable=False)

    authors = relationship('Author', secondary=book_m2m_author, backref='books')
    def __repr__(self):
        return "<Book name:%s>"%self.name

class Author(Base):
    __tablename__ = 'author'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(32), nullable=False)
    def __repr__(self):
        return "<Author name:%s>"%self.name

# Base.metadata.create_all(engine)

Session_class = sessionmaker(engine)
session = Session_class()

book_obj1 = Book(name='book1')
book_obj2 = Book(name='book2')
book_obj3 = Book(name='book3')
book_obj4 = Book(name='测试中文')

author_obj1 = Author(name='alex1')
author_obj2 = Author(name='alex2')
author_obj3 = Author(name='alex3')

book_obj1.authors = [author_obj1,author_obj2]
book_obj2.authors = [author_obj2,author_obj3]
book_obj3.authors = [author_obj1,author_obj2, author_obj3]

# session.add_all([book_obj1, book_obj2, book_obj3, author_obj1, author_obj2, author_obj3])
# session.add_all([book_obj4,])
# session.commit()

book3_obj = session.query(Book).filter(Book.name=='book3').first()
author3_obj = session.query(Author).filter(Author.name=='alex3').first()
print(book3_obj.authors)
# print(book3_obj.authors.append(author_obj1))
# print(author3_obj.books)

#注意：删除数据时不用管book_m2m_author，sqlalchemy会自动帮你把对应的数据删除
# book3_obj.authors.remove(author3_obj)
# session.commit()

# 删除作者时，会把这个作者跟所有书的关联关系数据也自动删除
# session.delete(author3_obj)
# session.commit()