python之mysql增删查改

安装mysql库

        在终端执行   pip install PyMySQL，安装python的mysql库

定义db链接

def connect():
    '''
    mysql 连接
    :return: db
    '''
    db = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='123456', database='v4aoy', charset='utf8')
    return db

select

查询分两类，有参和无参

def query(sql):
    '''
    query data
    :param sql: sql
    :return: result
    '''
    db = connect()
    cursor = db.cursor()
    cursor.execute(sql)
    result = cursor.fetchall()
    print(cursor._executed)
    cursor.close()
    db.close()
    return result


def queryByVal(sql, val):
    '''
    query data
    :param sql: sql
    :return: result
    '''
    db = connect()
    cursor = db.cursor()
    cursor.execute(sql,val)
    result = cursor.fetchall()
    print(cursor._executed)
    cursor.close()
    db.close()
    return result

测试代码：

if __name__ == '__main__':
    try:
        # test1
        querySql = 'select * from v_student;'
        tuple = query(querySql)

        for index in range(len(tuple)):
            print(tuple[index])

        # test2
        querySql = "select * from v_student where stu_name like %s and version = %s"
        val = ('python%', 0)
        tuple = queryByVal(querySql, val)
        print(tuple)

update

def update(sql, val):
    '''
    update mysql data
    :param sql: query sql
    :return:count  update row
    '''
    db = connect()
    cursor = db.cursor()
    cursor.execute(sql, val)
    print(cursor._executed)
    db.commit()
    cursor.close()
    db.close()
    return cursor.rowcount

        # test3 update
        updateSql = "update v_student set stu_name = %s where stu_id=%s"
        val = ('111', '8')
        result = update(updateSql, val)
        print('update {0} rows'.format(result))

        updateSql = "update v_student set stu_name = %s where stu_number like %s"
        val = ('pythonName', 'batchNumber%')
        result = update(updateSql, val)
        print('update {0} rows'.format(result))

insert

def insert(sql, val):
    '''
    insert mysql data
    :param sql: query sql
    :return:count  insert row
    '''
    db = connect()
    cursor = db.cursor()
    cursor.execute(sql, val)
    print(cursor._executed)
    db.commit()
    cursor.close()
    db.close()
    return cursor.rowcount


def batchInsert(sql, vals):
    '''
    insert mysql data
    :param sql: query sql
    :return:count  insert row
    '''
    db = connect()
    cursor = db.cursor()
    cursor.executemany(sql, vals)
    print(cursor._executed)
    db.commit()
    cursor.close()
    db.close()
    return cursor.rowcount

        #test4 insert
        insertSql = 'insert into v_student (stu_name,stu_number) values (%s, %s)'
        val = ('刘刚','n_21')
        #result = insert(insertSql, val)
        #print('insert {} rows'.format(result))

        val = (('李章', 'n_22'), ('程度', 'n_23'), ('肇庆', 'n_24'))
        #result = batchInsert(insertSql, val)
        #print('insert {} rows'.format(result))

delete

def delete(sql, val):
    '''
    delete data
    :param sql: sql
    :param val:
    :return:rows
    '''
    db = connect()
    cursor = db.cursor()
    cursor.execute(sql, val)
    print(cursor._executed)
    db.commit()
    cursor.close()
    db.close()
    return  cursor.rowcount

        # test5 delete
        deleteSql = 'delete from v_student where stu_id = %s'
        val = ('22')
        result = delete(deleteSql, val)
        print('delete {} rows'.format(result))

*** 在拼模糊查询的like语句时，碰到了些问题，sql里的占位符是%s，like的前后也可以有%，所以导致最终拼好的sql执行会出错，其他可以把sql里的%转移到参数里，python的sql模版值站位，具体可以看select与update的测试代码 ***