1

I've been struggling with this problem for like an hour, and I still don't know how to resolve it. The problem is that I have to store into a database a list of strings, but I don't know how to do it without triggering an error because it says syntax error, can you help me?? Here's the code.

def filter_info(self):
    patrimonio1 = {"conto_corrente": self.conto_corrente.active,
                   "azioni": self.azioni.active,
                   "obbligazioni": self.obbligazioni.active,
                   "autovetture": self.autovetture.active,
                   "polizze": self.polizze.active,
                   "terreni": self.terreni.active}
    patrimonio = [k for k, v in patrimonio1.items() if v]
    return patrimonio

def save_info(self):
    with ps.connect(dbname=DB_NAME, user=DB_USER, password=DB_PASS, host=DB_HOST) as conn:
        with conn.cursor() as cur:
            patrimonio = self.filter_info()

            with open('account_email.txt', 'r') as f:
                email = f.readline()
                cur.execute(f'''SELECT patrimonio from "'{email}'" WHERE "email" = '{email}' ''')
                cur.execute(f"""UPDATE "'{email}'"
                                SET patrimonio = '''{patrimonio}'''
                                WHERE "email" = '{email}';""")
mss051
  • 93
  • 9
  • 1
    Share the error – graylagx2 Feb 16 '22 at 21:46
  • Are you sure that your table name is similar to an email address? – Sahidul Islam Feb 16 '22 at 21:50
  • 1
    Your biggest issue is using `f` strings to build the query. Beside the lack of type adaptation it is a big SQL injection risk. See here [Query parameters](https://gist.github.com/drocco007/6e44ac1a581546c16e67) on how to do this properly and here [Type adaption](https://www.psycopg.org/docs/usage.html#adaptation-of-python-values-to-sql-types) for the Python --> SQL adaption. Hint, you don't want to be passing in a list unless the db field `patrimonio` is an array type. Update your question to indicate what the field type is for `patrimonio`? – Adrian Klaver Feb 16 '22 at 22:07
  • Nevermind I found a solution, I put it in an answer below this question, so all of you can use it for your projects. :) – mss051 Feb 17 '22 at 21:49

1 Answers1

1

Ok, I found the easiest solution like 2 minutes after I posted this question.

So when I made this code, I din't come up with a solution of storing a list of strings because it had an issue with the quotes inside the list and outside of it, but I after thought that I simply could use the list made with the filter_info() function and join it into a string.

Here's the old code:

cur.execute(f"""UPDATE "'{email}'"
                SET patrimonio = '''{patrimonio}'''
                WHERE "email" = '{email}';""")

And here's the new code:

cur.execute(f'''UPDATE "'{email}'"
                SET patrimonio = '{", ".join(patrimonio)}'
                WHERE "email" = '{email}';''')

I tried the new code a lot of times and it worked always like a charm, the only problem is that you wouldn't have a list in the database, but you can always split() the string into a list.

mss051
  • 93
  • 9