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 :)
@Selcuk and @John Gordon were right once I change my code to use parameters it started working.
Thanks guys :)