CODEWITHSUNDEEP
pythonmysqllist
KhanShaheb
KhanShaheb

Reputation: 714

Insert into database from list in python 2.7

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.

Upvotes: 0

Views: 360

Answers (3)

Sнаđошƒаӽ
Sнаđошƒаӽ

Reputation: 17612

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

Upvotes: 0

Vineesh
Vineesh

Reputation: 263

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

Upvotes: 0

alecxe
alecxe

Reputation: 474231

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.

Upvotes: 3

Related Questions