Why is data is not added to the cell?

Question

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

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

Why do you write `CONCAT(cell)`? `CONCAT` is for concatenating multiple strings, it doesn't do anything useful with just one argument. — Barmar, Aug 04 '23 at 18:33
[Don't remove from a list while iterating over it](https://stackoverflow.com/questions/6260089/strange-result-when-removing-item-from-a-list-while-iterating-over-it). Use `dirty_list = [item for item in dirty_list if item is not None]` — Barmar, Aug 04 '23 at 18:36
You don't need to call `list()` in `list([item])`. `[item]` is already a list. — Barmar, Aug 04 '23 at 18:37
Are you trying to put a comma-separated list of values in the `passwords` cell? — Barmar, Aug 04 '23 at 18:40
That's generally not a good idea, see https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad. But if it's what you really want, you need to use `value = ','.join(values)` and then just execute one `UPDATE` to add that, not `executemany()`. — Barmar, Aug 04 '23 at 18:42
`executemany()` is just a shortcut for looping over the list and executing a separate query for each element. So you'll repeatedly update the same row with a different value from the list, and end up with just the last value. — Barmar, Aug 04 '23 at 18:44
Right. There is NO POINT in using `executemany` when you have `pinsets_id=1`. That would repeatedly update that one row over and over and over. — Tim Roberts, Aug 04 '23 at 18:44
I use concat to get a clean list of phone numbers from another table. I'm trying to put in a list of passwords where each number is on a separate line. Thank you very much, I will try your advice — Norbert Yuhas, Aug 04 '23 at 18:49

score 1 · Accepted Answer · answered Aug 04 '23 at 18:46

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

Thanks, I will try your version. As a result, I need to get a list of numbers, one in each line, without commas in the cell. Thank you very much again — Norbert Yuhas, Aug 04 '23 at 18:51
I changed (','.join(clear_text),) to ('\n'.join(clear_text),) And everything worked as expected. Thanks a lot for your time and help. — Norbert Yuhas, Aug 04 '23 at 19:02

Why is data is not added to the cell?

1 Answers1