使用Python从.txt文件填充SQLite3数据库

问题描述

我正在尝试在Django中建立一个网站，允许用户将查询发送到包含有关其在欧洲议会中的代表信息的数据库。我将数据保存在逗号分隔的.txt文件中，格式如下：

I am trying to setup a website in django which allows the user to send queries to a database containing information about their representatives in the European Parliament. I have the data in a comma seperated .txt file with the following format:

7，英国的玛塔·安德里亚森，欧洲自由民主集团，英国独立党，成员

7, Marta Andreasen, United Kingdom, Europe of freedom and democracy Group, United Kingdom Independence Party, Member

等...。

我想用这些数据填充一个SQLite3数据库，但是到目前为止，我发现的所有教程都仅展示了如何手动执行此操作。由于文件中有736个观测值，所以我真的不想这样做。

I want to populate a SQLite3 database with this data, but so far all the tutorials I have found only show how to do this by hand. Since I have 736 observations in the file I dont really want to do this.

我怀疑这很简单，但是如果有人可以向我展示如何做到这一点，我将不胜感激。

I suspect this is a simple matter, but I would be very grateful if someone could show me how to do this.

托马斯

推荐答案

所以假设您的 models.py 看起来像这样：

So assuming your models.py looks something like this:

class Representative(models.Model):
    parliament = models.CharField(max_length=128)
    name = models.CharField(max_length=128)
    country = models.CharField(max_length=128)
    party_group = models.CharField(max_length=128)
    national_party = models.CharField(max_length=128)
    position = models.CharField(max_length=128)

然后可以运行 python manage.py shell 并执行以下命令：

You can then run python manage.py shell and execute the following:

import csv
from your_app.models import Representative
# If you're using different field names, change this list accordingly.
# The order must also match the column order in the CSV file.
fields = ['parliament', 'name', 'country', 'party_group', 'national_party', 'position']
for row in csv.reader(open('your_file.csv')):
    Representative.objects.create(**dict(zip(fields, row)))

您已完成。

附录（编辑）

根据托马斯的要求，以下是对 ** dict（zip（fields，row））的作用的解释：

Per Thomas's request, here's an explanation of what **dict(zip(fields,row)) does:

因此，最初，字段包含我们定义的字段名称的列表，而 row 包含代表以下内容的值的列表

So initially, fields contains a list of field names that we defined, and row contains a list of values that represents the current row in the CSV file.

fields = ['parliament', 'name', 'country', ...]
row = ['7', 'Marta Andreasen', 'United Kingdom', ...]

zip（）的作用是将两个列表组合成两个列表中的成对物品列表（例如拉链）；即 zip（['a'，'b，'c']，['A'，'B'，'C']）将返回 [（（'a'，'A'），（'b'，'B'），（'c'，'C'）] 。因此，在我们的情况下：

What zip() does is it combines two lists into one list of pairs of items from both lists (like a zipper); i.e. zip(['a','b,'c'], ['A','B','C']) will return [('a','A'), ('b','B'), ('c','C')]. So in our case:

>>> zip(fields, row)
[('parliament', '7'), ('name', 'Marta Andreasen'), ('country', 'United Kingdom'), ...]

dict（）函数仅转换成对的列表

The dict() function simply converts the list of pairs into a dictionary.

>>> dict(zip(fields, row))
{'parliament': '7', 'name': 'Marta Andreasen', 'country': 'United Kingdom', ...}

** 是将字典转换为关键字参数的一种方式功能列表。因此 function（** {'key'：'value'}）等同于 function（key ='value'）。因此在示例中，调用 create（** dict（zip（zip（field，row）））等效于：

The ** is a way of converting a dictionary into a keyword argument list for a function. So function(**{'key': 'value'}) is the equivalent of function(key='value'). So in out example, calling create(**dict(zip(field, row))) is the equivalent of:

create(parliament='7', name='Marta Andreasen', country='United Kingdom', ...)

希望这可以清除一切。

这篇关于使用Python从.txt文件填充SQLite3数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！