how to validate user input from database

[johnconar]

hi
i am creating a application i want to verify the user input name from my database.


These are my Two table

def create_tables(db):
    cur = db.cursor()

    cur.execute("""CREATE TABLE IF NOT EXISTS Habit (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,
    task_specification TEXT,
    period TEXT,
    created_at BOOLEAN,
    streak INT ) """)

    cur.execute("""CREATE TABLE IF NOT EXISTS count (
    name TEXT,
    created_at BOOLEAN,
    FOREIGN KEY (name) REFERENCES Habit(name))""")

    db.commit()

database function

def get_habit_data(db, name):
    cur = db.cursor()
    cur.execute("SELECT * FROM Habit WHERE name=?", (name,))
    result = cur.fetchone()
    if result is None:
        print("record already exists")

Where i test the application it prints "record already exists" when it not exist inthe database.

Gribouillis write Sep-10-2022, 05:31 PM:
Please post all code, output and errors (it it's entirety) between their respective tags. Refer to BBCode help topic on how to post. Use the "Preview Post" button to make sure the code is presented as you expect before hitting the "Post Reply/Thread" button.

[Gribouillis]

According to Python's DB API, the method .fetchone() returns None when no more data is available in a query return set. So your code asks to print "record already exists" when the record does not exist.

The error is in your code's wrong logic.

[Pedroski55]

I never understood why people use IF NOT EXISTS.

In my opinion, you really should know what tables you have in your database. Somebody has to keep an eye on them!

Check in phpMyAdmin, or do this first:

Why would you go around randomly creating new tables? They are made for a purpose!

import pymysql

def showTables(): 
    # To connect remote MySQL database 
    conn = pymysql.connect( 
        host='123.456.789.123', 
        user='me',  
        password = 'secret', 
        db='mydb', 
        ) 
      
    cur = conn.cursor()

    sql = "SHOW TABLES"
    cur.execute(sql)
    output = cur.fetchall()
    mytables = []
    for i in output: 
        mytables.append(output[i][0])
      
    # To close the connection 
    conn.close()
    return mytables

existing_tables = showTables()

if not new_table in existing_tables:
    create_tables(db)

[ndc85430]

def get_habit_data(db, name):
    cur = db.cursor()
    cur.execute("SELECT * FROM Habit WHERE name=?", (name,))
    result = cur.fetchone()
    if result is None:
        print("record already exists")

Your function is a bit strange, because it doesn't even return its result (it implicitly returns None in all cases). Also, testing by just running the application and printing stuff is not ideal. Why? Because we're humans and make mistakes - can you be certain that you've gotten the set up and inputs correct when you're testing? Not really, because there's nothing written down for you to look back at to see what you did. Also, you're going to need to check this stuff is still working as you change the code. Do you really want to repeat all these steps manually every time (which is time consuming and boring) and run the risk of not doing things correctly? Writing automated tests to get the computer to do the work solves these problems. Here are some example tests using the unittest module that's in Python's standard library:

import unittest

class TestHabitDatabase(unittest.TestCase):
    def test_when_no_habit_exists_with_the_given_name_None_is_returned(self):
        db = create_db() # Use production code to create the database!

        habit = get_habit_data(db, "doesnt-exist")

        self.assertEquals(habit, None)

    def test_a_habit_can_be_found_by_name(self):
        db = create_db()
        habit = Habit(
            id=None,
            name="Foo",
            task_specification="Bar",
            period="Baz",
            created_at=True,
            streak=0
        )
        add_habit(db, habit)

        retrieved_habit = get_habit_data(db, "Foo")

        self.assertEquals(retrieved_habit.name, "Foo")
        # Check other properties too


if __name__ == "__main__":
    unittest.main()

Saving the tests in a file called test_habit_database.py, you can run them with python3 test_habit_database.py (and they'll fail without the implementations of the various functions, obviously!).

Another thing to point out is that I've introduced a data class to model a habit:

from dataclasses import dataclass


@dataclass
class Habit:
    id: int
    name: str
    task_specification: str
    period: str
    created_at: bool # Really?
    streak: int

I'm assuming a habit is an important concept in your application, so it should be modelled so people know that when reading the code. Also, the database API returns a tuple and having to refer to indexes for the various fields in the calling code isn't very readable. So, as the test suggests, I'd have get_habit_data return a Habit object.