I need to construct SQL query from json,

Sample Json:

{
"table": "employee",
"type": "update",
"data": {
      "id": 111,
      "name": "swastik",
       "age": 23
      }

"old": {
      "age": 22,
      }
}

In general SQL queries will have double/single quotes for column strings and no quotes if column is int/float.

So for constructing SQL queries for SQLite I've 2 ways

1. Taking advantage of SQLite's "flexibly typed" feature

By using this feature of SQLite I can have double quotes for all columns without checking whether column value is String or Int or Flot

Output Query

UPDATE employee SET age = "23" WHERE id = 111;

2. Use python inbuilt function.

Use python inbuilt function isinstance() to determine whether column value is String or Int or Float and add quotes only if column value is string

Output Query

UPDATE employee SET age = 23 WHERE id = 111;

Which one of the above approach is best?

UPDATE employee SET age = "23" WHERE id = 111;

This is invalid SQL. If you mean for "23" to designate the text string '23', then you need to use single-quotes. Double-quotes are for identifiers.

I should think your JSON translates to:

update employee
   set age = 23
 where id = 111
   and name = 'swastik'
   and age = 22
;

otherwise the JSON contains useless information.

Why are you assuming that the creator of the JSON was a moron that included useless information?

Of course, a logical implementation would convert that JSON to the following statement:

update employee
   set id = 111,
       name = 'swastik',
       age = 23
 where age = 22
;

but clearly the JSON was not designed by a logical thinking person.

Please have my apologies for the reply from the other members response.

Having the db schema for the target query is useful in writing queries. Column definition, foreign keys, indexes etc all provide critical terrain for the query to navigate.

For example, if the table has a unique index on id, then one could use just the id to identify an individual record(stating the obvious).

The JSON you provide may have redundant/unnecessary info for many reasons, including providing you with all available info to not limit your implementation options, or the info is provided to more than you and a single canonical source for that info.

One of the things SQLITE gets right is they do not assume that use cases are limited to what they know about (as opposed to chrome who look at collected user metrics as the scripture of use cases).

As to you sample, Keith is quite right about the double quited string misfeature ( see https://www.sqlite.org/quirks.html ). While you may br able to get away with it, it is best to stick to standard SQL whenever possible.

Applying that same principle to your core question, my opinion is to the extent that you do not make your solution rely on sqlite's unique dynamic typing your application will be more easily adapted to another sql engine. The less subtle mental gymnastics one had to do the more they can focus on the essentials, and that will (IMO) lead to less problems.

Lastly, relying on quirks(even when well supported), premature optimization and tricks at the start is a poor omen. See Knuth art of computer programming pages 1-1000 :)(this is meant as humor but he is worth a read).

What is considered best is up to you, and only you. You are the one practicing your craft. You will know common practice, but also its limitations. you will test alternatives to explore the difference between theory, and practice. As yoggi Berra once said, "in theory, theory and practice are the same, in practice they ain't".

so, my advice: dump the double quoted literals (should bind those parameters anyway see sql injection expoloits), and write the query so it works well against the target db, both storing and retrieving. That is where your work is.

again, my apologies for what appears to me to be unecessary cruelty in another, highly talented sqlite forum member's response.

for your option 2, in python you can use parameterized queries. one ref to this type of thing is here https://stackoverflow.com/questions/45343175/python-3-sqlite-parameterized-sql-query

note they also mention sql injection as a reason to parameterize your queries.

With very few exceptions, it is almost always better to parametrize your queries (no indication you are anywhere near those exceptions).

Fixing your broken JSON, the example comes down to this very straightforward code:

import json

a = json.loads("""
{
"table": "employee",
"type": "update",
"data": {
      "id": 111,
      "name": "swastik",
       "age": 23
      },
"old": {
      "age": 22
      }
}
""")

sql = f'''{a['type']} {a['table']} set ''' + ', '.join(f'{k} = ?' for k in a['data'].keys()) + ' where ' + ' and '.join(f'{k} = ?' for k in a['old'].keys())
param = list(a['data'].values()) + list(a['old'].values())

print(sql)
print(param)

# db.execute(sql, params)

where you get the following for sql and param:

>>> print(sql)
update employee set id = ?, name = ?, age = ? where age = ?
>>> print(param)
[111, 'swastik', 23, 22]
>>>

All of which requires nothing special whatsoever (note that it is perspicacious, no matter what the bleeding hearts will tell you) to design your data inputs appropriately. If you do not, then you will have massive difficulties and introduce massive complication (and failure modes) where they need not exist.

That certainly handles their original sql injection pitfall and takes them through their stated 'problem' (which presents as an xy type).

While it's reasonable to presume that "update" translates to the sql UPDATE, it gets a bit dicey if the JSON "Type" has values such as "PUT" as well as "UPDATE". And of course the OP does not specify one way or the other.

Agree 100% the inputs should have more attention. Indeed, IMO they NEED more attention by the OP otherwise they most likely will have quite a few surprises.

And technically the sql does "update" all employees that have an age =22 to the 'data', it's seems unlikely that the JSON is intended to do that. It is of course possible that they want everyone aged 22 to all be 23 at the same time (while changing their names and id all to one name is like writing not in python, but monty python - 'Bruce' :). Thus some canonical clarity on that JSON meaning would serve the OP well before implementation.

Of course the JSON 'age' itself is problematic as it does not, pardon the pun, age well. A year from now that JSON will make the same employee stand still in time (which, for those seeking immortality, would be a feature, not a bug). The OP would be well served by obtaining JSON that contains a birthdate or birthyear (if necessary) so that proper calculations can be made in the future. This is what I refer to in advising considerations in both storing AND future retrieval in earlier posts.

It's plausible they want to update a specific employee, and the 'old' data may be an artifact from some user entry form (pure speculation obviously). Given that, I advise them to better examine their JSON source to understand what they are processing. This is the essence of that part of my post above. The OP may, or may not control that JSON, but they can at a minimum understand it's limitations and act accordingly.

I sure do hope they test appropriately so that they can learn before doing too much damage (Although as Brooks notes in MMM, "It is a very humbling experience to make a multi-million-dollar mistake, but it is also very memorable.").

Your eloquent python to sql, and conclusion seems sound enough, yet I do think the 'special' is the work (as noted in my post above). For folks to put in effort is 'special', and hopefully they (OP) will do so.

As to the non technical aspects, no offense taken. My apologies if I came across as a bleeding heart, I am actually a sociopath who thinks that the world being a better place benefits me personally. :) All humor aside, the SQLite license text ( https://sqlite.org/src/file?name=LICENSE.md&ci=trunk ) wisely summarises:

The author disclaims copyright to this source code. In place of a legal notice, here is a blessing:

May you do good and not evil. May you find forgiveness for yourself and forgive others. May you share freely, never taking more than you give.

regards

While it's reasonable to presume that "update" translates to the sql UPDATE, it gets a bit dicey if the JSON "Type" has values such as "PUT" as well as "UPDATE"

You are making an assumption of facts not in evidence. There was no mention of any other value of "Type" and what to do if it were not "update". A prudent programmer would "validate their inputs" and ensure that the invariants were met, and if not signal a massive explosion the scale of which could not be missed even by a moribund observer.

And technically the sql does "update" all employees that have an age =22 to the 'data', it's seems unlikely that the JSON is intended to do that.

That is merely your opinion. There is no data nor clue one way or the other what is meant by that particular JSON. It should be noted, however, that any other interpretation requires "twists of logic" and "addition of complication" which requires the assumption that the JSON was conceived by an illogical person (one not, as Bones would say, firing on all thrusters).

It's plausible they want to update a specific employee, and the 'old' data may be an artifact from some user entry form (pure speculation obviously). Given that, I advise them to better examine their JSON source to understand what they are processing. This is the essence of that part of my post above. The OP may, or may not control that JSON, but they can at a minimum understand it's limitations and act accordingly.

This is not the question which was asked. The question which was asked was how to transate such a JSON snippet into an SQL statement taking advantage of the python to SQLite3 adaption layer and the underlying SQLite3 database "ducky typing".

Possible use of "de-ducking" the data via isinstance was suggested as a possible way to do this.

The answer presented an alternative which used the inherent "duckiness" of all the bits to perform the manipulation with "nothing special" needing to be done at all to diddle the poor duck. This is precisely what was requested.

There was no mention (nor request) to operate on different "type" of duck, or even that different "type" of ducks may exist.

(to admins: My apologies for having to discuss this. Ignoring the increase in personal aspersions is not working. Nor was politely appealing to our better nature. To spare the noise on the forum, perhaps it's possible to forward directly to recipient rather than post?)

To avoid hijacking this thread, which is more or less topical(^), I am starting a new thread to deal with the subject you raise here and touch upon further later in your post.

For reasons that will become clear in that thread, I nearly rejected your post #10. Unfortunately, it appears the interpersonal issues need some attention.

You could use these to generate more "pleasing" SQL:

sql = 'update {}'.format(a['table']) + '\n' + \
      '   set ' + ',\n       '.join('{} = ?'.format(k) for k in a['data'].keys()) + '\n' + \
      ' where ' + '   and '.join('{} = ?'.format(k) for k in a['old'].keys())
param = tuple(list(a['data'].values()) + list(a['old'].values()))

which would give this pretty (but otherwise functionally equivalent) sql and param:

update employee
   set id = ?,
       name = ?,
       age = ?
 where age = ?
>>> print(param)
(111, 'swastik', 23, 22)