根据条件删除 [英] Removing on the basis of condition

问题描述

我试图创建一个查询循环，它执行从一个实例到下一个实例的交互式步骤.获取正确的数据后

我已经把它连接到数据库

<预><代码>

我能够运行，但我想根据数据类型应用这些案例，如果 COL_NAMES 数据类型是 varchar 则 ''，如果 COL_NAME 数据类型是 float 或 int，则将其替换为 0，对于日期时间数据类型将其替换为1880-10-10现在我只能在数据类型上应用空白:

 a = ','.join(f"[{y}]=isnull([{y}], '')" for y in COL_NAMES)一种

我的查询就像

 z = f"[UPDATE ABC_A SET {a}]

解决方案

您已经在循环遍历 SELECT 的结果，因此无需将 DATA_TYPE 存储在单独的 list 中，您只需存储COLUMN_NAME 及其在 dict 中的相应默认值.然后，您可以将 [column_name]=isnull([column_name, ... 片段粘合在一起并将它们插入到您的 UPDATE 语句中:

sql = """\从 INFORMATION_SCHEMA.COLUMNS 中选择 COLUMN_NAME、DATA_TYPEWHERE TABLE_NAME = 'ABC_A'"""column_defaults = {}对于 crsr.execute(sql) 中的行:如果 ['varchar', 'nvarchar'] 中的 row.DATA_TYPE:default_value = "''"['int', 'float'] 中的 elif row.DATA_TYPE:默认值=0"['date', 'datetime2'] 中的 elif row.DATA_TYPE:default_value = "'1880-10-10'"别的:引发 ValueError(f"未处理的列类型:{row.DATA_TYPE}")column_defaults[row.COLUMN_NAME] = default_values = ', '.join([f"[{col}]=isnull([{col}], {column_defaults[col]})" for col in column_defaults])印刷)# [Date]=isnull([Date], '1880-10-10'), [NAME]=isnull([NAME], ''), [FileNo]=isnull([FileNo], 0)sql = f"更新 ABC_A SET {s}"打印(SQL)# UPDATE ABC_A SET [Date]=isnull([Date], '1880-10-10'), [NAME]=isnull([NAME], ''), [FileNo]=isnull([FileNo], 0)

这只是一个示例，因此您可能需要向if/elif/else 块添加更多列类型.

I was trying to create a query loop, which does interactive steps from one instance to the next. After fetching the right data

I have connected it with database

I am able to run but I want to apply the cases on basis of datypes that if the COL_NAMES datatype is varchar then '' and if the COL_NAME datatype is float or int then replace it with 0 and for datetime datatye replace it with 1880-10-10 now I am able to apply only on blank on datatypes:

  a = ','.join(f"[{y}]=isnull([{y}], '')" for y in COL_NAMES)
  a

Mine query is like

  z = f"[UPDATE ABC_A SET {a}]

解决方案

You're already looping through the results from the SELECT, so instead of storing DATA_TYPE in a separate list you could just store the COLUMN_NAME and its corresponding default value in a dict. You could then glue together the [column_name]=isnull([column_name, ... fragments and insert them into your UPDATE statement:

sql = """\
SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'ABC_A'"""
column_defaults = {}
for row in crsr.execute(sql):
    if row.DATA_TYPE in ['varchar', 'nvarchar']:
        default_value = "''"
    elif row.DATA_TYPE in ['int', 'float']:
        default_value = "0"
    elif row.DATA_TYPE in ['date', 'datetime2']:
        default_value = "'1880-10-10'"
    else:
        raise ValueError(f"Unhandled column type: {row.DATA_TYPE}")
    column_defaults[row.COLUMN_NAME] = default_value
s = ', '.join([f"[{col}]=isnull([{col}], {column_defaults[col]})" for col in column_defaults])
print(s)
# [Date]=isnull([Date], '1880-10-10'), [NAME]=isnull([NAME], ''), [FileNo]=isnull([FileNo], 0)
sql = f"UPDATE ABC_A SET {s}"
print(sql)
# UPDATE ABC_A SET [Date]=isnull([Date], '1880-10-10'), [NAME]=isnull([NAME], ''), [FileNo]=isnull([FileNo], 0)

This is just an example, so you may need to add more column types to theif/elif/else block.

这篇关于根据条件删除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！