ForLoop或executemany-Python和SQLite3

我最近开始学习Python和SQL并且有一个问题.

使用Python和SQLite3我编写了以下代码:

# Use sqlite3 in the file
import sqlite3

# Create people.db if it doesn't exist or connect to it if it does exist
with sqlite3.connect("people.db") as connection:
    c = connection.cursor()

    # Create new table called people
    c.execute("""CREATE TABLE IF NOT EXISTS people(firstname TEXT, lastname TEXT, age INT, occupation TEXT)""")


    people_list = [
        ('Simon', 'Doe', 20, 'Python Master'),
        ('John', 'Doe', 50, 'Java Master'),
        ('Jane', 'Doe', 30, 'C++ Master'),
        ('Smelly', 'Doe', 2, 'Shower Master')
    ]

    # Insert dummy data into the table
    c.executemany("""INSERT INTO people(firstname, lastname, age, occupation) VALUES(?, ?, ?, ?)""", people_list)

我注意到我可以使用for循环而不是executemany来执行相同的操作:

# Use sqlite3 in the file
import sqlite3

# Create people.db if it doesn't exist or connect to it if it does exist
with sqlite3.connect("people.db") as connection:
    c = connection.cursor()

# Create new table called people
c.execute("""CREATE TABLE IF NOT EXISTS people(firstname TEXT, lastname TEXT, age INT, occupation TEXT)""")


people_list = [
    ('Simon', 'Doe', 20, 'Python Master'),
    ('John', 'Doe', 50, 'Java Master'),
    ('Jane', 'Doe', 30, 'C++ Master'),
    ('Smelly', 'Doe', 2, 'Shower Master')
]

# Insert dummy data into the table
for person in people_list:
    c.execute("""INSERT INTO people(firstname, lastname, age, occupation) VALUES(?, ?, ?, ?)""", person)

我只是想知道哪一个更有效率并且更频繁地使用？

批量插入executemany将更有效,并且随着记录数量的增加,性能差异通常会非常大.执行insert语句会产生很大的开销,如果一次插入一行,就会反复出现这种情况.

只要直接插入,您就应该更喜欢批量插入.

在某些情况下,编写一次插入一行的算法可能要简单得多.例如,如果您一次从一个项目的某个地方接收数据,则可能更容易在您获取数据时插入数据,而不是在保存大量数据时构建列表并执行单个插入.在这种情况下,您需要考虑性能和代码简单性之间的权衡.通常最好从简单的方法开始(一次插入一行),然后如果发现它不具备性能,则只优化其他方法.