插入记录时若已存在相似记录则出错

Question

我正在尝试检查数据库中是否已经存在某个记录（通过类似的标题），如果不存在就插入它。我试过两种方法，但都没有成功。

第一种方法是用 IF NOT EXISTS，这种方式看起来更优雅。

if mode=="update":
    #check if book is already present in the system
    cursor.execute('IF NOT EXISTS (SELECT * FROM book WHERE TITLE LIKE "%s") INSERT INTO book (title,author,isbn) VALUES ("%s","%s","%s") END IF;' % (title,title,author,isbn))
    cursor.execute('SELECT bookID FROM book WHERE TITLE LIKE "%s";' % (title))
    bookID = cursor.fetchall()
    print('found the bookid %s' % (bookID))
    #cursor.execute('INSERT INTO choice (uid,catID,priority,bookID) VALUES ("%d","%s","%s","%s");' % ('1',cat,priority,bookID)) #commented out because above doesn't work

但是用这个方法时，我在 IF NOT EXISTS 的查询中遇到了一个错误，提示“author”没有定义（虽然我确实有定义它）。

第二种方法是通过匹配记录的数量来实现，这种方式就显得不那么优雅了。

if mode=="update":
    #check if book is already present in the system
    cursor.execute('SELECT COUNT(*) FROM book WHERE title LIKE "%s";' % (title))
    anyresults = cursor.fetchall()
    print('anyresults looks like %s' % (anyresults))
    if anyresults[0] == 0: # if we didn't find a bookID
        print("I'm in the loop for adding a book")
        cursor.execute('INSERT INTO book (title,author,isbn) VALUES ("%s","%s","%s");' % (title,author,isbn))
    cursor.execute('SELECT bookID FROM book WHERE TITLE LIKE "%s";' % (title))
    bookID = cursor.fetchall()
    print('found the bookid %s' % (bookID))
    #cursor.execute('INSERT INTO choice (uid,catID,priority,bookID) VALUES ("%d","%s","%s","%s");' % ('1',cat,priority,bookID)) #commented out because above doesn't work

在这个版本中，anyresults 是一个元组，看起来像 (0L,)，但我找不到一种方法来匹配它，以便进入“添加书籍”的循环。如果 anyresults[0] 等于 0、0L、'0' 或 '0L'，这些都似乎无法让我进入这个循环。

我觉得我可能没有正确使用 IF NOT EXISTS——我找到的例子都是针对单独的过程，而这对于我这个小项目来说并不太适用。

补充说明：我觉得 unutbu 的代码应该能很好地工作，但我仍然遇到一个愚蠢的 NameError，提示 author 未定义，这阻止了 INSERT 操作的尝试，即使我确实传递了这个参数。

if form.has_key("title"): 
    title = form['title'].value
    mode = "update"
if form.has_key("author"): 
    author = form['author'].value
    mode = "update"
    print("I'm in here")
if form.has_key("isbn"):
    isbn = form['isbn'].value
    mode = "update"

它从来没有打印出“我在这里”的测试语句。是什么阻止它进入这个地方呢？看起来很明显——我一直在检查我的缩进，并且在命令行上测试时也明确指定了所有三个参数。

Answer 1

这段内容虽然不直接回答你的问题，因为它是关于Postgresql而不是MySQL的，但我想把它放在这里，方便那些搜索到这里的人。

在Postgres中，如果要插入的项目不存在，你可以批量插入：

CREATE TABLE book (title TEXT, author TEXT, isbn TEXT);

# Create a row of test data:
INSERT INTO book (title,author,isbn) VALUES ('a', 'b', 'c');

# Do the real batch insert:
INSERT INTO book
SELECT add.* FROM (VALUES
 ('a', 'b', 'c'),
 ('d', 'e', 'f'),
 ('g', 'h', 'i'),
) AS add (title, author, isbn)
LEFT JOIN book ON (book.title = add.title)
WHERE book.title IS NULL;       

这个操作其实很简单。它会把新的行当作一个表来选择，然后和现有的数据进行左连接。那些已经存在的行会和一个空行连接；接着我们会过滤掉那些已经存在的行（也就是book.title不为空的行）。这样做非常快：只需要一次数据库操作就能完成大量的插入，而且让数据库后端进行批量连接，这方面它非常擅长。

顺便说一下，你真的应该停止直接格式化你的SQL查询（除非你真的必须这样做，并且非常清楚自己在做什么，而在这里你并不需要）。建议使用查询替换，比如 cur.execute("SELECT * FROM table WHERE title=? and isbn=?", (title, isbn))。

Answer 2

如果你在 book 表上设置了一个唯一索引，那么插入唯一的记录就变得简单了。

举个例子，

mysql> ALTER IGNORE TABLE book ADD UNIQUE INDEX book_index (title,author);

注意：如果有重复的 (title, author) 组合，除了其中一条记录，其他的都会被删除。

如果你只想让 author 字段是唯一的，那就把 (title,author) 改成 (author) 就行了。

根据表的大小，这个过程可能需要一些时间……

现在，要插入一条唯一的记录，

sql='INSERT IGNORE INTO book (title,author,isbn) VALUES (%s, %s, %s)'
cursor.execute(sql,[title,author,isbn])

如果 (title,author) 是唯一的，那么三元组 (title,author,isbn) 就会被插入到 book 表中。

如果 (title,author) 不是唯一的，那么 INSERT 命令就会被忽略。

注意，cursor.execute 的第二个参数。这样传递参数可以帮助防止 SQL 注入攻击。