我正在尝试用python创建一个训练应用程序来处理电影数据库,通过文本菜单添加电影详细信息,提示用户输入所有字段(电影名称,演员,公司等(。
我正在使用PostgreSQL作为数据库,并在Python中导入psycopg2。在数据库中,我使用多对多关系(因为同一个演员可以出演几部电影,依此类推(。因此,我有一个电影表(movie_id,名称,公司,年份(,一个演员表(actor_id,last_name,first_name,actor_ordinal(,第三个中间表actors_movies(actor_id,movie_id(外键连接两者,其中(actor_id,movie_id(成为主键。
然后我在 Python 中有这段代码(这是用户通过文本界面输入数据的最终阶段(:
def insert_movie(name, actors, company, year):
connection = psycopg2.connect(user='postgres', password='postgres', database='movie')
cursor = connection.cursor()
query1 = "INSERT INTO movies (name, company, year) VALUES (%s, %s, %s);"
cursor.execute(query1, (name, company, year))
query2 = 'INSERT INTO actors (last_name, first_name, actor_ordinal) VALUES (%s, %s, %s);'
cursor.executemany(query2, [tuple(actor) for actor in actors])
query3 = 'INSERT INTO actors_movies (actor_id, movie_id) SELECT actor_id, movie_id FROM actors, movies ON CONFLICT DO NOTHING;'
cursor.execute(query3)
connection.commit()
connection.close()
它适用于查询 1 和查询 2。但是 query3(以中间表actors_movies为目标(会复制所有数据。每次我添加新电影时,在中间表中,它都会显示其他电影中的所有演员,例如(加入后(:
movie_id movie_name actor_id
1 The Matrix 1
1 The Matrix 2
1 The Matrix 3
1 The Matrix 4
2 Titanic 1
2 Titanic 2
2 Titanic 3
2 Titanic 4
3 Patriot 1
3 Patriot 2
3 Patriot 3
3 Patriot 4
所以,这是完全错误的,因为根据这张表,所有三部电影中都有四位演员,这是错误的。 为什么会这样发生,如何消除这个问题?我找不到解决此问题的任何选择。
您可以在第三个查询中放置一个不同的 on select 子句,这将删除重复项。 在将相关数据插入多个表时,请维护一些键,例如:电影将具有可以在演员表中引用的movie_id然后使用该键连接电影和演员表。
这就是关系数据库的工作方式。对于第三个查询,您将对两个表(演员和电影(执行笛卡尔联接,因为您没有提供应用联接的约束。因此,数据库唯一能做的就是将表 B 中的所有记录分配给表 A 的每条记录。
从正式(数学(的角度来看,这个概念在下面的维基百科页面进行了说明
https://en.wikipedia.org/wiki/Cartesian_product
Tutorialspoint也有一个很好的例子和解释,可能有助于理解你的问题是什么
。https://www.tutorialspoint.com/sql/sql-cartesian-joins.htm
TL;博士
为了避免在插入新值时出现笛卡尔连接,您必须手动插入它们,或者在连接两个表(演员和电影(后应用条件,如下所示
插入actors_movies(actor_id、movie_id(选择actor_id,movie_id演员,电影,其中 actors.id + movies.id <4;