Django -- loading a CSV file into database problems

Question

I have a Django project powered by a MySQL database. I fed the database a CSV file (via raw SQL statements) with some 600 records. That went smoothly (almost, there was one error of Field 'customer_id' doesn't have a default value - no idea why). The problem is that these records are not showing up in the webapp itself.

For example, the CSV file contained a list of about 600 records describing client contact info. When I fire up the Django test server and go to the client contact page (which should list all the contact records -- the 600) nothing is there. Further, when I go into the Admin section and view the client contact records, there is nothing there -- However it shows that there is a total of 600 records right beside the pagination buttons. Also, there are 7 pages (7 pagination buttons) of records -- of course, there is nothing on any page.

What the heck?!

EDIT for More Details

The file I am trying to import is called subset_ressy_esc.csv and a couple of lines of it look like this:

R0138,Y,1432 MyRoad Ct,MyCity, MyProv,H0H 0H0,N,100.00,0,1,1
R0140,Y,268 MyStreet Link,MyCity,MyProv,H0H 0H0,N,100.00,0,1,1
R0142,Y,10994 123 St,MyCity,MyProv,H0H 0H0,N,0.00,1,0,0

And the fields they represent (in the same order shown above) are:

systemID (pk), isRessy, systemAddress, systemCity, systemProvince, systemPostalCode, isHoseBibb, servicePreauthAmt, noWorkRequired, SUAuthorized, BDAuthorized

Now the isRessy and isHoseBibb fields are select style fields, where the user selects from a drop down, while the noWorkReqd, SUAth, and BDAuth are booleans. The serviceAmt is a decimal (dollar) amt.

To import this data, I go into the mySql interpreter mysql -u garfonzo -p and run the following command:

mysql> load data local infile '/home/garfonzo/subset_ressy_esc.csv' into table systems_system fields terminated by ',' lines terminated by '\n' (systemID, isRessy, systemAddress, systemCity, systemProvince, systemPostalCode, isHoseBibb, servicePreauthAmt, noWorkRequired, SUAuthorized, BDAuthorized);
Query OK, 684 rows affected, 1 warning (0.01 sec)
Records: 684  Deleted: 0  Skipped: 0  Warnings: 0

mysql> show warnings;
+---------+------+-----------------------------------------------------+
| Level   | Code | Message                                             |
+---------+------+-----------------------------------------------------+
| Warning | 1364 | Field 'systemOwner_id' doesn't have a default value |
+---------+------+-----------------------------------------------------+

When I do a SELECT * FROM systems_system I spits out all 684 records, with all the right data. When I open the django project, nothing is listed but the admin has 7 pages of systems, without a single system actually present -- just 7 pages of nothing.

Any ideas?

what fields does your model have? which columns did you import from the csv? — second
– second, Commented Nov 20, 2011 at 19:48
@ second: I have a whole whack of fields in the model, and only import a few columns. For clarity, I will edit my question to include more specific details. Maybe that will help find the answer to this riddle. — Garfonzo
– Garfonzo, Commented Nov 22, 2011 at 3:31

Peter Rowell · Accepted Answer · 2011-11-20 18:52:16Z

1

Interesting. The fact that the admin shows the correct record count means that the table names match (that was my first thought). So it sees the app, the table, and the record count. My guess is one of two things:

You did not define a __str__() or __unicode__() method for the class, or
You did not set up the matching admin.py classes correctly and you are display a blank field.

My money is on number one.

answered Nov 20, 2011 at 18:52

Peter Rowell

17.8k2 gold badges52 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Garfonzo Over a year ago

Interesting, I'll look into #1. I know it isn't #2 since, before loading data VIA CSV, I was loading data through the webapp itself and was able to view the records nicely within the Admin section. It is only with this raw CSV loading that has caused these issues. I'll keep looking...

Garfonzo Over a year ago

I do have __unicode__ methods for all my models, but not __str__. My understanding is that the __str__ is not totally necessary if the __unicode__ method is defined well.

Peter Rowell Over a year ago

@Garfonzo: Correct, and the __unicode()__ method is the preferred way to go. Regarding admin vs CSV loading: I wonder if the primary key either didn't get set, or ...? What does something like phpMyAdmin show you for the PK?

Garfonzo Over a year ago

Thanks for your comments. I do not have phpMyAdmin setup on my server (although I may set it up if this continues to be a problem). After I run the command in the MySQL interpreter, I can do a SELECT * command and see that there are, in fact, 600+ new records all with there pks. Now, I do my schemamigration via "South" and so I know that it does some of its own magic behind the scenes, I wonder if there is an issue with that somehow...

Peter Rowell Over a year ago

@Garfonzo: It's possible. Do you see any of the records that you entered directly through the admin interface? If so, try dumping the table and looking for what it is about those records that differs from the ones you can't see -- there must be a pattern.

|

Visgean Skeloru · Accepted Answer · 2011-11-20 20:01:11Z

1

Are you sure that you have actually saved those records via .save() method? What does console show? I mean have you tried to manually iterate in shell? (manage.py shell) And lastly have you tried to restart the server?

Admin should not require __str__ nor __unicode__.

answered Nov 20, 2011 at 20:01

Visgean Skeloru

2,2631 gold badge24 silver badges34 bronze badges

Comments

Garfonzo · Accepted Answer · 2011-11-22 04:12:30Z

0

Looking at the warning of Field systemOwner_id doesn't have a default value didn't really bother me. But it suddenly dawned on my that I am not importing a value for that field, and that field cannot be empty. So, (for testing purposes) I changed my model so that that field systemOwner_id can be null, ran the same MySQL import and, voila, all systems are present. Nice!!

Thanks for the help -- working through this on this Q&A has helped solve my problem.

Cheers!

answered Nov 22, 2011 at 4:12

Garfonzo

3,9768 gold badges49 silver badges81 bronze badges

Collectives™ on Stack Overflow

Django -- loading a CSV file into database problems

3 Answers 3

6 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related