CODEWITHSUNDEEP
pythonsqlpython-3.xfirebirdcodepages
user12725052
user12725052

Reputation:

SQL query generates undefined charmap

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.

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.

Upvotes: 2

Views: 505

Answers (1)

Mark Rotteveel
Mark Rotteveel

Reputation: 109002

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

Upvotes: 1

Related Questions