數(shù)據(jù)表的增刪改查(在flask_sqlalchemy 下進(jìn)行)

可參考之前文章：python元類

# 數(shù)據(jù)表 的增刪改查


from flask import Flask
from flask_sqlalchemy import SQLAlchemy


app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://root:root@localhost:3306/flask_demo?charset=utf8'
# app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///users.db'  # 采用 sqlite3 內(nèi)存數(shù)據(jù)庫方式

db = SQLAlchemy(app)

class User(db.Model):
    __tablename__ = 'user_demo' #表名

    # 列名 = 值
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)  #  primary_key 主鍵  autoincrement 自動(dòng)增長
    username = db.Column(db.String(64), unique=True, index=True)  # unique 不唯一
    # password = db.Column(db.String(64), unique=False)
    password = db.Column(db.String(64))


    def __repr__(self):
        return f'{self.__class__.__name__}:{self.__tablename__}'


def createTables():
    """
    創(chuàng)建 所有表結(jié)構(gòu)
    :return:
    """
    db.create_all()
    return '數(shù)據(jù)表創(chuàng)建成功'

# 向數(shù)據(jù)表插入一條數(shù)據(jù)
def insertOne(tabObj, username):
    """
    向數(shù)據(jù)表插入一條數(shù)據(jù);  tabObj 數(shù)據(jù)表對象
    :param tabObj:
    :param username:
    :return:
    """
    db.session.add(tabObj)
    db.session.commit()
    print(f'用戶:{username}數(shù)據(jù)插入成功')

# 刪除一條數(shù)據(jù)
def deleteOne(tabObj, username):
    """
    向數(shù)據(jù)表刪除一條數(shù)據(jù);  tabObj 數(shù)據(jù)表對象
    :param tabObj:
    :param username:
    :return:
    """
    tabObj.query.filter_by(username=username).delete()
    return f'用戶: {username} 信息刪除成功'

# 修改一條數(shù)據(jù)
def updateOne(tabName, colName, newVal, oldVal):
    """
    向數(shù)據(jù)表修改一條數(shù)據(jù);  tabObj 數(shù)據(jù)表對象
    :param tabObj:
    :return:
    """
    # user = tabObj.query.get(1)
    # print(user.username)
    # user.username = 'xiaoli'
    # db.session.commit()

    # UPDATE 表名稱 SET 列名稱 = 新值 WHERE 列名稱 = 某值
    sql = f'update {tabName} set {colName}="{newVal}" where {colName}="{oldVal}"'
    db.session.execute(sql) #執(zhí)行后， 無返回值

# 查詢一條數(shù)據(jù)
def select(tabName, id):
    sql = f'select * from {tabName} where id={id}'
    # 返回一個(gè)列表對象
    result = list(db.session.execute(sql))

    # return result[0][1]
    return result


if __name__ == '__main__':
    # print(createTables())

    # for i in range(1, 11):
    #     username = f'xiaoli{i}'
    #     user = User(username=username, password='666')
    #     insertOne(user, username)

    # updateOne('user_demo', 'username', 'xiaoli10', 'xiaoli11')
    print(select('user_demo', 6))