试图添加到数据库，但忽略重复项

Question

我试图将CSV中的数据添加到一个唯一的字段中，但是如果CSV中有任何重复，程序就不会提交。我想要做的是，如果您想要添加一个副本，只需继续其余的操作，但在最后执行提交。我在用SQLalchemy。这几乎就好像db遇到了一个错误，它根本不提交任何东西，即使我有了try/ the。

import csv
import os

from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session, sessionmaker

engine = create_engine(os.getenv("DATABASE_URL"))
# scoped session allows us to have multiple users on a database
db = scoped_session(sessionmaker(bind=engine))

def main():
    # id = db.execute("SELECT id FROM authors WHERE name='Anne Rice'")
    # print(id['id'])
    with open("books.csv") as file:
        reader = csv.DictReader(file)
        for row in reader:
            # :placeholder, and then provide variables 
            try:
                db.execute("INSERT INTO authors1 (name) VALUES (:name)",
                            {"name":row['author']})
            except:
                pass
            print("added",row['author'])

        db.commit()

Answer 1

这是示例的简化版本，它可以工作，它将遍历作者列表并将它们添加到数据库中，当数据库由于非唯一值而给出一个IntegrityError时，它将打印它并继续前进。

注意：我把相同的“打印添加”放在与您相同的级别上，它将同时打印添加的作者和被跳过的作者。

import sqlalchemy as sa

engine = sa.create_engine("sqlite:///:memory:", echo=True)

with engine.begin() as con:
    con.execute(
        "CREATE TABLE author ("
        "id INTEGER PRIMARY KEY, "
        "name TEXT NOT NULL UNIQUE)"
        )

authors = [
    "Guillaume Apollinaire",
    "Jules Verne",
    "Jean-Paul Sartre",
    "Jules Verne",
]

with engine.begin() as con:
    for author in authors:
        try:
            con.execute(
                "INSERT INTO author (name) VALUES (:name)",
                {"name": author}
            )
        except Exception as e:
            print(e)
        print(f"added {author}")

with engine.connect() as con:
    print(con.execute("SELECT * FROM author").all())

给予：

2022-06-04 11:53:26,812 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-06-04 11:53:26,813 INFO sqlalchemy.engine.Engine CREATE TABLE author (id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE)
2022-06-04 11:53:26,813 INFO sqlalchemy.engine.Engine [raw sql] ()
2022-06-04 11:53:26,816 INFO sqlalchemy.engine.Engine COMMIT
2022-06-04 11:53:29,950 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-06-04 11:53:29,950 INFO sqlalchemy.engine.Engine INSERT INTO author (name) VALUES (:name)
2022-06-04 11:53:29,950 INFO sqlalchemy.engine.Engine [raw sql] {'name': 'Guillaume Apollinaire'}
added Guillaume Apollinaire
2022-06-04 11:53:29,950 INFO sqlalchemy.engine.Engine INSERT INTO author (name) VALUES (:name)
2022-06-04 11:53:29,950 INFO sqlalchemy.engine.Engine [raw sql] {'name': 'Jules Verne'}
added Jules Verne
2022-06-04 11:53:29,951 INFO sqlalchemy.engine.Engine INSERT INTO author (name) VALUES (:name)
2022-06-04 11:53:29,951 INFO sqlalchemy.engine.Engine [raw sql] {'name': 'Jean-Paul Sartre'}
added Jean-Paul Sartre
2022-06-04 11:53:29,951 INFO sqlalchemy.engine.Engine INSERT INTO author (name) VALUES (:name)
2022-06-04 11:53:29,951 INFO sqlalchemy.engine.Engine [raw sql] {'name': 'Jules Verne'}
(sqlite3.IntegrityError) UNIQUE constraint failed: author.name
[SQL: INSERT INTO author (name) VALUES (:name)]
[parameters: {'name': 'Jules Verne'}]
(Background on this error at: https://sqlalche.me/e/14/gkpj)
added Jules Verne
2022-06-04 11:53:29,960 INFO sqlalchemy.engine.Engine COMMIT
2022-06-04 11:59:34,511 INFO sqlalchemy.engine.Engine SELECT * FROM author
2022-06-04 11:59:34,511 INFO sqlalchemy.engine.Engine [raw sql] ()
[(1, 'Guillaume Apollinaire'), (2, 'Jules Verne'), (3, 'Jean-Paul Sartre')]