How to let user input TABLE name in sqlite3, python3?

Question

I need to prompt a user to create a student table, check if a table with such a name exists in the database, and if not create it.

import sqlite3
conn = sqlite3.connect('School')
print ("Database has been created")

def create_table():
 TableName = input("Enter table name: ")
 tb_create ="""CREATE TABLE , (TableName,) (ID INT PRIMARY KEY,title VARCHAR(10), forename VARCHAR(20), 
 surname VARCHAR(20))"""
 tb_exists ="SELECT name FROM sqlite_master WHERE type='table' AND name= ?", (TableName,)
 if not conn.execute(tb_exists).fetchone():
   conn.execute(tb_create)
   print ("Table created successfully")
 else:
 print ("Table Exists!")

I know its possible to inser user inputed value into a table, but how do I create a table with inputed name? What should go after CREATE TABLE? If I use , (TableName,) the code wont compile. Also, once the new table has been added to database, how do I indicate its name in INSER INTO query?

def insert_data():
 conn.execute("INSERT INTO TableName (ID,title,forename,surname)VALUES \
 (234,'Mr','XXX','XXX'")
 conn.commit()

Answer 1

The correct syntax for a CREATE TABLE statement is:

CREATE TABLE tablename(column1 datatype1, column2 datatype2, ....)

Since you want the user to provide the name of the table, you can do it with string interpolation, because you can't pass it with a ? placeholder in the sql query as aparameter:

tb_create = f"CREATE TABLE [{TableName}](ID INTEGER PRIMARY KEY, title TEXT, forename TEXT, surname TEXT)"

The table's name must be enclosed inside square brackets, just in case the user provided a name that is not valid (for example it starts with digit or contains spaces).
Also, if you want the column ID to be autoincrement, you must use INTEGER instead of INT for its data type.
Also, there is no VARCHAR data type in SQLite. Use TEXT.

You can define the variable TableName as global so that you can use it in all the functions, like insert_data().
Use string interpolation for the INSERT statement also.

import sqlite3
conn = sqlite3.connect("School")
print ("Database has been created")
TableName = ""

def create_table():
  global TableName
  TableName = input("Enter table name: ").strip()
  tb_exists ="SELECT name FROM sqlite_master WHERE type = 'table' AND name = ?"
  if not conn.execute(tb_exists, (TableName,)).fetchone():
    tb_create = f"CREATE TABLE [{TableName}](ID INTEGER PRIMARY KEY, title TEXT, forename TEXT, surname TEXT)"
    conn.execute(tb_create)
    print("Table created successfully")
  else:
    print("Table Exists!")

def insert_data():
  if len(TableName) > 0:
    conn.execute(f"INSERT INTO [{TableName}] (ID,title,forename,surname) VALUES (234,'Mr','XXX','XXX')")
    conn.commit()

create_table()
insert_data()