python学习：mysql操作

–

测试环境

系统：centos6.9 
mysql：mysql-server-5.1.73
网段：192.168.170.0/24
python：python3.6

安装：
yum clean all && yum install -y mysql-server

配置：
如果MySQL的版本≥5.5.3，可以把编码设置为utf8mb4
utf8mb4和utf8完全兼容，但它支持最新的Unicode标准，可以显示emoji字符。

vim /etc/my.cnf
[client]
default-character-set = utf8

[mysqld]
default-storage-engine = INNODB
character-set-server = utf8

授权
mysql> grant all on *.* to 'root'@'192.168.170.%' identified by '123456';
mysql> flush privileges;

简单介绍

使用Python进行MySQL的库主要有三个，Python-MySQL（更熟悉的名字可能是MySQLdb），PyMySQL和SQLAlchemy。

Python-MySQL资格最老，核心由C语言打造，接口精炼，性能最棒，缺点是环境依赖较多，安装复杂，近两年已停止更新，只支持Python2，不支持Python3。

PyMySQL为替代Python-MySQL而生，纯python打造，接口与Python-MySQL兼容，安装方便，支持Python3。

SQLAlchemy是一个ORM框架，它并不提供底层的数据库操作，而是要借助于MySQLdb、PyMySQL等第三方库来完成，目前SQLAlchemy在Web编程领域应用广泛。

本文介绍PyMySQL的使用

安装

PyMySQL 项目地址：https://github.com/PyMySQL/PyMySQL

pip安装
pip install PyMySQL

克隆安装
git clone https://github.com/PyMySQL/PyMySQL
cd PyMySQL/
python3 setup.py install

如果需要制定版本号，可以使用 curl 命令来安装：
# X.X 为 PyMySQL 的版本号
curl -L https://github.com/PyMySQL/PyMySQL/tarball/pymysql-X.X | tar xz
cd PyMySQL*
python3 setup.py install

测试

打印版本号和创建test.employee表
/*
def __init__(self, host=None, user=None, password="",
             database=None, port=0, unix_socket=None,
             charset='', sql_mode=None,
             read_default_file=None, conv=None, use_unicode=None,
             client_flag=0, cursorclass=Cursor, init_command=None,
             connect_timeout=10, ssl=None, read_default_group=None,
             compress=None, named_pipe=None, no_delay=None,
             autocommit=False, db=None, passwd=None, local_infile=False,
             max_allowed_packet=16*1024*1024, defer_connect=False,
             auth_plugin_map={}, read_timeout=None, write_timeout=None,
             bind_address=None):
*/

import pymysql

db = pymysql.connect('192.168.170.10', 'root', '123456', 'test')

cursor = db.cursor()

cursor.execute('select version()')
data = cursor.fetchone()
print('version %s' % data)

cursor.execute('drop table if exists employee')

sql = '''create table employee (
        first_name char(20) not null,
        last_name char(20),
        age int,
        sex char(1),
        income float)'''

cursor.execute(sql)
db.close()




数据库插入操作
import pymysql

db = pymysql.connect('192.168.170.10', 'root', '123456', 'test')

cursor = db.cursor()

sql = "insert into employee \
        (first_name, last_name, age, sex, income) \
        values ('%s', '%s', '%d', '%c', '%d')" \
        % ('andy', 'bob', 20, 'm', 2000)

try:
    cursor.execute(sql)
    db.commit()
except:
    db.rollback()

db.close()




数据库查询操作
Python查询Mysql使用 fetchone() 方法获取单条数据, 使用fetchall() 方法获取多条数据。
fetchone(): 该方法获取下一个查询结果集。结果集是一个对象
fetchall(): 接收全部的返回结果行.
rowcount: 这是一个只读属性，并返回执行execute()方法后影响的行数。

import pymysql

db = pymysql.connect('192.168.170.10', 'root', '123456', 'test')

cursor = db.cursor()

sql = "select * from employee where income > '%d'" % (1000)

try:
    cursor.execute(sql)
    results = cursor.fetchall()
    print(results)
    for row in results:
        fname = row[0]
        lname = row[1]
        age = row[2]
        sex = row[3]
        income = row[4]
        print('fname %s, lname %s, age %d, sex %s, income %d' % (fname, lname, age, sex, income))
except Exception as e:
    print('error', e)

db.close()





数据库更新操作
import pymysql

db = pymysql.connect('192.168.170.10', 'root', '123456', 'test')

cursor = db.cursor()

sql = "update employee set age = age + 1 where sex = '%c'" % ('m')

try:
    cursor.execute(sql)
    db.commit()
except Exception as e:
    db.rollback()
    print('error', e)

db.close()



执行事务
事务机制可以确保数据一致性。

事务应该具有4个属性：原子性、一致性、隔离性、持久性。这四个属性通常称为ACID特性。

原子性（atomicity）。一个事务是一个不可分割的工作单位，事务中包括的诸操作要么都做，要么都不做。
一致性（consistency）。事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。
隔离性（isolation）。一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
持久性（durability）。持续性也称永久性（permanence），指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。
Python DB API 2.0 的事务提供了两个方法 commit 或 rollback。

PyMySQL中可用的异常错误类

异常错误类的继承关系如下：

StandardError
|__Warning
|__Error
   |__InterfaceError
   |__DatabaseError
      |__DataError
      |__OperationalError
      |__IntegrityError
      |__InternalError
      |__ProgrammingError
      |__NotSupportedError

PyMySQL中异常错误类的具体说明如下：

–