Python : insert 2 dimensional array into Mysql Database

Question

myArray = [['example1','example2'], ['value1','value2']]
arraySize = len(myArray)
try:
for r in range(0,arraySize):
    try:
        cur.execute("INSERT INTO series(title) VALUES (%s)",(myArray[r]))
        conn.commit() 
    except:
        print "Error"
       conn.rollback()

This is my code. I want to insert myArray[r] to my Database but Program gives me "Error" message. How can I insert all items like (['example1','example2']) in 1 row.

1. Are you always gonna have 2 items per sublist? 2. Are both of these items `title` values? — alecxe, Dec 21 '18 at 06:25
No. My array items become from html parse so my per sublist have no 2 . Some of 15 some of 1. Randomly . I want to all this sublists are in 1 row in my database row named 'title'. — Omer Tekbiyik, Dec 21 '18 at 06:28
This is a terrible practice wrt/ proper relational model design - each value should be atomic. — bruno desthuilliers, Dec 21 '18 at 08:10
I know that But I have thousands of data and Im using it for my DataMining project.Only I can access this database — Omer Tekbiyik, Dec 21 '18 at 09:14

score 1 · Answer 1 · answered Dec 21 '18 at 06:30

From what I understand, these are all titles and we could first flatten the list and then insert it with .executemany(). Look how concise and beautiful it is:

titles = [item for sublist in l for item in myArray]

cur.executemany("""
    INSERT INTO 
        series(title)
    VALUES (%s)""", titles)

cur.commit()

score 0 · Accepted Answer · answered Dec 21 '18 at 06:52

You can try joining each item in myArray first before trying to insert them.

myArray = [['example1','example2'], ['value1','value2']]
arraySize = len(myArray)
for r in range(0,arraySize):
    try:
        cur.execute(
              "INSERT INTO series(title) VALUES (%s)",
              (",".join(myArray[r]), ) # Merge the titles with a ,
        )
        conn.commit()
    except:
        print "Error"
       conn.rollback()

This should give you the following titles instead:

"example1,example2"
"value1,value2"

Python : insert 2 dimensional array into Mysql Database

2 Answers2