Search code examples
pythonstringsqlitestring-formatting

How to create table dynamically from user input?

I am creating a wishlist app using Tkinter and sqlite3. I want the user to be able to create tables in database by imputing names. For that I connected a button to this function:

def create_table(table_name):
    connection = sql.connect(f'{directory}\main.sqlite')
    cursor = connection.cursor()
    
    cursor.execute("CREATE TABLE ? (name TEXT, price REAL, url TEXT)",(table_name,))
    connection.close()

This doesn't work and I get:

cursor.execute("create table ? (name text, price real, url text)",(table_name,)) sqlite3.OperationalError: near "?": syntax error

Is it possible to do string formatting in CREATE TABLE? I'd rather create separate tables than one with additional column for id of items. I don't want to use f-string as it can be an issue if user inputs commands instead of a name.

Solution

  • Nope, this cannot be done. A table name cannot act as a dynamic parameter from SQLite's point of view. You will need to do something like this:

    f'CREATE TABLE {table_name} (name TEXT, price REAL, url TEXT)'

    But first you will need to validate the user input for table_name. Which shouldn't be a problem if you want to limit the allowed characters to (for example) only 1+ English letters and 0+ underscores. You might also want to validate the table name length and uniqueness somehow.