sqlalchemy快速使用
# 是orm框架，可以独立使用，集成到web项目中
安装： SQLAlchemy
# 组成部分：
Engine 框架的引擎
Connection Pooling 数据连接池
Dialect 选择链接数据库的DB API种类：mysql sqllite等等
Schema/Types 架构和类型
SQL Exprression Language SQL表达式语言
# 能够操作的关系型数据库
pymysql
mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>]
eg:
mysql+pymysql://root:123@127.0.0.1:3306/movie
MySQL-Connector
mysql+mysqlconnector://<user>:<password>@<host>[:<port>]/<dbname>
cx_Oracle
oracle+cx_oracle://user:pass@host:port/dbname[?key=value&key=value...]
操作原生sql快速使用
# 第一步：
from threading import Thread
import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy.engine.base import Engine
# 第二步： 实例化得到一个engine
engin = create_engine(
'mysql+pymysql://root:123@127.0.0.1:3306/flask?charset=utf8',
max_overflow=0, # 超过连接池大小外最多创建的连接
pool_size=5, # 连接池大小
pool_timeout=30, # 池中没有，线程最多等待的时间，否则报错
pool_recycle=-1 # 多久之后对线程池中的线程进行一次连接的回收（重置）
)
# 第三步：通过engine拿到一个链接 通过conn对象从连接池中取出一个链接
def task():
conn = engin.raw_connection()
cursor = conn.cursor()
cursor.execute("select * from boy")
print(cursor.fetchall())
# 第四步：多线程测试
for i in range(20):
t = Thread(target=task)
t.start()
使用sqlalchemy创建表
# 写一个类 继承某个父类 写字段
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, Text, ForeignKey, DateTime, UniqueConstraint, Index
import datetime
from sqlalchemy import create_engine
# 创建一个父类
Base = declarative_base()
# 写类 继承父类
class User(Base): # 所有字段都是Column类
id = Column(Integer, primary_key=True)
name = Column(String(32), index=True, nullable=False) # 索引 不可为空
email = Column(String(32), unique=True)
ctime = Column(DateTime, default=datetime.datetime.now) # now加括号的话就永远是当前时间
extra = Column(Text, nullable=True)
# 定义表名
__tablename__ = 'users' # 数据库表名称
__table_args__ = (
UniqueConstraint('id', 'name', name='uix_id_name'), # 联合唯一
Index('ix_id_name', 'name', 'email') # 联合索引
)
class Book(Base):
__tablename__ = 'books' # 数据库表名称
id = Column(Integer, primary_key=True)
name = Column(String(32), index=True, nullable=False)
price = Column(Integer)
class Publish(Base):
__tablename__ = 'publish'
id = Column(Integer, primary_key=True)
name = Column(String(32), nullable=True)
# sqlalchemy不能创建数据库，不能修改，删除字段，只能创建表和删除表
def init_db():
engine = create_engine(
"mysql+pymysql://root:123@127.0.0.1:3306/flask?charset=utf8",
max_overflow=0, # 超过连接池大小外最多创建的连接
pool_size=5, # 连接池大小
pool_timeout=30, # 池中没有线程最多等待的时间，否则报错
pool_recycle=-1 # 多久之后对线程池中的线程进行一次连接的回收（重置）
)
# 创建出所有被Base管理的表
Base.metadata.create_all(engine)
def drop_db():
engine = create_engine(
"mysql+pymysql://root:123@127.0.0.1:3306/flask?charset=utf8",
max_overflow=0, # 超过连接池大小外最多创建的连接
pool_size=5, # 连接池大小
pool_timeout=30, # 池中没有线程最多等待的时间，否则报错
pool_recycle=-1 # 多久之后对线程池中的线程进行一次连接的回收（重置）
)
# 删除所有被Base管理的表
Base.metadata.drop_all(engine)
if __name__ == '__main__':
init_db()
# drop_db()
使用sqlalchemy插入数据【使用线程安全】
# 先创建一个engine对象
from sqlalchemy import create_engine
engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/flask", max_overflow=0, pool_size=5)
# 通过engine获得session对象
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine)
# 每次执行数据库操作时，都需要创建一个connection
# session = Session()
# 通过session操作插入数据【创建数据】
from models import User, Book, Publish
# user = User(name='lqz',email='1@qq.com',extra='哈哈哈')
# session.add(user)
# session.commit()
# session.close()
# 每次创建的session是全局session 数据不安全
# sqlalchemy提供了一个定义一次的session，能够做到不同线程中使用的是自己的session，底层也是基于local
from sqlalchemy.orm import scoped_session
session = scoped_session(Session) # 线程安全的session
def task(i):
user = User(name='summer%s'%i, email='%s@qq.com'%i, extra='好冷')
session.add(user)
session.commit()
session.close()
from threading import Thread
for i in range(10):
t = Thread(target=task,args=[i,])
t.start()
表模型创建【一对多和多对多表关系】
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.orm import relationship
from sqlalchemy import create_engine
Base = declarative_base()
# 创建一对多关系
class Hobby(Base):
__tablename__ = 'hobby'
id = Column(Integer, primary_key=True)
caption = Column(String(50), default='篮球')
class Person(Base):
__tablename__ = 'person'
id = Column(Integer, primary_key=True)
name = Column(String(32), index=True, nullable=True)
hobby_id = Column(Integer, ForeignKey('hobby.id')) # 跟hobby表中id字段做外键关联
# 如果想要基于对象的跨表查询，那么就需要加这个字段 backref用于反向查询 只用于跨表查不存进数据库
hobby = relationship('Hobby', backref='pers')
# 创建多对多关系
class Rule(Base):
__tablename__ = 'rule'
id = Column(Integer, primary_key=True)
name = Column(String(32), nullable=True,unique=True)
class User(Base):
__tablename__ = 'user'
id = Column(Integer, primary_key=True)
name = Column(String(32), nullable=True,unique=True)
# 这个字段只用于快速查询 相当于django的manytomany
rules = relationship('Rule',secondary='rule2user',backref='users')
def __str__(self):
return self.name # 有时候需要配合repr
def __repr__(self):
return self.name
class Rule2User(Base):
__tablename__ = 'rule2user'
id = Column(Integer,primary_key=True) # autoincrement自增
rule_id = Column(Integer,ForeignKey('rule.id'))
user_id = Column(Integer,ForeignKey('user.id'))
def init_db():
engine = create_engine(
"mysql+pymysql://root:123@127.0.0.1:3306/flask?charset=utf8",
max_overflow=0, # 超过连接池大小外最多创建的连接
pool_size=5, # 连接池大小
pool_timeout=30, # 池中没有线程最多等待的时间，否则报错
pool_recycle=-1 # 多久之后对线程池中的线程进行一次连接的回收（重置）
)
# 创建出所有被Base管理的表
Base.metadata.create_all(engine)
if __name__ == '__main__':
init_db()
一对多数据操作
from sqlalchemy.orm import scoped_session, sessionmaker
from sqlalchemy import create_engine
from model import Hobby,Person
engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/flask", max_overflow=0, pool_size=5)
Session = sessionmaker(bind=engine)
session = scoped_session(Session)
# 一对多关系新增 【hobby表】
hobby = Hobby(caption='羽毛球')
print(hobby.id) # None
session.add(hobby)
person = Person(name='summer',hobby_id=1) # 方式一直接写id号
person = Person(name='jerry',hobby=Hobby(caption='足球')) # 方式二 直接新增
hobby = session.query(Hobby).filter_by(id=1).first() # 方式三 先查出当前的hobby对象 再进行绑定
person = Person(name='tony',hobby_id=hobby.id) # 也可以直接写hobby=hobby
session.add(person)
# #
# 基于对象的跨表查询 【正向查询】
person = session.query(Person).filter_by(id=1).first() # 查询当前id为1的用户的爱好id和名字
print(person.hobby_id)
print(person.hobby.caption)
# 反向查询
hobby = session.query(Hobby).filter_by(id=1).first() # 通过查询hobby对象查询person
print(hobby.pers) # 多条数据
for person in hobby.pers:
print(person.name)
session.commit()
session.close()
多对多数据操作
from sqlalchemy.orm import scoped_session, sessionmaker
from sqlalchemy import create_engine
from model import User, Rule2User, Rule
from models import Book
from sqlalchemy.sql import text
engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/flask", max_overflow=0, pool_size=5)
Session = sessionmaker(bind=engine)
session = scoped_session(Session)
# 多对多新增
# rule = Rule(name='工程师')
# user = User(name='hony')
# session.add(rule)
# session.add(user)
# session.add_all([rule,user]) # 方式1：手动增加表数据 all是可以存放多个表
# h = Rule2User(rule_id=1,user_id=1) # 手动操作第三张表
# session.add(h)
# user = User(name='kevin',rules=[Rule(name='运营'),Rule(name='运维')]) # 方式二 通过关键字段来创建数据 第三张表自动关联
# session.add(user)
# 基于对象的跨表查 【正向查询】
# user = session.query(User).filter_by(id=4).first()
# print(user) # 重写了str方法就可以显示名字 不然显示的是对象
# print(user.rules)
# for i in user.rules:
# print(i.id)
# print(i.name)
# 反向查询
# rule = session.query(Rule).filter_by(id=7).first()
# print(rule.users)
# for i in rule.users:
# print(i.name)
# 基本增删改查
# 增
# rule = Rule(name='高级工程师')
# user = User(name='herry')
# session.add_all([rule, user])
# 删 【有关联字段没法删除】
# rule = session.query(Rule).filter_by(id=9).delete()
# 查 filter_by是值 filter是条件
# user = session.query(User).filter_by(id=1).first() # 单个
# print(user.rules) # 反向查询
# for i in user.rules:
# print(i.name)
# user = session.query(User).all() # 所有
# print(user) # [hony, jerry, kevin, kkk000000, summer]
# user = session.query(User.name.label('XX'),User.id)
# print(user) # 原生sql:SELECT user.name AS `XX`, user.id AS user_id
# print(user.all()) # [('hony', 3), ('jerry', 2), ('kevin', 4), ('kkk000000', 5), ('summer', 1)]
# for i in user.all():
# print(i[0]) # 取出所有的名字
# print(i[1]) # 取出所有的id
# user = session.query(User).filter(User.name != 'summer').all()
# user = session.query(User).filter_by(name='jason').all()
# print(len(user))
# 查询所有，使用占位符(了解) :value :name 可以用and和or
# res = session.query(User).filter(text("id<:value and name=:name")).params(value=5, name='summer').order_by(User.id).all()
# print(res)
# 自定义查询（了解）
# res = session.query(User).from_statement(text("SELECT * FROM users where id=:id")).params(id=3).all()
# print(len(res))
# 改 【将id大于5的用户名改为kkk】
# user = session.query(User).filter(User.id>4).update({'name':'kkk'}) # 方式一
# 方式二 类似于django的F查询 相当于当作字符串相加 必须写这个参数synchronize_session=False
# user = session.query(User).filter(User.id>4).update({User.name:User.name+'000'},synchronize_session=False)
# 当数字相加
# book = session.query(Book).filter(Book.price > 1).update({'price': Book.price + 8}, synchronize_session='evaluate')
session.commit()
session.close()
session操作原生sql
from sqlalchemy.orm import scoped_session, sessionmaker
from sqlalchemy import create_engine
from models import User
engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/flask", max_overflow=0, pool_size=5)
Session = sessionmaker(bind=engine)
session = scoped_session(Session)
# 查询
# cursor = session.execute('select * from User')
# result = cursor.fetchall()
# print(result)
# 新增
cursor = session.execute('insert into User(name) values(:value)',params={'value':'ssss'})
print(cursor.lastrowid) # id为6
session.commit()
session.close()
flask中使用sqlalchemy
# flask中使用sqlalchemy，直接使用
# 使用flask-sqlalchemy集成
1 导入 from flask_sqlalchemy import SQLAlchemy
2 实例化得到对象
db = SQLAlchemy()
3 将db注册到app中
db.init_app(app)
4 视图函数中使用session
全局的db.session # 线程安全的
5 models.py 中继承Base
db.Base
6 写字段
username = db.Column(db.String(80), unique=True, nullable=False)
flask-migrate
# python manage.py makemigrations # 记录变化
# python manage.py migrate #把变化同步到数据库
# 使用步骤：
1 导入
from flask_script import Manager
from flask_migrate import Migrate, MigrateCommand
2 注册
manager = Manager(app)
# 使用flask_migrate的Migrate 包裹一下app和db（sqlalchemy对象）
Migrate(app, db)
3 给flask_script增加一个db命令
# 把命令增加到flask-script中去
manager.add_command('db', MigrateCommand)
4 出现3条命令
python manage.py db init # 只执行一次，做初始化操作，以后再也不执行了，多出一个migrations文件夹
python manage.py db migrate #等同于django 的makemigrations
python manage.py db upgrade #等同于django 的migrate
