Insert multiple values in MySQL with Python from an array

Question

I am trying to insert data from my array into MySQL. To my big surprise there were not many examples on how to do it if you perform a for-loop for your array, every example that I have found was from an already existing array list.

Thanks to Adrian below, we noticed that I need tuples for my list.

Updated code

connection = mysql.connector.connect(
    host='localhost', 
    database='test',
    user='root', 
    password='pass'
    )

query = "INSERT INTO blue (created, published, publisher) VALUES (%s, %s, %s)"

array = []
# The idea here is to get all table rows in the page so you can group the values into rows that are going to be added to MySQL
tr = soup.find_all('tr')
for table_row in tr:
    row_data = table_row.find_all('td')
    insert_row = []
    for data in row_data:
        data = re.sub('<[^>]*>', '', str(data))
        insert_row.append(data)
    array.append(tuple(insert_row))
print(array)

cursor = connection.cursor()
cursor.executemany(query, array)
cursor.commit()

Getting close but at the moment I receive the following

IndexError: Tuple index out of range

mysql.connector.errors.ProgrammingError: Not enough parameters for the SQL statement

Thanks in advance!

is array a list of lists or just a list of strings, because it looks like you are just iterating over a list of strings and for each string are trying to do an executemany that is expecting 3 parameters but you only pass 1 — gold_cy
– gold_cy, Commented Jan 3, 2020 at 20:18
Hmm you might be on to something, I believe a list of strings. So it might think everything I'm iterating is one big string? — massideux
– massideux, Commented Jan 3, 2020 at 20:22
if len(array) == 3 then you don't need a for-loop simply doing cursor.execute(query, array) should work — gold_cy
– gold_cy, Commented Jan 3, 2020 at 20:24
The array contains way more data then 3, my three columns in the DB will contain a lot of rows. — massideux
– massideux, Commented Jan 3, 2020 at 20:47
then you need array to be a list of list where each inner list has a length of 3 — gold_cy
– gold_cy, Commented Jan 3, 2020 at 20:54

Adrian Beloqui · Accepted Answer · 2020-01-03 23:38:35Z

1

I think you are mixing two ways of solving the problem...

One way is using the executemany method as described in the documentation

query = "INSERT INTO blues (created, published, publisher) VALUES (%s, %s, %s)"

array = []
# The idea here is to get all table rows in the page so you
# can group the values into rows that are going to be added to MySQL
tr = soup.find_all('tr')
for table_row in tr:
    row_data = table_row.find_all('td')
    insert_row = [None, None, None]
    for idx in range(len(row_data)):
        if row_data[idx] and idx < 3:
            data = re.sub('<[^>]*>', '', str(row_data[idx]))
            if data:
                insert_row[idx] = data
    array.append(tuple(insert_row))

cursor = connection.cursor()
cursor.executemany(query, array)
cursor.commit()

Another way is to build the query yourself...

query = "INSERT INTO blues (created, published, publisher) VALUES "
array = []
# The idea here is to get all table rows in the page so you can group the values into rows that are going to be added to MySQL
tr = soup.find_all('tr')
for table_row in tr:
    row_data = table_row.find_all('td')
    insert_row = []
    for data in row_data:
        data = re.sub('<[^>]*>', '', str(data))
        insert_row.append(data)
    array.append(tuple(insert_row))

values = []
for item in array:
    row = [None, None, None]
    for idx in range(len(item)):
        row[idx] = item[idx]
    values.append(str(tuple(row)))

query += ",".join(values)
cursor = connection.cursor()
cursor.execute(query)
cursor.commit()

Hope this helps...

edited Jan 3, 2020 at 23:38

answered Jan 3, 2020 at 20:21

Adrian Beloqui

1014 bronze badges

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

15 Comments

massideux Over a year ago

Thanks for examples, two questions! In your example 1, you have the data from the array. But I am retrieving data from a HTML Table with beautifulSoup, so I can't "hardcode" the list, correct? I started to play around with your second example, three columns and lots of collected data under these columns should be inserted but can't get it to work. What did you mean by "Assuming there always 3 elements in each item"?

massideux Over a year ago

Forgot to mention that I receive is IndexError: string index out of range

Adrian Beloqui Over a year ago

Okay, so...Example 1 - I put the example array so you could see what the array should look like when calling cursor.executemany(query, array). I would imagine that you don't want a hardcoded array =). If you are unsure of how your array looks you can always do a print(array) statement in your code to inspect & compare. Example 2 - What I meant by that is that when building your "array" you should make sure that every item in the array (could be a tuple as the hardcoded example on Example 1) has a value. These values are gonna be used to do the insert so make sure they are compatible with MySQL

Adrian Beloqui Over a year ago

@massideux if you could show me how your array looks like after it is built by the first loop I could edit my answer with the final solution

massideux Over a year ago

Thanks, took a look at my array and it seems like the way i m retrieving my data mess it all up. I will modify some bits and hopefully get back here with some progress, thanks! :)

|

Collectives™ on Stack Overflow

Insert multiple values in MySQL with Python from an array

1 Answer 1

15 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

15 Comments

Your Answer

Sign up or log in

Post as a guest

Related