如何将 CSV 导入到两个使用 Python 相互引用的不同 SQL 表中

Question

客观的

我正在研究 CS50 Web 的项目 1，书籍（https://docs.cs50.net/ocw/web/projects/1/project1.html）。它要求我将 csv 导入一个或两个表的数据库中。CSV 看起来像：

isbn, title, author, year

0131320931, The Hobbit, J.R.R. Tolkien, 1937

我选择制作两张表：书籍和作者。他们看起来像：

CREATE TABLE books (book_id SERIAL PRIMARY KEY, isbn VARCHAR(13), title VARCHAR(26021), author references authors(author_id),  year INT);

CREATE TABLE authors (author_id SERIAL PRIMARY KEY, name VARCHAR(255));

我的目标是使用带有 SQLAlchemy 的 Python 脚本将 CSV 导入这两个表中。

问题

我的问题是以书籍中的外国 id 引用作者的方式导入 CSV。我当前的代码是这样的：

def main():

    f = open("books.csv")

    reader = csv.reader(f)

    for isbn, title, author, year in reader:

        db.execute("INSERT INTO authors (name) VALUES(:author)",

                   {"author": author})

        db.execute("INSERT INTO books (isbn, title, year) VALUES(:isbn, :title, :year)", {

                   "isbn": isbn,

                   "title": title

                   "year": year})

    db.commit()

结果是：

 book_id |    isbn    |         title         | author | year 

---------+------------+-----------------------+--------+------

       1 | 0380795272 | Krondor: The Betrayal |   *    | 1998

       2 | 1416949658 | The Dark Is Rising    |        | 1973

*Should be "1", the foreign key for Raymond E. Feist.

 author_id |       name       

-----------+------------------

         1 | Raymond E. Feist

         2 | Susan Cooper

我似乎不太想用一种同时导入它们的方法来生成作者中的外键。我想创建一个临时表并使用 SELECT WHERE 查询，但这似乎是一种逃避。我还在使用作者的第二个 INSERT 中尝试了一个嵌套函数，但这似乎不起作用。

我正在使用 PostgreSQL。

Answer 1

解决方案我能够弄清楚。ON CONFLICT DO NOTHING在使authors.name列唯一后，我使用了 PostgreSQL 的子句。我认为在这个数据结构中不可避免的一个警告是同名作者。我也不确定如何包含由多位作者撰写的书籍。代码def main():    f = open("books.csv")    reader = csv.reader(f)    reader.__next__    for isbn, title, author, year in reader:        db.execute("INSERT INTO authors (name) VALUES(:author) ON CONFLICT (name) DO NOTHING",                   {"author": author})        db.execute("INSERT INTO books (isbn, title, author, year) VALUES(:isbn, :title, (SELECT author_id FROM authors WHERE name = :author), :year)", {                   "isbn": isbn,                   "author": author,                   "title": title,                   "year": year})    db.commit()输出 author_id |       name       -----------+------------------        38 | Raymond E. Feist book_id |    isbn    |         title         | author | year ---------+------------+-----------------------+--------+------      38 | 0380795272 | Krondor: The Betrayal |     38 | 1998注意：它们共享相同的 ID 是偶然的，因为它们是输入的第一条记录。