Setting input parameter types in SQLAlchemy raw SQL

Question

I want to execute some raw SQL and take advantage of SQLAlchemy's SQL injection prevention.

My code is similar to this bit:

import sqlalchemy
from sqlalchemy.sql import text

DB_URI = '...'
engine = sqlalchemy.create_engine(DB_URI)

sql = text("SELECT * FROM some_table WHERE userid = :userid")
res = engine.execute(sql, userid=12345)
# do something with the result... 

The problem is that userid in some_table is of type varchar. All I want to do is to tell SQLAlchemy to convert 12345 to a string before executing the statement. I know how I could do the conversion both in Python and in SQL. But I remember that I once used a explicit type definition in SQLAlchemy, I just can't find it anymore. Can someone point me to the right direction? (My actual question involves postgresql arrays of BIGINTs vs. INTs, but I tried to keep it simple.)

Thanks for any help!

Answer 1

The question is a bit old already, but knowing myself, I will probably come back for this in future. This was the bit of code I was looking for:

import sqlalchemy
from sqlalchemy.sql.expression import bindparam
from sqlalchemy.types import String
from sqlalchemy.dialects.postgresql import ARRAY

DB_URI = '...'
engine = sqlalchemy.create_engine(DB_URI)

sql = text("SELECT * FROM some_table WHERE userid = :userid").bindparams(bindparam("userid", String))
res = engine.execute(sql, userid=12345)

# in particular this bit is useful when you have a list of ids
sql = text("SELECT * FROM some_table WHERE userids = :userids").bindparams(bindparam("userids", ARRAY(String)))
res = engine.execute(sql, userids=[12345, 12346, 12347])