python web连接数据库模板

本文已参与「新人创作礼」活动，一起开启掘金创作之路。

连接数据库

import pymysql


def fau():
    # 链接数据库 返回链接对象
    user = 'root'
    passwd = '12345678'
    db = 'mythonetext'
    conn = pymysql.connect(host='127.0.0.1', port=3306, user=user, password=passwd, db=db, charset='utf8')

    return conn

# 查询数据库数据内容
def executeSql(cus):
    # 用游标对象中点execute（）执行sql语句
    rows = cus.execute("select * from student")
    # 返回行数
    print(rows)
    # 根据影响的行数获取记录
    rst = cus.fetchall();
    for row in rst:
        print("用户名 :" + row[1] + "年龄:" + str(row[2]))


# 往数据库添加多个值
def addSql(cus, conn):
    # 数据插入多条数据使用executemany
    cus.executemany('insert into student(stuname,stuage)values(%s,%s)', [('范冰冰', 18), ('彭于晏', 20)])
    # 提交数据
    conn.commit()


# 删除数据库中的值
def DelSql(cus,conn,id):
    conn = fau()  # 获取数据库对象
    cus = conn.cursor()  # 获取游标对象
    cus.execute("delete from student where stuid=%s", (id))

    conn.commit()
# 修改数据库值
def updateSql(cus,conn,id,setid,name):
    cus.execute("update student set stuname=%s ,stuage=%s where stuid=%s",(name,setid,id))
    conn.commit

if __name__ == '__main__':

    conn = fau() # 获取数据库对象
    cus = conn.cursor()  # 获取游标对象
    # executeSql(cus) # 数据库查询
    # addSql(cus, conn) # 添加数据库内容
    DelSql(cus,conn,11) #删除制定数据
    #updateSql("财产",20,4)

迭代器

# yield 迭代循环
# 他返回是一个生成器跟 retun 不一样
def getmsg():
    for i in range(0,3):
        yield i

if __name__ == '__main__':
    result =getmsg();
    for j in result:
        print(j)

yimsql

import pymysql
import contextlib  # 自动连接关闭


class DBHelper():
    def __init__(self):  # 构造方法
        pass  # 表示空 无值

    # 定义上下文管理机制
    @contextlib.contextmanager
    def cursor(self):
        user = 'root'
        passwd = '12345678'
        db = 'mythonetext'
        # 获取数据库对象
        conn = pymysql.connect(host='127.0.0.1', port=3306, user=user, password=passwd, db=db, charset='utf8')
        # 获取游标 元组形式
        # cus = conn.cursor()

        # 获取游标，字典形式
        cus = conn.cursor(cursor=pymysql.cursors.DictCursor)
        try:
            yield cus  # 返回一个生成器对象

            conn.commit()
        except Exception as e:
            print(e)
            # 出现异常回滚错误之前点操作
            conn.rollback()
        finally:
            # 关闭游标
            cus.close()
            conn.close()
    # 查询数据
    def querAll(self,cus,sql,params=None):
        cus.execute(sql,params)
        return cus.fetchall()
    # 添加，删除，修改
    def execute(self,cus,sql,params=None):
        result =cus.execute(sql,params)
        return result

if __name__ == '__main__':
    # 创建DBHelper 对象
    dbhelper =DBHelper();
    # as是别名点意思
    with dbhelper.cursor() as cus:
        rsult =dbhelper.querAll(cus,"select * from student")
        for i in rsult :
            print(i)

app连接

from flask import Flask
from utils import yiMYSQL
from utils.yiMYSQL import DBHelper

app = Flask(__name__)


@app.route('/')
def hello_world():
    #创建连接数据库的对象
    db = DBHelper()
    with db.cursor() as cus:
        result =db.querAll(cus,"select * from student")


    return str(result[0])


if __name__ == '__main__':
    app.run()