DEV Community

kemurayama
kemurayama

Posted on

Select a row with Whereclause in SQLAlchemy

#python

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.

Top comments (0)