Python之SQLAlchemy

sxyzy

SQLAlchemy的哲学
　　SQL数据库的行为不像对象集合的较具规模和业绩开始关系; 对象集合表现得不像越抽象开始关系表和行。 SQLAlchemy的目的是满足这两个原则。
　　SQLAlchemy认为数据库是关系代数发动机，而不仅仅是一个表的集合，行可以不仅从表中选择，但也加入和其他select语句; 任何这些单元可被组合成一个较大的结构，SQLAlchemy的表达式语言基础上，从它的核心这个概念。
　　SQLAlchemy是最有名的对象关系映射器（ORM），提供数据映射模式 ，其中类可以在开放式的，多种方式被映射到数据库中的可选组件-允许对象模型和数据库模式中，以开发干净地分离从开始方式。
　　SQLAlchemy的对这些问题的总体思路是大多数其它SQL/ORM工具，根植于所谓的complimentarity-导向的方式完全不同; 而不是藏起来了SQL和关系对象的细节自动化墙后面，所有的进程都充分一系列组合的，透明的工具中暴露出来 。 该库发生在自动冗余任务的工作，而开发商仍然在数据库中是如何组织和SQL是如何构造的控制。
　　SQLAlchemy的主要目标是改变你对数据库和SQL的方式！
SQLAlchemy的使用
　　数据库的连接
　　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>　　查看版本
>>> import sqlalchemy　　
>>> sqlalchemy.__version__
　　
'1.0.14'
创建与删除表
　　单表创建
#!/usr/bin/env python　　
# _*_ coding:utf-8 _*_
　　

　　
import sqlalchemyfrom sqlalchemy import create_enginefrom sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy import Column, Integer, String, Index, UniqueConstraint, ForeignKeyfrom sqlalchemy.orm import sessionmakerengine = create_engine('mysql+pymysql://root:as@127.0.0.1:3306/tesql?', echo=True)  # echo=True输出生成的SQL语句Base = declarative_base()  # 生成一个ORM基类class UserInfo(Base):
　　
    __tablename__ = 'UserInfo'  # 表名
　　
    """
　　
    创建字段
　　index=True  普通索引
　　unique=T  唯一索引
　　
    """
　　
    id = Column(Integer, primary_key=True, autoincrement=True)  # primary_key=主键,autoincrement=自增
　　
    name = Column(String(32))
　　
    password = Column(String(16))
　　
    __table_args__ = (
　　
        Index('id', 'name'),  # 联合索引
　　
        UniqueConstraint('name', 'password', name='name_password')  # 联合唯一索引,name索引的名字
　　
    )
　　
    # 让查询出来的数据显示中文
　　
    def __repr__(self):
　　
        return self.nameBase.metadata.create_all(engine)  # 把所有集成Base类的类，创建表结构
　　上面的代码其实就是创建了一个UserInfo表，包含了三个字段，实际执行的SQL语句如下：
CREATE TABLE `UserInfo` (　　id INTEGER NOT NULL AUTO_INCREMENT,
　　name VARCHAR(32),
　　password VARCHAR(16),
　　PRIMARY KEY (id),
　　CONSTRAINT name_password UNIQUE (name, password))
　　因为在创建引擎的时候加入了echo=True，所以执行的SQL会在控制台输出出来，以便于我们排查问题。
　　创建一对多表
class Favor(Base):　　
    __tablename__ = 'favor'
　　
    nid = Column(Integer, primary_key=True, autoincrement=True)
　　
    caption = Column(String(50), default='red', unique=True)class Person(Base):
　　
    __tablename__ = 'person'
　　
    nid = Column(Integer, primary_key=True, autoincrement=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'))
　　删除表
Base.metadata.drop_all(engine)  # 把所有集成Base类的类，删除表操作表
增加数据
　　添加单条数据
MySesion = sessionmaker(bind=engine)　　
session = MySesion()
　　

　　
# 创建一条数据
　　
users = UserInfo(name='Hello', password='World')
　　

　　
# 把数据添加到表内
　　
session.add(users)
　　

　　
# 提交生效
　　
session.commit()
　　添加多少数据
session.add_all([　　
    UserInfo(name='A', password='1'),
　　
    UserInfo(name='B', password='2')])# 提交session.commit()
删除数据
session.query(UserInfo).filter(UserInfo.name == 'a').delete()　　
session.commit()
查询
　　获取某个表中的所有内容
result = session.query(UserInfo).all()　　
print(result)
修改数据
session.query(UserInfo).filter(UserInfo.id == 8).update({"name": "ffff"})　　
session.commit()
查询数据
　　获取所有
result = session.query(UserInfo).all()　　获取指定字段
result = session.query(UserInfo.name, UserInfo.password).all()　　获取指定的
result = session.query(UserInfo).filter_by(name='b').all()# 返回的是一个列表　　获取第一条
result = session.query(UserInfo).filter_by(name='b').first()　　
# 获取值中的某个属性
　　
result.name
　　获取数据出现的个数
result = session.query(UserInfo).filter_by(name='b').count()　　使用and_和or_进行查询
　　导入and_, or_模块
from sqlalchemy import and_, or_　　and_
for row in session.query(UserInfo).filter(and_(UserInfo.name == 'A', UserInfo.password == 1)):　　
    print(row)
　　or_
for row in session.query(UserInfo).filter(or_(UserInfo.name == 'Hello', UserInfo.password == 1)):　　
    print(row)
　　关联查询
　　创建以下数据库
#!/usr/bin/env python# _*_ coding:utf-8 _*_from sqlalchemy import create_enginefrom sqlalchemy.orm import sessionmaker, relationshipsfrom sqlalchemy import Column, Integer, String, Index, UniqueConstraint, ForeignKeyfrom sqlalchemy.ext.declarative import declarative_baseengine = create_engine('mysql+pymysql://root:as@127.0.0.1:3306/tesql')Base = declarative_base()class Son(Base):　　
    __tablename__ = 'son'
　　
    id = Column(Integer, primary_key=True)
　　
    name = Column(String(32))
　　
    father = relationship('Father')
　　
    # 创建外键
　　
    father_id = Column(Integer, ForeignKey('father.id'))class Father(Base):
　　
    __tablename__ = 'father'
　　
    id = Column(Integer, primary_key=True)
　　
    name = Column(String(32))
　　
    son = relationship('Son')
　　# son = relationship('Son', backref='Father') 相当于上面两个relationship# 生成表Base.metadata.create_all(engine)
　　往表里添加数据
Session = sessionmaker(bind=engine)　　
session = Session()
　　

　　
# 添加父亲的数据F = Father(name='as')
　　
session.add(F)
　　
session.commit()
　　

　　
# 添加儿子的数据
　　
S1 = Son(name='Son1', father_id=1)
　　
S2 = Son(name='Son2', father_id=1)
　　
session.add_all([S1, S2])
　　
session.commit()
　　

　　
# 另外一种添加数据的方式
　　
F = session.query(Father).filter_by(id=1).first()
　　
S3 = Son(name='Son3')
　　
# 要用追加的方式进行添加，F.son是一个列表，如果不用append将会把之前的数据对应的值进行删除
　　
F.son.append(S3)
　　

　　
session.add(F)session.commit()
　　通过父亲找到所有的儿子
result = session.query(Father).filter_by(name='as').first()　　
for n in result.son:
　　
    print(n.name)
　　通过儿子找到父亲
result = session.query(Son).filter_by(name='Son2').first()　　
print(result.father.name, result.name)
　　
# son = relationship('Son', backref='Father')
　　
# print(result.father.name, result.name)
　　join
result = session.query(Father.name.label('kkk'), Son.name.label('ppp')).join(Son)　　
# label('kkk')相当于起了一个别名，等于sql中的as
　　
print(result)
　　
>>>>>
　　
SELECT father.name AS kkk, son.name AS ppp
　　
FROM father JOIN son ON father.id = son.father_id
　　多对多实例
　　在上面的多对多的代码中的Server类加入一下代码：
g = relationship("Group", secondary=ServerToGroup.__table__, backref='s')# seco　　
ndary 如果有第三张表自动加进来
　　然后生成数据库表.
　　添加组与主机的数据
G1 = Group(name='G1', port=22)　　
G2 = Group(name='G2', port=22)
　　
S1 = Server(hostname='Linux-node1')
　　
S2 = Server(hostname='Linux-node2')
　　
session.add_all([G1, G2, S1, S2])
　　
session.commit()
　　往第三张表里面添加关联数据
GS1 = ServerToGroup(server_id=1, group_id=1)　　
GS2 = ServerToGroup(server_id=2, group_id=2)
　　
session.add_all([GS1, GS2])
　　
session.commit()
　　通过relationship进行数据的添加
# 获取ID=1的主机　　
S = session.query(Server).filter_by(id=1).first()
　　
# 获取所有主机组G = session.query(Group).all()
　　

　　
S.g = G
　　
# 添加数据
　　
session.add_all([S, ])
　　
# 提交到数据库中
　　
session.commit()