SQLAlchemy

建立MySQL数据库

步骤1：登录MySQL

mysql -u用户 -p密码

步骤2：创建

create database 数据库名 charset=utf8

连接数据库

engine = create_engine('mysql+pymysql://用户名:密码@ip地址:3306/py3_sqlalchemy')
print(engine)

定义模型案例

我们以创建一个 users 表为例，看看它是怎么用 SQLAlchemy 的语言来描述的：

# 生成SQLORM基类
Base = declarative_base()  


# 定义一个类，继承自Base，这个类就对应数据表
class User(Base):
    # 对应MySQL中数据表的名字
    __tablename__ = 'users'

    # 创建字段
    id = Column(Integer, primary_key=True)  # users表中的id字段(主键)
    username = Column(String(64), nullable=False, index=True)  # users表中的username字段(有索引)
    password = Column(String(64), nullable=False)  # users表中的password字段
    email = Column(String(64), nullable=False, index=True)  # users表中的email字段(有索引)

常用的SQLAlchemy字段类型

1. 字段类型

2. 常用的SQLAlchemy字段选项

SQLAlchemy基本使用

1. 基本流程代码

# 创建session对象
session = DBSession()  # 生成链接数据库的实例
# 创建新User对象
new_user = User(id='1', name='Bob')
# 添加到session
session.add(new_user)
# 提交即保存到数据库
session.commit()
# 关闭session
session.close()

SQLAlchemy查询数据

1. 查询所有数据

obj = session.query(User).all()

2. 按id为4条件查询

session.query(User).filter(User.id==4).one()

按条件查询所有

session.query(User.username).filter(User.id>1).all()

通过索引取出数据

session.query(User.username).all()[1:4]

排序

session.query(User).order_by(User.id).all()不带有-表示升序
session.query(User).order_by(-User.id).all()带有-表示降序

在非连续范围

session.query(User.username).filter(User.id.in_([1,3])).all()

模糊查询

session.query(User.username).filter(User.username.like('%h___')).all()

查询数量

session.query(User).count()

逻辑与

session.query(User.username).filter(and_(User.id >= 4, User.username.like('%h%'))).all()

逻辑或

session.query(User).filter(or_(User.id >= 1, User.id <= 5)).all()

逻辑非

obj = session.query(User).filter(not_(User.id >= 3)).all()

SQLAlchemy修改数据

q1 = session.query(User).filter(User.id>2).update({'name':'boy'}) or session.query(User).filter(User.username == "laowang").update({"username": "new-laowang"})
session.commit()

SQLAlchemy删除数据

obj = session.query(User).filter(User.id == 6).one() 
# 删除对象
session.delete(obj)

数据库插入数据

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


# 链接是需要指定要用到的MySQL数据库
engine = create_engine('mysql+pymysql://root:python@localhost:3306/py3_sqlalchemy_2019?charset=utf8')
Base = declarative_base()  # 生成SQLORM基类


# 一对多
class Group(Base):
    __tablename__ = 'group'
    id = Column(Integer, primary_key=True, autoincrement=True)
    caption = Column(String(32))


class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(32))
    group_id = Column(Integer, ForeignKey('group.id'))
    # 虚拟创建关系,relationship  一般是跟foreignkey 在一起使用
    group = relationship("Group", backref='users')

    # 自定义输出方式
    def __repr__(self):
        temp = '%s-%s:%s' % (self.id, self.name, self.group_id)
        return temp


# 创建对应数据表
# Base.metadata.create_all(engine)

# 创建与数据库的会话，返回的是一个类
DBSession = sessionmaker(bind=engine)
# 创建session对象
session = DBSession()  # 生成链接数据库的实例

# 插入数据
g1 = Group(caption='研发')
g2 = Group(caption='测试')
session.add_all([g1, g2])

session.add_all([
    User(name='老王', group=g1),
    User(name='老闫', group=g1),
    User(name='老马', group=g2),
    User(name='老叶', group=g2)
])

session.commit()
session.close()