I’m trying to populate a postgresql table with data using sqlalchemy and its support for asyncio. I based my code on the example from sqlalchemy’s documentation (sqlalchemy asyncio synopsis), which populates only one column of data.
This is my most recent attempt…
import asyncio
from sqlalchemy import Column, MetaData, select, Table
from sqlalchemy import String, Integer, Boolean, ForeignKey
from sqlalchemy.ext.asyncio import create_async_engine
NUM_RECORDS = 10
DB_URL = 'postgresql+asyncpg://postgres:[email protected]/demo'
meta = MetaData()
users = Table('users', meta,
Column('id', Integer, primary_key=True),
Column('email', String),
Column('username', String),
Column('first_name', String),
Column('last_name', String),
)
async def async_main() -> None:
engine = create_async_engine(DB_URL, echo=True)
async with engine.begin() as conn:
await conn.run_sync(meta.drop_all)
await conn.run_sync(meta.create_all)
await conn.execute(users.insert(), [{"first_name": f"first_name_{x:06d}"} for x in range(NUM_RECORDS)])
for i in range(NUM_RECORDS):
await conn.execute(users.update().where(users.c.id == i).values({"last_name": f"last_name_{i:06d}"}))
await conn.execute(users.update().where(users.c.id == i).values({"email": f"email{i:06d}@nomail.io"}))
await conn.execute(users.update().where(users.c.id == i).values({"username": f"some name {i:06d}"}))
await engine.dispose()
asyncio.run(async_main())
… although this works, it seems very inefficient. Ideally, I would like to insert the entire record with a single call to users.insert().
I’ve tried combining all columns into a single call while still using a comprehension, but to no avail. I tried it like this (for just two columns)…
await conn.execute(users.insert(), [({"first_name": f"first_name_{x:06d}"}, {"last_name": f"last_name_{x:06d}"}) for x in range(NUM_RECORDS)])
… which results in TypeError: '<' not supported between instances of 'dict' and 'dict'. Note I had to enclose the two comprehension clauses in parentheses in order to make the comprehension work, but it results in tuples being passed to the call to insert().
2