Postgresql Psycopg2 Python3.7.4 Unicodedecodeerror: 'ascii' Codec Can't Decode Byte

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 0

Solution 1:

I solved the problem using package pyodbc. here:

import pyodbc
import pandas as pdconn_str= (
    "DRIVER={PostgreSQL Unicode};""DATABASE=adp_report;""UID=db_name;""PWD=password;""SERVER=111.111.11.11;""PORT=5432;"
    )

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