Python

Create MySQL Table using Python Taking Inputs from User

Nissaar

Dec 14, 2019 • 2 min read

We will now 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

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