Quickstart Guide¶
Creating a Table¶
Tables are simple to define.
import asyncio
from donphan import create_pool, Column, Table, MaybeAcquire, SQLType
class ExampleTable(Table):
id: Column[SQLType.Serial] = Column(primary_key=True) # example auto increment primary key
created_at: Column[SQLType.Timestamp] = Column(default='NOW()') # example column with a default value
some_text: Column[str] # example simple text column
a_list_of_numbers: Column[list[int]] # example coulmn with a foreign key
async def main():
pool = await create_pool(os.getenv("POSTGRES_DSN")) # Connects to postgres
async with MaybeAcquire(pool=pool) as connection: # Acquire a connection from the pool
await ExampleTable.create(connection) # Create the Example Table
This code shows how one could define a simple table using donphan.
Column
names are defined as class atributes, types are specified
as type hints, supplied types can either be defined as a built in python type
or using a SQLType
class.
Additional Column
properties such as wether the column is a primary key
can be set via creating a Column
instance.
Interacting with a Table¶
Once a table has been defined and created it can be interacted with using asynchronous
classmethods, A list of applicable methods can be found here: Table
The following shows an inserting a record into a predefined table
await ExampleTable.insert(
connection,
some_text='This is some text',
a_list_of_numbers=[1,2,3],
)
Records can be fetched from the table in a similar way
records = await ExampleTable.fetch(connection, a_list_of_numbers=[1,2,3])
In this example the variable records will hold a list of all records in the table where the value of the column some_other_thing is equal to 2.
Records returned are instances of asyncpg.Record
.
One can check if a value does not equal, is less or greater than and their or equal counterparts by appending __ne, __lt, __gt, __le, and __ge to the end of the keyword argument for each respective column.
records = await ExampleTable.fetch(connection, created_at__lt=datetime.datetime.utcnow())
By default all keword arguments applied are assumed to be an SQL AND statement. However it is possible to use an OR statement by appending or_ to the beginning of a keyword argument for a respective column.
records = await ExampleTable.fetch(connection, created_at__lt=datetime.datetime.utcnow(), or_some_other_thing = 2)
If desired a pure SQL where clause may be used. With value subtitution where needed.
records = await ExampleTable.fetch_where(connection, 'created_at < NOW() OR some_other_thing = $1', 2)
It is possible to obtain a single record from the database as well.
record = await ExampleTable.fetch_row(connection, id=1)
Using a asyncpg.Record
instance we can simply delete a record in a table.
await ExampleTable.delete_record(connection, record)