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()

hm?? I clicked as accept, no idea what's going on

DLaningham
– DLaningham

2021-02-02 16:30:49 +00:00
Commented Feb 2, 2021 at 16:30 — DLaningham
– DLaningham, Commented Feb 2, 2021 at 16:30

forpas · Accepted Answer · 2021-02-02 13:32:15Z

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()

Serge Ballesta · Accepted Answer · 2021-02-02 13:18:19Z

The name of the table, or of the columns cannot be parameterized, so you must build the query string. It means that you should control that the entered name is sane because building query from strings is know to be a vector for SQL injection.

But most (if not all) SQL database allow to create a table if it does not already exists. The syntax is just:

CREATE TABLE table_name IF NOT EXISTS (
    column_name type,
    ...)

So here, you could just do:

import re
...
def create_table():
 TableName = input("Enter table name: ")
 # control name:
 if not re.match(r'\w*$', TableName):
     raise IllegalValue("Invalid table name")
 tb_create =f"""CREATE TABLE {TableName} IF NOT EXISTS (ID INT PRIMARY KEY,title VARCHAR(10),
   forename VARCHAR(20), surname VARCHAR(20))"""
 conn.execute(tb_create)

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related