6.7. SQL Insert

6.7. SQL Insert — Python

6.7.1. Insert Values

INSERT INTO astronauts
VALUES ('Mark', 'Watney');

6.7.2. Insert Values to Columns

INSERT INTO astronauts (firstname, lastname)
VALUES ('Mark', 'Watney');

INSERT INTO astronauts (lastname, firstname)
VALUES ('Watney', 'Mark');

6.7.3. Insert to Autoincrement Column

INSERT INTO astronauts (id, firstname, lastname)
VALUES (NULL, 'Mark', 'Watney');

6.7.4. Prepared statements

For sequences (list, tuple, set):

INSERT INTO astronauts (firstname, lastname)
VALUES (?, ?);

For mappings (dict):

INSERT INTO astronauts
VALUES (NULL, :firstname, :lastname);

6.7.5. Assignments

# %% About
# - Name: Database Insert Raw
# - 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 insert data:
#    - table: `contacts`
#    - firstname: 'Mark'
#    - lastname: 'Watney'
# 2. Run doctests - all must succeed

# %% Polish
# 1. Napisz zapytanie SQL aby wstawić dane:
#    - tabela: `contacts`
#    - firstname: 'Mark'
#    - lastname: 'Watney'
# 2. Uruchom doctesty - wszystkie muszą się powieść

# %% Doctests
"""
>>> import sys; sys.tracebacklimit = 0
>>> from pathlib import Path
>>> import sqlite3

>>> database = Path(__file__).parent.parent / 'shop.db'
>>> db = sqlite3.connect(database)
>>> _ = db.execute(result)
>>> db.commit()

>>> USERS = 'SELECT COUNT(*) FROM `contacts`'
>>> users_count = db.execute(USERS).fetchone()[0]
>>> assert users_count >= 1

>>> db.close()
"""

# %% 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 = """

-- replace this comment
-- with your sql query

"""

# %% About
# - Name: Database Insert Tuple
# - 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 insert data:
#    - table: `contacts`
#    - data: `DATA: tuple`
#    - use: prepared statement (with `?`)
# 2. Run doctests - all must succeed

# %% Polish
# 1. Napisz zapytanie SQL aby wstawić dane:
#    - tabela: `contacts`
#    - dane: `DATA: tuple`
#    - użyj: przygotowanego zapytania (z `?`)
# 2. Uruchom doctesty - wszystkie muszą się powieść

# %% Doctests
"""
>>> import sys; sys.tracebacklimit = 0
>>> from pathlib import Path
>>> import sqlite3

>>> database = Path(__file__).parent.parent / 'shop.db'
>>> db = sqlite3.connect(database)
>>> _ = db.execute(result, DATA)
>>> db.commit()

>>> USERS = 'SELECT COUNT(*) FROM `contacts`'
>>> users_count = db.execute(USERS).fetchone()[0]
>>> assert users_count >= 2

>>> db.close()
"""

# %% 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
DATA = ('Melissa', 'Lewis')

# %% Result
result = """

INSERT INTO `contacts` (`firstname`, `lastname`)
VALUES ()

"""

# %% About
# - Name: Database Insert List[Tuple]
# - 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 insert data:
#    - table: `contacts`
#    - data: `DATA: list[tuple]`
#    - use: prepared statement (with `?`)
# 2. Run doctests - all must succeed

# %% Polish
# 1. Napisz zapytanie SQL aby wstawić dane:
#    - tabela: `contacts`
#    - dane: `DATA: list[tuple]`
#    - użyj: przygotowanego zapytania (z `?`)
# 2. Uruchom doctesty - wszystkie muszą się powieść

# %% Doctests
"""
>>> import sys; sys.tracebacklimit = 0
>>> from pathlib import Path
>>> import sqlite3

>>> database = Path(__file__).parent.parent / 'shop.db'
>>> db = sqlite3.connect(database)
>>> _ = db.executemany(result, DATA)
>>> db.commit()

>>> USERS = 'SELECT COUNT(*) FROM `contacts`'
>>> users_count = db.execute(USERS).fetchone()[0]
>>> assert users_count >= 6

>>> db.close()
"""

# %% 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
DATA = [
    ('Rick', 'Martinez'),
    ('Alex', 'Vogel'),
    ('Beth', 'Johanssen'),
    ('Chris', 'Beck'),
]

# %% Result
result = """

INSERT INTO `contacts` (`firstname`, `lastname`)
VALUES ()

"""

# %% About
# - Name: Database Insert Dict
# - 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 insert data:
#    - table: `contacts`
#    - data: `DATA: dict`
#    - use: prepared statement (with `:column`)
# 2. Run doctests - all must succeed

# %% Polish
# 1. Napisz zapytanie SQL aby wstawić dane:
#    - tabela: `contacts`
#    - dane: `DATA: dict`
#    - użyj: przygotowanego zapytania (z `:column`)
# 2. Uruchom doctesty - wszystkie muszą się powieść

# %% Doctests
"""
>>> import sys; sys.tracebacklimit = 0
>>> from pathlib import Path
>>> import sqlite3

>>> database = Path(__file__).parent.parent / 'shop.db'
>>> db = sqlite3.connect(database)
>>> _ = db.execute(result, DATA)
>>> db.commit()

>>> USERS = 'SELECT COUNT(*) FROM `contacts`'
>>> users_count = db.execute(USERS).fetchone()[0]
>>> assert users_count >= 7

>>> db.close()
"""

# %% 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
DATA = {
    'firstname': 'Mark',
    'lastname': 'Watney',
}

# %% Result
result = """

INSERT INTO `contacts` (`firstname`, `lastname`)
VALUES ()

"""

# %% About
# - Name: Database Insert List[Dict]
# - 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 insert data:
#    - table: `contacts`
#    - data: `DATA: list[dict]`
#    - use: prepared statement (with `:column`)
# 2. Run doctests - all must succeed

# %% Polish
# 1. Napisz zapytanie SQL aby wstawić dane:
#    - tabela: `contacts`
#    - dane: `DATA: list[dict]`
#    - użyj: przygotowanego zapytania (z `:column`)
# 2. Uruchom doctesty - wszystkie muszą się powieść

# %% Doctests
"""
>>> import sys; sys.tracebacklimit = 0
>>> from pathlib import Path
>>> import sqlite3

>>> database = Path(__file__).parent.parent / 'shop.db'
>>> db = sqlite3.connect(database)
>>> _ = db.executemany(result, DATA)
>>> db.commit()

>>> USERS = 'SELECT COUNT(*) FROM `contacts`'
>>> users_count = db.execute(USERS).fetchone()[0]
>>> assert users_count >= 12

>>> db.close()
"""

# %% 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
DATA = [
    {'firstname': 'Melissa', 'lastname': 'Lewis'},
    {'firstname': 'Rick', 'lastname': 'Martinez'},
    {'firstname': 'Alex', 'lastname': 'Vogel'},
    {'firstname': 'Beth', 'lastname': 'Johanssen'},
    {'firstname': 'Chris', 'lastname': 'Beck'},
]

# %% Result
result = """

INSERT INTO `contacts` (`firstname`, `lastname`)
VALUES ()

"""