使用PyMysql调用Mysql数据库示例

# 导入pymysql模块
import pymysql

# 连接database
conn = pymysql.connect(
    host="localhost",
    user="root",
    password="123456",
    database="fanis",
    charset="utf8"
)

# 获取一个光标
cursor = conn.cursor()

# 创建表
sql = """
CREATE TABLE if not exists user(
    id INT auto_increment PRIMARY KEY ,
    name CHAR(10) NOT NULL UNIQUE,
    age TINYINT NOT NULL
);"""
cursor.execute(sql)

# 清空表数据
sql = "TRUNCATE TABLE user;"
cursor.execute(sql)

# 插入数据
sql = "insert into user(name, age) values('july', 14);"
cursor.execute(sql)
sql = "insert into user(name, age) values('june', 25);"
cursor.execute(sql)
sql = "insert into user(name, age) values('marin', 36);"
cursor.execute(sql)

# 查询并打印结果
sql = "select * from user;"
cursor.execute(sql)
for item in cursor.fetchall():
    print(item)

# 涉及写操作要注意提交
conn.commit()

# 关闭连接
cursor.close()
conn.close()