Python 3.x 连接数据库(pymysql 方式)
首先下载pymysql 模块可以通过 pip 安装pip3 install pymysql
出现Successfully installed pymysql-1.0.2 ,下载成功
1.MySQL先创建一个学生表,方便测试 pymysql 的功能
drop database if exists school;
create database school charset='utf8';
use school;
/*学生表*/
drop table if exists student;
create table student(
id int primary key auto_increment not null comment '学号',
name varchar(255) unique not null comment '姓名',
sex char(1) comment '0:男 1:女',
age int not null comment '年龄'
)engine=innodb auto_increment=100000 default charset=gbk;
insert into student(name,sex,age) values
('张三','男',18),
('李四','女',20),
('王五','男',19),
('赵六','女',21);
select*from student;
2.python
# 导入pymysql模块
import pymysql
# 连接到database(mysql数据库)
con = pymysql.Connect(
host='localhost',
port=3306,
user='root',
passwd='root',
db='school', # 库名
charset='utf8'
)
# 插入数据
def insert():
sql = "insert into student (name, sex, age) VALUES ('雷军', '男', 22)"
cursor.execute(sql)
con.commit() # 提交
print('添加成功!')
# 修改数据
def update():
sql = "update student set name= '%s' where id=%d"
data = ('猪八戒', 100003)
cursor.execute(sql % data)
con.commit()
print('修改成功!')
# 查询数据
def select():
sql = "select * from student where name='%s'"
data = '猪八戒'
cursor.execute(sql % data)
print('查询成功!')
print(cursor.fetchall())
# 删除数据
def delete():
sql = "delete from student where id= %d "
data = 100004
cursor.execute(sql % data)
con.commit()
print('删除成功!')
if __name__ == '__main__':
while True:
print('*****************')
print("1.插入数据")
print("2.修改数据")
print("3.查询数据")
print("4.删除数据")
n = input("请选择:")
print(n)
# 获取游标
cursor = con.cursor()
if n == '1':
insert()
elif n == '2':
update()
elif n == '3':
select()
elif n == '4':
delete()
else:
print('输入有误!')
# 关闭连接
cursor.close()
con.close()
print('*****************')