Python Forum
Need help with SQLite data input
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Need help with SQLite data input
#1
My code checks if an account number exists and if so just prints a statement saying so, else if not then it asks for a name and then stores the new account number and name.

I have defined the data type for the fields both as TEXT. My program works if I only enter a single digit but if I enter a number of 2 or more digits then I get an error. But what is confusing to me is that I can enter a name for customer name with many characters and it stores fine even though both fields are defined as TEXT. I also tried defining the account number field as INTEGER and REAL and I get the same result.

Here's my code:
import sqlite3

conn = sqlite3.connect('example.db')
c = conn.cursor()

def create_table():
    c.execute('CREATE TABLE IF NOT EXISTS customers_main(acctnum TEXT, custname TEXT)')
    
def manual_input():
    account = input("Enter account number: ")
    
    c.execute("SELECT acctnum from customers_main WHERE acctnum=?", (account))

    result = c.fetchone()

    if result:
        print('Customer in database!')
    else:
        customername = input('Enter customer name: ')
        c.execute("INSERT INTO customers_main VALUES (?, ?)", (account, customername))
        conn.commit()

create_table() 
manual_input()

c.close()
conn.close()
And here's the error:
Error:
=================== RESTART: /home/pi/Documents/forum1.py =================== Enter account number: 1 Enter customer name: Don >>> =================== RESTART: /home/pi/Documents/forum1.py =================== Enter account number: 1 Customer in database! >>> =================== RESTART: /home/pi/Documents/forum1.py =================== Enter account number: 1234 Traceback (most recent call last): File "/home/pi/Documents/forum1.py", line 24, in <module> manual_input() File "/home/pi/Documents/forum1.py", line 12, in manual_input c.execute("SELECT acctnum from customers_main WHERE acctnum=?", (account)) sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 4 supplied.
What am I doing wrong?
Reply
#2
You never put anything in the database,
the table was created properly
using the command line interface, here's a snapshot of your database:
Output:
λ sqlite3 example.db SQLite version 3.21.0 2017-10-24 18:55:49 Enter ".help" for usage hints. sqlite> .schema CREATE TABLE customers_main(acctnum TEXT, custname TEXT); sqlite> select * from customers_main; sqlite> select count(*) from customers_main; 0 sqlite> .quit
Reply
#3
Actually I did store something in the database. If you look at the error message you can see that I entered 1 then my name and it was stored. I then ran it again and entered 1 and it printed my message indicating that it exists. I then ran it a 3rd time and entered 1234 and that's when I got the error.
Reply
#4
But that's not done until after you run:
c.execute("SELECT acctnum from customers_main WHERE acctnum=?", (account))
which creates the error because at this point, the table is empty
Reply
#5
Perhaps I need to ask the question in a different way- why will it let me store a row (record) using a single digit or letter in first column but not multiple digits or letters? It will store multiple letters in second column which in my mind is defined the same as first column.
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Help with to check an Input list data with a data read from an external source sacharyya 3 318 Mar-09-2024, 12:33 PM
Last Post: Pedroski55
  need help with data analysing with python and sqlite Hardcool 2 300 Jan-30-2024, 06:49 AM
Last Post: Athi
  manually input data jpatierno 0 316 Nov-10-2023, 02:32 AM
Last Post: jpatierno
  Input network device connection info from data file edroche3rd 6 913 Oct-12-2023, 02:18 AM
Last Post: edroche3rd
Question in this code, I input Key_word, it can not find although all data was exact Help me! duchien04x4 3 973 Aug-31-2023, 05:36 PM
Last Post: deanhystad
  Showing an empty chart, then input data via function kgall89 0 943 Jun-02-2022, 01:53 AM
Last Post: kgall89
Question Change elements of array based on position of input data Cola_Reb 6 2,063 May-13-2022, 12:57 PM
Last Post: Cola_Reb
  input data validation plumberpy 2 1,747 Aug-11-2021, 12:04 PM
Last Post: plumberpy
  Error while transferring data from sqlite to elasticsearch - please help! ps96068 1 2,630 Jun-12-2021, 09:24 AM
Last Post: ibreeden
  Importing data from a text file into an SQLite database with Python macieju1974 7 4,014 Jun-29-2020, 08:51 PM
Last Post: buran

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020