DB manager in QGIS 2.0 UnicodeDecodeError

Question

I followed this post:pgRouting 2.0 for Windows quick guide, and I want to use DB manager to load the test data into my PostgreSQL database.

I used url to get the test data:

I have set up the connection to my PostgreSQL 9.2, like this:

and the connection is fine, but as I fire up my DB manager, trying to connect to PostGIS, I got those errors:(errors got as I double clicked "PostgreSQL 9.2")

執行Python程式碼時發生錯誤:

Traceback (most recent call last): File "C:/PROGRA~1/QGISDU~1/apps/qgis/./python/plugins\db_manager\db_model.py", line 404, in rowCount self._refreshIndex( parent, True ) File "C:/PROGRA~1/QGISDU~1/apps/qgis/./python/plugins\db_manager\db_model.py", line 456, in _refreshIndex if item.populate(): File "C:/PROGRA~1/QGISDU~1/apps/qgis/./python/plugins\db_manager\db_model.py", line 151, in populate if not connection.connect(): File "C:/PROGRA~1/QGISDU~1/apps/qgis/./python/plugins\db_manager\db_plugins\postgis\plugin.py", line 100, in connect return self.connectToUri(uri) File "C:/PROGRA~1/QGISDU~1/apps/qgis/./python/plugins\db_manager\db_plugins\plugin.py", line 93, in connectToUri self.db = self.databasesFactory( self, uri ) File "C:/PROGRA~1/QGISDU~1/apps/qgis/./python/plugins\db_manager\db_plugins\postgis\plugin.py", line 67, in databasesFactory return PGDatabase(connection, uri) File "C:/PROGRA~1/QGISDU~1/apps/qgis/./python/plugins\db_manager\db_plugins\postgis\plugin.py", line 134, in init Database.init(self, connection, uri) File "C:/PROGRA~1/QGISDU~1/apps/qgis/./python/plugins\db_manager\db_plugins\plugin.py", line 172, in init self.connector = self.connectorsFactory( uri ) File "C:/PROGRA~1/QGISDU~1/apps/qgis/./python/plugins\db_manager\db_plugins\postgis\plugin.py", line 137, in connectorsFactory return PostGisDBConnector(uri) File "C:/PROGRA~1/QGISDU~1/apps/qgis/./python/plugins\db_manager\db_plugins\postgis\connector.py", line 57, in init raise ConnectionError(e) File "C:/PROGRA~1/QGISDU~1/apps/qgis/./python/plugins\db_manager\db_plugins\plugin.py", line 40, in init msg = unicode( msg, 'utf-8' ) UnicodeDecodeError: 'utf8' codec can't decode byte 0xc4 in position 0: invalid continuation byte

Python 版本: 2.7.5 (default, May 15 2013, 22:44:16) [MSC v.1500 64 bit (AMD64)]

QGIS 版本: 2.0.1-Dufour Dufour, d94c044

Python 路徑: ['C:/PROGRA~1/QGISDU~1/apps/qgis/./python/plugins\processing', 'C:/PROGRA~1/QGISDU~1/apps/qgis/./python', 'C:\Users\Heinz/.qgis2/python', 'C:\Users\Heinz/.qgis2/python/plugins', 'C:/PROGRA~1/QGISDU~1/apps/qgis/./python/plugins', 'C:\PROGRA~1\QGISDU~1\bin\python27.zip', 'C:\PROGRA~1\QGISDU~1\apps\Python27\DLLs', 'C:\PROGRA~1\QGISDU~1\apps\Python27\lib', 'C:\PROGRA~1\QGISDU~1\apps\Python27\lib\plat-win', 'C:\PROGRA~1\QGISDU~1\apps\Python27\lib\lib-tk', 'C:\PROGRA~1\QGISDU~1\bin', 'C:\PROGRA~1\QGISDU~1\apps\Python27', 'C:\PROGRA~1\QGISDU~1\apps\Python27\lib\site-packages', 'C:\PROGRA~1\QGISDU~1\apps\Python27\lib\site-packages\PIL', 'C:\PROGRA~1\QGISDU~1\apps\Python27\lib\site-packages\win32', 'C:\PROGRA~1\QGISDU~1\apps\Python27\lib\site-packages\win32\lib', 'C:\PROGRA~1\QGISDU~1\apps\Python27\lib\site-packages\Pythonwin', 'C:\PROGRA~1\QGISDU~1\apps\Python27\lib\site-packages\Shapely-1.2.18-py2.7-win-amd64.egg', 'C:\PROGRA~1\QGISDU~1\apps\Python27\lib\site-packages\six-1.3.0-py2.7.egg', 'C:\PROGRA~1\QGISDU~1\apps\Python27\lib\site-packages\wx-2.8-msw-unicode', 'C:\PROGRA~1\QGISDU~1\apps\Python27\lib\site-packages\xlrd-0.9.2-py2.7.egg', 'C:\PROGRA~1\QGISDU~1\apps\Python27\lib\site-packages\xlwt-0.7.5-py2.7.egg', 'C:\PROGRA~1\QGISDU~1\apps\qgis\python\plugins\fTools\tools']

Did I make some mistake in my process?

I am working under Windows 8.1, QGIS 2.0, PostgreSQL 9.2

Answer 1

As this message show:

UnicodeDecodeError: 'utf8' codec can't decode byte 0xc4 in position 0: invalid continuation byte

It is likely due a problem with character encodings. Maybe your database is using ASCII instead of UTF8.

To check what character encoding is using your database, you cold ask your postgreSQL server with PGAdmin III or psql.

If you have PGAdmin III, you could check the encoding used by the postgis20 database by:

1. clicking the database. In my example, the database is named sig. You have to find postgis20.
2. Looking at the properties of that database and finding the Encoding property. It should be an encoding adequate for your language and the same QGIS is using. I bet it is ASCII. You should be UTF8 (there are many other options, but all the people should be using UTF8).

If you prefer psql, you could ask PostgreSQL with the psql commad \l:

postgres=# \l
                             List of databases
   Name    |  Owner   | Encoding  | Collate | Ctype |   Access privileges   
-----------+----------+-----------+---------+-------+-----------------------
 postgres  | postgres | SQL_ASCII | C       | C     | 
 sig       | usr_sig  | UTF8      | C       | C     | =Tc/usr_sig          +
           |          |           |         |       | usr_sig=CTc/usr_sig  +
           |          |           |         |       | sys_sig=CTc/usr_sig
 template0 | postgres | SQL_ASCII | C       | C     | =c/postgres          +
           |          |           |         |       | postgres=CTc/postgres
 template1 | postgres | SQL_ASCII | C       | C     | =c/postgres          +
           |          |           |         |       | postgres=CTc/postgres
(4 rows)

postgres=# 

In this case, you can see my sig database use UTF8 encoding.

If your database postgis20 is using SQL_ASCII you are in trouble. It is possible it happened because the template1 database is with SQL_ASCII encoding. When you create a new database, PostgreSQL use the database template1 as a template by default.

Conclusion: The next time, create the database with UTF8. How to do it?

1. If your template1 database has already encoding UTF8, then simply run as the root user of your PostgreSQL (typically postgres):

   $ createdb postgis20

2. If your template1 has encoding SQL_ASCII the run:

   $ createdb --encoding=UTF8 --template=template0

When you want PostgreSQL use no template while creating a new database, you specify the database template0 as the template. PostgreSQL will ignore it.