Insert into database from list in python 2.7

Question

Suppose I have lists within a list. I want it to insert list item in database.

[['1', '2', '3'], ['0.2', '2.3', '4'], ['5.4', '2', '3']]

here you will see that the main list has three sub-list. I want 1,0.2,5.4 in first-column of database, 2,2.3,2 in second-column of database and 3,4,3 in third column of database. some portion of code is given:

FILE_NAME = "mmd.txt"
list=[]
with open(FILE_NAME, 'r') as f:
    lines = f.read().split('\n');
    for line in lines:
        list.append(line.split(','))

print list
for i in list:
    print i
    for j in i:
        print j
    print 'new line'

I can separate sub-list from list and I am also able to separate item from sub-list but how can I insert them into my database respectively? Please put your comment/suggestion.

alecxe · Accepted Answer · 2015-09-01 04:02:32Z

3

You can solve it with executemany():

cursor.executemany("""
    INSERT INTO 
        MYTABLE
        (COLUMN1, COLUMN2, COLUMN3)
    VALUES
        (%s, %s, %s)
""", mylist)

where, I am assuming mylist to be a list of lists with 3 items each:

[
    ['1', '2', '3'], 
    ['0.2', '2.3', '4'], 
    ['5.4', '2', '3']
]

And don't name your variable as list to avoid shadowing the built-in list.

answered Sep 1, 2015 at 4:02

alecxe

476k127 gold badges1.1k silver badges1.2k bronze badges

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

1 Comment

alecxe Over a year ago

@IrtizaKhanNiloy well, cursor is obviously cur in your case.

Sнаđошƒаӽ · Accepted Answer · 2015-09-01 06:46:27Z

0

Basing on the answer of alecxe, you should use executemany, and after the last execute command add a commit command like this. Assuming your database object name is myDbName:

myDbName.commit()

answered Sep 1, 2015 at 6:46

Sнаđошƒаӽ

17.8k13 gold badges78 silver badges95 bronze badges

Comments

Vineesh · Accepted Answer · 2015-09-01 04:48:14Z

0

    import MySQLdb

    my_list = [['1', '2', '3'], ['0.2', '2.3', '4'], ['5.4', '2', '3']]
    res_list = []

    for i,j,k in zip(my_list[0],my_list[1],my_list[2]):
        res_list.append(i+','+j+','+k)

    db = MySQLdb.connect(host="localhost", # your host, usually localhost
                         user="root", # your username
                          passwd="", # your password
                          db="db_name")

    cur = db.cursor()
    cur.execute("insert into tablename (column1,column2,column3) values(%s,%s,%s)",(res_list[0],res_list[1],res_list[2]))

answered Sep 1, 2015 at 4:48

Vineesh

2632 silver badges7 bronze badges

2 Comments

Moberg Over a year ago

Just expand the zip call zip(my_list[0],my_list[1], ..., my_list10000])

Sнаđошƒаӽ Over a year ago

@Moberg that seems very clumsy to me. There must some efficient way to do this, I believe.

Collectives™ on Stack Overflow

Insert into database from list in python 2.7

3 Answers 3

1 Comment

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related