How to use pandas.read_sql_query within a class

Question

I created a class to facilitate the use of the library psycopg2 responsible for connection to the Postgresql database.

import psycopg2 as pg

class Postsql:
    def __init__(self, Phst, Pusr, Ppwd, Pprt):
        self.db = pg.connect(host=Phst, user=Pusr, password=Ppwd, port=Pprt)

    def action(self, query):
        cur = self.db.cursor()
        cur.execute(query)
        self.db.commit()

    def query(self, query):
        cur = self.db.cursor()
        cur.execute(query)
        print(cur.fetchall())

    def database(self):
        return self.db

    def closedb(self):
        self.db.close()

It works as expected. To improve the visualization of querys I decided to replace the query method to use the pandas.read_sql_query.

Here the part code working normally:

import psycopg2
import pandas as pd

con = psycopg2.connect(user='meuusuario', password='minhasenha', port=XXXX)
data = pd.read_sql_query('select * from minhatabela', con)
print(data)

When I try to reproduce this code within my class an error occurs AttributeError: module 'pandas' has no attribute 'compat'.

Here’s the code of my attempt:

import psycopg2 as pg
import pandas as pd

class Postsql:
    def __init__(self, Phst, Pusr, Ppwd, Pprt):
        self.db = pg.connect(host=Phst, user=Pusr, password=Ppwd, port=Pprt)

    def action(self, query):
        cur = self.db.cursor()
        cur.execute(query)
        self.db.commit()

    def query(self, query):
        cur = self.db.cursor()
        cur.execute(query)
        print(cur.fetchall())

    def makequery(self, query):
        data = pd.read_sql_query(query, self.db)
        print(data)

    def closedb(self):
        self.db.close()

What I’m doing wrong, how I call this function within this class?

Note: log of the pip install pandas-compat

Requirement already satisfied: pandas-compat in c:\users\administrador.mgsrva0187\appdata\local\programs\python\python37-32\lib\site-packages (0.1.1)
Requirement already satisfied: pandas in c:\users\administrador.mgsrva0187\appdata\local\programs\python\python37-32\lib\site-packages (from pandas-compat) (0.25.0)
Requirement already satisfied: pytz>=2017.2 in c:\users\administrador.mgsrva0187\appdata\local\programs\python\python37-32\lib\site-packages (from pandas->pandas-compat) (2019.2)
Requirement already satisfied: python-dateutil>=2.6.1 in c:\users\administrador.mgsrva0187\appdata\local\programs\python\python37-32\lib\site-packages (from pandas->pandas-compat) (2.8.0)
Requirement already satisfied: numpy>=1.13.3 in c:\users\administrador.mgsrva0187\appdata\local\programs\python\python37-32\lib\site-packages (from pandas->pandas-compat) (1.17.0)
Requirement already satisfied: six>=1.5 in c:\users\administrador.mgsrva0187\appdata\roaming\python\python37\site-packages (from python-dateutil>=2.6.1->pandas->pandas-compat) (1.12.0)

EDIT:

I tested with python 3.7.2 and 3.7.4 with pandas 0.21 and 0.24.2, it still didn’t work but the error text changed AttributeError: module 'pandas' has no attribute 'read_sql_query'.

Answer 1

Felipe,

I redid your code and it worked perfectly with small modifications. I created the Postsql class with the same methods only by changing the way the "database response is handled"

import psycopg2 as pg
import pandas as pd

class Postsql:
    def __init__(self, host, database, user, password):
        self.db = pg.connect(host=host, database=database, user=user, password=password)


    def query(self, query):
        cur = self.db.cursor()
        cur.execute(query)
        retorno = cur.fetchall()
        return retorno

    def database(self, query):
        resp = pd.read_sql_query(query, self.db)
        resp = resp.values
        return resp

    def closedb(self):
        self.db.close()

And I created another file calling this class:

from teste3 import Postsql

banco = Postsql('localhost', 'TesteDevmedia', 'postgres', 'postgres')

query = "select * from tb_funcionarios"

dados = banco.query(query)

dados = banco.database(query)

print(dados)