Code
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 <<link|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 <<link|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.