python连接mysql

插入数据

import pymysql

conn = pymysql.connect(host='127.0.0.1' ,user='root', passwd='123456', db='test', port=3306, charset='utf8')
cur = conn.cursor()

# cur.execute("drop table if EXISTS  test")  # 如果数据库存在，删除数据库中的表
cur.execute("drop table if EXISTS  student")  # 如果表存在，删除该表

# 创建新表
sql = """create table student(
        id varchar(10) not null,
        name varchar(20),
        sex varchar(4),
        city varchar(6),
        birthday date
    )default charset = utf8;"""

cur.execute(sql)

## 插入数据

# 第一种插入方法
s1 = """insert into student(id, name, sex, city, birthday) values ('10000', '张三', '男', '广州', '1994-01-01')"""
cur.execute(s1)

# 第二种插入方法
s2 = "insert into student(id, name, sex, city, birthday) values (%s, %s, %s, %s, %s)"
v2 = ('10001', '李四', '女', '北京', '1994-01-21')
cur.execute(s2, v2)

# 第三种批量插入
s3 = "insert into student(id, name, sex, city, birthday) values (%s, %s, %s, %s, %s)"
v3 = [
    ('10002', '王五', '女', '北京', '1994-03-21'),
    ('10003', '何六', '男', '广州', '1994-04-30'),
    ('10004', '田七', '男', '上海', '1994-05-01')
]
cur.executemany(s3, v3)

print('插入成功')
conn.commit()
conn.close()

查询数据

import pymysql

conn = pymysql.connect(host='127.0.0.1' ,user='root', passwd='123456', db='test', port=3306, charset='utf8')
cur = conn.cursor()

# 查询表中所有数据
cur.execute("select * from student")
r1 = cur.fetchall() # 获取所有记录
for x in r1:
    print(x) # 以元组的形式输出

print('查询成功！')
    
# 查询表中city='广州'的学生的id，按名字排序
cur.execute("select id, name from student where city='广州' order by name")
r2 = cur.fetchall()
for x in r2:
    print(x[0], x[1])

print('查询成功！')

conn.commit()
conn.close()

(‘10000’, ‘张三’, ‘男’, ‘广州’, datetime.date(1994, 1, 1))
(‘10001’, ‘李四’, ‘女’, ‘北京’, datetime.date(1994, 1, 21))
(‘10002’, ‘王五’, ‘女’, ‘北京’, datetime.date(1994, 3, 21))
(‘10003’, ‘何六’, ‘男’, ‘广州’, datetime.date(1994, 4, 30))
(‘10004’, ‘田七’, ‘男’, ‘上海’, datetime.date(1994, 5, 1))
查询成功！
10003 何六
10000 张三
查询成功！

更新数据

import pymysql

conn = pymysql.connect(host='127.0.0.1' ,user='root', passwd='123456', db='test', port=3306, charset='utf8')
cur = conn.cursor()

# 更新id='10004'的性别
s = "update student set sex='女' where id='10004'"
cur.execute(s)

print('更新成功！')

conn.commit()
conn.close()

删除数据

import pymysql

conn = pymysql.connect(host='127.0.0.1' ,user='root', passwd='123456', db='test', port=3306, charset='utf8')
cur = conn.cursor()

# 删除id='10004'的学生记录
s = "delete from student where id='10004'"

cur.execute(s)

print('删除成功！')

conn.commit()
conn.close()