Python操作MySQL：pymysql

Python操作MySQL：pymysql 下载安装：

pip3 install pymysql

1.执行SQL

#!/usr/bin/env python
# -*- coding:utf-8 -*-
import pymysql
   
# 创建连接
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')
# 创建游标
cursor = conn.cursor()
   
# 执行SQL，并返回收影响行数
effect_row = cursor.execute("update hosts set host = '1.1.1.2'")
   
# 执行SQL，并返回受影响行数
#effect_row = cursor.execute("update hosts set host = '1.1.1.2' where nid > %s", (1,))
   
# 执行SQL，并返回受影响行数
#effect_row = cursor.executemany("insert into hosts(host,color_id)values(%s,%s)", [("1.1.1.11",1),("1.1.1.11",2)])
   
   
# 提交，不然无法保存新建或者修改的数据
conn.commit()
   
# 关闭游标
cursor.close()
# 关闭连接
conn.close()

示例：

import pymysql
 
 
conn = pymysql.connect(host="10.37.129.3",port=3306,user="egon",passwd="123456",db="homework",charset="utf8")
 
cursor = conn.cursor()
 
 
#方式一:
 
sql = "select * from course where cid=1"
effect_row = cursor.execute(sql)
 
 
# 方式二：
sql = "select * from course where cid='%s'" %(1,)
effect_row = cursor.execute(sql)
 
 
# 方式三  普通  列表
sql = "select * from course where cid='%s'"
 
effect_row = cursor.execute(sql,1)
effect_row = cursor.execute(sql,[1])
 
 
# 方式四  字典格式
 
sql = "select * from course where cid='%(u)s'"
effect_row = cursor.execute(sql,{"u":1})
 
 
row_1 = cursor.fetchone()
 
cursor.close()
conn.close()
 
print(row_1)

2、获取新创建数据自增ID

#!/usr/bin/env python
# -*- coding:utf-8 -*-
import pymysql
   
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')
cursor = conn.cursor()
cursor.executemany("insert into hosts(host,color_id)values(%s,%s)", [("1.1.1.11",1),("1.1.1.11",2)])
conn.commit()
cursor.close()
conn.close()
   
# 获取最新自增ID
new_id = cursor.lastrowid

3、获取查询数据

#!/usr/bin/env python
# -*- coding:utf-8 -*-
import pymysql
   
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')
cursor = conn.cursor()
cursor.execute("select * from hosts")
   
# 获取第一行数据
row_1 = cursor.fetchone()
   
# 获取前n行数据
# row_2 = cursor.fetchmany(3)
# 获取所有数据
# row_3 = cursor.fetchall()
   
conn.commit()
cursor.close()
conn.close()

注：在fetch数据时按照顺序进行，可以使用cursor.scroll(num,mode)来移动游标位置，如：

• cursor.scroll(1,mode='relative')  # 相对当前位置移动

• cursor.scroll(2,mode='absolute') # 相对绝对位置移动

4、fetch数据类型

　　关于默认获取的数据是元祖类型，如果想要或者字典类型的数据，即：

#!/usr/bin/env python
# -*- coding:utf-8 -*-
import pymysql
   
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')
   
# 游标设置为字典类型
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
r = cursor.execute("call p1()")
   
result = cursor.fetchone()
   
conn.commit()
cursor.close()
conn.close()

插入演示：

import pymysql
 
 
conn = pymysql.connect(host="10.37.129.3",port=3306,user="egon",passwd="123456",db="student_info",charset="utf8")
 
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
 
 
#插入一行
# sql = "insert into student_info(sname,gender,class_id) VALUES('alex1','女',2)"
 
#插入多行
sql = "insert into student_info(sname,gender,class_id) VALUES('alex1','女',2),('alex2','女',2),('alex3','女',2)"
 
r = cursor.execute(sql)
 
#或
 
sql = "insert into userinfo(username,password) values(%s,%s)"
# 受影响的行数
r = cursor.executemany(sql,[('egon','sb'),('laoyao','BS')])
 
 
 
 
conn.commit()
cursor.close()
conn.close()
 
print(r)

6、补充

# sql 注入
import pymysql
 
user = input("username:")
pwd = input("password:")
 
conn = pymysql.connect(host="localhost",user='root',password='',database="db3")   #建立与客户端的链接
cursor = conn.cursor()
sql = "select * from userinfo where username='%s' and pwd='%s'" %(user,pwd,) #不要自己定义放置占位符user和pwd，防数据库被泄露
# select * from userinfo where username='uu' or 1=1 -- ' and password='%s'
#上面部分会理解成，第一部分：select * from userinfo where username='uu'，第二部分：or 1=1，第三部分：-- 注释，
# 第四部分：' and password='%s'，第四部分会当成注释内容。所以执行该程序会显示登录成功。
cursor.execute(sql)         #执行sql语句
result = cursor.fetchone()  #返回第一行内容
#关闭连接
cursor.close()
conn.close()
 
if result:
    print('登录成功')
else:
    print('登录失败')

import pymysql
user = input("username:")
pwd = input("password:")
 
conn = pymysql.connect(host="localhost",user='root',password='',database="db3")
cursor = conn.cursor()
sql = "select * from userinfo where username=%s and pwd=%s"
# cursor.execute(sql,(user,pwd))
cursor.execute(sql,[user,pwd])    #cursor.execute(sql,user,pwd)也可写成cursor.execute(sql,[user,pwd])，效果一样
# cursor.execute(sql,{'u':user,'p':pwd})    #sql中加入key值，打印结果就会变成字典的格式而不是元组格式
result = cursor.fetchone()                  #取一行
cursor.close()
conn.close()
if result:
    print('登录成功')
else:
    print('登录失败')
 
print(result)

import pymysql
 
# 增加，删，该
#增
# conn = pymysql.connect(host="localhost",user='root',password='',database="db3")
# cursor = conn.cursor()
# sql = "insert into userinfo(username,pwd) values('root','123123')"
# 受影响的行数
# r = cursor.execute(sql)
# #  ******
# conn.commit()     #对数据库有改变均要执行conn.commit()命令，提交给数据库。所以增删改均需有这条命令，查不需要。
# cursor.close()
# conn.close()
 
# conn = pymysql.connect(host="localhost",user='root',password='',database="db3")
# cursor = conn.cursor()
# # sql = "insert into userinfo(username,pwd) values(%s,%s)"
# # cursor.execute(sql,(user,pwd,))
#
# sql = "insert into userinfo(username,password) values(%s,%s)"
# # 受影响的行数
# r = cursor.executemany(sql,[('egon','sb'),('laoyao','BS')])
# #  ******
# conn.commit()
# cursor.close()
# conn.close()
 
 
 
 
# 查
# conn = pymysql.connect(host="localhost",user='root',password='',database="db666")
# cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# sql = "select * from userinfo"
# cursor.execute(sql)
 
# cursor.scroll(1,mode='relative')  # 相对当前位置移动
# cursor.scroll(2,mode='absolute') # 相对绝对位置移动
# result = cursor.fetchone()
# print(result)
# result = cursor.fetchone()
# print(result)
# result = cursor.fetchone()
# print(result)
# result = cursor.fetchall()
# print(result)
 
 
# result = cursor.fetchmany(4)
# print(result)
# cursor.close()
# conn.close()
 
 
 
 
# 新插入数据的自增ID: cursor.lastrowid
# import pymysql
#
# conn = pymysql.connect(host="localhost",user='root',password='',database="db3")
# cursor = conn.cursor()
# sql = "insert into userinfo(username,pwd) values('asdfasdf','123123')"
# cursor.execute(sql)
# conn.commit()
# print(cursor.lastrowid)       #lastrowid最后一个自增id
# cursor.close()
# conn.close()