postgresql - CSV 文件数据到 PostgreSQL 表中

Question

我正在尝试为 movielens ( http://grouplens.org/datasets/movielens/ ) 创建一个数据库。我们有电影和收视率。电影有多种类型。我将它们拆分到一个单独的表中，因为它是 1:many 关系。用户与电影之间也存在多对多关系。我需要能够以多种方式查询此表。

所以我创建了:

CREATE TABLE genre (
  genre_id serial NOT NULL,
  genre_name char(20) DEFAULT NULL,
  PRIMARY KEY (genre_id)
) 

.

INSERT INTO genre VALUES 
  (1,'Action'),(2,'Adventure'),(3,'Animation'),(4,'Children\s'),(5,'Comedy'),(6,'Crime'),
  (7,'Documentary'),(8,'Drama'),(9,'Fantasy'),(10,'Film-Noir'),(11,'Horror'),(12,'Musical'),
  (13,'Mystery'),(14,'Romance'),(15,'Sci-Fi'),(16,'Thriller'),(17,'War'),(18,'Western');

.

CREATE TABLE movie (
  movie_id int NOT NULL DEFAULT '0',
  movie_name char(75) DEFAULT NULL,
  movie_year smallint DEFAULT NULL,
  PRIMARY KEY (movie_id)
  );

.

CREATE TABLE moviegenre (
  movie_id int NOT NULL DEFAULT '0',
  genre_id tinyint NOT NULL DEFAULT '0',
  PRIMARY KEY (movie_id, genre_id)
);

我不知道如何导入包含 movie_id、movie_name 和 movie_genre 列的 movies.csv 例如，第一行是 (1;Toy Story (1995);Animation|Children's|喜剧)如果我手动插入，它应该是这样的:

INSERT INTO moviegenre VALUES (1,3),(1,4),(1,5)

因为3是动画，4是 child ，5是喜剧

如何以这种方式导入所有数据集？

Answer 1

您应该首先创建一个可以从 CSV 文件中提取数据的表:

CREATE TABLE movies_csv (
  movie_id    integer, 
  movie_name  varchar,
  movie_genre varchar
);

请注意，任何单引号(Children's)都应该加倍(Children's)。一旦数据在此暂存表中，您就可以将数据复制到 movie 表中，该表应具有以下结构:

CREATE TABLE movie (
  movie_id   integer, -- A primary key has implicit NOT NULL and should not have default
  movie_name varchar NOT NULL, -- Movie should have a name, varchar more flexible
  movie_year integer,          -- Regular integer is more efficient
  PRIMARY KEY (movie_id)
);

以同样的方式清理您的其他 table 。

现在复制数据，从 CSV 名称中提取原始名称和年份:

INSERT INTO movie (movie_id, movie_name)
  SELECT parts[1], parts[2]::integer
  FROM movies_csv, regexp_matches(movie_name, '([[:ascii:]]*)\s\(([\d]*)\)$') p(parts)

这里是 regular expression说:

• ([[:ascii:]]*) - 捕获所有字符，直到下面的匹配项
• \s - 读取空格
• \( - 阅读左括号
• ([\d]*) - 捕获任何数字
• \) - 阅读结束括号
• $ - 从字符串末尾开始匹配

因此，在输入“虎胆龙威 17(约翰永远活着)(2074)”时，它会创建一个字符串数组，其中包含 {'虎胆龙威 17(约翰永远活着)', '2074'}。扫描必须从结尾 $ 开始，假设所有电影标题都以括号中的出版年份结尾，以便保留电影标题中的括号和数字。

现在您可以处理电影类型了。您必须使用 regex_split_to_table() 函数拆分 | 栏上的字符串，然后根据流派名称加入 genre 表:

INSERT INTO moviegenre
  SELECT movie_id, genre_id
  FROM movies_csv, regexp_split_to_table(movie_genre, '\|') p(genre) -- escape the |
  JOIN genre ON genre.genre_name = p.genre;

完成所有工作并清理干净后，您可以删除 movies_csv 表。