python - How do I transfer data in .csv file into my sqlite database in django?

Question

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.

Answer 1

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.