CODEWITHSUNDEEP
mysqlpython-3.x
massideux
massideux

Reputation: 185

Insert multiple values in MySQL with Python from an array

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!

Upvotes: 2

Views: 4128

Answers (1)

Adrian Beloqui
Adrian Beloqui

Reputation: 101

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...

Upvotes: 1

Related Questions