python操作数据库pymsql

# 0. pip install pymysql
# 1. 导入 pymysql  import pymsql
# 2. 创建一个数据库连接对象
# 3. 创建游标 cursor = conn.cursor()
# 4. SQL执行 cursor.execute(sql)
# 5. DML提交事务 conn.commit
# 6. 关闭游标 cursor.close()
# 7. 关闭连接 cursor.close()

import pymysql
def get_conn():
    """
    获取mysql的连接
    :return: 返回一个数据库连接对象
    """
    return pymysql.connect(
        host = 'localhost',
        user = 'root',
        password = '123456',
        database = 'TestDB',
        charset= 'utf8'
    )

def query_data(sql):
    """
    查询mysql数据
    :param sql:
    :return:
    """
    conn = get_conn()
    try:
        cursor = conn.cursor(pymysql.cursors.DictCursor) # 每行数据以字典的方式返回,整体是一个列表. 如果不选这种,默认返回是多维元组.
        cursor.execute(sql)
        cursor.close()
        return cursor.fetchall()

    except BaseException as e:
        print(f'操作失败,失败信息: {e}')
    finally:
        # 无论成功与否都执行如下语句.关闭连接
        conn.close()


def insert_or_update_or_delete(sql):
    """

    :param sql:
    :return:
    """
    conn = get_conn()
    try:
        cursor = conn.cursor()  #
        cursor.execute(sql)
        # DQL 执行后要提交事务.
        conn.commit()
        cursor.close()
    except BaseException as e:
        print(f'操作失败,失败信息: {e}')
    finally:
        # 无论成功与否都执行如下语句.关闭连接
        conn.close()


if __name__ == '__main__':
    sql1 = "update course_info set name = '陈二狗' where name = '张三疯';"
    insert_or_update_or_delete(sql1)
    sql2 = 'select * from course_info ;'
    data = query_data(sql2)
    for item in data:
        print(item)