flask 链接数据库 in 查询和保存

代码

from json import JSONEncoder
from flask import Flask, request,send_from_directory
#导入第三方连接库
from flask_sqlalchemy import SQLAlchemy
import json
from datetime import datetime
import time

from flask.json import JSONEncoder as _JSONEncoder
from flask import Flask as _Flask
from datetime import date
# Flask
app = Flask(__name__)


# #指定数据库连接还有库名                                                                
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://root:123456@127.0.0.1:3306/ceshi?charset=utf8'

# #指定配置用来省略提交操作
app.config['SQLALCHEMY_COMMIT_ON_TEARDOWN'] = True


#建立数据库对象
db = SQLAlchemy(app)
 
class JSONEncoder(_JSONEncoder):
    def default(self, o):
        if hasattr(o, 'keys') and hasattr(o, '__getitem__'):
            return dict(o)
        if isinstance(o, date):
            return o.strftime('%Y-%m-%d %H:%M:%S')
        return json.JSONEncoder.default(self, o)



class Flask(_Flask):
    json_encoder = JSONEncoder



# 声明入库log表
class ProcessLog(db.Model):
    # 设置表名，不设置表名就是类名。
    __tablename__ = "process_log"
    id = db.Column(db.Integer, primary_key=True)
    successStartTime = db.Column(db.DateTime)
    successEndTime = db.Column(db.DateTime)
    fileName = db.Column(db.String(10), nullable=False)
    failureStartTime = db.Column(db.DateTime)
    failureEndTime = db.Column(db.DateTime)
    logMsg = db.Column(db.String(255), nullable=False)
    creUser = db.Column(db.String(10), nullable=False) # 后续优化
    fileTable = db.Column(db.Integer) # 关联id
    def keys(self):
        return ['id', 'successStartTime',"successEndTime",'fileName','failureStartTime','failureEndTime','failureLog','creUser','fileTable']

    def __getitem__(self, item):
        return getattr(self, item)

# 声明文件表
class FileTable(db.Model):
    # 设置表名，不设置表名就是类名。
    __tablename__ = "file_table"
    id = db.Column(db.Integer, primary_key=True)
    fileName = db.Column(db.String(200), nullable=False)
    originalName = db.Column(db.String(200), nullable=False)
    def keys(self):
        return ['id', 'fileName','originalName']

    def __getitem__(self, item):
        return getattr(self, item)

class InvoiceInformation(db.Model):
    # 设置表名，不设置表名就是类名。
    __tablename__ = "invoice_information"
    id = db.Column(db.Integer, primary_key=True)
    fileId =db.Column(db.Integer)
    jsonStr = db.Column(db.String(200), nullable=False)
    pdfName = db.Column(db.String(200), nullable=False)
    results = db.Column(db.String(200), nullable=False)
    pdfPage = db.Column(db.Integer)
    validation = db.Column(db.Integer)
    detailedData = db.Column(db.String(200), nullable=False)

    def keys(self):
        return ['id', 'fileId', 'jsonStr', 'pdfName', 'results', 'pdfPage', 'validation','detailedData']

    def __getitem__(self, item):
        return getattr(self, item)

in查询和普通查询

# in查询
def downLoadAll():
    get_data = request.args.to_dict()
    id = get_data.get("id")
    details = InvoiceInformation.query.filter(InvoiceInformation.fileId.in_(ids)).all()
    return {'code': '200', 'msg': '处理成功'}


# 普通查询
def returnData():
    id = request.form.get("id")
    releList = FileTable.query.filter_by(id=id).first()
    newPath = str(filePath+"{}/{}").format(releList['fileName'],releList['originalName'])
    print(newPath)
    return {'code': '200', 'msg': '处理成功'}

# 使用 and 或者 or 查询
from sqlalchemy import or_, and_
failureCount = InvoiceInformation.query.filter(and_(InvoiceInformation.fileId.in_(fileId), InvoiceInformation.validation == 0)).count()

保存

nowEnd = datetime.now()
property = ProcessLog(fileName = fileName,failureStartTime = nowStart,failureEndTime = nowEnd,logMsg="文件后缀名出现错误",creUser="sys")
db.session.add(property)