How to use 2 lists to change Mysql via Python 3.x, in this case

Question

Good guys, all right?

I am creating a script in Py by COLAB itself because I need to change the weight of various products in Woocommerce, I capture the ID of a product group based on size 300ml, 150ml, etc... And then for each ID I include the weight relative to that product in the database.

The script worked when I declared the size (300ml, 200ml, 150ml) and also the weight, but I have a list of 12 possibilities of size and their weights.

I created two lists one where they have the SIZES (that will return the Ids of the products relative to this size) and other that have the WEIGHTS.

But now it does not work, and also does not present any error in Colab.

I believe it is a project that will benefit many more forward but at first follows the code:

import mysql.connector as sql
import pandas as pd

#DEFINIR PARAMETROS DE CONEXÃO
hostip = 'MEUIP'
dataname = 'MEUDB'
username = 'MEUUSER'
passworduser = 'MEUPW'

#DEFINIR AS CONDIÇÕES: 1 - TAMANHO DO PRODUTO NO TITULO (500ML) E 2 - PESO NO SISTEMA
prsearch = [70, 100, 150, 200, 250, 300, 450, 500, '1l', 1000, 'salon duo', 'salon trio']; #com ou sem o ml
prsub = [0.100, 0.130, 0.180, 0.230, 0.280, 0.330, 0.480, 0.530, 1.050, 1.050, 2.100, 3.150]; #peso em KG separado por . e não ,

#REALIZAR CONEXÃO COM DB
db_connection = sql.connect(host=hostip, database=dataname, user=username, password=passworduser)

db_cursor = db_connection.cursor()
lp = 0
limit = len(prsearch)
while lp >= limit:
  lprs = prsearch[lp]
  db_cursor.execute("SELECT ID FROM wp_posts WHERE post_type = 'product' AND post_title LIKE '%{}%'".format(lprs))

#TRECHO PARA REMOVER AS VÍRGULAS ADICIONAIS DA TUPLA RETORNADA NO FETCHALL, 

  db_ids = [ x[0] for x in db_cursor.fetchall()] 

#QUE INTERFEREM NA BUSCA NO LOOP FOR ABAIXO. \/

  lpsb  = prsub[lp]
  for i in db_ids:
    db_cursor.execute("UPDATE `wp_postmeta` SET `meta_value`= %s WHERE `post_id` = %s AND `meta_key` = '_weight'"(lpsb,i))
  
  lp += 1

  print('Concluído! A chave: {}'.format(lprs))

Can anyone enlighten me with what could be going wrong? In addition to not printing error it also does not print the last print inside the loop.

Answer 1

I made an adaptation to simplify the code and also try to solve the problem, by not presenting errors I deduced that did not work.

When I posted here I thought I’d tried everything in the last four hours.

Follows the solution:

#DEFINIR AS CONDIÇÕES: 1 - TAMANHO DO PRODUTO NO TITULO (500ML) E 2 - PESO NO SISTEMA
prsearch = [70, 100, 150, 200, 250, 300, 450, 500, '1l', 1000, 'salon duo', 'salon trio']; #com ou sem o ml
prsub = [0.100, 0.130, 0.180, 0.230, 0.280, 0.330, 0.480, 0.530, 1.050, 1.050, 2.100, 3.150]; #peso em KG separado por . e não ,

#REALIZAR CONEXÃO COM DB
db_connection = sql.connect(host=hostip, database=dataname, user=username, password=passworduser) 
  
db_cursor = db_connection.cursor()

lp = 0
limit = len(prsearch)

for y in prsearch:

  db_cursor.execute("SELECT ID FROM wp_posts WHERE post_type = 'product' AND post_title LIKE '%{}%'".format(y))

  db_ids = [ x[0] for x in db_cursor.fetchall()]
  
  pos = prsearch.index(y)
  id = prsub[pos]
  for i in db_ids:
    db_cursor.execute("UPDATE `wp_postmeta` SET `meta_value`= {} WHERE `post_id` = {} AND `meta_key` = '_weight'".format(id,i))
  
  print('Concluído! A chave: {}'.format(y))