将Python Dict插入MariaDB时出现错误1064

sc4hvdpw 于 2022-11-08 发布在 Python

关注(0)|答案(1)|浏览(115)

有一个structured_files变量的字典如下：

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']}

试图插入到mysql中的db代码如下;为创造;

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 )"

用于插入;

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

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

mycursor.execute(Q1)

它给出错误：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
为什么会这样呢？

mariadb

来源：https://stackoverflow.com/questions/72734645/inserting-python-dict-to-mariadb-giving-error-1064

1条答案

按热度按时间

9o685dep1#

好了，我测试了这段代码来做你想做的：

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()

小贴士：

在INSERT语句中显式写入列名。了解INSERT的语法：

INSERT INTO <table> (<columns>) VALUES (<values>)

你的id_key，dict中的键，不是一个列名，而是一个值，所以它属于VALUES子句。
这是插入一行的语法。一旦习惯了，就可以探索多行INSERT语法。

不要使用f字符串将变量插入INSERT字符串，因为这样会有SQL注入缺陷的风险。如我所示，将值作为占位符保留，然后将值作为第二个参数中的列表 * 分别 * 传递给cursor.execute()。

赞(0）回复(0）举报 2022-11-08

我来回答

将Python Dict插入MariaDB时出现错误1064

1条答案

相关问题

热门标签

最新问答