Python操作MySQL数据库

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()