Summary of frequently asked questions about importing Excel data into Mysql: How to solve the problem of large batch insertion when importing data?

Summary of frequently asked questions about importing Excel data into Mysql: How to solve the problem of large batch insertion when importing data?

Importing Excel data to MySQL is one of the tasks often encountered in daily development. For importing a small amount of data, you can use database client tools or command lines to perform insertion operations. But when faced with large batches of data import, a simple single insert operation will undoubtedly cause serious performance problems. This article will describe how to solve this problem and give corresponding code examples.

Problem description:
In actual use, when large batches of data in the Excel table need to be imported into the MySQL database, the efficiency of single insertion is too low, resulting in a very slow import operation. This not only wastes a lot of time, but may also cause problems such as database connection timeout or memory overflow.

Solution:
In order to improve the efficiency of import, we can use batch insertion to insert multiple records into the database at one time. MySQL provides a variety of methods to achieve this purpose. Three commonly used methods will be introduced below.

1. Use INSERT INTO...VALUES statement
   Batch insertion is achieved by constructing INSERT INTO...VALUES statement. The specific steps are as follows:

① Read the Excel table and store the data in a two-dimensional array;
② Convert the two-dimensional array into the string form of the VALUES clause;
③ Splice the INSERT INTO statement and insert the VALUES clause into the database.

Code example:

import xlrd
import pymysql

# 连接数据库
conn = pymysql.connect(host='localhost', user='root', password='password', database='database')
cursor = conn.cursor()

# 读取Excel表格数据
data = xlrd.open_workbook('data.xlsx')
table = data.sheet_by_name('Sheet1')
rows = table.nrows

# 构建values子句
values = []
for i in range(1, rows):
    values.append(tuple(table.row_values(i)))

# 批量插入
sql = "INSERT INTO table_name (column1, column2, column3) VALUES (%s, %s, %s)"
cursor.executemany(sql, values)
conn.commit()

# 关闭连接
cursor.close()
conn.close()

2. Using the LOAD DATA INFILE statement
   MySQL provides the LOAD DATA INFILE statement for importing data from a file. By saving the data as a CSV file and then using the LOAD DATA INFILE statement to import it into the database at once, the import efficiency can be greatly improved.

Code example:

LOAD DATA INFILE 'data.csv'
INTO TABLE table_name
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '
'
IGNORE 1 LINES;

3. Using batch insertion tools
   In addition to manually writing code to implement batch insertion, you can also use some tools to automate processing. For example, you can use Python's pandas library to directly insert the data in the DataFrame into the MySQL database by calling the to_sql method.

Code example:

import pandas as pd
from sqlalchemy import create_engine

# 连接数据库
engine = create_engine('mysql+pymysql://root:password@localhost/database')

# 读取Excel表格数据
df = pd.read_excel('data.xlsx', sheet_name='Sheet1')

# 批量插入
df.to_sql('table_name', engine, if_exists='append', index=False)

# 关闭连接
engine.dispose()

Summary:
When importing Excel data into MySQL, using a single insertion method is inefficient and cannot meet the import needs of large batches of data. Through batch insertion or the use of tools, the efficiency of import can be significantly improved and the import time can be reduced. Which method to use depends on individual needs and circumstances. I hope the introduction and examples in this article can help readers solve the bulk insertion problems encountered when importing data.

The above is the detailed content of Summary of frequently asked questions about importing Excel data into Mysql: How to solve the problem of large batch insertion when importing data?. For more information, please follow other related articles on the PHP Chinese website!