Python之MySQL基本操作

import pymysql

连接数据库

# 打开数据库链接
conn = pymysql.connect('localhost', user='root', password='root', db='testdb') 
# 游标
cursor = conn.cursor()   

创建/删除数据库

# # 创建数据库
# cursor.execute("CREATE DATABASE IF NOT EXISTS PythonDB DEFAULT CHARSET UTF8MB4")   

# # 删除数据库
# cursor.execute("DROP DATABASE PythonDB")   

创建表

# 创建 user表
cursor.execute('DROP TABLE IF EXISTS user')
sql = """CREATE TABLE IF NOT EXISTS `user` (
        `id` INT NOT NULL AUTO_INCREMENT, 
        `name` VARCHAR(255) NOT NULL,
        `age` INT(11) NOT NULL,
        PRIMARY KEY (`id`))
        ENGINE=InnoDB DEFAULT CHARSET=UTF8MB4"""
cursor.execute(sql)

0

插入数据

# 插入单条
# way 1
inserted_data = cursor.execute("INSERT INTO user (id, name, age) VALUES (1, 'zhf', 25)")
# way 2 
sql = "INSERT INTO user VALUES(%s, %s, %s)"
cursor.execute(sql, (2, 'wjq', 25))
# 插入多条
sql = "INSERT INTO user VALUES(%s, %s, %s)"
cursor.executemany(sql, [(3, 'tom', 25), (4, 'Tim', '26'), (5, 'K20', 20)])

conn.commit()

查询数据

# 查询单条, 返回 tuple
cursor.execute("SELECT * FROM user")
res = cursor.fetchone()      
print(res)

(1, 'zhf', 25)

# 查询多条, 返回 ((), (), ..., ())
cursor.execute("SELECT * FROM user")
res = cursor.fetchmany(2)    
print(res)

((1, 'zhf', 25), (2, 'wjq', 25))

# 查询所有数据, 返回 ((), (), ..., ())
cursor.execute("SELECT * FROM user")
res = cursor.fetchall()       
for r in res:
    print(r)

(1, 'zhf', 25)
(2, 'wjq', 25)
(3, 'tom', 25)
(4, 'Tim', 26)
(5, 'K20', 20)

更新数据

# 更新单条
cursor.execute("SELECT * FROM user WHERE name='zhf'")
res = cursor.fetchone()
print('更新前数据:', res)

cursor.execute("UPDATE user SET age=24 WHERE name='zhf'")   # 跟新数据
conn.commit()

cursor.execute("SELECT * FROM user WHERE name='zhf'")
res = cursor.fetchone()
print('更新后数据:', res)

更新前数据: (1, 'zhf', 25)
更新后数据: (1, 'zhf', 24)

# 更新多条
cursor.execute("SELECT * FROM user WHERE name in ('zhf', 'wjq')")
for res in cursor.fetchall():
    print('更新前数据:', res)
    
sql = "UPDATE user SET age=%s WHERE name=%s"
cursor.executemany(sql, [(18, 'wjq'), (25, 'zhf')])    # 更新数据
conn.commit()

cursor.execute("SELECT * FROM user WHERE name in ('zhf', 'wjq')")
for res in cursor.fetchall():
    print('更新后数据:', res)

更新前数据: (1, 'zhf', 24)
更新前数据: (2, 'wjq', 25)
更新后数据: (1, 'zhf', 25)
更新后数据: (2, 'wjq', 18)

删除数据

# 删除单条
cursor.execute("DELETE FROM user WHERE id=5")
conn.commit()

print('删除后数据:')
cursor.execute("SELECT * FROM user")
for res in cursor.fetchall():
    print(res)

删除后数据:
(1, 'zhf', 25)
(2, 'wjq', 18)
(3, 'tom', 25)
(4, 'Tim', 26)

# 删除2条数据
sql="DELETE FROM user WHERE name=%s"
cursor.executemany(sql, [('tom'),('Tim')])
conn.commit()

print('删除后数据:')
cursor.execute("SELECT * FROM user")
for res in cursor.fetchall():
    print(res)

删除后数据:
(1, 'zhf', 25)
(2, 'wjq', 18)

# 删除所有数据
cursor.execute("DELETE FROM user")
conn.commit()

print('删除所有数据:')
cursor.execute("SELECT * FROM user")
res = cursor.fetchall()
print(res)

删除所有数据:
()

cursor.close()  # 关闭游标
conn.close()    # 关闭链接