根据以下人士的回答和评论这个问题 https://stackoverflow.com/q/2638217/2144390以下似乎对我有用:
from sqlalchemy import create_engine, Column, ForeignKey, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relation, sessionmaker
connection_url = "mssql+pyodbc://@localhost,49242/myDb?driver=ODBC+Driver+17+for+SQL+Server"
engine = create_engine(connection_url)
Base = declarative_base()
class Person(Base):
__tablename__ = 'person'
id = Column(Integer, autoincrement=True, primary_key=True)
name = Column(String(50))
_parent_id = Column(Integer, ForeignKey('person.id'))
parent = relation('Person', remote_side=[id])
children = relation('Person', remote_side=[_parent_id], uselist=True)
def __repr__(self):
return f"<Person(id={self.id}, name='{self.name}'>)"
Base.metadata.drop_all(engine, checkfirst=True)
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()
parent = Person(name='Homer')
child = Person(name='Bart')
parent.children.append(child)
child = Person(name='Lisa')
parent.children.append(child)
print(f"{parent.name}'s (unpersisted) children: {parent.children}")
# Homer's (unpersisted) children: [<Person(id=None, name='Bart'>), <Person(id=None, name='Lisa'>)]
session.add(parent)
session.commit()
print(f"{parent.name}'s (persisted) children: {parent.children}")
# Homer's (persisted) children: [<Person(id=2, name='Bart'>), <Person(id=3, name='Lisa'>)]
child = session.query(Person).filter(Person.name=='Lisa').first()
print(f"{child.name}'s parent: {child.parent}")
# Lisa's parent: <Person(id=1, name='Homer'>)
with engine.begin() as conn:
print(conn.execute("SELECT * FROM person").fetchall())
# [(1, 'Homer', None), (2, 'Bart', 1), (3, 'Lisa', 1)]