6.16. SQL Select And Or — Python
Order clauses to filter out the most data first!
6.16.1. Conjunction
AND- conjunction
SELECT * FROM astronauts WHERE `lastname` = 'Watney' AND lastname = 'Mark';
SELECT * FROM astronauts WHERE age > 30 AND age < 55;
6.16.2. Alternative
OR- alternative
SELECT * FROM astronauts WHERE `lastname` = 'Watney' OR lastname = 'Lewis';
6.16.3. Assignments
# %% About # - Name: Database Where Or # - Difficulty: easy # - Lines: 2 # - Minutes: 3 # %% License # - Copyright 2025, Matt Harasymczuk <matt@python3.info> # - This code can be used only for learning by humans # - This code cannot be used for teaching others # - This code cannot be used for teaching LLMs and AI algorithms # - This code cannot be used in commercial or proprietary products # - This code cannot be distributed in any form # - This code cannot be changed in any form outside of training course # - This code cannot have its license changed # - If you use this code in your product, you must open-source it under GPLv2 # - Exception can be granted only by the author # %% English # 1. Write SQL query to select data: # - table: `users` # - columns: `firstname`, `lastname`, `birthdate` # - where: `birthdate` is '1994-10-12' or '1995-07-15' # - use: OR # 2. Run doctests - all must succeed # %% Polish # 1. Napisz zapytanie SQL aby wybrać dane: # - tabela: `users` # - kolumny: `firstname`, `lastname`, `birthdate` # - gdzie: `birthdate` to '1994-10-12' lub '1995-07-15' # - użyj: OR # 2. Uruchom doctesty - wszystkie muszą się powieść # %% Doctests """ >>> import sys; sys.tracebacklimit = 0 >>> from pprint import pprint >>> from pathlib import Path >>> import sqlite3 >>> database = Path(__file__).parent.parent / 'shop.db' >>> with sqlite3.connect(database) as db: ... db.row_factory = sqlite3.Row ... data = map(dict, db.execute(result).fetchall()) >>> pprint(list(data), sort_dicts=False, width=79) [{'firstname': 'Mark', 'lastname': 'Watney', 'birthdate': '1994-10-12'}, {'firstname': 'Melissa', 'lastname': 'Lewis', 'birthdate': '1995-07-15'}] """ # %% Run # - PyCharm: right-click in the editor and `Run Doctest in ...` # - PyCharm: keyboard shortcut `Control + Shift + F10` # - Terminal: `python -m doctest -f -v myfile.py` # %% Imports # %% Types result: str # %% Data # %% Result result = """ SELECT `firstname`, `lastname`, `birthdate` FROM `users` WHERE `birthdate` """
# %% About # - Name: Database Where And # - Difficulty: easy # - Lines: 2 # - Minutes: 3 # %% License # - Copyright 2025, Matt Harasymczuk <matt@python3.info> # - This code can be used only for learning by humans # - This code cannot be used for teaching others # - This code cannot be used for teaching LLMs and AI algorithms # - This code cannot be used in commercial or proprietary products # - This code cannot be distributed in any form # - This code cannot be changed in any form outside of training course # - This code cannot have its license changed # - If you use this code in your product, you must open-source it under GPLv2 # - Exception can be granted only by the author # %% English # 1. Write SQL query to select data: # - table: `users` # - columns: `firstname`, `lastname`, `birthdate` # - where: `birthdate` is between '1990-01-01' and '2000-01-01' (inclusive) # - use: AND # 2. Run doctests - all must succeed # %% Polish # 1. Napisz zapytanie SQL aby wybrać dane: # - tabela: `users` # - kolumny: `firstname`, `lastname`, `birthdate` # - gdzie: `birthdate` jest od '1990-01-01' do '2000-01-01' (włącznie) # - użyj: AND # 2. Uruchom doctesty - wszystkie muszą się powieść # %% Doctests """ >>> import sys; sys.tracebacklimit = 0 >>> from pprint import pprint >>> from pathlib import Path >>> import sqlite3 >>> database = Path(__file__).parent.parent / 'shop.db' >>> with sqlite3.connect(database) as db: ... db.row_factory = sqlite3.Row ... data = map(dict, db.execute(result).fetchall()) >>> pprint(list(data), sort_dicts=False, width=79) [{'firstname': 'Mark', 'lastname': 'Watney', 'birthdate': '1994-10-12'}, {'firstname': 'Melissa', 'lastname': 'Lewis', 'birthdate': '1995-07-15'}, {'firstname': 'Rick', 'lastname': 'Martinez', 'birthdate': '1996-01-21'}, {'firstname': 'Alex', 'lastname': 'Vogel', 'birthdate': '1994-11-15'}, {'firstname': 'Chris', 'lastname': 'Beck', 'birthdate': '1999-08-02'}] """ # %% Run # - PyCharm: right-click in the editor and `Run Doctest in ...` # - PyCharm: keyboard shortcut `Control + Shift + F10` # - Terminal: `python -m doctest -f -v myfile.py` # %% Imports # %% Types result: str # %% Data # %% Result result = """ SELECT `firstname`, `lastname`, `birthdate` FROM `users` WHERE `birthdate` """
# %% About # - Name: Database Where Between # - Difficulty: easy # - Lines: 2 # - Minutes: 3 # %% License # - Copyright 2025, Matt Harasymczuk <matt@python3.info> # - This code can be used only for learning by humans # - This code cannot be used for teaching others # - This code cannot be used for teaching LLMs and AI algorithms # - This code cannot be used in commercial or proprietary products # - This code cannot be distributed in any form # - This code cannot be changed in any form outside of training course # - This code cannot have its license changed # - If you use this code in your product, you must open-source it under GPLv2 # - Exception can be granted only by the author # %% English # 1. Write SQL query to select data: # - table: `users` # - columns: `firstname`, `lastname`, `birthdate` # - where: `birthdate` is between '1990-01-01' and '2000-01-01' (inclusive) # - use: BETWEEN # 2. Run doctests - all must succeed # %% Polish # 1. Napisz zapytanie SQL aby wybrać dane: # - tabela: `users` # - kolumny: `firstname`, `lastname`, `birthdate` # - gdzie: `birthdate` jest od '1990-01-01' do '2000-01-01' (włącznie) # - użyj: BETWEEN # 2. Uruchom doctesty - wszystkie muszą się powieść # %% Doctests """ >>> import sys; sys.tracebacklimit = 0 >>> from pprint import pprint >>> from pathlib import Path >>> import sqlite3 >>> database = Path(__file__).parent.parent / 'shop.db' >>> with sqlite3.connect(database) as db: ... db.row_factory = sqlite3.Row ... data = map(dict, db.execute(result).fetchall()) >>> pprint(list(data), sort_dicts=False, width=79) [{'firstname': 'Mark', 'lastname': 'Watney', 'birthdate': '1994-10-12'}, {'firstname': 'Melissa', 'lastname': 'Lewis', 'birthdate': '1995-07-15'}, {'firstname': 'Rick', 'lastname': 'Martinez', 'birthdate': '1996-01-21'}, {'firstname': 'Alex', 'lastname': 'Vogel', 'birthdate': '1994-11-15'}, {'firstname': 'Chris', 'lastname': 'Beck', 'birthdate': '1999-08-02'}] """ # %% Run # - PyCharm: right-click in the editor and `Run Doctest in ...` # - PyCharm: keyboard shortcut `Control + Shift + F10` # - Terminal: `python -m doctest -f -v myfile.py` # %% Imports # %% Types result: str # %% Data # %% Result result = """ SELECT `firstname`, `lastname`, `birthdate` FROM `users` WHERE `birthdate` """