Coverage for tests/test_select_typing.py: 100%

75 statements  

« prev     ^ index     » next       coverage.py v7.17.2, created at 2026-11-16 00:02 +0000

1from typing import assert_type 1NOPQRSTUVWXYZ

2 

3from sqlalchemy import Engine 1NOPQRSTUVWXYZ

4from sqlalchemy.engine import ScalarResult, TupleResult 1NOPQRSTUVWXYZ

5from sqlalchemy.sql.elements import UnaryExpression 1NOPQRSTUVWXYZ

6from sqlmodel import ( 1NOPQRSTUVWXYZ

7 Field, 

8 Session, 

9 SQLModel, 

10 asc, 

11 col, 

12 desc, 

13 nulls_first, 

14 nulls_last, 

15 select, 

16) 

17 

18 

19def test_fields(database_engine: Engine) -> None: 1NOPQRSTUVWXYZ

20 class Hero(SQLModel, table=True): 1abcdefghijklm

21 id: int | None = Field(default=None, primary_key=True) 1abcdefghijklm

22 name: str 1abcdefghijklm

23 secret_name: str 1abcdefghijklm

24 age: int | None = None 1abcdefghijklm

25 food: str | None = None 1abcdefghijklm

26 

27 engine = database_engine 1abcdefghijklm

28 

29 SQLModel.metadata.create_all(engine) 1abcdefghijklm

30 

31 with Session(engine) as session: 1abcdefghijklm

32 session.add(Hero(name="Deadpond", secret_name="Dive Wilson")) 1abcdefghijklm

33 session.add( 1abcdefghijklm

34 Hero(name="Spider-Boy", secret_name="Pedro Parqueador", food="pizza") 

35 ) 

36 session.add(Hero(name="Rusty-Man", secret_name="Tommy Sharp", age=48)) 1abcdefghijklm

37 

38 session.commit() 1abcdefghijklm

39 

40 # check typing of select with 3 fields 

41 with Session(engine) as session: 1abcdefghijklm

42 statement_3 = select(col(Hero.id), Hero.name, Hero.secret_name) 1abcdefghijklm

43 results_3 = session.exec(statement_3) 1abcdefghijklm

44 assert_type(results_3, TupleResult[tuple[int | None, str, str]]) 1abcdefghijklm

45 for hero_3 in results_3: 1abcdefghijklm

46 assert len(hero_3) == 3 1abcdefghijklm

47 name_3: str = hero_3[1] 1abcdefghijklm

48 assert type(name_3) is str 1abcdefghijklm

49 assert type(hero_3[0]) is int 1abcdefghijklm

50 assert type(hero_3[2]) is str 1abcdefghijklm

51 

52 # check typing of select with 4 fields 

53 with Session(engine) as session: 1abcdefghijklm

54 statement_4 = select(col(Hero.id), Hero.name, Hero.secret_name, col(Hero.age)) 1abcdefghijklm

55 results_4 = session.exec(statement_4) 1abcdefghijklm

56 assert_type(results_4, TupleResult[tuple[int | None, str, str, int | None]]) 1abcdefghijklm

57 for hero_4 in results_4: 1abcdefghijklm

58 assert len(hero_4) == 4 1abcdefghijklm

59 name_4: str = hero_4[1] 1abcdefghijklm

60 assert type(name_4) is str 1abcdefghijklm

61 assert type(hero_4[0]) is int 1abcdefghijklm

62 assert type(hero_4[2]) is str 1abcdefghijklm

63 assert type(hero_4[3]) in [int, type(None)] 1abcdefghijklm

64 

65 # check typing of select with 5 fields: currently runs but doesn't pass mypy 

66 # with Session(engine) as session: 

67 # statement_5 = select(Hero.id, Hero.name, Hero.secret_name, Hero.age, Hero.food) 

68 # results_5 = session.exec(statement_5) 

69 # for hero_5 in results_5: 

70 # assert len(hero_5) == 5 

71 # name_5: str = hero_5[1] 

72 # assert type(name_5) is str 

73 # assert type(hero_5[0]) is int 

74 # assert type(hero_5[2]) is str 

75 # assert type(hero_5[3]) in [int, type(None)] 

76 # assert type(hero_5[4]) in [str, type(None)] 

77 

78 

79def test_scalar_results(database_engine: Engine) -> None: 1NOPQRSTUVWXYZ

80 class Hero(SQLModel, table=True): 1nopqrstuvwxyz

81 id: int | None = Field(default=None, primary_key=True) 1nopqrstuvwxyz

82 name: str 1nopqrstuvwxyz

83 

84 SQLModel.metadata.create_all(database_engine) 1nopqrstuvwxyz

85 with Session(database_engine) as session: 1nopqrstuvwxyz

86 session.add(Hero(name="Deadpond")) 1nopqrstuvwxyz

87 session.commit() 1nopqrstuvwxyz

88 

89 statement = select(Hero) 1nopqrstuvwxyz

90 result = session.exec(statement) 1nopqrstuvwxyz

91 assert_type(result, ScalarResult[Hero]) 1nopqrstuvwxyz

92 assert result.one().name == "Deadpond" 1nopqrstuvwxyz

93 

94 scalars = session.scalars(statement) 1nopqrstuvwxyz

95 assert_type(scalars, ScalarResult[Hero]) 1nopqrstuvwxyz

96 assert scalars.one().name == "Deadpond" 1nopqrstuvwxyz

97 

98 scalar_subquery = ( 1nopqrstuvwxyz

99 select(Hero.name) 

100 .where(Hero.name == "Deadpond") 

101 .group_by(Hero.name) 

102 .having(Hero.name == "Deadpond") 

103 .scalar_subquery() 

104 ) 

105 name = session.exec(select(scalar_subquery)).one() 1nopqrstuvwxyz

106 assert_type(name, str) 1nopqrstuvwxyz

107 assert name == "Deadpond" 1nopqrstuvwxyz

108 

109 

110def test_ordering_types() -> None: 1NOPQRSTUVWXYZ

111 class Hero(SQLModel, table=True): 1ABCDEFGHIJKLM

112 id: int = Field(primary_key=True) 1ABCDEFGHIJKLM

113 name: str 1ABCDEFGHIJKLM

114 

115 ascending = asc(Hero.id) 1ABCDEFGHIJKLM

116 assert_type(ascending, UnaryExpression[int]) 1ABCDEFGHIJKLM

117 assert str(ascending) == "hero.id ASC" 1ABCDEFGHIJKLM

118 

119 descending = desc(Hero.id) 1ABCDEFGHIJKLM

120 assert_type(descending, UnaryExpression[int]) 1ABCDEFGHIJKLM

121 assert str(descending) == "hero.id DESC" 1ABCDEFGHIJKLM

122 

123 first = nulls_first(Hero.name) 1ABCDEFGHIJKLM

124 assert_type(first, UnaryExpression[str]) 1ABCDEFGHIJKLM

125 assert str(first) == "hero.name NULLS FIRST" 1ABCDEFGHIJKLM

126 

127 last = nulls_last(Hero.name) 1ABCDEFGHIJKLM

128 assert_type(last, UnaryExpression[str]) 1ABCDEFGHIJKLM

129 assert str(last) == "hero.name NULLS LAST" 1ABCDEFGHIJKLM