使用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)　

注意：针对增 删 改操作 执行重要程度偏高

你如果真想操作 必须有一步确认操作(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!')