pymysql常用到的方法

前提概要

1、你有一个MySQL数据库，并且已经启动。

2、你有可以连接该数据库的用户名和密码

3、你有一个有权限操作的database

连接数据库

#导入pymsql
import pymsql

name = input("name>>>").strip()
pwd = input("password>>>").strip()

#连接数据库 —— 注意这里password得写成字符串类型！！！ 
conn = pymysql.connect(host='localhost',port=3306,user='root',password='123456',database='db3',charset='utf8')

#获取光标对象
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) #返回的是一个含有多个字典的列表
#cursor = conn.cursor() #返回的是一个含有多个元组的元组

#执行sql语句

# sql = "select *  from userinfo where name='%s' and password='%s';"%(name,pwd)
# res = cursor.execute(sql)

#解决sql注入的问题
sql = "select * from userinfo where name=%s and password=%s;"

#获得受影响的信息条数
res = cursor.execute(sql,[name,pwd]) #让pymsql帮我们拼接sql语句
print(res)

#获取数据
ret = cursor.fetchall()
print(ret)

#关闭连接
cursor.close()
conn.close()

数据库的增删改查

除了查的操作不用conn.commit()外，其他的操作都需要

import pymysql

conn = pymysql.connect(host='localhost',port=3306,user='root',password='123456',database='db3',charset='utf8')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

#添加一条信息
sql = "insert into userinfo(name,password) values(%s,%s)"
res = cursor.execute(sql,["rain222","1234"])
print(res)
conn.commit()

#批量增加
sql = "insert into userinfo(name,password) values(%s,%s)"
res = cursor.executemany(sql,[("alex111","123"),("alex222","123"),("alex333",123)])
print(res)
conn.commit()

#修改
sql = "update userinfo set password=%s where name=%s;"
res = cursor.execute(sql,["666","summer1"])
print(res)
conn.commit()

#删除
sql = "delete from userinfo where name=%s"
res = cursor.execute(sql,["summer3",])
print(res)
conn.commit()

#查询
sql = "select * from userinfo;"
res = cursor.execute(sql)

ret = cursor.fetchone() #获取查询的第一条信息
ret = cursor.fetchmany(3) #获取查询的指定条数的信息
ret = cursor.fetchall() #获取查询的所有性信息
print(ret)
cursor.scroll(0,mode="absolute") #移动光标  绝对移动
cursor.scroll(1,mode="relative") #相对移动
ret = cursor.fetchall()
print(ret)

数据回滚与查看刚提交数据的id

import pymysql

conn = pymysql.connect(host='localhost',port=3306,user='root',password='123456',database='db3',charset='utf8')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

sql = "insert into userinfo(name,password) values(%s,%s)"
try:
    # 执行SQL语句
    res = cursor.execute(sql,["rain222","1234"])
    # 提交事务
    conn.commit()
    # 提交之后，获取刚插入的数据的ID
    last_id = cursor.lastrowid
except Exception as e:
    # 有异常，回滚事务
    conn.rollback()
cursor.close()
conn.close()

pymysql执行事物

# -*- coding: utf-8 -*-

import pymysql.cursors
# 连接数据库
connect = pymysql.Connect(
    host='localhost',
    port=3310,
    user='user',
    passwd='123',
    db='test',
    charset='utf8'
)
# 事务处理
sql_1 = "UPDATE staff SET saving = saving + 1000 WHERE user_id = '1001' "
sql_2 = "UPDATE staff SET expend = expend + 1000 WHERE user_id = '1001' "
sql_3 = "UPDATE staff SET income = income + 2000 WHERE user_id = '1001' "

try:
    cursor.execute(sql_1)  # 储蓄增加1000
    cursor.execute(sql_2)  # 支出增加1000
    cursor.execute(sql_3)  # 收入增加2000
except Exception as e:
    connect.rollback()  # 事务回滚
    print('事务处理失败', e)
else:
    connect.commit()  # 事务提交
    print('事务处理成功', cursor.rowcount)

# 关闭连接
cursor.close()
connect.close()

～～～