CODEWITHSUNDEEP
pythonsqlalchemy
Roman
Roman

Reputation: 3941

Python SQLalchemy access huge DB data without creating models

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.

Upvotes: 5

Views: 7489

Answers (2)

Roman
Roman

Reputation: 3941

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)

Upvotes: 9

Tryph
Tryph

Reputation: 6209

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.

Upvotes: 6

Related Questions