Create SQLite columns from a list or tuple?

[snakes]

Is there a way to create a table with column names pulled from a list or tuple?
For example...

cur.execute('CREATE TABLE IF NOT EXISTS Customers (Name TEXT, Address TEXT, Email TEXT, Phone TEXT)')

creates a table called Customers with the columns Name, Address, Email, Phone.

but...

my_list = ["Name", "Address", "Email", "Phone"]
cur.execute('CREATE TABLE IF NOT EXISTS Customers (my_list TEXT)')

obviously just create a table called Customers with a column called my_list.

Thanks for any help

Yoriz write May-02-2021, 10:07 PM:
Please post all code, output and errors (in their entirety) between their respective tags. Refer to BBCode help topic on how to post. Use the "Preview Post" button to make sure the code is presented as you expect before hitting the "Post Reply/Thread" button.

[Larz60+]

Yes, see: google: "insert list into sqlite3 database python"
If you'd like to use sqlalchemy see: https://python-forum.io/Thread-SqlAlchem...-Data-Load

[snakes]

Thank you.

I did try this, and with DDG too but there isn't an obvious answer to me.

[ibreeden]

Perhaps there are more elegant options to solve this, but if there are not, it is not difficult to solve in in Python.

my_list = ["Name", "Address", "Email", "Phone"]
my_columns = " TEXT, ".join(my_list)
my_command = "CREATE TABLE IF NOT EXISTS Customers (" + my_columns + " TEXT)"
print(my_command)
# cur.execute(my_command)

Output:
CREATE TABLE IF NOT EXISTS Customers (Name TEXT, Address TEXT, Email TEXT, Phone TEXT)

[snakes]

Thank you!

I've discovered sql can be very fussy on things like spacings and commas... I would never have worked out what to do without your help. Thank you.

My command ended up as...

cur.execute("CREATE TABLE IF NOT EXISTS " + self.tab_name + " (" + " TEXT, ".join(self.detail_name_list_keys) +" TEXT)")
cur.executemany("INSERT INTO "+ self.tab_name +"(" + ", ".join(self.detail_name_list_keys) + ") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", str(entry_value,))
            

The downside is that I now get an error reading

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

Depending on various things I've tried it sometimes says 7 are supplied, 8 are supplied or 9. I've basically created a form with 10 input boxes. If I fill in the boxes with details and press 'save' it says 12 are supplied!

[ibreeden]

There is not enough information to give the right solution, but I will try to explain what you need to know to make it work.

cur.executemany("INSERT INTO "+ self.tab_name +"(" + ", ".join(self.detail_name_list_keys) + ") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", str(entry_value,))

This consists of a number of elements that have to match.

cur.executemany(): This is used to insert more than one row at one time. To do this, the last parameter must be a list of tuples. Do you want this? If you need to insert only one row then you should use cur.execute() and the last parameter should be a tuple.

"INSERT INTO "+ self.tab_name +"(" + ", ".join(self.detail_name_list_keys) + "), well this seems to be allright now. All the column names should be listed here, separated by commas.

VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?), you must make sure to have just as many question marks as there are column names. So you must add some code to add the right number of question marks to the string.

str(entry_value,), this has to be a tuple (in case of execute, or a list of tuples if using executemany). The tuple must have just as many elements as there are question marks. This seems to be not correct because you stringify the entry_value, making it one string. So not a tuple.

By the way. It would help if you have an error to include the full error message in you post (in [error][/error] tags).

[snakes]

This is really helpful, thank you.

The code I posted was one iteration of the 500 I've tried!

I think I have the sql code correct now but my problem is that entry_value is created using a for loop so the tuple is only ever one value long. I'll try to work out how build the tuple outside the loop.

Thank you once again!

EDIT:
Done it! Thank you.
I'm sure my code it structured incorrectly but so far it's doing what I wanted. The database stuff is in the method on_save_button. No doubt vendor will cause problems because there aren't enough ?'s.

from tkinter import *
from tkinter.ttk import *
import sqlite3

### Create SQLite database and connect to it ###
conn = sqlite3.connect('Bookkeeping_Database.sqlite3')
cur = conn.cursor()

### Create tkinter window ###
root = Tk()
root.title("Bookkeeping")
root.geometry("800x600") 

### Add a Notebook to window ###
home_screen = Notebook(root)
home_screen.pack(fill="both", expand=1, pady=15)

### Class to add tabs to Notebook with a method to add entry boxes and a save button ###
class Contacts_tab:
    def __init__(self, tab):
        self.tab_name = str(tab)
        self.tab = tab
        self.tab = Frame(home_screen)
        self.tab.pack(fill="both", expand=1)
        home_screen.add(self.tab, text=self.tab_name)
        self.detail_name_dictionary = {}
        self.detail_name_list_keys = []
        
    def create_entry_boxes(self, detail_name):
        self.detail_name = detail_name
        self.entry_box = Entry(self.tab)
        self.entry_box.grid(column=1)
        self.entry_box.insert(0, detail_name)   
        self.detail_name_dictionary[self.detail_name] = self.entry_box
        self.detail_name_list_keys.append(detail_name)      
        
    def save_button(self):
        self.save_button = Button(self.tab, text="Save")
        self.save_button.grid(column=1)    
    
    def on_save_button(self, event):
        inputted_data=[]
        for detail_name in self.detail_name_list_keys:
            entry_value = self.detail_name_dictionary[detail_name].get()
            print(f' {detail_name} = {entry_value}') 
            inputted_data.append(entry_value)
        inputted_data=tuple(inputted_data)
        print(inputted_data)
        print(type(inputted_data))
        print(self.detail_name_list_keys)
        
        cur.execute("CREATE TABLE IF NOT EXISTS " + self.tab_name + " (" + " TEXT, ".join(self.detail_name_list_keys) +" TEXT)")
        cur.execute("INSERT INTO "+ self.tab_name +"(" + ", ".join(self.detail_name_list_keys) + ") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", inputted_data,)
        conn.commit()

### Create Customers tab ###
def create_customer_tab():
    customer_tab = Contacts_tab("Customers")

    entry_boxes = [ "Customer_ID",
                    "Company",
                    "Name",
                    "Address_1",
                    "Address_2",
                    "Address_3",
                    "Address_4", 
                    "Email", 
                    "Phone_1", 
                    "Phone_2",
                    ]
    for detail_name in entry_boxes:
        customer_tab.create_entry_boxes(detail_name)
        
    customer_tab.save_button()
    customer_tab.save_button.bind('<Button-1>', customer_tab.on_save_button)

def create_vendor_tab():
    vendor_tab = Contacts_tab("Vendors")

    entry_boxes = [ "Vendor_ID",
                    "Company",
                    "Name",
                    "Address_1",
                    "Address_2",
                    "Address_3",
                    "Address_4", 
                    "Email", 
                    "Phone_1", 
                    "Phone_2",
                    "VAT_number"
                    ]
    for detail_name in entry_boxes:
        vendor_tab.create_entry_boxes(detail_name)
        
    vendor_tab.save_button()
    vendor_tab.save_button.bind('<Button-1>', vendor_tab.on_save_button)

#Main
create_customer_tab()
create_vendor_tab()
root.mainloop()
conn.close()