1.PyMySQL的安装
pip install pymysql
2.python连接数据库
import pymysql
conn = pymysql.connent(
user = '用户名' , #数据库用户名
password = '密码',#数据库密码
host = '127.0.0.1', #ip地址
port = 3306, #mysql数据库默认端口3306
database = '数据库名' #输入数据库的库名
charset = 'utf8' #输入编码格式
)
3.查找数据操作
import pymysql
conn = pymysql.connect(
user = 'root',
password = '123456',
host = '127.0.0.1',
port = 3306,
charset = 'utf8',
database = 'day36'
)
cursor = conn.cursor(cursor = pymysql.cursors.DictCursor)#产生一个游标对象(也就是光标)
#cursor = pymysql.cursors.DictCursor 将查询出来的结果制作成字典的形式返回
sql = "select * from userinfo" #输入sql语句
res = cursor.execute(sql) #执行sql语句
print(res)#execute返回当前查找表中的数据行数
#ret = cursor.fetchone()#只查询结果中的一条数据
#ret = cursor.fetchall() # 获取查询结果的所有数据
#ret = cursor.fetchmany(2) # 指定获取几条数据 如果数字超出也不会报错
#相对移动
cursor.scroll(2,'relative')#基于指针所在的位置 ,往后偏移两位光标
#绝对移动
cursor.scrool(1,'absolute')#基于起始位置,光标从初始位移动往右偏移两位光标
4.插入数据操作
import pymysql
conn = pymysql.connect(
user = 'root',
password = '123456',
host = '127.0.0.1',
port = 3306,
charset = 'utf8',
database = 'day36'
autocommit = True ######## 自动提交确认
)
cursor = conn.cursor(cursor = pymysql.cursors.DictCursor) #产生光标
sql = "insert into userinfo(name,password) values('tank',123)"#sql插入数据语句
res = cursor.execute(sql)#执行语句
#若上面的autocommit = True 没写可在此处写自动提交确认语句,如下
#conn.commit()#确认当前操作 真正的同步到数据库
print(res)
5.更新数据操作
import pymysql
conn = pymysql.connect(
user = 'root',
password = '123456',
host = '127.0.0.1',
port = 3306,
charset = 'utf8',
database = 'day36'
autocommit = True
)
cursor = conn.cursor(cursor = pymysql.cursors.DictCursor)
sql = "update userinfo set name='tank_nb' where id = 1"
res = cursor.execute(sql)
print(res)
6.删除数据操作
import pymysql
conn = pymysql.connect(
user = 'root',
password = '123456',
host = '127.0.0.1',
port = 3306,
charset = 'utf8',
database = 'day36'
autocommit = True
)
cursor = conn.cursor(cursor = pymysql.cursors.DictCursor)
sql = "delete userinfo where id =1"
res = cursor.execute(sql)
print(res)
注意:针对增 删 改操作 执行重要程度偏高
你如果真想操作 必须有一步确认操作(commit)
6.MySQL注入问题
"""
sql注入问题
利用特殊符号和注释语法 巧妙的绕过真正的sql校验
关键性的数据 不要自己手动去拼接 而是交由execute帮你去做拼接
"""
import pymysql
conn = pymysql.connect(
user = 'root',
passwd = '123456',
db = 'day36',
host = '127.0.0.1',
port = 3306,
charset = 'utf8'
)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 获取用户输入的用户名和密码 然后取数据库中校验
username = input('username>>>:').strip()
password = input('password>>>:').strip()
# sql = "select * from userinfo where name='%s' and password= '%s'"%(username,password)
sql = "select * from userinfo where name=%s and password= %s"
print(sql)
cursor.execute(sql,(username,password))
res = cursor.fetchall()
if res:
print(res)
else:
print('username or password error!')