Pattern match "LIKE" and escape special character with Python postgres?

Question

How to pattern match "LIKE" and escape special character with the below Python code? I'm getting errors with special characters.

"parameter" values are "1.1", "1.2","1.3"

cr.execute("select sequence from account_stock_info "
"where sequence like '%s.%' "
"order by sequence ",
(tuple(parameter)))

Original query on PGADMIN was:

select sequence from account_stock_info
where sequence like '1.1.%'
order by sequence

Answer:"1.1.1", "1.1.1.03", "1.1.2","1.1.2.04","1.1.3"

Please help with Python pattern match.

ndpu · Accepted Answer · 2015-03-09 14:32:12Z

1

First error is using tuple function with string parameter:

>>> tuple('1.1')
('1', '.', '1')

you can pass tuple explicitly: (parameter,) or use dict to pass parameters.

To pass percent sign to LIKE statement you can add additional percent sign in your query:

parameter = "1.1"
cr.execute("select sequence from account_stock_info "
           "where sequence like '%s.%%' "
           "order by sequence ",
           (parameter,))

or pass it in parameter (quotes can be deleted here):

parameter = "1.1.%"
# ...
"where sequence like %s "

edited Mar 9, 2015 at 14:32

answered Mar 8, 2015 at 16:45

ndpu

22.6k6 gold badges61 silver badges72 bronze badges

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

3 Comments

erdembayar Over a year ago

Do you have more complete sql query? I still have problem with passing parameters in Like statement?

erdembayar Over a year ago

I got error. My code: parameter = "1.1.%" cr.execute("select id,name,sequence from account_stock_info " "where sequence like %s " "order by sequence ", (parameter,)) Error: No operator matches the given name and argument type(s). You might need to add explicit type casts. , in query select id,name,sequence from account_cash_move_type where sequence like %s order by sequence

erdembayar Over a year ago

I got error. It seems "sequence" is reserved word in Postgre. How to use sequence column now?

Collectives™ on Stack Overflow

Pattern match "LIKE" and escape special character with Python postgres?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related