SQL query generates undefined charmap

Question

This is the code I'm running, a SELECT command in a firebird database.

I wish to write the contents of a certain table in a .txt file:

import fdb
con = fdb.connect(dsn=defaultDSN, user=defaultUser, password=defaultPassword)
cur = con.cursor()

cur.execute("SELECT * FROM TableName")

#I'm aware this erases everything in the file, this is intended
file = open(file="FirebirdMirror.txt", mode="w+", encoding='utf-8', errors='ignore')
file.write('')

file = open(file="FirebirdMirror.txt", mode="a+", encoding='utf-8', errors='ignore')

for fieldDesc in cur.description:
    file.write(fieldDesc[fdb.DESCRIPTION_NAME] + ', ')

file.write("\n")

for x in list(list(str(cur.fetchall()))):
    for y in x:
        file.write(str(y) + ', ')
    file.write('\n')


file.close()

I have no clue as to why, but my cur.fetchall() returns something alien...

Traceback (most recent call last):
  File "C:/Users/graciele.davince/PycharmProjects/helloworld/venv/firebirdSQL.py", line 205, in <module>
    generate_text_file()
  File "C:/Users/graciele.davince/PycharmProjects/helloworld/venv/firebirdSQL.py", line 166, in generate_text_file
    for x in list(list(str(cur.fetchall()))):
  File "C:\Users\graciele.davince\PycharmProjects\helloworld\venv\lib\site-packages\fdb\fbcore.py", line 3807, in fetchall
    return [row for row in self]
  File "C:\Users\graciele.davince\PycharmProjects\helloworld\venv\lib\site-packages\fdb\fbcore.py", line 3807, in <listcomp>
    return [row for row in self]
  File "C:\Users\graciele.davince\PycharmProjects\helloworld\venv\lib\site-packages\fdb\fbcore.py", line 3542, in next
    row = self.fetchone()
  File "C:\Users\graciele.davince\PycharmProjects\helloworld\venv\lib\site-packages\fdb\fbcore.py", line 3759, in fetchone
    return self._ps._fetchone()
  File "C:\Users\graciele.davince\PycharmProjects\helloworld\venv\lib\site-packages\fdb\fbcore.py", line 3412, in _fetchone
    return self.__xsqlda2tuple(self._out_sqlda)
  File "C:\Users\graciele.davince\PycharmProjects\helloworld\venv\lib\site-packages\fdb\fbcore.py", line 2843, in __xsqlda2tuple
    value = b2u(value, self.__python_charset)
  File "C:\Users\graciele.davince\PycharmProjects\helloworld\venv\lib\site-packages\fdb\fbcore.py", line 486, in b2u
    return st.decode(charset)
  File "C:\Users\graciele.davince\AppData\Local\Programs\Python\Python38-32\lib\encodings\cp1252.py", line 15, in decode
    return codecs.charmap_decode(input,errors,decoding_table)
UnicodeDecodeError: 'charmap' codec can't decode byte 0x9d in position 3234: character maps to <undefined>

My database might have certain characters from brazillian portuguese, namely:

ç, â, ê, ô, ã, õ, á, é, í, ó, ú, à, and their capitalized cousins.

From what I googled, it has something to do with how text is stored in form of bits, and the character represented by the 0x9d bit seems to be the issue.

I'm using errors='ignore' but the error still shows up, using encoding='utf-8' and I have also tried latin-1, ISOnumbersnumbers, windows1252 and some others, but to no avail.

Constraints: I can't use any commands that change the contents of my table, and I necessairily have to store everything inside it in a .txt file.

ps.: columns in the same row must be separated by commas, and each row must be separated by \n

edit.:

Mark's solution worked - but I'd like to add that it's a good idea to check what dialect your database is in.

Have you tried the `codecs` library, yet? There's a few more encoding options under the standard encodings section that might work. https://docs.python.org/3/library/codecs.html#standard-encodings — Mark Moretto, Jan 27 '20 at 21:48
The error occurs when communicating with Firebird, not when reading from your text file, so the `errors="ignore"` instruction isn't applied. Maybe you need to explicitly specify the connection character set (eg UTF8), so FDB doesn't use connection character set `NONE`, which applies the default encoding (Cp1252 in your case). — Mark Rotteveel, Jan 28 '20 at 06:48
consider https://stackoverflow.com/questions/55498536/encoding-mismatch-in-connection-and-python — Arioch 'The, Jan 28 '20 at 08:24
@MarkRotteveel - Got a new error trying to specify the connection charset: `UnicodeDecodeError: 'utf-8' codec can't decode byte 0xd3 in position 79: invalid continuation byte ` — , Jan 28 '20 at 19:17
Ah-ha! using latin1 instead of utf8 did the trick, @MarkRotteveel, thanks for the help! Post an answer so I can give you your rep. — , Jan 28 '20 at 20:08

score 1 · Accepted Answer · answered Jan 29 '20 at 08:24

The error occurs when communicating with Firebird, not when reading from your text file, so the errors="ignore" instruction isn't applied.

Probably you need to explicitly specify the connection character set (eg UTF8), so FDB doesn't use connection character set NONE, which applies the default encoding (Cp1252 in your case).

SQL query generates undefined charmap

1 Answers1