@I am translating input from a user into a MySQL command but it says i have the syntax wrong and Idk how I can fix it. Essentially I made a program in python (not a problem with the python code) that has the ability to create MySQL tables and translates user input into a MySQL command to create the table, but I think i have worded the command wrong and need someone to help explain what I did wrong and how I can fix it or another way to do it (no new python packages just different MySQL command).
#Error
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 ''c1', 'c2') VALUES [('r1', 'r2')]' at line 1
#here is what i was collecting in my attempt to fix the bug
'''
ROW SYNTAX: [('r1', 'r2')]
COLUMN SYNTAX: ('c1', 'c2')
MySQL COMMAND: CREATE TABLE test ('c1', 'c2') VALUES [('r1', 'r2')]
'''
#just the values none of the ROW SYNTAX or COLUMN SYNTAX is actually put into the mysql command
I was expecting it to create the table with:
2 columns: c1 and c2
1 row with 2 values: r1 and r2
Yet as I have explained above... it doesn't. I don't work with MySQL just python really so I'm probably missing something really easy or big that I just don't understand.
i've seen some example syntax similar to this but again I don't understand it.
'''
ROW SYNTAX: [('r1', 'r3'), ('r2', 'r4')]
COLUMN SYNTAX TCOLN: (c1, c2)
COLUMN SYNTAX COLNAMES: (c1 VCHAR(255), c2 VCHAR(255))
CREATE TABLE COMMAND: CREATE TABLE test (c1 VCHAR(255), c2 VCHAR(255));
INSERT TABLE COMMAND: INSERT INTO test (c1, c2) VALUES [('r1', 'r3'), ('r2', 'r4')];
'''
'''
Traceback (most recent call last):
line 472, in cmd_query
raw_as_string=raw_as_string)
_mysql_connector.MySQLInterfaceError: 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 'VCHAR(255), c2 VCHAR(255))' at line 1
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
line 558, in ctable
cursor.execute(sqlCol) #slqCol is CREATE TABLE test (c1 VCHAR(255), c2 VCHAR(255));
line 266, in execute
raw_as_string=self._raw_as_string)
line 475, in cmd_query
sqlstate=exc.sqlstate)
mysql.connector.errors.ProgrammingError: 1064 (42000): 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 'VCHAR(255), c2 VCHAR(255))' at line 1
'''
Thanks to your suggestions i am using VARCHAR now but i still seem to be running into close to the same problem
Thanks to @Martin for explaining what varchar is ect, it also turns out VCHAR is not the same as VARCHAR (thanks @robsiemb). Now the table is creating BUT not putting the data values in? (this might be a python problem, hope not)
'''
INSERT TABLE COMMAND: INSERT INTO test (c1, c2) VALUES ('r1', 'r2'),('rr1', 'rr2');
'''
# LATEST UPDATE
New Error when inserting values
```python
#CREATE TABLE COMMAND: CREATE TABLE test (c1 VARCHAR(255), c2 VARCHAR(255));
#INSERT TABLE COMMAND: INSERT INTO test (c1, c2) VALUES (r1,r3),(r2,r4);
'''
Traceback (most recent call last):
line 472, in cmd_query
raw_as_string=raw_as_string)
_mysql_connector.MySQLInterfaceError: Unknown column 'r1' in 'field list'
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
line 563, in ctable
cursor.execute(sqlVal)
line 266, in execute
raw_as_string=self._raw_as_string)
line 475, in cmd_query
sqlstate=exc.sqlstate)
mysql.connector.errors.ProgrammingError: 1054 (42S22): Unknown column 'r1' in 'field list'
'''
Python problem, thanks for answering my question!
The problem with what you are doing is in this statement:
CREATE TABLE test ('c1', 'c2') VALUES [('r1', 'r2')]
This is not a valid CREATE TABLE statement.
It looks like you're trying to create the table, and then insert some values into it. Is that the case?
If so, this should be broken into two statements. First create the table:
CREATE TABLE test (c1 DATATYPE, c2 DATATYPE);
Replace DATATYPE with the type of the column you are creating (i.e. INT, VARCHAR(10), etc.).
Second, insert your values into the table:
INSERT INTO test (c1, c2) VALUES (r1, r2);