how to create literal based query in sqlalchemy?

Question

I created one function for create expression

def test(operator1, operation, operator2):
    return literal_column(operator1).op(operation)(operator2)

Now when I call it with

test(1, '=', 1)

then it works

But when I pass

test('abc', '=', 'abc')

Then it gives error that abc is not a column.

I tried to convert it

def test(operator1, operation, operator2):
    return literal_column(operator1, String).op(operation)(operator2)

But that was not working.

This will work if i call with

test("'abc'", '=', 'abc')

Is there any way, to get the type of operator1 and on that bases we can create literal_colum which will be map to same type of content ?

zzzeek · Accepted Answer · 2011-09-14 14:27:47Z

2

any literal value can be converted to an expression construct:

from sqlalchemy import literal_column, bindparam


# ? = ?, 1 will be bound
bindparam(1) == bindparam(1)

# " 1 = 1", literals rendered inline (no quoting is applied !!)
literal_column(str(1)) == literal_column(str(1))

answered Sep 14, 2011 at 14:27

zzzeek

75.8k24 gold badges204 silver badges190 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

NPatel Over a year ago

When i use bind params like bindparam(1)==bindparam(1) then i get FROM users WHERE %s = %s 2011-09-16 10:56:24,588 INFO sqlalchemy.engine.base.Engine (None, None) and when i convert it in literal_column('1')==literal_column('1') i get FROM users WHERE 1 = 1 2011-09-16 10:59:49,835 INFO sqlalchemy.engine.base.Engine () This will work for integer but for string it give error and convert int FROM users WHERE abc = abc 2011-09-16 10:59:49,835 INFO sqlalchemy.engine.base.Engine ()

zzzeek Over a year ago

Its assumed you're using the mechanics of execute() here to work with the clause construct. If you're just doing str(clause) and then using that, then yes you lose the bind param values but you're kind of going outside the usual usage patterns of the API. If you want to use literal_column with a string, you have to securely quote it. I'd advise against doing that and I'd stick to the patterns at sqlalchemy.org/docs/core/tutorial.html as much as possible.

NPatel Over a year ago

Thx for your help. If get any better idea then please post. I will add quote with string variable. :)

Collectives™ on Stack Overflow

how to create literal based query in sqlalchemy?

1 Answer 1

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Linked

Related