Связанные базы данных python
Отношение один-ко-многим (one-to-many) представляет ситуацию, когда одна модель хранит ссылку на один объект другой модели, а вторая модель может ссылаться на коллекцию объектов первой модели. Например, в одной компании может работать несколько пользователей, а каждый пользователь в свою очередь может официально работать только в одной компании:
from sqlalchemy import create_engine, Column, Integer, String, ForeignKey from sqlalchemy.orm import DeclarativeBase from sqlalchemy.orm import relationship sqlite_database = "sqlite:///metanit2.db" engine = create_engine(sqlite_database) class Base(DeclarativeBase): pass class User(Base): __tablename__ = "users" primary_key=True, index=True) name = Column(String) company_id = Column(Integer, ForeignKey("companies.id")) company = relationship("Company", back_populates="users") class Company(Base): __tablename__ = "companies" primary_key=True, index=True) name = Column(String) users = relationship("User", back_populates="company") Base.metadata.create_all(bind=engine) Здесь пользователи представлены моделью User, а компании — моделью Company. Оба класса имеют обычные атрибуты-столбцы — id и name. Но кроме того, они имеют атрибуты, которые позволяют установить отношения между моделями
#class User company_id = Column(Integer, ForeignKey("companies.id")) company = relationship("Company", back_populates="users") #class Company users = relationship("User", back_populates="company") Для установки отношений между моделями применяется функция relationship() . Она принимает множество параметров, из которых самый первый параметр указывает на связанную модель. А параметр back_populates представляет атрибут связанной модели, с которой будет сопоставляться текущая модель. Например, в классе Company атрибут
users = relationship("User", back_populates="company") указывает, что он будет связан с моделью User через ее атрибут «company».
В классе User мы имеем обратную ситуацию
company = relationship("Company", back_populates="users") здесь атрибут company связан с моделью Company через ее атрибут «users». То есть получается связь User.company — Company.users.
Но какая из этих моделей будет главной и хранить список объектов, а какая будет подчиненной и хранить ссылку на один объект связанной модели? Для этого в подчиненной модели User определяем атрибут-столбец, который будет представлять внешний ключ:
company_id = Column(Integer, ForeignKey("companies.id")) То есть атрибут company_id будет представлять числовой внешний ключ на столбец id из таблицы «companies».
После выполнения программы в базе данных metanit2.db будут созданы две таблицы с помощью следующих скриптов SQL:
CREATE TABLE companies ( id INTEGER NOT NULL, name VARCHAR, PRIMARY KEY (id) ) CREATE TABLE users ( id INTEGER NOT NULL, name VARCHAR, company_id INTEGER, PRIMARY KEY (id), FOREIGN KEY(company_id) REFERENCES companies (id) )
Основные операции
Добавление
Для добавления данных моделей, связанных отношением один ко многим, используются уже ранее рассмотренные методы добавления в бд. При этом, при добавлении одного объекта все связанные с ним объекты добавляются автоматически:
from sqlalchemy import create_engine, Column, Integer, String, ForeignKey from sqlalchemy.orm import DeclarativeBase from sqlalchemy.orm import relationship, Session sqlite_database = "sqlite:///metanit2.db" engine = create_engine(sqlite_database) class Base(DeclarativeBase): pass class User(Base): __tablename__ = "users" primary_key=True, index=True) name = Column(String) company_id = Column(Integer, ForeignKey("companies.id")) company = relationship("Company", back_populates="users") class Company(Base): __tablename__ = "companies" primary_key=True, index=True) name = Column(String) users = relationship("User", back_populates="company") with Session(autoflush=False, bind=engine) as db: # создаем компании microsoft = Company(name="Microsoft") google = Company(name="Google") # создаем пользователей tom = User(name="Tom") bob = User(name="Bob") # устанавливаем для компаний списки пользователей microsoft.users=[tom] google.users = [bob] # добавляем компании в базу данных, и вместе с ними добавляются пользователи db.add_all([microsoft, google]) db.commit() # можно отдельно добавить объект в список alice = User(name="Alice") google.users.extend([alice]) # добавляем список из одного элемента # можно установить для пользователя определенную компанию sam = User(name="Sam") sam.company = microsoft db.add(sam) db.commit() При добавлении компаний в бд также добавляются связанные с ними пользователи(если они не добавлены в бд)
db.add_all([microsoft, google])
Также можно отдельно добавлять пользователей в определенную компанию, используя методы списков:
Также можно, наоборот, у пользователя установить компанию:
Получение данных
Через атрибуты, через которые установлена связь между моделями, можно получить связанные данные. Например, получим компании пользователей:
with Session(autoflush=False, bind=engine) as db: # получение всех объектов users = db.query(User).all() for u in users: print(f" ()")
Tom (Microsoft) Bob (Google) Alice (Google) Sam (Microsoft)
Получение пользователей у компаний:
with Session(autoflush=False, bind=engine) as db: # получение всех объектов companies = db.query(Company).all() for c in companies: print(f"") for u in c.users: print(f"") print()
Microsoft Tom Sam Google Bob Alice
Редактирование
Редактирование производится как и в общем случае. Например, изменим у пользователя компанию:
with Session(autoflush=False, bind=engine) as db: # получаем пользователя с именем Tom tom = db.query(User).filter(User.name=="Tom").first() # получаем компанию Google google = db.query(Company).filter(Company.name=="Google").first() # меняем у Тома компанию на Google if tom != None and google !=None: tom.company = google db.commit() # проверяем изменение users = db.query(User).all() for u in users: print(f" - ")
Tom - Google Bob - Google Alice - Google Sam - Microsoft
Удаление
Для удаления объекта зависимой модели из списка объектов в главной модели, можно использовать методы списка, в частности, метод remove() :
with Session(autoflush=False, bind=engine) as db: # получаем пользователя с именем Tom tom = db.query(User).filter(User.name=="Tom").first() # получаем компанию Google google = db.query(Company).filter(Company.name=="Google").first() # удаляем Тома из компании Google if tom != None and google !=None: google.users.remove(tom) db.commit() # проверяем изменение users = db.query(User).all() for u in users: print(f" - ")
Tom - None Bob - Google Alice - Google Sam - Microsoft
Удаление объекта зависимой модели (User) из базы данных проиходит как и в общем случае.
with Session(autoflush=False, bind=engine) as db: # получаем пользователя с именем Tom tom = db.query(User).filter(User.name=="Tom").first() # удаляем Toma db.delete(tom) db.commit()
Удаление объекта главной модели (Company) из базы данных зависит от настройки выражения ON DELETE . Например, для выше определенных моделей User и Company отношение установливалось следующим образом:
# User company_id = Column(Integer, ForeignKey("companies.id")) company = relationship("Company", back_populates="users") #class Company users = relationship("User", back_populates="company") В данном случае атрибут company_id в модели User может принимать значение None (на уровне базы данных столбец может принимать значение NULL). При удалении объекта главной модели, этот столбец company_id получит значение NULL (то есть компания для пользователя не установлена)
with Session(autoflush=False, bind=engine) as db: # получаем компанию Google google = db.query(Company).filter(Company.name=="Google").first() # удаляем ее db.delete(google) db.commit()
Однако нередко применяется каскадное удаление, при котором при удалении объекта главной модели также удаляются все связанные с ней объекты зависимой модели. Для установки каскадного удаления в функции relationship() применяется параметр cascade , которая получает значение «all, delete-orphan» . Например, создадим новую базу данных с подобной настройкой:
from sqlalchemy import create_engine, Column, Integer, String, ForeignKey from sqlalchemy.orm import DeclarativeBase from sqlalchemy.orm import relationship, Session sqlite_database = "sqlite:///metanit3.db" engine = create_engine(sqlite_database) class Base(DeclarativeBase): pass class User(Base): __tablename__ = "users" primary_key=True, index=True) name = Column(String) company_id = Column(Integer, ForeignKey("companies.id")) company = relationship("Company", back_populates="users") class Company(Base): __tablename__ = "companies" primary_key=True, index=True) name = Column(String) users = relationship("User", back_populates="company", cascade="all, delete-orphan") Base.metadata.create_all(bind=engine) with Session(autoflush=False, bind=engine) as db: # создаем для теста компанию google = Company(name="Google") # создаем пользователей tom = User(name="Tom") bob = User(name="Bob") # устанавливаем для компаний список пользователей google.users=[User(name="Tom"), User(name="Bob")] db.add(google) db.commit() Ключевой момент здесь — установка атрибута users в классе Company:
users = relationship("User", back_populates="company", cascade="all, delete-orphan") Удалим компанию, и вместе с ней будут удалены все связанные с ней пользователи:
with Session(autoflush=False, bind=engine) as db: # получаем компанию Google google = db.query(Company).filter(Company.name=="Google").first() # удаляем ее db.delete(google) db.commit()