使用 Django 将数千条记录插入到 SQLite 表中的有效方法是什么?

问题描述

我必须使用 Django 的 ORM 将 8000 多条记录插入到 SQLite 数据库中.此操作需要大约每分钟作为 cronjob 运行一次.
目前，我正在使用 for 循环遍历所有项目，然后将它们一一插入.
示例:

 用于项目中的项目:entry = Entry(a1=item.a1, a2=item.a2)入口.save()

这样做的有效方法是什么?

两种插入方法的一些比较.

没有 commit_manually 装饰器(11245 条记录):

nox@noxdevel Marinetraffic]$ time python manage.py insrec真实 1m50.288s用户 0m6.710s系统 0m23.445s

使用 commit_manually 装饰器(11245 条记录):

[nox@noxdevel Marinetraffic]$ time python manage.py insrec真正的 0m18.464s用户 0m5.433s系统 0m10.163s

注意:test 脚本除了插入到数据库中之外，还执行一些其他操作(下载 ZIP 文件，从 ZIP 存档中提取 XML 文件，解析 XML文件)，所以执行所需的时间并不一定代表插入记录所需的时间.

您想查看django.db.transaction.commit_manually.

http://docs.djangoproject.com/en/dev/topics/db/transactions/#django-db-transaction-commit-manually

所以它会是这样的:

from django.db 导入事务@transaction.commit_manuallydef viewfunc(请求):...对于项目中的项目:entry = Entry(a1=item.a1, a2=item.a2)入口.save()交易.commit()

它只会提交一次，而不是在每次 save() 时提交.

在 django 1.3 中引入了上下文管理器.所以现在你可以使用 transaction.commit_on_success() 以类似的方式:

from django.db 导入事务def viewfunc(请求):...使用 transaction.commit_on_success():对于项目中的项目:entry = Entry(a1=item.a1, a2=item.a2)入口.save()

在 django 1.4 中，bulk_create 已添加，允许您创建模型对象的列表，然后一次性提交它们.

注意使用批量创建时不会调用保存方法.

在 django 1.6 中，transaction.atomic 被引入，旨在替换现在的遗留函数 commit_on_success 和 commit_manually.

来自 django 关于原子的文档:

atomic 既可用作装饰器:

from django.db 导入事务@transaction.atomicdef viewfunc(请求):# 此代码在事务内执行.做东西()

作为上下文管理器:

from django.db 导入事务def viewfunc(请求):# 此代码在自动提交模式下执行(Django 的默认设置).做东西()使用 transaction.atomic():# 此代码在事务内执行.do_more_stuff()

I have to insert 8000+ records into a SQLite database using Django's ORM. This operation needs to be run as a cronjob about once per minute.
At the moment I'm using a for loop to iterate through all the items and then insert them one by one.
Example:

for item in items:
    entry = Entry(a1=item.a1, a2=item.a2)
    entry.save()

What is an efficient way of doing this?

Edit: A little comparison between the two insertion methods.

Without commit_manually decorator (11245 records):

nox@noxdevel marinetraffic]$ time python manage.py insrec             

real    1m50.288s
user    0m6.710s
sys     0m23.445s

Using commit_manually decorator (11245 records):

[nox@noxdevel marinetraffic]$ time python manage.py insrec                

real    0m18.464s
user    0m5.433s
sys     0m10.163s

Note: The test script also does some other operations besides inserting into the database (downloads a ZIP file, extracts an XML file from the ZIP archive, parses the XML file) so the time needed for execution does not necessarily represent the time needed to insert the records.

You want to check out django.db.transaction.commit_manually.

http://docs.djangoproject.com/en/dev/topics/db/transactions/#django-db-transaction-commit-manually

So it would be something like:

from django.db import transaction

@transaction.commit_manually
def viewfunc(request):
    ...
    for item in items:
        entry = Entry(a1=item.a1, a2=item.a2)
        entry.save()
    transaction.commit()

Which will only commit once, instead at each save().

In django 1.3 context managers were introduced. So now you can use transaction.commit_on_success() in a similar way:

from django.db import transaction

def viewfunc(request):
    ...
    with transaction.commit_on_success():
        for item in items:
            entry = Entry(a1=item.a1, a2=item.a2)
            entry.save()

In django 1.4, bulk_create was added, allowing you to create lists of your model objects and then commit them all at once.

NOTE the save method will not be called when using bulk create.

>>> Entry.objects.bulk_create([
...     Entry(headline="Django 1.0 Released"),
...     Entry(headline="Django 1.1 Announced"),
...     Entry(headline="Breaking: Django is awesome")
... ])

In django 1.6, transaction.atomic was introduced, intended to replace now legacy functions commit_on_success and commit_manually.

from the django documentation on atomic:

atomic is usable both as a decorator:

from django.db import transaction

@transaction.atomic
def viewfunc(request):
    # This code executes inside a transaction.
    do_stuff()

and as a context manager:

from django.db import transaction

def viewfunc(request):
    # This code executes in autocommit mode (Django's default).
    do_stuff()

    with transaction.atomic():
        # This code executes inside a transaction.
        do_more_stuff()

这篇关于使用 Django 将数千条记录插入到 SQLite 表中的有效方法是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！