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

Question

我现在有一些html数据存储在文本文件中。我最近决定将HTML数据存储在pgsql数据库中，而不是平面文件中。现在，"entries"表包含一个指向文件的"path"列。我添加了一个"内容"列，现在应该将数据存储在"路径"指向的文件中。一旦完成，"路径"列将被删除。我遇到的问题是文件中包含撇号，这会使我的脚本不正常。我能做些什么来纠正这个问题？？

这是的脚本

#!/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:$f的内容中的任何撇号都会关闭SQL字符串，并且我会得到语法错误。

Answer 1

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

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

要包括文件内容而不是cat，对于LIKE中的第二次调用，您似乎打算使用文件name，请使用：

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

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

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

如果你需要担心其他贝壳的话。

---

SQL中还有很多其他问题。

• 您正试图在第二次调用中执行$f。我敢肯定你不是有意的；我想你的意思是包括文字字符串
• 您的子查询也是错误的，它缺少括号；CCD_ 9而不仅仅是CCD_
• 你的LIKE表达可能也没有达到你想要的效果；您可能指的是%而不是*，因为%是SQL通配符

---

如果我也将backticks更改为$()(因为它更清晰、更容易阅读IMO)，修复子查询语法并添加别名以消除列的歧义，并使用传递给psql的stdin的here文档，结果是：

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__

以上假设您使用的是一个相当现代的带有standard_conforming_strings = on的PostgreSQL。如果不是，请更改regexp以用转义撇号，而不是将它们加倍，并在字符串前面加上E，使O'Brien变为E'O'Brien'。在现代PostgreSQL中，它变成了'O''Brien'。

---

通常，我建议使用真正的脚本语言，如带有DBD:：Pg的Perl或带有psycopg的Python，来解决数据库的脚本问题。使用外壳有点古怪。使用支持参数化语句的数据库接口，这个表达式将更容易编写。

例如，我会这样写：

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协同进程，如下所示。。。这不值得这么麻烦。

Answer 2

在最初的帖子中，我让它听起来像是文件名中有撇号，由$f表示。事实并非如此，所以一个简单的echo "$f"就可以解决我的问题。

为了更清楚，我的文件内容被格式化为html片段，通常类似于<p>Blah blah <b>blah</b>...</p>。在尝试了Craig发布的解决方案后，我意识到我在一些锚标签中使用了单引号，我不想把它们改成其他东西。只有少数文件发生了这种违规行为，所以我只是手动将其更改为双引号。我还意识到，与其转义撇号，不如将它们转换为'。以下是我最终使用的最后一个脚本：

dbname="myDB"
username="username"
fileroot="/path/to/files/*"
for f in $fileroot
do
psql $dbname $username << __END__
UPDATE entries
SET content='$(sed "s/'/&apos;/g" < "$f")'
WHERE id=(SELECT e.id FROM entries e WHERE path LIKE '%$(echo "$f")');
__END__
done

这里的格式着色可能会让它看起来语法不正确，但我已经验证了它是正确的。