MySQL 不用存儲過程循環(huán)插入數(shù)據(jù)的方法

更新時間：2024年09月14日 09:38:46 作者：牛肉胡辣湯

在MySQL中,使用INSERT INTO VALUES語句可以一次性插入多行數(shù)據(jù),提高插入效率,還可通過Python的pymysql庫生成和執(zhí)行插入語句,這不僅減少了操作時間,還提高了代碼的簡潔性和執(zhí)行效率

在 MySQL 中，有時候我們需要向數(shù)據(jù)庫中插入大量數(shù)據(jù)，而且這些數(shù)據(jù)可能需要進行一些處理或者計算。一種常見的做法是使用存儲過程，在其中使用循環(huán)來逐條插入數(shù)據(jù)。然而，存儲過程的執(zhí)行效率可能不高，并且對于大批量數(shù)據(jù)的插入，這種方式可能會導致性能問題。在本文中，我們將介紹一種不使用存儲過程循環(huán)插入數(shù)據(jù)的方法，而是通過 SQL 語句一次性插入大量數(shù)據(jù)，以提高插入效率。

使用 INSERT INTO VALUES 插入多行數(shù)據(jù)

MySQL 提供了 INSERT INTO VALUES 語句，可以一次性插入多行數(shù)據(jù)，這種方式比循環(huán)插入數(shù)據(jù)效率更高。假設我們有一個名為 students 的表，包含 id 和 name 兩個字段，我們想要向這個表中插入多條數(shù)據(jù)，可以使用以下 SQL 語句：

INSERT INTO students (id, name)
VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie'),
...
(n, 'Zoe');

其中，n 表示要插入的數(shù)據(jù)條數(shù)，每一條數(shù)據(jù)用括號括起來，用逗號分隔，分別對應表中的各個字段的值。

使用生成器生成插入數(shù)據(jù)的語句

如果需要插入的數(shù)據(jù)量很大，手動編寫這樣的 SQL 語句會非常繁瑣。為了簡化操作，我們可以使用生成器來生成插入數(shù)據(jù)的 SQL 語句。例如，我們可以使用 Python 中的列表推導式來生成插入數(shù)據(jù)的語句：

values = [(i, f'Student{i}') for i in range(1, 1001)]
sql = "INSERT INTO students (id, name) VALUES " + ", ".join(["(%s, '%s')" % (v[0], v[1]) for v in values])
print(sql)

這段代碼將生成一個包含 1000 條插入數(shù)據(jù)的 SQL 語句，然后我們可以直接將這個 SQL 語句在 MySQL 中執(zhí)行，實現(xiàn)高效地插入大量數(shù)據(jù)。

當實際應用場景中需要向MySQL中插入大量數(shù)據(jù)時，可以使用以下示例代碼:

import pymysql
# 連接MySQL數(shù)據(jù)庫
connection = pymysql.connect(
    host='localhost',
    user='root',
    password='password',
    database='test'
)
# 創(chuàng)建游標對象
cursor = connection.cursor()
# 準備要插入的數(shù)據(jù)
data = [
    (1, 'Alice'),
    (2, 'Bob'),
    (3, 'Charlie'),
    ...
    (n, 'Zoe')
]
# 生成插入數(shù)據(jù)的SQL語句
sql = "INSERT INTO students (id, name) VALUES (%s, %s)"
try:
    # 執(zhí)行插入操作
    cursor.executemany(sql, data)
    
    # 提交事務
    connection.commit()
    print("數(shù)據(jù)插入成功！")
except Exception as e:
    # 發(fā)生錯誤時回滾
    connection.rollback()
    print("數(shù)據(jù)插入失??！Error:", e)
# 關閉連接
cursor.close()
connection.close()

上述代碼中，通過pymysql庫連接到MySQL數(shù)據(jù)庫，并創(chuàng)建了游標對象。然后，準備要插入的數(shù)據(jù)列表，其中每個元組表示一條數(shù)據(jù)。接下來，生成插入數(shù)據(jù)的SQL語句，使用executemany()方法將數(shù)據(jù)批量插入數(shù)據(jù)庫中。最后，通過commit()提交事務，或者在插入失敗時使用rollback()進行回滾操作。最后，關閉游標和數(shù)據(jù)庫連接。在實際應用中，你可以根據(jù)自己的需求修改代碼，設置合適的數(shù)據(jù)庫連接信息和表字段信息，以及生成數(shù)據(jù)的方式。

pymysql 是一個純 Python 實現(xiàn)的用于連接和操作 MySQL 數(shù)據(jù)庫的庫。它提供了一種簡單而有效的方式來與 MySQL 數(shù)據(jù)庫進行交互，并執(zhí)行各種數(shù)據(jù)庫操作，如連接、查詢、插入、更新和刪除等。以下是 pymysql 庫的一些主要特點：

純 Python 實現(xiàn)：pymysql 是一個純 Python 庫，無需額外的編譯和安裝即可在 Python 環(huán)境中使用。這使得它易于安裝和部署。
支持 Python 數(shù)據(jù)類型：pymysql 支持將 Python 數(shù)據(jù)類型直接映射到 MySQL 數(shù)據(jù)庫中的相應數(shù)據(jù)類型。例如，你可以使用 Python 的字符串、數(shù)字、日期等直接插入數(shù)據(jù)庫，而不需要手動轉換數(shù)據(jù)類型。
支持事務處理：pymysql 提供了對事務的支持，可以使用 connection 對象的 commit() 方法提交事務，并使用 rollback() 方法在出現(xiàn)錯誤時進行回滾操作，以保證數(shù)據(jù)的一致性和完整性。
兼容性與擴展性：pymysql 兼容 MySQL 數(shù)據(jù)庫的多個版本，并支持高級功能，如復雜查詢、存儲過程、觸發(fā)器等。此外，pymysql 可以通過插件開發(fā)來擴展其功能，以滿足不同的需求。
安全可靠：pymysql 庫采用參數(shù)化查詢的方式來執(zhí)行 SQL 語句，從而有效地防止 SQL 注入攻擊。它還提供了連接池功能，可以在多線程環(huán)境下安全地管理數(shù)據(jù)庫連接。下面是一個簡單示例，展示了如何使用 pymysql 連接到 MySQL 數(shù)據(jù)庫，并查詢數(shù)據(jù)：

import pymysql
# 連接數(shù)據(jù)庫
connection = pymysql.connect(
    host='localhost',
    user='username',
    password='password',
    database='databasename'
)
# 創(chuàng)建游標對象
cursor = connection.cursor()
# 執(zhí)行查詢
sql = "SELECT * FROM students"
cursor.execute(sql)
# 獲取查詢結果
results = cursor.fetchall()
for row in results:
    id = row[0]
    name = row[1]
    print(f"ID: {id}, Name: {name}")
# 關閉游標和數(shù)據(jù)庫連接
cursor.close()
connection.close()

在這個示例中，我們使用 pymysql 連接到 MySQL 數(shù)據(jù)庫，然后創(chuàng)建了一個游標對象 cursor。接下來，執(zhí)行了一個查詢語句，獲取了所有的學生數(shù)據(jù)，并遍歷打印出每一條數(shù)據(jù)的 ID 和 Name。最后，關閉了游標和數(shù)據(jù)庫連接。總的來說，pymysql 提供了方便易用的 API 來連接和操作 MySQL 數(shù)據(jù)庫，可以幫助你在 Python 中輕松進行數(shù)據(jù)庫操作。無論是簡單的查詢還是復雜的事務處理，pymysql 都能夠滿足你的需求，并提供安全可靠的數(shù)據(jù)庫訪問方式。