Sqlite связи между таблицами 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()
Foreign key support in SQLite and Python
Foreign key support was added in SQLite version 3.6.19, Oct 14th 2009 (Release History of SQLite). There is no point trying to use this feature if you have an earlier version of SQLite. But which version do you have? Here are the relevant commands. NB version is the version of pysqlite, not sqlite. So 2.6.0 does not refer to the SQLite version, which is 3.7.4 i.e. very up-to-date as at the time of posting.
>>> import sqlite3 as sqlite
>>> sqlite.version
‘2.6.0’
>>> sqlite.sqlite_version
‘3.7.4’
The default installation of Python 2.6.6 (which I had to stay with for various reasons) installed an older version of SQLite. But this was easily remedied by installing a newer version (2.7.2) alongside 2.6.6 and overwriting the older version of sqlite3.dll in the C:\Python26\DLL folder with the version from C:\Python27\DLL. It was as simple as that.
Now foreign key constraints are disabled by default, so they must be enabled for each connection.
cur.execute(«PRAGMA foreign_keys = ON»)
Now to check that it is set, run the following:
to run the PRAGMA command you need, and
to get the result back from it. Either (0,) or (1,). NB to do the fetchone() after “PRAGMA foreign_keys” not “PRAGMA foreign_keys = ON”. If nothing is returned, you either forgot the previous instruction and didn’t rerun “PRAGMA foreign_keys” before the fetchone() 😉 or SQLite was compiled with the wrong flags set.
Tip: If the command “PRAGMA foreign_keys” returns no data instead of a single row containing “0” or “1”, then the version of SQLite you are using does not support foreign keys (either because it is older than 3.6.19 or because it was compiled with SQLITE_OMIT_FOREIGN_KEY or SQLITE_OMIT_TRIGGER defined).
SQLite Foreign Key Support
SQLITE_OMIT_TRIGGER
Defining this option omits support for TRIGGER objects. Neither the CREATE TRIGGER or DROP TRIGGER commands are available in this case, and attempting to execute either will result in a parse error. This option also disables enforcement of foreign key constraints, since the code that implements triggers and which is omitted by this option is also used to implement foreign key actions.
Compilation Options for SQLite
If you are using SQLite as installed along with Python the most likely explanation for no result is that you made a mistake.