使用Bash脚本构建SQL更新语句 [英] Building SQL update statements using a Bash script

问题描述

对于最近的项目，我需要遍历3列csv(ignore.csv)的行:

For a recent project I need to loop through rows of a csv (ignore.csv) with 3 columns:

acctnum, errcode, date←依次…

(列命名在这里无关紧要，仅用于上下文)并使用这些变量来构建SQL语句.

(Column naming doesn't matter here, its just for context) and use those variables to build SQL statements.

CSV可能有500行，也可能有20k.我的SQL语句应如下所示:

The CSV may have 500 rows or it might have 20k. My SQL statement should look like this:

UPDATE O.ACCT_ERR SET REC_ACTV_IND='T'
  WHERE BUS_DT='20200603' and ERR_CD='R4442' AND ACCT_KEY
  IN (
    SELECT ACCT_KEY FROM O.ACCT
      WHERE ACCT_SRCH_NBR='10100000011'
  );

目前，我在Bash中的草率示例是:

Currently my sloppy example in Bash is this:

while IFS=, read -r field1 field2 field3;
do
   echo "UPDATE ODS.PERF_ACCT_ERR_DTL SET REC_ACTV_IND='T' WHERE BUS_DT='$field3' and ETL_ERR_CD='$field2' AND ACCT_KEY in (SELECT ACCT_KEY FROM ODS.ACCT_PORTFOLIO WHERE ACCT_SRCH_NBR='$field1');" > sqlfile.txt
done < ignore.csv

sqlfile.txt的输出中已验证的行仅适用于其中一行.

Which works for just one row as verified in output of sqlfile.txt.

但是我如何遍历它以打印CSV的所有行?

But how can I iterate over it to print all rows of the CSV?

我仍然讨厌循环逻辑-幸运的是在这方面成为产品支持人员.

I am still awful at looping logic - lucky to be a Prod Support guy in that aspect.

我们非常感谢您的帮助.

Any help is much appreciated.

推荐答案

如 Barmar指出的:

每次在循环中回显时，您都将覆盖文件.

You're overwriting the file every time you echo inside the loop.

您可以通过在CSV的字段分隔符中包含空格来做到这一点.

You can do it this way with including space into the field separators of the CSV.

要特别注意:

• Shell无法可靠地解析CSV字段，可能会包含引号，转义引号.您应该考虑使用能够正确解析CSV并返回值作为参数或null分隔字段(如csvtool)的命令.
• 如果您的SQL数据库引擎了解PREPARE语句，则对PREPARE SQL UPDATE请求并向其提供参数会更安全.您需要在循环之前准备请求，然后提供参数并在循环中执行准备好的语句.

• Shell cannot reliably parse CSV fields witch may include quotes, escaping of quotes. You should consider using a command able to parse CSV correctly and return values as arguments or null delimited fields like csvtool.
• If your SQL database engine knows about PREPARE statements, it would be safer to PREPARE the SQL UPDATE request, and feed it with arguments. You'd prepare the request before the loop, and then feed the arguments and execute the prepared statement in the loop.

#!/usr/bin/env bash

while IFS=', ' read -r acctnum errcode date; do

  cat <<SQL
UPDATE ODS.PERF_ACCT_ERR_DTL SET REC_ACTV_IND='T'
  WHERE BUS_DT='$date' and ETL_ERR_CD='$errcode' AND ACCT_KEY
  IN (
    SELECT ACCT_KEY FROM ODS.ACCT_PORTFOLIO
      WHERE ACCT_SRCH_NBR='$acctnum');
SQL
done < ignore.csv > sqlfile.txt

使用csvtool解析exclude.csv并将其转换为SQL查询:

excludeCSV2sql:

Parsing and converting your exclude.csv into SQL queries using csvtool:

excludeCSV2sql:

#!/usr/bin/env bash

# This script uses csvtool to parse exclude.csv CSV data from stdin
# and output SQL queries to stdout

# Convert arguments from csvtool call, into an SQL query
to_sql ()
{
  # Double single-quote for SQL string values if any
  local -- \
    acct_num="${1//\'/\'\'}" \
    err_code="${2//\'/\'\'}" \
    date="${3//\'/\'\'}"

  cat <<SQL
UPDATE ODS.PERF_ACCT_ERR_DTL SET REC_ACTV_IND='T'
  WHERE BUS_DT='$date' AND ETL_ERR_CD='$err_code' AND ACCT_KEY
  IN (
    SELECT ACCT_KEY FROM ODS.ACCT_PORTFOLIO
      WHERE ACCT_SRCH_NBR='$acct_num'
  );
SQL
}

# Export for use in csvtool call
export -f to_sql

# Process CSV from stdin
csvtool call to_sql -

测试:

使上述脚本可执行:

chmod +x excludeCSV2sql

创建示例test_exclude.csv:

cat >test_exclude.csv <<CSV
foo,bar,baz
here,it's using a single quote, string
this, "has a double-quoted string
with a newline", in it
10100000012, "R4242, has comma", 20200524
10100000042, R1337, 20200525
CSV

运行测试:

./excludeCSV2sql <test_exclude.csv >test.sql

检查结果:

test.sql

UPDATE ODS.PERF_ACCT_ERR_DTL SET REC_ACTV_IND='T'
  WHERE BUS_DT='baz' AND ETL_ERR_CD='bar' AND ACCT_KEY
  IN (
    SELECT ACCT_KEY FROM ODS.ACCT_PORTFOLIO
      WHERE ACCT_SRCH_NBR='foo'
  );
UPDATE ODS.PERF_ACCT_ERR_DTL SET REC_ACTV_IND='T'
  WHERE BUS_DT='string' AND ETL_ERR_CD='it''s using a single quote' AND ACCT_KEY
  IN (
    SELECT ACCT_KEY FROM ODS.ACCT_PORTFOLIO
      WHERE ACCT_SRCH_NBR='here'
  );
UPDATE ODS.PERF_ACCT_ERR_DTL SET REC_ACTV_IND='T'
  WHERE BUS_DT='in it' AND ETL_ERR_CD='has a double-quoted string
with a newline' AND ACCT_KEY
  IN (
    SELECT ACCT_KEY FROM ODS.ACCT_PORTFOLIO
      WHERE ACCT_SRCH_NBR='this'
  );
UPDATE ODS.PERF_ACCT_ERR_DTL SET REC_ACTV_IND='T'
  WHERE BUS_DT='20200524' AND ETL_ERR_CD='R4242, has comma' AND ACCT_KEY
  IN (
    SELECT ACCT_KEY FROM ODS.ACCT_PORTFOLIO
      WHERE ACCT_SRCH_NBR='10100000012'
  );
UPDATE ODS.PERF_ACCT_ERR_DTL SET REC_ACTV_IND='T'
  WHERE BUS_DT='20200525' AND ETL_ERR_CD='R1337' AND ACCT_KEY
  IN (
    SELECT ACCT_KEY FROM ODS.ACCT_PORTFOLIO
      WHERE ACCT_SRCH_NBR='10100000042'
  );

这篇关于使用Bash脚本构建SQL更新语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！