Registration without duplicity in Sqlite

Asked

Viewed 685 times

0

I was able to avoid the double record of romaneio(nr_roman), but I made several attempts to do the same with the plaqueta(plaq),so that if the platelet already exists in the database, it persists in requesting the nr platelet, without having to go back to the beginning of the program, but without success.

def cad_romaneios():
    # conectando...
    con_romaneios = sqlite3.connect("romaneios.db")
    # definindo um cursor
    cursor = con_romaneios.cursor()

    # Se a tabela não existe, então é criada a seguir...
    cursor.execute('''CREATE TABLE IF NOT EXISTS romaneios
        (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
        nr_roman VARCHAR(4) NOT NULL,
        data_cad TEXT NOT NULL,
        qtde_cad INT NOT NULL,
        nome_tor VARCHAR(30) NOT NULL,
        nome_fre VARCHAR(30) NOT NULL,
        plac_01 VARCHAR(8),
        plac_02 VARCHAR(8),
        plaq VARCHAR(6) NOT NULL,
        espe VARCHAR(30) NOT NULL,
        dia1 INT NOT NULL,
        dia2 INT NOT NULL,
        comp FLOAT NOT NULL,
        desc FLOAT(2),
        vlrm3 FLOAT NOT NULL,
        volume FLOAT NOT NULL,
        vlrpg FLOAT NOT NULL);''')

    print("===========================")
    print("=  CADASTRO DE ROMANEIOS  =")
    print("===========================")

    continua_cadastro = True
    volume_total = 0.000
    total_pago = 0.00
    total_desconto = 0.000

    while continua_cadastro:
        p_nr_roman = input("Nº do romaneio  : ")
        while p_nr_roman == "":
            p_nr_roman = input("Nº do romaneio  : ")
        # Verifica se já existe romaneio com este número...
        sql_romaneio = "SELECT * FROM romaneios WHERE nr_roman = ?"
        for dados in cursor.execute(sql_romaneio, (p_nr_roman,)):
            if dados:
                beep(700, 50)
                print("========== A T E N Ç Ã O ==========")
                print("    O romaneio nº", p_nr_roman + " já existe!")
                print("===================================")
                # continua_cadastro = False
                time.sleep(.5)
                print("")
                cad_romaneios()

        # ... se o romaneio informado ainda não existe, continua o cadastro...
        p_data_cad = str(input("Data do romaneio: "))
        # A data é obrigatória...
        while p_data_cad == "":
            p_data_cad = str(input("Data do romaneio: "))
        p_qtde_cad = int(input("Quantidade toras: "))
        while not int(p_qtde_cad):
            p_qtde_cad = int(input("Quantidade toras: "))
        p_nome_tor = str(input("Toreiro/Vendedor: ")).upper()
        # Obriga o usuário a informar o nome do Toreiro/Vendedor/Projeto...
        while p_nome_tor == "":
            p_nome_tor = str(input("Toreiro/Vendedor: ")).upper()
        p_nome_fre = str(input("Nome do Freteiro: ")).upper()
        if p_nome_fre == "":
            p_nome_fre = "Não informado."
        p_plac_01 = str(input("Placa 01 veículo: ")).upper()
        # Obriga o usuário a informar uma placa pelo menos...
        while p_plac_01 == "":
            print("Informe uma placa...")
        p_plac_02 = str(input("Placa 02 veículo: ")).upper()
        print("===================================")

        for i in range(p_qtde_cad):
            if i + 1 <= p_qtde_cad:
                print("Cadastrando a tora nº", i + 1)
            else:
                print("Cadastrando última tora...")

The platelet should not have duplicity. This I could not do. I tried to do the same p_nr_roman, but to no avail.

            p_plaq = input("Nº plaqueta: ")
            while p_plaq == "":
                p_plaq = input("Nº plaqueta: ")**
            p_espe = input("Essência...: ")
            while p_espe == "":
                p_espe = input("Essência...: ")
            p_dia1 = input("Diâmetro 1 : ")  # int
            while p_dia1 == "":
                p_dia1 = input("Diâmetro 1 : ")
            p_dia2 = input("Diâmetro 2 : ")
            while p_dia2 == "":
                p_dia2 = input("Diâmetro 2 : ")  # int
            p_comp = input("Comprimento: ")  # float
            while p_comp == "":
                p_comp = input("Comprimento: ")
            # Verifica de houve desconto...
            p_desc = 0
            try:
                p_desc = int(input("Desconto ..: "))
            except ValueError:
                print("Não houve desconto!")
            p_vlrm3 = input("Valor do m3:")  # float
            while p_vlrm3 == "":
                p_vlrm3 = input("Valor do m3: ")

            p_vlrm3 = float(p_vlrm3)
            p_dia1 = int(p_dia1)
            p_dia2 = int(p_dia2)
            p_comp = float(p_comp)
            p_vlrm3 = float(p_vlrm3)

            # =====================================
            # CUBICANDO AS TORAS INDIVIDUALMENTE...
            # Cálculo do desconto...
            # ======================
            p_desc = ((p_desc * p_desc) * p_comp * .7854) / 10000.0

            # Cálculo do volume...
            # ====================
            p_volume = ((((p_dia1 + p_dia2) / 2) * ((p_dia1 + p_dia2) / 2) * .7854 * p_comp) / 10000.000) - p_desc

            # Cálculo do valor pago...
            # ========================
            p_vlrpg = p_vlrm3 * p_volume
            print("Custo da tora: R$ %.2f" % p_vlrpg)
            print("")

            # Aquí vai acumulando os totais do Volume / Valor pago / Desconto
            # ===============================================================
            volume_total = volume_total + p_volume
            total_pago = total_pago + p_vlrpg
            total_desconto = total_desconto + p_desc

            # Contador que controla o número de toras cadastradas...
            # ======================================================
            i += 1
            # GRAVANDO NO BANCO DE DADOS...
            # Informação: são 15 variáveis...
            cursor.execute("""INSERT INTO romaneios (nr_roman, data_cad, qtde_cad, nome_tor, nome_fre, plac_01, plac_02,
                                    plaq, espe, dia1, dia2, comp, desc, vlrm3, volume, vlrpg) 
                                    VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)""", (
                            p_nr_roman, p_data_cad, p_qtde_cad, p_nome_tor, p_nome_fre, p_plac_01, p_plac_02, p_plaq,
                            p_espe, p_dia1, p_dia2, p_comp, p_desc, p_vlrm3, p_volume, p_vlrpg))
            con_romaneios.commit()

        # Montagem e apresentação do Romaneio...
        # ======================================
        import os
        os.system("cls")  # Apaga porção da tela
        beep(700, 70)
        print("  ======================")
        print("  =    Cadastro OK!    =")
        print("  ======================")
        time.sleep(1)
        print("")
        # if i + 1 >= int(p_qtde_cad):
        print("Romaneio nº:", p_nr_roman + "  -  Data:", p_data_cad)
        print("Origem ....:", p_nome_tor + "  -  Freteiro:", p_nome_fre)
        print("Veículo ...:", p_plac_01 + " / ", p_plac_02)
        print("Quantidade :", int(p_qtde_cad), " tora(s)")
        print(29 * "=" + int(len(p_nome_tor + p_nome_fre)) * "=")
        print(" Volume total ...: %.3f" % volume_total + "m3")
        print(" Volume desconto : %.3f" % total_desconto + "m3")
        print(" Valor total pago: R$ %.2f" % total_pago)
        print("")
        print("   Estatística")
        print("  =============")
        print(" Média m3 por tora : %.3f" % (volume_total / int(p_qtde_cad)) + "m3")
        print(" Média pago p/ tora: R$ %.2f" % (total_pago / int(p_qtde_cad)))
        break
    con_romaneios.close()
    sys.exit(0)

principal()
  • 1

    I haven’t read every question, it’s probably too long and none of this is necessary, but is there any reason why I haven’t used UNIQUE? https://sqlite.org/lang_createtable.html

  • Hello dear Denis! Thank you for your time.

  • ... I will delve into this subject of "UNIQUE". Denis grateful!

  • Did the answer solve your question? Do you think you can accept it? See [tour] if you don’t know how you do it. This would help a lot to indicate that the solution was useful for you. You can also vote on any question or answer you find useful on the entire site (when you have 15 points).

1 answer

1

When you need a die (one or more columns) to be unique in a table you use the clause UNIQUE prohibiting the registration of duplicate values in an unambiguous manner and without risk of running condition how it could occur in the code you were trying to do.

In general it is important to have a suitable index to facilitate this check, otherwise it has the risk of slowing down.

Whenever trying to register something duplicate will generate an error in the database that should be handled by the application.

It could be something like this:

CREATE TABLE tabela (col1 INT, col2 INT, UNIQUE(col1, col2));

I put in the Github for future reference.

In this case the conjunction of these two columns needs to be unique.

In some cases you may want to use a ON CONFLICT REPLACE if you want the key to be updated instead of inserted. Or you can choose other forms.

Maybe I just want to be informed of the error in the application. Each language and each Sqlite access technology can work with a different way of handling the error. Example in C#:

try {
    //seu código
} catch (SQLiteConstraintException e) {
    //o que fazer quando dá o erro, pode ser que queira ver se é UNIQUE mesmo
}
  • Good afternoon colleagues! I check that if I don’t use UNIQUE, I can criticize the routine that makes data entry not to allow duplicity; I have done this. At the moment, my doubt is in the code snippet that I wrote, ie: if an existing romanticism number is informed, the user is informed and the program resumes the question; after this criticism, is informed the number of the board; if this already exists, I inform the user and the program has to return to the point where this last information is requested. That’s where I’m not finding my way...

  • That is, you will insist on doing wrong?

  • Ah!! means I can use Try/catch to solve this issue. Sorry, I didn’t understand. Come on then.... Very grateful!!

Browser other questions tagged

You are not signed in. Login or sign up in order to post.