Python MySQL CRUD 示例

- 日理万妓 2023-10-03 08:47 48阅读 0赞

## **数据库设置** ##

您可以自行创建数据库名称、表等。

-- 导出 test_db 的数据库结构
    CREATE DATABASE IF NOT EXISTS `test_db` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
    USE `test_db`;
    
    -- 导出  表 test_db.cars 结构
    CREATE TABLE IF NOT EXISTS `cars` (
      `id` int NOT NULL AUTO_INCREMENT,
      `car_model` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
      `car_brand` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
      PRIMARY KEY (`id`),
      UNIQUE KEY `id_UNIQUE` (`id`)
    )

#### **Python 环境设置** ####

设置好数据库后，请确保您的 Python 环境也很好。

#### **PyMySQL 库** ####

检查`PyMySQL`您的系统中是否有库。

运行 a`pip3 list`并`PyMySQL`从列表中查找。如果找不到，请安装该库。

pip3 install PyMySQL

### **脚本编写** ###

现在让我们开始创建 CRUD 操作。让我们从创建操作开始。

### **创建记录示例** ###

使用`INSERT INTO`SQL 语句，它将插入（创建）一条新记录到表中。

SQL = """INSERT INTO test_db.cars(car_model, car_brand) VALUES ('accord', 'honda')"""

下面是`create_record.py`演示创建新记录示例的文件。

import pymysql.cursors
    
    SERVER_URL = "localhost"
    DB = "test_db"
    USER_NAME = "yourusername"
    PASSWORD = "yourpassword"
    
    SQL_CONNECTION = pymysql.connect(host=SERVER_URL,
                        user=USER_NAME,
                        passwd=PASSWORD,
                        db=DB,
                        charset='utf8mb4',
                        cursorclass=pymysql.cursors.DictCursor,
                        autocommit=True)
    
    SQL = """INSERT INTO test_db.cars(car_model, car_brand) VALUES ('accord', 'honda')"""
    
    with SQL_CONNECTION.cursor() as cursor:
        try:
            sql_exec = cursor.execute(SQL)
            if sql_exec:
                print(sql_exec)
                print("Record Added")
            else:
                print(sql_exec)
                print("Not Added")
        except (pymysql.Error, pymysql.Warning) as e:
            print(f'error! {e}')
    
        finally:
            SQL_CONNECTION.close()

### **读取记录示例** ###

使用`SELECT * FROM`SQL语句，它将查询表中的所有记录。

下面是从数据库表中读取记录的示例代码read\_record.py。

import pymysql.cursors
    
    SERVER_URL = "localhost"
    DB = "test_db"
    USER_NAME = "yourusername"
    PASSWORD = "yourpassword"
    
    SQL_CONNECTION = pymysql.connect(host=SERVER_URL,
                        user=USER_NAME,
                        passwd=PASSWORD,
                        db=DB,
                        charset='utf8mb4',
                        cursorclass=pymysql.cursors.DictCursor,
                        autocommit=True)
    
    SQL = """SELECT * FROM test_db.cars"""
    
    with SQL_CONNECTION.cursor() as cursor:
        try:
            sql_exec = cursor.execute(SQL)
            if sql_exec:
                print(sql_exec)
                print(cursor.fetchall())
            else:
                print(sql_exec)
                print("No Record")
        except (pymysql.Error, pymysql.Warning) as e:
            print(f'error! {e}')
        
        finally:
            SQL_CONNECTION.close()

### **更新记录示例** ###

使用`UPDATE test_db.cars SET...`SQL语句更新表中现有数据的信息。

下面是更新记录的示例代码update\_record.py。

import pymysql.cursors
    
    SERVER_URL = "localhost"
    DB = "test_db"
    USER_NAME = "yourusername"
    PASSWORD = "yourpassword"
    
    SQL_CONNECTION = pymysql.connect(host=SERVER_URL,
                        user=USER_NAME,
                        passwd=PASSWORD,
                        db=DB,
                        charset='utf8mb4',
                        cursorclass=pymysql.cursors.DictCursor,
                        autocommit=True)
    
    SQL = """UPDATE test_db.cars SET car_model = 'explorer', car_brand = 'ford' WHERE id = '3'"""
    
    with SQL_CONNECTION.cursor() as cursor:
        try:
            sql_exec = cursor.execute(SQL)
            if sql_exec:
                print(sql_exec)
                print("Record Upadted")
            else:
                print(sql_exec)
                print("Not Upadted")
        except (pymysql.Error, pymysql.Warning) as e:
            print(f'error! {e}')
    
        finally:
            SQL_CONNECTION.close()

### **删除记录示例** ###

使用`DELETE FROM`和`WHERE id =...`SQL 语句，它将删除指定的记录。

下面是删除指定记录的示例代码delete\_record.py。

import pymysql.cursors
    
    SERVER_URL = "localhost"
    DB = "test_db"
    USER_NAME = "yourusername"
    PASSWORD = "yourpassword"
    
    SQL_CONNECTION = pymysql.connect(host=SERVER_URL,
                        user=USER_NAME,
                        passwd=PASSWORD,
                        db=DB,
                        charset='utf8mb4',
                        cursorclass=pymysql.cursors.DictCursor,
                        autocommit=True)
    
    SQL = """DELETE FROM test_db.cars WHERE id = '4'"""
    
    with SQL_CONNECTION.cursor() as cursor:
        try:
            sql_exec = cursor.execute(SQL)
            if sql_exec:
                print(sql_exec)
                print("Record Deleted")
            else:
                print(sql_exec)
                print("Not Deleted")
        except (pymysql.Error, pymysql.Warning) as e:
            print(f'error! {e}')
    
        finally:
            SQL_CONNECTION.close()

###  ###