Click here to Skip to main content
15,442,578 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I am a newbie to Sql and database concepts. so I am trying to make a program which does the basic tasks done by replacing the idea of entering commands by using functions in MySQL( like creating a table in database, adding records, modifying table etc). I now want to take input from user which include column name, its datatype, its size so that user can decide the basic structure of a new column.

So , I came across something called string formatting which I used a bit to enter variables into the Sql commands. so I tried using this concept in this case. But it gives syntax error. Can somebody suggest me a solution to this and tell me my mistake.

I apologize for my bad grammar

Thanks

here is the code

What I have tried:

##importing mysql connector
import mysql.connector


## Creating connection object
mydb = mysql.connector.connect(
    host = "localhost",
    user = "root",
    password = "milkshake#343",
    database = 'practice'
)
## defining a cursor object

cursor_obj = mydb.cursor()

##creating a table (uncommnent to edit and execute)
def table_create():
    cursor_obj.execute("CREATE TABLE test3(name VARCHAR(50),ageINT unsigned,person_id int PRIMARY KEY AUTO_INCREMENT)")

 


## Describing the table (uncommnent to edit and execute)
def table_desc():
    cursor_obj.execute("describe test3")
    for x in cursor_obj:
        print(x)


##creating a function to enter values into the table 
def inputer():
    #taking records from user to enter in the table
    name = input("please enter the name to enter into the table:")
    age = int(input("please enter the age to enter into the table:"))

    #Entering values into the table

    cursor_obj.execute('insert into test3 values(%s,%s)',(name,age))#you may change table name here if you want

##creating a comitt option
def commit():
    mydb.commit()

##function to display the whole content of the table
def whole_table():
    cursor_obj.execute("select * from test3")
    for x in cursor_obj:
        print(x)
##adding column into the table
def column_add():
    new_column = input("Enter the name of the new column:")
    data_type = input("Enter the data type of new column:")
    size = int(input("Enter the size of the new column:"))
    cursor_obj.execute("alter table test3 add column %s %s(%s)",(new_column,data_type,size))

column_add()
commit()
Python
<pre lang="Python">
Posted
Updated 8-Aug-22 2:54am

1 solution

Look at your CREATE statement:
CREATE TABLE test3(name VARCHAR(50),ageINT unsigned ...

There is a space missing between age and INT.

Further down you have:
Python
cursor_obj.execute("alter table test3 add column %s %s(%s)",(new_column,data_type,size))

Which should have .format instead of the comma at the end of the format string. See 7. Input and Output — Python 3.10.6 documentation[^].
 
Share this answer
 
Comments
no name Aug2022 8-Aug-22 9:51am    
thanks for the answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900