Python Forum
sqlite: INSERT using a variable for table name
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
sqlite: INSERT using a variable for table name
#1
I am trying to figure out how to use a variable instead of the actual table name in the database statement when using INSERT.
I am using a MenuOption to select a recipe topic. I want the INSERT to database to use the selection chosen as the table name.

Example: If the selection is "Appetizers" from the MenuOption I would want the data in the entry fields to INSERT into that table and like wise for any menu option select to load only to the table name that is selected in the MenuOption.

Any help would be appreciated.

    def UpLoad():
        selection = var.get()
        try:
            c.execute('''INSERT INTO {} VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?).format(selection)''', (Recipe.get(),
            (Cook_Time.get()), (Serves.get()), (Difficulty.get()), (Ingredient1.get()), (Ingredient2.get()), (Ingredient3.get()), (Ingredient4.get()), (Ingredient5.get()),
            (Ingredient6.get()), (Ingredient7.get()), (Ingredient8.get()), (Ingredient9.get()), (Ingredient10.get()), (Ingredient11.get()), (Ingredient12.get()),
            (Ingredient13.get()), (Ingredient14.get()), (Ingredient15.get()), (Ingredient16.get()), (Ingredient17.get()), (Ingredient18.get()), (Ingredient19.get()),
            (Ingredient20.get()), (Ingredient21.get()), (Ingredient22.get()), (Instruction1.get()), (Instruction2.get()), (Instruction3.get()), (Instruction4.get()), (Instruction5.get()), (Instruction6.get()),
            (Instruction7.get()), (Instruction8.get()), (Instruction9.get()), (Instruction10.get()), (Instruction11.get()), (Instruction12.get()), (Instruction13.get()),
            (Instruction14.get())))
            conn.commit()
        except:
            messagebox.showerror("Error", "Data Base was not able to SAVE")

    var = tkinter.StringVar(window_3)
    # initial value
    var.set('Menu')

    choices = [
        'Appetizers',
        'Beef',
        'Bread',
        'Cake',
        'Chicken',
        'Chilli',
        'Curry',
        'Desert',
        'Drinks',
        'Egg',
        'Fish',
        'Pasta',
        'Pork',
        'Potato',
        'Rice',
        'Salad',
        'Sandwich',
        'Sauce',
        'Sea Food',
        'Slow Cooker',
        'Soup',
        'Stew',
        'Tofu',
        'Vegetables']

    option = tkinter.OptionMenu(window_3, var, *choices)
    option.place(x=215, y=120)

    button = tkinter.Button(window_3, text="Save Recipe", command=UpLoad)
    button.pack
    button.place(x=363, y=120)
"Often stumped... But never defeated."
Reply
#2
Fix your DB/schema design. Don't use separate table for each type. You need only one table for recepies. Recepy/dish type is just another table and extra column in recepies. Also I would I would not store each ingredient as separate column.
If you can't explain it to a six year old, you don't understand it yourself, Albert Einstein
How to Ask Questions The Smart Way: link and another link
Create MCV example
Debug small programs

Reply
#3
Thank you for the reply... I have since made revisions to the DB. IT now has a column to hold the various dish types as you suggested instead of multiple tables.

I have been using the MenuOption to display the available choices however I have a window that the menuoption would be displayed at the bottom of the window... this creates a problem because the menu would exceed the window because it drops down. I have been checking to see if there was a way to have the menu open in the upward direction but have not found anything to do that so I have been exploring the possibility of using the combobox.

I have the old method able to call all the data I needed through a variable for the table of the menuoption but I am struggling to do the same with the combobox.

I have the combobox displaying correctly and I can get it to print the corresponding choice each time one is selected however I could use some help in the DB query to get the selected items.

As seen in the prior example this of for a recipe program and the menu would have the same available option.
If I select the #1 item in the list it would be "Appetizers". The database now has a column named Dish_Type where all recipes would be categorized to one of the corresponding options in the combobox.

    def Load():
        selection = combobox.current(), combobox.get()
        try:
            c.execute('SELECT * FROM Recipes WHERE dish_type = {} '.format(selection))
            data = c.fetchall()
            print(data)
        except:
            messagebox.showinfo("Recipes","Please choose a Topic from the list.")

    combobox = ttk.Combobox(window_2,
    values = [
    'Categories',
    'Appetizers',
    'Beef',
    'Bread',
    'Cake',
    'Chicken',
    'Chilli',
    'Curry',
    'Desert',
    'Drinks',
    'Egg',
    'Fish',
    'Pasta',
    'Pork',
    'Potato',
    'Rice',
    'Salad',
    'Sandwich',
    'Sauce',
    'Sea Food',
    'Slow Cooker',
    'Soup',
    'Stew',
    'Tofu',
    'Vegetables'])
    combobox.place(x=210, y=130)
    combobox.current(0)
    print(combobox.current(), combobox.get())
Can you offer some insight for the best way to have it query the DB for only the selected option in the combobox when it is chosen and the button clicked?
"Often stumped... But never defeated."
Reply
#4
No need for the above to be answered.... Thank you again.
"Often stumped... But never defeated."
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Insert 10gb csv files into sql table via python mg24 2 1,831 Apr-28-2023, 04:14 PM
Last Post: snippsat
  store all variable values into list and insert to sql_summary table mg24 3 1,097 Sep-28-2022, 09:13 AM
Last Post: Larz60+
  Insert into SQL Table only when Table is First Created? Extra 4 1,409 Jun-28-2022, 07:50 AM
Last Post: Pedroski55
  UPDATE SQLITE TABLE - Copy a fields content to another field. andrewarles 14 4,245 May-08-2021, 04:58 PM
Last Post: ibreeden
  Unable to Update SQLite Table sambanerjee 5 2,865 Sep-30-2020, 12:21 PM
Last Post: Larz60+
  How to create db table with SQLite and SQLAlchemy?? marcello86 1 2,272 Sep-02-2020, 03:05 PM
Last Post: marcello86
  how to use items combobox in table name sqlite in python hampython 1 2,624 May-24-2020, 02:17 AM
Last Post: Larz60+
  Failed to insert record into MySQL table.Python type tuple cannot be converted farah97 3 21,359 Dec-26-2019, 02:01 PM
Last Post: buran
  Insert a variable in a Python > Cellular AT command ElectronicsNut 1 2,038 Jul-07-2019, 02:26 PM
Last Post: joe_momma
  Updating records 1 to n on an SQLite table KevinBrown 2 2,608 Mar-30-2019, 05:02 PM
Last Post: KevinBrown

Forum Jump:

User Panel Messages

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