Python--简单操作数据库

一、 操作 MySQL

1. 基本介绍

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

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

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

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

default charset utf8mb4 collate utf8mb4_general_ci;

2. 安装包 pymysql

pymsql是Python中操作MySQL的模块

shell> pip3 install pymysql

3. 基本操作

示例数据

info = {
   
    "base_info": {
   
        "manufacturer": "VMware, Inc.",
        "pod_name": "VMware7,1",
        "sn": "VMware-56 4d 2b 4b 91 1e 48 15-5b d2 73 9c ec 98 da 22",
        "host_name": "qfedu.com",
        "kernel": "3.10.0-957.el7.x86_64",
        "os": "CentOS Linux release 7.6.1810 (Core)"
    },
    "cpu": {
   
        "cpu_name": "Intel(R) Core(TM) i5-5350U CPU @ 1.80GHz",
        "cpu_pyc": 1,
        "cpu_cores_each": 1
    },
    "memory": [
        {
   
            "capacity": " 8192 MB",
            "slot": " DIMM_B2",
            "model": " DDR3",
            "speed": " 1333 MT/s",
            "manufacturer": " 00CE00B380CE",
            "sn": " 82B79F71"
        },
        {
   
            "capacity": " 8192 MB",
            "slot": " DIMM_B3",
            "model": " DDR3",
            "speed": " 1333 MT/s",
            "manufacturer": " 00CE00B380CE",
            "sn": " 32CDDE81"
        },
        {
   
            "capacity": " No Module Installed",
            "slot": " DIMM_B4",
            "model": " DDR3",
            "speed": " Unknown",
            "manufacturer": "",
            "sn": ""
        },
        {
   
            "capacity": " 8192 MB",
            "slot": " DIMM_B5",
            "model": " DDR3",
            "speed": " 1333 MT/s",
            "manufacturer": " 00CE04B380CE",
            "sn": " 85966B82"
        },
        {
   
            "capacity": " 8192 MB",
            "slot": " DIMM_B6",
            "model": " DDR3",
            "speed": " 1333 MT/s",
            "manufacturer": " 000000B380CE",
            "sn": " 00000000"
        }
    ]
}

3.1 创建表

import pymysql，json
# JSON(JavaScript Object Notation, JS 对象标记) 是一种轻量级的数据交换格式。
#JSON的数据格式其实就是python里面的字典格式，里面可以包含方括号括起来的数组，也就是python里面的列表。

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