Python笔记_38_python操作mySQL

python 操作mysql

• 基本语法

import pymysql

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

• 创建/删除 数据表

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, '')
)
"""

事务处理

• 基本语法

# 连接数据库
conn = pymysql.connect(host = "127.0.0.1",user = "root",password="123456",database = "db5",charset="utf8",port=3306)
# 创建游标对象,该对象执行sql相关方法
cursor = conn.cursor()
# 开启事务  通过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

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

• 解决办法:

# 如果想用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 增删改

通过pymysql这个模块提交给mysql 服务器,默认开启事务
事务处理,必须要依赖commit来进行提交数据,也可以用rollback回滚到开始时候 的数据
不提交数据,默认回滚

提交数据 conn.commit()
回滚数据conn.rollback()

execute executemany 如果执行的是增删改,返回的是受影响的行数
execute 如果执行的是查,返回的是查询到的数量;

import pymysql

# 连接数据库
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)

• 增

# 执行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)

• 删

sql = "delete from myt9 where id = %s "
res = cursor.execute(sql,(5))

if res:
	print("删除成功")
else:
	print("删除失败")

• 改

sql = "update myt9 set first_name = %s where  id = %s"
res = cursor.execute(sql,("王",9))
if res:
	print("修改成功")
else:
	print("修改失败")

• 查 返回搜索的条数

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

Mysql相关

索引原理

• innodb和myisam的索引的存储方式是不同的
  innodb存储引擎 索引和数据都存在ibd文件中
  myisam 把所有的索引全部单独存储,就是那个MYI文件

• 索引的数据结构使用的是b+树
  一个叶子节点也叫一个数据页,可以存多条数据,大概16k
  单条数据量越小,叶子节点存的数据量就会越大,需要的叶子节点就越少,
  这样的话,树的高度相对降低一些,查询的速度越快.

联合索引

联合索引:经常查询时候,几个字段要放在一起查,比如找名字:姓+名
name = ‘xxx’ and email = ‘xxx’
create index 索引名 on 表明(字段1,字段2 … )
create index name_email on t1(name,email);

数据库相关

• 导出数据库 [退出mysql时在操作]

mysqldump -uroot -p123456 db1 > db1.sql
mysqldump -uroot -p123456 db1 表1 表2 > ceshi01.sql

• 导入数据库 [登入mysql时在操作]

source ~/ceshi01.sql;