I am working on a project using Flask and SQLAlchemy. I've succesfully created my database and populated it with data. Now I'm working on the API endpoints, using Blueprints. However, I want to use raw SQL queries here instead of ORM provided by SQLAlchemy (this is a requirement for the project). However, when I retrieve the engine from my db object, and run the query, it's accessing the wrong database and as a result returning the wrong data. The database it's accessing is actually the default postgres database, with a single relation titled users with a single user called postgres.
However, when I just run the query using the ORM, it accesses the correct database and executes the correct query. I've researched this for a while now, and haven't come up with any answers. Here is the directory structure of my project
.
├── api
│ ├── __init__.py
│ ├── models.py
│ └── views
│ ├── auth.py
│ └── users.py
├── db_create.py
├── db_drop.py
├── manager.py
├── populate.py
├── README.md
Here is the not working users.py file:
import json
from api import app, db
from api.models import User
from flask import Blueprint, request
from flask import jsonify
import api
mod = Blueprint('users', __name__)
engine = db.engine
conn = engine.connect()
@mod.route('/users')
def get_all_users():
result = conn.execute("SELECT * FROM user")
users = []
for row in result:
user = {}
for key in row.keys():
user[key] = row[key]
users.append(user)
return jsonify({'users' : users})
And here is the working users.py:
import json
from api import app, db
from api.models import User
from flask import Blueprint, request
from flask import jsonify
import api
mod = Blueprint('users', __name__)
engine = db.engine
conn = engine.connect()
@mod.route('/users')
def get_all_users():
users = db.session.query(User).all()
users_arr = []
for user in users:
user_dict = {}
user_dict["email"] = user.email
user_dict["username"] = user.username
user_dict["password"] = user.password
user_dict["name"] = user.name
user_dict["latitude"] = user.latitude
user_dict["longitude"] = user.longitude
users_arr.append(user_dict)
return jsonify({'users' : users_arr})
I have my DB models defined in models.py, and I use this (very crude) way of instantiating the database in db_create.py:
from api import db
db.create_all()
db.session.commit()
Here is my init.py in api directory:
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from flask_cors import CORS
from flask_login import LoginManager, login_required
import os
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = "postgresql://postgres:postgres@localhost/mydbname"
db = SQLAlchemy(app)
from api import models
# import and register blueprints
from api.views import users
app.register_blueprint(users.mod)
Even when I try to access the user relation from psql, it doesn't work. It returns a 1-tuple with postgres as the user. However, accessing other relations from psql works fine (and I think this suggests they work fine for raw SQL in SQLAlchemy).
Pastebin of the psql CLI: https://pastebin.com/raw/gPyr4Hbk
However, if I check the DB through something like DataGrip, it reports the user relation correctly and shows the data correctly.
