MySQL 之与Python相关
一.python 操作mysql
import pymysql
'''
# 1.基本语法
# (1) 连接数据库
# conn = pymysql.connect(host = "ip地址",user = "用户",password = "密码",database = "数据库",charset = "字符集",port = "端口号")
# 至少填写前4个参数
conn = pymysql.connect(host = "127.0.0.1",user = "root",password="123456",database = "db5",charset="utf8",port=3306)
# (2).创建游标对象,该对象执行sql相关方法
cursor = conn.cursor()
# (3).执行sql语句
sql = "select * from employee"
# (如果是查询,返回查到的所有条数)
res = cursor.execute(sql)
print(res)
# (4) 获取查询出来的数据 fetchone 只获取一条数据
res = cursor.fetchone()
print(res)
#获取当前数据版版本号
res = cursor.execute("select version()")
print(res)
data = cursor.fetchone()
print("版本号",data)
# (5) 释放游标对象
# cursor.close()
# (6) 关闭数据库连接
conn.close()
'''
# 2.创建/删除 数据表
'''
conn = pymysql.connect(host = "127.0.0.1",user = "root",password="123456",database="db5")
# 创建游标对象 通过这个对象操作数据库
cursor = conn.cursor()
sql1 = """
create table myt10(
id int unsigned primary key auto_increment,
first_name char(10) not null,
last_name char(10) not null,
age int unsigned,
sex tinyint,
money float
)
"""
# 准备sql语句
sql2 = "desc myt9"
# 执行sql语句
# cursor.execute(sql1)
cursor.execute(sql2)
# 获取一条数据
# data = cursor.fetchone()
# 获取所有数据
data = cursor.fetchall()
print(data) #('id', 'int(10) unsigned', 'NO', 'PRI', None, 'auto_increment')
try:
# sql3 = "drop table myt1011111"
sql3 = "drop table myt10"
res = cursor.execute(sql3)
print(res)
except:
pass
print(33344)
# 释放游标对象
cursor.close()
# 关闭远程数据库连接
conn.close()
"""
(
('id', 'int(10) unsigned', 'NO', 'PRI', None, 'auto_increment'),
('first_name', 'char(10)', 'NO', '', None, ''),
('last_name', 'char(10)', 'NO', '', None, ''),
('age', 'int(10) unsigned', 'YES', '', None, ''),
('sex', 'tinyint(4)', 'YES', '', None, ''),
('money', 'float', 'YES', '', None, '')
)
"""
'''
# (3)事务处理
# 1.基本语法
# 连接数据库
conn = pymysql.connect(host = "127.0.0.1",user = "root",password="123456",database = "db5",charset="utf8",port=3306)
# 创建游标对象,该对象执行sql相关方法
cursor = conn.cursor()
# 1 .开启事务 通过pymysql 操作数据库,默认开启事务,需要最后通过commit进行提交数据;
sql1 = "begin"
sql2 = "select * from employee limit 3"
sql3 = "update employee set age = 39 where id = 3"
sql4 = "commit"
cursor.execute(sql1)
cursor.execute(sql2)
cursor.execute(sql3)
# 最终需要通过commit提交事务,提交数据
cursor.execute(sql4)
# 释放游标对象
cursor.close()
# 关闭数据库连接
conn.close()
二.sql注入相关
import pymysql
""""""
# 1.sql 注入的问题
user = input("user>>:").strip()
pwd = input("password>>:").strip()
# sdfsd' or 1=1 -- sfdksjk
conn= pymysql.connect(host="127.0.0.1",user="root",password="123456",database="db5",charset="utf8",port=3306)
cursor = conn.cursor()
sql = "select * from usr_pwd where username = '%s' and password = '%s' " % (user,pwd)
print(sql) #select * from usr_pwd where username = 'iuiuuyuy' or 1=1 -- sdfsdfs' and password = ''
res = cursor.execute(sql)
print(res)
if res:
print("登录成功!")
else:
print("登录失败~")
# 释放游标对象
cursor.close()
# 关闭数据库连接
conn.close()
''''''
# 2.解决办法:
# 如果想用execute 的预处理功能 %s 不要在套一层引号了,但是如果是字符串的格式化,必须加引号.
user = input("user>>:").strip()
pwd = input("password>>:").strip()
conn= pymysql.connect(host="127.0.0.1",user="root",password="123456",database="db5",charset="utf8",port=3306)
cursor = conn.cursor()
sql = 'select * from usr_pwd where username = %s and password = %s '
# execute可以提前过滤sql语句,做一下预处理.方式sql注入.
print(sql)
res = cursor.execute(sql,(user,pwd))
if res:
print("登录成功")
else:
print("登录失败")
# 释放游标对象
cursor.close()
# 关闭数据库连接
conn.close()
三.python 操作mysql 增删改
import pymysql
"""
通过pymysql这个模块提交给mysql 服务器,默认开启事务
事务处理,必须要依赖commit来进行提交数据,也可以用rollback回滚到开始时候 的数据
不提交数据,默认回滚
提交数据 conn.commit()
回滚数据 conn.rollback()
execute executemany 如果执行的是增删改,返回的是受影响的行数
execute 如果执行的是查,返回的是查询到的数量;
"""
# 连接数据库
conn = pymysql.connect(host="127.0.0.1",user="root",password="123456",database="db5")
# cursor=pymysql.cursors.DictCursor 把返回的数据变成字典,默认是元组;
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 1.增
# 执行sql语句
sql = """insert into myt9(first_name,last_name,age,sex,money) values(%s,%s,%s,%s,%s)"""
# execute只执行一条数据
res = cursor.execute( sql,( "马","巨强",74,1,8) )
# print(res)
# executemany执行多条数据 返回第一次插入的那条数据的id
# res = cursor.executemany( sql, [("张","过程",88,0,2),("意","四",13,1,90),("罗","婷",18,1,100000),("黄","胸大",20,0,900)] )
# print(res)
# 获取最后一条插入数据的id(一般常用订单号上)
print(cursor.lastrowid)
# 2.删
"""
sql = "delete from myt9 where id = %s "
res = cursor.execute(sql,(5))
if res:
print("删除成功")
else:
print("删除失败")
"""
# 3.改
"""
sql = "update myt9 set first_name = %s where id = %s"
res = cursor.execute(sql,("王",9))
if res:
print("修改成功")
else:
print("修改失败")
"""
# 4.查 返回搜索的条数
sql2 = "select * from myt9"
res = cursor.execute(sql2)
print(res)
# 查询一条 fetchone()
data = cursor.fetchone()
print(data)
# 查询多条 fetchmany(查询的条数) 默认查一条,基于上一条查询,往下在查查2条
data = cursor.fetchmany(2)
print(data)
# 查询所有数据
data = cursor.fetchall()
print(data)
#[{'id': 9, 'first_name': '王', 'last_name': '巨强', 'age': 74, 'sex': 1, 'money': 8.0}]
for row in data:
first_name = row['first_name']
last_name = row['last_name']
age = row['age']
if row['sex'] == 0:
sex = "男"
else:
sex = "女"
money = row['money']
print("姓:{},名字:{},性别:{},年龄:{},收入:{}".format(first_name,last_name,sex,age,money))
# 可以选择查询的位置
sql3 = "select * from myt9 where id >= 20"
res = cursor.execute(sql3)
print(res)
data = cursor.fetchone()
print(data)
# 相对当前位置进行移动
cursor.scroll(7,mode="relative") # 向后移动
print(cursor.fetchone())
cursor.scroll(-5,mode="relative") # 向前移动
print(cursor.fetchone())
# 绝对位置移动
cursor.scroll(0,mode="absolute")
print(cursor.fetchone())
conn.commit()
cursor.close()
conn.close()
