pymysql 套接字客户端软件
先建立连接到数据库,然后执行sql语句
user = input('user>>: ').strip()
pwd = input('password>>: ').strip()
conn = pymysql.connect(
host='192.168.xx.xxx',
port=3306,
user='root',
password='123',
db='db2',
charset='utf8' # 不是utf-8
)
# 拿到游标
cursor = conn.cursor()
# 执行sql语句认证,不能写成第一种形式
# sql = 'select * from userinfo where name= "%s" and password = "%s"'%(name,password)
# rows = cursor.execute(sql)
sql='select * from userinfo where user = %s and pwd=%s'
rows=cursor.execute(sql,[user,pwd]) # 受影响的行数
# 关闭连接
cursor.close()
conn.close()
# 进行判断
if rows:
print('登录成功')
else:
print('登录失败')
sql语句注释:引号拼接,绕过密码验证
# sql = 'select * from userinfo where name= "%s" and password = "%s"'%(name,password)
# rows = cursor.execute(sql)
此时知道用户名不知道密码,如果输入
小明”-----nn
sql会拼成:
select * from userinfo where name= "xiaoming"----nnn" and password = ""
– 任意字符会被注释掉,实际执行:select * from userinfo where name= "xiaoming"
密码和用户名都不知道输入:
xxxx" or 1=1 --nnnnn
– 任意字符会被注释掉,实际执行:select * from userinfo where name= "xxxx" or 1=1
所以解决办法就是不要加引号:
execute帮我们做字符串拼接,我们无需且一定不能再为%s加引号了,用execute 传值
sql='select * from userinfo where user = %s and pwd=%s'
rows=cursor.execute(sql,[user,pwd])
增、删、改:
import pymysql
#链接
conn=pymysql.connect(host='localhost',user='root',password='123',database='egon')
#游标
cursor=conn.cursor()
#执行sql语句 三种执行方法
#part1
# sql='insert into userinfo(name,password) values("root","123456");'
# res=cursor.execute(sql) #执行sql语句,返回sql影响成功的行数
#part2
# sql='insert into userinfo(name,password) values(%s,%s);'
# res=cursor.execute(sql,("root","123456"))
#part3
sql='insert into userinfo(name,password) values(%s,%s);'
res=cursor.executemany(sql,[("root","123456"),("lhf","12356"),("eee","156")])
conn.commit() #提交
cursor.close()
conn.close()
查:fetchone,fetchmany,fetchall
import pymysql
#链接
conn=pymysql.connect(host='localhost',user='root',password='123',database='egon')
#游标
cursor=conn.cursor()
#执行sql语句
sql='select * from userinfo;'
rows=cursor.execute(sql) # rows只是行数
res1=cursor.fetchone() # 拿一个,元祖形式,拿完显示为none
# (1, 'aaabbb', '123')
res4=cursor.fetchmany(2) # 拿多个
# ((1, 'aaabbb', '123'), (2, 'abcd', '456'), (3, 'egon3', '789'))
res5=cursor.fetchall() # 拿完,列表形式,再拿就是空列表
# cursor.scroll(3,mode='absolute') # 绝对位置移动
# cursor.scroll(3,mode='relative') # 相对当前位置移动
#光标移动移动过之后可以再次获取
#cursor=conn.cursor(pymysql.cursors.DictCursor) # 字典形式
# 连同字段一起显示为字典形式
{'id': 1, 'name': 'xiaoming', 'password': '123'}
conn.commit()
cursor.close()
conn.close()
print(cursor.lastrowid)获取最后一条数据的自增ID
import pymysql
conn=pymysql.connect(host='localhost',user='root',password='123',database='egon')
cursor=conn.cursor()
sql='insert into userinfo(name,password) values("xxx","xxx");'
rows=cursor.execute(sql)
print(cursor.lastrowid) #等于插入之前表里自增id的值+1
conn.commit()
cursor.close()
conn.close()