python3---操作数据库（mysql）

操作MySQL

简介：

• Python3操作Mysql数据库 可以使用的模块式pymysql 和 MySQLdb。

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

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

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

实验演示

注意：数据库需要在 MySQL 中先创建出来

create database host_info  default charset utf8mb4 collate utf8mb4_general_ci;

• 注意授权

grant all on *.* to root@'%'  identified by 'Lwq@Lzy123';

安装包 pymysql

• pymsql是Python中操作MySQL的模块

pip3 install pymysql

基本操作：

• 创建表

import pymysql
  
# 创建连接
conn = pymysql.connect(
    host='127.0.0.1',  #数据库地址
    port=3306,             # 数据库端口
    user='root',           # 连接数据库的用户
    passwd='123',    # 连接数据库的密码
    db='host_info',   # 数据库的库名，需要先在 MySQL 里创建
    charset='utf8mb4'         # 字符集
)
# 获取游标对象
# cursor = conn.cursor()  
# ((1,), (2,))
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# [{"id": 1}, {"id": 2}]

# 定义 sql 语句, 创建第一个表 服务器基础信息表 base_info
sql = """
create table base_info
 (id int auto_increment primary key, 
  host_name varchar(64) not null, 
  kernel varchar(64),
  os varchar(64),
  manufacturer varchar(32),
  pod_name varchar(64),
  sn varchar(128),
  cpu_name varchar(64)
)"""


# 执行 sql 语句
cursor.execute(sql)
  
# 提交更改
conn.commit()

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

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

• 插入数据

import pymysql
  
# 创建连接
conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    passwd='QFedu123@',
    db='host_info',
    charset='utf8mb4'
)

# 获取游标
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

# 数据
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_name": "Intel(R) Core(TM) i5-5350U CPU @ 1.80GHz"
}

# 一次插入一条数据, 并且使用 pymysql 定义的变量占位符
# 编写语句的时候，字段的顺序需要和上面字段的字典中的 key 的顺序一致
sql = '''
 insert into base_info(
   manufacturer,
   pod_name,
   sn,
   host_name,
   kernel,
   os, 
   cpu_name
) values(%s, %s, %s, %s, %s, %s, %s);'''

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

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

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

一次插入多条数据

import pymysql
  
# 创建连接
conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    passwd='123',
    db='host_info',
    charset='utf8mb4'
)

# 获取游标
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

base_info = {
     "manufacturer": "VMware, Inc.",
     "pod_name": "VMware7,1",
     "sn": "VMware-56 4d 2d 4c 91 1e 48 15-5b d2 73 9c ec 98 da 22",
     "host_name": "sharkyum.com",
     "kernel": "3.10.0-957.el7.x86_64",
     "os": "CentOS Linux release 7.6.1810 (Core)",
     "cpu_name": "Intel(R) Core(TM) i5-5350U CPU @ 1.80GHz"
}

sql = '''
 insert into base_info(
   manufacturer, pod_name,
   sn, host_name,
   kernel, os, cpu_name
) values(%s, %s, %s, %s, %s, %s, %s);'''


# 语法：
# cursor.executemany(sql, [("v1","v2"), ("v3", "v4")])
"""
datas = []
for d in ...:
   # 把每条数据构造成元组("v1", "v2", 100)
   datas.append(d)

"""
infos = [
   tuple(base_info.values()),
    tuple(base_info.values())
]
cursor.executemany(sql, infos)
print(cursor.rowcount)
conn.commit()
cursor.close()
conn.close()

• 查询数据

import pymysql, json
  
# 创建连接
conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    passwd='123',
    db='host_info',
    charset='utf8mb4'
)

# 获取游标
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

# 定义一个查询语句
query_sql = """
select id, host_name, os 
from base_info 
where id > %s;
"""

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

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

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

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

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

cursor.close()
conn.close()
print("-" * 10)
print(f"共返回数据{row_nums}:")
print("-" * 25)
print(json.dumps(one_data, indent=4))
print("-" * 25)
print(json.dumps(many_data, indent=4))
print("-" * 25)
print(json.dumps(all_data, indent=4))