CODEWITHSUNDEEP
pythonhtmldatabasesqliteflask
r0001
r0001

Reputation: 1

My sqlite3 if-else query in Flask application isn't returning the results I want

application.py

import os
from flask import Flask , render_template, request
import sqlite3 as sql

app = Flask(__name__)

@app.route("/")
def index():
    return render_template("index.html")

@app.route("/salary", methods = ["POST"])
def salar():
    name = request.form.get("employeee_name")
    price = request.form.get("pay")
    with sql.connect('database.db') as conn:
        c=conn.cursor()
    if c.execute('SELECT employee_name FROM employees WHERE employee_name=name').fetchone() is None:
        c.execute('INSERT INTO employees(employee_name,basicpayment) VALUES (?,?)',(name,price))
        conn.commit()
        return render_template("success.html", name=name)
    else: 
        return render_template("already.html")  

if __name__ == '__main__':
    app.run(debug = True)

Database Creation

import sqlite3
conn = sqlite3.connect('database.db')
conn.execute('CREATE TABLE employees(employee_name TEXT,basicpayment INT)')
conn.close()

index.html

<!DOCTYPE html>
<html>
</body>
<title>MY SALARY</title>
<body>
    <form action="{{url_for('salar')}}" method ="POST">
        <input type="text" name="employeee_name" placeholder="Enter employee name here.....">
        <input type="number" name="pay" placeholder="Enter pay of employee">
        <button>SUBMIT</button>
    </form>
</body>
</html>

success.html

<!DOCTYPE html>
<html>
<head>EMPLOYEE ADDED TO DATABASE</head>
<BODY>
    <P>
        {{name}}, has been added to database!
        <a href="{{url_for('index')}}">MAKE ANOTHER ENTRY TO THE DATABASE</a>
    </P>
</BODY>
</html>

already.html

<!DOCTYPE html>
<html>
<HEAD>THIS EMPLOYEE ALREADY EXISTS IN THE DATABASE</HEAD>
<BODY>
    <P>
        {{name}}, already exists in the database!
        <a href="{{url_for('index')}}">MAKE ANOTHER ENTRY TO THE DATABASE</a>
    </P>
</BODY>
</html>

I have been deleting the database and creating it again because of the "wrong" error messages. Sometimes it says table isn't created and sometimes it can't find the column name.

Upvotes: 0

Views: 127

Answers (2)

DinoCoderSaurus
DinoCoderSaurus

Reputation: 6520

This SELECT employee_name FROM employees WHERE employee_name=name would give a "can't find column name" message because employees has not column called name. Here is info from the sqlite3 python doc on how to use parameter substitution:

use the DB-API’s parameter substitution. Put ? as a placeholder wherever you want to use a value, and then provide a tuple of values as the second argument to the cursor’s execute() method.

The doc gives many examples.

Upvotes: 1

Ujjwal Dash
Ujjwal Dash

Reputation: 823

You have forgot to create cursor() object

Here is the correct code to connect:

import sqlite3
conn = sqlite3.connect('database.db')
c=conn.cursor() # add this
c.execute('CREATE TABLE if not exists employees(employee_name TEXT,basicpayment INT)')
conn.commit()
c.close()

Upvotes: 1

Related Questions