前提、sql命令–基本的增删改查

MySQL的一些常用命令可以参考我之前的博客：
https://blog.csdn.net/weixin_42081389/article/details/93788977

1、创建表

create table student(
    id int auto_increment primary key,
    name varchar(100) not null,
    sex char(1) not null,
    address varchar(100) default '郑州',
    phone varchar(11),
    birthday date
);

2、插入（增）

insert into student(name,sex,address,phone,birthday) values('老王','男','开封','11111111111','1998-2-2');

3、删除（删）

delete from student where id = 3;

4、更新（改）

update student set address='开封' where id = 5;
update student set sex='女',address='曼谷' where id = 5;

UPDATE 
    student 
SET 
    sex='女',address='曼谷' 
WHERE 
    id = 5;

5、查找（查）

select * from student;
select name,phone from student;
select name 姓名,phone 电话 from student;

1、安装pymysql

pip install pymysql

2、python与MySQL交互连接

# !/usr/bin/python3
import pymysql

# 打开数据库连接
db = pymysql.connect(
    host='127.0.0.1',  # ip地址
    # host='localhost',  # ip地址
    port=3306,  # 端口号
    user='root',  # 用户名
    passwd='123456',  # 密码
    db='demo',  # 库名
    charset='utf8')  # 链接字符集

# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()

# 使用 execute() 方法执行 SQL，如果表存在则删除
cursor.execute("drop table if exists student")

# 使用预处理语句创建表
sql = ''' create table student(
    id int auto_increment primary key,
    name varchar(100) not null,
    passward varchar(100) not null
    );'''

cursor.execute(sql)

# 关闭数据库连接
db.close()

3、python与MySQL交互–（增）

import pymysql

# 1.插入操作
db = pymysql.connect(host="localhost", user="root", password="123456", db="demo", port=3306)

# 创建表命令
''' create table student(
    id int auto_increment primary key,
    name varchar(100) not null,
    passward varchar(100) not null
    );'''

# 使用cursor()方法获取操作游标
cur = db.cursor()
sql_insert = """insert into student(name,passward) values('小王','123456')"""

try:
    cur.execute(sql_insert)
    # 提交
    db.commit()
except Exception as e:
    # 错误回滚
    print("异常",e)
    db.rollback()
finally:
    db.close()

4、python与MySQL交互–（删）

import pymysql

# 2.删除操作
db = pymysql.connect(host="localhost", user="root", password="123456", db="demo", port=3306)

# 使用cursor()方法获取操作游标
cur = db.cursor()

sql_delete = "delete from student where id = {}"

try:
    cur.execute(sql_delete.format(3))  # 像sql语句传递参数
    # 提交
    db.commit()
except Exception as e:
    # 错误回滚
    print("异常", e)
    db.rollback()
finally:
    db.close()

5、python与MySQL交互–（改）

import pymysql

# 3.更新操作
db = pymysql.connect(host="localhost", user="root",password="123456", db="demo", port=3306)

# 使用cursor()方法获取操作游标
cur = db.cursor()

# 俩种字符串拼接方法
# sql_update = "update student set name = '%s' where id = %d"
sql_update = "update student set name = '{}' where id = {}"

try:
    # sql_str = sql_update % ("小红", 2)
    sql_str = sql_update.format("王姐", 2)
    print('sql_str',sql_str)
    cur.execute(sql_str)  # 像sql语句传递参数
    # cur.execute(sql_update.format("小红", 2))  # 像sql语句传递参数
    # 提交
    db.commit()
except Exception as e:
    # 错误回滚
    print("异常", e)
    db.rollback()
finally:
    db.close()

6、python与MySQL交互–（查）

import pymysql  # 导入 pymysql

# 打开数据库连接
db = pymysql.connect(host="localhost", user="root", password="123456", db="demo", port=3306)

# 使用cursor()方法获取操作游标
cur = db.cursor()

# 4.查询操作
# 编写sql 查询语句  user 对应我的表名
sql = "select * from student"
try:
    cur.execute(sql)  # 执行sql语句

    results = cur.fetchall()  # 获取查询的所有记录
    print("id", "name", "password")
    # 遍历结果
    for row in results:
        id = row[0]
        name = row[1]
        password = row[2]
        print(id, name, password)
except Exception as e:
    print("异常", e)
finally:
    db.close()  # 关闭连接