Can't transfer Json data from sqlite3 to postgres

Using Django Using the ORM
1

Hello, my name is Luiz.

This is the code code I’m using to do the sync and all to other columns are working except for “dados” which contais my json

class DatabaseSynchronizer:
    @staticmethod
    def sync_db():
       
        with connections["default"].cursor() as cursor:
            cursor.execute("SELECT * FROM todos_fichadeaplicacao")
            rows = cursor.fetchall()
       
        with connections["secondary"].cursor() as cursor:
            for row in rows:
                # Certifique-se de que row[1] e row[11] não são nulos antes de usá-los
                ficha_data_criada = row[1].astimezone(
                    pytz.timezone("America/Sao_Paulo")
                )
                ficha_data_atualizado = datetime.combine(row[11], datetime.min.time())

                area = (
                    row[2] if row[2] is not None else 0.0
                )  
                dados = row[3] if row[3] is not None else {}
                data_aplicada = datetime.combine(row[4], datetime.min.time())
                atividade_id = (
                    row[5] if row[5] is not None else 0
                ) 
                estufa_id = (
                    row[6] if row[6] is not None else 0
                )  
                irrigador_id = (
                    row[7] if row[7] is not None else 0
                )  
                ativo = (
                    row[8] if row[8] is not None else False
                )  
                pendente = (
                    row[9] if row[9] is not None else False
                )  
                obs = row[10] if row[10] is not None else ""

                try:
                    existing_ficha = FichaDeAplicacao.objects.using("secondary").get(
                        data_criada=ficha_data_criada
                    )

                    existing_ficha_data_atualizado = (
                        datetime.combine(
                            existing_ficha.data_atualizado, datetime.min.time()
                        )
                        if existing_ficha.data_atualizado
                        else None
                    )

                    if (
                        ficha_data_atualizado
                        and existing_ficha_data_atualizado < ficha_data_atualizado
                    ):
                        existing_ficha.data_atualizado = row[11]
                        existing_ficha.save(using="secondary")
                except FichaDeAplicacao.DoesNotExist:
                    # Se a ficha não existir no banco de dados PostgreSQL, insere a nova ficha
                    FichaDeAplicacao.objects.using("secondary").create(
                        data_criada=ficha_data_criada,
                        data_atualizado=ficha_data_atualizado,
                        area=area,
                        dados=json.loads(dados),
                        data_aplicada=data_aplicada,
                        atividade_id=atividade_id,
                        estufa_id=estufa_id,
                        irrigador_id=irrigador_id,
                        ativo=ativo,
                        pendente=pendente,
                        obs=obs,
                    )

My original json in sqlite3 is like this: [{“produto”: “Nomolt 150 - (329)”, “dose”: “0.15”, “campo1”: “”, “campo2”: “”, “antiCamara”: “0.0765”, “previsto”: “0.0765”, “total”: “”}, {“produto”: “Trivor - (327)”, “dose”: “0.3”, “campo1”: “”, “campo2”: “”, “antiCamara”: “0.153”, “previsto”: “0.153”, “total”: “”}]

But in postgres it looks like this:
“[{"produto": "Nomolt 150 - (329)", "dose": "0.15", "campo1": "", "campo2": "", "antiCamara": "0.0765", "previsto": "0.0765", "total": ""}, {"produto": "Trivor - (327)", "dose": "0.3", "campo1": "", "campo2": "", "antiCamara": "0.153", "previsto": "0.153", "total": ""}]”

And when I make a table using this instead of 2 ur 3 rows which should br printed, ir appears 100+ rows.

2

Without seeing the data being processed, it’s going to be difficult for us to try and help you diagnose this. There are a couple things though that I’d like to comment on:

  • You don’t need to use a with ... block for your second loop. You’re creating a cursor object, but never using it. (You’re writing the data using the ORM.) That with statement can be removed.

  • Sqlite stores JSON internally as a text field. Your update of an existing row is then going to store the dados field as a string. (You’re only converting that string to JSON in the case where it’s an insert and not an update.)

  • I don’t know if this is something you do regularly or if this is a 1-time conversion. If this is a process that you’re going to run periodically, I’d suggest you create a model for todos_fichadeaplicacao and read it using the ORM.

  • If you’re needing to figure out what’s being written, you could print row in each iteration, and then print the key fields you’re curious about from existing_ficha or the new instance being created.