python: insert the list data into database

Question

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!

Gouri · Accepted Answer · 2016-06-01 16:10:50Z

2

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()

answered Jun 1, 2016 at 16:10

Gouri

3471 silver badge5 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Gouri Over a year ago

BTW, I created the directors list to change individual elements to list type.(in accordance with your database column definition).

Ke Tian Over a year ago

I really thanks for your help, I will report my result!

Ke Tian Over a year ago

Thanks!@ Nouri, when use your code or method to insert the html data which parsed by the python, I found there is error, please see this question link(stackoverflow.com/questions/37566197/…):

Ke Tian Over a year ago

Thanks@Gouri, it works! Could you explain this code meaning: directors = [ [d, ] for d in director ], and why the roles did not need this? Thanks, you are a good guy.

Collectives™ on Stack Overflow

python: insert the list data into database

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related