forked from netology-code/sqlpy-code
-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathorm.py
More file actions
81 lines (57 loc) · 2.33 KB
/
Copy pathorm.py
File metadata and controls
81 lines (57 loc) · 2.33 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
import sqlalchemy
import sqlalchemy as sq
from sqlalchemy.orm import declarative_base, relationship, sessionmaker
Base = declarative_base()
class Course(Base):
__tablename__ = "course"
id = sq.Column(sq.Integer, primary_key=True)
name = sq.Column(sq.String(length=40), unique=True)
# homeworks = relationship("Homework", back_populates="course")
class Homework(Base):
__tablename__ = "homework"
id = sq.Column(sq.Integer, primary_key=True)
number = sq.Column(sq.Integer, nullable=False)
description = sq.Column(sq.Text, nullable=False)
course_id = sq.Column(sq.Integer, sq.ForeignKey("course.id"), nullable=False)
# course = relationship(Course, back_populates="homeworks")
course = relationship(Course, backref="homeworks")
def create_tables(engine):
# Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)
DSN = "postgresql://postgres:postgres@localhost:5432/netology_db"
engine = sqlalchemy.create_engine(DSN)
create_tables(engine)
# сессия
Session = sessionmaker(bind=engine)
session = Session()
# создание объектов
js = Course(name="JavaScript")
print(js.id)
hw1 = Homework(number=1, description="первое задание", course=js)
hw2 = Homework(number=2, description="второе задание (сложное)", course=js)
session.add(js)
print(js.id)
session.add_all([hw1, hw2])
session.commit() # фиксируем изменения
print(js.id)
# запросы
q = session.query(Course).join(Homework.course).filter(Homework.number == 1)
print(q)
for s in q.all():
print(s.id, s.name)
for hw in s.homeworks:
print("\t", hw.id, hw.number, hw.description)
# вложенный запрос
subq = session.query(Homework).filter(Homework.description.like("%сложн%")).subquery("simple_hw")
q = session.query(Course).join(subq, Course.id == subq.c.course_id)
print(q)
for s in q.all():
print(s.id, s.name)
for hw in s.homeworks:
print("\t", hw.id, hw.number, hw.description)
# обновление объектов
session.query(Course).filter(Course.name == "JavaScript").update({"name": "NEW JavaScript"})
session.commit() # фиксируем изменения
# удаление объектов
session.query(Homework).filter(Homework.number > 1).delete()
session.commit() # фиксируем изменения