by In this tutorial we will learn how to take Table Name, Column Names, Methods and Datatypes as input from the user.
I will use the SQL statements: CREATE TABLE and ALTER TABLE TABLE
Step One: Connect to the Database
Follow this link if you don’t have any idea how to connect to the Database. I have explained as much as I can.
Step Two: 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
Step Three: SQL Statement and Execution
1. sql = "CREATE TABLE " + table_name + " (" +" "+ primary_key +" "+ datatype_of_primary_key +" "+ primary_key_method +" "+ "PRIMARY KEY);"
2.mycursor.execute(sql)
3.print("Table "+table_name+"has been successfully created with primary key "+primary_key)
1. is the SQL statement to be executed. It consists of the SQL codes concatenated with the variables to which user inputs have been assigned.
2. executes the SQL statement assigned to the variable sql
3. is an output which provides the user a confirmation that the TABLE has been successfully created
Step Four: Create a function to ADD COLUMN
1. def add_column(table_name):
2. column_name = input("Name of column: ")
3. data_type = input("Datatype: ")
4. sql1 = "ALTER TABLE " + table_name + " ADD " +column_name+" "+data_type+" ;"
5. mycursor.execute(sql1)
6. print(column_name + " Added Successfully")
1. defines the Function: add_column
2. Name of the column is taken as input and assigned to variable: column_name
3. Datatype of the column is taken as input and assigned to variable: data_type
4. SQL codes concatenated with the variables to which user inputs have been assigned.
5. executes the SQL statement assigned to the variable sql
6. is an output which provides the user a confirmation that the COLUMN has been successfully added
Step Five: How many Columns to create and Function Execution
1.column_no = int(input("How many columns to be created ?"))
2.for i in range(column_no):
3. add_column(table_name)
1. asks the user how many columns he needs to input.
2. a for loop which executes the function add_column for the number of times taken from the user.
3.add_column function is executed with table_name as parameter
Step Six (optional): Checks if user needs more column and how many
1.check = input("Do you need more columns ? (y/n): "
2.if check == "y" or check == "Y":
3. more = int(input("How many more columns do you need ? : "))
4. for i in range(more):
5. add_column(table_name)
6.else :
7. print("Columns added Successfully."
1. ask the user for a confirmation if he needs to add more columns or not.
2. is the condition statement which compares the user’s input to y or Y which have been already defined.
3. takes how many more columns the user needs.
4. a for loop to call the function: add_column(table_name_) for the number of times input by the user.
5. calls the function
6. if the user’s input was not y or Y do the next statement.
7. Outputs Columns added Successfully
It should now look like this:
