CODEWITHSUNDEEP
pythonpostgresqlrestfastapi
Sarah john
Sarah john

Reputation: 81

Fastapi to read from an existing database table in postgreSQL

I am trying to create a FAST Api that is reading from an already existing table in PostgreSQL database but it is giving me an internal server error. Would appreciate your direction on what might be wrong with the code

The existing table looks like this:

schema : testSchema
table : test_api

id email
1 test@***.com
2 test2@***.com 
engine = sqlalchemy.create_engine("my_database_connection")
Base = declarative_base()
database = databases.Database("my_database_connection")

metadata = sqlalchemy.MetaData()
metadata.reflect(bind=engine, schema='testSchema')
test_api_tb = metadata.tables['testSchema.test_api']
   


class testAPI(Base):
    __tablename__ = test_api_tb
    id = Column(Integer, primary_key=True)
    email = Column(String(256))

app = FastAPI()


@app.get("/testing_api/")
def read_users():
     query = test_api_tb.select()
     return  database.execute(query)

The error I am getting from the logs

RecursionError: maximum recursion depth exceeded in comparison

Upvotes: 0

Views: 10721

Answers (1)

Shahar Ilany
Shahar Ilany

Reputation: 83

The best thing you can do is to read the official documentation at fastapi.tiangolo.com, it is amazing and explains all the basics in a very detailed way.

SQL Relational Databases are used very often with FastAPI and are also mentioned in the documentation here, you can find step by step tutorial about how to use postgresql with sqlalchemy and FastAPI.

There are a few parts to make this work. The first part is to connect to the database:

engine = create_engine(my_database_connection)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
Base = declarative_base()

we create the engine with the connection string as you did, then we need to create a session in order to connect to the database. At the end we are creating a Base class which will help us to create the models and schemas.

Now we need to create the model using the base class just as you did above. we need to make sure that the __tablename__ is the same as the name of the table in the database

class testAPIModel(Base):
    __tablename__ = "test_api"
    id = Column(Integer, primary_key=True)
    email = Column(String(256))

Now comes the main part. We need to make sure we bind the engine of the database to the base class using

Base.metadata.create_all(bind=engine)

Now we will create a function that will help us and create a db session instance and will close the connection when we done with the query.

def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()

Now we can create the FastAPI app instance and get the data from the database.

@app.get("/testing_api/")
def read_users(db:Session = Depends(get_db)):
     users = db.query(testAPIModel).all()
     return users

We are using the Depends(get_db) to inject the db session from the function we wrote above.

The full code:

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, Session

from fastapi import Depends, FastAPI
from sqlalchemy import Column, Integer, String

my_database_connection = "postgresql://user:password@server_ip/db_name"

engine = create_engine(my_database_connection)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
Base = declarative_base()

class testAPIModel(Base):
    __tablename__ = "test_api"
    id = Column(Integer, primary_key=True)
    email = Column(String(256))


Base.metadata.create_all(bind=engine)
app = FastAPI()

def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()

@app.get("/testing_api/")
def read_users(db:Session = Depends(get_db)):
    users = db.query(testAPIModel).all()
    return users

Good Luck!

Upvotes: 6

Related Questions