使用Python操作MySQL数据库
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)
注意:针对增 删 改操作 执行重要程度偏高
"""
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!')