SQLITE query returning value outside of query rule

Asked

Viewed 546 times

0

I made a program, in python language, that creates a database SQLITE which stores information about a number of closet doors. The information stored are: id (port identification), size (PQ = small, MD = medium, GD = large), enabled (true/false), busy (true/false), qtd_cycles (amount of vzs of port use). Follow the code below:

import sqlite3


class Database:
    def __init__(self):
        try:
            self.conn = sqlite3.connect(r'C:\db\teste_sqlite_db.sqlite')
        except sqlite3.Error as e:
            print(str(e))
            raise

    def init_tables(self):
        try:
            # Cria tabela das portas
            self.conn.execute('''CREATE TABLE IF NOT EXISTS porta(
                                id INTEGER PRIMARY KEY,
                                tamanho VARCHAR(2) NOT NULL,
                                habilitado BOOLEAN NOT NULL,
                                ocupado BOOLEAN NOT NULL,
                                qtd_ciclos INTEGER NOT NULL
                                )''')
            self.conn.commit()
        except sqlite3.Error as e:
            print(str(e))
            raise
        finally:
            self.conn.rollback()

    def close(self):
        try:
            self.conn.close()
        except sqlite3.Error as e:
            print(str(e))
            raise

    def insert_porta(self, id: int, tamanho: str, habilitado: bool, ocupado: bool, qtd_cliclos: int):
        ret = 0

        try:
            self.conn.execute('INSERT INTO porta(id, tamanho, habilitado, ocupado, qtd_ciclos) '
                          'VALUES (?, ?, ?, ?, ?)', (str(id), tamanho, str(habilitado), str(ocupado),
                                                     str(qtd_cliclos)))
            self.conn.commit()
            ret = 1
        except sqlite3.Error as e:
            print(str(e))
            raise
        finally:
            self.conn.rollback()
        return ret

    def select_porta_com_menor_qtd_ciclos(self, tamanho: str, habilitado: bool, ocupado: bool):
        try:
            cur = self.conn.cursor()
            cur.execute(
            "SELECT * FROM porta WHERE qtd_ciclos = (SELECT MIN(qtd_ciclos) FROM porta WHERE tamanho='{}' AND "
            "habilitado='{}' AND ocupado='{}');".format(tamanho, habilitado, ocupado))

            rows = cur.fetchall()

            print("========================")
            for row in rows:
                print("ID: {}".format(row[0]))
                print("TAMANHO: {}".format(row[1]))
                print("HABILITADO: {}".format(row[2]))
                print("OCUPADO: {}".format(row[3]))
                print("QTD_CICLOS: {}".format(row[4]))

            cur.close()
        except sqlite3.Error as e:
            print(str(e))
            raise
        return rows


def main():
    database = Database()
    database.init_tables()
    database.insert_porta(1, 'PQ', True, False, 10)
    database.insert_porta(2, 'PQ', True, False, 9)
    database.insert_porta(3, 'MD', True, False, 8)
    database.insert_porta(4, 'MD', True, False, 15)
    database.insert_porta(5, 'GD', True, True, 2)
    database.insert_porta(6, 'MD', True, False, 5)
    database.insert_porta(7, 'PQ', False, False, 8)
    database.insert_porta(8, 'MD', True, False, 5)
    database.insert_porta(9, 'GD', True, False, 7)
    database.insert_porta(10, 'MD', True, False, 7)

    database.select_porta_com_menor_qtd_ciclos('PQ', True, False)
    print("===========================================================")
    database.select_porta_com_menor_qtd_ciclos('MD', True, False)
    print("===========================================================")
    database.select_porta_com_menor_qtd_ciclos('GD', True, False)

    database.close()


if __name__ == "__main__":
    main()

The rules of the query of the records of the ports are: Returns from the database to(s) port(s) with less use (qtd_cycles), and it has a given size (PQ, MD or GD), is enabled (enabled = true) and unoccupied (busy = false). As can be seen in the method def select_porta_com_menor_qtd_ciclos(self, tamanho: str, habilitado: bool, ocupado: bool) query was as follows: 

cur.execute("SELECT * FROM porta WHERE qtd_ciclos = (SELECT MIN(qtd_ciclos) FROM porta WHERE tamanho='{}' AND habilitado='{}' AND ocupado='{}');".format(tamanho, habilitado, ocupado))

I created the following table to test the query:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0//EN" "http://www.w3.org/TR/REC-html40/strict.dtd">
<html><meta http-equiv="Content-Type" content="text/html; charset=UTF-8"/><title>Exported table: porta</title><style type="text/css">table{border-style:solid;border-width:1px;border-color:black;border-collapse:collapse;}table tr{background-color:white;}table tr.header{background-color:#DDDDDD;}table tr.title{background-color:#EEEEEE;}table tr td{padding:0px 3px 0px 3px;border-style:solid;border-width:1px;border-color:#666666;}table tr td.null{color:#999999;text-align:center;padding:0px 3px 0px 3px;border-style:solid;border-width:1px;border-color:#666666;}table tr td.separator{padding:0px 3px 0px 3px;border-style:solid;border-width:1px;border-color:#666666;background-color:#DDDDDD;}table tr td.rownum{padding:0px 3px 0px 3px;border-style:solid;border-width:1px;border-color:#666666;background-color:#DDDDDD;text-align:right;}</style><body><table><tr class="title"><td colspan="6" align="center">Table: porta</td></tr><tr class="header"><td align="right"><b><i>#</i></b></td><td><b>id</b><br/>INTEGER</td><td><b>tamanho</b><br/>VARCHAR</td><td><b>habilitado</b><br/>BOOLEAN</td><td><b>ocupado</b><br/>BOOLEAN</td><td><b>qtd_ciclos</b><br/>INTEGER</td></tr><tr><td class="rownum"><i>1</i></td><td align="right">1</td><td align="left">PQ</td><td align="left">True</td><td align="left">False</td><td align="right">10</td></tr><tr><td class="rownum"><i>2</i></td><td align="right">2</td><td align="left">PQ</td><td align="left">True</td><td align="left">False</td><td align="right">9</td></tr><tr><td class="rownum"><i>3</i></td><td align="right">3</td><td align="left">MD</td><td align="left">True</td><td align="left">False</td><td align="right">8</td></tr><tr><td class="rownum"><i>4</i></td><td align="right">4</td><td align="left">MD</td><td align="left">True</td><td align="left">False</td><td align="right">15</td></tr><tr><td class="rownum"><i>5</i></td><td align="right">5</td><td align="left">GD</td><td align="left">True</td><td align="left">True</td><td align="right">2</td></tr><tr><td class="rownum"><i>6</i></td><td align="right">6</td><td align="left">MD</td><td align="left">True</td><td align="left">False</td><td align="right">5</td></tr><tr><td class="rownum"><i>7</i></td><td align="right">7</td><td align="left">PQ</td><td align="left">False</td><td align="left">False</td><td align="right">8</td></tr><tr><td class="rownum"><i>8</i></td><td align="right">8</td><td align="left">MD</td><td align="left">True</td><td align="left">False</td><td align="right">5</td></tr><tr><td class="rownum"><i>9</i></td><td align="right">9</td><td align="left">GD</td><td align="left">True</td><td align="left">False</td><td align="right">7</td></tr><tr><td class="rownum"><i>10</i></td><td align="right">10</td><td align="left">MD</td><td align="left">True</td><td align="left">False</td><td align="right">7</td></tr></table><br/><br/><i>Document generated by SQLiteStudio v3.1.1 on qua mai 8 10:20:34 2019</i></body></html>

  • When doing the test, when I look for PQ ports (small) the query returns correctly the ID ports 9 and 10, both have same amount of cycles (7). (Check in the table);
  • When I look for the MD ports (average) the query returns correctly the ID ports 6 and 8, both have the same amount of cycles (5). (Check in the table);
  • However, when I look for GD (grid) ports the query returns erroneously. It was to return only the port with ID 9 with 7 cycles, however, in addition to the port with ID 9, the query also returns the port with ID 10 which is a port of size MD (average), but coincidentally with 7 cylinders as well. (Check in the table);

Would anyone like to tell me why the GD (big) query behaves this way? I’m not finding the problem.

python python-3.x sqlite sqlite3

  • Could you elaborate a problem [mcve]? There is a lot of noise in the question. If the problem is that the query is returning wrong data, just put in the question the query you made, along with the data that is in the table, describing what was the result and what was expected. It seems to me that all this code in Python and HTML has nothing to do with the problem.

  • @Andersoncarloswoss the code itself is a verifiable example, it has a main that creates the database, inserts the records exactly according to the table and makes the queries I mentioned: Query PQ ports (small), query MD ports (media) and GD ports (large). Just copy the code and run the file.

  • I’ll add how I made the queries to the bank in the problem description.

  • That would be a good [mcve]: https://www.db-fiddle.com/f/e2dnNjmidkABv2RMXNejTz/0. You have the table, you have the data and you have the query. All you need in your question. Just describe why the result you got was not the expected result. No need to have Python and HTML in the middle.

  • I appreciate the observation, but I believe I did according to the tutorial you linckou. The code is as simple as possible with respect to the actual code. It reproduces the necessary scenario for the error and it is generated. The visual table is the table that the code creates in the database and that will generate the error. In short, the code creates the database, inserts the information exactly according to the table, makes the cited queries in the explanation and generates the error without having to edit it, maybe, just edit the database directory. But I will take your criticism into consideration and try to improve my next questions. Thank you.

  • Yeah, and that’s a big help. The question I wanted to raise is that even giving to reproduce the problem you can eliminate enough thing that is noise and greatly simplify your question.

  • Got it! I think I need to get better at it, really. I’ll take a closer look at it. Thanks.

Show 2 more comments

1 answer

1


The logic problem you have is not Python, but SQL - your consultation is:

SELECT * FROM porta WHERE qtd_ciclos = 
    (SELECT MIN(qtd_ciclos) FROM porta WHERE 
          tamanho='{}' AND habilitado='{}' AND ocupado='{}'
     )

I mean - the outside select only has one Where condition, which is the "qtd_cycles". The internal select yes, has the port size Constraint enabled, and busy - but returns only one number (in the case of your example, 7) - and the external query then returns all ports with that number of cycles, regardless of size, "enabled" or "busy".

The obvious solutions are either put the 3 conditions also in select from the outside, or create a single select, and sort the query in the descending order of qtd_cycles - the first results will be the smallest cycles, and you filter on the Python side the same values.

That being said - something very important in terms of security - although Python has several ways to form strings, when using SQL queries - be it sqlite, or any other connector, it is important to leave the driver sql to position the parameters, and not use the method .format or another way to format the string - this prevents malicious parameters from being used for SQL Injection.

Here is how the call to the cursor can look like.execut for both styles. (another thing is that although some style rules recommend the use of " on all lines of long strings, such as your SQL, and counting on the concatenation of the compiled, it is much more convenient to use the strings with triple quotes - """ - and put the whole query in a single string - this avoids typing difficulty in the query, reading difficulty, possible problems by forgetting or leaving a " somewhere, among other things. Never let "style norms" get in the way instead of helping.

cur.execute("""
    SELECT * FROM porta WHERE 
        tamanho=:tamanho AND 
        habilitado=:habilitado AND
        tamanho=:tamanho AND
        qtd_ciclos= (SELECT MIN(qtd_ciclos) FROM porta WHERE 
              tamanho=:tamanho AND
              habilitado=:habilitado AND
              ocupado=:ocupado
         )
    """, 
   {"tamanho": tamanho, "habilitado": habilitado, "ocupado": ocupado}
)

Using the notation of "named Parameters" (https://docs.python.org/2/library/sqlite3.html#sqlite3.Cursor ), you pass a dictionary with keys:values in the second parameter of execute - and let Python take the values, convert to SQL (numeric, boolean and date values are automatically converted to SQL notation, for example, without ever requiring an explicit conversion of type str(...) - in addition to preventing any SQL Injection attack - and, in this case, passing each parameter only once, even if they are used twice in the query. (Purely positional parameters - either as you were using the "format", or with the notation of ? sqlite are prone to error in ordering, and difficult to verify when doing code review).

(also note that the ; end of the SQL statement should not be used - it is a syntax of the interactive use of SQL, not of the Select commands - sqlite ignores it, but there are database drivers in which the inclusion of the ; is a syntax error)

  • Thanks for the help and the class. I’m new in both python and SQLITE. Your explanation helped a lot and added knowledge. Thanks!

Browser other questions tagged python python-3.x sqlite sqlite3

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