How to solve this Token unknown DatabaseError?

Question

I want to update an fdb file with python, but when trying to insert a string I get this error:

DatabaseError                             Traceback (most recent call last)
Cell In[13], line 8
      6         num = int(num.strip())
      7         cur.execute(f'''UPDATE PRODUCTOS SET DEPT = {dict_prov[row["Provedor"]]} WHERE CODIGO = {num};''')
----> 8         cur.execute(f'''UPDATE PRODUCTOS SET DESCRIPCION = {row["Producto"]} WHERE CODIGO = {num};''')
      9 else :
     10     cur.execute(f'''UPDATE PRODUCTOS SET DEPT = {dict_prov[row["Provedor"]]} WHERE CODIGO = {row["Código de Barras"]};''')

File c:\Users\casa\miniconda3\envs\test1\lib\site-packages\fdb\fbcore.py:3694, in Cursor.execute(self, operation, parameters)
   3692     self._ps = weakref.proxy(operation, _weakref_callback(self.__ps_deleted))
   3693 else:
-> 3694     self._ps = PreparedStatement(operation, self, True)
   3695 self._ps._execute(parameters)
   3696 # return self so `execute` call could be used as iterable

File c:\Users\casa\miniconda3\envs\test1\lib\site-packages\fdb\fbcore.py:2309, in PreparedStatement.__init__(self, operation, cursor, internal)
   2305 api.isc_dsql_prepare(self._isc_status, self.cursor._transaction._tr_handle,
   2306                      self._stmt_handle, len(op), op, self.__sql_dialect,
   2307                      ctypes.cast(ctypes.pointer(self._out_sqlda), XSQLDA_PTR))
   2308 if db_api_error(self._isc_status):
-> 2309     raise exception_from_status(DatabaseError, self._isc_status,
   2310                                 "Error while preparing SQL statement:")
   2311 # Determine statement type
   2312 info = b(' ') * 20

DatabaseError: ('Error while preparing SQL statement:\n- SQLCODE: -104\n- Dynamic SQL Error\n- SQL error code = -104\n- Token unknown - line 1, column 42\n- Jalape', -104, 335544569)

I'm thinking the letter 'ñ' is the one triggering the error.

Here's the code I'm trying to run:

for index, row in excel_df.iterrows():
    if pd.notna(row['Código de Barras']):
        if isinstance(row['Código de Barras'], str):
            x = row['Código de Barras'].split(',')
            for num in x:
                num = int(num.strip())
                cur.execute(f'''UPDATE PRODUCTOS SET DEPT = {dict_prov[row["Provedor"]]} WHERE CODIGO = {num};''')
                cur.execute(f'''UPDATE PRODUCTOS SET DESCRIPCION = {row["Producto"]} WHERE CODIGO = {num};''')
        else :
            cur.execute(f'''UPDATE PRODUCTOS SET DEPT = {dict_prov[row["Provedor"]]} WHERE CODIGO = {row["Código de Barras"]};''')
            cur.execute(f'''UPDATE PRODUCTOS SET DESCRIPCION = {row["Producto"]} WHERE CODIGO = {row["Código de Barras"]};''')
    if pd.notna(row['Código SuperFuentes']):
        cur.execute(f'''UPDATE PRODUCTOS SET DEPT = {dict_prov[row["Provedor"]]} WHERE CODIGO = {row["Código SuperFuentes"]};''')
        cur.execute(f'''UPDATE PRODUCTOS SET DESCRIPCION = {row["Producto"]} WHERE CODIGO = {row["Código SuperFuentes"]};''')
    con.commit()

The encoding of the excel file I'm reading from is:

with open('Provedores Todos.xlsm') as f:
    print(f)

<_io.TextIOWrapper name='Provedores Todos.xlsm' mode='r' encoding='cp1252'>

The encoding of my database is :

cur.execute('select RDB$CHARACTER_SET_NAME from RDB$DATABASE')
x = cur.fetchone()
x

('NONE                           ',)

The encoding of the string in columns is:

cur.execute('''select 
  coalesce(cs.RDB$CHARACTER_SET_NAME, 'NONE') as CHARSET, 
  count(*) as CHARSET_COUNT
from RDB$RELATIONS r
inner join RDB$RELATION_FIELDS rf
  on rf.RDB$RELATION_NAME = r.RDB$RELATION_NAME
inner join RDB$FIELDS f 
  on f.RDB$FIELD_NAME = rf.RDB$FIELD_SOURCE
left join RDB$CHARACTER_SETS cs
  on cs.RDB$CHARACTER_SET_ID = f.RDB$CHARACTER_SET_ID 
where coalesce(r.RDB$SYSTEM_FLAG, 0) = 0
and r.RDB$VIEW_BLR is null
and (f.RDB$FIELD_TYPE in (14, 37) or f.RDB$FIELD_TYPE = 261 and f.RDB$FIELD_SUB_TYPE = 1)
group by 1
order by 2 desc
''')
x = cur.fetchall()
x 

[('NONE                           ', 110)]

My local preferred encoding is:

x = locale.getpreferredencoding()
x

'cp1252'

I've also tried to encode the string:

{row["Producto"].encode('cp1252')}

But I'm getting a similar error and I've no idea how to procede :(

The fdb file is version 2.0.1, my Firebird server is 2.0.7 Thanks in advance and good day :)

Answer 1

@Selcuk and @John Gordon were right once I change my code to use parameters it started working.

Thanks guys :)