如果类似的记录尚不存在，则插入记录时出现问题

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 查询上收到一个错误，指出“作者”未定义（尽管它是）。

使用匹配记录计数的不太优雅的方式

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,) 的元组，但我找不到一种匹配它的方法来让我进入“添加一本书的循环”。 if anyresults[0] == 0, 0L, '0', '0L' ——这些似乎都没有让我进入循环。

我想我可能没有正确使用 IF NOT EXISTS ——我发现的例子是针对单独的过程的，这实际上并不在这个小项目的范围内。

添加： 我认为 unutbu 的代码会很好用，但我仍然会收到这个愚蠢的 NameError ，说作者未定义，这会阻止尝试插入，即使我肯定将其传递进去。

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

如果您在 book 上设置了 UNIQUE 索引，那么插入唯一行就很容易。

例如，

mysql> ALTER IGNORE TABLE book ADD UNIQUE INDEX book_index (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 注入。

Answer 2

这并不能回答你的问题，因为它是针对 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;       

这非常简单。它选择新行，就像它们是一个表一样，然后将它们与现有数据进行左连接。尚不存在的行将连接到 NULL 行；然后我们过滤掉已经存在的内容（其中 book.title 不为 NULL）。这是非常快的：只需要一个数据库事务来执行大量插入，并让数据库后端执行批量连接，这是它非常擅长的。

顺便说一句，您确实需要停止直接格式化 SQL 查询（除非您确实必须这样做并且真正知道自己在做什么，但这里您并不知道）。使用查询替换，例如。 cur.execute("SELECT * FROM table WHERE title=? and isbn=?", (title, isbn)).