python 操作mysql pymysql

时间:2022-10-10 07:20:05


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')