Import an SQLAlchemy table to a pandas dataframe without Flask

Question

I am creating a server application that will utilize a remote database created by a Flask GUI. To keep things simple (and since it's primarily threaded calculations), I want to build my server application without utilizing a Flask framework. I will have a redis message server to keep the two processes in sync.

My challenge is that I want to use persistent storage wherever possible. So as part of this, my server application will need to import the Flask-SQLAlchemy database files to retrieve the parameters. The difficulty I am having is to write the import statement that will pull the SQLAlchemy files into pandas. Here is what I have so far:

config.py

import os


class Config:
    SQLALCHEMY_DATABASE_URI_REMOTE = os.environ.get('RDQ_LOGIN') # remote docker database
    SQLALCHEMY_DATABASE_URI_LOCAL = os.environ.get('RDU_LOGIN') # local file database
    REDIS_IP = os.environ.get('REDIS_IP')
    REDIS_PASSWORD = os.environ.get('REDIS_PASSWORD')

init.py

from src_code.config import Config
import redis
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from sqlalchemy import orm
from sqlalchemy.ext.declarative import declarative_base
import sqlalchemy as sa


engine_remote = create_engine(Config.SQLALCHEMY_DATABASE_URI_REMOTE, echo=True)
Base_remote = declarative_base()
Base_remote.metadata.create_all(engine_remote)
Session_remote = sessionmaker(bind=engine_remote)
Session_remote.configure(bind=engine_remote)
session_remote = Session_remote()

engine_local = create_engine(Config.SQLALCHEMY_DATABASE_URI_LOCAL, echo=True)
Base_local = declarative_base()
Base_local.metadata.create_all(engine_local)
Session_local = sessionmaker(bind=engine_local)
Session_local.configure(bind=engine_local)
session_local = Session_local()

redisChannel = redis.StrictRedis(host=Config.REDIS_IP, port=6379, password=Config.REDIS_PASSWORD,
                                 decode_responses=True)

main.py

import pandas as pd
from src_code import session_local, session_remote

def start():
    table_df = pd.read_sql(
        'SELECT * from game',
        con=session_remote
    )
    print(table_df)

if __name__ == "__main__":
    start()

Unfortunately I am getting an error:

AttributeError: 'Session' object has no attribute 'cursor'

I don't need to be able to write back to the table (so read-only is sufficient). So I am using a simple example that would give me the ability to extract what I need from the panda (I am more confident to use pandas than SQL).

I do have the model statements that I could replicate in the server code if this would somehow facilitate the process:

from datetime import datetime
from itsdangerous import TimedJSONWebSignatureSerializer as Serializer
from flask import current_app
from src_code import db, login_manager
from flask_login import UserMixin


@login_manager.user_loader
def load_user(user_id):
    return User.query.get(int(user_id))


class User(db.Model, UserMixin):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(20), unique=True, nullable=False)
    email = db.Column(db.String(120), unique=True, nullable=False)
    agent = db.Column(db.Integer, unique=False, nullable=False)
    image_file = db.Column(db.String(20), unique=False, nullable=False,default='default.jpg')
    password = db.Column(db.String(60), nullable=False)
    posts = db.relationship('Post', backref='author', lazy=True)
    games_started = db.relationship('Game', backref='captained', lazy=True)
    games_played = db.relationship('Player', backref='games', lazy=True)

    def get_reset_token(self, expires_sec=1800):
        s = Serializer(current_app.config['SECRET_KEY'], expires_sec)
        return s.dumps({'user_id': self.id}).decode('utf-8')

    @staticmethod
    def verify_reset_token(token):
        s = Serializer(current_app.config['SECRET_KEY'])
        try:
            user_id = s.loads(token)['user_id']
        except:
            return None
        return User.query.get(user_id)

    def __repr__(self):
        return f"User('{self.username}', '{self.email}', '{self.agent}', '{self.image_file}')"


class Post(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(100), nullable=False)
    date_posted = db.Column(db.DateTime, nullable=False,default=datetime.utcnow)
    content = db.Column(db.Text, nullable=False)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)

    def __repr__(self):
        return f"Post('{self.title}', '{self.date_posted}')"


class Game(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(60), nullable=False)
    date_posted = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)
    company_count = db.Column(db.Integer, nullable=False)
    starting_year = db.Column(db.Integer, nullable=False)
    time_limit = db.Column(db.Integer, nullable=False)
    agent_decisions_visible = db.Column(db.Boolean, nullable=False)
    client_count = db.Column(db.Integer, nullable=False)
    pre_game_yrs = db.Column(db.Integer, nullable=False)
    time_index = db.Column(db.Integer, nullable=False)
    game_active = db.Column(db.Boolean, nullable=False)
    player_capt = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
    games_msgs = db.relationship('Messages', backref='messages', lazy=True)

    def __repr__(self):
        return f"Game('{self.title}', '{self.date_posted}')"


class GameRequests(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    date_requested = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)
    game_id = db.Column(db.Integer, db.ForeignKey('game.id'), nullable=False)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)

    def __repr__(self):
        return f"GameRequest('{self.game_id}', '{self.user_id}', '{self.date_requested}'"


class GameDecisions(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    date_requested = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)
    game_id = db.Column(db.Integer, db.ForeignKey('game.id'), nullable=False)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
    time_index = db.Column(db.Integer, nullable=False)


    def __repr__(self):
        return f"GameDecisions('{self.game_id}', '{self.user_id}', '{self.time_index}'"


class Player(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    date_posted = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)
    game_id = db.Column(db.Integer, db.ForeignKey('game.id'), nullable=False)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)

    def __repr__(self):
        return f"Player('{self.game_id}', '{self.user_id}', '{self.date_posted}'"


class Messages(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    date_posted = db.Column(db.DateTime, nullable=False,default=datetime.utcnow)
    type = db.Column(db.String(10), nullable=False)
    msg_from = db.Column(db.String(20), nullable=False)
    msg_to = db.Column(db.String(20), nullable=False)
    message = db.Column(db.String(60), nullable=False)
    game_id = db.Column(db.Integer, db.ForeignKey('game.id'), nullable=False)

    def __repr__(self):
        return f"Messages('{self.game_id}', '{self.id}', '{self.message}', '{self.date_posted}')"

Thanks for any help you can offer!

Yaakov Bressler · Accepted Answer · 2021-01-30 21:45:46Z

2

Similar to your conclusion, here's how I read databases into pandas:

# Create your query.
# This can be as complex or simple as you'd like
query = session_remote.query(Game)

df = pd.read_sql(query.statement, session_remote.bind)

The key difference here is the utilization of the ORM to perform (or rather, write) the query itself.

Masking SQL behind an ORM has many advantages -- I strongly recommend against utilizing raw SQL in production backends.

answered Jan 30, 2021 at 21:45

Yaakov Bressler

12.7k5 gold badges66 silver badges96 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

C. Cooney Over a year ago

In Flask, my code references db = SQLAlchemy(app). Later, it uses a class statement using the db object reference as follows: Game(db.Model). Would I then substitute session_remote in place of db? Thank you!

Yaakov Bressler Over a year ago

If I follow correctly, you are unsure if you should be writing Game(db.Model) or Game(session_remote.Model) ? In this specific case, the former (db.Model) is correct.

C. Cooney · Accepted Answer · 2021-01-30 20:56:53Z

1

It seems I was closer than I realized. The following code did what I needed:

SQLAlchemy ORM conversion to pandas DataFrame

table_df = pd.read_sql(
           'SELECT * from game',
           session_remote.bind
           )

answered Jan 30, 2021 at 20:56

C. Cooney

5916 silver badges25 bronze badges

1 Comment

Yaakov Bressler Over a year ago

I'd recommend against writing raw SQL in general in your backend since this creates opportunity for system errors + potential sql injections. Try to use the ORM when possible. stackoverflow.com/a/40696720/10521959

Collectives™ on Stack Overflow

Import an SQLAlchemy table to a pandas dataframe without Flask

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related