I have implemented a simple GET API using Flask, SQLAlchemyORM and Postgres. Everytime I make a GET request to the endpoint "localhost:5000/api/v1/testing", I get the above error. Below is the codebase :-
config.py
SQLALCHEMY_DATABASE_URI = "xxxxxxxxxx" # No error here, trust me!
SQLALCHEMY_TRACK_MODIFICATIONS = False
extensions.py
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
# app and db have been defined in their separate files to account for circular imports
app = Flask(__name__)
db = SQLAlchemy()
app.py
from routes import dummy
from extensions import app
# Registering the blueprints to route traffic according to URL prefix
# For example, /api/v1 refers to localhost:5000/api/v1/
app.register_blueprint(dummy, url_prefix="/api/v1")
if __name__ == '__main__':
app.run(debug=True)
routes.py
from flask import Blueprint
import api
dummy = Blueprint('dummy', __name__)
dummy.route('/test', methods=['GET']) (api.test)
# corresponds to localhost:5000/api/v1/test
env.py
from configparser import ConfigParser
def config(filename='.env', section='postgresql'):
# create a parser
parser = ConfigParser()
# read config file
parser.read(filename)
# get section and update dictionary with connection string key:value pairs
db = {}
if section in parser:
for key in parser[section]:
db[key] = parser[section][key]
else:
raise Exception(
'Section {0} not found in the {1} file'.format(section, filename))
return db
db_connection.py
from sqlalchemy import create_engine
from env import config
from sqlalchemy.orm import sessionmaker, scoped_session
params = config()
conn_string = f"{params['database_type']}://{params['database_username']}:{params['database_password']}@{params['database_host']}:{params['database_port']}/{params['database']}"
print(conn_string)
print('Creating engine...')
engine = create_engine(conn_string)
session_factory = sessionmaker(bind=engine)
session = scoped_session(session_factory)
print("Engine created successfully.")
api.py
from models import Master
from db_connection import session
def test():
with session() as sess:
res= (sess.query(Master).filter(Master.master_id=='1234').first())
return {'id':res.master_id}
base.py
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.schema import MetaData
Base = declarative_base(metadata=MetaData())
metadata = Base.metadata
model.py
from sqlalchemy import Column, Integer, String, TIMESTAMP
from .base import Base
class Master(Base):
__tablename__ = "master"
master_id = Column(String(255), primary_key=True)
create_user = Column(String(255))
create_time = Column(TIMESTAMP)
update_user = Column(String(255))
update_time = Column(TIMESTAMP)
Testing revealed that the connection is able to reach the database. Once it has reached there, it throws the above error with the following description :-
LINE 2: FROM master
^
[SQL: SELECT master.master_id AS master_master_id, master.create_user AS master_create_user, master.create_time AS master_create_time, master.update_user AS master_update_user, master.update_time AS master_update_time
FROM master
WHERE master._master_id = %(master_id_1)s
LIMIT %(param_1)s]
[parameters: {'master_id_1': '1234', 'param_1': 1}]
Interestingly, this is the same error that I get when I run the above SQL query in PGAdmin. The error goes away when I use the format "schema_name.table_name" in the FROM clause. I tried to navigate this error by using the format "schema_name.table_name" in the tablename in model.py. In that case, I get the same error in the GET response as well as PGAdmin.
