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