用于更新 postgres 数据库的 bash 脚本

Question

我现在有一些 html 数据存储在文本文件中。我最近决定将 HTML 数据存储在 pgsql 数据库中而不是平面文件中。现在，“条目”表包含一个指向文件的“路径”列。我添加了一个“内容”列，现在应该将数据存储在“路径”指向的文件中。完成后，“路径”列将被删除。我遇到的问题是这些文件包含使我的脚本乱七八糟的撇号。我该怎么做才能解决这个问题？？

这是脚本

#!/bin/sh
dbname="myDB"
username="username"
fileroot="/path/to/the/files/*"

for f in $fileroot
do
psql $dbname $username -c "
  UPDATE entries
  SET content='`cat $f`'
  WHERE id=SELECT id FROM entries WHERE path LIKE '*`$f`';"
done

注意:id=SELECT...FROM...WHERE path LIKE "" 中的逻辑不是问题所在。我已经在 pgsql 环境中使用示例文件名对此进行了测试。

问题是，当我 cat $f 时，Edit: the contents of $f 中的任何撇号都会关闭 SQL 字符串，并且出现语法错误。

Answer 1

对于单引号转义问题，合理的解决方法可能是将引号加倍，因此您可以使用:

`sed "s/'/''/g" < "$f"`

包含文件 contents 而不是 cat，并且对于 LIKE 中的第二次调用，您似乎打算使用文件名称 使用:

${f/"'"/"''"/}

包含 $f 的文字字符串内容而不是执行它，并加倍引号。 ${varname/match/replace} 表达式是 bash 语法，可能不适用于所有 shell；使用:

`echo "$f" | sed "s/'/''/g"`

如果您需要担心其他 shell。

---

该 SQL 中还有许多其他问题。

• 您正在尝试在第二次调用中执行 $f。我很确定你不是故意的；我想你打算包括文字字符串。
• 你的子查询也是错误的，它缺少括号； (SELECT ...) 不仅仅是SELECT。
• 您的 LIKE 表达式也可能没有达到您的预期；您可能指的是 % 而不是 *，因为 % 是 SQL 通配符。

---

如果我也将反引号更改为 $()(因为它更清晰、更易于阅读 IMO)，请修复子查询语法并添加别名以消除列歧义，并改用 here-document传递给psql的stdin，结果为:

psql $dbname $username <<__END__
  UPDATE entries
  SET content=$(sed "s/'/''/g" < "$f")
  WHERE id=(SELECT e.id FROM entries e WHERE e.path LIKE '$(echo "$f" | sed "s/'/''/g")');
__END__

以上假设您使用的是相当现代的 PostgreSQL，并且 standard_conforming_strings = on。如果不是，请更改正则表达式以使用 \ 转义撇号而不是加倍它们，并在字符串前加上 E，所以 O'Brien 变成 E'O\'Brien'。在现代 PostgreSQL 中，它会变成 'O''Brien'。

---

一般来说，我建议使用真正的脚本语言，如带有 DBD::Pg 的 Perl 或带有 psycopg 的 Python 来解决数据库的脚本问题。使用 shell 有点时髦。使用支持参数化语句的数据库接口(interface)编写此表达式会容易得多。

例如，我会这样写:

import os
import sys
import psycopg2

try:
        connstr = sys.argv[1]
        filename = sys.argv[2]
except IndexError as ex:
        print("Usage: %s connect_string filename" % sys.argv[0])
        print("Eg: %s \"dbname=test user=fred\" \"some_file\"" % sys.argv[0])
        sys.exit(1)


def load_file(connstr,filename):
        conn = psycopg2.connect(connstr)
        curs = conn.cursor()
        curs.execute("""
        UPDATE entries
        SET content = %s
        WHERE id = (SELECT e.id FROM entries e WHERE e.path LIKE '%%'||%s);
        """, (filename, open(filename,"rb").read()))
        curs.close()

if __name__ == '__main__':
        load_file(connstr,filename)

请注意，SQL 通配符 % 被加倍以对其进行转义，因此在最终 SQL 中会产生单个 %。这是因为 Python 使用 % 作为它的格式说明符，所以文字 % 必须加倍才能转义它。

您可以简单地修改上面的脚本以接受文件名列表，连接到数据库一次，然后遍历所有文件名列表。这会快很多，尤其是当您在一次交易中完成所有操作时。使用 psql 脚本来做这件事真的很痛苦；你必须使用 bash 协同处理 as shown here ……这不值得麻烦。