python 操作mysql pymysql

import pymysql.cursors

conn = pymysql.connect(
    host="localhost",
    user="root",
    password="root",
    db="test",
    charset="utf8mb4",
    cursorclass=pymysql.cursors.DictCursor  # dict list
)


# add
def test_create_mysql():
    try:
        with conn.cursor() as cursor:
            sql = "INSERT INTO `users` (`email`, `password`) VALUES (%s, %s)"
            cursor.execute(sql, ('adleytales@126.com', 'adley1234'))

        conn.commit()
    finally:
        conn.close()


# query
def test_query_mysql():
    try:
        with conn.cursor() as cursor:
            sql = "select `id`, `password`, `email` from `users`"
            cursor.execute(sql)

        result = cursor.fetchall()
        print(result, len(result))
        print("*" * 100)

        for user in result:
            line_str = "用户id: {}, 用户邮箱: {}, 用户密码: {} ".format(user['id'], user['email'], user['password'])
            print(line_str)

    finally:
        conn.close()


# update
def test_update_mysql(uid, password):
    try:
        with conn.cursor() as cursor:
            sql = "UPDATE `users` SET `password` = %s WHERE `id` = %s"
            cursor.execute(sql, (password, uid))

        conn.commit()

    finally:
        conn.close()


# delete
def test_delete_mysql(uid):
    try:
        with conn.cursor() as cursor:
            sql = "DELETE FROM `users` WHERE `id` = %s"
            cursor.execute(sql, (uid,))

        conn.commit()
    finally:
        conn.close()


if __name__ == '__main__':
    # test_create_mysql()

    # test_query_mysql()

    # test_update_mysql('2', 'abc0000!')

    test_delete_mysql('1')