Python 学习笔记 - SQLAlchemy(上)

上一篇简单的介绍了MySQL的原生命令，以及如何使用pymysql模块在Python里面使用。这一节简单的介绍一下sqlAlchemy和他的ORM框架。ORM框架允许开发人员通过类的方法来操作数据库，而无需在使用原生的SQL语句。

例1. 单表的操作

#导入模块
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index
from sqlalchemy.orm import sessionmaker, relationship

#创建一个engine，基本格式是数据库类型名字+驱动：//用户名：密码@主机：端口/数据库
engine = create_engine("mysql+pymysql://yli:yli@sydnagios:3306/mydb", max_overflow=5)

#声明一个基类
Base = declarative_base()

# 定义一个类，继承基类
class Test(Base):
    #表名
    __tablename__ = 'test'
    #列名nid，整型，主键，自增
    nid = Column(Integer, primary_key=True,autoincrement=True)
    #列名name，字符串型
    name = Column(String(32))
    
#通过类创建表，本质其实是在通过pymysql执行sql语句    
def init_db():
    Base.metadata.create_all(engine)

#删除表    
def drop_db():
    Base.metadata.drop_all(engine)
init_db()

#绑定数据库
Session = sessionmaker(bind=engine)
session = Session()

#添加一条数据并提交
session.add(Test(name='apple'))
session.commit()

#查询Test表
ret=session.query(Test)

#自动转换成sql语句
print(ret)

#sql语句的结果，all()返回所有的对象的结果，以列表结果显示，first()只显示第一个结果
print(ret.first())

#显示这个对象的属性值
print(ret.first().nid,ret.first().name)
--------------
SELECT test.nid AS test_nid, test.name AS test_name 
FROM test
<__main__.Test object at 0x0000021D709F9438>
1 apple

修改

session.query(Test).filter(Test.nid==1).update({"name":"pear"})
session.commit()

删除

session.query(Test).filter(Test.nid==1).delete()
session.commit()

例2. 1对多联表的查询

group表和user表之间通过group_id形成外键约束

#导入模块
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index
from sqlalchemy.orm import sessionmaker, relationship
engine = create_engine("mysql+pymysql://yli:yli@sydnagios:3306/mydb", max_overflow=5)
Base = declarative_base()

# 一对多
class Group(Base):
    #表名
    __tablename__ = 'group'
    #列名
    nid = Column(Integer, primary_key=True,autoincrement=True)
    caption = Column(String(32))
class User(Base):
    #表名
    __tablename__ = 'user'
    
    #列名
    nid = Column(Integer, primary_key=True,autoincrement=True)
    username = Column(String(32))
    
    #外键约束
    group_id = Column(Integer, ForeignKey('group.nid'))
    
    #显示这个类的对象的时候不再显示内存地址，而是执行这个方法
    def __repr__(self):
        temp = "%s - %s: %s" %(self.nid, self.username, self.group_id)
        return temp
        
def init_db():
    Base.metadata.create_all(engine)
def drop_db():
    Base.metadata.drop_all(engine)
    
init_db()
Session = sessionmaker(bind=engine)
session = Session()

#添加
session.add(Group(caption='dba'))
session.add(Group(caption='ddd'))
session.commit()

#批量添加
session.add_all([
    User(username='alex1',group_id=1),
    User(username='alex2',group_id=2)
])
session.commit()

# 只是获取用户表的内容，通过filter进行过滤
ret = session.query(User).filter(User.username == 'alex1')
#显示对应的sql语句
print(ret)
#显示对象，这里自动调用_repr_方法
print(ret.all())

#不过滤
ret = session.query(User).all()
#获取列表里面的第一个对象
obj = ret[0]
print(ret)
print(obj)

#获取这个对象属性值
print(obj.nid)
print(obj.username)
print(obj.group_id)
--------
SELECT user.nid AS user_nid, user.username AS user_username, user.group_id AS user_group_id 
FROM user 
WHERE user.username = %(username_1)s
[1 - alex1: 1, 3 - alex1: 1]
[1 - alex1: 1, 2 - alex2: 2, 3 - alex1: 1, 4 - alex2: 2]
1 - alex1: 1
1
alex1
1

在上面的基础上，如果我们希望执行联表查询，可以有两种方式。

第一种，通过join实现。修改一下上面的例子

例如

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index
from sqlalchemy.orm import sessionmaker, relationship
engine = create_engine("mysql+pymysql://yli:yli@sydnagios:3306/mydb", max_overflow=5)

Base = declarative_base()

# 一对多
class Group(Base):
    __tablename__ = 'group'
    nid = Column(Integer, primary_key=True,autoincrement=True)
    caption = Column(String(32))
class User(Base):
    __tablename__ = 'user'
    nid = Column(Integer, primary_key=True,autoincrement=True)
    username = Column(String(32))
    group_id = Column(Integer, ForeignKey('group.nid'))

    def __repr__(self):
        temp = "%s - %s: %s" %(self.nid, self.username, self.group_id)
        return temp

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

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

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

#查询user表的username字段的所有信息
ret = session.query(User.username).all()
print(ret)

#left join的效果
sql = session.query(User,Group).join(Group, isouter=True)
print(sql)
ret = session.query(User,Group).join(Group, isouter=True).all()
print(ret)
-----------
"C:\Program Files\Python3\python.exe" C:/Users/yli/Downloads/a39dab3773523eacb8c8568b446bbcec580842/day13/s1.py
[('alex1',), ('alex2',), ('alex1',), ('alex2',), ('alex1',), ('alex2',), ('alex1',), ('alex2',)]
SELECT user.nid AS user_nid, user.username AS user_username, user.group_id AS user_group_id, `group`.nid AS group_nid, `group`.caption AS group_caption 
FROM user LEFT OUTER JOIN `group` ON `group`.nid = user.group_id
[(1 - alex1: 1, <__main__.Group object at 0x0000027295723630>), (2 - alex2: 2, <__main__.Group object at 0x0000027295723710>), (3 - alex1: 1, <__main__.Group object at 0x0000027295723630>), (4 - alex2: 2, <__main__.Group object at 0x0000027295723710>), (5 - alex1: 1, <__main__.Group object at 0x0000027295723630>), (6 - alex2: 2, <__main__.Group object at 0x0000027295723710>), (7 - alex1: 1, <__main__.Group object at 0x0000027295723630>), (8 - alex2: 2, <__main__.Group object at 0x0000027295723710>)]

第二种，通过relation实现

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index
from sqlalchemy.orm import sessionmaker, relationship
engine = create_engine("mysql+pymysql://yli:yli@sydnagios:3306/mydb", max_overflow=5)
Base = declarative_base()
#  一对多
class Group(Base):
    __tablename__ = 'group'
    nid = Column(Integer, primary_key=True,autoincrement=True)
    caption = Column(String(32))
class User(Base):
    __tablename__ = 'user'
    nid = Column(Integer, primary_key=True,autoincrement=True)
    username = Column(String(32))
    group_id = Column(Integer, ForeignKey('group.nid'))
    
    #指定relationship，通过User找Group正向关系是‘Group’，通过Group找user是u，这个是个虚拟的关系，不影响表的实际结构
    group=relationship('Group',backref='u')
    def __repr__(self):
        temp = "%s - %s: %s" %(self.nid, self.username, self.group_id)
        return temp
def init_db():
    Base.metadata.create_all(engine)
def drop_db():
    Base.metadata.drop_all(engine)
# init_db()
Session = sessionmaker(bind=engine)
session = Session()

#正向的查询
ret=session.query(User).all()
print(ret)
for obj in ret:
    print(obj.username,obj.group.caption)
    
    
#逆向的查询
ret=session.query(Group).filter(Group.caption=='dba').first()

print(ret.caption,ret.u)
for j in ret.u:
    print(j.username)

    
--------------
[1 - alex1: 1, 2 - alex2: 2, 3 - alex1: 1, 4 - alex2: 2, 5 - alex1: 1, 6 - alex2: 2, 7 - alex1: 1, 8 - alex2: 2]
alex1 dba
alex2 ddd
alex1 dba
alex2 ddd
alex1 dba
alex2 ddd
alex1 dba
alex2 ddd
#逆向
dba [1 - alex1: 1, 3 - alex1: 1, 5 - alex1: 1, 7 - alex1: 1]
alex1
alex1
alex1
alex1

下一篇继续学习多表之间的联合查询