flask使用sqlalchemy对sqlite数据库进行增删改查

flask使用sqlalchemy对sqlite数据库进行增删改查代码如下： 

#!/usr/bin/python
# -*- coding: UTF-8 -*-
"""
@author:ZSW
@file:test4.py
@time:2020/04/27
"""

from flask import Flask
import json
from sqlite3test.conf.GetConfParams import GetConfParams
import pandas as pd
from sqlite3test.tojson import queryToDict

ConfParams = GetConfParams()
logger = ConfParams.logger      # 日志记录函数

app = Flask(__name__)

from flask_sqlalchemy import  SQLAlchemy

###对sqlalchemy配置
#用于连接数据的数据库
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///E:/sqlite/sqlite-tools-win32-x86-3310100/study.db'
#如果设置成 True (默认情况)，Flask-SQLAlchemy 将会追踪对象的修改并且发送信号。这需要额外的内存， 如果不必要的可以禁用它。
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = True


###定义数据类型
db = SQLAlchemy(app)
class Student(db.Model):
    __tablename__ = "student"
    id = db.Column(db.Integer,primary_key=True)
    name = db.Column(db.String(32))
    age = db.Column(db.String(32))
    gender = db.Column(db.String(32))
    classes = db.Column(db.String(32))
    # def __repr__(self):      #通过__repr__函数，可以对想要打印的内容进行设置
    #     return {'name':'{}'.format(self.name)}

db.create_all() #创建所有表
# db.drop_all() #删除所有表

###对数据库进行操作
#增
def insertData():
    try:
        student = Student(name='lee',age=18,gender='man',classes='python')  #增加的内容
        db.session.add(student)
        db.session.commit()
    except Exception as e:
        logger.error(e)
        res = {'code': 0, 'message': '新增数据报错'}
        return json.dumps(res, ensure_ascii=False, indent=4)

#批量增加
def patchinsertData():
    try:
        student1 = Student(name='张三',age=18,gender='male',classes='java')
        student2 = Student(name='李四',age=19,gender='female',classes='c++')
        student3 = Student(name='王五',age=22,gender='male',classes='php')
        student4 = Student(name='赵六',age=25,gender='female',classes='matalab')
        db.session.add_all([student1,student2,student3,student4])
        db.session.commit()
    except Exception as e:
        logger.error(e)
        res = {'code': 0, 'message': '批量新增数据报错'}
        return json.dumps(res, ensure_ascii=False, indent=4)


#删
def deleteData():
    try:
        student = Student.query.get(2) #删除id为2的数据行
        db.session.delete(student)
        db.session.commit()
    except Exception as e:
        logger.error(e)
        res = {'code': 0, 'message': '删除数据报错'}
        return json.dumps(res, ensure_ascii=False, indent=4)


#改
def updateData():
    try:
        student = Student.query.get(7)  # 删除id为2的数据行
        student.name = '李七'
        student.age = 24
        student.gender = 'male'
        db.session.commit()
    except Exception as e:
        logger.error(e)
        res = {'code': 0, 'message': '数据修改报错'}
        return json.dumps(res, ensure_ascii=False, indent=4)


#查
def selectData():
    try:
        student = Student.query.all()
        print(student)  #print(student)的结果返回的是name是因为在创建数据库的时候，定义了__repr__(self):return self.name
        for s in student:
            print(s.name)
    except Exception as e:
        logger.error(e)
        res = {'code': 0, 'message': '数据查询报错'}
        return json.dumps(res, ensure_ascii=False, indent=4)


#删除所有数据
def deleteallData():
    try:
        student = Student.query.all()
        print(student)  #print(student)的结果返回的是name是因为在创建数据库的时候，定义了__repr__(self):return self.name
        for s in student:
            db.session.delete(s)  #全部删除
        db.session.commit()
    except Exception as e:
        logger.error(e)
        res = {'code': 0, 'message': '删除所有数据报错'}
        return json.dumps(res, ensure_ascii=False, indent=4)


#查询数据，相关操作测试
def checkData():
    try:
        #法一：取数据转字典
        student = (db.session.query(Student.name,Student.id,Student.age).filter(Student.age==18).all())
        print(student)
        res = [dict(zip(r.keys(), r)) for r in student] #转字典
        print(res)
        #法二：
        # student = Student.query.filter_by(age=18)
        # # print(student) #这里student返回的是SQL语句
        # for s in student:
        #     # print(s)
        #     result = queryToDict(s)
        #     print(result)
        #     # print(s.id, s.name, s.classes)
    except Exception as e:
        logger.error(e)
        res = {'code': 0, 'message': '数据查询报错'}
        return json.dumps(res, ensure_ascii=False, indent=4)

#相关操作：
    """
    student = Student.query.filter_by(age = 18) #按条件过滤查询
    student = Student.query.get(1) #自动以主键查询
    student = Student.query.group_by("age") #按照组
    #排序
    student = Student.query.order_by(Student.age)
    #倒序排序
    student = Student.query.order_by(Student.age.desc()) #按照组
    """


if __name__ == '__main__':
    checkData()

 日志函数 GetConfParams.py

import logging
import logging.config
import os

class GetConfParams:
    PATH = os.getcwd()  # 获取上级目录
    logging.config.fileConfig(PATH + '/conf/logging.conf')

    def __init__(self):
        self.logger = logging.getLogger('root')
 

sqlalchemy查询出来的实体类结果转字典文件 to_json.py 

#!/usr/bin/python
# -*- coding: UTF-8 -*-
"""
@author:ZSW
@file:tojson.py
@time:2020/04/27
"""
#flask_sqlalchemy 查询结果转dict 终极解决方案

from datetime import datetime as cdatetime  # 有时候会返回datatime类型

from datetime import date, time
from flask_sqlalchemy import Model
from sqlalchemy.orm.query import Query
from sqlalchemy import DateTime, Numeric, Date, Time  # 有时又是DateTime


def queryToDict(models):
    if (isinstance(models, list)):
        if (isinstance(models[0], Model)):
            lst = []
            for model in models:
                gen = model_to_dict(model)
                dit = dict((g[0], g[1]) for g in gen)
                lst.append(dit)
            return lst
        else:
            res = result_to_dict(models)
            return res

    else:
        if (isinstance(models, Model)):  #####**************第1步*************************
            gen = model_to_dict(models)
            dit = dict((g[0], g[1]) for g in gen)
            return dit                  #####第4步  返回结果
        else:
            res = dict(zip(models.keys(), models))
            find_datetime(res)
            return res

# 当结果为result对象列表时，result有key()方法
def result_to_dict(results):
    res = [dict(zip(r.keys(), r)) for r in results]
    # 这里r为一个字典，对象传递直接改变字典属性
    for r in res:
        find_datetime(r)
    return res


def model_to_dict(model):  # 这段来自于参考资源   #####第2步
    for col in model.__table__.columns:
        if isinstance(col.type, DateTime):
            value = convert_datetime(getattr(model, col.name))
        elif isinstance(col.type, Numeric):
            value = float(getattr(model, col.name))
        else:
            value = getattr(model, col.name)  #####第3步
        yield (col.name, value)


def find_datetime(value):
    for v in value:
        if (isinstance(value[v], cdatetime)):
            value[v] = convert_datetime(value[v])  # 这里原理类似，修改的字典对象，不用返回即可修改


def convert_datetime(value):
    if value:
        if (isinstance(value, (cdatetime, DateTime))):
            return value.strftime("%Y-%m-%d %H:%M:%S")
        elif (isinstance(value, (date, Date))):
            return value.strftime("%Y-%m-%d")
        elif (isinstance(value, (Time, time))):
            return value.strftime("%H:%M:%S")
    else:
        return ""

参考：https://blog.csdn.net/weixin_44251004/article/details/89388538

          https://www.cnblogs.com/eating-gourd/p/9997751.html