sqlalchemy模块的基本使用

Python中SQLAlchemy模块通过建立orm来对数据库进行操作

1. 建表

　　方式1

# -*- coding:utf-8 -*-
# Author:Wong Du

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, DATE

# 建立镜像连接
engine = create_engine("mysql+pymysql://caiyun:caiyun814@192.168.1.73/testdb")

# 创建orm基类，用于建表结构继承使用
Base = declarative_base()

# 创建表Student类
class Student(Base):
    __tablename__ = 'student'   # 表名
    id = Column(Integer, nullable=False, primary_key=True, autoincrement=True)
    name = Column(String(32), nullable=False)
    register_date = Column(DATE)


    # 让查询到的数据变得可读
    def __repr__(self):
        return "id:%s name:%s date:%s"
               % (self.id, self.name, self.register_date)

# 提交，创建表结构
Base.metadata.create_all(engine)

　　方式2

# -*- coding:utf-8 -*-
# Author:Wong Du

from sqlalchemy import Table, create_engine
from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base

# 建立镜像连接
engine = create_engine("mysql+pymysql://root:root814@111.230.64.164/testdb", echo=True)

# 创建orm基类，用于建表结构继承使用
Base = declarative_base()

# 建表
user = Table(
    'user', Base.metadata,  # ‘user’为表名，通过Base的metadata方法建表
    Column('id', Integer, nullable=False, primary_key=True, autoincrement=True),
    Column('name', String(32), nullable=False),
    Column('password', String(64), nullable=False),
)

# 提交，创建表结构
Base.metadata.create_all(engine)

　　方式3

# -*- coding:utf-8 -*-
# Author:Wong Du

from sqlalchemy import Table, MetaData, create_engine
from sqlalchemy import Column, Integer, String

# 创建镜像连接
engine = create_engine("mysql+pymysql://root:root814@111.230.64.164/testdb", echo=True)

# 创建orm对象
metadata = MetaData()

# 建表
user = Table(
    'user2', metadata,
    Column('id', Integer, nullable=False, primary_key=True, autoincrement=True),
    Column('name', String(32), nullable=False),
    Column('password', String(64), nullable=False),
)

metadata.create_all(engine)

2. sqlalchemy筛选表内容基本操作

# -*- coding:utf-8 -*-
# Author:Wong Du

import sqlalchemy_createTable
from sqlalchemy.orm import sessionmaker
from sqlalchemy import func

# 通过绑定镜像连接生成会话类
Session_class = sessionmaker(bind=sqlalchemy_createTable.engine)
# 生成会话对象
session = Session_class()

# 多条件查询，通过.query方法来查表，.filter方法来筛选表内容
sel_obj = session.query(sqlalchemy_createTable.Student)
    .filter(sqlalchemy_createTable.Student.id>0)
    .filter(sqlalchemy_createTable.Student.id<5).first()

# 查询所有数据
sel_obj2 = session.query(sqlalchemy_createTable.Student).all()
sel_obj3 = session.query(sqlalchemy_createTable.Student).filter().all()
if sel_obj2 == sel_obj3:
    print("效果相同")

# like匹配&统计
sel_obj4 = session.query(sqlalchemy_createTable.Student)
    .filter(sqlalchemy_createTable.Student.name.like("%a%")).count()
print(sel_obj4)

# 分组统计
sel_obj5 = session.query(
    sqlalchemy_createTable.Student.name,
    func.count(sqlalchemy_createTable.Student.name))
    .group_by(sqlalchemy_createTable.Student.name).all()
print(sel_obj5)

3. 其他操作

# -*- coding:utf-8 -*-
# Author:Wong Du

import sqlalchemy_createTable
from sqlalchemy.orm import sessionmaker

# 创建与engine镜像连接的数据库交互的类
Session_class = sessionmaker(bind=sqlalchemy_createTable.engine)
# 生成会话实例
session = Session_class()

# 添加/插入表数据，创建要插入的数据对象
ins_stu_obj = sqlalchemy_createTable.Student(name='zhangsan', register_date='2019-12-12')

# 将要操作的数据对象添加到会话实例中
session.add(ins_stu_obj)

# 提交会话操作对象，使其真正生效
session.commit()

# 关闭会话实例
session.close()


# 查询表数据
'''
# filter_by关键字表达式，filter数据库SQL表达式，为空则匹配所有
# .first()获取匹配的第一条查询数据，.all()获取匹配的所有数据
# 格式1：session.query(表类名).filter_by(匹配关键字).first()
# 格式2：session.query(表类名).filter(匹配SQL语句).all()
# 获取查询到的表数据对象，为一个列表形式
sel_stu_obj = session.query(sqlalchemy_createTable.Student).filter_by(name='zhangsan').first()
sel_stu_obj2 = session.query(sqlalchemy_createTable.Student).filter(sqlalchemy_createTable.Student.id>0).all()
print(sel_stu_obj)
print(sel_stu_obj2)
print(sel_stu_obj2[1].name)
'''

# 修改表数据
'''
# 获取要修改的表数据对象
upd_stu_obj = session.query(sqlalchemy_createTable.Student).filter_by(name='zhangsan').first()
print(upd_stu_obj)
upd_stu_obj.name = 'ZhangSan'

session.commit()
sel = session.query(sqlalchemy_createTable.Student).filter_by().first()
print(sel)

session.close()
'''

# 回滚应用例子
upd = session.query(sqlalchemy_createTable.Student).filter(sqlalchemy_createTable.Student.id==7).all()
print(upd[0].register_date)
upd[0].register_date = '2008-10-01'

ins = sqlalchemy_createTable.Student(name='WangWu', register_date='2018-12-12')
session.add(ins)

print(session.query(sqlalchemy_createTable.Student).
      filter(sqlalchemy_createTable.Student.register_date.
             in_(['2008-10-01', '2018-12-12'])).all())

session.rollback()

print(session.query(sqlalchemy_createTable.Student).
      filter(sqlalchemy_createTable.Student.register_date.
             in_(['2008-10-01', '2018-12-12'])).all())

4. 一对一外键

# -*- coding:utf-8 -*-
# Author:Wong Du

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

# 创建镜像连接
engine = create_engine("mysql+pymysql://caiyun:caiyun814@192.168.1.73/testdb?charset=utf8")
# 创建orm基类
Base = declarative_base()

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, nullable=False, primary_key=True, autoincrement=True)
    name = Column(String(32), nullable=False)
    passwd = Column(String(64), nullable=False, default='123456')

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

class Addr(Base):
    __tablename__ = 'addr'
    id = Column(Integer, nullable=False, primary_key=True, autoincrement=True)
    address = Column(String(32), default='X')
    u_id = Column(Integer, ForeignKey('user.id'), nullable=False)   # 配置外键

    # 通过sqlalchemy内部方法把两张表外键关联起来，从而实现不同表数据的互相调用
    user = relationship('User', backref='addr')

    def __repr__(self):
        return "<id:%d address:%s u_id:%d>" 
               % (self.id, self.address, self.u_id)


Base.metadata.create_all(engine)


'''
--------------------------------------------------------------------------------------
                                   华丽分隔线
--------------------------------------------------------------------------------------
'''

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

# 插入
ins_user = User(name='dudu', passwd='dudu814')
# ins_user2 = User(name='hongfa', passwd='hongfa814')
# ins_user3 = User(name='jinglin', passwd='jinglin814')
#
# ins_addr = Addr(address='guangzhou', u_id=1)
# ins_addr2 = Addr(address='Guangdong', u_id=2)
# ins_addr3 = Addr(address='shenzhen', u_id=3)
# session.add_all( [ins_user, ins_user2, ins_user3,
#             ins_addr, ins_addr2, ins_addr3] )
# session.commit()

sel_user = session.query(User).filter(User.name=='dudu').first()
print(sel_user)
print(sel_user.addr)
print(sel_user.addr[0].address)

print('33[33;1m华丽分隔线33[0m'.center(40, '-'))

sel_addr = session.query(Addr).all()
print(sel_addr)
print(sel_addr[2])
print(sel_addr[2].user)
print(sel_addr[2].user.passwd)

5. 一对多外键

# -*- coding:utf-8 -*-
# Author:Wong Du

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

engine = create_engine("mysql+pymysql://caiyun:caiyun814@localhost/testdb?charset=utf8")

Base = declarative_base()


class Taobao_addr(Base):
    __tablename__ = 'taobao_addr'
    id = Column(Integer, nullable=False, primary_key=True, autoincrement=True)
    addr = Column(String(32), nullable=False, server_default='XXXXXXXXXXXXXXX')

    def __repr__(self):
        return "<id:%d addr:%s>" 
               % (self.id, self.addr)

class Taobao_user(Base):
    __tablename__ = 'taobao_user'
    id = Column(Integer, nullable=False, primary_key=True, autoincrement=True)
    name = Column(String(32), nullable=False, server_default='X')
    home_addr_id = Column(Integer, ForeignKey('taobao_addr.id'), nullable=False)
    work_addr_id = Column(Integer, ForeignKey('taobao_addr.id'), nullable=False)

    # home_addr = relationship('Taobao_addr')
    # work_addr = relationship('Taobao_addr')
    home_addr = relationship('Taobao_addr', foreign_keys=home_addr_id)
    work_addr = relationship('Taobao_addr', foreign_keys=work_addr_id)

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

Base.metadata.create_all(engine)


'''
--------------------------------------------------------------------------------------
                                   华丽分隔线
--------------------------------------------------------------------------------------
'''

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

# 插入数据
'''
addr_list = ['ShenZhen', 'GuangZhou', 'BeiJing', 'ShangHai']
ins_addr_list = []
for i in addr_list:
    obj = Taobao_addr(addr=i)
    ins_addr_list.append(obj)
session.add_all(ins_addr_list)

ins_user = Taobao_user(name='dudu', home_addr_id=1, work_addr_id=4)
ins_user2 = Taobao_user(name='junry', home_addr_id=3, work_addr_id=3)
ins_user3 = Taobao_user(name='hongfa', home_addr_id=4, work_addr_id=2)
session.add_all([ins_user, ins_user2, ins_user3])

session.commit()
'''

# 查询数据
sel_user = session.query(Taobao_user).all()
print(sel_user)
print(sel_user[0].home_addr)
print(sel_user[0].id, sel_user[0].name,
      sel_user[0].home_addr.addr,
      sel_user[0].work_addr.addr)