sqlalchemy

sqlalchemy

https://docs.sqlalchemy.org/en/20/orm/quickstart.html

1 快速使用

安装 2.0.30版本

pip3 insatll sqlalchemy 

架构

Engine，框架的引擎
Connection Pooling ，数据库连接池
Dialect，选择连接数据库的DB API种类 
SQL Exprression Language，SQL表达式语言

链接不同数据库

postgresql

engine = create_engine("postgresql://scott:tiger@localhost/mydatabase")
# psycopg2
engine = create_engine("postgresql+psycopg2://scott:tiger@localhost/mydatabase")
# pg8000
engine = create_engine("postgresql+pg8000://scott:tiger@localhost/mydatabase")

Mysql

# default
engine = create_engine("mysql://scott:tiger@localhost/foo")
# mysqlclient (a maintained fork of MySQL-Python)
engine = create_engine("mysql+mysqldb://scott:tiger@localhost/foo")
# PyMySQL
engine = create_engine("mysql+pymysql://scott:tiger@localhost/foo")

oracle

engine = create_engine("oracle://scott:tiger@127.0.0.1:1521/sidname")
engine = create_engine("oracle+cx_oracle://scott:tiger@tnsname")

Microsoft SQL Server

# pyodbc
engine = create_engine("mssql+pyodbc://scott:tiger@mydsn")
# pymssql
engine = create_engine("mssql+pymssql://scott:tiger@hostname:port/dbname")

sqlite

# Unix/Mac - 4 initial slashes in total
engine = create_engine("sqlite:////absolute/path/to/foo.db")
# Windows
engine = create_engine("sqlite:///C:\\path\\to\\foo.db")
# Windows alternative using raw string
engine = create_engine(r"sqlite:///C:\path\to\foo.db")

2 原生操作

import threading
from sqlalchemy import create_engine
engine = create_engine(
    "mysql+pymysql://root:123456@127.0.0.1:3306/sqlalchemy01?charset=utf8",
    max_overflow=0,  # 超过连接池大小外最多创建的连接
    pool_size=5,  # 连接池大小
    pool_timeout=30,  # 池中没有线程最多等待的时间，否则报错
    pool_recycle=-1  # 多久之后对线程池中的线程进行一次连接的回收（重置）
)

def task():
    conn = engine.raw_connection()
    cursor = conn.cursor()
    cursor.execute(
        "select * from user"
    )
    result = cursor.fetchall()
    print(result)
    cursor.close()
    conn.close()

for i in range(20):
    t = threading.Thread(target=task)
    t.start()

3 sqlalchemy操作表

1 建表删表

from sqlalchemy import Column, Integer, String, Text, ForeignKey, DateTime, UniqueConstraint, Index
import datetime
# 创建基类，以后所有类，都必须继承基类

# 老版本创建基类(不建议)
# from sqlalchemy.ext.declarative import declarative_base
# Base = declarative_base()

# 新版本创建基类
from sqlalchemy.orm import DeclarativeBase
class Base(DeclarativeBase):
    pass


# 写个类，继承
# 写字段：传统方式，类型方式
class User(Base):
    __tablename__='user'
    id=Column(Integer,primary_key=True,autoincrement=True)
    name = Column(String(32), index=True, nullable=False)  # name列varchar32，索引，不可为空
    email = Column(String(32), unique=True)  # email 列，varchar32，唯一
    # datetime.datetime.now不能加括号，加了括号，以后永远是当前时间
    ctime = Column(DateTime, default=datetime.datetime.now)
    extra = Column(Text, nullable=True)


if __name__ == '__main__':
    from sqlalchemy import create_engine

    engine = create_engine(
        "mysql+pymysql://root:123456@127.0.0.1:3306/sqlalchemy01?charset=utf8",
        max_overflow=0, 
        pool_size=5, 
        pool_timeout=30,  
        pool_recycle=-1 
    )
    # 创建表(不能创建库，只能新增或删除表，不能增加删除字段)
    Base.metadata.create_all(engine)

    # 删除表
    # Base.metadata.drop_all(engine)

2 数据增删查改

from models import User
# 创建engine
from sqlalchemy import create_engine
engine = create_engine(
    "mysql+pymysql://root:123456@127.0.0.1:3306/sqlalchemy01?charset=utf8",
    max_overflow=0, 
    pool_size=5, 
    pool_timeout=30,
    pool_recycle=-1
)

# 创建 session对象---老方式
# from sqlalchemy.orm import sessionmaker
# Session = sessionmaker(bind=engine)
# session=Session()
# 创建 session对象---新方式(推荐)
from sqlalchemy.orm import Session
session=Session(engine)

新增

user=User(name='qc',email='35@qq.com')
session.add(user)
session.commit()  #提交
session.close()

新增多条

user1=User(name='tom',email='344@qq.com')
user2=User(name='jom',email='335@qq.com')
session.add_all([user1,user2])
session.commit()
session.close()

查

res=session.query(User).filter_by(name='qc').all()
# res=session.query(User).filter_by(id=1).all()
print(res)
print(res[0])

删除

res=session.query(User).filter_by(name='lqz').delete()
print(res)
session.commit()

修改一

session.query(User).filter_by(id=2).update({'name':'ooo'})
session.commit()

修改二

user=session.query(User).filter_by(id=2).first()
user.name='uuii'
session.add(user) #[id字段在不在] 如果对象不存在，就是新增，如果对象存在，就是修改
session.commit()

4 一对多关系

models.py

from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.orm import relationship

from sqlalchemy.orm import DeclarativeBase
class Base(DeclarativeBase):
    pass

class Hobby(Base):
    __tablename__ = 'hobby'
    id = Column(Integer, primary_key=True)
    caption = Column(String(50), default='篮球')
    def __str__(self):
        return self.caption


class Person(Base):
    __tablename__ = 'person'
    nid = Column(Integer, primary_key=True)
    name = Column(String(32), index=True, nullable=True)
    # hobby指的是tablename而不是类名
    hobby_id = Column(Integer, ForeignKey("hobby.id")) 

    # 跟数据库无关，不会新增字段，只用于快速链表操作
    # 类名，backref用于反向查询
    hobby = relationship('Hobby', backref='pers')
    def __str__(self):
        return self.name

    def __repr__(self):
        return self.name

使用

from models import Person,Hobby
from sqlalchemy import create_engine
engine = create_engine(
    "mysql+pymysql://root:123456@127.0.0.1:3306/sqlalchemy01?charset=utf8",
    max_overflow=0, 
    pool_size=5,
    pool_timeout=30, 
    pool_recycle=-1
)


from sqlalchemy.orm import Session
session=Session(engine)

增(笨办法)

# 1 先增加一个hobby
hobby=Hobby()
session.add(hobby)
session.commit()
# 2 增加Person---》必须要有hobby_id
person=Person(name='qc',hobby_id=1)
session.add(person)
session.commit()

增((简便方法)

person = Person(name='qc', hobby=Hobby(caption='乒乓球'))
session.add(person)
session.commit()
hobby=session.query(Hobby).filter_by(id=1).first()
person = Person(name='qc1', hobby=hobby)
session.add(person)
session.commit()

基于对象的跨表查询--->正向

# hobby = relationship('Hobby', backref='pers')
person=session.query(Person).filter_by(nid=2).first()
print(person)
print(person.hobby_id)
print(person.hobby)

基于对象的跨表查询--->反向

# hobby = relationship('Hobby', backref='pers')
hobby = session.query(Hobby).filter_by(id=1).first()
print(hobby.caption)
print(hobby.pers)

5 多对多关系

models.py

from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.orm import relationship
from sqlalchemy.orm import DeclarativeBase


class Base(DeclarativeBase):
    pass


class Boy2Girl(Base):
    __tablename__ = 'boy2girl'
    id = Column(Integer, primary_key=True, autoincrement=True)
    girl_id = Column(Integer, ForeignKey('girl.id'))
    boy_id = Column(Integer, ForeignKey('boy.id'))


class Girl(Base):
    __tablename__ = 'girl'
    id = Column(Integer, primary_key=True)
    name = Column(String(64), unique=True, nullable=False)

    def __repr__(self):
        return self.name


class Boy(Base):
    __tablename__ = 'boy'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(64), unique=True, nullable=False)

    girls = relationship('Girl', secondary='boy2girl', backref='boys')

    def __repr__(self):
        return self.name

使用

from models import Boy, Girl, Boy2Girl
from sqlalchemy import create_engine

engine = create_engine(
    "mysql+pymysql://root:123456@127.0.0.1:3306/sqlalchemy01?charset=utf8",
    max_overflow=0,
    pool_size=5,
    pool_timeout=30,
    pool_recycle=-1
)

from sqlalchemy.orm import Session

session = Session(engine)

普通增加

boy = Boy(name='cat')
boy1 = Boy(name='adr')
girl = Girl(name='red')
girl1 = Girl(name='pink')
session.add_all([boy,girl,boy1,girl1])
session.commit()

# 往中间表增加记录
res=Boy2Girl(boy_id=1,girl_id=1)
session.add_all([res])
session.commit()

快捷添加

girls = session.query(Girl).filter(Girl.id >= 2).all()
print(girls)
boy = session.query(Boy).filter(Boy.id == 1).first()
boy.girls = girls
session.add(boy)
session.commit()

# 新用户建立关联
girls = session.query(Girl).filter(Girl.id >= 2).all()
boy = Boy(name='fff',girls=girls)
session.add(boy)
session.commit()

基于对象的跨表查询--->正向

#girls = relationship('Girl', secondary='boy2girl', backref='boys')
boy = session.query(Boy).filter(Boy.id == 1).first()
print(boy.name)
print(boy.girls)

基于对象的跨表查询--->反向

#girls = relationship('Girl', secondary='boy2girl', backref='boys')
girl = session.query(Girl).filter(Girl.id == 2).all()[0]
print(girl.name)
print(girl.boys)

6 常用查询

1 filter_by

res=session.query(User).filter_by(name='aaa').all() # 返回列表
res=session.query(User).filter_by(name='aaa').first() # 返回单条

2 filter[where]

res=session.query(User).filter(User.id>1).all()
res=session.query(User).where(User.id>1).all()

3 between

# select * from user where id between 1 and 3
res=session.query(User).where(User.id.between(1,3)).all()

4 in

# select * from user where id in (1,3,4)
res = session.query(User).filter(User.id.in_([1, 3, 4])).all()

5 ~ (非,not in)

# select * from user where id not in (1,3,4)
res = session.query(User).filter(~User.id.in_([1, 3, 4])).all()

6 二次筛选

# select * from user where user.id in (select id from user where name='xxx')
res = session.query(User).where(User.id.in_(session.query(Person.nid).filter_by(name='xxx'))).all()

7 and,or 条件

from sqlalchemy import and_, or_

# select * from user where id=3 and name='aaa'
res = session.query(User).filter(and_(User.id ==3, User.name == 'aaa')).all()

# select * from user where id>1 or name='qdd'
res = session.query(User).filter(or_(User.id > 1, User.name == 'qdd')).all()

8 通配符，以e开头，不以e开头

# select * from user where name like 'a%'
res = session.query(User).filter(User.name.like('a%')).all()
# select * from user where name not like 'a%'
res = session.query(User).filter(~User.name.like('a%')).all()
# select * from user where name like 'a_'  _ 表示只有一位
res = session.query(User).filter(User.name.like('a_')).all()

9 限制，用于分页，区间

# select * from user limit 3
res = session.query(User)[0:3]
print(res)

10 排序 desc asc

# select * from user order by name  升序 默认
res = session.query(User).order_by(User.name).all()

# select * from user order by name desc  降序
res = session.query(User).order_by(User.name.desc()).all()

11 分组

# select name,count(*) from user group by name
res = session.query(func.count(User.name), User.name).group_by(User.name).all()

12 having筛选

# select name,count(*) from user group by name having count(*)>1
res = session.query(func.count(User.name), User.name).group_by(User.name).having(func.count(User.id) > 1).all()

13 连表（默认用forinkey关联）

# select * from person,hobby where person.hobby_id=hobby.id;
res=session.query(Person,Hobby).where(Person.hobby_id==Hobby.id).all()

14 join表

默认是inner join

# select * from person inner join hobby on person.hobby_id=hobby.id;
res = session.query(Person).join(Hobby).all()

left join

isouter=True 外连，表示 left join

# select * from person left join hobby on person.hobby_id=hobby.id;
res=session.query(Person).join(Hobby,isouter=True)

right join

没有右连接，反过来即可

# select * from person right join hobby on person.hobby_id=hobby.id;
ret = session.query(Hobby).join(Person, isouter=True)

自定义连表

# 自己指定on条件（连表条件）,第二个参数，支持on多个条件，用and_,同上因为我们可以没有强外键关联
    ret = session.query(Person).join(Hobby, Person.nid == Hobby.id, isouter=True)

7 scoped线程安全

全局就用一个session对象，它在不同线程中---》都是这个线程自己的

from models import User
import threading
# 第一步：创建engine
from sqlalchemy import create_engine
engine = create_engine(
    "mysql+pymysql://root:1234@127.0.0.1:3306/sqlalchemy001?charset=utf8",
    max_overflow=0,  # 超过连接池大小外最多创建的连接
    pool_size=5,  # 连接池大小
    pool_timeout=30,  # 池中没有线程最多等待的时间，否则报错
    pool_recycle=-1  # 多久之后对线程池中的线程进行一次连接的回收（重置）
)

# 第二步：创建 线程安全的 session
from sqlalchemy.orm import sessionmaker
from sqlalchemy.orm import scoped_session
Session = sessionmaker(bind=engine)
session=scoped_session(Session)

# 第三步：正常使用-->再flask中，使用全局的session即可，实现：不同线程使用线程自己的session对象
def task(se,i):
    session=se()
    session.add(User(name='xxx',email=f'{i}@qq.com'))
    session.commit()
    print('=========',session)

if __name__ == '__main__':
    l=[]
    for i in range(10):
        t=threading.Thread(target=task,args=[session,i])
        t.start()
        l.append(t)
    for i in l:
        i.join()

8 flask-sqlalchemy使用

1 集成到项目中(自己集成)

models.py

from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import DeclarativeBase

class Base(DeclarativeBase):
    pass

class User(Base):
    __tablename__='user'
    id=Column(Integer,primary_key=True,autoincrement=True)
    name = Column(String(32), index=True, nullable=False) 
    email = Column(String(32), unique=True)

    
    
    def __str__(self):
        return self.name

    def __repr__(self):
        return self.name

db.py

from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session
engine = create_engine("mysql+pymysql://root:123456@127.0.0.1:3306/sqlalchemy01?charset=utf8", max_overflow=0, pool_size=5)
Session = sessionmaker(bind=engine)
session = scoped_session(Session)

app.py

from flask import Flask, jsonify
from .models import User
from .db import session

app = Flask(__name__)
app.debug = True

@app.get('/')
def index():
    res = session.query(User).filter(User.id >= 1).all()
    result = []
    for user in res:
        user_data = {
            'id': user.id,
            'name': user.name,
            'age': user.email
        }
        result.append(user_data)
    return jsonify({'code': 100, 'result': result})

if __name__ == '__main__':
    app.run()

2 使用flask-sqlalchemy集成到flask中

安装

pip install Flask-sqlalchemy

使用步骤

1 导入 from flask_sqlalchemy import SQLAlchemy
2 实例化得到对象 db = SQLAlchemy(app)
3 models.py 中继承Model
4 视图函数中使用session
	全局的db.session  # 线程安全的
5 配置文件中加入


"""
├── 项目
    ├── src/      
     	├── __init__.py	
        ├── models.py
        ├── settings.py
		├── views.py
    └── static/    
    ├── templates/
    └── app.py   
"""

代码

__init__.py

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__, template_folder='../templates', static_folder='../static')
app.config.from_pyfile('./settings.py')
db = SQLAlchemy(app)

from .views import bp_user

app.register_blueprint(bp_user)

models.py

from . import db


class User(db.Model):
    __tablename__ = 'user'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    name = db.Column(db.String(32), index=True, nullable=False)
    email = db.Column(db.String(32), unique=True)

    def __str__(self):
        return self.name

    def __repr__(self):
        return self.name

settings.py

DEBUG = True
SQLALCHEMY_DATABASE_URI = "mysql+pymysql://root:123456@127.0.0.1:3306/sqlalchemy01?charset=utf8"
SQLALCHEMY_POOL_SIZE = 5
SQLALCHEMY_POOL_TIMEOUT = 30
SQLALCHEMY_POOL_RECYCLE = -1
# 追踪对象的修改并且发送信号
SQLALCHEMY_TRACK_MODIFICATIONS = False

views.py

from flask import Blueprint, jsonify
from .models import User
from . import db

bp_user = Blueprint('user', __name__)


@bp_user.get('/')
def index():
    res = db.session.query(User).where(User.id > 1).all()
    l = []
    for i in res:
        l.append({'name': i.name, 'email': i.email})
    return jsonify({'code': 100, 'msg': '成功', 'results': l})

app.py

from src import app

if __name__ == '__main__':
    app.run()

9 flask-migrate使用

安装

pip3 install Flask-Migrate --upgrade

使用

__init__.py

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from flask_migrate import Migrate

app = Flask(__name__, template_folder='../templates', static_folder='../static')
app.config.from_pyfile('./settings.py')
db = SQLAlchemy(app)
migrate = Migrate(app, db)

from .views import bp_user

app.register_blueprint(bp_user)

初始化

flask db init

生成迁移文件

 flask db migrate

迁移记录生效------同步到真正的数据库

flask db upgrade

视图里要调用表模型

10 django操作原生sql

	# 方式一：
    from django.db import connection
    cur=connection.cursor() 
    cur.execute('select distinct id, book_name from test_book')
    print(cur.fetchall())
    cur.close()
    
    # 方式二：
    # 原生sql，方式一，跟对象做映射：
	book_list=Book.objects.raw('select id,name,price,publish from app01_book where id =2')
    # print(book_list) # RawQuerySet
    # for book in book_list:
    #     print(book.name)

# obj_list = User.objects.raw('select id,name,price,publish from app01_book where id =2')
# print(obj_list)  # RawQuerySet
# for obj in obj_list:
#     print(obj.name)
#     print(obj.price)