flask--基础知识点--5--clickhouse_sqlalchemy的使用

1 环境

python>=2.7
此处使用的版本

解释器|第三方库	version
python	2.7.18
clickhouse-sqlalchemy	0.1.10
clickhouse-driver	0.2.0

clickhouse-sqlalchemy 依赖 clickhouse-driver，在使用pip下载clickhouse-sqlalchemy时会自动下载依赖clickhouse-driver。

2 安装

pip install clickhouse-sqlalchemy

3 demo

# -*-coding:utf-8-*-
from datetime import date, timedelta
from flask import Flask, jsonify
from flask_sqlalchemy import SQLAlchemy
from clickhouse_sqlalchemy import get_declarative_base, types, engines
from sqlalchemy import MetaData, Column, or_

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = "clickhouse://[username]:[passwd]@localhost:8123/[database_name]" # 中括号中的内容根据实际情况填写
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
app.config['SQLALCHEMY_ENGINE_OPTIONS'] = {'pool_size': 10, 'max_overflow': 20}
db = SQLAlchemy(app, session_options={"autoflush": False})
db.init_app(app)
db.engine.pool._use_threadlocal = True


class Rate(db.Model):
    __tablename__ = 'rate'  # 表名默认是类名的小写，当指定了__tablename__后，则表名就为该变量的值
    day = Column(types.Date, primary_key=True)
    value = Column(types.Int32)

    __table_args__ = (
        engines.Memory(),  # 注意引擎写项目中需要使用的引擎
    )
db.create_all()


@app.route("/create")
def create():
    # 方法一：单条插入
    # _model = Rate(day=date.today(), value=50)
    # db.session.add(_model)
    # db.session.commit()  # 必须有commit()

    # 方法二：批量插入
    # _model1 = Rate(day=date.today(), value=50)
    # _model2 = Rate(day=date.today() + timedelta(1), value=51)
    # _model3 = Rate(day=date.today() + timedelta(2), value=52)
    # models = [_model1, _model2, _model3]
    # db.session.add_all(models)
    # db.session.commit()  # 必须有commit()

    # 方法三：批量插入
    today = date.today()
    rates = [
        {'day': today - timedelta(i), 'value': 200 - i}
        for i in range(100)
    ]
    db.session.execute(Rate.__table__.insert(), rates)
    return jsonify(status=200, message="ok")


@app.route("/retrieve")
def retrieve():
    # 方法一：
    # data = db.session.query(Rate.day, Rate.value).filter().all()  # 获取[(day0, value0), ...] 

    # 方法二：
    _models = db.session.query(Rate).filter().all()  # 获取model列表
    data = [(_model.day, _model.value) for _model in _models]

    # 也可以使用 filter|filter_by
    # db.session.query(a, b) 参数只能是表字段，不能是表名
    # today = date.today()
    # data = db.session.query(Rate.day, Rate.value).filter(Rate.day > today - timedelta(20)).all()
    return jsonify(status=200, message="ok", data=data)


@app.route("/update/<value>")
def update(value):
    # 方法一：
    _model = db.session.query(Rate).filter_by(value=value).one()
    _model.value = int(value) - 1
    db.session.commit()  # 必须有commit()

    # 方法二：
    # _query = db.session.query(Rate).filter_by(value=value)
    # _query.update({"value": int(value) - 1}, synchronize_session=False)
    # db.session.commit()  # 必须有commit()

    # 方法三：
    # from sqlalchemy import update
    # data = {"value": int(value) - 1}
    # update_stmt = update(Rate).where(Rate.value == value).values(data)
    # db.session.execute(update_stmt)
    return jsonify(status=200, message="ok")


@app.route("/delete/<value>")
def delete(value):
    # 方法一：删除单条或多条数据
    # db.session.query(Rate).filter_by(value=value).delete()  # 单条数据删除
    # db.session.query(Rate).filter(or_(Rate.value == value, Rate.value == int(value)+1)).delete()  # 批量删除

    # 方法二：删除单条数据
    _model = db.session.query(Rate).filter_by(value=value).first()
    db.session.delete(_model)  # 删除单条数据
    db.session.commit()  # 必须有commit()

    # 方法三：删除单条或多条数据
    # from sqlalchemy import delete
    # delete_stmt = delete(Rate).where(Rate.value == value)
    # db.session.execute(delete_stmt)
    return jsonify(status=200, message="ok")


if __name__ == "__main__":
    app.run(host="0.0.0.0", port=80)