pymysql的使用

from DBUtils.PooledDB import PooledDB, SharedDBConnection
import pymysql


class Config(object):
    DEBUG = False
    TESTING = False
    SECRET_KEY = 'asdfghj'
    DATABASE_URL = ''
    SALT = b'asdfg12sd'
    MAX_CONTENT_LENGTH = 1024 * 1024 * 7

    POOL = PooledDB(
        creator=pymysql,  # 使用链接数据库的模块
        maxconnections=6,  # 连接池允许的最大连接数，0和None表示不限制连接数
        mincached=2,  # 初始化时，链接池中至少创建的空闲的链接，0表示不创建
        maxcached=5,  # 链接池中最多闲置的链接，0和None不限制
        maxshared=3,
        # 链接池中最多共享的链接数量，0和None表示全部共享。PS: 无用，因为pymysql和MySQLdb等模块的 threadsafety都为1，所有值无论设置为多少，_maxcached永远为0，所以永远是所有链接都共享。
        blocking=True,  # 连接池中如果没有可用连接后，是否阻塞等待。True，等待；False，不等待然后报错
        maxusage=None,  # 一个链接最多被重复使用的次数，None表示无限制
        setsession=[],  # 开始会话前执行的命令列表。如：["set datestyle to ...", "set time zone ..."]
        ping=0,
        # ping MySQL服务端，检查是否服务可用。# 如：0 = None = never, 1 = default = whenever it is requested, 2 = when a cursor is created, 4 = when a query is executed, 7 = always
        host='127.0.0.1',
        port=3306,
        user='root',
        password='123456',
        database='s9',
        charset='utf8'
    )


class ProductionConfig(Config):
    DATABASE_URL = ''


class DevelopmentConfig(Config):
    TESTING = True

import pymysql

from settings import Config


def connect():
    conn = Config.POOL.connection()
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
    return conn,cursor


def connect_close(conn,cursor):
    cursor.close()
    conn.close()


def fetch_all(sql, args):
    conn,cursor = connect()

    cursor.execute(sql, args)
    record_list = cursor.fetchall()
    connect_close(conn, cursor)

    return record_list


def fetch_one(sql, args):
    conn, cursor = connect()
    cursor.execute(sql, args)
    result = cursor.fetchone()
    connect_close(conn, cursor)

    return result


def insert(sql, args):
    conn, cursor = connect()
    row = cursor.execute(sql, args)
    conn.commit()
    connect_close(conn, cursor)
    return row

from flask import Blueprint, redirect, session, Flask, request, render_template
import os
import uuid
from ..utils import helper
import shutil

ind = Blueprint('ind', __name__)


@ind.before_request
def process_request():
    if not session.get('user_info'):
        return redirect('/login')
    return None


@ind.route('/layout')
def layout():
    return render_template('layout.html')


@ind.route('/zxtu/<int:nid>')
def zxtu(nid):
    record_list = helper.fetch_all('SELECT id,line,ctime FROM record where user_id=%s', (nid,))
    return render_template('zxtu.html',record_list=record_list)


@ind.route('/home')
def home():
    return render_template('home.html')


@ind.route('/user_list')
def user_list():
    data_list = helper.fetch_all('SELECT id,user,nickname FROM userinfo',[])
    return render_template('user_list.html', data_list=data_list)

@ind.route('/detail/<int:nid>')
def detail(nid):
    record_list = helper.fetch_all('SELECT id,line,ctime FROM record where user_id=%s',(nid,))
    return render_template('detail.html', record_list=record_list)


@ind.route('/upload',methods=['GET', 'POST'])
def upload():
    if request.method == 'GET':
        return render_template('upload.html')
        # 获取代码文件
    file = request.files.get('code_file')
    # 对文件名进行切割
    path, etx = file.filename.rsplit('.', maxsplit=1)
    # 判断是否是压缩文件
    if etx != 'zip':
        return '上传文件必须是zip压缩文件'
    # 动态设置服务器上传文件路径
    upload_path = os.path.join('files', str(uuid.uuid4()))
    # 解压上传文件的目录
    shutil._unpack_zipfile(file.stream, upload_path)

    total_num = 0
    for (dirpath, dirnames, filenames) in os.walk(upload_path):
        for filename in filenames:
            file_path = os.path.join(dirpath, filename)
            file_path_etx = file_path.rsplit('.', maxsplit=1)

            if len(file_path_etx) != 2:
                continue
            if file_path_etx[1] != 'py':
                continue

            line_num = 0
            with open(file_path, mode='rb') as f:
                for line in f:
                    if line.strip().startswith(b'#'):
                        continue
                    line_num += 1
            total_num += line_num
            print(22222222222222,total_num)

    import datetime
    # 获取当前时间
    ctime = datetime.date.today()
    # print(total_num, ctime, session['user_info']['id'])
    data = helper.fetch_one('select id from record where ctime=%s and user_id=%s',(ctime,session['user_info']['id']))
    if data:
        return '今天已经上传'
    helper.insert('insert into record(line,ctime,user_id)values(%s,%s,%s)',(total_num,ctime,session['user_info']['id']))
    return '上传成功'