We will now learn how to take
Table Name, Column Names, Methods and
Datatypes as input from the user.
I will use the
CREATE TABLE and
ALTER TABLE TABLE
Connect to the Database
Follow this link if you don't have any idea how to connect to the Database.
Taking Inputs from User to Create Table
We will start by naming the variables and use the
input() method with a display message to prompt the user.
table_name = input("Name of Table: ") primary_key = input("Name of Primary Key: ") datatype_of_primary_key = input("Datatype of Primary Key: ") primary_key_method = input("Primary Key method to be used (AUTO_INCREMENT for example): ")
Each of these lines on code takes
input from the user by prompting with their respective messages and is the
assigned to their respective
variables which are quite self-explanatory
SQL Statement and Execution
sql = "CREATE TABLE " + table_name + " (" +" "+ primary_key +" "+ datatype_of_primary_key +" "+ primary_key_method +" "+ "PRIMARY KEY);" # SQL statement to be executed mycursor.execute(sql) # Executes SQL Statement assigned to the variable "sql" print("Table "+table_name+"has been successfully created with primary key "+primary_key) # Confirmation that Tables have been created
Create a function to ADD COLUMN
def add_column(table_name): # defines the Function: add_column column_name = input("Name of column: ") # Name of the column is taken as input and assigned to variable: column_name data_type = input("Datatype: ") # Datatype of the column is taken as input and assigned to variable: data_type sql1 = "ALTER TABLE " + table_name + " ADD " +column_name+" "+data_type+" ;" # SQL codes concatenated with the variables to which user inputs have been assigned. mycursor.execute(sql1) # executes the SQL statement assigned to the variable sql print(column_name + " Added Successfully") # is an output which provides the user a confirmation that the COLUMN has been successfully added
How many Columns to create and Function Execution
column_no = int(input("How many columns to be created ?")) # Ask the user how many columns needed as input for i in range(column_no): # For loop to execute function " add_column for the number of columns input by the user add_column(table_name) # Executes add_column function with table_name as parameter
Checks if user needs more column and how many (optional)
check = input("Do you need more columns ? (y/n): ") # Ask user for confirmation if more columns are needed if check == "y" or check == "Y": # Check if user has input yor Y to proceed more = int(input("How many more columns do you need ? : ")) # how may more columns user needs for i in range(more): # For loop to execute add_column function the number of times defined by user above add_column(table_name) # Executes function add_column() print("Columns added Successfully.") else : # If output is not y or Y proceed to next step print("No more columns Added") # Outputs no more columns added if the user's input was anything but y/Y