1.通过pymysql方式测试mysql数据库连接
# -*- coding: utf-8 -*-
"""
Created on Tue Jun 27 10:39:14 2017
@author: Administrator
"""
from flask import Flask,request,redirect
import pandas as pd
import pymysql
app = Flask(__name__)
#连接mysql数据库
dbcon = pymysql.connect(
host="localhost",
user="root",
password="123456",
db = "flask",
#port=3306,
charset='utf8mb4'
)
@app.route("/")
def hello():
return "Welcome to Python Flask App!"
@app.route("/Authenticate")
def Authenticate():
#从数据表读取数据
sql = "select * from user"
#利用pandas模块导入数据库
data = pd.read_sql(sql,dbcon)
print(data)
if data is None:
return "用户名或密码错误!"
else:
return "登录成功!"
if __name__ == "__main__":
app.run()
运行结果如下图所示:
此时,后台数据变化如下图所示:
当然,上述操作只是实现了基本的mysql数据库查询操作。后面将展示如何在前端查询数据库内容。
2.通过flask-mysql方式查询mysql数据库
上面是通过pymysql方式连接mysql数据库,但在创建flask项目时可能会用到另外一种flask-mysql的方式来连接mysql数据库。下面是flask-mysql方式连接mysql数据库的代码:
from flask import Flask
from flaskext.mysql import MySQL
app = Flask(__name__)
mysql = MySQL()
app.config['MYSQL_DATABASE_USER'] = 'root'
app.config['MYSQL_DATABASE_PASSWORD'] = '123456'
app.config['MYSQL_DATABASE_DB'] = 'blog'
app.config['MYSQL_DATABASE_HOST'] = 'localhost'
mysql.init_app(app)
#连接数据库
connect = mysql.connect()
cursor = connect.cursor()
#执行查询操作
cursor.execute("SELECT * from User")
data = cursor.fetchall()
print(data)
#插入语句
sql = "INSERT INTO `User` (`username`,`password`) VALUES (%s,%s)"
username = 'test'
password = '12345678'
#data = ('test', '12345678')
#执行插入操作
cursor.execute(sql, (username, password))
#再次执行查询操作
cursor.execute("SELECT * from User")
data2 = cursor.fetchall()
#提交数据库操作,将数据存入表中
connect.commit()
print(data2)
执行结果如下图所示: