pymysql模块的安装:
pip install pymysql
# 如果无法安装,也无法翻墙,请使用如下命令
pip install pymysql -i https://pypi.tuna.tsinghua.edu.cn/simple
# 使用国内的清华镜像进行下载安装
先创建一个user数据表
import pymysql
db = pymysql.connect("localhost","root","密码","数据库名",charset="utf8")
# 创建游标,用来执行sql语句
cursor = db.cursor()
# 创建一个user表
sql='''
create table user(
uid int primary key auto_increment comment "用户编号",
nick varchar(50) not null comment "用户昵称"
) engine=InnoDB default charset = utf8;
'''
# 执行sql
cursor.execute(sql)
# 关闭游标
cursor.close()
# 关闭数据库
db.close()
插入一条数据
import pymysql
db = pymysql.connect("localhost","root","密码","数据库名",charset="utf8")
# 创建游标,用来执行sql语句
cursor = db.cursor()
sql='''
insert into user(nick) value('小萝莉')
'''
try:
cursor.execute(sql)
# 查看影像的行数
num = cursor.rowcount
print(num)
except pymysql.Error as e:
print("数据库报错",e)
db.rollback()
else:
db.commit()
# 关闭游标
cursor.close()
# 关闭数据库
db.close()
批量插入多条数据
import pymysql
db = pymysql.connect("localhost","root","","beifeng",charset="utf8")
# 创建游标,用来执行sql语句
cursor = db.cursor()
sql='''
insert into user(nick) values(%s);
'''
try:
data = [
('吕布'),
('诸葛亮'),
('司马懿')
]
cursor.executemany(sql,data)
# 查看影像的行数
num = cursor.rowcount
print(num)
except pymysql.Error as e:
print("数据库报错",e)
db.rollback()
else:
db.commit()
# 关闭游标
cursor.close()
# 关闭数据库
db.close()
删除和更新写法和上面大相径庭,修改下sql的语句即可,不在累赘重复。
看下查询的写法:
fetchone():获取一条数据
fetchall():获取查询到的全部数据
fetchmany(n):从返回的结果中取n条
import pymysql
db = pymysql.connect("localhost","root","密码","数据库名",charset="utf8")
# 创建游标,用来执行sql语句
cursor = db.cursor()
sql='''
select * from user
'''
cursor.execute(sql)
users = cursor.fetchall()
for item in users:
print("ID:%d, 昵称:%s"%(item[0],item[1]))
# 关闭游标
cursor.close()
# 关闭数据库
db.close()