Ke Tian Ke Tian - 6 months ago 36
SQL Question

python: insert the list data into database

there is postgresql database named movie_db, and created a table named films as following:

CREATE TABLE movies (
title varchar(128) NOT NULL,
description varchar(256) NOT NULL,
directors varchar(128)[],
roles varchar(128)[]
);


I have four list for the title, description, directors, roles.
for example(maybe a large list not just three elements).

title = ['a', 'b', 'c']
description = ['good love', 'sun beautiful', 'wind go']
director = ['tom', 'jack', 'john']
roles=[['kate', 'back', 'jon'], ['tian', 'den', 'lucy'], ['cruse', 'jodan', 'peter']]


I want to insert these list data into the table films, How could I use the python sql to deal with it, I want to get the result like the following table, please notice that the roles not just has one name, sometimes have three names, such as kate, back, jon

title description director roles

a good love tom kate, back, jon
b sun beautiful jack tian, den, lucy
c wind go john cruse, jodan, peter


I use the following python code:

cur.execute('insert into film_test(title, description, directors,
roles)values(%s, %s, %s, %s)', title, description, director, roles)


But there is error:

TypeError: function takes at most 2 arguments (5 given)


Could you use the python to insert into the table, thanks!

Answer

psycopg2 has a great support for array insert. This probably will help.

import psycopg2

conn = psycopg2.connect(database="postgres")
cur = conn.cursor()

title = ['a', 'b', 'c']
description = ['good love', 'sun beautiful', 'wind go']
director = ['tom', 'jack', 'john']
directors = [ [d, ] for d in director ]
roles=[['kate', 'back', 'jon'], ['tian', 'den', 'lucy'], ['cruse', 'jodan', 'peter']]

sql = "INSERT INTO movies (title, description, directors, roles) VALUES (%s, %s, %s, %s);"
for obj in zip(title, description, directors, roles):
    cur.execute(cur.mogrify(sql, obj))
conn.commit()

cur.close()
conn.close()