python操作mysql数据库_使用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!')