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

dedec0 · 06-28-2022, 05:47 PM

Quote:

Originally Posted by boughtonp

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.

Did you see what I posted in #15, and its output? The code executes everything in the try block, including a call to "sys.exit(0)", which is where an exception occurs (since the print after it is not seen in the output). The database did not get a line of data, as this code supposedly does.

I do not know what else to do.

astrogeek · 06-28-2022, 06:10 PM

The first thing I would do (also as per posts #9 and #11) is to create a simple, single column test table and submit non-parameterized data to it. In other words, replace this...

Code:

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

... with something similar to this for the test table:

Code:

"INSERT INTO test_table VALUES ("value")"

And remove ALL of the unnecessary functions and code surrounding it.

Does it insert? You have a connection, database and working SQL INSERT statement. Now change it to a parameterized INSERT statement... does it work? ...

Interpret "minimal test case" as being just that - the minimal code necessary to insert data. Once that is working then begin to build out the desired use case, and verify it one step at a time.

One other thing that you might do is perform an invalid query and see if it gives an error message. If not then troubleshoot that problem first!

dugan · 06-28-2022, 07:30 PM

Quote:

Originally Posted by dedec0

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.

Just post the CREATE TABLE statement.

dedec0 · 06-28-2022, 07:48 PM

Quote:

Originally Posted by dugan

Just post the CREATE TABLE statement.

I create the tables with sqlitebrowser. So, no create table written by me.

pan64 · 06-29-2022, 12:59 AM

Quote:

Originally Posted by dedec0

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:

....
# Insert data
try:
...
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.

What I told you already. The real error message is suppressed, you inhibit yourself from solving it. Just print the real exception message to know why did it happen and try to handle that.

dedec0 · 06-29-2022, 01:46 AM

Quote:

Originally Posted by pan64

What I told you already. The real error message is suppressed, you inhibit yourself from solving it. Just print the real exception message to know why did it happen and try to handle that.

Supressed? I did not do this. How do I see these errors?

You talked about pylint. Is this what you mean?

dedec0 · 06-29-2022, 02:12 AM

The biggest thing pylint told about my code is that i was importing a file before a library. The rest are just warning, and a complaint about file documentation.

pan64 · 06-29-2022, 02:18 AM

Quote:

Originally Posted by dedec0

Supressed? I did not do this. How do I see these errors?

just look for the official documentation or for example here: https://realpython.com/python-exceptions/

Quote:

Originally Posted by dedec0

You talked about pylint. Is this what you mean?

https://pylint.pycqa.org/en/latest/

boughtonp · 06-29-2022, 07:59 AM

Quote:

Originally Posted by dedec0

Did you see what I posted in #15, and its output? The code executes everything in the try block, including a call to "sys.exit(0)", which is where an exception occurs (since the print after it is not seen in the output). The database did not get a line of data, as this code supposedly does.

I saw it but was tired of repeating myself.

This is a minimal example:

Code:

import sqlite3

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

bdBicicletas.execute("create table if not exists test_table(test_col text)")

bdBicicletas.execute("insert into test_table values (datetime())")

conBicicletas.commit()
conBicicletas.close()
print('done?')

Run it as (e.g.) "python3 minimal.py" and - since there's no try/except - any exception will be output to the command line.

The exception will include the line in question so there's no need for a bunch of print statements.

Likewise, running the script directly there is no need to worry about the connection staying open (as there would be with a long-running webapp), so there's no need for a try block.

In a situation where a try block is necessary (such as to temporarily avoid global error handling), the tutorial Pan64 linked is a reasonable overview - though it does fail to mention that sys.exc_info() can be useful when dealing with exceptions of unknown type (as would be the case here).

Assuming it works, you can create another example with greater functionality (i.e. complexity) knowing that you've ruled out things like incorrect path, read-only file, version mismatch, connection/driver issues, etc.

dedec0 · 07-02-2022, 03:11 PM

Quote:

Originally Posted by boughtonp

In a situation where a try block is necessary (such as to temporarily avoid global error handling), the tutorial Pan64 linked is a reasonable overview - though it does fail to mention that sys.exc_info() can be useful when dealing with exceptions of unknown type (as would be the case here).

So, how do I use the 3 things sys.exc_info() returns? Can you show me that? The documentation you pointed does not say things easy to understand.

dedec0 · 07-02-2022, 04:56 PM

boughtonp · 07-02-2022, 04:58 PM

Hrm, yeah it does waffle and assume the reader knows things that one might not - the first paragraph you don't need to worry about, for the second paragraph...

Quote:

If no exception is being handled anywhere on the stack, a tuple containing three None values is returned. Otherwise, the values returned are (type, value, traceback).

i.e. the function always returns a three-part tuple, but if there isn't an exception happening, the values are None.

Quote:

type gets the type of the exception being handled (a subclass of BaseException)

The "type" may be useful if you need to a subset of logic to behave differently for a class of exception, e.g. using isinstance.

Quote:

value gets the exception instance (an instance of the exception type)

The "value" is what actually went wrong, at least from Python's perspective.

Quote:

traceback gets a traceback object which encapsulates the call stack at the point where the exception originally occurred.

This is where it went wrong - i.e. the filename and line number - other languages call it a stack trace.

Unfortunately if you just print the result of sys.exc_info(), Python isn't very helpful and doesn't give you the stack trace. The error text is sometimes enough to figure out what's up, but to confirm the code causing it, you'll want something to output the stack trace - excepthook is one option, e.g:

Code:

import sqlite3
import sys
try:
	sqlite3.connect("invalid/filename")
except:
	sys.excepthook(*sys.exc_info())

The will output the formatted traceback, followed by "type: value" error text.

dedec0 · 07-02-2022, 06:22 PM

Quote:

Originally Posted by boughtonp

[...]

The "value" is what actually went wrong, at least from Python's perspective.

This is where it went wrong - i.e. the filename and line number - other languages call it a stack trace.

Unfortunately if you just print the result of sys.exc_info(), Python isn't very helpful and doesn't give you the stack trace. The error text is sometimes enough to figure out what's up, but to confirm the code causing it, you'll want something to output the stack trace - excepthook is one option, e.g:

Code:

import sqlite3
import sys
try:
	sqlite3.connect("invalid/filename")
except:
	sys.excepthook(*sys.exc_info())

The will output the formatted traceback, followed by "type: value" error text.

This is so great! I will use

Code:

except:
	sys.excepthook( *sys.exc_info() )

everywhere I can, now.

For my little test:

Code:

"""
bla bla bla
"""
import sys

try:
    print( "oi" )
    sys.exit( 0 )
except:
    print( "erro:" )
    print( sys.exc_info() )
    sys.excepthook( *sys.exc_info() )
    sys.exit( 1 )

print( 'fim' )
sys.exit( 0 )

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

the output is this:

Code:

$ p3 min3.py 
oi
erro:
(<class 'SystemExit'>, SystemExit(0), <traceback object at 0x7f5195c1f840>)
Traceback (most recent call last):
  File "/path/to/my/file/min3.py", line 8, in <module>
    sys.exit( 0 )
SystemExit: 0

$ # exit code = 1

dugan · 07-03-2022, 11:26 AM

I see that you've found the problem and started a new thread to talk about that.

As you've noticed,

Code:

bdBicicletas.executemany
(
...
)

Is not the same as

Code:

bdBicicletas.executemany(
...
)

I'll post the explanation in the other thread.

dedec0 · 07-03-2022, 10:44 PM

In the first post, I explained the program I built, with a fairly simple idea. I did not trim many things of what I had locally because the problem was simple enough to say that the code left would not affect it.

After a long discussion, things learned and some good time playing with Python, I finally achieved a corrected version of the code seen in #1. The main problem it has is the formatting chosen for the source lines that insert the data in the database. It has the issue isolated and shown in this separate thread: Python syntax problem?. Specifically, the main problem is in the lines:

Code:

...
133         bdBicicletas.executemany
134         (
135             "INSERT INTO produtos VALUES ( ?, ?, ?, ?, ?, ?, ?)",
136             dadosDeTeste
137         )
...

Because there is no "" in the end of the line 133, this line is valid, but does nothing. The lines 134-137 are also valid, and they are separated from 133, but also do nothing. These lines are not interpreted together as a call to executemany() function of bdBicicletas variable, with the 2 arguments on line 135 and 136, as intended. To fix it without losing the chosen code arrangement, we have to add that "" to tell Python that line 133 is not everything it has to read at that point.

A fixed version, and improved in a few other points, of #1 code is:

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

# ==== This content is what file constantes.py had: ==========================
# 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 bicicletas 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()
        print( "Inserções no banco feitas e confirmadas." )
        return "Inserções no banco feitas e confirmadas."
    except:
        print( "Erro ao inserir bicicletas de teste." )
        conBicicletas.close()

        # Imprime de forma "crua" a saída da função exc_info()
        print( sys.exc_info() )

        # Imprime de forma amigável e informativa os dados exceção acontecida
        sys.excepthook( *sys.exc_info() )

        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, this code inserts the data as expected, and the thread is solved. It assumes that a database with a good table already exists. This table could be created with the query:

Code:

CREATE TABLE "bicicletas" (
	"número"	INTEGER NOT NULL DEFAULT 1 UNIQUE,
	"situação"	INTEGER,
	"estação"	INTEGER,
	"destino"	INTEGER,
	"alugadaEm"	REAL,
	"entregaMáxima"	REAL,
	"preço"	        REAL,
	PRIMARY KEY("número" AUTOINCREMENT)
);

Thanks to all who helped here