PostgreSQL via Asyncpg¶
Description:
- Work with data in PostgreSQL asynchronously
Resources:
- [GitHub] https://github.com/MagicStack/asyncpg
- [Docs] https://magicstack.github.io/asyncpg/current/index.html
Source and license:
- Some of these examples are adapted from the asyncpg README and documentation (https://github.com/MagicStack/asyncpg), Copyright (C) 2016-present the asyncpg authors and contributors, licensed under Apache-2.0. That code stays under its own license; see THIRD_PARTY_NOTICES.md.
Environment variables¶
This notebook reads its settings from environment variables. Set them before starting Jupyter:
POSTGRES_HOST: hostname of your PostgreSQL serverPOSTGRES_USER: PostgreSQL user namePOSTGRES_PASSWORD: password for that userPOSTGRES_DB: database to connect to (must exist)
Example 1¶
Read data aysnchronously from an postgres database using asyncpg
In [ ]:
Copied!
import asyncio
import asyncpg
import datetime
import os
async def run_test():
conn = await asyncpg.connect(
user=os.environ["POSTGRES_USER"],
password=os.environ["POSTGRES_PASSWORD"],
database=os.environ["POSTGRES_DB"],
host=os.environ["POSTGRES_HOST"],
)
print("Connected to database")
await conn.execute('''
CREATE TABLE users(
id serial PRIMARY KEY,
name text,
dob date
)
''')
# Insert a record into the created table.
await conn.execute('''
INSERT INTO users(name, dob) VALUES($1, $2)
''', 'Bob', datetime.date(1986, 12, 9))
# Select a row from the table.
row = await conn.fetchrow(
'SELECT * FROM users WHERE name = $1', 'Bob')
for r in row:
print(r)
await conn.close()
loop = asyncio.get_event_loop()
loop.run_until_complete(run_test())
import asyncio
import asyncpg
import datetime
import os
async def run_test():
conn = await asyncpg.connect(
user=os.environ["POSTGRES_USER"],
password=os.environ["POSTGRES_PASSWORD"],
database=os.environ["POSTGRES_DB"],
host=os.environ["POSTGRES_HOST"],
)
print("Connected to database")
await conn.execute('''
CREATE TABLE users(
id serial PRIMARY KEY,
name text,
dob date
)
''')
# Insert a record into the created table.
await conn.execute('''
INSERT INTO users(name, dob) VALUES($1, $2)
''', 'Bob', datetime.date(1986, 12, 9))
# Select a row from the table.
row = await conn.fetchrow(
'SELECT * FROM users WHERE name = $1', 'Bob')
for r in row:
print(r)
await conn.close()
loop = asyncio.get_event_loop()
loop.run_until_complete(run_test())