Python SQLite3 Cheat Sheet Examples

April - 2021
TODO: Figure out why this isn't working in MDX


### TL;DR

I couldn't find a good, single page set of basic Python SQLite examples. So, I made this one. 

python
#!/usr/bin/env python3

####################################
# Notes are at the end of the file #
####################################

import sqlite3

db_file = 'basic_examples.sqlite3'

######################################################################
# Setup the connection and cursor

connection = sqlite3.connect('example.db')
cursor = connection.cursor()


######################################################################
# Make a table

create_statement = '''
CREATE TABLE IF NOT EXISTS people
(
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL, 
    favorite_color TEXT NOT NULL
)
'''

cursor.execute(create_statement)
connection.commit()


######################################################################
# Add a single row with basic insert

insert_person_statement = '''
    INSERT INTO people VALUES (NULL, ?, ?)
'''

cursor.execute(
    insert_person_statement,
    ('Dario', 'Red')
)
connection.commit()


######################################################################
# Add multiple rows with basic insert via `.executemany`

insert_many_people_statement = '''
    INSERT INTO people VALUES (NULL, ?, ?)
'''

people = [
    ('JJ', 'Blue'),
    ('Fisken', 'Pink'),
]

cursor.executemany(insert_many_people_statement, people)
connection.commit()


######################################################################
# Add a row with insert or ignore

insert_or_ignore_person_statement = '''
    INSERT OR IGNORE INTO people VALUES (?, ?, ?)
'''

cursor.execute(
    insert_or_ignore_person_statement,
    (1, 'This will not show up since the id already exists', 'Obsidian')
)
connection.commit()

######################################################################
# Update a row

update_statement = '''
    UPDATE people 
    SET favorite_color = ?
    WHERE id = ?
'''

cursor.execute(
    update_statement,
    ('Green', 1)
)
connection.commit()

######################################################################
# Get a single row

get_single_record_statement = '''
    SELECT * FROM people WHERE name = ? LIMIT 1
'''

cursor.execute(
    get_single_record_statement,
    ('Dario',) # Note the trailing `,` Details below
)

result_row = cursor.fetchone()

print(result_row)

# Output:
# (1, 'Dario', 'Green')


######################################################################
# Get multiple rows via `.execute` iterator

get_multiple_rows_statement_iterator = '''
    SELECT * FROM people
'''

results = cursor.execute(
    get_multiple_rows_statement_iterator
)

for row in results:
    print(row)

# Output:
# (1, 'Dario', 'Green')
# (2, 'JJ', 'Blue')
# (3, 'Fisken', 'Pink')



######################################################################
# Get multiple rows via `.fetchall()`

get_multiple_rows_statement_fetchall = '''
    SELECT * FROM people
'''

cursor.execute(
    get_multiple_rows_statement_fetchall
)

print(cursor.fetchall())

# Output:
# [(1, 'Dario', 'Green'), (2, 'JJ', 'Blue'), (3, 'Fisken', 'Pink')]


######################################################################
# Delete a row

delete_statement = '''
    DELETE FROM people
    WHERE id = ?
'''

cursor.execute(
    delete_statement,
    (1,)
)

connection.commit() 



######################################################################
# Close the connection now that you're done

connection.close()

######################################################################


### Notes

- This is all one run of code. I did it that way to avoid setting up the table and doing the insert each time. The main thing that might be confusing is the value of Dario's color. At the top, it's set to Red initially, but we update if further down to Green.  

- TODO: Add UPSERTs: https://www.sqlite.org/draft/lang_UPSERT.html

- TODO: Add conflict resolution: https://sqlite.org/lang_conflict.html and https://stackoverflow.com/questions/3634984/insert-if-not-exists-else-update

- TODO: https://stackoverflow.com/questions/19337029/insert-if-not-exists-statement-in-sqlite/19343100

- There's no need to use PIP. SQLite3 is in the Python 3 standard library. That's awesome. The [docs are here](https://docs.python.org/3/library/sqlite3.html)

- This example creates a database file. If you want to
    run everything in memory without creating a file 
    (and those having the database disappear when the 
    connection is closed) change the `db_file` assignment
    to:
    
        db_file = ':memory:'

- It's possible to put your statements directly in the
`.execute` calls, but it's cleaner to set them in variables
and then execute them. Especially if you're passing
arguments.

- This example doesn't use AUTOINCREMENT in the
CREATE TABLE statement. The `id` field will still
maintain itself, but if you delete a row, it's
possible that an id number will be reused. If that
will cause problems you can add `AUTOINCREMENT` to the
end of the statement (e.g. `id INTEGER PRIMARY KEY AUTOINCREMENT`)

- Remember that if you're supplying only one item in a tuple
you have to add a trailing comma to the tuple. E.g. instead of this:

        cursor.execute(
            the_statement, ('Dario')
        )

    You have to add the comma like this:

        cursor.execute(
            the_statement, ('Dario',)
        )

    If you don't do that, you'll get an error about the
    number of arguments being wrong since the string
    gets turned into individual arguments for each letter
    without the comma

- Note that the COUNT value from SQL isn't trusted. 
    So, get the records you're after and count then 
    yourself by looping over the returned list

- TODO: put in example with named calls:

    from the [docs](https://docs.python.org/3/library/sqlite3.html#sqlite3.Cursor.fetchall)
    
    cur.execute(
        "select * from lang where lang_name=:name and lang_age=:age",
        {"name": "C", "age": 49}
    )

- Some of the above SQL statements are duplicates (e.g. 
`insert_person_statement` and `insert_many_people_statement`). 
They're split out to make the examples easier to read. There's
no issue reusing one of the statements for both the single and
multiple row inserts.