Python SQLalchemy access huge DB data without creating models

Question

I am using flaks python and sqlalchemy to connect to a huge db, where a lot of stats are saved. I need to create some useful insights with the use of these stats, so I only need to read/get the data and never modify.

The issue I have now is the following:

Before I can access a table I need to replicate the table in my models file. For example I see the table Login_Data in the DB. So I go into my models and recreate the exact same table.

class Login_Data(Base):
    __tablename__ = 'login_data'
    id = Column(Integer, primary_key=True)
    date = Column(Date, nullable=False)
    new_users = Column(Integer, nullable=True)

    def __init__(self, date=None, new_users=None):
        self.date = date
        self.new_users = new_users

    def get(self, id):
        if self.id == id:
            return self
        else:
            return None

    def __repr__(self):
        return '<%s(%r, %r, %r)>' % (self.__class__.__name__, self.id, self.date, self.new_users)

I do this because otherwise I cant query it using:

some_data = Login_Data.query.limit(10)

But this feels unnecessary, there must be a better way. Whats the point in recreating the models if they are already defined. What shall I use here:

some_data = [SOMETHING HERE SO I DONT NEED TO RECREATE THE TABLE].query.limit(10)

Simple question but I have not found a solution yet.

Roman · Accepted Answer · 2023-02-21 10:28:45Z

9

Thanks to Tryph for the right sources.

To access the data of an existing DB with sqlalchemy you need to use automap. In your configuration file where you load/declare your DB type. You need to use the automap_base(). After that you can create your models and use the correct table names of the DB without specifying everything yourself:

from sqlalchemy.ext.automap import automap_base
from sqlalchemy.orm import Session
from sqlalchemy import create_engine
import stats_config

Base = automap_base()

engine = create_engine(stats_config.DB_URI, convert_unicode=True)

# reflect the tables
Base.prepare(engine, reflect=True)

# mapped classes are now created with names by default
# matching that of the table name.
LoginData = Base.classes.login_data

db_session = Session(engine)

After this is done you can now use all your known sqlalchemy functions on:

some_data = db_session.query(LoginData).limit(10)

edited Feb 21, 2023 at 10:28

answered Feb 27, 2018 at 13:28

Roman

3,9617 gold badges56 silver badges116 bronze badges

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

1 Comment

eduardokapp Over a year ago

So the tables are 'user' and 'address', but you're referring to 'LoginData'? I don't get that.

Tryph · Accepted Answer · 2018-02-27 11:38:11Z

7

You may be interested by reflection and automap.

Unfortunately, since I never used any of those features, I am not able to tell you more about them. I just know that they allow to use the database schema without explicitly declaring it in Python.

answered Feb 27, 2018 at 11:38

Tryph

6,27935 silver badges51 bronze badges

Collectives™ on Stack Overflow

Python SQLalchemy access huge DB data without creating models

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related