1.首先创建一个表
import pymysql
# 连接数据库
connect = pymysql.connect(host='127.0.0.1', user='root', password='')
cursor = connect.cursor() #创建一个游标,类似于一个帮助我们执行命令的工具
cursor.execute("use day01db") # 选择一个你保存的数据库
sql = """
create table books(
id int not null primary key auto_increment,
name varchar(128),
price int not null
)default charset=utf8; #不能写utf-8
""" #写mysql命令
cursor.execute(sql)
connect.commit() #提交内容
2.向表中填写内容
cursor.execute("insert into books(name,price) values('十四行诗','14')")
cursor.execute("insert into books(name,price) values('浮士德','20')")
cursor.execute("insert into books(name,price) values('平凡的世界','17')")
connect.commit()
这时候我们可以使用select * from books
查看内容是否插入
cursor.execute('use day01db')
cursor.execute('show tables')
cursor.execute('select * from books')
resp = cursor.fetchall()
print(resp)
3.这是时候我们就可以导入flask框架了
#获取数据库的内容
from flask import Flask, render_template, redirect, request
import pymysql
connect = pymysql.connect(host='127.0.0.1', user='root', password='')
cursor = connect.cursor()
cursor.execute('use day01db')
cursor.execute('show tables')
cursor.execute('select * from books')
resp = cursor.fetchall()
cursor.close()
connect.close()
4.在html中输出
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>index</title>
</head>
<body>
<table border="1">
<thead>
<tr>
<td>ID</td>
<td>书名</td>
<td>价格</td>
<td>操作</td>
</tr>
</thead>
<tbody>
{% for i in data %}
<tr>
<td>{{i[0]}}</td>
<td>{{i[1]}}</td>
<td>{{i[2]}}</td>
<td>
<a href="/edit?nid={{i[0]}}">修改</a>
<a href="/dele?nid={{i[0]}}">删除</a>
</td>
</tr>
{% endfor %}
</tbody>
</table>
</body>
</html>
效果如下