上课笔记

from flask import Flask, request, url_for
import sqlite3
app = Flask(__name__)

@app.route('/')
def index():
    conn = sqlite3.connect('c:\\db.db')
    # 关键步骤: 设置 row_factory 返回字典类型
    conn.row_factory = sqlite3.Row
    c = conn.cursor()
    cursor = c.execute("SELECT * from singer")
    rows = cursor.fetchall() #获取结果集合
    if len(rows)<1:
        return '无数据'
    str="<ul>"
    for row in rows:
        str= str+f"""<li>
        {row['id']},
        {row['name']},
        {row['age']},
        {row['sex']},
        {row['area']},
        <img src='{row['img']}' style='height:48px;width:48px;' >
        </li>
"""
    str=str+"</ul>"
    conn.close()
    return str

@app.route('/login')
def login():
    form = '''
        <form action="/password" method="get">
            <input type="text" name="username" placeholder="用户名"></input>
            <input type="password" name="password" placeholder="密码"></input>
            <input type="submit" value="登录"></input>
        </form>
'''
    return form

@app.route('/password')
def password():
    username = request.args.get('username')
    password = request.args.get('password')
    conn = sqlite3.connect('c:\\db.db')
    c = conn.cursor()
    cursor = c.execute(f"SELECT * from users where userid='{username}' and pwd='{password}'")
    rows = cursor.fetchall()
    if len(rows)==1:
        return "ok"
    return "失败"

app.run(debug=True, host='0.0.0.0', port=80)

 笔记 2.0

from flask import Flask, request, url_for,render_template
import sqlite3

app = Flask(__name__)
@app.route('/')
def index():
    conn = sqlite3.connect('c:\\db.db')
    # 关键步骤：设置 row_factory  返回字典类型
    conn.row_factory = sqlite3.Row
    c = conn.cursor()
    cursor = c.execute("SELECT *  from singer")
    rows = cursor.fetchall() #获取结果集合
    if len(rows)<1:
        return '无数据'
    str="<ul>"
    for row in rows:
        str= str+f"""<li>
        {row['id']},
        {row['name']},
        {row['age']},
        {row['sex']},
        {row['area']},
        <img src='{row['img']}' style='height:48px;width:48px;' >
        </li>
        """
    str=str+"</ul>"
    conn.close()
    return str

@app.route('/init')
def init():
    list=['CREATE TABLE IF NOT exists users(   ID INT,   name  CHAR(50) ,   age   INT,   userid char(50),   pwd char(50),   del int   )']
    list.append('CREATE TABLE IF NOT exists singer(   ID INT,   name  CHAR(50) ,age INT,sex char(10),   area char(50),   img text )')
    list.append('CREATE TABLE IF NOT exists songs(ID INT,name  CHAR(50) ,url text ,singer int)')
    list.append('CREATE TABLE IF NOT exists songsmenu(   ID INT,   userid int,   songsid int,   index_s int);')
    list.append("""insert into users values(1,'宋阳',14,'songyang','123456',0);""")
    list.append("""INSERT INTO singer (ID, name, age, sex, area, img) VALUES (1, '周杰伦', 14, '男', '台湾', 'https://c1.kgimg.com/custom/240/20251125/20251125182917808816.jpg'); """)
    list.append("""INSERT INTO singer (ID, name, age, sex, area, img) VALUES (3, '李荣浩', 14, '男', '内地', 'https://img0.baidu.com/it/u=1856246291,3042340795&fm=253&fmt=auto&app=120&f=JPEG?w=920&h=800'); """)
    list.append("""INSERT INTO singer (ID, name, age, sex, area, img) VALUES (2, '毛不易', 14, '男', '内地', 'https://q9.itc.cn/images01/20250807/74936defa6164fe09caa8619cfb65f02.jpeg'); """)
    list.append(""" INSERT INTO singer (ID, name, age, sex, area, img) VALUES (4, '邓紫棋', 14, '女', '台湾', 'https://q1.itc.cn/images01/20260614/ef89e43312104373857654979bff9933.jpeg');""")
    list.append("""insert into songs values(1,'稻香','http://listen.92kk.com//2019/%E4%B8%B2%E7%83%A7%E8%88%9E%E6%9B%B2/201912/20191202/%E6%9C%80%E6%96%B0%E5%A5%BD%E5%90%AC%E7%BD%91%E7%BB%9C%E4%BC%A4%E6%84%9F%E6%AD%8C%E6%9B%B2%E6%8E%A8%E8%8D%90%E9%80%9F%E9%80%92%E8%BD%A6%E8%BD%BDCD%E5%A4%A7%E7%A2%9F-%E9%85%B7%E9%9F%B3%E9%A2%86%E5%9F%9F-DJ%E5%B1%B1%E4%BB%94.mp3',1); """)
    list.append("""insert into songs values(2,'双截棍','%E4%B94.mp3',1); """)
    list.append("""insert into songs values(3,'青花瓷','%E4%B94.mp3',1); """)
    list.append("""insert into songs values(4,'烟花易冷','%E4%B94.mp3',1); """)
    list.append("""insert into songsmenu values(1,1,1,1); """)

    conn = sqlite3.connect('c:\\db.db')
    c = conn.cursor()
    for sql in list:
        cursor = c.execute(sql)
        rows = cursor.fetchall()
        print(rows)
    conn.commit()
    return "ok"
@app.route('/users')
def getusers():
    return render_template("users.html")

@app.route('/greet/<username>')
def greeting(username):
    return f'hello, {username} from ip: {request.remote_addr}'

@app.route('/login')
def login():
    form = '''
        <form action="/password" method="get">
            <input type="text" name="username" placeholder="用户名"></input>
            <input type="password" name="password" placeholder="密码"></input>
            <input type="submit" value="登录"></input>
        </form>
    '''
    return form

def get_user_list():
   
    return 'users', 'passwords'                   # 返回 用户名列表 密码列表

@app.route('/password')
def password():
    username = request.args.get('username')   
    password = request.args.get('password')  
    conn = sqlite3.connect('c:\\db.db')
    c = conn.cursor()
    cursor = c.execute(f"SELECT *  from users where userid='{username}' and pwd='{password}'")
    rows = cursor.fetchall()
    if len(rows)==1:
        return "OK"
    return "失败"

app.run(debug=True, host='0.0.0.0', port=80)