导入到 SQLite 时转义单引号字符

发布于 2024-07-30 12:32:17 字数 338 浏览 4 评论 0原文

我有一个这样的records.txt 文件：

INSERT INTO 'blogtitles' VALUES('Kelly's at house');
INSERT INTO 'blogtitles' VALUES('Nice catch!');

当我尝试将records.txt 导入数据库时： sqlite3 my.db < 记录.txt 由于第一行，它给出了一个错误。我在records.txt 文件中有很多这样的行。我需要一个 sed 语法来转义字符串中的所有这些单引号。所以导入的时候不会有任何问题。我真的需要这个:( 谢谢你！

原文

I have a records.txt file like this:

INSERT INTO 'blogtitles' VALUES('Kelly's at house');
INSERT INTO 'blogtitles' VALUES('Nice catch!');

When i try to import records.txt into the db: sqlite3 my.db < records.txt
It gives an error because of the first line.
I have many lines like this in the records.txt file.
I need a sed syntax that will escape all these single quotes within the strings. So there will not be any problem when importing.
I really need this :(
Thank you!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

银河中√捞星星 2024-08-06 12:32:17

SQL 标准指定通过在一行中放置两个单引号来转义字符串中的单引号。在这方面，SQL 的工作方式类似于 Pascal 编程语言。 SQLite 遵循这个标准。示例：

INSERT INTO xyz VALUES('5 O''clock');

链接

The SQL standard specifies that single-quotes in strings are escaped by putting two single quotes in a row. SQL works like the Pascal programming language in the regard. SQLite follows this standard. Example:

INSERT INTO xyz VALUES('5 O''clock');

link

回复收藏 0 原文

凉城凉梦凉人心 2024-08-06 12:32:17

在一般情况下，您不能使用正则表达式执行此操作，因为它们无法计数。但如果文件看起来就像那样，您可以伪造它：

sed -e "s/INSERT INTO 'blogtitles' VALUES('//" -e "s/');//" \
     -e "s/'/''/g" \
     -e "s/^/^INSERT INTO 'blogtitles' VALUES('/" -e "s/$/');/"

即删除该行的静态部分，复制引号，然后再次附加静态部分。

如果你的例子太简单，我建议看看 gawk(1) 它可以做更复杂的处理（例如，在“','”处分割行，它可能在两个之间值而不是其他地方）。

You can't do that with regular expressions in the general case because they can't count. But if the file looks just like that, you can fake it:

sed -e "s/INSERT INTO 'blogtitles' VALUES('//" -e "s/');//" \
     -e "s/'/''/g" \
     -e "s/^/^INSERT INTO 'blogtitles' VALUES('/" -e "s/$/');/"

i.e. remove the static part of the line, duplicate the quotes and then attach the static part again.

If your example is too simple, I suggest to have a look at gawk(1) which can do much more complicated processing (for example, split the line at "','" which is probably between two values and nowhere else).

回复收藏 0 原文

救星 2024-08-06 12:32:17

这就是我经常使用的：

cat myfile \
| sed -e "s/X/XX/g" \
| sed -e "s/^\([^']*\)'\([^']\)*'\([^']\)*'/\1XQ\2XQ\3XQ/" \
| sed -e "s/'\([^']*\)$/XQ\1" \
| sed -e "s/'/''/g" \
| sed -e "s/XQ/'/g" \
| sed -e "s/XX/X/g"

与 Aaron 的答案相比，主要优点是即使行的开头并不总是相同，它仍然有效，尽管以更长的代码为代价。

很容易记住：

选择一个“转义字符”
转义它本身
转义您想要保留的字符的出现位置（但不要让原始字符出现在转义序列中）
转换出现的字符
取消转义您的字符
取消转义转义字符

或者，简而言之：

转义除您需要转换的内容之外的所有内容
变换
取消转义所有内容

That's what I often use :

cat myfile \
| sed -e "s/X/XX/g" \
| sed -e "s/^\([^']*\)'\([^']\)*'\([^']\)*'/\1XQ\2XQ\3XQ/" \
| sed -e "s/'\([^']*\)$/XQ\1" \
| sed -e "s/'/''/g" \
| sed -e "s/XQ/'/g" \
| sed -e "s/XX/X/g"

The main advantage compared to Aaron's answer is that even if the begining of the line isn't always the same, it still works, although to the expense of longer code.

It's easy to remember :

Choose an 'escape character'
Escape it itself
Escape the occurrences of your character that you want to keep (but don't let the original character appear in the escaped sequence)
Transform the occurrences of your character
Unescape your character
Unescape the escape character

Or, in short :