1 环境
python>=2.7 此处使用的版本
解释器|第三方库versionpython2.7.18clickhouse-sqlalchemy0.1.10clickhouse-driver0.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/
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/
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)
推荐阅读
发表评论