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

小开

看看这个。它只适用于 MySQL 的开箱即用，但是也有一些关于如何处理其他数据库的指南。

小开

最好是批量加载这些项目——准备一个文件并使用批量加载工具。这将是远远超过8000个单独的插入效率。

小开

最佳答案

你想看看 django.db.transaction.commit_manually。

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

所以应该是这样的:

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()

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

在 django 中引入了1.3上下文管理器。因此，现在你可以用类似的方式使用 < a href = “ https://docs.djangoproject.com/en/1.3/subject/db/actions/# control-action-management-in-views”rel = “ noReferrer”> transaction.commit _ on _ Success () :

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()

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

注意在使用批量创建时不会调用 save 方法。

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

在 django 1.6中引入了翻译: 奇芳翻译: 奇芳翻译: 奇芳翻译: 奇芳翻译: 奇芳翻译: 奇芳翻译: 奇芳翻译: 奇芳翻译: 奇芳翻译: 奇芳翻译: 奇芳翻译: 奇芳翻译: 奇芳翻译: 奇芳翻译: 奇芳，目的是取代现在的遗留函数 commit_on_success和 commit_manually。

来自姜戈原子能文件:

原子可以作为装饰物使用:

from django.db import transaction


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

作为上下文管理者:

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()

小开

我建议使用普通 SQL (而不是 ORM) ，您可以通过一次插入插入多行:

insert into A select from B;

只要结果与表 A 中的列匹配并且没有约束冲突，sql 的 从 B 中选择部分就可以像您希望的那样复杂。

小开

你应该看看 DSE。我编写 DSE 是为了解决这类问题(大规模插入或更新)。使用 django orm 是一个死胡同，您必须使用普通的 SQL，而 DSE 会为您解决大部分问题。

托马斯

小开

Django 1.4提供批量创建:

Https://django.readthedocs.io/en/1.4/ref/models/querysets.html#bulk-create

小开

为了回答这个问题，特别是关于 SQLite 的问题，正如我刚才所问的那样，虽然我刚刚证实了 mass _ create 确实提供了巨大的加速效果，但是 SQLite 有一个限制: “默认情况是在一个批处理中创建所有对象，除了 SQLite，其默认情况是每个查询最多使用999个变量。”

- 引用的内容来自文件-A-IV 提供了一个链接。

我必须补充的是，这些小东西条目的阿尔帕似乎也为我工作。它是一个小包装器，可以将要处理的大批处理分解为较小的批处理，从而管理999个变量的限制。

小开

def order(request):
if request.method=="GET":
cust_name = request.GET.get('cust_name', '')
cust_cont = request.GET.get('cust_cont', '')
pincode = request.GET.get('pincode', '')
city_name = request.GET.get('city_name', '')
state = request.GET.get('state', '')
contry = request.GET.get('contry', '')
gender = request.GET.get('gender', '')
paid_amt = request.GET.get('paid_amt', '')
due_amt = request.GET.get('due_amt', '')
order_date = request.GET.get('order_date', '')
print(order_date)
prod_name = request.GET.getlist('prod_name[]', '')
prod_qty = request.GET.getlist('prod_qty[]', '')
prod_price = request.GET.getlist('prod_price[]', '')
print(prod_name)
print(prod_qty)
print(prod_price)
# insert customer information into customer table
try:
# Insert Data into customer table
cust_tab = Customer(customer_name=cust_name, customer_contact=cust_cont, gender=gender, city_name=city_name, pincode=pincode, state_name=state, contry_name=contry)
cust_tab.save()
# Retrive Id from customer table
custo_id = Customer.objects.values_list('customer_id').last()   #It is return
Tuple as result from Queryset
custo_id = int(custo_id[0]) #It is convert the Tuple in INT
# Insert Data into Order table
order_tab = Orders(order_date=order_date, paid_amt=paid_amt, due_amt=due_amt, customer_id=custo_id)
order_tab.save()
# Insert Data into Products table
# insert multiple data at a one time from djanog using while loop
i=0
while(i<len(prod_name)):
p_n = prod_name[i]
p_q = prod_qty[i]
p_p = prod_price[i]
# this is checking the variable, if variable is null so fill the varable value in database
if p_n != "" and p_q != "" and p_p != "":
prod_tab = Products(product_name=p_n, product_qty=p_q, product_price=p_p, customer_id=custo_id)
prod_tab.save()
i=i+1

小开

def order(request):
if request.method=="GET":
# get the value from html page
cust_name = request.GET.get('cust_name', '')
cust_cont = request.GET.get('cust_cont', '')
pincode = request.GET.get('pincode', '')
city_name = request.GET.get('city_name', '')
state = request.GET.get('state', '')
contry = request.GET.get('contry', '')
gender = request.GET.get('gender', '')
paid_amt = request.GET.get('paid_amt', '')
due_amt = request.GET.get('due_amt', '')
order_date = request.GET.get('order_date', '')
prod_name = request.GET.getlist('prod_name[]', '')
prod_qty = request.GET.getlist('prod_qty[]', '')
prod_price = request.GET.getlist('prod_price[]', '')


# insert customer information into customer table
try:
# Insert Data into customer table
cust_tab = Customer(customer_name=cust_name, customer_contact=cust_cont, gender=gender, city_name=city_name, pincode=pincode, state_name=state, contry_name=contry)
cust_tab.save()
# Retrive Id from customer table
custo_id = Customer.objects.values_list('customer_id').last()   #It is return Tuple as result from Queryset
custo_id = int(custo_id[0]) #It is convert the Tuple in INT
# Insert Data into Order table
order_tab = Orders(order_date=order_date, paid_amt=paid_amt, due_amt=due_amt, customer_id=custo_id)
order_tab.save()
# Insert Data into Products table
# insert multiple data at a one time from djanog using while loop
i=0
while(i<len(prod_name)):
p_n = prod_name[i]
p_q = prod_qty[i]
p_p = prod_price[i]


# this is checking the variable, if variable is null so fill the varable value in database
if p_n != "" and p_q != "" and p_p != "":
prod_tab = Products(product_name=p_n, product_qty=p_q, product_price=p_p, customer_id=custo_id)
prod_tab.save()
i=i+1


return HttpResponse('Your Record Has been Saved')
except Exception as e:
return HttpResponse(e)


return render(request, 'invoice_system/order.html')