1.查询操作
注意：
Python查询Mysql使用 fetchone() 方法获取单条数据, 使用fetchall() 方法获取多条数据。
-
fetchone(): 该方法获取下一个查询结果集。结果集是一个对象
-
fetchall(): 接收全部的返回结果行.
-
rowcount: 这是一个只读属性，并返回执行execute()方法后影响的行数。
import pymysql # 导入需要的包
con = pymysql.connect(host="localhost", user="root", password="root", database="userdb")
# 获取连接对象 localhost代表本地主机，用127.0.0.1 ip地址也可以。database这个是数据库名，你要操作那个数据库
r = con.cursor() # 获得游标
sql = ''' # 写sql语句
select * from user where id = 1
'''
try:
cur.execute(sql)
list = cur.fetchall()
con.commit()
except Exception as e:
print("失败")
con.rollback()
for i in list: # 写个for循环查看一下数据
print(i)
cur.close()
con.close()
2.添加数据
import pymysql
con = pymysql.connect(host="localhost", user="root", password="root", database="userdb")
cur = con.cursor()
sql = '''
insert into admin_info(id,username,password,email)
values(%s,%s,%s,%s)
'''
data = [2,"小明","123456","123456@qq.com"]
try:
cur.execute(sql,data)
con.commit()
except Exception as e:
print("失败")
con.rollback()
cur.close()
con.close()
3.删除数据
import pymysql
con = pymysql.connect(host="localhost", user="root", password="root", database="userdb")
cur = con.cursor()
sql = '''
delete from admin_info where id = 2
'''
try:
cur.execute(sql)
con.commit()
except Exception as e:
print("失败")
con.rollback()
cur.close()
con.close()
4.修改数据
import pymysql
con = pymysql.connect(host="localhost", user="root", password="root", database="userdb")
cur = con.cursor()
sql = '''
update admin_info set username = "小黑" where username = "xuyunfei"
'''
try:
cur.execute(sql)
con.commit()
except Exception as e:
print("失败")
con.rollback()
cur.close()
con.close()
