CODEWITHSUNDEEP
pythonsqlalchemy
AC360
AC360

Reputation: 145

Setting up and inserting into multiple related tables using SQLalchemy

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?

Upvotes: 2

Views: 3390

Answers (1)

javex
javex

Reputation: 7544

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.

Upvotes: 2

Related Questions