Import CSV to Postgresql

Question

I am working on a Django based web application.

I am going to import a csv to postgresql database, which has over 100,000 lines, and use it as a database for the Django application. Here, I've faced two problems. The field name includes special characters like this:

%oil, %gas, up/down, CAPEX/Cash-flow, D&C Cape,...

1st, How should I define the field name of Postgresql database to import csv?

2nd, After import, I am going to get data through django model. Then how can I define the Django model variable name that includes special characters?

Of course, It's possible if I change the column name of the csv which includes special characters, but I don't want to change it. I want to import original csv without any changes.

Is there any solution to solve this problem?

If you use copy then you don't even need to specify column names, provided the content lines up identically. — Hambone
– Hambone, Commented Nov 22, 2017 at 12:01
You should at least try @Hambone's suggestion, before you disqualify it. His suggestion is good. — Borut
– Borut, Commented Nov 22, 2017 at 14:46

Borut · Accepted Answer · 2017-11-22 15:03:33Z

1

There are no special characters in your example. At least not any that would be problematic from the python or database point of view.

First of, avoid dubious field names, especially in finance. %oil can mean either oil share, oil margin or something else. Define a model with meaningful names like

class FinancialPeformanceData(models.Model):

     oil_share = models.DecimalField(max_digits=5, decimal_places=2)
     gas_share = models.DecimalField(max_digits=5, decimal_places=2)
     growth = models.DecimalField(max_digits=10, decimal_places=2)
     capex_to_cf = models.DecimalField(max_digits=7, decimal_places=2)
     ... etc.

Then you use copy to import data from CSV as @Hambone suggested. You don't need headers in CSV files.

def import_csv(request):

    file = './path/to/file'
    with open(file, 'rb') as csvfile:
          with closing(connections['database_name_from_settings'].cursor()) as cursor:
                cursor.copy_from(
                    file=csvfile,
                    table='yourapp_financialperformancedata', #<-- table name from db
                    sep='|',  #<-- delimiter
                    columns=(
                        'oil_share',
                        'gas_share',
                        'growth',
                        'capex_to_cf',
                        ... etc.
                    ),
            )

    return HttpResponse('Done!')

edited Nov 22, 2017 at 15:03

answered Nov 22, 2017 at 14:44

Borut

3,3642 gold badges15 silver badges19 bronze badges

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

3 Comments

Deimos620 Over a year ago

Great! Thanks. This helped me out.

Borut Over a year ago

You probably won't get it working the first time, but stick with it. It can be done. And my example won't work if you have headers in CSV. I'm importing CSVs with millions of rows this way.

Deimos620 Over a year ago

Thanks. At first i thought header is parsed, too. That's why I think parsing is problematic and declined @hambone's one.

Collectives™ on Stack Overflow

Import CSV to Postgresql

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related