Importing CSV into MySQL Database (Django Webapp)

Question

I'm developing a webapp in Django, and for it's database I need to import a CSV file into a particular MySQL database.

I searched around a bit, and found many pages which listed how to do this, but I'm a bit confused.

Most pages say to do this:

LOAD DATA INFILE '<file>' INTO TABLE <tablenname>
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n';

But I'm confused how Django would interpret this, since we haven't mentioned any column names here. I'm new to Django and even newer to databasing, so I don't really know how this would work out.

See the answers in https://stackoverflow.com/questions/10154633/load-csv-data-into-mysql-in-python, it's done. — M4r, Oct 31 '17 at 20:23

score 2 · Answer 1 · answered Jun 28 '12 at 12:58

It looks like you are in the database admin (i.e. PostgreSQL/MySQL). Others above has given a good explanation for that.

But if you want to import data into Django itself -- Python has its own csv implementation, like so: import csv.

But if you're new to Django, then I recommend installing something like the Django CSV Importer: http://django-csv-importer.readthedocs.org/en/latest/index.html. (You install the add-ons into your Python library.)

The author, unfortunately, has a typo in the docs, though. You have to do from csvImporter.model import CsvDbModel, not from csv_importer.model import CsvDbModel.

In your models.py file, create something like:

class MyCSVModel(CsvDbModel):
    pass  
    class Meta:
        dbModel = Model_You_Want_To_Reference
        delimiter = ","
        has_header = True

Then, go into your Python shell and do the following command: my_csv = MyCsvModel.import_data(data = open("my_csv_file_name.csv"))

django-csv-importer has been replaced by django-adaptors now. — trez, Dec 29 '12 at 09:11

score 0 · Answer 2 · answered Jun 28 '12 at 12:51

This isn't Django code, and Django does not care what you call the columns in your CSV file. This is SQL you run directly against your database via the DB shell. You should look at the MySQL documentation for more details, but it will just take the columns in order as they are defined in the table.

If you want more control, you could write some Python code using the csv module to load and parse the file, then add it to the database via the Django ORM. But this will be much much slower than the SQL way.

score 0 · Answer 3 · answered Jun 28 '12 at 12:55

0

It will likely just add the data to the columns in order, since they are omitted from your SQL statement.

If you want, you can add the fields to the end of the SQL:

LOAD DATA INFILE '<file>' INTO TABLE <tablenname>
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n'
(@Field1, @Field2, @Field3)    /* Fields in CSV */
SET Col1 = @Field1, Col2 = @Field2, Col3 = @Field3;  /* Columns in DB */

More in-depth analysis of the LOAD DATA command at MySQL.com

answered Jun 28 '12 at 12:55

jeffjenx

17,041
6
57
99

No need to go via user variables unless you want to perform further manipulation. You can simply specify `(Col1, Col2, Col3)` directly. – eggyal Jun 28 '12 at 12:56

score 0 · Answer 4 · answered Jun 28 '12 at 12:55

The command is interpreted by MySQL, not Django. As stated in the manual:

By default, when no column list is provided at the end of the LOAD DATA INFILE statement, input lines are expected to contain a field for each table column. If you want to load only some of a table's columns, specify a column list:
LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata (col1,col2,...);
You must also specify a column list if the order of the fields in the input file differs from the order of the columns in the table. Otherwise, MySQL cannot tell how to match input fields with table columns.

Importing CSV into MySQL Database (Django Webapp)

4 Answers4