LinuxQuestions.org - [SOLVED] Python: executes without errors or warnings, but data is not inserted in SQLite database

- Programming (https://www.linuxquestions.org/questions/programming-9/)

- - Python: executes without errors or warnings, but data is not inserted in SQLite database (https://www.linuxquestions.org/questions/programming-9/python-executes-without-errors-or-warnings-but-data-is-not-inserted-in-sqlite-database-4175713940/)

Python: executes without errors or warnings, but data is not inserted in SQLite database

Hello, people.

I am learning Python. Right now, I am trying to make a backend using Flask library. I manually created an empty database, defining just the types for each field it has. After that, I manually created some data, which will be used during tests. The execution shows no error, but the database is still empty!

Code:

import sqlite3                              # Biblioteca de SQLite

from crypt import methods

from flask import Flask, jsonify, request

from flask_cors import CORS



# Importa o arquivo com constantes para o projeto

#import constantes

# Arquivo com constantes para o projeto



# Para bicicletas:

BicicletaPronta = 0

BicicletaAlugada = 1

BicicletaEstragada = 2

BicicletaEmConserto = 3

BicicletaNãoDevolvida = 4      # Não devolvida *no prazo*

PreçoPorHora = 2.50            # Preço a pagar por hora de aluguel

PreçoPorHoraDeAtraso = 4.50    # Preço a pagar por hora de atraso



# Para estações

EstaçãoNãoDefinida = 0

EstaçãoVendaNova = 1

EstaçãoPalmares = 2

EstaçãoPrado = 3

EstaçãoSãoGabriel = 4

EstaçãoCentral = 5

EstaçãoSion = 6

DataDesconsiderada = 1721059.5  # Equivale à data  0000/01/01 00:00:00. Veja:

                                # https://sqlite.org/lang_datefunc.html





app = Flask(__name__)

CORS(app)



# Insere dados de teste na tabela bicicletas

# Retorno:

#      0 = normal

#      1 = erro ao inserir algum dado

@app.route( "/populaParaTestes", methods=["GET"] )

def populaParaTestes() :



    # Conecta ao banco de dados das bicicletas e insere dados nele

    conBicicletas = sqlite3.connect(".bancos/bicicletas")

    bdBicicletas = conBicicletas.cursor()

    print( "Conexão ao banco de bicicletas aberta." )



    dadosDeTeste = \

    [

        # número da bicicleta:          inteiro (chave do banco)

        # situação, estação, destino:  3 inteiros

        # alugada em, entrega máxima:  2 reais (data e hora SQLite)

        # preço do empréstimo:          real

        (

            None,

            BicicletaPronta,    # situação

            EstaçãoCentral,      # estação atual, quando guardada

            EstaçãoNãoDefinida,  # estação de destino, quando alugada



            # As datas de empréstimo e devolução serão dadas à função SQLite

            # julianday(), na hora de calcular o preço do aluguel.

            DataDesconsiderada,

            DataDesconsiderada,



            # Preço também é calculado na hora do aluguel

            0

        ),

        (

            None,

            BicicletaEmConserto, # situação

            EstaçãoNãoDefinida,  # estação atual, quando guardada

            EstaçãoVendaNova,    # estação de destino após o conserto



            # As datas de empréstimo e devolução serão dadas à função SQLite

            # julianday(), na hora de calcular o preço do aluguel.

            '2022-06-16 11:33:11',      # Momento em que vai pro conserto

            '2022-06-22 15:22:51',      # Previsão de devolução



            # Preço também é calculado na hora do aluguel

            0

        ),

        (

            None,

            BicicletaEstragada,      # situação

            EstaçãoNãoDefinida,      # estação atual, quando guardada

            EstaçãoSion,            # destino, quando estragada



            # As datas de empréstimo e devolução serão dadas à função SQLite

            # julianday(), na hora de calcular o preço do aluguel.

            '2022-02-01 10:11:01',      # Hora em que saiu para o conserto

            '2022-07-01 10:07:22',



            # Preço do conserto? Não pode ser definido quando ela sai, mas na

            # hora que ela volta, será colocada "pronta" para alugar. Então, o

            # preço de estragada não existe. Pra registrar o caixa, será feito

            # com a situação "em conserto", quando ela for devolvida.

            0

        ),

        (

            None,

            BicicletaAlugada,    # situação

            EstaçãoCentral,      # estação de onde foi emprestada

            EstaçãoPrado,        # estação de destino, quando alugada



            # As datas de empréstimo e devolução serão dadas à função SQLite

            # julianday(), na hora de calcular o preço do aluguel.

            '2022-02-02 20:02:22',

            '2022-07-01 11:22:11',



            # Preço a pagar, entregando dentro do prazo. Explicação didática no

            # arquivo 

            # Para calcular este preço, fazemos a conta:

            # tempoDeAlguel * 24 * preçoPorHora

            # S

            0

        ),

        (

            None,

            BicicletaNãoDevolvida,  # situação

            EstaçãoCentral,      # estação de onde foi emprestada

            EstaçãoPrado,        # estação de destino, quando alugada



            # As datas de empréstimo e devolução serão dadas à função SQLite

            # julianday(), na hora de calcular o preço do aluguel.

            '2022-02-02 20:02:22',

            '2022-07-01 10:07:22',



            # Preço também é calculado na hora do aluguel

            0

        )

    ]



    # Laço para inserir os dados

    try:

        bdBicicletas.executemany

        (

            "INSERT INTO produtos VALUES ( ?, ?, ?, ?, ?, ?, ?)",

            dadosDeTeste

        )

        print( "Pedidos de SQLite para inserção dos dados terminado." )

        conBicicletas.commit()

        print( "Confirmação das inserções no banco terminada." )

        conBicicletas.close()

        return "Inserções no banco feitas e confirmadas."

    except:

        print( "Erro ao inserir bicicletas de teste." )

        conBicicletas.close()

        return "Erro ao inserir bicicletas de teste."



# Executa o arquivo e fica pronto para a chamada de /populaParaTestes localmente

@app.run()

def nada() :

    print( "Nada é executada?" )

    return "Nada"



# vim: fileencoding=utf-8: expandtab: shiftwidth=4: tabstop=8: softtabstop=4

So, to run this backend, the command is python3 <filename.py>. After that, I copy my local IP and port, paste (without visiting) in the browser, add "/populaParaTestes" to it.

The browser shows "Inserções no banco feitas e confirmadas.", which is what this function returns. It is also printed in the terminal. So, the functions bdBicicletas.executemany(), conBicicletas.commit() and conBicicletas.close() were successful. But when I examine the database with sqlitebrowser or sqlitestudio, there is not data there.

What am I doing wrong?

Well, in the case of the code you actually posted, the problem is that you commented out the import of constantes and you're still using stuff from it.

I installed flask and Flask-Cors, created the .bancos directory, took out the try...except wrapper, and ran it. This is what I got:

Code:

❯ python3 filename.py

 * Serving Flask app 'filename' (lazy loading)

 * Environment: production

  WARNING: This is a development server. Do not use it in a production deployment.

  Use a production WSGI server instead.

 * Debug mode: off

 * Running on http://127.0.0.1:5000 (Press CTRL+C to quit)

Conexão ao banco de bicicletas aberta.

[2022-06-27 19:54:02,455] ERROR in app: Exception on /populaParaTestes [GET]

Traceback (most recent call last):

  File "/Users/dugan/Documents/dec/env/lib/python3.9/site-packages/flask/app.py", line 2077, in wsgi_app

    response = self.full_dispatch_request()

  File "/Users/dugan/Documents/dec/env/lib/python3.9/site-packages/flask/app.py", line 1525, in full_dispatch_request

    rv = self.handle_user_exception(e)

  File "/Users/dugan/Documents/dec/env/lib/python3.9/site-packages/flask_cors/extension.py", line 165, in wrapped_function

    return cors_after_request(app.make_response(f(*args, **kwargs)))

  File "/Users/dugan/Documents/dec/env/lib/python3.9/site-packages/flask/app.py", line 1523, in full_dispatch_request

    rv = self.dispatch_request()

  File "/Users/dugan/Documents/dec/env/lib/python3.9/site-packages/flask/app.py", line 1509, in dispatch_request

    return self.ensure_sync(self.view_functions[rule.endpoint])(**req.view_args)

  File "/Users/dugan/Documents/dec/filename.py", line 54, in populaParaTestes

    constantes.BicicletaPronta,    # situação

NameError: name 'constantes' is not defined

127.0.0.1 - - [27/Jun/2022 19:54:02] "GET /populaParaTestes HTTP/1.1" 500 -

Conexão ao banco de bicicletas aberta.

[2022-06-27 19:56:34,492] ERROR in app: Exception on /populaParaTestes [GET]

Traceback (most recent call last):

  File "/Users/dugan/Documents/dec/env/lib/python3.9/site-packages/flask/app.py", line 2077, in wsgi_app

    response = self.full_dispatch_request()

  File "/Users/dugan/Documents/dec/env/lib/python3.9/site-packages/flask/app.py", line 1525, in full_dispatch_request

    rv = self.handle_user_exception(e)

  File "/Users/dugan/Documents/dec/env/lib/python3.9/site-packages/flask_cors/extension.py", line 165, in wrapped_function

    return cors_after_request(app.make_response(f(*args, **kwargs)))

  File "/Users/dugan/Documents/dec/env/lib/python3.9/site-packages/flask/app.py", line 1523, in full_dispatch_request

    rv = self.dispatch_request()

  File "/Users/dugan/Documents/dec/env/lib/python3.9/site-packages/flask/app.py", line 1509, in dispatch_request

    return self.ensure_sync(self.view_functions[rule.endpoint])(**req.view_args)

  File "/Users/dugan/Documents/dec/filename.py", line 54, in populaParaTestes

    constantes.BicicletaPronta,    # situação

NameError: name 'constantes' is not defined

127.0.0.1 - - [27/Jun/2022 19:56:34] "GET /populaParaTestes HTTP/1.1" 500 -

127.0.0.1 - - [27/Jun/2022 19:56:35] "GET /favicon.ico HTTP/1.1" 404 -

The imported file was pasted, but i forgot to remove the link to use them

My local code has 2 files: insertData.py and constantes.py, with the lines i inserted in the first thread:

Code:

# Para bicicletas:

BicicletaPronta = 0

BicicletaAlugada = 1

BicicletaEstragada = 2

BicicletaEmConserto = 3

BicicletaNãoDevolvida = 4      # Não devolvida *no prazo*

PreçoPorHora = 2.50            # Preço a pagar por hora de aluguel

PreçoPorHoraDeAtraso = 4.50    # Preço a pagar por hora de atraso



# Para estações

EstaçãoNãoDefinida = 0

EstaçãoVendaNova = 1

EstaçãoPalmares = 2

EstaçãoPrado = 3

EstaçãoSãoGabriel = 4

EstaçãoCentral = 5

EstaçãoSion = 6

DataDesconsiderada = 1721059.5

But i forgot to remove "constantes." parts when i wrote the first thread. I will edit the first thread, now.

The fact the database existed before the code in #1 was executed is important? It will just insert more data, if i refresh the address in the browser, right?

Now, the code in the first thread works to give a running backend. I tested it.

I was about to ask if maybe you needed a CREATE TABLE statement.

Quote:

Originally Posted by dedec0 (Post 6363996)

The execution shows no error, but the database is still empty!

That is almost impossible.
At first I would recommend to use a linter (like pylint) which will catch a lot of coding issues.
From the other hand a missing table or similar error must be reported, so if you can't see any error it is suppressed.

For this project, I created the database files and the tables inside them, manually. I just did not want to populate them in the same way. But other students created the tables just with sqlite3.connect() function. It is not called "create", and i find this very strange. But it seems to do it, anyway, when the file path passed to it does not exist.

https://docs.python.org/3/library/sqlite3.html is not clear about creating or not the database file. I have that file, and all the columns and types are defined. This is why i only connect to it, insert data, commit and then close.

As i said, the database is already present when i execute this script the first time, and it has the table inside it, with all defined data types for each column. Maybe I should have given you this file, so I am making this post to give it: https://tmpfile.us/f/trPQYVaw Save this file in a folder named ".bancos" in the folder where the Python code of #1 post is saved.

Too much code.

Start by cutting it down to the absolute minimum - e.g. a single simple insert statement to a single-column table - and check the behaviour.

http://www.sscce.org/

Quote:

Originally Posted by boughtonp (Post 6364165)

Too much code.

Start by cutting it down to the absolute minimum - e.g. a single simple insert statement to a single-column table - and check the behaviour.

http://www.sscce.org/

The code in #1 very simple. We read through it with ease. This one can make you happier:

Code:

import sqlite3                              # Biblioteca de SQLite

from flask import Flask, jsonify, request

from flask_cors import CORS



import constantes



app = Flask(__name__)

CORS(app)



# Insert data in an existing table in an existing SQLite database file

@app.route( "/populaParaTestes", methods=["GET"] )

def populaParaTestes() :



    conBicicletas = sqlite3.connect(".bancos/bicicletas")

    bdBicicletas = conBicicletas.cursor()

    print( "Connection opened." )



    dadosDeTeste = \

    [

        # integer

        # 3 integers

        # 2 reals

        # real

        (

            None,



            constantes.BicicletaPronta,  

            constantes.EstaçãoCentral,    

            constantes.EstaçãoNãoDefinida,



            # 2 invalid dates in the data fields

            constantes.DataDesconsiderada,

            constantes.DataDesconsiderada,



            0

        )

    ]



    # Insert data

    try:

        bdBicicletas.executemany

        (

            "INSERT INTO produtos VALUES ( ?, ?, ?, ?, ?, ?, ?)",

            dadosDeTeste

        )

        print( "SQLite insert finished." )

        conBicicletas.commit()

        print( "Commit finished." )

        conBicicletas.close()

        return "Everything done normally."

    except:

        print( "Exception" )

        conBicicletas.close()

        return "Exception"



# Get ready to listen to http://127.0.0.1:5000/populaParaTestes

@app.run()

def nada() :

    print( "Nada é executada?" )

    return "Nada"



# vim: fileencoding=utf-8: expandtab: shiftwidth=4: tabstop=8: softtabstop=4

The behaviour did not change. The database .bancos/bicicletas is still empty.

That's still not a minimal example.

The behaviour you're concerned about is sqlite3 related, right? So remove EVERYTHING that is not sqlite3 related, then further minimize the result until you have something that's approximately 6-7 lines long.

When you've confirmed a regular minimal un-parameterised insert works then switch it for a parameterised executemany and check if it still works, if it does, progressively restore complexity until you re-encounter the unexpected behaviour.

Quote:

Originally Posted by boughtonp (Post 6364175)

So, you see a problem in the smaller code i posted, but do not want to say what it is. "Thank you".

Quote:

Originally Posted by dedec0 (Post 6364184)

So, you see a problem in the smaller code i posted, but do not want to say what it is. "Thank you".

no, you misunderstood. Anyway, you need to clean up your code and make an absolute minimal example. Not only for us, but also for yourself. To be able to catch it.
I can only tell some general rules:
1. you need to check every command/function if that was successfully executed or failed. As an example:

Code:

    except:    # you must catch the exception and print it. This code will just hide the real error message

        print( "Exception" )

        conBicicletas.close()

        return "Exception"

Also as an example you can try to insert data without flask too.
2. you need to check all the table/column and other names if they are not misspelled somehow (for example you write into a table and check another one if the data is available).
3. sometimes pylint or other checker can catch strange errors, you may try to use it.

Quote:

Originally Posted by dedec0 (Post 6364184)

So, you see a problem in the smaller code i posted, but do not want to say what it is. "Thank you".

No, I'm trying to convey a basic debugging/diagnostic technique used by professional developers to quickly narrow down the number of potential issues, whilst simultaneously providing a concise example that demonstrates the issue in question.

An example without Flask

I wrote another reduced example. This one has no Flask squeleton. The call to SQLite inserting data finishes well. But when the database closes, in the "try" codeblock, i call sys.exit() function, and that is what generates an exception (a print() after it is not seen). No output, though.

The new reduced code:

Code:

import sqlite3                              # Biblioteca de SQLite

import constantes

import sys



try:

    conBicicletas = sqlite3.connect(".bancos/bicicletas")

    bdBicicletas = conBicicletas.cursor()

    print( "- Connection opened." )

except:

    print( "Erro ao abrir banco de dados bicicletas." )

    #return "Erro ao abrir banco de dados bicicletas."

    sys.exit( 1 )



dadosDeTeste = \

[

    # integer

    # 3 integers

    # 2 reals

    # real

    (

        None,



        constantes.BicicletaPronta,  

        constantes.EstaçãoCentral,    

        constantes.EstaçãoNãoDefinida,



        # 2 dates that do not matter

        constantes.DataDesconsiderada,

        constantes.DataDesconsiderada,



        0

    )

]



# Insert data

try:

    bdBicicletas.executemany

    (

        "INSERT INTO bicicletas VALUES ( ?, ?, ?, ?, ?, ?, ?)",

        dadosDeTeste

    )

    print( "- SQLite inserted." )

    conBicicletas.commit()

    print( "- Commit done." )

    conBicicletas.close()

    print( "- Everything done normally." )

    sys.exit( 0 )

    print( "- after sys.exit(0)" )

except:

    print( "- exception." )

    conBicicletas.close()

    #return "Exception ocurred."

    sys.exit( 2 )



# vim: fileencoding=utf-8: expandtab: shiftwidth=4: tabstop=8: softtabstop=4

The output of '$ python3 min.py' is:

Code:

$ python min.py 

- Connection opened.

- SQLite inserted.

- Commit done.

- Everything done normally.

- exception.



$

I have to buy some food, now. Soon i will be back.