Python操作MySQL

2018-09-21 本文已影响0人 DonLex

在学习的过程中总免不了需要使用大量的数据。而这些数据一般都存在数据库中，当需要使用的时候就可以通过连接数据库进行增删改查等操作。

而数据库又可以分为关系型数据库和非关系型数据库。关系型数据库中常见的有mysql，oracle等。非关系型数据库有MongoDB等。相信在学习爬虫的过程中对MongoDB的基本使用都已经了解了。那今天看一下mysql的基本使用方法。

python与mysql的桥梁

python是非常不错的"两门语言"，所以在py2和py3中使用的连接mysql的模块有点变化；py2中一般使用MySqldb来调用Mysql，但是在py3中不支持该包，则使用pymysql来代替了。

现在python的版本都到3.7了，所以还是老老实实的使用pymysql吧。安装pymysql非常简单，只需要pip一下就可以了

pymysql的简单操作

数据库的连接，基本操作增删改查，还有事务处理的能力可以说是程序员的基本功。所以从这几个方面入手，了解一下pymysql。

1.连接

连接非常简单只需要使用pymysql的connect方法就可以了。

例如：

import pymysql

connection = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='123456',
    database='test',
    charset='utf8')

host为数据库的主机IP地址，port为MySQL的默认端口号，user为数据的用户名，password为数据库的登录密码，db为数据库的名称。
注意：charset="utf8",中间没有空格，因为mySql不支持

看到没有password=‘123456’，这就是前段时间华住脱库的元凶。当然我们只是在本机测试，123456就123456吧，谁叫它简单又好记呢。

2.MySQL的游标

在开始操作mysql之前，先介绍一下MySQL的游标(cursor)。它是mysql中一个重要的概念，它有几个特性：

有数据缓冲的思想：游标的设计是一种数据缓冲区的思想，用来存放SQL语句执行的结果。
有数据基础：游标是在先从数据表中检索出数据之后才能继续灵活操作的技术。
类似于指针：游标类似于指向数据结构堆栈中的指针，用来pop出所指向的数据，并且只能每次取一个。

上面看读懂没关系。总的来说，想要操作数据库，就先要拿到游标，具体获取方法，参考下面的例子。

假设接下来的例子都是建立在数据库中已经新建好的user表的基础上运行的:

CREATE TABLE `user` (
  `id` varchar(20) NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

3.增加

使用insert来将数据插入数据库

import pymysql

connection = pymysql.connect(
    port=3306,
    user='root',
    password='123456',
    database='test')
cursor = connection.cursor() # 获取cursor
cursor.execute('INSERT INTO USER(name, id) VALUES (%s, %s);',['DonLex', 1]) # 执行sql语句

connection.close()# 关闭数据连接

可以看到，cursor的获取非常简单。执行sql语句也是易于理解的。

最后一定不要忘记了使用close()关闭数据连接。

4.删除

有一句话：“从删库到跑路”；可见删除数据是一件非常危险的操作。而一般做这些危险操作，都需要添加事务，这里为了简单，就不添加事务了。

import pymysql

connection = pymysql.connect(
    port=3306,
    user='root',
    password='123456',
    database='test')
cursor = connection.cursor() # 获取cursor
sql = "DELETE FROM USER WHERE id=%s;" #使用%s进行占位
cursor.execute(sql, [0]) 
connection.close()# 关闭数据连接

5.修改

使用update来修改，通过set设置新的值，where代表选择条件

import pymysql

connection = pymysql.connect(
    port=3306,
    user='root',
    password='123456',
    database='test')
cursor = connection.cursor() # 获取cursor

# 修改数据的SQL语句
sql = "UPDATE USER SET id=%s WHERE name=%s;"
id = 0
name = "DonLex"

# 执行SQL语句
cursor.execute(sql, [id, name])
connection.close()# 关闭数据连接

6.查询

查询则使用select来选择，from后面跟着表名，where是选择条件

import pymysql

connection = pymysql.connect(
    port=3306,
    user='root',
    password='123456',
    database='test')
cursor = connection.cursor() # 获取cursor
cursor.execute('select * from user where id = %s', ('0',)) # 执行sql语句

values = cursor.fetchone() # 获取单条数据
print(values)
connection.close()# 关闭数据连接

获取单条数据使用fecthone();当获取多条数据时，则需要使用fecthall()：

#查询数据库多条数据
result = cursor.fetchall()
for data in result:
    print(data)

7.事务

终于到数据库的重量级内容了。查了一下，关于事务的定义如下：

事务就是一组原子性的SQL查询，或者说一个独立的工作单元。如果数据库引擎能够成功地对数据应用该组查询的全部语句，那么久执行该组查询。如果其中任何一条语句因为崩溃或其他原因无法执行，那么所有语句都不会执行。也就是说，事务内的语句，要么全部执行成功，要么全部执行失败。

事务的四大特性（ACID）：

原子性（atomicity）
一致性（consistency）
隔离性（isolation）
持久性（durability）

实现代码

import pymysql

connection = pymysql.connect(
    port=3306,
    user='root',
    password='123456',
    database='test')
cursor = connection.cursor()
sql = "DELETE FROM USER WHERE id=%s;"
try:
    cursor.execute(sql, [1])
    # 提交事务
    connection.commit()
except Exception as e:
    # 有异常，回滚事务
    connection.rollback()

connection.close()

从上面的代码中可以看到，execute()执行删除操作使用try...except包起来了。如果在执行try的部分出错的话，就回滚，否则就提交事务。

更多精彩内容，敬请关注微信公众号：Python绿洲