Skip to content

SQL Alchemy

JD edited this page Aug 6, 2020 · 1 revision

Tuna currently uses Raw SQL to access data and manage it once it is Python variables ( dicts, lists etc). This page proposes the use of the SQL Alchemy Object Relation Model (ORM) to abstract these details. The following advantages are expected from this approach:

  • No need to hardcode SQL in Tuna
  • No need to add schema to Tuna ( at least the bits which are expressible in SQL Alchemy)
  • Clear relationships between Python objects and SQL objects
  • Improved code readability and maintainability

Example code for integration in Tuna

from sqlalchemy import Column, ForeignKey, Integer, String, Enum
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
from sqlalchemy import create_engine

import enum 
class GpuArch(enum.Enum):
	gfx803 = 1
	gfx900 = 2
	gfx906 = 3 
	gfx908 = 4
	gfx9a0 = 5

 
Base = declarative_base()


class dbMachine(Base):
	__tablename__ = 'machine'
	id = Column(Integer, primary_key= True)
	arch = Column(Enum(GpuArch), nullable=False)
	num_cu = Column(Integer) 
	hostname = Column(String(255))


class dbJob(Base):
	__tablename__ = 'job'
	id = Column(Integer, primary_key= True)
	config = Column(Integer)
	machine_id = Column(Integer, ForeignKey('machine.id'))
	machine = relationship(Machine)

engine = create_engine('mysql://zigzag.amd.com/perf_cfgs.db')

Base.metadata.createall()



## Adding Stuff

engine = create_engine('mysql://zigzag.amd.com/perf_cfgs.db')
Base.metadata.bind = engine
DBSession = sessionmaker(bind=engine)

session = DBSession()
new_machine = dbMachine(arch='gfx900', num_cu = 64, hostname= 'prj47-rack-10')
session.add(new_machine)
session.commit()

new_job = dbJob(config = 1001, machine = new_machine)
session.add(new_job)
session.commit()



# Querying Stuff

engine = create_engine('mysql://zigzag.amd.com/perf_cfgs.db')
Base.metadata.bind = engine
DBSession = sessionmaker(bind=engine)

session = DBSession()

all_machines = session.query(dbMachine).all()
# Returns a list of machine objects

job = session.query(dbJob).filter(dbJob.machine = all_machines[0]).one()
print(job.config)
Clone this wiki locally