Skip to content

Instantly share code, notes, and snippets.

@anandology
Created March 6, 2023 04:13
Show Gist options
  • Save anandology/f9c88405bae73897b66d9fe13649535f to your computer and use it in GitHub Desktop.
Save anandology/f9c88405bae73897b66d9fe13649535f to your computer and use it in GitHub Desktop.
Utility to query database in python
"""The database interface.
Please see schema.sql for the schema of the tables.
"""
from pathlib import Path
import sqlite3
import sys
from . import config
def query(q, parameters=[]):
"""Executes a SQL query and returns all the rows in the result.
>>> query("select 1 as x, 2 as y from people")
[{"x": 1, "y": 2}]
Support you have a table person with columns name and age.
>>> query("SELECT * FROM person")
[
{"name": "alice", "age": 10},
{"name": "bob", "age": 20},
{"name": "charlie", "age": 30},
{"name": "dave", "age": 40}
]
You can pass parameters to use in the query. The parameter will be marked using `?`
in the query and the corresponding values will be passed as a list parameters.
>>> query("select * from people WHERE age >= ?", [25])
[
{"name": "charlie", "age": 30},
{"name": "dave", "age": 40}
]
The following query find people with a certain age or more and limits the result to one single row.
>>> query("select * from people WHERE age >= ? ORDER by age LIMIT ?", [25, 1])
[
{"name": "charlie", "age": 30},
]
To insert something:
>>> query("INSERT INTO table_name (col_one, col_two, col_three) VALUES (?, ?, ?)", [val_one, val_two, val_three])
...
To update something:
>>> query("UPDATE table_name SET col_one=? WHRE col_two=?", [val_one, val_two])
...
"""
print(f"query: {q} {parameters}", file=sys.stderr)
with sqlite3.connect(config.db_path) as conn:
# configure sqlite to return each row as a dict-like object
conn.row_factory = sqlite3.Row
# execute the query
cursor = conn.execute(q, parameters)
# fetch all the rows and convert each one of them to a dictionary
return [dict(row) for row in cursor]
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment