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
– alecxe, Commented Dec 21, 2018 at 6: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
– Omer Tekbiyik, Commented Dec 21, 2018 at 6:28
This is a terrible practice wrt/ proper relational model design - each value should be atomic. — bruno desthuilliers
– bruno desthuilliers, Commented Dec 21, 2018 at 8: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
– Omer Tekbiyik, Commented Dec 21, 2018 at 9:14

alecxe · Accepted Answer · 2018-12-21 06:30:58Z

1

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

answered Dec 21, 2018 at 6:30

alecxe

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

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

Comments

fixatd · Accepted Answer · 2018-12-21 06:52:53Z

0

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"

answered Dec 21, 2018 at 6:52

fixatd

1,4041 gold badge13 silver badges22 bronze badges

Collectives™ on Stack Overflow

Python : insert 2 dimensional array into Mysql Database

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related