Avoiding bindparams sometimes ?

[AbdealiLoKo]

Hi, i have a query which is pretty large.
And uses a lot of python variables and constants.
So many that sometimes older versions of sqlite complain with too many arguments to bind.
As per the sqlite documentation - Item 9 in limits (link):

999 for SQLite versions prior to 3.32.0 (2020-05-22) or 32766 for SQLite versions after 3.32.0

In one part of this query, I have something like extract("day", dt1 - dt2) * 24 * 60 * 60
And on checking with echo, I realized that this gets created with 5 bindparams. dt1, dt2, 24, 60, 60

I'm trying to understand if there is a way for me to tell sqlalchemy that the 24, 60, 60 values are all constants.
Something like:

extract("day", dt1 - dt2) * bindnow(24 * 60 * 60)

Where bindnow is used to tell sqlalchemy to immediately bind the value and not provide it as a parameter later on.

Other approaches I've tried:

Approach 1: Precalculate the python parts of my expression

unit = 24 * 60 * 60
extract("day", dt1 - dt2) * unit

This should reduce the number of params to 3: dt1, dt2, unit`

Approach 2: Dedupe bindparams manually.
For example I can use the bindparam() function to create a param for 60 and reuse it twice as I learnt here:

from sqlalchemy import bindparam

num_60 = bindparam(60)
extract("day", dt1 - dt2) * 24 * num_60 * num_60

But obviously the Approach-1 would be better nad more intuitive.

[CaselIT]

Hi,

Both approaches work, but you need to look at the bindparam signature. You are passing the value as the name.

Note that im this case parentheses work equally well: extract("day", dt1 - dt2) * (24 * 60 * 60)

[AbdealiLoKo]

Is there any option of something like:

extract("day", dt1 - dt2) * bindnow(24 * 60 * 60)

Where bindnow will directly bind the value and not pass it as a param ?

This would reduce the number of params to 2 - which is actually my "user inputs"

[CaselIT]

There is the literal_execute argument you can use for this. I would suggest looking at the docs.

Also in your case you may want to use literal() instead (it's just a constructor for bindparams, but it's likely easier to use in this case)