4.4 MySQL存储

目录

1、使用前提

2、使用连接数据库最初步骤

2.1 最初步骤

2.2 connect()方法中参数简单传递

3、创建数据库(创建架构)和创建表

3.1 创建数据库(创建架构)

3.2 创建表

3.2.1 基本创建

3.2.2 创建自增主键

4、Pycharm 可视化连接 MySQL 图形界面

5、插入、更新、查询、删除数据

5.1 插入数据

5.2 、更新数据

5.3、查询数据

5.4、删除数据

6. MySQL存储字典

1、使用前提

        安装MySQL数据库，安装pymysql第三方库。

2、使用连接数据库最初步骤

2.1 最初步骤

(1) 声明一个连接对象。使用connect()方法声明一个连接对象conn，参数为数据库的一些信息。依次为主机名、用户名、密码、端口号；

(2) 获得游标。使用cursor()方法获得游标；

(3) 执行 sql 语句。使用execute()方法；

(4)关闭游标；

(5) 关闭连接。

以下代码对数据库进行了连接，执行语句，得到版本。

import pymysql# (1) 声明一个连接对象
conn = pymysql.connect(host='localhost', user='root', passwd='1141477238', port=3306)   # (2) 建立游标
cursor = conn.cursor()# (3) 执行语句        
sql = "SELECT VERSION()"
cursor.execute(sql)data = cursor.fetchone()    # fetchone()得到第一条数据
print('DataBase version:', data)cursor.close()            # (4) 关闭游标
conn.close()                # (5) 关闭连接# 输出：DataBase version: ('8.3.0',)

2.2 connect()方法中参数简单传递

        在上面可以看到connect()方法中参数很多，若要多次连接，每次一个个写或者复制比较繁琐，所以下面采取更方便的传递方法。

        建立一个新python文件mysql_info.py，将其中参数设置为字典，如下：

mysql_local = {'host': 'localhost','user': 'root','passwd': '1141477238','port': 3306,
}

        在主要文件中使用如下：

from mysql_info import mysql_local#.......
conn = pymysql.connect(**mysql_local)
#.......

        **mysql_local会将mysql_local字典中的键值对展开，作为关键字参数传递给pymysql.connect函数。

3、创建数据库(创建架构)和创建表

3.1 创建数据库(创建架构)

# 使用语句create database 数据库名
# 默认编码方式urf-8
sql = "create database spider default character set utf8mb4"

3.2 创建表

3.2.1 基本创建

        使用CREATE TABLE 表名(表头)，此处为id，name，age。

import pymysqlconn = pymysql.connect(host='localhost', user='root',passwd='1141477238', port=3306, db='spider')
cursor = conn.cursor()
sql = ('CREATE TABLE IF NOT EXISTS student(id VARCHAR(255) NOT NULL, ''name VARCHAR(255) NOT NULL, ''age INT NOT NULL, ''PRIMARY KEY (id))')
cursor.execute(sql)
cursor.close()
conn.close()

        IF NOT EXISTS表明如果没有此表则创建该表，有则不会再创建表，即使改动后面的name、age等再执行一次，也不会有变化。若没有IF NOT EXISTS 在初始创建后再执行了一次该程序后再执行会报错，即存在表再创建会报错。

        NOT NULL表明不允许该键为空，否则报错。

3.2.2 创建自增主键

        该内容不与下方联系。

    sql = ('CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY, ''username VARCHAR(50),''email VARCHAR(100))')

         该主键id会随着数据的增多而自增，再 insert 语句中不需要自己添加。（主键是用来唯一标识每一条记录的，如果没有主键或者主键值为空，就无法唯一标识记录，会容易导致插入失败。）

def insert_movie():conn = pymysql.connect(**mysql_local)cursor = conn.cursor()username = 'hhhhh'email = '1111111'sql = f'INSERT INTO users (username, email) VALUES (%s, %s)'cursor.execute(sql, (username, email))conn.commit()conn.close()

        结果如下（第一条为最先插入的，现插入第二条）：

4、Pycharm 可视化连接 MySQL 图形界面

        在执行代码后观察。具体见链接http://t.csdnimg.cn/ZYZoQ

        根据上述代码，我们可以看到我们的数据库spider，点击表student，发现如下图所示：

        即建表成功，可以开始之后的操作。

5、插入、更新、查询、删除数据

5.1 插入数据

        使用insert into 表名(表头元素) values (%s, %s, %s)，之后execute()中传入参数，再比最初步骤多了一个连接提交conn.commit()即可。

        为了避免插入失败导致报错崩溃程序，加一层异常处理。若插入失败，则执行conn.rollback()，会发生数据回滚，相当于没插入这条数据。之后执行后面的程序。

import mysql# 插入数据
conn = pymysql.connect(host='localhost', user='root',passwd='1141477238', port=3306, db='spider')
cursor = conn.cursor()
id = '10001'
name = 'John'
age = 20
sql = 'insert into student(id, name, age) values (%s, %s, %s)'
try:cursor.execute(sql, (id, name, age))conn.commit()
except:conn.rollback()print("插入失败")
cursor.close()
conn.close()

5.2 、更新数据

        使用update 表明 set 要改变量名 = %s where id = %s（id为定位变量）

# 改id为10001的age
sql = 'update student set age = %s where id = %s'
cursor.execute(sql, (25, '10001'))

5.3、查询数据

        使用select * from 表名 where 条件"。执行后结果返回再游标对象里，使用fetchall()方法返回所有符合条件的结果。

初表：

# 查询数据
conn = pymysql.connect(host='localhost', user='root',passwd='1141477238', port=3306, db='spider')
cursor = conn.cursor()
sql = "select * from student where age >= 25"
cursor.execute(sql)
result = cursor.fetchall()
print(result)

执行代码结果：

5.4、删除数据

        改变 sql 语句即可，使用delete from 表名 where 条件即可

import mysqlconn = pymysql.connect(host='localhost', user='root',passwd='1141477238', port=3306, db='spider')
cursor = conn.cursor()
sql = "delete from student where age >= 25 "
cursor.execute(sql)
conn.commit()
cursor.close()
conn.close()

6. MySQL存储字典

        在上述发现表格数据id，name，age等要一个个写入比较麻烦，所以接下来介绍一个以字典的方式把数据存入MySQL。

        主要是将字典的键值拆开，分别合并为字符串，去对应 sql 语句中的内容，之后将excute()方法中的第二个参数设置为元组即可。

import pymysql
from mysql_info import mysql_local    # 插入数据此文件中要加入'db': '数据库名'data = {'id': '10005','name': 'HaLun','age': 26
}
table_name = 'student'
conn = pymysql.connect(**mysql_local)
cursor = conn.cursor()keys = ','.join(data.keys())    
s_value = ','.join(['%s'] * len(data))sql = f"INSERT INTO {table_name}({keys}) VALUES ({s_value})"
print(sql)    # 输出：INSERT INTO student(id,name,age) VALUES (%s,%s,%s)
print(tuple(data.values()))    # 输出：('10005', 'HaLun', 26)cursor.execute(sql, tuple(data.values()))
conn.commit()
cursor.close()
conn.close()

本人新手，若有错误，欢迎指正；若有疑问，欢迎讨论。若文章对你有用，点个小赞鼓励一下，谢谢，一起加油吧！