本文链接：https://www.jianshu.com/p/90239ed59b29
作者: 西瓜甜

一、 操作 MySQL

1. 基本介绍

Python3 操作 MySQL 数据库 可以使用的模块是 pymysql 和 MySQLdb。

这个两个模块都是通过自己的 API 执行原生的 SQL 语句实现的。

MySQLdb 是最早出现的一个操作 MySQL 数据库的模块，核心由C语言编写，接口精炼，性能最棒，缺点是环境依赖较多，安装稍复杂，特别是 windows 中不好安装。更新较慢。

pymysql 为替代 MySQLdb 而生，纯 Python 实现，API 的接口与 MySQLdb 完全兼容，安装方便。

2. 安装包 pymysql

1. 方案一 pymsql

pymsql是Python中操作MySQL的模块

shell> pip3 install pymysql

基本使用

import pymysql

# 获取连接到 DB 的对象
db = pymysql.connect(host='172.16.153.10',
                       port=3306,
                       user='root',
                       passwd='123', 
                       db='shark_db',
# 获取游标，之后使用游标操作表
cursor = conn.cursor()

2. 方案2 mysqlclient

安装 mysqlclient

适用于 Centos7

安装系统依赖库和编译器

yum   install   mariadb-devel   gcc   

• 如果是系统自带的 python2环境，安装 Python2 依赖库

python-devel

• 如果是 python3环境，编译安装 python3 后就不用再操作了

安装 mysqlclient

pip   install   mysqlclient

基本使用

import MySQLdb

# 获取连接到 DB 的对象
db=MySQLdb.connect(host='172.16.153.10',passwd="moonpie",db="thangs")

# 获取游标，之后使用游标操作表
c=db.cursor()

3. 基本操作

3.1 创建表

import pymysql
  
# 创建连接
conn = pymysql.connect(host='172.16.153.10',
                       port=3306,
                       user='root',
                       passwd='123', 
                       db='shark_db',
                       charset='utf8mb4')
# 获取游标对象
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

# 定义 sql 语句, 创建第一个表 服务器基础信息表 base_info
sql1 = """create table base_info
 (id int auto_increment primary key, 
  {} varchar(64) not null, 
  {} varchar(64),
  {} varchar(64),
  {} varchar(32),
  {} varchar(64),
  {} varchar(64),
  {} varchar(64),
  {} int not null,
  {} int not null)"""

# 创建第二个表 内存表  memory
sql2 = '''
create table memory(
    id int auto_increment primary key,
    capacity varchar(32),
    slot varchar(16),
    model varchar(4),
    speed varchar(32),
    manufacturer varchar(128),
    sn varchar(128),
    server_id int
) 
'''

# 执行 sql 语句
cursor.execute(sql1)
cursor.execute(sql2)

  
# 提交更改
conn.commit()

# 关闭游标对象
cursor.close()

# 关闭连接对象
conn.close()

3.2 插入数据

一次插入一条数据

# 一次插入一条数据, 并且使用 pymysql 定义的变量占位符
insert_data_sql = '''insert into base_info(host_name, kernel,
                    os, manufacturer, pod_name, sn,
                    cpu_name, cpu_num, cpu_cores_each) 
                    values(%s, %s, %s, %s, %s, %s, %s, %s, %s);'''

# 注意这里不是 Python 的字符串格式化，所以传值的时候不需要使用 %
cursor.execute(insert_data_sql, tuple(dic['base_info'].values()))

# 数据库中受影响的行数
print(cursor.rowcount)

conn.commit()
cursor.close()
conn.close()

一次插入多条数据

示例数据:

dic = {"mem": [{
            'capacity': '8192 MB',
            'slot': 'DIMM_A3',
            'model': 'DDR3',
            'speed': '1333 MT/s',
            'manufacturer': '00CE04B380CE',
            'sn': '8362A2F8'
        },
        {
            'capacity': 'No Module Installed',
            'slot': 'DIMM_A4',
            'model': 'DDR3',
            'speed': 'Unknown',
            'manufacturer': '',
            'sn': ''
        }
    ]
}

定义插入数据的语句
many_sql = '''
insert into memory(
    capacity, slot, model, speed, manufacturer, sn, server_id
) values(%s, %s, %s, %s, %s, %s, %s)
'''
li = []

for item in dic['mem']:
    item['server_id'] = 1
    li.append(tuple(item.values()))

cursor.executemany(many_sql, li)

conn.commit()
cursor.close()
conn.close()

3.3 查询数据

1) 获取到的数据是元组类型

定义一个查询语句
query_sql = "select id, host_name, os from base_info;"

执行查询语句，并且返回得到结果的行数
row_nums = cursor.execute(query_sql, ('shark1'))

"""
获取到数据结果集具有迭代器的特性:
1. 可以通过索引取值，可以切片
2. 结果集中的数据每次取出一条就少一条
"""

获取结果集中的第一条数据， 注意不是整个表的第一条数据
one_data = cursor.fetchone()

获取结果集中接下来的第 2 条和 第 3 条 数据
many_data = cursor.fetchmany(2)

获取结果集中剩余的全部数据 
all_data = cursor.fetchall()

cursor.close()
conn.close()
print(row_nums)
print(one_data)
print(many_data)
print(all_data)