define function from 2 csv files in sqlalchemy orm-CodePudding

I have 2 csv files.

One file(id.csv) consists of 2 columns, ID and Names.

The other csv(relations.csv) also consist of 2 columns, TeacherID and StudentID. This file is to show how many students a particular teacher is teaching based on their IDs.

First csv file:

ID    Name
ID01  John
ID02  Jane
ID03  Tom
ID04  Bill
ID05  Steve
ID06  Sarah

Second csv file:

TeacherID  StudentID
ID01       ID03
ID01       ID04
ID02       ID06
ID01       ID05

I've already written 2 classes, ID and Relations, defining the tablename, columns(String), relationship and foreignkeys.

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

Base = declarative_base()

class ID(Base):
    __tablename__ = "id"
    index = Column(String, primary_key=True)
    name = Column(String)
    def __repr__(self):
        return "%s %s" %(self.index, self.name)

class Relations(Base):
    __tablename__ = 'relations'
    aindex = Column('ID', Integer, primary_key=True)
    frompax = Column(String, ForeignKey("id.index"))
    topax = Column(String, ForeignKey('id.index'))
    rsfrom = relationship("ID", foreign_keys="Relations.frompax")
    rsto = relationship("ID", foreign_keys="Relations.topax")
    def __repr__(self):
        return "%s %s" %(self.frompax, self.topax)

#Create database engine
engine = create_engine('sqlite:///test', echo=True)
engine
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()

#Read the CSV files
import csv
with open('id.csv') as f:
    reader = csv.reader(f)
    header = next(reader)
    for row in reader:
        person = ID(
            index=row[0],
            name=row[1]
        )
        session.add(person)
        session.commit()
        
with open('relations.csv') as f:
    reader = csv.reader(f)
    header = next(reader)
    for row in reader:
        rel = Acquaintance(
            frompax=row[0],
            topax=row[1],
        )
        session.add(rel)
        session.commit()

I want to write a function whereby when I input the teacher's ID, it will return all the names of students that he/she taught. I've managed to write the function code this far, and it return the students based on their ID.

def direct(id):
    return list(session.query(Relations).filter_by(frompax=id))

direct('ID01')
[ID01 ID03,
ID01 ID04,
ID01 ID05]

How do I get it to return just the student names instead? Example:

direct('ID01')
[Tom,
Bill,
Steve]

CodePudding user response：

import csv
def direct(id):
    id_list = list(session.query(Relations).filter_by(frompax=id))
    name_list = []
    with open("your_first_file.csv", "r") as f:
        reader = csv.reader(f, delimiter='\t')
        for i, line in enumerate(reader):
            if line[0] in id_list:
                name_list.append(line[1])
    return name_list

CodePudding user response：

It looks as if you are trying to create a self-referential many to many relationship. This requires some changes to your code to make it match that in the docs.

Firstly, convert Relations into a Table with two columns (note that it uses the same metadata as the model):

Base = declarative_base()


relations_table = Table(
    'relations',
    Base.metadata,
    Column('frompax', String, ForeignKey('id.index')),
    Column('topax', String, ForeignKey('id.index')),
)

Move the relationships to ID; they can be expressed as a single relationship.

class ID(Base):
    __tablename__ = 'id'
    index = Column(String, primary_key=True)
    name = Column(String)
    # ID's that have this row as relation.
    has_relations = relationship(
        'ID',
        secondary=relations_table,
        primaryjoin=index == relations_table.c.topax,
        secondaryjoin=index == relations_table.c.frompax,
        backref='is_related_to',
    )

    def __repr__(self):
        return '%s %s' % (self.index, self.name)

Finally, change the direct function to fetch an ID by primary key using Session.get, and then use a list comprehension to collect the names of the related objects.

def direct(id_):
    instance = session.get(ID, id_)
    return [r.name for r in instance.is_related_to]