Python 3.6+ Asyncio PostgreSQL query builder and model

Overview

windyquery - A non-blocking Python PostgreSQL query builder

Windyquery is a non-blocking PostgreSQL query builder with Asyncio.

Installation

$ pip install windyquery

Connection

import asyncio

from windyquery import DB

# create DB connection for CRUD operatons
db = DB()
asyncio.get_event_loop().run_until_complete(db.connect('db_name', {
    'host': 'localhost',
    'port': '5432',
    'database': 'db_name',
    'username': 'db_user_name',
    'password': 'db_user_password'
}, default=True))

asyncio.get_event_loop().run_until_complete(db.connect('other_db_name', {
    'host': 'localhost',
    'port': '5432',
    'database': 'other_db_name',
    'username': 'db_user_name',
    'password': 'db_user_password'
}, default=False))

# switch connections between different databases
db.connection('other_db_name')

# the default connection can also be changed directly
db.default = 'other_db_name'

# close DB connection
asyncio.get_event_loop().run_until_complete(db.stop())

CRUD examples

A DB instance can be used to constuct a SQL. The instance is a coroutine object. It can be scheduled to run by all asyncio mechanisms.

Build a SQL and execute it

async def main(db):
    # SELECT id, name FROM users
    users = await db.table('users').select('id', 'name')
    print(users[0]['name'])

asyncio.run(main(db))

SELECT

# SELECT name AS username, address addr FROM users
await db.table('users').select('name AS username', 'address addr')

# SELECT * FROM users WHERE id = 1 AND name = 'Tom'
await db.table('users').select().where('id', 1).where('name', 'Tom')

# SELECT * FROM users WHERE id = 1 AND name = 'Tom'
await db.table('users').select().where('id', '=', 1).where('name', '=', 'Tom')

# SELECT * FROM users WHERE id = 1 AND name = 'Tom'
await db.table('users').select().where('id = ? AND name = ?', 1, 'Tom')

# SELECT * FROM users WHERE id IN (1, 2)
await db.table('cards').select().where("id", [1, 2])

# SELECT * FROM users WHERE id IN (1, 2)
await db.table('cards').select().where("id", 'IN', [1, 2])

# SELECT * FROM users WHERE id IN (1, 2)
await db.table('cards').select().where("id IN (?, ?)", 1, 2)

# SELECT * FROM users ORDER BY id, name DESC
await db.table('users').select().order_by('id', 'name DESC')

# SELECT * FROM users GROUP BY id, name
await db.table('users').select().group_by('id', 'name')

# SELECT * FROM users LIMIT 100 OFFSET 10
await db.table('users').select().limit(100).offset(10)

# SELECT users.*, orders.total FROM users
#   JOIN orders ON orders.user_id = users.id
await db.table('users').select('users.*', 'orders.total').\
    join('orders', 'orders.user_id', '=', 'users.id')
    
# SELECT users.*, orders.total FROM users
#   JOIN orders ON orders.user_id = users.id AND orders.total > 100
await db.table('users').select('users.*', 'orders.total').\
    join('orders', 'orders.user_id = users.id AND orders.total > ?', 100)

INSERT

# INSERT INTO users(id, name) VALUES
#   (1, 'Tom'),
#   (2, 'Jerry'),
#   (3, DEFAULT)
await db.table('users').insert(
    {'id': 1, 'name': 'Tom'},
    {'id': 2, 'name': 'Jerry'},
    {'id': 3, 'name': 'DEFAULT'}
)

# INSERT INTO users(id, name) VALUES
#   (1, 'Tom'),
#   (2, 'Jerry'),
#   (3, DEFAULT)
#   RETRUNING id, name
await db.table('users').insert(
    {'id': 1, 'name': 'Tom'},
    {'id': 2, 'name': 'Jerry'},
    {'id': 3, 'name': 'DEFAULT'}
).returning('id', 'name')

# INSERT INTO users(id, name) VALUES
#   (1, 'Tom'),
#   (2, 'Jerry'),
#   (3, DEFAULT)
#   RETRUNING *
await db.table('users').insert(
    {'id': 1, 'name': 'Tom'},
    {'id': 2, 'name': 'Jerry'},
    {'id': 3, 'name': 'DEFAULT'}
).returning()

# INSERT INTO users (id, name) VALUES
#   (1, 'Tom')
#   ON CONFLICT (id) DO NOTHING
await db.table('users').insert(
    {'id': 1, 'name': 'Tom'},
).on_conflict('(id)', 'DO NOTHING')

# INSERT INTO users As u (id, name) VALUES
#   (1, 'Tom')
#   ON CONFLICT ON CONSTRAINT users_pkey
#   DO UPDATE SET name = EXCLUDED.name || ' (formerly ' || u.name || ')'
await db.table('users AS u').insert(
    {'id': 1, 'name': 'Tom'},
).on_conflict(
    'ON CONSTRAINT users_pkey',
    "DO UPDATE SET name = EXCLUDED.name || ' (formerly ' || u.name || ')'"
)        

UPDATE

# UPDATE cards SET name = 'Tom' WHERE id = 9
await db.table('cards').where('id', 9).update({'name': 'Tom'})

# UPDATE cards SET total = total + 1 WHERE id = 9
await db.table('cards').update('total = total + 1').where('id', 9)

# UPDATE users SET name = 'Tom' WHERE id = 9 RETRUNING *
await db.table('users').update({'name': 'Tom'}).where('id', '=', 9).returning()

# UPDATE users SET name = 'Tom' WHERE id = 9 RETRUNING id, name
await db.table('users').update({'name': 'Tom'}).where('id', '=', 9).returning('id', 'name')

# UPDATE users SET name = orders.name
#   FROM orders
#   WHERE orders.user_id = users.id
await db.table('users').update('name = orders.name').\
    from_table('orders').\
    where('orders.user_id = users.id')

# UPDATE users SET name = products.name, purchase = products.name, is_paid = TRUE
#   FROM orders
#   JOIN products ON orders.product_id = products.id
#   WHERE orders.user_id = users.id
await db.table('users').update('name = product.name, purchase = products.name, is_paid = ?', True).\
    from_table('orders').\
    join('products', 'orders.product_id', '=', 'products.id').\
    where('orders.user_id = users.id')

DELETE

# DELETE FROM users WHERE id = 1
await db.table('users').where('id', 1).delete()

# DELETE FROM users WHERE id = 1 RETURNING id, name
await db.table('users').where('id', 1).delete().returning('id', 'name')

Migration examples

The DB instance can also be used to migrate database schema.

CREATE TABLE

# CREATE TABLE users (
#    id            serial PRIMARY KEY,
#    group_id      integer references groups (id) ON DELETE CASCADE,
#    created_at    timestamp not null DEFAULT NOW(),
#    email         text not null unique,
#    is_admin      boolean not null default false,
#    address       jsonb,
#    payday        integer not null,
#    CONSTRAINT unique_email UNIQUE(group_id, email)
#    check(payday > 0 and payday < 8)
#)
await db.schema('TABLE users').create(
    'id            serial PRIMARY KEY',
    'group_id      integer references groups (id) ON DELETE CASCADE',
    'created_at    timestamp not null DEFAULT NOW()',
    'email         text not null unique',
    'is_admin      boolean not null default false',
    'address       jsonb',
    'payday        integer not null',
    'CONSTRAINT unique_email UNIQUE(group_id, email)',
    'check(payday > 0 and payday < 8)',
)

# CREATE TABLE accounts LIKE users
await db.schema('TABLE accounts').create(
    'like users'
)

# CREATE TABLE IF NOT EXISTS accounts LIKE users
await db.schema('TABLE IF NOT EXISTS accounts').create(
    'like users'
)

Modify TABLE

# ALTER TABLE users
#   ALTER   id TYPE bigint,
#   ALTER   name SET DEFAULT 'no_name',
#   ALTER   COLUMN address DROP DEFAULT,
#   ALTER   "user info" SET NOT NULL,
#   ALTER   CONSTRAINT check(payday > 1 and payday < 6),
#   ADD     UNIQUE(name, email) WITH (fillfactor=70),
#   ADD     FOREIGN KEY (group_id) REFERENCES groups (id) ON DELETE SET NULL,
#   DROP    CONSTRAINT IF EXISTS idx_email CASCADE
await db.schema('TABLE users').alter(
    'alter  id TYPE bigint',
    'alter  name SET DEFAULT \'no_name\'',
    'alter  COLUMN address DROP DEFAULT',
    'alter  "user info" SET NOT NULL',
    'add    CONSTRAINT check(payday > 1 and payday < 6)',
    'add    UNIQUE(name, email) WITH (fillfactor=70)',
    'add    FOREIGN KEY (group_id) REFERENCES groups (id) ON DELETE SET NULL',
    'drop   CONSTRAINT IF EXISTS idx_email CASCADE',
)

# ALTER TABLE users RENAME TO accounts
await db.schema('TABLE users').alter('RENAME TO accounts')

# ALTER TABLE users RENAME email TO email_address
await db.schema('TABLE users').alter('RENAME email TO email_address')

# ALTER TABLE users RENAME CONSTRAINT idx_name TO index_name
await db.schema('TABLE users').alter('RENAME CONSTRAINT idx_name TO index_name')

# ALTER TABLE users ADD COLUMN address text
await db.schema('TABLE users').alter('ADD COLUMN address text')

# ALTER TABLE users DROP address
await db.schema('TABLE users').alter('DROP address')

# CREATE INDEX idx_email ON users (name, email)
await db.schema('INDEX idx_email ON users').create('name', 'email')

# CREATE UNIQUE INDEX unique_name ON users(name) WHERE soft_deleted = FALSE
await db.schema('UNIQUE INDEX unique_name ON users').create('name',).where('soft_deleted', False)

# DROP INDEX idx_email CASCADE
await db.schema('INDEX idx_email').drop('CASCADE')

# DROP TABLE users
await db.schema('TABLE users').drop()

Raw

The raw method can be used to execute any form of SQL. Usually the raw method is used to execute complex hard-coded (versus dynamically built) queries. It's also very common to use raw method to run migrations.

The input to raw method is not validated, so it is not safe from SQL injection.

RAW for complex SQL

await db.raw('SELECT ROUND(AVG(group_id),1) AS avg_id, COUNT(1) AS total_users FROM users WHERE id in ($1, $2, $3)', 4, 5, 6)

await db.raw("SELECT * FROM (VALUES (1, 'one'), (2, 'two'), (3, 'three')) AS t (num, letter)")

await db.raw("""
    INSERT INTO user (id, name)
        SELECT $1, $2 WHERE NOT EXISTS (SELECT id FROM users WHERE id = $1)
""", 1, 'Tom')

RAW for migration

await db.raw("""
    CREATE TABLE users(
        id                       INT NOT NULL,
        created_at               DATE NOT NULL,
        first_name               VARCHAR(100) NOT NULL,
        last_name                VARCHAR(100) NOT NULL,
        birthday_mmddyyyy        CHAR(10) NOT NULL,
    )
""")

WITH Clause using VALUES Lists

The Postgres VALUES provides a way to generate a "constant table" from a list of values. Together with the WITH clause, a small set of data can be loaded into the DB and queried like a table.

SELECT using WITH VALUES

# WITH "my_values" ("text_col", "bool_col", "num_col", "dict_col", "datetime_col", "null_col", "null_col2") AS
#   (VALUES 
#     ('Tom', TRUE, 2, '{"id": 1}'::jsonb, '2021-07-20 10:00:00+00:00'::timestamptz, NULL, NULL)
#   )
# SELECT * FROM "my_values"
result = await db.with_values('my_values', {
    'text_col': 'Tom',
    'bool_col': True,
    'num_col': 2,
    'dict_col': {'id': 1},
    'datetime_col': datetime.now(),
    'null_col': 'null',
    'null_col2': None
}).table('my_values').select()
result[0]['text_col']      # 'Tom'
result[0]['bool_col']      # True
result[0]['num_col']       # 2
result[0]['dict_col']      # '{"id": 1}'
result[0]['datetime_col']  # datetime.datetime(2021, 7, 20, 10, 0, tzinfo=datetime.timezone.utc)
result[0]['null_col']      # None
result[0]['null_col2']     # None

# join other tables
# WITH "workers" ("task_id", "name") AS 
#   (VALUES 
#     (1, 'Tom'), 
#     (2, 'Jerry')
#   ) 
# SELECT
#   "workers"."name" AS "worker_name",
#   "tasks"."name" AS "task_name"
# FROM "workers"
# JOIN "tasks" ON "workers"."task_id" = "tasks"."id"
await db.with_values('workers', {
    'task_id': 1,
    'name': 'Tom'
}, {
    'task_id': 2,
    'name': 'Jerry'
}).table('workers').select(
    'workers.name AS worker_name',
    'tasks.name AS task_name'
).join('tasks', 'workers.task_id = tasks.id').order_by('tasks.id')

# multiple WITH VALUES
# WITH "workers1" ("task_id", "name") AS
#   (VALUES
#     (1, 'Tom'),
#     (2, 'Jerry')
#   ), "workers2" ("task_id", "name") AS
#   (VALUES
#     (1, 'Topsy'), 
#     (2, 'Nibbles')
#   )
# SELECT
#   "workers1"."name" AS "primary_worker_name",
#   "workers2"."name" AS "secondary_worker_name",
#   "tasks"."name" AS "task_name"
# FROM "tasks"
# JOIN "workers1" ON "workers1"."task_id" = "tasks"."id"
# JOIN "workers2" ON "workers2"."task_id" = "tasks"."id"
await db.with_values('workers1', {
    'task_id': 1,
    'name': 'Tom'
}, {
    'task_id': 2,
    'name': 'Jerry'
}).with_values('workers2', {
    'task_id': 1,
    'name': 'Topsy'
}, {
    'task_id': 2,
    'name': 'Nibbles'
}).table('tasks').select(
    'workers1.name AS primary_worker_name',
    'workers2.name AS secondary_worker_name',
    'tasks.name AS task_name'
).join('workers1', 'workers1.task_id = tasks.id').\
    join('workers2', 'workers2.task_id = tasks.id')

UPDATE using WITH VALUES

# WITH "workers" ("task_id", "name") AS
#   (VALUES
#     (1, 'Tom'), 
#     (2, 'Jerry')
#   )
# UPDATE "tasks" 
# SET
#   "name" = "tasks"."name" || ' (worked by ' || "workers"."name" || ')'
# FROM "workers"
# WHERE
#   "workers"."task_id" = "tasks"."id"
# RETURNING
#   "workers"."name" AS "worker_name",
#   "tasks"."name" AS "task_name"
await db.with_values('workers', {
    'task_id': 1,
    'name': 'Tom'
}, {
    'task_id': 2,
    'name': 'Jerry'
}).table('tasks').update("name = tasks.name || ' (worked by ' || workers.name || ')'").\
    from_table('workers').\
    where('workers.task_id = tasks.id').\
    returning(
        'workers.name AS worker_name',
        'tasks.name AS task_name'
    )

RAW using WITH VALUES

# WITH "workers" ("task_id", "name") AS
#   (VALUES
#     (1, 'Tom'), 
#     (2, 'Jerry')
#   )
# SELECT * FROM tasks WHERE EXISTS(
#   SELECT 1 FROM workers
#   JOIN task_results ON workers.task_id = task_results.task_id
#   WHERE workers.task_id = tasks.id
# )
await db.with_values('workers', {
    'task_id': 1,
    'name': 'Tom'
}, {
    'task_id': 2,
    'name': 'Jerry'
}).raw("""
SELECT * FROM tasks WHERE EXISTS(
    SELECT 1 FROM workers
    JOIN task_results ON workers.task_id = task_results.task_id
    WHERE workers.task_id = tasks.id
)
""")

JSONB examples

Methods are created to support jsonb data type for some simple use cases.

Create a table with jsonb data type

# CREATE TABLE users (
#    id     serial PRIMARY KEY,
#    data   jsonb
#)
await db.schema('TABLE users').create(
    'id     serial PRIMARY KEY',
    'data   jsonb',
)

Select jsonb field

# SELECT data->name AS name, data->>name AS name_text FROM users
rows = await db.table('users').select('data', 'data->name AS name', 'data->>name AS name_text')
# rows[0]['data'] == '{"name":"Tom"}'
# rows[0]['name'] == '"Tom"'
# rows[0]['name_text'] == 'Tom'

# SELECT data->name AS name FROM users WHERE data->>name LIKE 'Tom%'
await db.table('users').select('data->name AS name').where('data->>name', 'LIKE', 'Tom%')

# SELECT data->name AS name FROM users WHERE data->name = '"Tom"'
await db.table('users').select('data->name AS name').where("data->name", 'Tom')

Insert jsonb field

# INSERT INTO users (data) VALUES
#   ('{"name": "Tom"}'),
#   ('{"name": "Jerry"}')
#   RETURNING *
await db.table('users').insert(
    {'data': {'name': 'Tom'}},
    {'data': {'name': 'Jerry'}},
).returning()

Update jsonb field

# UPDATE SET data = '{"address": {"city": "New York"}}'
await db.table('users').update({'data': {'address': {'city': 'New York'}}})

# UPDATE SET data = jsonb_set(data, '{address,city}', '"Chicago"')
await db.table('users').update({'data->address->city': 'Chicago'})

Migrations

Windyquery has a preliminary support for database migrations. The provided command-line script is called wq.

Generate a migration file

A migration file can be created by,

# this creates a timestamped migration file, e.g. "20210705233408_create_my_table.py"
$ wq make_migration --name=create_my_table

By default, the new file is add to database/migrations/ under the current working directory. If the diretory does not exist, it will be created first. The file contains an empty function to be filled by the user,

async def run(db):
    # TODO: add code here
    pass

Some sample migration templates are provided at here. They can be automatically inserted in the generated file by specifying the --template parameter,

# the generated file is pre-filled with some code template,
# async def run(db):
#     await db.schema('TABLE my_table').create(
#         'id      serial PRIMARY KEY',
#         'name    text not null unique',
#     )
$ wq make_migration --name=create_my_table --template="create table"

# create a migration file that contains all avaiable templates
$ wq make_migration --name=create_my_table --template=all

Run migrations

To run all of the outstanding migrations, use the migrate sub-command,

$ wq migrate --host=localhost --port=5432 --database=my-db --username=my-name --password=my-pass

# alternatively, the DB config can be provided by using environment variables
$ DB_HOST=localhost DB_PORT=5432 DB_DATABASE=my-db DB_USERNAME=my-name DB_PASSWORD=my-pass wq migrate

Use custom directory and database table

The wq command requires a directory to save the migration files, and a database table to store executed migrations. By default, the migration directory is database/migrations/ under the current working directroy, and the database table is named migrations. They are created automatically if they do not already exist. The directory and table name can be customized by using --migration_dir and --migration_table parameters,

# creates the migrations file in "my_db_work/migrations/" of the current directory
$ wq make_migration --name=create_my_table --migrations_dir="my_db_work/migrations"

# looks for outstanding migrations in "my_db_work/migrations/" and stores finished migrations in my_migrations table in DB
$ wq migrate --host=localhost --port=5432 --database=my-db --username=my-name --password=my-pass --migrations_dir="my_db_work/migrations" --migrations_table=my_migrations

Syntax checker

A very important part of windyquery is to validate the inputs of the various builder methods. It defines a Validator class, which is used to reject input strings not following the proper syntax. As a result, it can be used separately as a syntax checker for other DB libraries. For example, it is very common for REST API to support filtering or searching parameters specified by the users,

......
# GET /example-api/users?name=Tom&state=AZ;DROP%20TABLE%20Students
url_query = "name=Tom&state=AZ;DROP TABLE Students"
where = url_query.replace("&", " AND ")

from windyquery.validator import Validator
from windyquery.validator import ValidationError
from windyquery.ctx import Ctx

try:
    ctx = Ctx()
    validator = Validator()
    where = validator.validate_where(where, ctx)
except ValidationError:
    abort(400, f'Invalid query parameters: {url_query}')

connection = psycopg2.connect(**dbConfig)
cursor = connection.cursor()
cursor.execute(f'SELECT * FROM users WHERE {where}')
......

Please note,

  • Except raw, all windyquery's own builder methods, such as select, update, where, and so on, already implicitly use these validation functions. They may be useful when used alone, for example, to help other DB libraries validate SQL snippets;
  • These validation functions only cover a very small (though commonly used) subset of SQL grammar of Postgres.

Listen for a notification

Postgres implements LISTEN/NOTIFY for interprocess communications. In order to listen on a channel, use the DB.listen() method. It returns an awaitable object, which resolves to a dict when a notification fires.

from windyquery.exceptions import ListenConnectionClosed

# method 1: manually call start() and stop()
listener = db.listen('my_table')
await listener.start()
try:
    for _ in range(100):
        result = await listener
        # or result = await listener.next()
        print(result) 
        # {
        #     'channel': 'my_table',
        #     'payload': 'payload fired by the notifier',
        #     'listener_pid': 7321,
        #     'notifier_pid': 7322
        # }
except ListenConnectionClosed as e:
    print(e)
finally:
    await listener.stop()

# method 2: use with statement
async with db.listen('my_table') as listener:
    for _ in range(100):
        result = await listener
        print(result)

RRULE

Windyquery has a rrule function that can "expand" a rrule string into it occurrences (a list of datetimes) by using dateutil. A values CTE is prepared from the rrule occurrences, which can be further used by other querries.

A simple rrule example

rruleStr = """
DTSTART:20210303T100000Z
RRULE:FREQ=DAILY;COUNT=5
"""

# WITH my_rrules ("rrule") AS 
# (VALUES
#   ('2021-03-03 10:00:00+00:00'::timestamptz),
#   ('2021-03-04 10:00:00+00:00'::timestamptz),
#   ('2021-03-05 10:00:00+00:00'::timestamptz),
#   ('2021-03-06 10:00:00+00:00'::timestamptz),
#   ('2021-03-07 10:00:00+00:00'::timestamptz)
# )
# SELECT * FROM my_rrules
await db.rrule('my_rrules', {'rrule': rruleStr}).table('my_rrules').select()

More than one rrules

rruleStr1 = """
DTSTART:20210303T100000Z
RRULE:FREQ=DAILY;COUNT=5
"""

rruleStr2 = """
DTSTART:20210303T100000Z
RRULE:FREQ=DAILY;INTERVAL=10;COUNT=3
RRULE:FREQ=DAILY;INTERVAL=5;COUNT=3
"""

# WITH my_rrules ("rrule") AS 
# (VALUES
#   ('2021-03-03 10:00:00+00:00'::timestamptz),
#   ('2021-03-04 10:00:00+00:00'::timestamptz),
#   ('2021-03-05 10:00:00+00:00'::timestamptz),
#   ('2021-03-06 10:00:00+00:00'::timestamptz),
#   ('2021-03-07 10:00:00+00:00'::timestamptz),
#   ('2021-03-03 10:00:00+00:00'::timestamptz),
#   ('2021-03-08 10:00:00+00:00'::timestamptz),
#   ('2021-03-13 10:00:00+00:00'::timestamptz),
#   ('2021-03-23 10:00:00+00:00'::timestamptz)
# )
# SELECT * FROM my_rrules
)
await db.rrule('my_rrules', {
        'rrule': rruleStr1
    }, {
        'rrule': rruleStr2
    }).table('my_rrules').select()

# the rrule field can also take a list of mulitple rrules.
# the previous example is equivalent to
await db.rrule('my_rrules', {
        'rrule': [rruleStr1, rruleStr2]
    }).table('my_rrules').select()

Use exrule

rruleStr = """
DTSTART:20210303T100000Z
RRULE:FREQ=DAILY;COUNT=5
"""

exruleStr = """
DTSTART:20210303T100000Z
RRULE:FREQ=DAILY;BYWEEKDAY=SA,SU
"""

# WITH my_rrules ("rrule") AS 
# (VALUES
#   ('2021-03-03 10:00:00+00:00'::timestamptz),
#   ('2021-03-04 10:00:00+00:00'::timestamptz),
#   ('2021-03-05 10:00:00+00:00'::timestamptz)
# )
# SELECT * FROM my_rrules
await db.rrule('my_rrules', {'rrule': rruleStr, 'exrule': exruleStr}).table('my_rrules').select()

Use rdate

# WITH my_rrules ("rrule") AS 
# (VALUES
#   ('2021-05-03 10:00:00+00:00'::timestamptz)
# )
# SELECT * FROM my_rrules
await db.rrule('my_rrules', {'rdate': '20210503T100000Z'}).table('my_rrules').select()

rruleStr = """
DTSTART:20210303T100000Z
RRULE:FREQ=DAILY;COUNT=5
"""

# WITH my_rrules ("rrule") AS 
# (VALUES
#   ('2021-03-03 10:00:00+00:00'::timestamptz),
#   ('2021-03-04 10:00:00+00:00'::timestamptz),
#   ('2021-03-05 10:00:00+00:00'::timestamptz),
#   ('2021-03-06 10:00:00+00:00'::timestamptz),
#   ('2021-03-07 10:00:00+00:00'::timestamptz),
#   ('2021-05-03 10:00:00+00:00'::timestamptz)
# )
# SELECT * FROM my_rrules
await db.rrule('my_rrules', {'rrule': rruleStr, 'rdate': '20210503T100000Z'}).table('my_rrules').select()

# similary to rrule, the rdate field can take a list of date strings
# WITH my_rrules ("rrule") AS 
# (VALUES
#   ('2021-03-03 10:00:00+00:00'::timestamptz),
#   ('2021-03-04 10:00:00+00:00'::timestamptz),
#   ('2021-03-05 10:00:00+00:00'::timestamptz),
#   ('2021-03-06 10:00:00+00:00'::timestamptz),
#   ('2021-03-07 10:00:00+00:00'::timestamptz),
#   ('2021-05-03 10:00:00+00:00'::timestamptz),
#   ('2021-06-03 10:00:00+00:00'::timestamptz)
# )
# SELECT * FROM my_rrules
await db.rrule('my_rrules', {'rrule': rruleStr, 'rdate': ['20210503T100000Z','20210603T100000Z']}).table('my_rrules').select()

Use exdate

rruleStr = """
DTSTART:20210303T100000Z
RRULE:FREQ=DAILY;COUNT=5
"""

# WITH my_rrules ("rrule") AS 
# (VALUES
#   ('2021-03-03 10:00:00+00:00'::timestamptz),
#   ('2021-03-05 10:00:00+00:00'::timestamptz),
#   ('2021-03-06 10:00:00+00:00'::timestamptz),
#   ('2021-03-07 10:00:00+00:00'::timestamptz)
# )
# SELECT * FROM my_rrules
await db.rrule('my_rrules', {'rrule': rruleStr, 'exdate': '20210304T100000Z'}).table('my_rrules').select()

# similary to rrule, the exdate field can take a list of date strings
# WITH my_rrules ("rrule") AS 
# (VALUES
#   ('2021-03-03 10:00:00+00:00'::timestamptz),
#   ('2021-03-05 10:00:00+00:00'::timestamptz),
#   ('2021-03-07 10:00:00+00:00'::timestamptz)
# )
# SELECT * FROM my_rrules
await db.rrule('my_rrules', {'rrule': rruleStr, 'exdate': ['20210304T100000Z','20210306T100000Z']}).table('my_rrules').select()

Use after, before, and between

rruleStr = """
DTSTART:20210715T100000Z
RRULE:FREQ=DAILY;COUNT=5
"""

# rrule_after returns the first recurrence after the given datetime dt.
# WITH my_rrules ("rrule") AS 
# (VALUES
#   ('2021-07-17 10:00:00+00:00'::timestamptz)
# )
# SELECT * FROM my_rrules
await db.rrule('my_rrules', {'rrule': rruleStr, 'rrule_after': {'dt': '20210716T100000Z'}}]}).table('my_rrules').select()

# if the inc keyword is True dt is included if it is an occurrence.
# WITH my_rrules ("rrule") AS 
# (VALUES
#   ('2021-07-16 10:00:00+00:00'::timestamptz)
# )
# SELECT * FROM my_rrules
await db.rrule('my_rrules', {'rrule': rruleStr, 'rrule_after': {'dt': '20210716T100000Z', 'inc': True}}]}).table('my_rrules').select()

# rrule_before returns the last recurrence before the given datetime dt.
# WITH my_rrules ("rrule") AS 
# (VALUES
#   ('2021-07-15 10:00:00+00:00'::timestamptz)
# )
# SELECT * FROM my_rrules
await db.rrule('my_rrules', {'rrule': rruleStr, 'rrule_before': {'dt': '20210716T100000Z'}}]}).table('my_rrules').select()

# if the inc keyword is True dt is included if it is an occurrence.
# WITH my_rrules ("rrule") AS 
# (VALUES
#   ('2021-07-16 10:00:00+00:00'::timestamptz)
# )
# SELECT * FROM my_rrules
await db.rrule('my_rrules', {'rrule': rruleStr, 'rrule_before': {'dt': '20210716T100000Z', 'inc': True}}]}).table('my_rrules').select()

# rrule_between returns all the occurrences of the rrule between after and before.
# WITH my_rrules ("rrule") AS 
# (VALUES
#   ('2021-07-17 10:00:00+00:00'::timestamptz)
#   ('2021-07-18 10:00:00+00:00'::timestamptz)
# )
# SELECT * FROM my_rrules
await db.rrule('my_rrules', {'rrule': rruleStr, 'rrule_between': {'after': '20210716T100000Z', 'before': '20210719T100000Z'}}]}).table('my_rrules').select()

# if the inc keyword is True after and/or before are included if they are occurrences.
# WITH my_rrules ("rrule") AS 
# (VALUES
#   ('2021-07-16 10:00:00+00:00'::timestamptz)
#   ('2021-07-17 10:00:00+00:00'::timestamptz)
#   ('2021-07-18 10:00:00+00:00'::timestamptz)
#   ('2021-07-19 10:00:00+00:00'::timestamptz)
# )
# SELECT * FROM my_rrules
await db.rrule('my_rrules', {'rrule': rruleStr, 'rrule_between': {'after': '20210716T100000Z', 'before': '20210719T100000Z', 'inc': True}}]}).table('my_rrules').select()

Join rrule with other tables

import datetime

rruleStr1 = """
DTSTART:20210303T100000Z
RRULE:FREQ=DAILY;COUNT=5
"""

rruleStr2 = """
DTSTART:20210303T100000Z
RRULE:FREQ=DAILY;INTERVAL=10;COUNT=3
RRULE:FREQ=DAILY;INTERVAL=5;COUNT=3
"""

# WITH task_rrules ("task_id", "rrule") AS 
# (VALUES
#   (1, '2021-03-03 10:00:00+00:00'::timestamptz),
#   (1, '2021-03-04 10:00:00+00:00'::timestamptz),
#   (1, '2021-03-05 10:00:00+00:00'::timestamptz),
#   (1, '2021-03-06 10:00:00+00:00'::timestamptz),
#   (1, '2021-03-07 10:00:00+00:00'::timestamptz),
#   (2, '2021-03-03 10:00:00+00:00'::timestamptz),
#   (2, '2021-03-08 10:00:00+00:00'::timestamptz),
#   (2, '2021-03-13 10:00:00+00:00'::timestamptz),
#   (2, '2021-03-23 10:00:00+00:00'::timestamptz)
# )
# SELECT task_rrules.rrule, tasks.name
# FROM task_rrules
# JOIN tasks ON tasks.id = task_rrules.task_id
# WHERE
#   rrule > '2021-03-05 10:00:00+00:00' AND
#   rrule < '2021-03-08 10:00:00+00:00'
await db.rrule('task_rrules', {
        'task_id': 1, 'rrule': rruleStr1
    }, {
        'task_id': 2, 'rrule': rruleStr2
    }).table('task_rrules').
    join('tasks', 'tasks.id', '=', 'task_rrules.task_id').
    where('rrule > ? AND rrule < ?',
        datetime.datetime(2021, 3, 5, 10, 0,
                tzinfo=datetime.timezone.utc),
        datetime.datetime(2021, 3, 8, 10, 0,
                tzinfo=datetime.timezone.utc),
    ).select('task_rrules.rrule', 'tasks.name')

Using rrule in update

import datetime

rruleStr1 = """
DTSTART:20210303T100000Z
RRULE:FREQ=DAILY;COUNT=5
"""

rruleStr2 = """
DTSTART:20210303T100000Z
RRULE:FREQ=DAILY;INTERVAL=10;COUNT=3
RRULE:FREQ=DAILY;INTERVAL=5;COUNT=3
"""

# WITH task_rrules ("task_id", "rrule") AS 
# (VALUES
#   (1, '2021-03-03 10:00:00+00:00'::timestamptz),
#   (1, '2021-03-04 10:00:00+00:00'::timestamptz),
#   (1, '2021-03-05 10:00:00+00:00'::timestamptz),
#   (1, '2021-03-06 10:00:00+00:00'::timestamptz),
#   (1, '2021-03-07 10:00:00+00:00'::timestamptz),
#   (2, '2021-03-03 10:00:00+00:00'::timestamptz),
#   (2, '2021-03-08 10:00:00+00:00'::timestamptz),
#   (2, '2021-03-13 10:00:00+00:00'::timestamptz),
#   (2, '2021-03-23 10:00:00+00:00'::timestamptz)
# )
# UPDATE tasks SET result = 'done'
# FROM task_rrules
# WHERE task_rrules.task_id = tasks.id
await db.rrule('task_rrules', {
        'task_id': 1, 'rrule': rruleStr1
    }, {
        'task_id': 2, 'rrule': rruleStr2
    }).table('tasks').update("result = 'done'").
    from_table('task_rrules').
    where('task_rrules.task_id = tasks.id')

Using rrule with raw method

import datetime

rruleStr1 = """
DTSTART:20210303T100000Z
RRULE:FREQ=DAILY;COUNT=5
"""

rruleStr2 = """
DTSTART:20210303T100000Z
RRULE:FREQ=DAILY;INTERVAL=10;COUNT=3
RRULE:FREQ=DAILY;INTERVAL=5;COUNT=3
"""

# WITH task_rrules ("task_id", "rrule") AS 
# (VALUES
#   (1, '2021-03-03 10:00:00+00:00'::timestamptz),
#   (1, '2021-03-04 10:00:00+00:00'::timestamptz),
#   (1, '2021-03-05 10:00:00+00:00'::timestamptz),
#   (1, '2021-03-06 10:00:00+00:00'::timestamptz),
#   (1, '2021-03-07 10:00:00+00:00'::timestamptz),
#   (2, '2021-03-03 10:00:00+00:00'::timestamptz),
#   (2, '2021-03-08 10:00:00+00:00'::timestamptz),
#   (2, '2021-03-13 10:00:00+00:00'::timestamptz),
#   (2, '2021-03-23 10:00:00+00:00'::timestamptz)
# )
# DELETE FROM tasks
# WHERE EXISTS(
#   SELECT 1 FROM task_rrules
#   WHERE
#     task_id = tasks.id AND
#     rrule > '2021-03-20 10:00:00+00:00'
# )
# RETURNING id, task_id
await db.rrule('task_rrules', {
        'task_id': 1, 'rrule': rruleStr1
    }, {
        'task_id': 3, 'rrule': rruleStr2
    }).raw("""
        DELETE FROM tasks
        WHERE EXISTS(
            SELECT 1 FROM task_rrules
            WHERE 
                task_id = tasks.id AND
                rrule > $1
        )
        RETURNING id, task_id
    """, datetime.datetime(2021, 3, 20, 10, 0,
                tzinfo=datetime.timezone.utc))

Using a slice to limit the occurrences

import datetime

rruleStr = """
DTSTART:20210303T100000Z
RRULE:FREQ=DAILY
"""

# WITH my_rrules ("task_id", "rrule") AS 
# (VALUES
#   (1, '2021-03-03 10:00:00+00:00'::timestamptz),
#   (1, '2021-03-04 10:00:00+00:00'::timestamptz),
#   (1, '2021-03-05 10:00:00+00:00'::timestamptz),
# )
# SELECT * FROM my_rrules
await db.rrule('my_rrules', {'rrule': rruleStr, 'rrule_slice': slice(3)}).table('my_rrules').select()

# WITH my_rrules ("task_id", "rrule") AS 
# (VALUES
#   (1, '2021-03-13 10:00:00+00:00'::timestamptz),
#   (1, '2021-03-15 10:00:00+00:00'::timestamptz),
#   (1, '2021-03-17 10:00:00+00:00'::timestamptz),
#   (1, '2021-03-19 10:00:00+00:00'::timestamptz),
#   (1, '2021-03-21 10:00:00+00:00'::timestamptz),
# )
# SELECT * FROM my_rrules
await db.rrule('my_rrules', {'rrule': rruleStr, 'rrule_slice': slice(10,20,2)}).table('my_rrules').select()

Tests

Windyquery includes tests. These tests are also served as examples on how to use this library.

Running tests

Install pytest to run the included tests,

pip install -U pytest

Set up a postgres server with preloaded data. This can be done by using docker with the official postgre docker image,

docker run --rm --name windyquery-test -p 5432:5432 -v ${PWD}/windyquery/tests/seed_test_data.sql:/docker-entrypoint-initdb.d/seed_test_data.sql -e POSTGRES_USER=windyquery-test -e POSTGRES_PASSWORD=windyquery-test -e POSTGRES_DB=windyquery-test -d postgres:12-alpine

Note: to use existing postgres server, it must be configured to have the correct user, password, and database needed in tests/conftest.py. Data needed by tests is in tests/seed_test_data.sql.

To run the tests,

pytest
You might also like...
The ormar package is an async mini ORM for Python, with support for Postgres, MySQL, and SQLite.

python async mini orm with fastapi in mind and pydantic validation

A pure Python Database Abstraction Layer

pyDAL pyDAL is a pure Python Database Abstraction Layer. It dynamically generates the SQL/noSQL in realtime using the specified dialect for the databa

Rich Python data types for Redis

Created by Stephen McDonald Introduction HOT Redis is a wrapper library for the redis-py client. Rather than calling the Redis commands directly from

A Python Object-Document-Mapper for working with MongoDB

MongoEngine Info: MongoEngine is an ORM-like layer on top of PyMongo. Repository: https://github.com/MongoEngine/mongoengine Author: Harry Marr (http:

Beanie - is an Asynchronous Python object-document mapper (ODM) for MongoDB

Beanie - is an Asynchronous Python object-document mapper (ODM) for MongoDB, based on Motor and Pydantic.

Solrorm : A sort-of solr ORM for python

solrorm : A sort-of solr ORM for python solrpy - deprecated solrorm - currently in dev Usage Cores The first step to interact with solr using solrorm

Easy-to-use data handling for SQL data stores with support for implicit table creation, bulk loading, and transactions.

dataset: databases for lazy people In short, dataset makes reading and writing data in databases as simple as reading and writing JSON files. Read the

Piccolo - A fast, user friendly ORM and query builder which supports asyncio.

A fast, user friendly ORM and query builder which supports asyncio.

PyPika is a python SQL query builder that exposes the full richness of the SQL language using a syntax that reflects the resulting query. PyPika excels at all sorts of SQL queries but is especially useful for data analysis.

PyPika - Python Query Builder Abstract What is PyPika? PyPika is a Python API for building SQL queries. The motivation behind PyPika is to provide a s

Estoult - a Python toolkit for data mapping with an integrated query builder for SQL databases
Estoult - a Python toolkit for data mapping with an integrated query builder for SQL databases

Estoult Estoult is a Python toolkit for data mapping with an integrated query builder for SQL databases. It currently supports MySQL, PostgreSQL, and

A fast PostgreSQL Database Client Library for Python/asyncio.
A fast PostgreSQL Database Client Library for Python/asyncio.

asyncpg -- A fast PostgreSQL Database Client Library for Python/asyncio asyncpg is a database interface library designed specifically for PostgreSQL a

aiopg is a library for accessing a PostgreSQL database from the asyncio

aiopg aiopg is a library for accessing a PostgreSQL database from the asyncio (PEP-3156/tulip) framework. It wraps asynchronous features of the Psycop

Continuous Query Decomposition for Complex Query Answering in Incomplete Knowledge Graphs

Continuous Query Decomposition This repository contains the official implementation for our ICLR 2021 (Oral) paper, Complex Query Answering with Neura

Code for ACL 21: Generating Query Focused Summaries from Query-Free Resources

marge This repository releases the code for Generating Query Focused Summaries from Query-Free Resources. Please cite the following paper [bib] if you

Python module that parse power builder file (PBD) and analyze code

PowerBuilder-decompile Python module that parse power builder file (PBD) and analyze code (Incomplete) this tool is composed of: pbd_dump.py pbd file

Virtual Python Environment builder

virtualenv A tool for creating isolated virtual python environments. Installation Documentation Changelog Issues PyPI Github Code of Conduct Everyone

A python HTML builder library.

PyML A python HTML builder library. Goals Fully functional html builder similar to the javascript node manipulation. Implement an html parser that ret

A simple application builder. Made with python.
A simple application builder. Made with python.

Python Flask Server Template Check the Github Repository for updates Flask is an application builder. It is very common in Python but can also be used

CloudFormation template and CDK stack that contains a CustomResource with Lambda function to allow the setting of the targetAccountIds attribute of the EC2 Image Builder AMI distribution settings which is not currently supported (as of October 2021) in CloudFormation or CDK.
Comments
  • Error installing windyquery using Python 3.11

    Error installing windyquery using Python 3.11

    Python version: 3.11 Windyquery version: 0.0.33

    Issue: Can not install windyquery package

    Error:

    pip install windyquery
    Collecting windyquery
      Using cached windyquery-0.0.33-py3-none-any.whl (107 kB)
    Collecting asyncpg==0.23.0
      Using cached asyncpg-0.23.0.tar.gz (775 kB)
      Preparing metadata (setup.py) ... done
    Requirement already satisfied: ply==3.11 in ./venv/lib/python3.11/site-packages (from windyquery) (3.11)
    Requirement already satisfied: python-dateutil==2.8.1 in ./venv/lib/python3.11/site-packages (from windyquery) (2.8.1)
    Requirement already satisfied: fire==0.4.0 in ./venv/lib/python3.11/site-packages (from windyquery) (0.4.0)
    Requirement already satisfied: six in ./venv/lib/python3.11/site-packages (from fire==0.4.0->windyquery) (1.16.0)
    Requirement already satisfied: termcolor in ./venv/lib/python3.11/site-packages (from fire==0.4.0->windyquery) (2.1.1)
    Building wheels for collected packages: asyncpg
      Building wheel for asyncpg (setup.py) ... error
      error: subprocess-exited-with-error
      
      × python setup.py bdist_wheel did not run successfully.
      │ exit code: 1
      ╰─> [109 lines of output]
          running bdist_wheel
          running build
          running build_py
          creating build
          creating build/lib.macosx-12-x86_64-3.11
          creating build/lib.macosx-12-x86_64-3.11/asyncpg
          copying asyncpg/compat.py -> build/lib.macosx-12-x86_64-3.11/asyncpg
          copying asyncpg/_version.py -> build/lib.macosx-12-x86_64-3.11/asyncpg
          copying asyncpg/transaction.py -> build/lib.macosx-12-x86_64-3.11/asyncpg
          copying asyncpg/__init__.py -> build/lib.macosx-12-x86_64-3.11/asyncpg
          copying asyncpg/types.py -> build/lib.macosx-12-x86_64-3.11/asyncpg
          copying asyncpg/serverversion.py -> build/lib.macosx-12-x86_64-3.11/asyncpg
          copying asyncpg/prepared_stmt.py -> build/lib.macosx-12-x86_64-3.11/asyncpg
          copying asyncpg/connection.py -> build/lib.macosx-12-x86_64-3.11/asyncpg
          copying asyncpg/utils.py -> build/lib.macosx-12-x86_64-3.11/asyncpg
          copying asyncpg/cluster.py -> build/lib.macosx-12-x86_64-3.11/asyncpg
          copying asyncpg/connect_utils.py -> build/lib.macosx-12-x86_64-3.11/asyncpg
          copying asyncpg/introspection.py -> build/lib.macosx-12-x86_64-3.11/asyncpg
          copying asyncpg/connresource.py -> build/lib.macosx-12-x86_64-3.11/asyncpg
          copying asyncpg/pool.py -> build/lib.macosx-12-x86_64-3.11/asyncpg
          copying asyncpg/cursor.py -> build/lib.macosx-12-x86_64-3.11/asyncpg
          running egg_info
          warning: no files found matching '*.py' under directory 'examples'
          writing manifest file 'asyncpg.egg-info/SOURCES.txt'
          creating build/lib.macosx-12-x86_64-3.11/asyncpg/_testbase
          copying asyncpg/_testbase/__init__.py -> build/lib.macosx-12-x86_64-3.11/asyncpg/_testbase
          copying asyncpg/_testbase/fuzzer.py -> build/lib.macosx-12-x86_64-3.11/asyncpg/_testbase
          creating build/lib.macosx-12-x86_64-3.11/asyncpg/exceptions
          copying asyncpg/exceptions/__init__.py -> build/lib.macosx-12-x86_64-3.11/asyncpg/exceptions
          copying asyncpg/exceptions/_base.py -> build/lib.macosx-12-x86_64-3.11/asyncpg/exceptions
          creating build/lib.macosx-12-x86_64-3.11/asyncpg/pgproto
          copying asyncpg/pgproto/__init__.pxd -> build/lib.macosx-12-x86_64-3.11/asyncpg/pgproto
          copying asyncpg/pgproto/__init__.py -> build/lib.macosx-12-x86_64-3.11/asyncpg/pgproto
          copying asyncpg/pgproto/buffer.pxd -> build/lib.macosx-12-x86_64-3.11/asyncpg/pgproto
          copying asyncpg/pgproto/buffer.pyx -> build/lib.macosx-12-x86_64-3.11/asyncpg/pgproto
          copying asyncpg/pgproto/consts.pxi -> build/lib.macosx-12-x86_64-3.11/asyncpg/pgproto
          copying asyncpg/pgproto/cpythonx.pxd -> build/lib.macosx-12-x86_64-3.11/asyncpg/pgproto
          copying asyncpg/pgproto/debug.h -> build/lib.macosx-12-x86_64-3.11/asyncpg/pgproto
          copying asyncpg/pgproto/debug.pxd -> build/lib.macosx-12-x86_64-3.11/asyncpg/pgproto
          copying asyncpg/pgproto/frb.pxd -> build/lib.macosx-12-x86_64-3.11/asyncpg/pgproto
          copying asyncpg/pgproto/frb.pyx -> build/lib.macosx-12-x86_64-3.11/asyncpg/pgproto
          copying asyncpg/pgproto/hton.h -> build/lib.macosx-12-x86_64-3.11/asyncpg/pgproto
          copying asyncpg/pgproto/hton.pxd -> build/lib.macosx-12-x86_64-3.11/asyncpg/pgproto
          copying asyncpg/pgproto/pgproto.c -> build/lib.macosx-12-x86_64-3.11/asyncpg/pgproto
          copying asyncpg/pgproto/pgproto.pxd -> build/lib.macosx-12-x86_64-3.11/asyncpg/pgproto
          copying asyncpg/pgproto/pgproto.pyx -> build/lib.macosx-12-x86_64-3.11/asyncpg/pgproto
          copying asyncpg/pgproto/tohex.h -> build/lib.macosx-12-x86_64-3.11/asyncpg/pgproto
          copying asyncpg/pgproto/tohex.pxd -> build/lib.macosx-12-x86_64-3.11/asyncpg/pgproto
          copying asyncpg/pgproto/types.py -> build/lib.macosx-12-x86_64-3.11/asyncpg/pgproto
          copying asyncpg/pgproto/uuid.pyx -> build/lib.macosx-12-x86_64-3.11/asyncpg/pgproto
          creating build/lib.macosx-12-x86_64-3.11/asyncpg/pgproto/codecs
          copying asyncpg/pgproto/codecs/__init__.pxd -> build/lib.macosx-12-x86_64-3.11/asyncpg/pgproto/codecs
          copying asyncpg/pgproto/codecs/bits.pyx -> build/lib.macosx-12-x86_64-3.11/asyncpg/pgproto/codecs
          copying asyncpg/pgproto/codecs/bytea.pyx -> build/lib.macosx-12-x86_64-3.11/asyncpg/pgproto/codecs
          copying asyncpg/pgproto/codecs/context.pyx -> build/lib.macosx-12-x86_64-3.11/asyncpg/pgproto/codecs
          copying asyncpg/pgproto/codecs/datetime.pyx -> build/lib.macosx-12-x86_64-3.11/asyncpg/pgproto/codecs
          copying asyncpg/pgproto/codecs/float.pyx -> build/lib.macosx-12-x86_64-3.11/asyncpg/pgproto/codecs
          copying asyncpg/pgproto/codecs/geometry.pyx -> build/lib.macosx-12-x86_64-3.11/asyncpg/pgproto/codecs
          copying asyncpg/pgproto/codecs/hstore.pyx -> build/lib.macosx-12-x86_64-3.11/asyncpg/pgproto/codecs
          copying asyncpg/pgproto/codecs/int.pyx -> build/lib.macosx-12-x86_64-3.11/asyncpg/pgproto/codecs
          copying asyncpg/pgproto/codecs/json.pyx -> build/lib.macosx-12-x86_64-3.11/asyncpg/pgproto/codecs
          copying asyncpg/pgproto/codecs/jsonpath.pyx -> build/lib.macosx-12-x86_64-3.11/asyncpg/pgproto/codecs
          copying asyncpg/pgproto/codecs/misc.pyx -> build/lib.macosx-12-x86_64-3.11/asyncpg/pgproto/codecs
          copying asyncpg/pgproto/codecs/network.pyx -> build/lib.macosx-12-x86_64-3.11/asyncpg/pgproto/codecs
          copying asyncpg/pgproto/codecs/numeric.pyx -> build/lib.macosx-12-x86_64-3.11/asyncpg/pgproto/codecs
          copying asyncpg/pgproto/codecs/pg_snapshot.pyx -> build/lib.macosx-12-x86_64-3.11/asyncpg/pgproto/codecs
          copying asyncpg/pgproto/codecs/text.pyx -> build/lib.macosx-12-x86_64-3.11/asyncpg/pgproto/codecs
          copying asyncpg/pgproto/codecs/tid.pyx -> build/lib.macosx-12-x86_64-3.11/asyncpg/pgproto/codecs
          copying asyncpg/pgproto/codecs/uuid.pyx -> build/lib.macosx-12-x86_64-3.11/asyncpg/pgproto/codecs
          creating build/lib.macosx-12-x86_64-3.11/asyncpg/protocol
          copying asyncpg/protocol/__init__.py -> build/lib.macosx-12-x86_64-3.11/asyncpg/protocol
          copying asyncpg/protocol/consts.pxi -> build/lib.macosx-12-x86_64-3.11/asyncpg/protocol
          copying asyncpg/protocol/coreproto.pxd -> build/lib.macosx-12-x86_64-3.11/asyncpg/protocol
          copying asyncpg/protocol/coreproto.pyx -> build/lib.macosx-12-x86_64-3.11/asyncpg/protocol
          copying asyncpg/protocol/cpythonx.pxd -> build/lib.macosx-12-x86_64-3.11/asyncpg/protocol
          copying asyncpg/protocol/encodings.pyx -> build/lib.macosx-12-x86_64-3.11/asyncpg/protocol
          copying asyncpg/protocol/pgtypes.pxi -> build/lib.macosx-12-x86_64-3.11/asyncpg/protocol
          copying asyncpg/protocol/prepared_stmt.pxd -> build/lib.macosx-12-x86_64-3.11/asyncpg/protocol
          copying asyncpg/protocol/prepared_stmt.pyx -> build/lib.macosx-12-x86_64-3.11/asyncpg/protocol
          copying asyncpg/protocol/protocol.c -> build/lib.macosx-12-x86_64-3.11/asyncpg/protocol
          copying asyncpg/protocol/protocol.pxd -> build/lib.macosx-12-x86_64-3.11/asyncpg/protocol
          copying asyncpg/protocol/protocol.pyx -> build/lib.macosx-12-x86_64-3.11/asyncpg/protocol
          copying asyncpg/protocol/scram.pxd -> build/lib.macosx-12-x86_64-3.11/asyncpg/protocol
          copying asyncpg/protocol/scram.pyx -> build/lib.macosx-12-x86_64-3.11/asyncpg/protocol
          copying asyncpg/protocol/settings.pxd -> build/lib.macosx-12-x86_64-3.11/asyncpg/protocol
          copying asyncpg/protocol/settings.pyx -> build/lib.macosx-12-x86_64-3.11/asyncpg/protocol
          creating build/lib.macosx-12-x86_64-3.11/asyncpg/protocol/codecs
          copying asyncpg/protocol/codecs/__init__.py -> build/lib.macosx-12-x86_64-3.11/asyncpg/protocol/codecs
          copying asyncpg/protocol/codecs/array.pyx -> build/lib.macosx-12-x86_64-3.11/asyncpg/protocol/codecs
          copying asyncpg/protocol/codecs/base.pxd -> build/lib.macosx-12-x86_64-3.11/asyncpg/protocol/codecs
          copying asyncpg/protocol/codecs/base.pyx -> build/lib.macosx-12-x86_64-3.11/asyncpg/protocol/codecs
          copying asyncpg/protocol/codecs/pgproto.pyx -> build/lib.macosx-12-x86_64-3.11/asyncpg/protocol/codecs
          copying asyncpg/protocol/codecs/range.pyx -> build/lib.macosx-12-x86_64-3.11/asyncpg/protocol/codecs
          copying asyncpg/protocol/codecs/record.pyx -> build/lib.macosx-12-x86_64-3.11/asyncpg/protocol/codecs
          copying asyncpg/protocol/codecs/textutils.pyx -> build/lib.macosx-12-x86_64-3.11/asyncpg/protocol/codecs
          creating build/lib.macosx-12-x86_64-3.11/asyncpg/protocol/record
          copying asyncpg/protocol/record/__init__.pxd -> build/lib.macosx-12-x86_64-3.11/asyncpg/protocol/record
          copying asyncpg/protocol/record/recordobj.c -> build/lib.macosx-12-x86_64-3.11/asyncpg/protocol/record
          copying asyncpg/protocol/record/recordobj.h -> build/lib.macosx-12-x86_64-3.11/asyncpg/protocol/record
          running build_ext
          creating build/temp.macosx-12-x86_64-3.11
          creating build/temp.macosx-12-x86_64-3.11/asyncpg
          creating build/temp.macosx-12-x86_64-3.11/asyncpg/pgproto
          clang -Wsign-compare -Wunreachable-code -fno-common -dynamic -DNDEBUG -g -fwrapv -O3 -Wall -isysroot /Library/Developer/CommandLineTools/SDKs/MacOSX12.sdk -I/Users/dimonoff/Repos/Bitbucket/RVE/smp-service/venv/include -I/usr/local/opt/[email protected]/Frameworks/Python.framework/Versions/3.11/include/python3.11 -c asyncpg/pgproto/pgproto.c -o build/temp.macosx-12-x86_64-3.11/asyncpg/pgproto/pgproto.o -O2 -fsigned-char -Wall -Wsign-compare -Wconversion
          asyncpg/pgproto/pgproto.c:223:12: fatal error: 'longintrepr.h' file not found
            #include "longintrepr.h"
                     ^~~~~~~~~~~~~~~
          1 error generated.
          error: command '/usr/bin/clang' failed with exit code 1
          [end of output]
      
      note: This error originates from a subprocess, and is likely not a problem with pip.
      ERROR: Failed building wheel for asyncpg
      Running setup.py clean for asyncpg
    Failed to build asyncpg
    Installing collected packages: asyncpg, windyquery
      Attempting uninstall: asyncpg
        Found existing installation: asyncpg 0.27.0
        Uninstalling asyncpg-0.27.0:
          Successfully uninstalled asyncpg-0.27.0
      Running setup.py install for asyncpg ... error
      error: subprocess-exited-with-error
      
      × Running setup.py install for asyncpg did not run successfully.
      │ exit code: 1
      ╰─> [111 lines of output]
          running install
          /Users/dimonoff/Repos/Bitbucket/RVE/smp-service/venv/lib/python3.11/site-packages/setuptools/command/install.py:34: SetuptoolsDeprecationWarning: setup.py install is deprecated. Use build and pip and other standards-based tools.
            warnings.warn(
          running build
          running build_py
          creating build
          creating build/lib.macosx-12-x86_64-3.11
          creating build/lib.macosx-12-x86_64-3.11/asyncpg
          copying asyncpg/compat.py -> build/lib.macosx-12-x86_64-3.11/asyncpg
          copying asyncpg/_version.py -> build/lib.macosx-12-x86_64-3.11/asyncpg
          copying asyncpg/transaction.py -> build/lib.macosx-12-x86_64-3.11/asyncpg
          copying asyncpg/__init__.py -> build/lib.macosx-12-x86_64-3.11/asyncpg
          copying asyncpg/types.py -> build/lib.macosx-12-x86_64-3.11/asyncpg
          copying asyncpg/serverversion.py -> build/lib.macosx-12-x86_64-3.11/asyncpg
          copying asyncpg/prepared_stmt.py -> build/lib.macosx-12-x86_64-3.11/asyncpg
          copying asyncpg/connection.py -> build/lib.macosx-12-x86_64-3.11/asyncpg
          copying asyncpg/utils.py -> build/lib.macosx-12-x86_64-3.11/asyncpg
          copying asyncpg/cluster.py -> build/lib.macosx-12-x86_64-3.11/asyncpg
          copying asyncpg/connect_utils.py -> build/lib.macosx-12-x86_64-3.11/asyncpg
          copying asyncpg/introspection.py -> build/lib.macosx-12-x86_64-3.11/asyncpg
          copying asyncpg/connresource.py -> build/lib.macosx-12-x86_64-3.11/asyncpg
          copying asyncpg/pool.py -> build/lib.macosx-12-x86_64-3.11/asyncpg
          copying asyncpg/cursor.py -> build/lib.macosx-12-x86_64-3.11/asyncpg
          running egg_info
          warning: no files found matching '*.py' under directory 'examples'
          writing manifest file 'asyncpg.egg-info/SOURCES.txt'
          creating build/lib.macosx-12-x86_64-3.11/asyncpg/_testbase
          copying asyncpg/_testbase/__init__.py -> build/lib.macosx-12-x86_64-3.11/asyncpg/_testbase
          copying asyncpg/_testbase/fuzzer.py -> build/lib.macosx-12-x86_64-3.11/asyncpg/_testbase
          creating build/lib.macosx-12-x86_64-3.11/asyncpg/exceptions
          copying asyncpg/exceptions/__init__.py -> build/lib.macosx-12-x86_64-3.11/asyncpg/exceptions
          copying asyncpg/exceptions/_base.py -> build/lib.macosx-12-x86_64-3.11/asyncpg/exceptions
          creating build/lib.macosx-12-x86_64-3.11/asyncpg/pgproto
          copying asyncpg/pgproto/__init__.pxd -> build/lib.macosx-12-x86_64-3.11/asyncpg/pgproto
          copying asyncpg/pgproto/__init__.py -> build/lib.macosx-12-x86_64-3.11/asyncpg/pgproto
          copying asyncpg/pgproto/buffer.pxd -> build/lib.macosx-12-x86_64-3.11/asyncpg/pgproto
          copying asyncpg/pgproto/buffer.pyx -> build/lib.macosx-12-x86_64-3.11/asyncpg/pgproto
          copying asyncpg/pgproto/consts.pxi -> build/lib.macosx-12-x86_64-3.11/asyncpg/pgproto
          copying asyncpg/pgproto/cpythonx.pxd -> build/lib.macosx-12-x86_64-3.11/asyncpg/pgproto
          copying asyncpg/pgproto/debug.h -> build/lib.macosx-12-x86_64-3.11/asyncpg/pgproto
          copying asyncpg/pgproto/debug.pxd -> build/lib.macosx-12-x86_64-3.11/asyncpg/pgproto
          copying asyncpg/pgproto/frb.pxd -> build/lib.macosx-12-x86_64-3.11/asyncpg/pgproto
          copying asyncpg/pgproto/frb.pyx -> build/lib.macosx-12-x86_64-3.11/asyncpg/pgproto
          copying asyncpg/pgproto/hton.h -> build/lib.macosx-12-x86_64-3.11/asyncpg/pgproto
          copying asyncpg/pgproto/hton.pxd -> build/lib.macosx-12-x86_64-3.11/asyncpg/pgproto
          copying asyncpg/pgproto/pgproto.c -> build/lib.macosx-12-x86_64-3.11/asyncpg/pgproto
          copying asyncpg/pgproto/pgproto.pxd -> build/lib.macosx-12-x86_64-3.11/asyncpg/pgproto
          copying asyncpg/pgproto/pgproto.pyx -> build/lib.macosx-12-x86_64-3.11/asyncpg/pgproto
          copying asyncpg/pgproto/tohex.h -> build/lib.macosx-12-x86_64-3.11/asyncpg/pgproto
          copying asyncpg/pgproto/tohex.pxd -> build/lib.macosx-12-x86_64-3.11/asyncpg/pgproto
          copying asyncpg/pgproto/types.py -> build/lib.macosx-12-x86_64-3.11/asyncpg/pgproto
          copying asyncpg/pgproto/uuid.pyx -> build/lib.macosx-12-x86_64-3.11/asyncpg/pgproto
          creating build/lib.macosx-12-x86_64-3.11/asyncpg/pgproto/codecs
          copying asyncpg/pgproto/codecs/__init__.pxd -> build/lib.macosx-12-x86_64-3.11/asyncpg/pgproto/codecs
          copying asyncpg/pgproto/codecs/bits.pyx -> build/lib.macosx-12-x86_64-3.11/asyncpg/pgproto/codecs
          copying asyncpg/pgproto/codecs/bytea.pyx -> build/lib.macosx-12-x86_64-3.11/asyncpg/pgproto/codecs
          copying asyncpg/pgproto/codecs/context.pyx -> build/lib.macosx-12-x86_64-3.11/asyncpg/pgproto/codecs
          copying asyncpg/pgproto/codecs/datetime.pyx -> build/lib.macosx-12-x86_64-3.11/asyncpg/pgproto/codecs
          copying asyncpg/pgproto/codecs/float.pyx -> build/lib.macosx-12-x86_64-3.11/asyncpg/pgproto/codecs
          copying asyncpg/pgproto/codecs/geometry.pyx -> build/lib.macosx-12-x86_64-3.11/asyncpg/pgproto/codecs
          copying asyncpg/pgproto/codecs/hstore.pyx -> build/lib.macosx-12-x86_64-3.11/asyncpg/pgproto/codecs
          copying asyncpg/pgproto/codecs/int.pyx -> build/lib.macosx-12-x86_64-3.11/asyncpg/pgproto/codecs
          copying asyncpg/pgproto/codecs/json.pyx -> build/lib.macosx-12-x86_64-3.11/asyncpg/pgproto/codecs
          copying asyncpg/pgproto/codecs/jsonpath.pyx -> build/lib.macosx-12-x86_64-3.11/asyncpg/pgproto/codecs
          copying asyncpg/pgproto/codecs/misc.pyx -> build/lib.macosx-12-x86_64-3.11/asyncpg/pgproto/codecs
          copying asyncpg/pgproto/codecs/network.pyx -> build/lib.macosx-12-x86_64-3.11/asyncpg/pgproto/codecs
          copying asyncpg/pgproto/codecs/numeric.pyx -> build/lib.macosx-12-x86_64-3.11/asyncpg/pgproto/codecs
          copying asyncpg/pgproto/codecs/pg_snapshot.pyx -> build/lib.macosx-12-x86_64-3.11/asyncpg/pgproto/codecs
          copying asyncpg/pgproto/codecs/text.pyx -> build/lib.macosx-12-x86_64-3.11/asyncpg/pgproto/codecs
          copying asyncpg/pgproto/codecs/tid.pyx -> build/lib.macosx-12-x86_64-3.11/asyncpg/pgproto/codecs
          copying asyncpg/pgproto/codecs/uuid.pyx -> build/lib.macosx-12-x86_64-3.11/asyncpg/pgproto/codecs
          creating build/lib.macosx-12-x86_64-3.11/asyncpg/protocol
          copying asyncpg/protocol/__init__.py -> build/lib.macosx-12-x86_64-3.11/asyncpg/protocol
          copying asyncpg/protocol/consts.pxi -> build/lib.macosx-12-x86_64-3.11/asyncpg/protocol
          copying asyncpg/protocol/coreproto.pxd -> build/lib.macosx-12-x86_64-3.11/asyncpg/protocol
          copying asyncpg/protocol/coreproto.pyx -> build/lib.macosx-12-x86_64-3.11/asyncpg/protocol
          copying asyncpg/protocol/cpythonx.pxd -> build/lib.macosx-12-x86_64-3.11/asyncpg/protocol
          copying asyncpg/protocol/encodings.pyx -> build/lib.macosx-12-x86_64-3.11/asyncpg/protocol
          copying asyncpg/protocol/pgtypes.pxi -> build/lib.macosx-12-x86_64-3.11/asyncpg/protocol
          copying asyncpg/protocol/prepared_stmt.pxd -> build/lib.macosx-12-x86_64-3.11/asyncpg/protocol
          copying asyncpg/protocol/prepared_stmt.pyx -> build/lib.macosx-12-x86_64-3.11/asyncpg/protocol
          copying asyncpg/protocol/protocol.c -> build/lib.macosx-12-x86_64-3.11/asyncpg/protocol
          copying asyncpg/protocol/protocol.pxd -> build/lib.macosx-12-x86_64-3.11/asyncpg/protocol
          copying asyncpg/protocol/protocol.pyx -> build/lib.macosx-12-x86_64-3.11/asyncpg/protocol
          copying asyncpg/protocol/scram.pxd -> build/lib.macosx-12-x86_64-3.11/asyncpg/protocol
          copying asyncpg/protocol/scram.pyx -> build/lib.macosx-12-x86_64-3.11/asyncpg/protocol
          copying asyncpg/protocol/settings.pxd -> build/lib.macosx-12-x86_64-3.11/asyncpg/protocol
          copying asyncpg/protocol/settings.pyx -> build/lib.macosx-12-x86_64-3.11/asyncpg/protocol
          creating build/lib.macosx-12-x86_64-3.11/asyncpg/protocol/codecs
          copying asyncpg/protocol/codecs/__init__.py -> build/lib.macosx-12-x86_64-3.11/asyncpg/protocol/codecs
          copying asyncpg/protocol/codecs/array.pyx -> build/lib.macosx-12-x86_64-3.11/asyncpg/protocol/codecs
          copying asyncpg/protocol/codecs/base.pxd -> build/lib.macosx-12-x86_64-3.11/asyncpg/protocol/codecs
          copying asyncpg/protocol/codecs/base.pyx -> build/lib.macosx-12-x86_64-3.11/asyncpg/protocol/codecs
          copying asyncpg/protocol/codecs/pgproto.pyx -> build/lib.macosx-12-x86_64-3.11/asyncpg/protocol/codecs
          copying asyncpg/protocol/codecs/range.pyx -> build/lib.macosx-12-x86_64-3.11/asyncpg/protocol/codecs
          copying asyncpg/protocol/codecs/record.pyx -> build/lib.macosx-12-x86_64-3.11/asyncpg/protocol/codecs
          copying asyncpg/protocol/codecs/textutils.pyx -> build/lib.macosx-12-x86_64-3.11/asyncpg/protocol/codecs
          creating build/lib.macosx-12-x86_64-3.11/asyncpg/protocol/record
          copying asyncpg/protocol/record/__init__.pxd -> build/lib.macosx-12-x86_64-3.11/asyncpg/protocol/record
          copying asyncpg/protocol/record/recordobj.c -> build/lib.macosx-12-x86_64-3.11/asyncpg/protocol/record
          copying asyncpg/protocol/record/recordobj.h -> build/lib.macosx-12-x86_64-3.11/asyncpg/protocol/record
          running build_ext
          creating build/temp.macosx-12-x86_64-3.11
          creating build/temp.macosx-12-x86_64-3.11/asyncpg
          creating build/temp.macosx-12-x86_64-3.11/asyncpg/pgproto
          clang -Wsign-compare -Wunreachable-code -fno-common -dynamic -DNDEBUG -g -fwrapv -O3 -Wall -isysroot /Library/Developer/CommandLineTools/SDKs/MacOSX12.sdk -I/Users/dimonoff/Repos/Bitbucket/RVE/smp-service/venv/include -I/usr/local/opt/[email protected]/Frameworks/Python.framework/Versions/3.11/include/python3.11 -c asyncpg/pgproto/pgproto.c -o build/temp.macosx-12-x86_64-3.11/asyncpg/pgproto/pgproto.o -O2 -fsigned-char -Wall -Wsign-compare -Wconversion
          asyncpg/pgproto/pgproto.c:223:12: fatal error: 'longintrepr.h' file not found
            #include "longintrepr.h"
                     ^~~~~~~~~~~~~~~
          1 error generated.
          error: command '/usr/bin/clang' failed with exit code 1
          [end of output]
      
      note: This error originates from a subprocess, and is likely not a problem with pip.
      Rolling back uninstall of asyncpg
      Moving to /Users/dimonoff/Repos/Bitbucket/RVE/smp-service/venv/lib/python3.11/site-packages/asyncpg-0.27.0.dist-info/
       from /Users/dimonoff/Repos/Bitbucket/RVE/smp-service/venv/lib/python3.11/site-packages/~syncpg-0.27.0.dist-info
      Moving to /Users/dimonoff/Repos/Bitbucket/RVE/smp-service/venv/lib/python3.11/site-packages/asyncpg/
       from /Users/dimonoff/Repos/Bitbucket/RVE/smp-service/venv/lib/python3.11/site-packages/~syncpg
    error: legacy-install-failure
    
    × Encountered error while trying to install package.
    ╰─> asyncpg
    
    note: This is an issue with the package mentioned above, not pip.
    hint: See above for output from the failure.
    
    opened by FernandoArteaga 0
Releases(0.0.33)
Owner
null
A PostgreSQL or SQLite orm for Python

Prom An opinionated lightweight orm for PostgreSQL or SQLite. Prom has been used in both single threaded and multi-threaded environments, including en

Jay Marcyes 18 Dec 1, 2022
ORM for Python for PostgreSQL.

New generation (or genius) ORM for Python for PostgreSQL. Fully-typed for any query with Pydantic and auto-model generation, compatible with any sync or async driver

Yan Kurbatov 3 Apr 13, 2022
A new ORM for Python specially for PostgreSQL

A new ORM for Python specially for PostgreSQL. Fully-typed for any query with Pydantic and auto-model generation, compatible with any sync or async driver

Yan Kurbatov 3 Apr 13, 2022
a small, expressive orm -- supports postgresql, mysql and sqlite

peewee Peewee is a simple and small ORM. It has few (but expressive) concepts, making it easy to learn and intuitive to use. a small, expressive ORM p

Charles Leifer 9.7k Jan 8, 2023
Tortoise ORM is an easy-to-use asyncio ORM inspired by Django.

Tortoise ORM was build with relations in mind and admiration for the excellent and popular Django ORM. It's engraved in it's design that you are working not with just tables, you work with relational data.

Tortoise 3.3k Jan 7, 2023
MongoEngine flask extension with WTF model forms support

Flask-MongoEngine Info: MongoEngine for Flask web applications. Repository: https://github.com/MongoEngine/flask-mongoengine About Flask-MongoEngine i

MongoEngine 815 Jan 3, 2023
Pydantic model support for Django ORM

Pydantic model support for Django ORM

Jordan Eremieff 318 Jan 3, 2023
A single model for shaping, creating, accessing, storing data within a Database

'db' within pydantic - A single model for shaping, creating, accessing, storing data within a Database Key Features Integrated Redis Caching Support A

Joshua Jamison 178 Dec 16, 2022
SQLModel is a library for interacting with SQL databases from Python code, with Python objects.

SQLModel is a library for interacting with SQL databases from Python code, with Python objects. It is designed to be intuitive, easy to use, highly compatible, and robust.

Sebastián Ramírez 9.1k Dec 31, 2022
A Python Library for Simple Models and Containers Persisted in Redis

Redisco Python Containers and Simple Models for Redis Description Redisco allows you to store objects in Redis. It is inspired by the Ruby library Ohm

sebastien requiem 436 Nov 10, 2022