Getting Started¶
Below is an example of a program which can print "{greeting}, {world_name}!"
from data held in a minimal SQLite
database containing greetings and worlds.
The SQL is in a greetings.sql
file with -- name:
definitions on each query to tell anosql
under which name
we would like to be able to execute them. For example, the query under the name get-all-greetings
in the example
below will be available to us after loading via anosql.from_path
as a method get_all_greetings(conn)
.
Each method on an anosql.Queries
object accepts a database connection to use in communicating with the database.
-- name: get-all-greetings
-- Get all the greetings in the database
select greeting_id, greeting from greetings;
-- name: get-worlds-by-name
-- Get all the world record from the database.
select world_id,
world_name,
location
from worlds
where world_name = :world_name;
By specifying db_driver="sqlite3"
we can use the python stdlib sqlite3
driver to execute these sql queries and
get the results. We’re also using the sqlite3.Row
type for our records to make it easy to access our data via
their column names rather than as tuple indices.
import sqlite3
import anosql
queries = anosql.from_path("greetings.sql", db_driver="sqlite3")
conn = sqlite3.connect("greetings.db")
conn.row_factory = sqlite3.Row
greetings = queries.get_greetings(conn)
worlds = queries.get_worlds_by_name(conn, world_name="Earth")
# greetings = [
# <Row greeting_id=1, greeting="Hi">,
# <Row greeting_id=2, greeting="Aloha">,
# <Row greeting_id=3, greeting="Hola">
# ]
# worlds = [<Row world_id=1, world_name="Earth">]
for world_row in worlds:
for greeting_row in greetings:
print(f"{greeting_row['greeting']}, {world_row['world_name']}!")
# Hi, Earth!
# Aloha, Earth!
# Hola, Earth!
conn.close()