sqlalchemy.exc.ProgrammingError: (pymysql.err.ProgrammingError)

最新推荐文章于 2025-04-30 11:01:36 发布
原创 最新推荐文章于 2025-04-30 11:01:36 发布 · 6.6k 阅读 · 0 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。

本文探讨了在执行SQL更新语句时如何避免SQL注入风险，通过使用pymysql库的escape_string方法来确保字符串参数的安全性，展示了正确的SQL参数化方法。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

 

源代码：

sql = """
              UPDATE t_datav SET info ="%s" WHERE category ="mock"
          """ % (res)

改后：

sql="""
    UPDATE t_datav SET info ="%s" WHERE category ="mock"
"""%(pymysql.escape_string(res) )
sqlalchemy搭配mysqlconnector和pymysql时遇到的问题记录
zhengyajun_email的博客
03-11 3218
使用sqlachemy + mysqlconnector： Traceback (most recent call last): File "D:\ProgramData\Anaconda3\lib\site-packages\mysql\connector\conversion.py", line 179, in to_mysql return getattr(self, "_{0}...
sqlalchemy.exc.ProgrammingError: (pymysql.err.ProgrammingError) (1146, "Table 'fisher.user' doesn't
热门推荐
qq_45048148的博客
02-04 1万+
问题 我在做用户注册界面，前台表单传入注册信息保存至数据库时，网页报以下错误： sqlalchemy.exc.ProgrammingError sqlalchemy.exc.ProgrammingError: (pymysql.err.ProgrammingError) (1146, "Table 'fisher.user' doesn't exist") [SQL: INSERT INTO us...
python 将dataframe数据存入mysql出现sqlalchemy.exc.ProgrammingError: (pymysql.err.ProgrammingError) (1064, “
qq_36116627的博客
02-28 1043
将dataframe数据使用to_excel时存入excel表时没出错，使用to_sql时出现sqlalchemy.exc.ProgrammingError: (pymysql.err.ProgrammingError) (1064, "You have an error in your SQL syntax
解决sqlalchemy.exc.ProgrammingError: (pymysql.err.ProgrammingError) (1064, "You have an error in your
zy_whynot的博客
03-10 7824
报错如下： sqlalchemy.exc.ProgrammingError: (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 ...
"sqlalchemy.exc.programmingerror: (pymysql.err.programmingerror) (1146, \"tabl"
08-17
可以确定的是，该问题涉及SQLAlchemy和pymysql库中的一个编程错误。具体的问题可能是在使用SQLAlchemy进行数据库操作时，读取了一个不存在的表。 要解决这个问题，首先要确认数据库中是否存在一个名为“tabl”的表...
【已解决】python pymysql报错DataError (1265, Data truncated for column ‘num‘ at row 1)
SophiaSSS的博客
12-18 1万+
一、背景 拉取tushare财经数据到本地，再转换入数据库。 建表遇到这个报错：DataError (1265, Data truncated for column 'num' at row 1) 时间：20201216 部分执行代码如下： db = pymysql.connect(host,user,password,database) #获取游标对象 cursor = db.cursor() #创建数据库，如果数据库已经存在，注意主键不要重复，否则出错 try: curso...
生成的sql有问题： raise errorclass(errno, errval) sqlalchemy.exc.ProgrammingError: (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 '} END,\ncustomer = CASE\nWHEN sku = 'Z1D03B00AD32CE79A5163Z' THEN 158\nWHEN sku = '' at line 23") [SQL: UPDATE t_offer_info SET saler_score = CASE WHEN sku = %(key_0)s THEN %(saler_score_0)s WHEN sku = %(key_1)s THEN %(saler_score_1)s WHEN sku = %(key_2)s THEN %(saler_score_2)s WHEN sku = %(key_3)s THEN %(saler_score_3)s WHEN sku = %(key_4)s THEN %(saler_score_4)s WHEN sku = %(key_5)s THEN %(saler_score_5)s WHEN sku = %(key_6)s THEN %(saler_score_6)s WHEN sku = %(key_7)s THEN %(saler_score_7)s WHEN sku = %(key_8)s THEN %(saler_score_8)s WHEN sku = %(key_9)s THEN %(saler_score_9)s WHEN sku = %(key_10)s THEN %(saler_score_10)s WHEN sku = %(key_11)s THEN %(saler_score_11)s WHEN sku = %(key_12)s THEN %(saler_score_12)s WHEN sku = %(key_13)s THEN %(saler_score_13)s WHEN sku = %(key_14)s THEN %(saler_score_14)s WHEN sku = %(key_15)s THEN %(saler_score_15)s WHEN sku = %(key_16)s THEN %(saler_score_16)s WHEN sku = %(key_17)s THEN %(saler_score_17)s WHEN sku = %(key_18)s THEN %(saler_score_18)s WHEN sku = %(key_19)s THEN %(saler_score_19)s ELSE {col} END, customer = CASE WHEN sku = %(key_0)s THEN %(customer_0)s WHEN sku = %(key_1)s THEN %(customer_1)s WHEN sku = %(key_2)s THEN %(customer_2)s WHEN sku = %(key_3)s THEN %(customer_3)s WHEN sku = %(key_4)s THEN %(customer_4)s WHEN sku = %(key_5)s THEN %(customer_5)s WHEN sku = %(key_6)s THEN %(customer_6)s WHEN sku = %(key_7)s THEN %(customer_7)s WHEN sku = %(key_8)s THEN %(customer_8)s WHEN sku = %(key_9)s THEN %(customer_9)s WHEN sku = %(key_10)s THEN %(customer_10)s WHEN sku = %(key_11)s THEN %(customer_11)s WHEN sku = %(key_12)s THEN %(customer_12)s WHEN sku = %(key_13)s THEN %(customer_13)s WHEN sku = %(key_14)s THEN %(customer_14)s WHEN sku = %(key_15)s THEN %(customer_15)s WHEN sku = %(key_16)s THEN %(customer_16)s WHEN sku = %(key_17)s THEN %(customer_17)s WHEN sku = %(key_18)s THEN %(customer_18)s WHEN sku = %(key_19)s THEN %(customer_19)s ELSE {col} END WHERE sku IN (%(sku_0)s,%(sku_1)s,%(sku_2)s,%(sku_3)s,%(sku_4)s,%(sku_5)s,%(sku_6)s,%(sku_7)s,%(sku_8)s,%(sku_9)s,%(sku_10)s,%(sku_11)s,%(sku_12)s,%(sku_13)s,%(sku_14)s,%(sku_15)s,%(sku_16)s,%(sku_17)s,%(sku_18)s,%(sku_19)s)] [parameters: {'key_0': 'Z1D03B00AD32CE79A5163Z', 'saler_score_0': 4.8, 'key_1': 'Z8F10294E91F2D7D62E10Z', 'saler_score_1': 4.8, 'key_2': 'ZAF5F7EA26402545B78CCZ', 'saler_score_2': 4.8, 'key_3': 'Z779B4AF6E3266F5D91C2Z', 'saler_score_3': 4.8, 'key_4': 'Z826E2C5B1529CD23E9B5Z', 'saler_score_4': 4.8, 'key_5': 'Z26A665DE4787BA0AE7D4Z', 'saler_score_5': 4.8, 'key_6': 'ZD5CA810500167A3CBBCCZ', 'saler_score_6': 4.8, 'key_7': 'Z65E67086B1D75AF0BEE3Z', 'saler_score_7': 4.8, 'key_8': 'ZC41A881C97D6D10A6830Z', 'saler_score_8': 4.8, 'key_9': 'Z66099A00658818ECA0B2Z', 'saler_score_9': 4.8, 'key_10': 'Z9748D36000FEDE033C5EZ', 'saler_score_10': 4.9, 'key_11': 'Z1FDC2F9CE99B175E2C5AZ', 'saler_score_11': 4.8, 'key_12': 'Z7979354FEBF3D8CBEA5BZ', 'saler_score_12': 4.8, 'key_13': 'ZE2399C4D2FE10C0B30FDZ', 'saler_score_13': 4.8, 'key_14': 'Z50B6086CE8B0C774E5B3Z', 'saler_score_14': 4.8, 'key_15': 'N31985097A', 'saler_score_15': 4.8, 'key_16': 'Z3E03FE75F38DEFC3E762Z', 'saler_score_16': 4.9, 'key_17': 'N43636512A', 'saler_score_17': 4.8, 'key_18': 'N42962838A', 'saler_score_18': 4.9, 'key_19': 'Z2CD404C49F79F8137521Z', 'saler_score_19': 4.9, 'customer_0': 158, 'customer_1': 153, 'customer_2': 153, 'customer_3': 153, 'customer_4': 153, 'customer_5': 153, 'customer_6': 153, 'customer_7': 153, 'customer_8': 153, 'customer_9': 153, 'customer_10': 137, 'customer_11': 128, 'customer_12': 120, 'customer_13': 118, 'customer_14': 109, 'customer_15': 68, 'customer_16': 67, 'customer_17': 66, 'customer_18': 62, 'customer_19': 61, 'sku_0': 'Z1D03B00AD32CE79A5163Z', 'sku_1': 'Z8F10294E91F2D7D62E10Z', 'sku_2': 'ZAF5F7EA26402545B78CCZ', 'sku_3': 'Z779B4AF6E3266F5D91C2Z', 'sku_4': 'Z826E2C5B1529CD23E9B5Z', 'sku_5': 'Z26A665DE4787BA0AE7D4Z', 'sku_6': 'ZD5CA810500167A3CBBCCZ', 'sku_7': 'Z65E67086B1D75AF0BEE3Z', 'sku_8': 'ZC41A881C97D6D10A6830Z', 'sku_9': 'Z66099A00658818ECA0B2Z', 'sku_10': 'Z9748D36000FEDE033C5EZ', 'sku_11': 'Z1FDC2F9CE99B175E2C5AZ', 'sku_12': 'Z7979354FEBF3D8CBEA5BZ', 'sku_13': 'ZE2399C4D2FE10C0B30FDZ', 'sku_14': 'Z50B6086CE8B0C774E5B3Z', 'sku_15': 'N31985097A', 'sku_16': 'Z3E03FE75F38DEFC3E762Z', 'sku_17': 'N43636512A', 'sku_18': 'N42962838A', 'sku_19': 'Z2CD404C49F79F8137521Z'}] (Background on this error at: https://sqlalche.me/e/20/f405)
最新发布
06-07
一种方法是使用白名单验证，另一种是使用SQLAlchemy的`text`构造时使用`schema`对象，但这样比较复杂。考虑到我们已经在方法中使用了表名和列名作为输入参数，为了安全起见，我们可以通过验证列名是否在表结构中存在...
用什么指令启动项目呢？
05-11
sqlalchemy.exc.ProgrammingError: (pymysql.err.ProgrammingError) (1146, "Table 'todo_db.todos' doesn't exist") ``` 解决方法： ```python # 在database.py添加自动建表逻辑 Base.metadata.create_all(bind=...
Python中数据库操作报错的排查与修复
shejizuopin的博客
04-30 612
连接管理使用连接池（如SQLAlchemy的QueuePool）避免资源耗尽。操作完成后显式关闭连接（推荐with语句）。SQL安全禁止字符串拼接SQL，始终使用参数化查询。ORM操作时检查表名大小写与数据库一致。错误处理捕获特定异常（如DataError）而非通用Exception。使用日志分级记录错误（DEBUG/INFO/WARNING/ERROR）。性能监控配置MySQL慢查询日志，优化超过阈值的SQL。在Python代码中监控查询耗时，设置超时告警。
sqlalchemy.exc.ProgrammingError: (pymysql.err.ProgrammingError) (1064, “You have an error in your SQ
m0_61884675的博客
09-06 1786
sqlalchemy.exc.ProgrammingError: (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 'fromt_user' at line 1")
已解决sqlalchemy.exc.ProgrammingError: (pymssql._pymssql.ProgrammingError) (102, b“Incorrect syntax nea
努力让自己发光，对的人才能迎着光而来
04-24 1万+
已解决sqlalchemy.exc.ProgrammingError: (pymssql._pymssql.ProgrammingError) (102, b"Incorrect syntax near ‘\xef\xbc\x8c’.DB-Lib error message 20018, severity 15:\nGeneral SQL Server error: Check messages from the SQL Server\n")
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.DuplicateObject)错误解决方法
chenliang1038的博客
07-26 1298
flask enum 枚举
sqlalchemy.exc.ProgrammingError: (mysql.connector.errors.ProgrammingError) 1045 (28000)
我是小飞熊的博客
03-10 2003
报错： sqlalchemy.exc.ProgrammingError: (mysql.connector.errors.ProgrammingError) 1045 (28000): Access denied for user ‘root’@‘localhost’ (using password: NO) 如下图所示：
mysql 中的errors_求助各位大佬：一个关于 sqlalchemy 的报错(mysql.connector.errors.OperationalError)...
weixin_39815410的博客
01-27 392
直接先贴代码吧：for result in nminfo['scan'].values():if result['status']['state'] == "up":ip = result['addresses']['ipv4']host_os = "xxx"# 位置 1host_assets_id = db.session.query(HostAsset.host_id).filter(Host...
Python连接mysql数据库出错解决方案
weixin_30487317的博客
07-27 1537
最近在学习python使用connector连接MySQL时遇到错误: sqlalchemy.exc.ProgrammingError: (mysql.connector.errors.ProgrammingError) 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) (Backgroun...
已解决sqlalchemy.exc.ProgrammingError: (207, b“Invalid column name ‘DB_ID‘.DB-Lib error message 20018
weixin_45091564的博客
03-24 1530
已解决sqlalchemy.exc.ProgrammingError: (pymssql._pymssql.ProgrammingError) (207, b"Invalid column name 'DB_ID'.DB-Lib error message 20018, severity 16:\nGeneral SQL Server error: Check messages from the SQL Server\n")
sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) can‘t adapt type ‘BinaryExpression‘
m0_72639960的博客
02-21 765
sqlalchemy.exc.ProgrammingError: adapt type BinaryExpression
sqlalchemy.exc.operationalerror: (pymysql.err.operationalerror)
06-28
SQLAlchemy.exc.OperationalError: (pymysql.err.OperationalError) 是一个数据库操作错误，通常是由于数据库连接问题或语法错误引起的。这个错误可能会导致应用程序无法正常工作，需要及时解决。常见的解决方法包括...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值