CODEWITHSUNDEEP
pythonsqljsonpandassqlalchemy
Kirk Holmes
Kirk Holmes

Reputation: 59

Use JSON as SQL Query Builder

I need to run SQL Queries with multiple conditions so I'm thinking if I'm able to store the conditions for each query in a JSON file I can run the JSON file and spit out the results object by object. Does anyone know of any libraries for Python that will take a JSON file and from that file build an SQL Query? I'm not looking to move JSON data into a database, but rather use the JSON objects as a means to build search criteria I want to run on a database.

For example assume my database table contains contact information such as First Name, Last Name, Street Address, City, State, Zip Code, Phone Number, Email Address, Marital Status, etc. If I wanted to have a query that would show results of people in my table that live in the states of NV, CA, and UT but do not have a marital status of single my JSON Object could contain something like the following:

{
  "include": {
    "State": {
        "NV",
        "CA",
        "UT" }
  "exclude": {
    "Marital Status":"Single"
  }
 }

I'm open to other suggestions on how to accomplish this, but my goal is for users to be able to fill out some type of form of their search criteria and save that as a JSON object (or equivalent) and then go to my database and run those queries. It seems like something like this probably already exists. If it doesn't and others think it valuable cool!

Upvotes: 1

Views: 3256

Answers (1)

c8999c 3f964f64
c8999c 3f964f64

Reputation: 1627

EDIT: I assumed you were attempting to do this using API calls rather than a "json file", but its still relevant.

You are possibly looking for something like GraphQL, but similar solutions can be achieved with standard sqlalchemy syntax.

Consider the following request:

{
    "table": "people",
    "where": {"State": "NV"}
}

you can decode this request to search the table "people" for all rows that have "State" = "NV"

    table = request_data.get('table')
    selected_table = db.Table(table, metadata, autoload=True)

the actual query would then be constructed via

    query = selected_table.select()
    where_fields = request_data.get('where', {})
    for k, v in where_fields.items():
        query = query.where(getattr(selected_table.c, k) == v)

and executed with 

    my_session = Session(engine)
    result = my_session.execute(query)
    my_session.close()

This is a very basic example, but you can see that with this technique, the user is able to define what the query should look like using the "where" attribute in their JSON request and it will work for any column on any table.

Possible downsides: Special cases such as "exclude" is not so straight-forward, as well as providing a list of items (NV, CA, UT) which are allowed (OR instead of AND, etc...), but technically realistic. (maybe with a "where_not" or "where_or" field ?)

Provided that your users have in-dept knowledge about your db structure, this is an (imo) elegant solution to give your users way more access without constantly having to update your routes, models and schemas according to their needs.

GraphQL will put you through a LOT of schema & model creation, but there is more information about it than doing it on your own like this.

Upvotes: 2

Related Questions