mariadb.SQL语法错误:INSERT INTO issue with (%s/?)<

Question

我使用python 3.9我的sql server是10.3.29-MariaDB-0+deb10u1 Raspbian 10

我想用python在我的sql数据库中插入一个新的行。

# python3!
import mariadb
mydb = mariadb.connect(
host="***(just wanted to exclude the domain)",
port=25555,
user="test",
password="test",
database="test"
)
cursor = mydb.cursor()
first = "test5"
cursor.execute("INSERT INTO contacts (firstname) VALUES (%s)",
first)
mydb.commit()
cursor.close()
mydb.close()

，我收到这个错误:

Traceback (most recent call last):
File "E:contact_testcontactstests.py", line 14, in <module>
cursor.execute("INSERT INTO contacts (firstname) VALUES (%s)",
mariadb.ProgrammingError: 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 '%s)' at line 1

我试着在(%s)和(?)之间变换，但是没有区别。

当我尝试在数据库中直接使用相同的命令时:

MariaDB [test]> INSERT INTO contacts (firstname) VALUES ("test5");
Query OK, 1 row affected (0.009 sec)

我变成了这样的输出:

MariaDB [test]> SELECT * FROM contacts;
+----+-----------+------------+---------+----------+----------+--------------+--------------+-------+
| id | firstname | secondname | sirname | landline | mobile   | mailaddress1 | mailaddress2 | group |
+----+-----------+------------+---------+----------+----------+--------------+--------------+-------+
|  . | ...       |            |         |          |          |              |              |     . |
|  6 | test5     |            |         |          |          |              |              |     0 |
+----+-----------+------------+---------+----------+----------+--------------+--------------+-------+
5 rows in set (0.001 sec)

so it work .

更新:

我也改变了:

sql = "INSERT INTO contacts (firstname) VALUES (%s)"
val = ("test5")
cursor.execute(sql, val)

输出:

Traceback (most recent call last):
File "E:codingcontactcontacts_testv1.0test.py", line 14, in <module>
cursor.execute("INSERT INTO contacts (firstname) VALUES (%s)",
mariadb.ProgrammingError: 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 '%s)' at line 1

:

first = "test5"
cursor.execute("INSERT INTO contacts (firstname) VALUES (%s)",
(first))

输出:

Traceback (most recent call last):
File "E:codingcontactcontacts_testv1.0test.py", line 15, in <module>
cursor.execute("INSERT INTO contacts (firstname) VALUES (%s)",
mariadb.ProgrammingError: 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 '%s)' at line 1

Answer 1

已解决:

根据@balderman的建议，我添加了{，}:

first = "test7"
cursor.execute("INSERT INTO contacts (firstname) VALUES (%s)",
(first,))

Python输出:

Process finished with exit code 0

sql输出:

MariaDB [test]> SELECT * FROM contacts WHERE firstname = 'test7';
+----+-----------+------------+---------+----------+--------+--------------+--------------+-------+
| id | firstname | secondname | sirname | landline | mobile | mailaddress1 | mailaddress2 | group |
+----+-----------+------------+---------+----------+--------+--------------+--------------+-------+
|  8 | test7     |            |         |          |        |              |              |     0 |
+----+-----------+------------+---------+----------+--------+--------------+--------------+-------+
1 row in set (0.001 sec)

所以我的问题解决了。谢谢大家的帮助!: D