Python操作数据库

pymysql模块简单实用

1、安装pymysql模块

pip3 install PyMySQL

2、建立连接

与MySQL服务端建立连接

conn=pymysql.connect(
  host = '127.0.0.1',  # ip
  port = 3306, #端口
  user = 'root',		 # 数据库用户名
  password = '123',  	 # 数据库密码
  database = 'egon',   # 数据库名字
  charset = 'utf8'  	 # 编码千万不要加- 如果写成了utf-8会直接报错
  autocommit = True  # 这个参数配置完成后  增删改操作都不需要在手动加conn.commit了
)

3、产生游标对象

cursor = conn.cursor(pymysql.cursors.DictCursor)  
# 产生一个游标对象  以字典的形式返回查询出来的数据 键是表的字段  值是表的字段对应的信息
# cursor=conn.cursor() #执行完毕返回的结果集默认以元组显示

4、执行sql语句

#执行sql语句
sql='select * from userinfo where name="%s" and password="%s"' %(user,pwd) #注意%s需要加引号
res=cursor.execute(sql) #执行sql语句，返回sql查询成功的记录数目
print(res)

# r1 = cursor.fetchone()  #返回一条，游标前进一次
# r2 = cursor.fetchone()  #返回一条，游标再前进一次
# r3 = cursor.fetchone()

r = cursor.fetchall()  # 直接拉取所有结果
print(r)

cursor.close()
conn.close()
if res:
    print('登录成功')
else:
    print('登录失败')

用户登录案例

import pymysql

# 建立连接
conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='Quattro!',
    database='db1',
    charset='utf8',
    autocommit=True
)

# 创建游标
cursor = conn.cursor(pymysql.cursors.DictCursor)

username = input('输入用户名:').strip()
password = input('输入密码:').strip()

sql = 'select * from user where username = %s and password = %s'

res = cursor.execute(sql,(username, password)) #能够帮你自动过滤特殊符号 避免sql注入的问题
if res:
    print(cursor.fetchall())
else:
    print('登录失败')

SQL注入

利用注释等特殊符号，篡改sql语句，从而达到自己的目的

MySQL中的注释： - - #
注意：符号--会注释掉它之后的sql，正确的语法：--后至少有一个任意字符

根本原理：就根据程序的字符串拼接name='%s'，我们输入一个xxx' -- haha,用我们输入的xxx加'在程序中拼接成一个判断条件name='xxx' -- haha'

sql注入一：知道用户名，绕过密码

select * from user where name = 'zjy' - - asldkfjalsdjfad任意字符

sql注入二：不知道用户名，绕过用户名和密码

select * from user where name = 'zjy' or 1=1 - - alskdjflasjdflajsdlfk任意字符

解决方案：

# 原来是我们对sql进行字符串拼接
# sql="select * from userinfo where name='%s' and password='%s'" %(user,pwd)
# print(sql)
# res=cursor.execute(sql)

#改写为（execute帮我们做字符串拼接，我们无需且一定不能再为%s加引号了）
sql="select * from userinfo where name=%s and password=%s" #！！！注意%s需要去掉引号，因为pymysql会自动为我们加上
res=cursor.execute(sql,[user,pwd]) #pymysql模块自动帮我们解决sql注入的问题，只要我们按照pymysql的规矩来。

增删改：conn.commit()

增：

import pymysql
conn = pymysql.connect(
	host='localhost',
    port=3306,
    user='root',
    password='123',
    database='db1',
    charset='utf8',
    autocommit=True  #如果不加这个需要手动 conn.commit ,否则数据增删改不了。
)
# 插入方式一：
# sql='insert into userinfo(name,password) values("root","123456");'
# res=cursor.execute(sql) #执行sql语句，返回sql影响成功的行数
# print(res)

# 插入方式二：
# sql='insert into userinfo(name,password) values(%s,%s);'
# res=cursor.execute(sql,("root","123456")) #执行sql语句，返回sql影响成功的行数
# print(res)

# 插入方式三：
cursor = conn.cursor(pymysql.cursors.DictCursor)
sql = 'insert into user(username, password) values(%s, %s)'
res = cursor.executemany(sql, [('user1','pwd1'),('user2','pwd2')])
# res是受影响的记录数量
cursor.close()
conn.close()

查：fetchone, fetchall, fetchmany

#执行sql语句
sql='select * from userinfo;'
rows=cursor.execute(sql) #执行sql语句，返回sql影响成功的行数rows,将结果放入一个集合，等待被查询

# cursor.scroll(3,mode='absolute') # 相对绝对位置移动
# cursor.scroll(3,mode='relative') # 相对当前位置移动
res1=cursor.fetchone()  # 拉取一条数据
res2=cursor.fetchone()
res3=cursor.fetchone()
res4=cursor.fetchmany(2)  # 拉取两条数据
res5=cursor.fetchall()  # 拉取全部数据
 
conn.commit() #提交后才发现表中插入记录成功,连接时如果写了 autocommit=True，就不需要这行

获取插入的最后一条数据的自增ID

print(cursor.lastrowid) #在插入语句后查看