Repository: AlertaDengue/AlertaDengue (language: python)

Issue:
Atualizar o APP de upload de DBFs para aceitar DBFs do Brasil
Atualmente o formulário de upload de DBFs, requer a especificação do estado a que se refere o DBF. Porém, agora que o Infodengue recebe DBF de todo o país, podemos remover este campo ou permitir a entrada `BR` caso seja útil.

Outra falha é que na rotina de validação  do modelo de dados o processo falha se o campo `NM_BAIRRO` não existe. Este erro deve ser convertido em um simples warning, pois o DBF nacional não o contém.
> BR-CHIK-2021-08-23-2021.dbf
![nm_bairro](https://user-images.githubusercontent.com/3450741/130622033-494f5a5b-636e-43f2-ab33-125806f9a6d3.png)



Discussion / hints:
> Atualmente o formulário de upload de DBFs, requer a especificação do estado a que se refere o DBF. Porém, agora que o Infodengue recebe DBF de todo o país, podemos remover este campo ou permitir a entrada `BR` caso seja útil.
- Conversando com @sarasouzabh ainda prosseguirão com os uploads por estados. Apenas o campo do formulário é para ser removido?
> Outra falha é que na rotina de validação do modelo de dados o processo falha se o campo `NM_BAIRRO` não existe. Este erro deve ser convertido em um simples warning, pois o DBF nacional não o contém.
- Olhando por cima... Para a validação dos campos usamos o validation.py que [espera](https://github.com/AlertaDengue/AlertaDengue/blob/main/AlertaDengue/dbf/validation.py#L10) os campos especificados e retorna a mensagem a baixo caso não encontre. Algum DBF como referência para testes? 
```
raise ValidationError(
{
    "__all__": _(
        "Este arquivo "
        "não contém {}, que é esperado em um arquivo "
        "válido do SINAN.".format(field)`
    )
}
```
Temos algum traceback?

 


@esloch Acho que o caminho é acrescentar na lista de estados, o Brasil. Acho que o que o Flavio quer dizer é atualmente a escolha de um estado é obrigatória. E quando enviamos os dados do Brasil não existe a opção de ignorar o estado ou de colocar "Brasil". 

Certo @fccoelho ?
certo @sarasouzabh .

Registrando o log dos uploads no staging de 3 arquivos DBFs Nacionais (chik, dengue e zika). Apenas o de ZIKA_SE28 concluiu o upload:

> BR_CHIK_SE30_2021.dbf
```
staging_web_1            | Instanciando SINAN (/MEDIA_ROOT/BR_CHIK_SE30_2021.dbf, 2021)
staging_web_1            | Formatando as datas
staging_web_1            | Escrevendo no PostgreSQL
staging_web_1            | Formatando linhas e inserindo em "Municipio"."Notificacao"
staging_web_1            | 1000 linhas inseridas. Commitando mudanças no banco
.......
staging_web_1            | 59000 linhas inseridas. Commitando mudanças no banco
staging_web_1            | [2021-08-24 12:38:23 +0000] [82] [CRITICAL] WORKER TIMEOUT (pid:393)
staging_web_1            | [2021-08-24 12:38:23 +0000] [393] [INFO] Worker exiting (pid: 393)
staging_web_1            | [2021-08-24 12:38:24 +0000] [82] [WARNING] Worker with pid 393 was terminated due to signal 9
staging_web_1            | [2021-08-24 12:38:24 +0000] [442] [INFO] Booting worker with pid: 442
```
> Dengue_SE30.dbf
```
staging_web_1            | Instanciando SINAN (/MEDIA_ROOT/Dengue_SE30.dbf, 2021)
staging_web_1            | [2021-08-24 12:10:51 +0000] [82] [CRITICAL] WORKER TIMEOUT (pid:112)
staging_web_1            | [2021-08-24 12:10:54 +0000] [82] [WARNING] Worker with pid 112 was terminated due to signal 9
staging_web_1            | [2021-08-24 12:10:54 +0000] [383] [INFO] Booting worker with pid: 383
staging_web_1            | Not Found: /favicon.ico
staging_web_1            | Instanciando SINAN (/MEDIA_ROOT/Dengue_SE30_aJrpUtJ.dbf, 2021)
ddstaging_web_1            | [2021-08-24 12:15:53 +0000] [82] [CRITICAL] WORKER TIMEOUT (pid:110)
staging_web_1            | [2021-08-24 12:15:54 +0000] [82] [WARNING] Worker with pid 110 was terminated due to signal 9
staging_web_1            | [2021-08-24 12:15:54 +0000] [393] [INFO] Booting worker with pid: 393
``` 
> BR_ZIKA_SE28_2021.dbf 
``` 
staging_web_1            | Instanciando SINAN (/MEDIA_ROOT/BR_ZIKA_SE28_2021.dbf, 2021)
staging_web_1            | Formatando as datas
staging_web_1            | Escrevendo no PostgreSQL
staging_web_1            | Formatando linhas e inserindo em "Municipio"."Notificacao"
staging_web_1            | 1000 linhas inseridas. Commitando mudanças no banco
staging_web_1            | 2000 linhas inseridas. Commitando mudanças no banco
staging_web_1            | 3000 linhas inseridas. Commitando mudanças no banco
staging_web_1            | 4000 linhas inseridas. Commitando mudanças no banco
staging_web_1            | 5000 linhas inseridas. Commitando mudanças no banco
staging_web_1            | 6000 linhas inseridas. Commitando mudanças no banco
staging_web_1            | 7000 linhas inseridas. Commitando mudanças no banco
staging_web_1            | 8000 linhas inseridas. Commitando mudanças no banco
staging_web_1            | Sinan <unloaded DBF table '/MEDIA_ROOT/BR_ZIKA_SE28_2021.dbf'> inserido no banco
staging_web_1            | Task dbf.tasks.import_dbf_to_database[5488ef3c-422f-44c8-9b45-e02d7efa1c96] succeeded in 78.3318589180708s: None

O arquivo BR_ZIKA_SE28_2021.dbf exportado em 24/03/2021 com notificações do ano 2021 foi enviado com sucesso. Você será informado por email (alerta_dengue@fiocruz.br) assim que o processo de importação for finalizado,
```
>Atualmente o formulário de upload de DBFs, requer a especificação do estado a que se refere o DBF. Porém, agora que o Infodengue recebe DBF de todo o país, podemos remover este campo ou permitir a entrada BR caso seja útil.<

A remoção implicaria na tabela *dbf_dbf* na base de dados infodengue a qual armazena metadados com o nome do arquivo carregado:

``` 
infodengue> SELECT * FROM dbf_dbf ORDER BY uploaded_at DESC LIMIT 100;
 
+------+----------------------------------------------+-------------------------------+---------------+---------------------+------------------+----------------------+-----------------------+
| id   | file                                         | uploaded_at                   | export_date   | notification_year   | uploaded_by_id   | state_abbreviation   | municipio             |
|------+----------------------------------------------+-------------------------------+---------------+---------------------+------------------+----------------------+-----------------------|
| 2916 | ERJ-DEN-2021-08-25-2021_In9ibhM.dbf          | 2021-08-25 11:09:08.480545-03 | 2021-08-24    | 2021                | 2                | RJ                   |                       |
| 2915 | ERJ-CHIK-2021-08-25-2021_5mQzYTB.dbf         | 2021-08-25 11:08:58.440391-03 | 2021-08-24    | 2021                | 2                | RJ                   |                       |
| 2914 | ERJ-CHIK-MN-RiodeJaneiro-2021-08-25-2021.dbf | 2021-08-25 11:07:20.771-03    | 2021-08-25    | 2021                | 2                | RJ                   | Rio de Janeiro        |
| 2913 | ERJ-DEN-MN-RiodeJaneiro-2021-08-25-2021.dbf  | 2021-08-25 11:07:18.360078-03 | 2021-08-25    | 2021                | 2                | RJ                   | Rio de Janeiro        |
| 2912 | ERS-MN-POA-2021-08-25-2021.dbf               | 2021-08-25 11:04:52.317418-03 | 2021-08-25    | 2021                | 2                | RS                   | Porto Alegre          |
| 2911 | ESP-MN-Bauru-DEN-2021-08-25-2021.dbf         | 2021-08-25 10:58:01.975595-03 | 2021-08-25    | 2021                | 2                | SP                   | Bauru                 |
| 2910 | EAC-ZIKA-MN-RioBranco-2021-08-25-2021.dbf    | 2021-08-25 10:54:16.268503-03 | 2021-08-25    | 2021                | 2                | AC                   | Rio Branco            |
| 2909 | EAC-DEN-MN-RioBranco-2021-08-25-2021.dbf     | 2021-08-25 10:54:12.434616-03 | 2021-08-25    | 2021                | 2                | AC                   | Rio Branco            |
| 2908 | EAC-CHIK-MN-RioBranco-2021-08-25-2021.dbf    | 2021-08-25 10:54:09.117301-03 | 2021-08-25    | 2021                | 2                | AC                   | Rio Branco            |

``` 
###### Database Details:
```
dengue> SELECT count(*) FROM dbf_dbf;
+---------+
| count   |
|---------|
| 65      |
+---------+

infodengue> SELECT count(*) FROM dbf_dbf;
+---------+
| count   |
|---------|
| 2916    |
+---------+

infodengue> \d dbf_dbf
+--------------------+--------------------------+-------------------------------------------------------+
| Column             | Type                     | Modifiers                                             |
|--------------------+--------------------------+-------------------------------------------------------|
| id                 | integer                  |  not null default nextval('dbf_dbf_id_seq'::regclass) |
| file               | character varying(100)   |  not null                                             |
| uploaded_at        | timestamp with time zone |  not null                                             |
| export_date        | date                     |  not null                                             |
| notification_year  | integer                  |  not null                                             |
| uploaded_by_id     | integer                  |  not null                                             |
| state_abbreviation | character varying(2)     |                                                       |
| municipio          | character varying(255)   |  not null                                             |
+--------------------+--------------------------+-------------------------------------------------------+

```
###### Error after fields are removed:

```
Traceback (most recent call last):
  File "/opt/conda/lib/python3.8/site-packages/django/core/handlers/exception.py", line 47, in inner
    response = get_response(request)
  File "/opt/conda/lib/python3.8/site-packages/django/core/handlers/base.py", line 181, in _get_response
    response = wrapped_callback(request, *callback_args, **callback_kwargs)
  File "/opt/conda/lib/python3.8/site-packages/django/views/generic/base.py", line 70, in view
    return self.dispatch(request, *args, **kwargs)
  File "/opt/conda/lib/python3.8/site-packages/django/contrib/auth/mixins.py", line 71, in dispatch
    return super().dispatch(request, *args, **kwargs)
  File "/opt/conda/lib/python3.8/site-packages/django/views/generic/base.py", line 98, in dispatch
    return handler(request, *args, **kwargs)
  File "/srv/deploy/AlertaDengue/dbf/views.py", line 35, in post
    return super(Upload, self).post(self.request, *args, **kwargs)
  File "/opt/conda/lib/python3.8/site-packages/django/views/generic/edit.py", line 142, in post
    return self.form_valid(form)
  File "/srv/deploy/AlertaDengue/dbf/views.py", line 49, in form_valid
    state_abbreviation=form.cleaned_data['state_abbreviation'],
KeyError: 'state_abbreviation'

```
###### Relation between variables and methods:
``` 
class DBFForm(forms.Form):
    uploaded_by = forms.ModelChoiceField(
        queryset=User.objects.all(), widget=forms.HiddenInput()
    )
    chunked_upload_id = forms.IntegerField(
        required=False, widget=forms.HiddenInput()
    )
    filename = forms.CharField(required=False, widget=forms.HiddenInput())
    export_date = forms.DateField(label=_("Data da exportação"))
    notification_year = forms.IntegerField(label=_("Ano de notificação"))
    state_abbreviation = forms.ChoiceField(
        choices=DBF.STATE_ABBREVIATION_CHOICES, label=_("U.F.")
    )
    municipio = forms.CharField(
        label=_("Nome do município (opcional)"), required=False
    )
    

dbf = DBF.objects.create(
    uploaded_by=self.request.user,
    file=uploaded_file,
    export_date=form.cleaned_data['export_date'],
    notification_year=form.cleaned_data['notification_year'],
    state_abbreviation=form.cleaned_data['state_abbreviation'],
    municipio=form.cleaned_data['municipio'],
)

        
def copy_file_to_final_destination(dbf):
    new_filename = "{}_{}_{}_{}.dbf".format(
        dbf.state_abbreviation,
        dbf.municipio,
        dbf.export_date,
        dbf.notification_year,
    )
    src = dbf.file.path
    dest = os.path.join(settings.IMPORTED_FILES_DIR, new_filename)
    shutil.copy(src, dest)
    
``` 
![Screenshot_20210825_202637](https://user-images.githubusercontent.com/3450741/130877295-bb273ce4-0da0-4c70-9e17-86f782f4433f.png)

###### With the removal of the municipality field (optional) from the form:
``` 
dbf = DBF.objects.create(
    uploaded_by=self.request.user,
    file=uploaded_file,
    export_date=form.cleaned_data['export_date'],
    notification_year=form.cleaned_data['notification_year'],
    # state_abbreviation=form.cleaned_data['state_abbreviation'],
    # municipio=form.cleaned_data['municipio'],
)
``` 
###### After migrate:
```
web_1            | Traceback (most recent call last):
web_1            |   File "/srv/deploy/AlertaDengue/dbf/views.py", line 44, in form_valid
web_1            |     dbf = DBF.objects.create(
web_1            |   File "/opt/conda/lib/python3.8/site-packages/django/db/models/manager.py", line 85, in manager_method
web_1            |     return getattr(self.get_queryset(), name)(*args, **kwargs)
web_1            |   File "/opt/conda/lib/python3.8/site-packages/django/db/models/query.py", line 453, in create
web_1            |     obj.save(force_insert=True, using=self.db)
web_1            |   File "/opt/conda/lib/python3.8/site-packages/django/db/models/base.py", line 726, in save
web_1            |     self.save_base(using=using, force_insert=force_insert,
web_1            |   File "/opt/conda/lib/python3.8/site-packages/django/db/models/base.py", line 763, in save_base
web_1            |     updated = self._save_table(
web_1            |   File "/opt/conda/lib/python3.8/site-packages/django/db/models/base.py", line 868, in _save_table
web_1            |     results = self._do_insert(cls._base_manager, using, fields, returning_fields, raw)
web_1            |   File "/opt/conda/lib/python3.8/site-packages/django/db/models/base.py", line 906, in _do_insert
web_1            |     return manager._insert(
web_1            |   File "/opt/conda/lib/python3.8/site-packages/django/db/models/manager.py", line 85, in manager_method
web_1            |     return getattr(self.get_queryset(), name)(*args, **kwargs)
web_1            |   File "/opt/conda/lib/python3.8/site-packages/django/db/models/query.py", line 1270, in _insert
web_1            |     return query.get_compiler(using=using).execute_sql(returning_fields)
web_1            |   File "/opt/conda/lib/python3.8/site-packages/django/db/models/sql/compiler.py", line 1416, in execute_sql
web_1            |     cursor.execute(sql, params)
web_1            |   File "/opt/conda/lib/python3.8/site-packages/django/db/backends/utils.py", line 66, in execute
web_1            |     return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
web_1            |   File "/opt/conda/lib/python3.8/site-packages/django/db/backends/utils.py", line 75, in _execute_with_wrappers
web_1            |     return executor(sql, params, many, context)
web_1            |   File "/opt/conda/lib/python3.8/site-packages/django/db/backends/utils.py", line 84, in _execute
web_1            |     return self.cursor.execute(sql, params)
web_1            |   File "/opt/conda/lib/python3.8/site-packages/django/db/utils.py", line 90, in __exit__
web_1            |     raise dj_exc_value.with_traceback(traceback) from exc_value
web_1            |   File "/opt/conda/lib/python3.8/site-packages/django/db/backends/utils.py", line 84, in _execute
web_1            |     return self.cursor.execute(sql, params)
web_1            | django.db.utils.IntegrityError: null value in column "municipio" violates not-null constraint
web_1            | DETAIL:  Failing row contains (2815, BR_ZIKA_SE28_2021.dbf, 2021-08-25 23:09:32.83749+00, 2021-08-25, 2021, 2, null, null, null).
web_1            | 
``` 
> @esloch Acho que o caminho é acrescentar na lista de estados, o Brasil. Acho que o que o Flavio quer dizer é atualmente a escolha de um estado é obrigatória. E quando enviamos os dados do Brasil não existe a opção de ignorar o estado ou de colocar "Brasil".
> 
> Certo @fccoelho ?

Vou testar isso:
```
class DBF(models.Model):
    STATE_ABBREVIATION_CHOICES = (
        ('BR', 'Brasil'),
        (None, '-----'),
        ('AC', 'Acre'),
        ('AL', 'Alagoas'),
        ('AP', 'Amapá'),
        ('AM', 'Amazonas'),
        ('BA', 'Bahia'),
        ('CE', 'Ceará'),
``` 
Agradecido @sarasouzabh!
Registrando o warning de arquivo não válido no campo  *DT_DIGITA* após  a remoção de NM_BAIRRO e ID_BAIRRO:
```
expected_fields = [
    u'NU_ANO',
    u'ID_MUNICIP',
    u'ID_AGRAVO',
    u'DT_SIN_PRI',
    u'SEM_PRI',
    u'DT_NOTIFIC',
    u'NU_NOTIFIC',
    u'SEM_NOT',
    u'DT_DIGITA',
    u'DT_NASC',
    u'NU_IDADE_N',
    u'CS_SEXO',
]
```
> DBF BR-DEN-2021-08-23-2021/BR-CHIK-2021-08-23-2021
![dt_digita_error](https://user-images.githubusercontent.com/3450741/130877604-85dfd756-87d3-4bc1-a5ad-682995d61bab.png)


Fix patch:
diff --git a/.dockerignore b/.dockerignore
index e8947353..6457bd80 100644
--- a/.dockerignore
+++ b/.dockerignore
@@ -1,6 +1,6 @@
 ci/*
 docs/*
-*dev_dumps/*
+docker/dev_dumps/*
 notebooks_tmp/*
 notebooks/*
 rever/*
diff --git a/AlertaDengue/ad_main/settings.py b/AlertaDengue/ad_main/settings.py
index 163acf12..5d898a43 100644
--- a/AlertaDengue/ad_main/settings.py
+++ b/AlertaDengue/ad_main/settings.py
@@ -360,3 +360,5 @@ def read_admins(value):
 ACTIVE_STATES = (
     os.getenv('ACTIVE_STATES').split(',') if os.getenv('ACTIVE_STATES') else []
 )
+
+DOCKER_HOST_PQDIR = os.getenv('DOCKER_HOST_PQDIR')
diff --git a/AlertaDengue/dados/views.py b/AlertaDengue/dados/views.py
index 02f0857d..cec7415d 100644
--- a/AlertaDengue/dados/views.py
+++ b/AlertaDengue/dados/views.py
@@ -760,7 +760,7 @@ def get_context_data(self, **kwargs):
         geo_ids = list(mun_dict.keys())
 
         dbf = (
-            DBF.objects.filter(state_abbreviation=context['state'])
+            DBF.objects.filter(abbreviation=context['state'])
             .order_by('export_date')
             .last()
         )
diff --git a/AlertaDengue/dbf/forms.py b/AlertaDengue/dbf/forms.py
index 0949dfad..9a93a0b4 100644
--- a/AlertaDengue/dbf/forms.py
+++ b/AlertaDengue/dbf/forms.py
@@ -1,10 +1,9 @@
+from bootstrap4.renderers import FormRenderer
 from django import forms
 from django.contrib.auth.models import User
 from django.core.exceptions import ValidationError
 from django.utils.translation import ugettext_lazy as _
 
-from bootstrap4.renderers import FormRenderer
-
 from .models import DBF, DBFChunkedUpload
 from .validation import is_valid_dbf
 
@@ -19,8 +18,8 @@ class DBFForm(forms.Form):
     filename = forms.CharField(required=False, widget=forms.HiddenInput())
     export_date = forms.DateField(label=_("Data da exportação"))
     notification_year = forms.IntegerField(label=_("Ano de notificação"))
-    state_abbreviation = forms.ChoiceField(
-        choices=DBF.STATE_ABBREVIATION_CHOICES, label=_("U.F.")
+    abbreviation = forms.ChoiceField(
+        choices=DBF.ABBREVIATION, label=_("Abrangência")
     )
     municipio = forms.CharField(
         label=_("Nome do município (opcional)"), required=False
diff --git a/AlertaDengue/dbf/migrations/0009_auto_20210901_1332.py b/AlertaDengue/dbf/migrations/0009_auto_20210901_1332.py
new file mode 100644
index 00000000..d894cc7d
--- /dev/null
+++ b/AlertaDengue/dbf/migrations/0009_auto_20210901_1332.py
@@ -0,0 +1,53 @@
+# Generated by Django 3.2.6 on 2021-09-01 13:32
+
+from django.db import migrations, models
+
+
+class Migration(migrations.Migration):
+
+    dependencies = [
+        ('dbf', '0008_sendtopartner'),
+    ]
+
+    operations = [
+        migrations.RenameField('dbf', 'state_abbreviation', 'abbreviation'),
+        migrations.AlterField(
+            model_name='dbf',
+            name='abbreviation',
+            field=models.CharField(
+                choices=[
+                    ('BR', 'Brasil'),
+                    (None, '-----'),
+                    ('AC', 'Acre'),
+                    ('AL', 'Alagoas'),
+                    ('AP', 'Amapá'),
+                    ('AM', 'Amazonas'),
+                    ('BA', 'Bahia'),
+                    ('CE', 'Ceará'),
+                    ('DF', 'Distrito Federal'),
+                    ('ES', 'Espírito Santo'),
+                    ('GO', 'Goiás'),
+                    ('MA', 'Maranhão'),
+                    ('MT', 'Mato Grosso'),
+                    ('MS', 'Mato Grosso do Sul'),
+                    ('MG', 'Minas Gerais'),
+                    ('PA', 'Pará'),
+                    ('PB', 'Paraíba'),
+                    ('PR', 'Paraná'),
+                    ('PE', 'Pernambuco'),
+                    ('PI', 'Piauí'),
+                    ('RJ', 'Rio de Janeiro'),
+                    ('RN', 'Rio Grande do Norte'),
+                    ('RS', 'Rio Grande do Sul'),
+                    ('RO', 'Rondônia'),
+                    ('RR', 'Roraima'),
+                    ('SC', 'Santa Catarina'),
+                    ('SP', 'São Paulo'),
+                    ('SE', 'Sergipe'),
+                    ('TO', 'Tocantins'),
+                ],
+                max_length=2,
+                null=True,
+            ),
+        ),
+    ]
diff --git a/AlertaDengue/dbf/models.py b/AlertaDengue/dbf/models.py
index 020751b1..582150d3 100644
--- a/AlertaDengue/dbf/models.py
+++ b/AlertaDengue/dbf/models.py
@@ -1,9 +1,9 @@
-from django.db import models
-from django.core.exceptions import ValidationError
-from django.utils.translation import ugettext_lazy as _
-
 from datetime import date
+
 from chunked_upload.models import ChunkedUpload
+from django.core.exceptions import ValidationError
+from django.db import models
+from django.utils.translation import ugettext_lazy as _
 
 from .validation import is_valid_dbf
 
@@ -13,8 +13,9 @@ def current_year():
 
 
 class DBF(models.Model):
-    STATE_ABBREVIATION_CHOICES = (
-        (None, ''),
+    ABBREVIATION = (
+        ('BR', 'Brasil'),
+        (None, '-----'),
         ('AC', 'Acre'),
         ('AL', 'Alagoas'),
         ('AP', 'Amapá'),
@@ -49,8 +50,8 @@ class DBF(models.Model):
     uploaded_at = models.DateTimeField(auto_now_add=True)
     export_date = models.DateField()
     notification_year = models.IntegerField(default=current_year)
-    state_abbreviation = models.CharField(
-        max_length=2, null=True, choices=STATE_ABBREVIATION_CHOICES
+    abbreviation = models.CharField(
+        max_length=2, null=True, choices=ABBREVIATION
     )
     municipio = models.CharField(max_length=255, blank=True, default="")
 
diff --git a/AlertaDengue/dbf/sinan.py b/AlertaDengue/dbf/sinan.py
index ef7895c1..96934826 100644
--- a/AlertaDengue/dbf/sinan.py
+++ b/AlertaDengue/dbf/sinan.py
@@ -1,33 +1,19 @@
+import logging
 from datetime import date
-from psycopg2.extras import DictCursor
-from dbfread import DBF
+
+import pandas as pd
+import psycopg2
+
+# from dbfread import DBF
 from django.conf import settings
 from django.core.exceptions import ValidationError
 from django.utils.translation import ugettext_lazy as _
+from psycopg2.extras import DictCursor
 
-import pandas as pd
-import psycopg2
-import logging
+from .utils import FIELD_MAP, chunk_dbf_toparquet
 
 logger = logging.getLogger(__name__)
 
-field_map = {
-    'dt_notific': "DT_NOTIFIC",
-    'se_notif': "SEM_NOT",
-    'ano_notif': "NU_ANO",
-    'dt_sin_pri': "DT_SIN_PRI",
-    'se_sin_pri': "SEM_PRI",
-    'dt_digita': "DT_DIGITA",
-    'bairro_nome': "NM_BAIRRO",
-    'bairro_bairro_id': "ID_BAIRRO",
-    'municipio_geocodigo': "ID_MUNICIP",
-    'nu_notific': "NU_NOTIFIC",
-    'cid10_codigo': "ID_AGRAVO",
-    'cs_sexo': "CS_SEXO",
-    'dt_nasc': "DT_NASC",
-    'nu_idade_n': "NU_IDADE_N",
-}
-
 
 def calculate_digit(dig):
     """
@@ -79,11 +65,17 @@ def __init__(self, dbf_fname, ano, encoding="iso=8859-1"):
         :param ano: Ano dos dados
         :return:
         """
-        logger.info("Instanciando SINAN ({}, {})".format(dbf_fname, ano))
+        logger.info("Partition parquet files and concatenate in pandas")
+
+        pq_files = chunk_dbf_toparquet(dbf_fname)
+        chunks_list = [
+            pd.read_parquet(f, engine='fastparquet') for f in pq_files
+        ]
+        self.tabela = pd.concat(chunks_list, ignore_index=True)
         self.ano = ano
-        self.dbf = DBF(dbf_fname, encoding=encoding)
-        self.colunas_entrada = self.dbf.field_names
-        self.tabela = pd.DataFrame(self.dbf.records)
+
+        logger.info(f"Instanciando SINAN ({dbf_fname}, {ano})")
+
         if "ID_MUNICIP" in self.tabela.columns:
             self.geocodigos = self.tabela.ID_MUNICIP.dropna().unique()
         elif "ID_MN_RESI" in self.tabela.columns:
@@ -124,8 +116,8 @@ def _fill_missing_columns(self, col_names):
         :param col_names:
         """
         for nm in col_names:
-            if field_map[nm] not in self.tabela.columns:
-                self.tabela[field_map[nm]] = None
+            if FIELD_MAP[nm] not in self.tabela.columns:
+                self.tabela[FIELD_MAP[nm]] = None
 
     def _get_postgres_connection(self):
         return psycopg2.connect(**self.db_config)
@@ -137,10 +129,10 @@ def save_to_pgsql(
         logger.info("Escrevendo no PostgreSQL")
 
         with connection.cursor(cursor_factory=DictCursor) as cursor:
-            cursor.execute("select * from {} limit 1;".format(table_name))
+            cursor.execute(f"SELECT * FROM {table_name} LIMIT 1;")
             col_names = [c.name for c in cursor.description if c.name != "id"]
             self._fill_missing_columns(col_names)
-            df_names = [field_map[n] for n in col_names]
+            df_names = [FIELD_MAP[n] for n in col_names]
             insert_sql = (
                 'INSERT INTO {}({}) VALUES ({}) on conflict '
                 'on CONSTRAINT casos_unicos do UPDATE SET {}'
@@ -150,9 +142,7 @@ def save_to_pgsql(
                 ','.join(['%s' for i in col_names]),
                 ','.join(['{0}=excluded.{0}'.format(j) for j in col_names]),
             )
-            logger.info(
-                "Formatando linhas e inserindo em {}".format(table_name)
-            )
+            logger.info(f"Formatando linhas e inserindo em {table_name}")
             for row in self.tabela[df_names].iterrows():
                 i = row[0]
                 row = row[1]
@@ -178,8 +168,11 @@ def save_to_pgsql(
                     if isinstance(row[5], type(pd.NaT))
                     else date.fromordinal(row[5].to_pydatetime().toordinal())
                 )  # dt_digita
+                row[6] = (
+                    None if pd.isnull(row[6]) else str(row[6])
+                )  # nm_bairro
                 row[7] = (
-                    None if not row[7] else int(row[7])
+                    None if pd.isnull(row[7]) else int(row[7])
                 )  # bairro_bairro_id
                 row[8] = (
                     None if row[8] == '' else add_dv(int(row[8]))
@@ -201,14 +194,18 @@ def save_to_pgsql(
                     if (isinstance(row[11], type(pd.NaT)) or row[11] is None)
                     else date.fromordinal(row[11].to_pydatetime().toordinal())
                 )  # dt_nasc
-                row[13] = None if not row[13] else int(row[13])  # nu_idade_n
+                row[13] = None if pd.isnull(row[13]) else int(row[13])
                 cursor.execute(insert_sql, row)
                 if (i % 1000 == 0) and (i > 0):
                     logger.info(
-                        "{} linhas inseridas. Commitando mudanças "
-                        "no banco".format(i)
+                        f"{i} linhas inseridas. Commitando mudanças "
+                        "no banco"
                     )
                     connection.commit()
 
             connection.commit()
-            logger.info("Sinan {} inserido no banco".format(self.dbf))
+            logger.info(
+                'Sinan {} rows in {} fields inserted in the database'.format(
+                    self.tabela.shape[0], self.tabela.shape[1]
+                )
+            )
diff --git a/AlertaDengue/dbf/tasks.py b/AlertaDengue/dbf/tasks.py
index b681bec6..c37134d0 100644
--- a/AlertaDengue/dbf/tasks.py
+++ b/AlertaDengue/dbf/tasks.py
@@ -56,7 +56,7 @@ def send_failure_email(dbf, message):
 
 def copy_file_to_final_destination(dbf):
     new_filename = "{}_{}_{}_{}.dbf".format(
-        dbf.state_abbreviation,
+        dbf.abbreviation,
         dbf.municipio,
         dbf.export_date,
         dbf.notification_year,
diff --git a/AlertaDengue/dbf/templates/dbf_upload.html b/AlertaDengue/dbf/templates/dbf_upload.html
index 8963e63c..e258234f 100644
--- a/AlertaDengue/dbf/templates/dbf_upload.html
+++ b/AlertaDengue/dbf/templates/dbf_upload.html
@@ -34,7 +34,7 @@
     <style>
         .ui-datepicker {
             background-color: #FFF;
-            border: 1px solid gray;
+            border: 0px solid white;
             border-radius: 10px;
             padding: 10px;
         }
@@ -117,14 +117,14 @@
               $("#chunked_upload").fileupload({
                 url: "{% url 'dbf:api_chunked_upload' %}",
                 dataType: "json",
-                maxChunkSize: 100000, // Chunks of 100 kB
+                maxChunkSize: 10000000, // Chunks of 10MB
                 formData: form_data,
                 add: function(e, data) { // Called before starting upload
                   $("#messages").empty();
                   // If this is the second file you're uploading we need to remove the
                   // old upload_id and just keep the csrftoken (which is always first).
                   form_data.splice(1);
-                  calculate_md5(data.files[0], 100000);  // Again, chunks of 100 kB
+                  calculate_md5(data.files[0], 10000000);  // Again, chunks of 10MB
                   data.submit();
 		  $("#filename_display").show();
 		  $("#filename_display>span").text("Enviando " + data.files[0].name + "...");
diff --git a/AlertaDengue/dbf/utils.py b/AlertaDengue/dbf/utils.py
new file mode 100644
index 00000000..341f69c4
--- /dev/null
+++ b/AlertaDengue/dbf/utils.py
@@ -0,0 +1,83 @@
+import glob
+import os
+
+# from typing import Callable
+
+import geopandas as gpd
+from simpledbf import Dbf5
+
+
+DBFS_PQDIR = '/tmp/dbfs_parquet'
+
+FIELD_MAP = {
+    'dt_notific': "DT_NOTIFIC",
+    'se_notif': "SEM_NOT",
+    'ano_notif': "NU_ANO",
+    'dt_sin_pri': "DT_SIN_PRI",
+    'se_sin_pri': "SEM_PRI",
+    'dt_digita': "DT_DIGITA",
+    'bairro_nome': "NM_BAIRRO",
+    'bairro_bairro_id': "ID_BAIRRO",
+    'municipio_geocodigo': "ID_MUNICIP",
+    'nu_notific': "NU_NOTIFIC",
+    'cid10_codigo': "ID_AGRAVO",
+    'cs_sexo': "CS_SEXO",
+    'dt_nasc': "DT_NASC",
+    'nu_idade_n': "NU_IDADE_N",
+}
+
+
+def chunk_gen(chunksize, totalsize):
+    """
+    Create chunks
+    Parameters
+    ----------
+    chunksize: int
+    totalsize: int
+    Returns
+    -------
+    yield: += chunks * chunksize
+    """
+    chunks = totalsize // chunksize
+
+    for i in range(chunks):
+        yield i * chunksize, (i + 1) * chunksize
+
+    rest = totalsize % chunksize
+
+    if rest:
+        yield (chunks * chunksize, (chunks * chunksize) + rest)
+
+
+def chunk_dbf_toparquet(dbfname) -> glob:
+    """
+    name: Generator to read the dbf in chunks
+    Filtering columns from the field_map dictionary on dataframe and export
+    to parquet files
+    Parameters
+    ----------
+    dbf_fname: str
+        path: path to dbf file
+    Returns
+    -------
+    files:
+        .parquet list
+    """
+
+    dbf = Dbf5(dbfname)
+    fname_topath = str(dbf.dbf)[:-4]
+    for chunk, (lowerbound, upperbound) in enumerate(
+        chunk_gen(1000, dbf.numrec)
+    ):
+        df = gpd.read_file(
+            dbfname, rows=slice(lowerbound, upperbound), ignore_geometry=True,
+        )
+
+        pq_fname = os.path.join(
+            f'{DBFS_PQDIR}', f'{fname_topath}-{chunk}.parquet'
+        )
+
+        df[FIELD_MAP.values()].to_parquet(pq_fname)
+
+    fetch_pq_fname = os.path.join(f'{DBFS_PQDIR}', f'{fname_topath}')
+    return glob.glob(f'{fetch_pq_fname}*.parquet')
diff --git a/AlertaDengue/dbf/validation.py b/AlertaDengue/dbf/validation.py
index 643e7053..1920fa77 100644
--- a/AlertaDengue/dbf/validation.py
+++ b/AlertaDengue/dbf/validation.py
@@ -1,17 +1,15 @@
+import os
+import struct
 from contextlib import contextmanager
-from django.core.exceptions import ValidationError
-from django.utils.translation import ugettext_lazy as _
 from tempfile import NamedTemporaryFile
 
-import os
-import struct
 import dbfread
+from django.core.exceptions import ValidationError
+from django.utils.translation import ugettext_lazy as _
 
 expected_fields = [
     u'NU_ANO',
     u'ID_MUNICIP',
-    u'NM_BAIRRO',
-    u'ID_BAIRRO',
     u'ID_AGRAVO',
     u'DT_SIN_PRI',
     u'SEM_PRI',
diff --git a/AlertaDengue/dbf/views.py b/AlertaDengue/dbf/views.py
index 027d59f2..667c9115 100644
--- a/AlertaDengue/dbf/views.py
+++ b/AlertaDengue/dbf/views.py
@@ -46,7 +46,7 @@ def form_valid(self, form):
             file=uploaded_file,
             export_date=form.cleaned_data['export_date'],
             notification_year=form.cleaned_data['notification_year'],
-            state_abbreviation=form.cleaned_data['state_abbreviation'],
+            abbreviation=form.cleaned_data['abbreviation'],
             municipio=form.cleaned_data['municipio'],
         )
         import_dbf_to_database.delay(dbf.id)
diff --git a/docker/Dockerfile b/docker/Dockerfile
index dfc90a67..63caad71 100644
--- a/docker/Dockerfile
+++ b/docker/Dockerfile
@@ -52,7 +52,7 @@ RUN chmod 0644 /etc/cron.d/cron-sendmail \
 
 ADD docker/entrypoint.sh /entrypoint.sh
 
-USER deploy
+# USER deploy
 
 ENTRYPOINT ["bash", "/activate.sh"]
 CMD ["/entrypoint.sh", "/srv/deploy/AlertaDengue/runwsgi.sh"]
diff --git a/docker/Dockerfile-postgres b/docker/Dockerfile-postgres
index c6c960ab..f91d8707 100644
--- a/docker/Dockerfile-postgres
+++ b/docker/Dockerfile-postgres
@@ -1,5 +1,4 @@
 FROM mdillon/postgis:9.6
 
 ADD docker/postgres_setup/* /docker-entrypoint-initdb.d/
-ADD docker/dev_dumps /dumps
 ADD docker/sql /docker-entrypoint-sql
diff --git a/docker/docker-compose.yml b/docker/docker-compose.yml
index a222e01c..953e8542 100644
--- a/docker/docker-compose.yml
+++ b/docker/docker-compose.yml
@@ -35,6 +35,7 @@ services:
       - ${DOCKER_HOST_TIFFS_DIR}:/srv/deploy/tiffs
       - ${DOCKER_HOST_LOG_DIR}:/var/log/mapserver
       - ${DOCKER_HOST_INCIDENCE_MAPS}:/srv/deploy/AlertaDengue/static/img/incidence_maps
+      - ${DOCKER_HOST_PQDIR}:/tmp/dbfs_parquet
     depends_on:
       - rabbitmq
       - memcache
diff --git a/docker/staging-compose.yml b/docker/staging-compose.yml
index 53ed0f18..968e55ef 100644
--- a/docker/staging-compose.yml
+++ b/docker/staging-compose.yml
@@ -26,6 +26,8 @@ services:
       - 25432:25432
     volumes:
       - ${STAGING_DATA_DIR}/staging:/var/lib/postgresql/data
+      - ./dev_dumps:/dumps
+
     restart: always
     command: -p 25432
     networks:
@@ -57,6 +59,7 @@ services:
       - ${DOCKER_HOST_TIFFS_DIR}:/srv/deploy/tiffs
       - ${DOCKER_HOST_LOG_DIR}:/var/log/mapserver
       - ${DOCKER_HOST_INCIDENCE_MAPS}:/srv/deploy/AlertaDengue/static/img/incidence_maps
+      - ./${DOCKER_HOST_PQDIR}:/tmp/dbfs_parquet
       - staging_web:/srv/deploy/AlertaDengue/
     depends_on:
       - staging_rabbitmq
@@ -83,6 +86,7 @@ services:
     volumes:
       - ${DOCKER_HOST_MEDIA_ROOT}:/MEDIA_ROOT
       - ${DOCKER_HOST_IMPORTED_FILES_DIR}:/IMPORTED_FILES
+      - ./${DOCKER_HOST_PQDIR}:/tmp/dbfs_parquet
     depends_on:
       - staging_db
       - staging_rabbitmq
diff --git a/environment-3.8.yml b/environment-3.8.yml
index 03d66891..247c024e 100644
--- a/environment-3.8.yml
+++ b/environment-3.8.yml
@@ -13,6 +13,7 @@ dependencies:
 - django-leaflet
 - django-makemessages-xgettext
 - docker-compose
+- fastparquet
 - fiona
 - gdal >=2.4.2
 - geojson
@@ -35,5 +36,6 @@ dependencies:
 - sh
 - sqlalchemy <1.4
 - pip:
+  - simpledbf
   - django-maintenance-mode
   - django-pymemcache
diff --git a/environment-dev.yml b/environment-dev.yml
index 6d129e8b..327b795b 100644
--- a/environment-dev.yml
+++ b/environment-dev.yml
@@ -13,6 +13,7 @@ dependencies:
 - django-leaflet
 - django-makemessages-xgettext
 - docker-compose
+- fastparquet
 - fiona
 - gdal >=2.4.2
 - geojson
@@ -47,6 +48,7 @@ dependencies:
 - pip:
   - django-maintenance-mode
   - django-pymemcache
+  - simpledbf
   # develop
   - django-extensions
   - django-cprofile-middleware


Test patch:
diff --git a/AlertaDengue/dbf/tests/test_models.py b/AlertaDengue/dbf/tests/test_models.py
index 5990704a..b01adfd0 100644
--- a/AlertaDengue/dbf/tests/test_models.py
+++ b/AlertaDengue/dbf/tests/test_models.py
@@ -29,7 +29,7 @@ def test_notification_year_cant_be_greater_than_current_year(self):
                 file=File(fp, name="simple.dbf"),
                 export_date=date.today(),
                 notification_year=date.today().year + 1,
-                state_abbreviation='RJ',
+                abbreviation='RJ',
             )
             with self.assertRaises(ValidationError):
                 dbf.clean()
@@ -43,7 +43,7 @@ def test_raises_error_if_dbf_is_invalid(self, mocked_validation):
                 file=File(fp, name="invalid.dbf"),
                 export_date=date.today(),
                 notification_year=date.today().year,
-                state_abbreviation='RJ',
+                abbreviation='RJ',
             )
             with self.assertRaises(ValidationError):
                 dbf.clean()
diff --git a/AlertaDengue/dbf/tests/test_views.py b/AlertaDengue/dbf/tests/test_views.py
index 2c815a5d..4a7e614d 100644
--- a/AlertaDengue/dbf/tests/test_views.py
+++ b/AlertaDengue/dbf/tests/test_views.py
@@ -90,7 +90,7 @@ def test_redirects_to_success_url_when_form_is_valid(self):
                 "export_date": date.today(),
                 "notification_year": date.today().year,
                 "chunked_upload_id": 1,
-                "state_abbreviation": "RJ",
+                "abbreviation": "RJ",
             }
             response = self.client.post(reverse('dbf:upload'), data)
         self.assertEqual(response.status_code, 302)
@@ -116,7 +116,7 @@ def test_cannot_create_file_for_other_user(self):
                 "export_date": date.today(),
                 "notification_year": date.today().year,
                 "chunked_upload_id": 1,
-                "state_abbreviation": "RJ",
+                "abbreviation": "RJ",
             }
             self.client.post(reverse('dbf:upload'), data)