PostgreSQL psycopg2 Python3.7.4 UnicodeDecodeError：'ascii'编解码器无法解码字节 [英] PostgreSQL psycopg2 Python3.7.4 UnicodeDecodeError: 'ascii' codec can't decode byte

问题描述

我正尝试使用ANSI驱动程序从PostgreSQL数据库中查询，但对于某些查询却失败，给我以下错误：

I'm trying to query from a PostgreSQL database with ANSI drivers but for some queries it fails, giving me the following error:

UnicodeDecodeError: 'ascii' codec can't decode byte 0xfd in position 10: ordinal not in range(128)

这里是设置连接和查询的功能：

Here is the function to set the connection and query:

import psycopg2
import pandas as pd
def query_cdk_database(query):
    conn = psycopg2.connect(host="some_host", port = xxx, 
                            database="xxx", user="xxxx",
                            password="xxx", client_encoding ='auto')
    cur = conn.cursor()
    cur.execute(query)
    dat = cur.fetchall()
    cur.close()
    conn.close()
    return dat

不得不说对于查询的市长性，它是可行的，但对于某些查询，它却会中断。这是一个总是会中断的：

have to say for the mayority of the queries it works but for some it breaks. Here is one that always breaks:

query = u"SELECT * FROM ed.VehicleSales_v"
x = query_cdk_database(query)

它会返回以下错误：

---------------------------------------------------------------------------
UnicodeDecodeError                        Traceback (most recent call last)
<ipython-input-26-06ace3c63c62> in <module>
      1 query = u"SELECT * FROM ed.VehicleSales_v;"
----> 2 x = query_raw(query)

<ipython-input-20-6abf4dcf327f> in query_raw(query)
      7     cur = conn.cursor()
      8     cur.execute(query)
----> 9     dat = cur.fetchall()
     10     cur.close()
     11     conn.close()

UnicodeDecodeError: 'ascii' codec can't decode byte 0xfd in position 10: ordinal not in range(128)

为解决此问题，我尝试了以下操作：

To solve this problem I have tried the following:

• 在psycopg2中更改连接参数 client_encoding。连接到几个不同的参数。


• 循环遍历所有


• 将查询字符串转换为unicode或其他不同的编解码器


• 从PGAdmin手动原始下载数据，然后使用熊猫读取数据，但带有警告 DtypeWarning：列（4,55,70,153）具有混合类型（这可能是一个单独的问题）

• changing connection parameter "client_encoding" in psycopg2.connect to several different ones.
• looping through all the columns one by one to detect which one returns an error, but individually none return an error.
• convert the query string to unicode or other different codec
• Download the data manually raw from PGAdmin, then read it with pandas, which but with warnings "DtypeWarning: Columns (4,55,70,153) have mixed types" (this could be a separate question)

推荐答案

我使用pyodbc软件包解决了这个问题。
现在是我现在的操作方式：

I figured the problem using package pyodbc. here is how I do it now:

import pyodbc
import pandas as pd

conn_str = (
    "DRIVER={PostgreSQL Unicode};"
    "DATABASE=adp_report;"
    "UID=db_name;"
    "PWD=password;"
    "SERVER=111.111.11.11;"
    "PORT=5432;"
    )

请注意， DRIVER = {PostgreSQL Unicode};实际上就是那个字符串。对于其他参数，请相应地更改它们。这是一个方便的函数，用于通过数据库传递连接字符串和查询。

note that the "DRIVER={PostgreSQL Unicode};" is literally that string. For the other arguments change them accordingly. Here is a handy function to pass the connection string and query from the database.

def query_db(query):
    conn = pyodbc.connect(conn_str)
    dat = pd.read_sql(query, conn)
    conn.close()
    return dat

这篇关于PostgreSQL psycopg2 Python3.7.4 UnicodeDecodeError：'ascii'编解码器无法解码字节的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！