Inserting Python Dict to MariaDB giving Error 1064-CodePudding

Having a dict of structured_files variable as below;

structured_files = {'158795_1635509614542_20211029151335135_27.1943_38.4319.jpg': ['158795', '1635509614542', '20211029151335135', '27.1943', '38.4319'], '2773170_1635255851988_20211026164412576_27.1836_38.4777.jpg': ['2773170', '1635255851988', '20211026164412576', '27.1836', '38.4777'], '2777171_1635330525915_20211027132846547_27.1875_38.4720.jpg': ['2777171', '1635330525915', '20211027132846547', '27.1875', '38.4720'], '2779392_1635150026780_20211025112027410_27.1627_38.4724.jpg': ['2779392', '1635150026780', '20211025112027410', '27.1627', '38.4724'], '3724797_1634893471399_20211022120432057_27.1334_38.5426.jpg': ['3724797', '1634893471399', '20211022120432057', '27.1334', '38.5426'], '5218186_1635161250087_20211025142730572_27.0694_38.4943.jpg': ['5218186', '1635161250087', '20211025142730572', '27.0694', '38.4943'], '5347212_1634987405913_20211023141006566_27.0454_38.5138.jpg': ['5347212', '1634987405913', '20211023141006566', '27.0454', '38.5138'], '8174846_1635335281021_20211027144801672_27.1039_38.3572.jpg': ['8174846', '1635335281021', '20211027144801672', '27.1039', '38.3572'], '9179383_1635321573332_20211027105933968_27.1251_38.3576.jpg': ['9179383', '1635321573332', '20211027105933968', '27.1251', '38.3576']}

Trying to insert in to mysql db code is follows; For Creation;

Qcreate1 = "CREATE TABLE data_11 (id_key LONGTEXT NOT NULL, name VARCHAR(50) NOT NULL, date VARCHAR(50) NOT NULL, sample VARCHAR(50) NOT NULL, coord_y VARCHAR(50) NOT NULL, coord_x VARCHAR(50) NOT NULL, created datetime NOT NULL, key_id int PRIMARY KEY NOT NULL AUTO_INCREMENT )"

For Inserting;

cols = list(structured_files.keys())
vals = list(structured_files.values())

Q1 = f"INSERT INTO data_11 {cols} values {vals}"

mycursor.execute(Q1)

It gives the error : mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '['158795_1635509614542_20211029151335135_27.1943_38.4319.jpg', '2773170_16352...' at line 1

Why is that ?

CodePudding user response：

Okay, I tested this code to do what you want:

Q1 = "INSERT INTO data_11 (id_key, name, date, sample, coord_y, coord_x) VALUES (%s, %s, %s, %s, %s, %s)"

for id_key, values in structured_files.items():
    cursor.execute(Q1, [id_key]   values)

cnx.commit()

Tips:

Write the column names explicitly in the INSERT statement. Learn the syntax for INSERT:
```
INSERT INTO <table> (<columns>) VALUES (<values>)
```
Your id_key, the key in the dict, isn't a column name. It's one of the values, so it belongs in the VALUES clause.

This is the syntax for inserting one row. Once you get used to this, you can explore multi-row INSERT syntax.
Don't use f-strings to interpolate variables into the INSERT string, because that risks SQL injection flaws. Leave the values as placeholders as I showed, then pass the values separately as a list in the second argument to cursor.execute().