pythonpython-3.xmysql-pythonmysql-connector-python

Why does MySQL return error on creating a table?

I've been trying to create a table with user input name but I get an error no matter how I write the query.

right now query is like this:

query=("CREATE TABLE "+t+" ( NAME VARCHAR , DISTANCE VARCHAR , PRICE VARCHAR , LOCATION VARCHAR)")

and after running the whole code it returns:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ', DISTANCE VARCHAR , PRICE VARCHAR , LOCATION VARCHAR)' at line 1

I also tried other ways like using %s:

c.execute("CREATE TABLE %s (NAME VARCHAR, DISTANCE VARCHAR, PRICE VARCHAR, LOCATION VARCHAR)"%(t))

or {} on replacing the table name:

c.execute("CREATE TABLE {} (NAME VARCHAR, DISTANCE VARCHAR, PRICE VARCHAR, LOCATION VARCHAR)".format(t)

All codes return the same error. Also tried changing columns names and datatypes. I'd be thankful if somebody could help me.

Solution

  • First:

    Be sure your variable t is a string.

    Second:

    You have to declare a size for your VARCHAR, so a max width for your string. Try:

    query=("CREATE TABLE "+t+" ( NAME VARCHAR(90) , DISTANCE VARCHAR(90) , PRICE varchar(90) , LOCATION VARCHAR(90))")