Select a row with Whereclause in SQLAlchemy

I am new to SQLAlchemy and aiopg. After defined a Tabled object and finished DB initialization, I wanted to fetch one row with specified id in aiohttp server.

import aiopg.sa
from sqlalchemy import (
    MetaData, Table, Column, ForeignKey,
    Integer, String, Date
)

...

product = Table(
    'product', meta,

    Column('id', Integer, primary_key=True),
    Column('Name', String(200), nullable=False),
    Column('Description', String(200), nullable=False),
    Column('Price', Integer, nullable=False),

)

async def init_pg(app):
    conf = app['config']['postgres']
    engine = await aiopg.sa.create_engine(
        database=conf['database'],
        user=conf['user'],
        password=conf['password'],
        host=conf['host'],
        port=conf['port'],
        minsize=conf['minsize'],
        maxsize=conf['maxsize'],
    )
    app['db'] = engine

After spending all afternoon, finally I figured it out. text can convert string as TextClause and it can be used in Whereclause.

https://docs.sqlalchemy.org/en/13/core/selectable.html#sqlalchemy.sql.expression.select

from sqlalchemy import text

...

async with request.app['db'].acquire() as conn:
        whereclause = text('id = {}'.format(request.match_info['id']))
        cursor = await conn.execute(db.product.select(whereclause=whereclause))
        record = await cursor.fetchone()
        product = Product(*record.values())

I am really happy to know it :) and I had better look into Official Document more carefully.

Blog

Select a row with Whereclause in SQLAlchemy

kemurayama

Join Our Newsletter. No Spam, Only the good stuff.

Related