mysql 参数%s_在使用pymysql对mysql进行操作时,使用%s给excute传入参数时出错

在使用pymysql对mysql进行操作时,使用%s给excute传入参数时出错,错误代码如下:

table="huxing_table"

key="house_structure_page_url"

value="test"

cursor=db.cursor()

cursor.execute("INSERT INTO %s (%s) VALUES(%s)",(table,key,value))

db.commit()

cursor.close()

错误提示为:

Traceback (most recent call last):

File "/Users/huangjing/Library/Python/3.5/lib/python/site-packages/pymysql/cursors.py", line 112, in execute

result = self._query(query)

File "/Users/huangjing/Library/Python/3.5/lib/python/site-packages/pymysql/cursors.py", line 230, in _query

conn.query(q)

File "/Users/huangjing/Library/Python/3.5/lib/python/site-packages/pymysql/connections.py", line 607, in query

self._affected_rows = self._read_query_result()

File "/Users/huangjing/Library/Python/3.5/lib/python/site-packages/pymysql/connections.py", line 691, in _read_query_result

result.read()

File "/Users/huangjing/Library/Python/3.5/lib/python/site-packages/pymysql/connections.py", line 869, in read

self.first_packet = self.connection.read_packet()

File "/Users/huangjing/Library/Python/3.5/lib/python/site-packages/pymysql/connections.py", line 686, in read_packet

packet.check_error()

File "/Users/huangjing/Library/Python/3.5/lib/python/site-packages/pymysql/connections.py", line 328, in check_error

raise_mysql_exception(self.__data)

File "/Users/huangjing/Library/Python/3.5/lib/python/site-packages/pymysql/err.py", line 142, in raise_mysql_exception

_check_mysql_exception(errinfo)

File "/Users/huangjing/Library/Python/3.5/lib/python/site-packages/pymysql/err.py", line 135, in _check_mysql_exception

raise errorclass(errno,errorvalue)

pymysql.err.ProgrammingError: (1064, "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 ''huxing_table' ('house_structure_page_url') VALUES('test')' at line 1")

During handling of the above exception, another exception occurred:

Traceback (most recent call last):

File "/Users/huangjing/downHouseInfo/MainF.py", line 238, in

cursor.execute("INSERT INTO %s (%s) VALUES(%s)",(table,key,value))

File "/Users/huangjing/Library/Python/3.5/lib/python/site-packages/pymysql/cursors.py", line 117, in execute

self.errorhandler(self, exc, value)

File "/Users/huangjing/Library/Python/3.5/lib/python/site-packages/pymysql/connections.py", line 189, in defaulterrorhandler

raise errorclass(errorvalue)

pymysql.err.ProgrammingError: (1064, "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 ''huxing_table' ('house_structure_page_url') VALUES('test')' at line 1")

Exception ignored in: >

Traceback (most recent call last):

File "/Users/huangjing/Library/Python/3.5/lib/python/site-packages/pymysql/cursors.py", line 41, in __del__

File "/Users/huangjing/Library/Python/3.5/lib/python/site-packages/pymysql/cursors.py", line 47, in close

ReferenceError: weakly-referenced object no longer exists

但是,尝试执行

cursor.execute("INSERT INTO huxing_table (house_structure_page_url) VALUES(%s)",(value))

时,没有错误提示。

在错误提示第31行发现,执行的mysql语句中用%s替换的参数外加上了单引号。

''huxing_table' ('house_structure_page_url') VALUES('test')'

在mysql命令行终端进行测试,执行语句

mysql> insert into huxing_table (`house_structure_page_url`) values("test");

Query OK, 1 row affected (0.00 sec)

没有错误提示。而执行

mysql> insert into huxing_table ('house_structure_page_url') values("test");

ERROR 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 ''house_structure_page_url') values("test")' at line 1

则有错误提示。再进行验证

mysql> insert into huxing_table (house_structure_page_url) values('test');

Query OK, 1 row affected (0.00 sec)

不出错。

mysql> insert into 'huxing_table' (house_structure_page_url) values("test");

ERROR 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 ''huxing_table' (house_structure_page_url) values("test")' at line 1

出错,说明在mysql的insert语句中表名和列名外都不能加单引号,而值则可以加单引号。

就直接写语句好了。

最后的解决办法是插入一条数据写一条sql语句。

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
使用 HTML 中的 GET 方法传递数据到 Python 后端,你可以使用 `pymysql` 库来连接 MySQL 数据库并将获取的数据插入到数据库中。以下是一个示例代码: ```python from flask import Flask, request import pymysql app = Flask(__name__) @app.route('/save_data', methods=['GET']) def save_data(): # 获取 GET 请求中的参数 param1 = request.args.get('param1') param2 = request.args.get('param2') # 连接 MySQL 数据库 connection = pymysql.connect( host='your_host', user='your_user', password='your_password', database='your_database' ) try: with connection.cursor() as cursor: # 执行插入数据的 SQL 语句 sql = "INSERT INTO your_table (column1, column2) VALUES (%s, %s)" cursor.execute(sql, (param1, param2)) # 提交事务 connection.commit() return 'Data saved successfully!' except Exception as e: # 发生错误回滚 connection.rollback() return 'Error occurred: ' + str(e) finally: # 关闭数据库连接 connection.close() if __name__ == '__main__': app.run() ``` 在上面的示例中,我们使用 Flask 框架创建一个简单的 Web 应用。`save_data` 函数处理 `/save_data` 路径的 GET 请求,并从请求中获取参数 `param1` 和 `param2`。然后,我们使用 `pymysql` 连接到 MySQL 数据库,并执行插入数据的 SQL 语句将参数值插入到数据库表中。 请注意,你需要根据你的实际情况修改示例代码中的数据库连接信息、表名以及列名。确保在运行代码之前安装 `flask` 和 `pymysql` 库,可以使用 `pip install flask pymysql` 命令进行安装。

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值