MYSQL.connector Python create table returns invalid sintax error

Question

I am creating table like in tutorial for mysql.connector via dict

    TABLES[name] = (
    f"CREATE TABLE `{name}` ("
    "  `product_id` int(11) NOT NULL AUTO_INCREMENT,"
    "  `Product name` varchar(255) NOT NULL,"
    "  `Seller name` varchar(255) NOT NULL,"
    "  `Image link` varchar(255)) NOT NULL,"
    "  `Original price` decimal(6,2) NOT NULL,"
    "  `Discount` varchar(10) NOT NULL,"
    "  `Description` text NOT NULL,"
    "  `Actual price` decimal(6,2) NOT NULL,"
    "  `product URL` varchar(255) NOT NULL,"
    "  `Rating score` decimal(1,15) NOT NULL,"
    "  `Reviews` int(5) NOT NULL,"
    "  `ItemId` int(11) NOT NULL,"
    "  `InStock` int(1) NOT NULL,"
    "  `ColorFamily` int(1) NOT NULL,"
    "  PRIMARY KEY (`product_id`)"
    ") ENGINE=InnoDB")


TABLES[colorname] = (
    f"CREATE TABLE `{colorname}` ("
    "  `item_no` int(11) NOT NULL AUTO_INCREMENT,"
    "  `product_id` int(11) NOT NULL,"
    "  `ColorName` varchar(255) NOT NULL,"
    "  `ColorPrice` decimal(6,2) NOT NULL,"
    "  PRIMARY KEY (`item_no`)"
    f"  CONSTRAINT `{colorname}_ibfk_1` FOREIGN KEY (`product_id`) "
    f"     REFERENCES `{name}` (`product_id`) ON DELETE CASCADE"
    ") ENGINE=InnoDB")

and I get

Creating table Women sunglasses: 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 'NOT NULL, Original price decimal(6,2) NOT NULL, Discount varchar(10) NOT N' at line 1

Creating table Women sunglasses colorFamily: 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 'CONSTRAINT Women sunglasses colorFamily_ibfk_1 FOREIGN KEY (product_id) ' at line 1

What is not correct there?

Table and column names should be in backticks, not single quotes. — Barmar, Jun 30 '20 at 05:26
You're missing a comma at the end of the `PRIMARY KEY` line. — Barmar, Jun 30 '20 at 05:42
For the second table - Yes. Thank you. But first table is OK with commas. And returns that error — Victor Semenov, Jun 30 '20 at 05:48
near 'NOT NULL, `Original price` decimal(6,2) NOT NULL, `Discount` varchar(10) NOT N' at line 1 — Victor Semenov, Jun 30 '20 at 05:49
No it isn't. `PRIMARY KEY (\`item_no\`)` should be `PRIMARY KEY (\`item_no\`),` — Barmar, Jun 30 '20 at 05:49
Is it really so hard to look for typos right before the place where it says the error is? — Barmar, Jun 30 '20 at 05:50
Your code would be much easier to read if you used a triple-quoted string to allow newlines, instead of starting a new string on each line. — Barmar, Jun 30 '20 at 05:51
oh!!!!! Thank you. I have spent so much time with those backticks that couldn't see anything. Extra ) !!!!!! Oh, so good it is working )))) Thanks again — Victor Semenov, Jun 30 '20 at 05:54
I used this template https://dev.mysql.com/doc/connector-python/en/connector-python-example-ddl.html ... Really new to MySQL. In python I always used Postgres and now client wants MySQL — Victor Semenov, Jun 30 '20 at 05:56
Postgres or MySQL makes no difference. You can use triple quotes to make a string that can continue over multiple lines.[ — Barmar, Jun 30 '20 at 05:56
Thanks for the hint. Maybe tutorial makers shall also receive such advice )) — Victor Semenov, Jun 30 '20 at 05:59

MYSQL.connector Python create table returns invalid sintax error

0 Answers0