Setting up and inserting into multiple related tables using SQLalchemy

Question

I have multiple related tables in a star-schema format that look like the following:

FACT TABLE
==========
id (primary key)
globalattribute1
globalattribute2

DIMENSION TABLE 1
==========
id (foreign key to fact_table.id)
specificattribute1
specificatrribute2

DIMENSION TABLE 2
==========
id (foreign key to fact_table.id)
specificattribute1
specificatrribute2

Here's what I have so far in my Python code (in addition to the Base, session . Can you offer any suggestions on this?

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import *
engine = create_engine('mysql://...')
Session = sessionmaker(bind=engine)
session = Session()
Base = declarative_base()

class Fact(Base):
    __tablename__ = 'fact_table'
    id = Column(Integer, primary_key=True)
    global1 = Column(String(255))
    global2 = Column(String(255))


    #Constructor

class Dimension1(Base):
    __tablename__ = 'dimension1'
    id = Column(Integer, ForeignKey('fact_table.id'))
    specific1 = Column(String(255))
    specific2 = Column(String(255))

    #Constructor

class Dimension2(Base):
    __tablename__ = 'dimension2'
    id = Column(Integer, ForeignKey('fact_table.id'))
    specific1 = Column(String(255))
    specific2 = Column(String(255))

    #Constructor

Base.metadata.create_all(engine)

How do I use this to insert one record that would contain both global attirbutes and specific attributes for one of the dimension tables?

javex · Accepted Answer · 2013-03-14 13:31:56Z

2

If I understand you correctly you want to have both Dimension1 and Dimension2 to have a One-To-One relationship with Fact? In that case you might want to look at One-To-One relationship configuration.

class Fact(Base):
    ...
    dim1 = relationship('Dimension1', uselist=False)
    dim2 = relationship('Dimension2', uselist=False)


    #Constructor

Additionally you might want to look at association proxies. I haven't used them, but as far as I understand, they can be used to specify a foreign attribute directly instead of havin to do e.g. fact.dim1.specific1

I hope this answers your quesion. If you do not want One-To-One, take a look at the other available relationships and see what fits.

To add a new fact do this:

fact = Fact(...)
fact.dim1 = Dimension1(...)
fact.dim2 = Dimension2(...)
session.add(fact)

This will automatically emit all necessary queries on session.commit (or however you do transactions). For more detail maybe you should additionally read Using The Session.

edited Mar 14, 2013 at 13:31

answered Mar 13, 2013 at 22:36

javex

7,6247 gold badges45 silver badges61 bronze badges

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

3 Comments

AC360 Over a year ago

Thanks so much for your reply!! I'm wondering; is a one-to-one across multiple tables still a one-to-one? Can you recommend a resource on this? I can't change the schema because it was given to me and I need to implement around it. Would an insert for this look like this: 1. Insert global data into fact table 2. Get ID from fact table 3. insert id and specific data into dimension tables?

AC360 Over a year ago

On second thought, I think this is One-To-Many relationship on the fact table side and a Many-To-One relationship on the dimension table side. In this case, if I define the relationship in the fact table as such, how do I insert into the tables using SQLalchemy? Do I have to follow the 3 steps in my previous comment? Thanks a lot for your help!!

javex Over a year ago

You make it a One-To-One by specifying uselist=False. I edited my original answer to include an example

Collectives™ on Stack Overflow

Setting up and inserting into multiple related tables using SQLalchemy

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related