python操作数据库模块－pymysql

用简单的登陆来看这个模块的操作

import pymysql

user = input("username:")
pwd = input("password:")

conn = pymysql.connect(host="localhost",user='root',password='',database="login")
cursor = conn.cursor()
sql = "select * from users where username='%s' and password='%s'" %(user,pwd,)
# select * from userinfo where username='uu' or 1=1 -- ' and password='%s'  mysql注入问题
cursor.execute(sql)
result = cursor.fetchone()
print(result)
cursor.close()
conn.close()

if result:
    print('登录成功')
else:
    print('登录失败')

　　

import pymysql

user=input("user:").strip()
pwd=input("pwd:").strip()

conn= pymysql.connect(host="localhost",user="root",password="",database="login",charset="utf8")
cursor=conn.cursor()
sql = "select * from users where username=%s and password=%s"
cursor.execute(sql,[user,pwd])

# sql = "select * from users where username=%s and password=%s"
# cursor.execute(sql,user,pwd)


ret=cursor.fetchone()
print(ret)


cursor.close()
conn.close()


if ret:
    print("login success")
else:
    print("login failed")

　　

import pymysql

user = input("username:")
pwd = input("password:")

conn = pymysql.connect(host="localhost",user='root',password='',database="login")
cursor = conn.cursor()
sql = "select * from userinfo where username=%s and password=%s"
cursor.execute(sql,user,pwd)
# cursor.execute(sql,[user,pwd])
# cursor.execute(sql,{'u':user,'p':pwd})
result = cursor.fetchone()

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

　　

用pymysql操作增删改查

import pymysql

# 增加，删，改
# conn = pymysql.connect(host="localhost",user='root',password='',database="db666")
# cursor = conn.cursor()
# sql = "insert into userinfo(username,password) values('root','123123')"
# 受影响的行数
# r = cursor.execute(sql)
# #  ******
# conn.commit()
# cursor.close()
# conn.close()

# conn = pymysql.connect(host="localhost",user='root',password='',database="db666")
# cursor = conn.cursor()
# # sql = "insert into userinfo(username,password) 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="school",charset="utf8")
# cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# sql = "select * from student"
# cursor.execute(sql)

# cursor.scroll(1,mode='relative')  # 相对当前位置移动
# cursor.scroll(2,mode='absolute') # 相对绝对位置移动
# 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="school",charset="utf8")
# cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# sql = "insert into student(sname,gender,class_id) values('alex','男',5)"
# cursor.execute(sql)
# conn.commit()
# # 返回自增id
# print(cursor.lastrowid)
# cursor.close()
# conn.close()

　　

 

简单总结

pymysql模块：
	    作用：Python模块，对数据库进行操作（SQL语句）
		安装：pip3 install pymysql -i https://pypi.douban.com/simple

		
		1. Python实现用户登录
		2. MySQL保存数据
		
		
		- 连接、关闭（游标）
		- execute() ：  -- SQL注入
		- 增删改都要提交： conn.commit()
		- fetchone(取一条) ，fetchall(取所有条)
		- 获取插入数据自增ID：cursor.lastrowid