python学习之操作mysql
pymysql是Python中操作MySQL的模块,本文主要介绍pymysql一些基本使用
一、查询数据
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
#!/usr/bin/env python # -*- coding:utf-8 -*- import pymysql # 创建连接 conn = pymysql.connect(host='x.x.x.x.', port=3306, user='root', passwd='xxxx', db='xxxx') # 创建游标 cursor = conn.cursor() # 执行SQL,并返受影响行数 effect_row = cursor.execute("SELECT * FROM user_info") print(effect_row) #获取一条数据 #row_1 = cursor.fetchone() #获取所有数据,默认以元组形式返回 row_2 = cursor.fetchall() # 获取剩余结果前n行数据 # row_3 = cursor.fetchmany(3) print(row_2) # 关闭游标 cursor.close() # 关闭连接 conn.close()
二、增、删、改数据
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
#!/usr/bin/env python # -*- coding:utf-8 -*- import pymysql # 创建连接 conn = pymysql.connect(host='x.x.x.x', port=3306, user='root', passwd='xxxxx', db='xxxx') # 创建游标 cursor = conn.cursor() # 执行update SQL,并返受影响行数 #effect_row = cursor.execute("UPDATE user_info SET username='张三' WHERE nid= %s", (13,)) # 执行delete SQL,并返受影响行数 #effect_row = cursor.execute("DELETE FROM user_info WHERE nid= %s", (13,)) # 执行insert SQL,并返受影响行数 #effect_row = cursor.execute("INSERT INTO user_info (username,passwd,user_type_id) VALUES (%s,%s,%s)", ('李五','123',1)) # 执行SQL,并返回受影响行数,执行多次 effect_row = cursor.executemany("INSERT INTO user_info (username,passwd,user_type_id) VALUES (%s,%s,%s)", [('李五','123',1),('李三','123',1)]) print(effect_row) # 提交,不然无法保存新建或者修改的数据 conn.commit() # 关闭游标 cursor.close() # 关闭连接 conn.close()
三、获取新创建数据的自增ID
可以获取到最新自增的ID,也就是最后插入的一条数据ID
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
#!/usr/bin/env python # -*- coding:utf-8 -*- import pymysql # 创建连接 conn = pymysql.connect(host='x.x.x.x', port=3306, user='root', passwd='xxxx', db='xxxx') # 创建游标 cursor = conn.cursor() # 执行insert SQL,并返受影响行数 effect_row = cursor.execute("INSERT INTO user_info (username,passwd,user_type_id) VALUES (%s,%s,%s)", ('李六','123',1)) print(effect_row) # 提交,不然无法保存新建或者修改的数据 conn.commit() #获取自增id new_id = cursor.lastrowid print (new_id) # 关闭游标 cursor.close() # 关闭连接 conn.close()
四、fetch数据类型
默认获取的数据是元祖类型,如果想要字典类型的数据可以使用如下方式
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
#!/usr/bin/env python # -*- coding:utf-8 -*- import pymysql # 创建连接 conn = pymysql.connect(host='x.x.x.x', port=3306, user='root', passwd='xxx', db='xxxx') #创建游标游标设置为字典类型 cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) cursor.execute("SELECT * FROM user_info") row_1 = cursor.fetchone() print(row_1) # 关闭游标 cursor.close() # 关闭连接 conn.close() 查询结果 {'nid': 1, 'username': 'root', 'passwd': 'abc', 'user_type_id': 1}
五、防止sql注入
在构造sql语句时如果使用字符串拼接的方式,会造成sql注入
例如
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
#!/usr/bin/env python # -*- coding:utf-8 -*- import pymysql # 创建连接 conn = pymysql.connect(host='x.x.x.x', port=3306, user='root', passwd='xxxx', db='xxxx') #创建游标游标设置为字典类型 cursor = conn.cursor() sql="SELECT * FROM user_info WHERE username='%s'" name="root' or '1'-- " sql=sql % name #拼接语句被构造成下面这样,永真条件,此时就注入成功了。因此要避免这种情况需使用pymysql提供的参数化查询。 print(sql) #SELECT * FROM user_info WHERE username='root' or '1'-- ' cursor.execute(sql) row_1 = cursor.fetchone() print(row_1) # 关闭游标 cursor.close() # 关闭连接 conn.close()
为避免注入,使用pymysql提供的参数化语句
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
#!/usr/bin/env python # -*- coding:utf-8 -*- import pymysql # 创建连接 conn = pymysql.connect(host='x.x.x.x', port=3306, user='root', passwd='xxxx', db='xxx') #创建游标游标设置为字典类型 cursor = conn.cursor() '''sql="SELECT * FROM user_info WHERE username='%s'" name="root' or '1'-- " sql=sql % name #拼接语句被构造成下面这样,永真条件,此时就注入成功了。因此要避免这种情况需使用pymysql提供的参数化查询。 print(sql) #SELECT * FROM user_info WHERE username='root' or '1'-- ' cursor.execute(sql)''' #执行参数化查询,%s不用加引号,默认会加上的 cursor.execute("SELECT * FROM user_info WHERE username=%s",('root',)) row_1 = cursor.fetchone() print(row_1) # 关闭游标 cursor.close() # 关闭连接 conn.close()
六、以类的方式连接操作mysql
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
''' class ConnDB: def __init__(self,host,user,passwd,db): self.host=host self.user=user self.passwd=passwd self.db=db self.conn = None self.cursor = None def connect(self, cursor=pymysql.cursors.DictCursor): self.conn = pymysql.connect(host=self.host,user=self.user,passwd=self.passwd,db=self.db) self.cursor = self.conn.cursor(cursor=cursor) return self.cursor def close(self): self.conn.commit() self.cursor.close() self.conn.close() obj=ConnDB('x.x.x.x','root','xx','xx') cursor=obj.connect() cursor.execute("SELECT * FROM user_info WHERE username=%s",('root',)) row_1 = cursor.fetchone() print(row_1) cursor.close() ''' #定义数据库信息字典 my_dic = { "host": 'x.x.x.x, "port": 3306, "user": 'root', "passwd": 'xxx', "db": 'xxx', "charset": 'utf8' } #以字典的方式传入数据库登录相关信息 class ConnDB: def __init__(self,dic): self.__conn_dict = dic self.conn = None self.cursor = None def connect(self, cursor=pymysql.cursors.DictCursor): self.conn = pymysql.connect(**self.__conn_dict) self.cursor = self.conn.cursor(cursor=cursor) return self.cursor def close(self): self.conn.commit() self.cursor.close() self.conn.close() obj=ConnDB(my_dic) cursor=obj.connect() cursor.execute("SELECT * FROM user_info WHERE username=%s",('root',)) row_1 = cursor.fetchone() print(row_1) cursor.close()