Implementing generators to populate database

Question

I can't wrap my head around generators fully, sometimes I happen to use them in a right way and sometimes I do not.

I am populating a db from a .csv file:

name, location
Tom, France
Doug, USA
Michael, UK

Here is my code:

def process_csv(filecsv):
    f = open(filecsv)
    f_csv = csv.reader(f)
    headers = next(f_csv)
    User = namedtuple('User', headers)
    for user in f_csv:
        user = [u.strip() for u in user]
        user = User(*user)
        yield user 

def insert(cur, user): 
    u = list(user)[0]   # it's a one-elem list so take it 
    cur.execute("INSERT INTO users (name, location) VALUES(%s, %s)", 
    (u.name, u.location))


if __name__ == '__main__':
    cur = cxn.cursor()
    user = process_csv(filecsv)
    insert(cur, user)

When run this, only the first row got inserted into the db. Can you please advise how to fix it?

We can't see how either of these functions is called. In particular, they don't seem to be connected; neither of them calls the other. How are these functions related? — user2357112
– user2357112, Commented Dec 11, 2013 at 12:48
Just to say that if this a one-off job, most databases have functions to import directly from CSV files. e.g. sqlite.org/cvstrac/wiki?p=ImportingFiles and dev.mysql.com/doc/refman/5.1/en/load-data.html — YXD
– YXD, Commented Dec 11, 2013 at 12:54

user2357112 · Accepted Answer · 2013-12-11 12:56:05Z

2

process_csv(filecsv) returns an iterator over users. insert turns the iterator into a list and explicitly takes the first item. (The comment is wrong; it's not a 1-element list.) Instead, loop over the users and insert each one:

def insert(cur, users): 
    for user in users:
        cur.execute("INSERT INTO users (name, location) VALUES(%s, %s)", 
        (user.name, user.location))

Note that there are almost certainly better ways to perform this database operation. This answer only addresses the generator issue.

answered Dec 11, 2013 at 12:56

user2357112

286k32 gold badges490 silver badges571 bronze badges

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

Comments

Philippe T. · Accepted Answer · 2013-12-11 12:56:20Z

2

You aren't so far from your goal i think, try this :

def process_csv(filecsv):
    f = open(filecsv)
    f_csv = csv.reader(f)
    headers = next(f_csv)
    User = namedtuple('User', headers)
    for user in f_csv:
        user = [u.strip() for u in user]
        user = User(*user)
        yield user 

def insert(cur, user): 
    cur.execute("INSERT INTO users (name, location) VALUES(%s, %s)", 
    (user.name, user.location))

if __name__ == '__main__':
    cur = cxn.cursor()
    for user in process_csv():
        insert(cur,user)

a generateur is an iterator , so you should use it like a iterator

answered Dec 11, 2013 at 12:56

Philippe T.

1,1927 silver badges11 bronze badges

Collectives™ on Stack Overflow

Implementing generators to populate database

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related