python string parsing given sql query tell if it's read or write

Question

I'm given a sql query and I have to decide if it's a write statement or read statement.

I'm not sure how to go about it and I can't find anything online that already has this capability.

I'm assuming the only way to do it is to inspect the string for words like select vs update alter delete insert drop etc...

But I'll have to strip out all string literals first.

def is_write_query(query):
    # returns true or false 
    # strip string literals 
    # if contains key words: 
    #   UPDATE, DELETE, INSERT, CREATE, ALTER, DROP
    # return true, else false

Does anyone know a better way to do this? do you know about a package that has this capability?

if this is the best way to do it, what's the easiest way to handle the string parsing? Specifically stripping out the string literals.

user2774695 · Accepted Answer · 2018-10-12 00:17:25Z

1

I don't know of a better way than what you suggest - you need to look at the operation that is happening. The operation will always be the first word in the query so you can do:

def is_write_query(query): 
    query = query.lower().strip() # to remove trailing and leading whitespace, convert to lowercase 
    if query.startswith(("update","insert","create","alter","delete","drop")): 
        return True 
    else:
        return False

You might need to include more keywords than I am including here, but this would be the general idea. .startswith() can take a tuple (not list) of string to try which is what is happening here.

answered Oct 12, 2018 at 0:17

user2774695

1167 bronze badges

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

2 Comments

MetaStack Over a year ago

actually, these statements can be pretty complex, so the first word might not be the operation. For instance, it could start with a with if it's creating a CTE table first. But thanks, this is a good start.

Sandburg Over a year ago

Counter example, you can begin with an alias or a sub request, and the operation comes later: WITH tmp as (SELECT col1 FROM tb1) DELETE FROM tmp WHERE col1 = tmp.col1

Collectives™ on Stack Overflow

python string parsing given sql query tell if it's read or write

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related