如何将.csv文件中的数据传输到django中的sqlite数据库？ [英] How do I transfer data in .csv file into my sqlite database in django?
问题描述
这是我的models.py
from django.db import models
class School models.Model）：
school = models.CharField（max_length = 300）
def __unicode __（self）：
return self.school
class Lawyer（models.Model）：
firm_url = models.URLField（'Bio'，max_length = 200）
firm_name = models.CharField（'Firm'，max_length = 100）
first = models.CharField（'First Name' max_length = 50）
last = models.CharField（'Last Name'，max_length = 50）
year_graduated = models.IntegerField（'Year graduated'）
school = models.CharField（max_length = 300）
school = models.ForeignKey（School）
class Meta：
ordering =（'？'，）
def __unicode __（self）：
return self。 first
csv文件中的2个示例行：
http://www.graychase.com/aabbas,Gray& Chase LLP，Amr A，Abbas，The George Washington University Law School，2005
http://www.graychase.com/kadam,Gray& Chase LLP，Karin，Adam，Ernst Moritz Arndt大学Greifswald，2004
谢谢。 >
编辑
你能给出更详细的脚本信息吗？
我的应用程式位于
C：... / Documents / PROJECTS / Django / sw2 / wkw2。
$ b $ p
这个路径已经在PYTHONPATH中，
sys.path.append（'C：\\sw2' ）
os.environ 我尝试阅读文档，但我不明白。
os.environ ['DJANGO_SETTINGS_MODULE'] ='sw2.settings '
from django.core.management import setup_environ
from sw2 import settings
from sw2.wkw2.models import *
import csv
dataReader = csv.reader（open（'csvtest1.csv'），delimiter ='，'，quotechar =''）
for dataReader：
lawyer = lawyer（）
lawyer.firm_url = row [0]
lawyer.firm_name = row [1]
lawyer.first = row [2]
lawyer.last = row [ 3]
lawyer.school = row [4]
lawyer.year_graduated = row [5]
lawyer.save（）
谢谢！
编辑回应celopes的回答：
celopes：
我看到你的回答有点晚了。我一直在尝试更新数据库在shell中与
>>> p1 = Lawyer（school =The George Washington University Law School，last =Babbas，firm_url =http://www.graychase.com/babbas，year_graduated = 2005，firm_name =Gray& Chase， first =Amr A）
但我一直得到整数错误。最后，我意识到学校需要是 school_id ，即以
>>> p1 =律师（school_id = 1，last =Babbas，firm_url =http://www.graychase.com/babbas，year_graduated = 2005，firm_name =Gray& Chase，first =Amr A）
从这里我意识到，我需要知道每所学校的学校id，以更新律师表。由于这是不可能的，我决定删除 ForeignKey ，因为我不知道如何解决这个问题。 （我是Python和Django的新手。）
今天早上我看到你的答案。现在我改变了我的模型在开发服务器，我只有1表：律师。我想这是我将使用。
$ b
编辑12/14/09： b
celopes：
再次感谢这个指令码。它解决了我的问题。很好，我不需要将csv转换为json或另一种格式，然后保存到db。我做了几个更改。首先如前所述，我把模型改为律师。此外，您使用名称修复了重复的学校。但我有 list_display ， list_filter 和 search_fields c $ c> admin.py 并更改字段名称导致的错误太多。
class LawyerAdmin（admin.ModelAdmin）：
fieldsets = [
（'Name'，{'字段'：['last'，'first'，'firm_name'，'firm_url'，'school'，'year_graduated']}，
＃ ，'year_graduated']}），
]
list_display =（'last'，'first'，'firm_name'，'firm_url'，'school'，'year_graduated'）
list_filter = ['school'，'year_graduated']
search_fields = ['last'，'school'，'firm_name']
#search_fields = ['school__school']
#search_fields = ['school__lawyer__last ']
使用新的 models.py 简化的脚本运行良好。这是我正在使用的：
csv_filepathname =C：/ Users / A / Documents / Projects / Django / sw2 / wkw2 /fixtures/data1.csv
your_djangoproject_home =C：/ Users / A / Documents / PROJECTS / Django /
import sys，os
sys.path.append your_djangoproject_home）
os.environ ['DJANGO_SETTINGS_MODULE'] ='sw2.settings'
来自sw2.wkw2.models import Lawyer
import csv
dataReader = csv.reader（open（csv_filepathname），delimiter ='，'，quotechar =''）
for dataReader：
lawyer = Lawyer（）
lawyer .firm_url = row [0]
lawyer.firm_name = row [1]
lawyer.first = row [2]
lawyer.last = row [3]
lawyer.school = row [4]
lawyer.year_graduated = row [5]
lawyer.save（）
另外我删除了每行的引号，我注意到，如果我把年份引号，我得到了整数错误，没有引号工作正常你是如何使用引号？
再次感谢，这已经非常有帮助。
我创建了一个完整的脚本使用这些数据作为测试： / p>
http://www.graychase.com/aabbas，Gray& Chase LLP，Amr A 阿巴斯，乔治华盛顿大学法学院，2005
http://www.graychase.com/kadam\",Gray& Chase LLP，Karin，Adam Ernst Moritz Arndt University Greifswald，2004
请注意， 错误 。 csv文件读取器将读取整个行作为条目，因为整个行在引号中。从csv文件中的每一行中删除开头和结尾的引号，或者像我一样，用引号将每一个不同的值括在引号中。
将使用下面的脚本：
from django.db import models
class School模型）：
name = models.CharField（max_length = 300，unique = True）
def __unicode __（self）：
return self.name
$ b b class Lawyer（models.Model）：
firm_url = models.URLField（'Bio'，max_length = 200，unique = True）
firm_name = models.CharField（'Firm'，max_length = 100）
first = models.CharField（'First Name'，max_length = 50）
last = models.CharField（'Last Name'，max_length = 50）
year_graduated = models.IntegerField '）
school = models.ForeignKey（School）
def __unicode __（self）：
return self.first
这里是读取CSV文件的脚本（除非我有你的项目名称 sw2 wkw2 错误，然后修复这些引用）：
#### ########所有你需要修改的是以下############
＃csv文件的完整路径和名称
csv_filepathname =C：/用户/ A /文档/项目/ Django / sw2 / wkw2 / fixtures / data.csv
＃django项目目录正上方目录的完整路径
your_djangoproject_home =C：... / Documents / PROJECTS / Django /
############所有您需要修改的地方是上面############
import sys，os
sys.path.append（your_djangoproject_home）
os.environ ['DJANGO_SETTINGS_MODULE'] ='sw2.settings'
from sw2.wkw2.models import School，律师
import csv
dataReader = csv.reader（open（csv_filepathname），delimiter ='，'，quotechar =''）
old_school = None
对于dataReader中的行：
如果old_school！= row [4]：
old_school = row [4]
school = School（）
school.name = old_school
school.save（）
dataReader = csv.reader（open（csv_filepathname），delimiter ='，'，quotechar =''）
dataReader：
lawyer = Lawyer（）
lawyer.firm_url = row [0]
lawyer.firm_name = row [1]
lawyer.first = row [2]
lawyer.last = row [3]
lawyer_school = School.objects.get（name = row [4]）
lawyer.school = lawyer_school
律师.year_graduated = row [5]
lawyer.save（）
脚本首先创建来自可用学校的每一个可能的学校在CSV文件。然后它再次运行通过CSV并创建每一个律师。
我使用测试数据运行此脚本。它工作正常，并加载所有CSV数据。
This is my models.py
from django.db import models
class School(models.Model):
school = models.CharField(max_length=300)
def __unicode__(self):
return self.school
class Lawyer(models.Model):
firm_url = models.URLField('Bio', max_length=200)
firm_name = models.CharField('Firm', max_length=100)
first = models.CharField('First Name', max_length=50)
last = models.CharField('Last Name', max_length=50)
year_graduated = models.IntegerField('Year graduated')
school = models.CharField(max_length=300)
school = models.ForeignKey(School)
class Meta:
ordering = ('?',)
def __unicode__(self):
return self.first
And 2 sample rows from the csv file:
"http://www.graychase.com/aabbas,Gray & Chase LLP, Amr A ,Abbas,The George Washington University Law School, 2005"
"http://www.graychase.com/kadam,Gray & Chase LLP, Karin ,Adam,Ernst Moritz Arndt University Greifswald, 2004"
Thank you.
EDIT
Can you give a bit more detailed info about this script? Some questions:
My app is in
C:.../Documents/PROJECTS/Django/sw2/wkw2.
This path is already in PYTHONPATH do I still need this line? If so, do I enter it like this?
sys.path.append('C:\\sw2')
what does os.environ do? I tried to read the documentation but I didn't understand.
os.environ['DJANGO_SETTINGS_MODULE'] = 'sw2.settings'
from django.core.management import setup_environ
from sw2 import settings
from sw2.wkw2.models import *
import csv
dataReader = csv.reader(open('csvtest1.csv'), delimiter=',', quotechar='"')
for row in dataReader:
lawyer=Lawyer()
lawyer.firm_url=row[0]
lawyer.firm_name=row[1]
lawyer.first=row[2]
lawyer.last=row[3]
lawyer.school=row[4]
lawyer.year_graduated=row[5]
lawyer.save()
Thanks!
Edit in response to celopes' answer:
celopes:
I saw your answer a little late. I've been trying to update the database in the shell with
>>> p1 = Lawyer(school = "The George Washington University Law School", last = "Babbas", firm_url = "http://www.graychase.com/babbas", year_graduated = 2005, firm_name= "Gray & Chase", first= "Amr A")
but I kept getting the integer error. Finally, I realized that school needed to be the school_id, that is, in the form,
>>> p1 = Lawyer(school_id = 1, last = "Babbas", firm_url = "http://www.graychase.com/babbas", year_graduated = 2005, firm_name= "Gray & Chase", first= "Amr A")
From this I realized that, I needed to know the school id of each school to update the Lawyer table. Since that was not possible, I decided to remove the ForeignKey because I didn't know how to fix this. (I am new to both Python and Django.)
And this morning I saw your answer. Now I changed my model in the dev server and I have only 1 table: Lawyer. I think this is what I will be using. My apologies again for not seeing your answer earlier.
Edit 12/14/09:
celopes:
Thanks again for this script. It solved my problem. It's good that I don't need to convert the csv to json or another format before saving to the db. I have made a few changes. First as mentioned before, I changed the model to just Lawyer. Also, you fixed the duplicate school by using "name." But I have list_display, list_filter and search_fields in admin.py and changing fields names caused too many errors.
class LawyerAdmin(admin.ModelAdmin):
fieldsets = [
('Name', {'fields': ['last', 'first', 'firm_name', 'firm_url', 'school', 'year_graduated']}),
#('School', {'fields': ['school', 'year_graduated']}),
]
list_display = ('last', 'first', 'firm_name', 'firm_url', 'school', 'year_graduated')
list_filter = ['school', 'year_graduated']
search_fields = ['last', 'school', 'firm_name']
#search_fields = ['school__school']
#search_fields = ['school__lawyer__last']
With the new models.py the simplified script worked well. This is what I am using:
csv_filepathname="C:/Users/A/Documents/Projects/Django/sw2/wkw2/fixtures/data1.csv"
your_djangoproject_home="C:/Users/A/Documents/PROJECTS/Django/"
import sys,os
sys.path.append(your_djangoproject_home)
os.environ['DJANGO_SETTINGS_MODULE'] ='sw2.settings'
from sw2.wkw2.models import Lawyer
import csv
dataReader = csv.reader(open(csv_filepathname), delimiter=',', quotechar='"')
for row in dataReader:
lawyer=Lawyer()
lawyer.firm_url=row[0]
lawyer.firm_name=row[1]
lawyer.first=row[2]
lawyer.last=row[3]
lawyer.school=row[4]
lawyer.year_graduated=row[5]
lawyer.save()
Also I removed the quotes around each row. I noticed that if I put the the year in quotes, I got the integer error, without quotes works fine. How did you make it work with quotes?
Thanks again, this has been very helpful. Now I have to make it work in the production server.
I created a complete script using this data as a test:
"http://www.graychase.com/aabbas","Gray & Chase LLP","Amr A","Abbas","The George Washington University Law School","2005"
"http://www.graychase.com/kadam","Gray & Chase LLP","Karin","Adam","Ernst Moritz Arndt University Greifswald","2004"
Please mind that your CSV file as you exemplify above IS WRONG. The csv file reader will read the entire line as an entry because the entire line is in quotes. Either remove the beginning and trailing quotes from every line in the csv file or - like I did - enclose each distinct value in the line in quotes.
Here are your models that will work with the script below:
from django.db import models
class School(models.Model):
name = models.CharField(max_length=300, unique=True)
def __unicode__(self):
return self.name
class Lawyer(models.Model):
firm_url = models.URLField('Bio', max_length=200, unique=True)
firm_name = models.CharField('Firm', max_length=100)
first = models.CharField('First Name', max_length=50)
last = models.CharField('Last Name', max_length=50)
year_graduated = models.IntegerField('Year graduated')
school = models.ForeignKey(School)
def __unicode__(self):
return self.first
Here is the script that will read your CSV file (unless I got the name of you project sw2 and application wkw2 wrong, then fix those references):
############ All you need to modify is below ############
# Full path and name to your csv file
csv_filepathname="C:/Users/A/Documents/Projects/Django/sw2/wkw2/fixtures/data.csv"
# Full path to the directory immediately above your django project directory
your_djangoproject_home="C:.../Documents/PROJECTS/Django/"
############ All you need to modify is above ############
import sys,os
sys.path.append(your_djangoproject_home)
os.environ['DJANGO_SETTINGS_MODULE'] ='sw2.settings'
from sw2.wkw2.models import School, Lawyer
import csv
dataReader = csv.reader(open(csv_filepathname), delimiter=',', quotechar='"')
old_school = None
for row in dataReader:
if old_school != row[4]:
old_school = row[4]
school = School()
school.name = old_school
school.save()
dataReader = csv.reader(open(csv_filepathname), delimiter=',', quotechar='"')
for row in dataReader:
lawyer=Lawyer()
lawyer.firm_url=row[0]
lawyer.firm_name=row[1]
lawyer.first=row[2]
lawyer.last=row[3]
lawyer_school=School.objects.get(name=row[4])
lawyer.school=lawyer_school
lawyer.year_graduated=row[5]
lawyer.save()
The script is first creating every single possible school from the available schools in the CSV file. Then it runs through the CSV again and create every single lawyer.
I ran this script with the test data. It works just fine and loads all the CSV data.
这篇关于如何将.csv文件中的数据传输到django中的sqlite数据库？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
