Structuring request JSON for API

Question

I'm building a small API to interact with our database for other projects. I've built the database and have the API functioning fine, however, the data I get back isn't structured how I want it.

I am using Python with Flask/Flask-Restful for the API. Here is a snippet of my Python that handles the interaction:

class Address(Resource):
    def get(self, store):
        print('Received a request at ADDRESS for Store ' + store )
        conn = sqlite3.connect('store-db.db')
        cur = conn.cursor()
        addresses = cur.execute('SELECT * FROM Sites WHERE StoreNumber like ' + store)     
        for adr in addresses:
            return(adr, 200)

If I make a request to the /sites/42 endpoint, where 42 is the site id, this is what I'll receive:

[
    "42",
    "5000 Robinson Centre Drive",
    "",
    "Pittsburgh",
    "PA",
    "15205",
    "(412) 787-1330",
    "(412) 249-9161",
    "",
    "Dick's Sporting Goods"
]

Here is how it is structured in the database:

Ultimately I'd like to use the column name as the Key in the JSON that's received, but I need a bit of guidance in the right direction so I'm not Googling ambiguous terms hoping to find something.

Here is an example of what I'd like to receive after making a request to that endpoint:

{
    "StoreNumber": "42",
    "Street": "5000 Robinson Centre Drive",
    "StreetSecondary": "",
    "City": "Pittsburgh",
    "State": "PA",
    "ZipCode": "15205",
    "ContactNumber": "(412) 787-1330",
    "XO_TN": "(412) 249-9161",
    "RelocationStatus": "",
    "StoreType": "Dick's Sporting Goods"
}

I'm just looking to get some guidance on if I should change how my data is structured in the database (i.e. I've seen some just put the JSON in their database, but I think that's messy) or if there's a more intuitive method I could use to control my data.

Updated Code using Accepted Answer

class Address(Resource):
    def get(self, store):
        print('Received a request at ADDRESS for Store ' + store )
        conn = sqlite3.connect('store-db.db')
        cur = conn.cursor()
        addresses = cur.execute('SELECT * FROM Sites WHERE StoreNumber like ' + store)     
        for r in res:
            column_names = ["StoreNumber", "Street", "StreetSecondary","City","State", "ZipCode", "ContactNumber", "XO_TN", "RelocationStatus", "StoreType"]
            data = [r[0], r[1], r[2], r[3], r[4], r[5], r[6], r[7], r[8]]            
            datadict = {column_names[itemindex]:item for itemindex, item in enumerate(data)}
            return(datadict, 200)

Use SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N'YourTableName' to get the column names — nerd100
– nerd100, Commented Nov 14, 2018 at 14:41
I created a tool to help exposing databases with json: github.com/thomaxxl/safrs — Tohmaxxx
– Tohmaxxx, Commented Nov 14, 2018 at 15:11

forgetso · Accepted Answer · 2018-11-14 15:07:15Z

1

You could just convert your list to a dict and then parse it to a JSON string before passing it back out.

// These are the names of the columns in your database
>>> column_names = ["storeid", "address", "etc"]

// This is the data coming from the database.
// All data is passed as you are using SELECT * in your query
>>> data = [42, "1 the street", "blah"]

// This is a quick notation for creating a dict from a list
// enumerate means we get a list index and a list item
// as the columns are in the same order as the data, we can use the list index to pull out the column_name
>>> datadict = {column_names[itemindex]:item for itemindex, item in enumerate(data)}

//This just prints datadict in my terminal
>>> datadict

We now have a named dict containing your data and the column names.

{'etc': 'blah', 'storeid': 42, 'address': '1 the street'}

Now dump the datadict to a string so that it can be sent to the frontend.

>>> import json
>>> json.dumps(datadict)

The dict has now been converted to a string.

'{"etc": "blah", "storeid": 42, "address": "1 the street"}'

This would require no change to your database but the script would need to know about the column names or retrieve them dynamically using some SQL.

If the data in the database is in the correct format for passing to the frontend then you shouldn't need to change the database structure. If it was not in the correct format then you could either change the way it was stored or change your SQL query to manipulate it.

edited Nov 14, 2018 at 15:07

answered Nov 14, 2018 at 14:34

forgetso

2,55420 silver badges38 bronze badges

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

7 Comments

forgetso Over a year ago

By the way, it looks like your SQL may be vulnerable to SQL injection depending on how the variable store is passed in.

CodeSpent Over a year ago

I'm new to Python, would you mind commenting in your example a little bit? Mainly in the columns, data, and datadict declarations. Also as far as the injections go, my SQL won't be used in production, I'm just making the PoC and sending it off to the guys who know what they're doing. :)

forgetso Over a year ago

I've added some comments into the code and added some extra thoughts at the end of the answer. If you're new to python you may want to check out dict comprehensions. Let me know if this helps.

CodeSpent Over a year ago

Perfect, looking that over now. For future viewers, I'd recommend creating a separate code block for terminal output just to avoid any confusion. Going to give this a try now and report back with results.

forgetso Over a year ago

Sure, that's updated. There's a useful comment regarding getting database names from @nerd100 further up.

|

Collectives™ on Stack Overflow

Structuring request JSON for API

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related