DEV Community

kemurayama
kemurayama

Posted on

2 1

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
Enter fullscreen mode Exit fullscreen mode

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()) 
Enter fullscreen mode Exit fullscreen mode

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

Heroku

Build apps, not infrastructure.

Dealing with servers, hardware, and infrastructure can take up your valuable time. Discover the benefits of Heroku, the PaaS of choice for developers since 2007.

Visit Site

Top comments (0)

Postmark Image

Speedy emails, satisfied customers

Are delayed transactional emails costing you user satisfaction? Postmark delivers your emails almost instantly, keeping your customers happy and connected.

Sign up

👋 Kindness is contagious

Please leave a ❤️ or a friendly comment on this post if you found it helpful!

Okay