Python: String manipulation issue when trying to form SQL statement

Question

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?

What do you mean by "all queries in this form?"? Which all queries are you talking about here? — rednafi
– rednafi, Commented May 10, 2021 at 22:36
Sorry, meant all queries that follow a similar structure to the example provided. — Mathew Carroll
– Mathew Carroll, Commented May 10, 2021 at 22:41

Chris Doyle · Accepted Answer · 2021-05-10 22:51:46Z

4

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

answered May 10, 2021 at 22:51

Chris Doyle

12.4k2 gold badges30 silver badges49 bronze badges

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

Comments

kinshukdua · Accepted Answer · 2021-05-10 22:37:34Z

1

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)

answered May 10, 2021 at 22:37

kinshukdua

2,0041 gold badge7 silver badges15 bronze badges

Comments

gene · Accepted Answer · 2021-05-10 22:46:04Z

0

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]])

edited May 10, 2021 at 22:46

answered May 10, 2021 at 22:38

gene

413 bronze badges

1 Comment

kinshukdua Over a year ago

This fails when there's a whitespace before the = sign.

Collectives™ on Stack Overflow

Python: String manipulation issue when trying to form SQL statement

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related