sqlalchemy

一、SQLAlchemy

SQLAlchemy是Python编程语言下的一款ORM框架，该框架建立在数据库API之上，使用关系对象映射进行数据库操作，简言之便是：将对象转换成SQL，然后使用数据API执行SQL并获取执行结果。

安装

pip3 install SQLAlchemy

SQLAlchemy本身无法操作数据库，其必须以来pymsql等第三方插件，Dialect用于和数据API进行交流，根据配置文件的不同调用不同的数据库API，从而实现对数据库的操作，如：

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...]

 
更多详见：http://docs.sqlalchemy.org/en/latest/dialects/index.html 

创建表

#!/usr/bin/env python
# -*- coding:utf-8 -*-
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,Integer,String,ForeignKey,UniqueConstraint,Index
from sqlalchemy.orm import sessionmaker,relationship
from sqlalchemy import create_engine
engine = create_engine('mysql+pymysql://root:534198lu@127.0.0.1:3306/com',echo = True)  # echo = True会把执行的过程显示出来
Base = declarative_base()  #生成一个SQLORM基类
class Son(Base):  #必须继承基类
    __tablename__ = 'son'  # 表名
    id = Column(Integer, primary_key=True) #各列的数据
    name = Column(String(32))
    age = Column(String(16))

Base.metadata.create_all(engine)  # 创建表建构

• 增

# #这两行触发sessionmaker类下的__call__方法，return得到 Session实例，赋给变量session，所以session可以调用Session类下的add，add_all等方法
MySession = sessionmaker(bind=engine)
session = MySession()    #用MySession对象进行基本的操作
ed_user1 = User(name='xiaowang', fullname='Xiao Liu', password='111')
ed_user2 = User(name='xiaoming', fullname='Xiao Liu', password='222')
print(ed_user1,ed_user2)  #打印出来的是俩行数据的内存地址
#session.add(ed_user1) #用于添加单行数据
session.add_all([ed_user1,ed_user2])   #用于增加多行数据【第一行数据，第二行数据，......】
session.commit()    #添加数据后必须提交

• 删

session.query(User).filter(User.id ==1).delete()
session.commit()

• 改

session.query(Users).filter(Users.id > 2).update({"name" : "099"})
session.query(Users).filter(Users.id > 2).update({Users.name: Users.name + "099"}, synchronize_session=False)
session.query(Users).filter(Users.id > 2).update({"num": Users.num + 1}, synchronize_session="evaluate")
session.commit()

• 查

#获取所有数据,返回的是多条数据组成的列表
print(session.query(User).all())
#获取第一条数据，返回的是一条数据
print(session.query(User).first())
#limit ,相当于切片
print(session.query(User).all()[1:3])
#order_by 排序
print(session.query(User).order_by(-User.id).all())
#可以指定查询，all()返回的是列表套元祖，first()返回的只有元祖
print(session.query(User.name,User.id).all())
#获取某一列数据,类似于django的get,如果返回数据为多个则报错
print(session.query(User).filter(User.id==5).one())
#过滤数据,filter()括号内跟的是条件
print(session.query(User).filter(User.name == 'xiaowang').first())
#过滤数据,filter_by()括号内跟的是键值对
print(session.query(User).filter_by(name = 'xiaowang').first())

• 其他类型

#　条件
ret = session.query(Users).filter_by(name='alex').all()
ret = session.query(Users).filter(Users.id > 1, Users.name == 'eric').all()
ret = session.query(Users).filter(Users.id.between(1, 3), Users.name == 'eric').all()
ret = session.query(Users).filter(Users.id.in_([1,3,4])).all()
ret = session.query(Users).filter(~Users.id.in_([1,3,4])).all()
ret = session.query(Users).filter(Users.id.in_(session.query(Users.id).filter_by(name='eric'))).all()
from sqlalchemy import and_, or_
ret = session.query(Users).filter(and_(Users.id > 3, Users.name == 'eric')).all()
ret = session.query(Users).filter(or_(Users.id < 2, Users.name == 'eric')).all()
ret = session.query(Users).filter(
    or_(
        Users.id < 2,
        and_(Users.name == 'eric', Users.id > 3),
        Users.extra != ""
    )).all()


# 通配符
ret = session.query(Users).filter(Users.name.like('e%')).all()
ret = session.query(Users).filter(~Users.name.like('e%')).all()

# 限制
ret = session.query(Users)[1:2]

# 排序
ret = session.query(Users).order_by(Users.name.desc()).all()
ret = session.query(Users).order_by(Users.name.desc(), Users.id.asc()).all()

# 分组
from sqlalchemy.sql import func

ret = session.query(Users).group_by(Users.extra).all()
ret = session.query(
    func.max(Users.id),
    func.sum(Users.id),
    func.min(Users.id)).group_by(Users.name).all()

ret = session.query(
    func.max(Users.id),
    func.sum(Users.id),
    func.min(Users.id)).group_by(Users.name).having(func.min(Users.id) >2).all()

# 连表

ret = session.query(Users, Favor).filter(Users.id == Favor.nid).all()

ret = session.query(Person).join(Favor).all()

ret = session.query(Person).join(Favor, isouter=True).all()


# 组合
q1 = session.query(Users.name).filter(Users.id > 2)
q2 = session.query(Favor.caption).filter(Favor.nid < 2)
ret = q1.union(q2).all()

q1 = session.query(Users.name).filter(Users.id > 2)
q2 = session.query(Favor.caption).filter(Favor.nid < 2)
ret = q1.union_all(q2).all()

一对多，多对多表的创建

#!/usr/bin/env python
# -*- coding:utf-8 -*-
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy import create_engine
engine = create_engine('mysql+pymysql://root:534198lu@127.0.0.1:3306/com')#加echo = True显示执行过程
Base = declarative_base()
# 创建单表
class Users(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String(32))
    extra = Column(String(16))

    __table_args__ = (    #组合索引
    UniqueConstraint('id', 'name', name='uix_id_name'),
        Index( 'ix_id_name','name', 'extra'),
    )

# 一对多
class Favor(Base):
    __tablename__ = 'favor'
    nid = Column(Integer, primary_key=True)
    caption = Column(String(50), default='red', unique=True)
class Person(Base):
    __tablename__ = 'person'
    nid = Column(Integer, primary_key=True)
    name = Column(String(32), index=True, nullable=True)
    favor_id = Column(Integer, ForeignKey("favor.nid"))

# # 多对多
class Group(Base):
    __tablename__ = 'group'
    id = Column(Integer, primary_key=True)
    name = Column(String(64), unique=True, nullable=False)
    port = Column(Integer, default=22)

class Server(Base):
    __tablename__ = 'server'
    id = Column(Integer, primary_key=True, autoincrement=True)
    hostname = Column(String(64), unique=True, nullable=False)

class ServerToGroup(Base):
    __tablename__ = 'servertogroup'
    nid = Column(Integer, primary_key=True, autoincrement=True)
    server_id = Column(Integer, ForeignKey('server.id'))
    group_id = Column(Integer, ForeignKey('group.id'))

def init_db():
    Base.metadata.create_all(engine)

def drop_db():
  Base.metadata.drop_all(engine)

son = relationship('Son',backref='father')　　用于建立俩张表之间的关系

#!/usr/bin/env python
# -*- coding:utf-8 -*-
from sqlalchemy import create_engine
from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship

engine = create_engine('mysql+pymysql://root:534198lu@127.0.0.1:3306/com')#加echo = True显示执行过程
Base = declarative_base()


class Student(Base):
    __tablename__ = 'student'
    nid = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(20))
    cour= relationship('Course', backref='student')
    #建立俩者之间的关系，其中backref='student'相当于在下面的类中写studeng = relationship('Student')效果一样


class Course(Base):
    __tablename__ = 'course'
    nid = Column(Integer, primary_key=True, autoincrement=True)
    course = Column(String(20))
    student_id = Column(Integer, ForeignKey('student.nid'))
    # studeng = relationship('Student')

Session = sessionmaker(bind=engine)
session = Session()

Base.metadata.create_all(engine)
# Base.metadata.drop_all(engine)
#
s1 = Student(name = 'zane')
c1 = Course(course='wuli')
c2 = Course(course='huaxue')
c3 = Course(course='shuxue')
s1.cour = [c1,c2,c3]   #批量添加，相当于是c1，c2，c3 共同和s1有关系
session.add_all([s1,c1,c2,c3])
session.commit()

c1 = session.query(Course).filter(Course.student_id==6).first()
print(c1)
print(c1.student)   #通过c1.student就可以获取和c1有对应关系的Student表中所有的数据
print(c1.student.name)
# s1 = session.query(Student).filter(Student.name == 'zane').first()
# print(s1)
# print(s1.cour)
# print(s1.cour[0].course)
# print(s1.cour[1].course)

print( session.query(Course.course,Student.name).join(Student).all())------------用于关联表的查询

son = relationship('Son',backref='father')内部和join一样也是一个关联查询

s1.cour = [c1,c2,c3] 用于给子表重新建立对应关系，如果表里面已经有对应关系，这样做的话会把里面的对应冲掉，

如果是新添加数据，又需要建立对应关系的话用s1.cour.append(w3)

ForeignKey建在多的一方

多对多实例：（注意第三张表一定要放到最上面）

#!/usr/bin/env python
# -*- coding:utf-8 -*-
from sqlalchemy import create_engine
from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship

engine = create_engine('mysql+pymysql://root:534198lu@127.0.0.1:3306/com?charset=utf8')#加echo = True显示执行过程
Base = declarative_base()

class Man_to_Wemon(Base):
    __tablename__ = 'Men_to_Wemon'
    nid = Column(Integer, primary_key=True, autoincrement=True)
    man_id = Column(Integer,ForeignKey('man.id'))
    women_id= Column(Integer,ForeignKey('women.id'))

class Man(Base):
    __tablename__ = 'man'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(20))
    age = Column(String(16))

class Women(Base):
    __tablename__ = 'women'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(20))
    age = Column(String(16))
    bf = relationship('Man',secondary =  Man_to_Wemon.__table__,backref = 'gf')
#secondary =  Man_to_Wemon.__table__ 对应第三张表（使用与有第三张表的情况）

Base.metadata.create_all(engine)

Session = sessionmaker(bind=engine)
session = Session()

# m1 = Man(name = 'alex',age = '18')
# m2 = Man(name = 'tony',age = '18')
# w1 = Women(name = '如花',age = '18')
# w2 = Women(name = '铁锤',age = '18')
#普通的添加数据
# session.add_all([m1,m2,w1,w2])
# session.commit()
# t1 = Man_to_Wemon(man_id=1,women_id=2)
# session.add_all([t1])
# session.commit()


#利用relationship往关系表中添加数据
m1 = session.query(Man).filter_by(id=2).first()   #获取一个男人
w1 = session.query(Women).all()   #获取好几个妹纸
m1.gf = w1  #建立起一个男人对应好几个妹纸的一个对应关系
session.add(m1)
session.commit()

# #利用relationship往关系表中添加数据
m1 = session.query(Man).filter_by(id=1).first()   #获取一个男人
print(m1)
w1 = session.query(Women).filter_by(id=1).first()  #获取好几个妹纸
print(w1)
m1.gf =[w1]  #建立起一个男人对应好几个妹纸的一个对应关系
print(m1.gf)
session.add(m1)
session.commit()

 注意：

m1.gf =[w1] 中如果m1.gf以前有值的时候，再m1.gf =[w1]这样的话就会把其中的内容从新赋值，把以前的对应关系替换掉

m1.gf =[w1] 中如果等号后面的w1获取的就是一个列表的时候直接写m1.gf =w1就可以了，如果w1只是一个单独的值的话就应该放到列表中