0

I have a program that currently reads a database and it will print out the list of tables the current database has.

This is the DB LINK: database (Copy and Paste)

What I am trying to do now is to get user input to display the records from the specific table they chose. I am having trouble to get user selection to display the records. I am using SQLite3 as my main database software. Also I am very aware of this question on here, but

I keep getting an error when I used the .format(category) embedded on my SQL. 

sqlite3.ProgrammingError:
Incorrect number of bindings supplied. 
The current statement uses 1, and there are 0 supplied.

This is what I have done so far:

import sqlite3

def get_data():
  print("\nSelect a table: ", end="")
  category = input()
  category = str(category)
  if '1' <= category <= '11':
      print()
      return category
  else:
      raise ValueError

def get_tables():
    database = 'Northwind.db'
    connection = sqlite3.connect(database)
    c = connection.cursor()

    sql = "SELECT * FROM sqlite_master WHERE type='table' AND NAME NOT LIKE 'sqlite_sequence' ORDER BY NAME "

    x = c.execute(sql)

    for row in x.fetchall():
        table = row[1]
        print(table)

def main():

  category = get_data()
  print(category)
  get_tables()

if __name__ == "__main__":
    main()

I hope this all makes sense. I appreciate the help.

Copy comment: My sql statement look like this:
*)multiple lines for readability

sql = ("SELECT * FROM sqlite_master 
          WHERE type='table' 
            AND Name = ? 
            AND NAME NOT LIKE 'sqlite_sequence'".format(category))
stovfl
  • 14,998
  • 7
  • 24
  • 51
smokingpenguin
  • 575
  • 2
  • 4
  • 15
  • @smokingpenguin *"Here is my error"*: The error message is contradict to your shown query statement. There is **no** `binding`? - Change to `.format(category,)` **note** the `,` – stovfl Apr 24 '19 at 15:40
  • @stovfl I can't seem to get what you're saying. Does this mean that that my sql statement should look like this: sql = ("SELECT * FROM sqlite_master WHERE type='table' AND Name = ? AND NAME NOT LIKE 'sqlite_sequence'".format(category,)) – smokingpenguin Apr 24 '19 at 17:38
  • @smokingpenguin: **NOW**, you are showing the `.format(...` statement that's the culprit of the followup `sqlite3.ProgrammingError:`. You are misleaded how `.format(...` and `sqlite binding` are working. You'r using `sqlite` syntax for `.format(...` wich does **nothing**. Add a `print(sql)` to see what you get. **Solution**: Don't use `.format(...` change to `c.execute(sql, (category,))`. – stovfl Apr 24 '19 at 19:48

1 Answers1

0

Your SQL string should be:

sql = """SELECT * FROM sqlite_master 
         WHERE type='table' 
         AND Name = ? 
         AND NAME NOT LIKE 'sqlite_sequence'"""

and the execute statement should be:

x = c.execute(sql, (category,))

also ensure that you are passing category as a parameter to your get_tables function.

Verma
  • 956
  • 6
  • 21