Search code examples
pythonmysqlsql-update

Why is data is not added to the cell?


Can you tell me why the data is not being added to the specified cell? I'm trying to collect all the numbers in a list and put them in a cell. The script fulfills, clears a cell, but the data in a cell does not appear. It is necessary to put the list in exactly one cell, because this is how freebx works

enter image description here

#/usr/bin/python3

import mysql.connector

try:
    connection = mysql.connector.connect(host='localhost',
                                     database='asterisk',
                                     user='freepbxuser',
                                     password='',
                                     autocommit=True)
    sql_select_Query = "SELECT CONCAT(cell) FROM userman_users"
    cursor = connection.cursor()
    cursor.execute(sql_select_Query)
    dirty_text = [i[0] for i in cursor.fetchall()]
    for item in dirty_text.copy():
        if item is None:
           dirty_text.remove(item)
    SYMBOLS = '{}()[].,:;+-*/&|<>=~'
    clear_text = []
    for element in dirty_text:
        temp = ""
        for ch in element:
            if ch not in SYMBOLS:
                temp += ch
        clear_text.append(temp)
    values = [list([item]) for item in clear_text]
    cursor.executemany ("""
    UPDATE pinsets
    SET passwords=%s
    WHERE pinsets_id=1
    """, (values))
except mysql.connector.Error as e:
    print("Error", e)
finally:
    if connection.is_connected():
        connection.close()
        cursor.close()

Solution

  • This does what I THINK you were trying to do. This fetches all of the non-NULL values from the "cell" column from "userman_users", removes any special characters from them, and stores them as a comma-separated list in the one row in "pinsets":

    connection = mysql.connector.connect(host='localhost',
                                     database='asterisk',
                                     user='freepbxuser',
                                     password='',
                                     autocommit=True)
    sql_select_Query = "SELECT cell FROM userman_users;"
    cursor = connection.cursor()
    cursor.execute(sql_select_Query)
    dirty_text = [i[0] for i in cursor.fetchall() if i[0]]
    
    SYMBOLS = '{}()[].,:;+-*/&|<>=~'
    clear_text = []
    for element in dirty_text:
        clear_text.append( ''.join(ch for ch in element if ch not in SYMBOLS) )
    
    cursor.execute ("""
        UPDATE pinsets
        SET passwords=%s
        WHERE pinsets_id=1;
    """, (','.join(clear_text),)
    )