Ask for user input and insert in DB in python with sqlite3

Question

At the moment I try to ask the user for input in Python, to enter some information (here: expenseID, expense, categoryID, date). But I do not know were to start. No input validation is necessary at this step.

I managed to access my database and INSERT something manually. I tried several ways of the python input function but cannot use it as a placeholder in the SQL string.

import sqlite3 

with sqlite3.connect('Expenses.sqlite') as conn:
    # INSERT MANUALLY
    script = "INSERT INTO Expense (ExpenseId, Amount, CategoryId, Date) VALUES ('103', '43625.5', '5', '2019-01-20');"
    conn.execute(script) # execute the script
    conn.commit()  # commit changes to the file
    # INSERT USER INPUT ???
    pass

This is my idea:

with sqlite3.connect('Expenses.sqlite') as conn:

    amount = input("What is the amount?")
    script = "SELECT * FROM Category;"
    conn.execute(script)
    print(script)
    category = input("What is the category?")
    exp_ID = "SELECT LAST ExpenseId FROM Expense);"
    date = datetime.date.today()
    script = "INSERT INTO Expense (ExpenseId, Amount, CategoryId, Date) VALUES (exp_ID, amount, category, date);"
    conn.execute(script)
    conn.commit()
    pass

Finally I want to achieve that the user is asked for amount of expense, and afterwards for expense category. ExpenseID and date should be added automatically. Date format is year-month-day. Thank you very much for advice.

Austin P · Accepted Answer · 2019-05-02 16:00:47Z

2

Use the input function to retrieve the user input

user_input = input("Expense Amount: ")

Then use placeholders with sqlite3

sql = "INSERT INTO TABLE (COLUMN) VALUES (?)"
conn.execute(sql, (user_input,))

**in response to your edit

You need to add placeholders instead of the variable names.

Something like this:

script = "INSERT INTO Expense (ExpenseId, Amount, CategoryId, Date) VALUES (?, ?, ?, ?);"
conn.execute(script, (exp_ID,amount,category,date))

edited May 2, 2019 at 16:00

answered May 2, 2019 at 15:29

Austin P

814 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

roganjosh Over a year ago

This says absolutely nothing about how to insert data into an SQLite DB

Austin P Over a year ago

Yes, my mistake I did not fully read the question. Edited my answer

Marco Over a year ago

I tried input outside of sql-connection but did not manage to include it in script by any placeholder. That's my first problem.

Marco Over a year ago

So, it looks like a great idea, but I get IntegrityError with this approach. Regards

Austin P Over a year ago

IntegrityError is a sqlite exception. Check your column definitions.

Collectives™ on Stack Overflow

Ask for user input and insert in DB in python with sqlite3

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related