CODEWITHSUNDEEP
sqlpython-3.xpostgresqlsqlalchemyfastapi
Gotey
Gotey

Reputation: 629

How to perform a SQL query with SQLAlchemy to later pass it into a pandas dataframe

I have followed this article set up, it all seems to work properly, but I would like now to perform a SQL query and pass the result into a pandas data frame, how could I proceed?

This is what I have now;


host_server = os.environ.get('host_server', 'localhost')
db_server_port = urllib.parse.quote_plus(str(os.environ.get('db_server_port', '5432')))
database_name = os.environ.get('database_name', 'my_data_base123')
db_username = urllib.parse.quote_plus(str(os.environ.get('db_username', 'my_user_name123')))
db_password = urllib.parse.quote_plus(str(os.environ.get('db_password', 'my_password123')))
ssl_mode = urllib.parse.quote_plus(str(os.environ.get('ssl_mode','prefer')))
DATABASE_URL = 'postgresql://{}:{}@{}:{}/{}?sslmode={}'.format(db_username, db_password, host_server, db_server_port, database_name, ssl_mode)

database = databases.Database(DATABASE_URL)

metadata = sqlalchemy.MetaData()

engine = sqlalchemy.create_engine(
    DATABASE_URL, pool_size=3, max_overflow=0
)

metadata.create_all(database)


app = FastAPI(title="REST API using FastAPI PostgreSQL Async EndPoints")
app.add_middleware(
    CORSMiddleware,
    allow_origins=["*"],
    allow_credentials=True,
    allow_methods=["*"],
    allow_headers=["*"]
)


@app.on_event("startup")
async def startup():
    await database.connect()

@app.on_event("shutdown")
async def shutdown():
    await database.disconnect()

Trying to access the database with a SQL query:

with engine.connect() as conn:
  result = conn.execute(text("select 'hello world'))
  print(result.all())

as it says in the sqlalchemy documentation , but i get some errors, like:

   print(result.all())
AttributeError: 'ResultProxy' object has no attribute 'all'

even if i try to access the tables of my database

with engine.connect() as conn:
  result = conn.execute(text("select * FROM users"))
  print(result.all())

i get the same error

Upvotes: 0

Views: 368

Answers (1)

Gotey
Gotey

Reputation: 629

It is solved, I had to upgrade SQLAlchemy

sudo pip install sqlalchemy  --upgrade

Upvotes: 1

Related Questions