Python之pymysql详解

发表于 | 分类于

Python之pymysql详解

PyMySQL

PyMySQL 是在 Python3.x 版本中用于连接 MySQL 服务器的一个库，Python2中则使用mysqldb。

安装pymysql

PyMySQL ： 这是一个使Python连接到MySQL的库，它是一个纯Python库。

PyMySQL是一个开源项目 ：https://github.com/PyMySQL/PyMySQL

通过下面的命令来进行安装pymysql模块：

1
pip3 install PyMySQL

连接数据库

安装好pymysql后，在python程序中可以用 import pymysql 来导入模块。

在连接数据库前，首先要确定数据库服务端已启动。在数据库中有已经创建好的数据库，这里连接数据库‘mydb’，使用的用户名为‘root’，密码为‘mysql’，你可以可以自己设定。

比较常用的参数包括:

  • host:数据库主机名.默认是用本地主机
  • user:数据库登陆名.默认是当前用户
  • passwd:数据库登陆的秘密.默认为空
  • db:要使用的数据库名.没有默认值
  • port:MySQL服务使用的TCP端口.默认是3306
  • charset:数据库编码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
导入pymysql
import pymysql
 
打开数据库连接
db = pymysql.connect(
    host = ''127.0.0.1'',   服务端地址
    port = 3306,    服务端端口
    user = ''root'',   用户名		
    password = ''mysql'',   密码
    database = ''mydb'',   要连接的数据库
    charset = ''utf8''    设置数据库编码
)

使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()

关闭数据库连接
db.close()

插入数据

这个连接对象也提供了对事务操作的支持,标准的方法:

  • commit() 提交数据
  • rollback() 发生错误后回滚

commit()方法游标的所有更新操作，rollback（）方法回滚当前游标的所有操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
import pymysql

db = pymysql.connect(
    host = ''127.0.0.1'', 
    port = 3306,
    user = ''root'',
    password = ''mysql'',  
    database = ''mydb'',  
    charset = ''utf8'' 
)

cursor = db.cursor()
sql = """INSERT INTO EMPLOYEE(NAME,
         PASSWORD, AGE, SEX)
         VALUES (''LinWoW'', ''mysql'', 20, ''M'')"""
try：
	cursor.execute(sql)
	确认数据无误之后 commit之后才会将数据真正修改到数据库，如果不执行，程序运行正常，但是数据实际未插入成功
	db.commit()
except:
    print(''插入数据失败！'')
    发生错误时回滚
    db.rollback()
db.close()

为了操作方便，可以在打开数据库连接的参数中添加下面的语句，修改完数据后就不需要手动确认数据执行。

1
autocommit = True

查询数据

Python查询Mysql使用 fetchone() 方法获取单条数据, 使用fetchall() 方法获取多条数据。

  • fetchone(): 该方法获取下一个查询结果集。结果集是一个对象
  • etchall(): 接收全部的返回结果行.
  • rowcount: 这是一个只读属性，并返回执行execute()方法后影响的行数。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
import pymysql
db = pymysql.connect(
    host = ''127.0.0.1'', 
    port = 3306,
    user = ''root'',
    password = ''mysql'',  
    database = ''mydb'',  
    charset = ''utf8'',
    autocommit = True
)

cursor = db.cursor(pymysql.cursors.DictCursor)  设置为字典的形式返回数据（默认是元组）
使用 execute()  方法执行 SQL 查询 ，这里返回的是查询到数据的总条数
cursor.execute("SELECT * from employee")    
 
使用 fetchone() 方法获取查询到的单条数据，可以利用该函数实现一条一条数据的读取
data1 = cursor.fetchone()
print (data1)
''''''
不设置数据返回类型打印的结果
(2, ''LinWoW'', ''mysql'', 20, ''M'')
设置数据返回类型为字典后打印的结果
{''ID'': 2, ''NAME'': ''LinWoW'', ''PASSWORD'': ''mysql'', ''AGE'': 20, ''SEX'': ''M''}
''''''

使用 fetchall() 方法获取所有查询到的所有数据
data2 = cursor.fetchall()
print (data2)
''''''
[{''ID'': 3, ''NAME'': ''LinWoW'', ''PASSWORD'': ''mysql'', ''AGE'': 20, ''SEX'': ''M''}, {''ID'': 4, ''NAME'': ''LinWoW'', ''PASSWORD'': ''mysql'', ''AGE'': 20, ''SEX'': ''M''}]
''''''
scroll()类似文件操作中的seek(),第一个参数表示偏移量，第二个参数表示偏移的起始位置

绝对移动，参照开始的位置
cursor.scroll(1,''absolute'')

相对移动，参照当前光标的位置
cursor.scroll(1,''relative'')
 
db.close()

更新数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
import pymysql

db = pymysql.connect(
    host = ''127.0.0.1'', 
    port = 3306,
    user = ''root'',
    password = ''mysql'',  
    database = ''mydb'',  
    charset = ''utf8'' 
)

cursor = db.cursor()
sql = "UPDATE EMPLOYEE SET AGE = 18 WHERE ID = %s" % (2)
try：
	cursor.execute(sql)
	db.commit()
except:
    print(''修改数据失败！'')
    db.rollback()
db.close()

删除数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
import pymysql

db = pymysql.connect(
    host = ''127.0.0.1'', 
    port = 3306,
    user = ''root'',
    password = ''mysql'',  
    database = ''mydb'',  
    charset = ''utf8'' 
)

cursor = db.cursor()
sql = "DELETE FROM EMPLOYEE WHERE AGE > %s" % (20)
try：
	cursor.execute(sql)
	db.commit()
except:
    print(''删除数据失败！'')
    db.rollback()
db.close()

查询优化

为了防止SQL注入，千万不要手动拼接(关键参数)查询条件，通过下面的示例来实现对查询数据的优化。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
import pymysql
cone = pymysql.connect(
    host = ''127.0.0.1'',
    port = 3306,
    user = ''root'',
    password = ''mysql'',
    database = ''day41'',
    charset = ''utf8'',
    autocommit = True
)
ursor = cone.cursor(pymysql.cursors.DictCursor)
username = input(''username>>>:'')
password = input(''password>>>:'')
sql = "select * from employee  where name=%s and password=%s"

res = cursor.execute(sql,(username,password))   利用方法实现字符的自动拼接
if res:
    print(''输入正确！'')
else:
    print(''用户名或密码错误！'')

cone.close()
''''''
username>>>:LinWoW
password>>>:mysql
输入正确！

username>>>:"qwwqw" or 1=1 --
password>>>:
用户名或密码错误！
''''''