目的：删除数据库各个系统his库里的测试soe与故障数据； 一、sql执行错误： 执行sql：SELECT * FROM SOE WHERE sDevCode = 012400000001 报错： [SQL Server]将 varchar 转换为数据类型 numeric 时出现算术溢出错误。 原因：查看表设计，sDevCode列的类型为varchar（字符串）， 修改：SELECT * FROM SOE WHERE sDevCode = '012400000001' 。 二、脚本；
-- coding:UTF-8 --
import os import pymssql import pprint sql_name = [ qqqqq ] print('程序正在启动') os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.UTF8' os.environ['NLS_LANG'] = 'AMERICAN_AMERICA.AL32UTF8' for SJK in sql_name: IP_HIS = SJK[0] # 获取数据库IP us_HIS = SJK[1] PW_HIS= SJK[2] HIS = SJK[3] print(HIS) conn = pymssql.connect(host=IP_HIS, user=us_HIS, password=PW_HIS, database=HIS) cur1 = conn.cursor() check_sql1 = "DELETE FROM SOE WHERE sDevCode = '012400000001'" cur1.execute(check_sql1) cur2 = conn.cursor() check_sql2 = "DELETE FROM Fault_Record WHERE LOWER(sLineName) LIKE '%cs%' or sLineName LIKE '%测试%' or sLineName LIKE '%模拟%'" cur2.execute(check_sql2) conn.close()
解析：主要在sql上， DELETE FROM Fault_Record WHERE LOWER(sLineName) LIKE '%cs%' or sLineName LIKE '%测试%' or sLineName LIKE '%模拟% 其中，模糊查询用like ，忽略大小写用lower或者upper都可以。
