CODEWITHSUNDEEP
python
Mathew Carroll
Mathew Carroll

Reputation: 377

Python: String manipulation issue when trying to form SQL statement

Suppose I have the following string:

query = '''UPDATE table1 SET 'variable1' = 'value1', 'something' = "['bla', 'yada']", location = 'chicago' WHERE id = 9'''

This is a string that I will be using as an argument to a custom library to update a SQLite table. What I need to do is remove the single quotes around the word before each equal sign (if the word does have single quotes).

Desired output is as follows:

query = '''UPDATE table1 SET variable1 = 'value1', something = "['bla', 'yada']", location = 'chicago' WHERE id = 9'''

Notice the single quotes around words 'variable1' and 'something' have been removed. How can I do this for all queries in this form?

Upvotes: 2

Views: 123

Answers (3)

Chris Doyle
Chris Doyle

Reputation: 11968

You could use a regex to replace those. The regex captures the values inside the single quotes if they are followed by a space and equals sign, then replaces the quote and value with just the value.

import re
query = '''UPDATE table1 SET 'variable1' = 'value1', 'something' = "['bla', 'yada']", location = 'chicago' WHERE id = 9'''

updated_query = re.sub(r"'([^']+)'(?=\s=)", r"\1", query)
print(query)
print(updated_query)

OUTPUT

UPDATE table1 SET 'variable1' = 'value1', 'something' = "['bla', 'yada']", location = 'chicago' WHERE id = 9
UPDATE table1 SET variable1 = 'value1', something = "['bla', 'yada']", location = 'chicago' WHERE id = 9

Upvotes: 4

gene
gene

Reputation: 41

I would try the following:

words = query.split(" ")

new_query = ' '.join([words[i].replace("'", "") if words[i + 1] == '=' else words[i] for i in range(len(words) - 1)] + [words[len(words) - 1]])

Upvotes: 0

kinshukdua
kinshukdua

Reputation: 1994

How about splitting the query with commas. To get all different fields.

fields = query.split(", ")

then splitting each field by an = sign

fields = [i.split("=") for i in fields]

then removing the single quotes but only from the left hand side of the assignment operator

for f in fields:
    f[0] = f[0].replace("'", "")

then just going backwards and combining the strings

fields = ["=".join(f) for f in fields]
query = ", ".join(fields)

Upvotes: 1

Related Questions