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. def
ines 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