How to read data out of a SQLite database, into a dictionary before encoding it in JSON?

Question

I am a beginner at python and using SQLite. So please be patient with me. I am not entirely sure how much information i should provide, so i have decided on putting up as much code as i think is related. Like the saying goes; better safe than sorry.

Basically, what I have is a python script running a cherrypy server for a kind of peer to peer social networking web app. I have a method which logs three kind of Updates i make to my profile; New Post, New Photo, or New Event.

Each Update contains the following fields:

messageID: A 16 letter string containing a unique identifier
creator: My user name
created: A time stamp, UNIX Epoch time, of when the update took place
body: A short message about the update.
Link: A link to the update. e.g.. "/gallery/photo5"
Type: type 1 means new post, 2 means photo, 3 means event.

I have made these fields into columns of a table inside a database created with SQLite, here is the method i used to do it:

    @cherrypy.expose
    def writeUpdate(self, type=None):
        """This method is called whenever a change takes place on the Acebook
        It takes an input 'type' to know what kind of update it is.
        The method then make appropriet change to the 'Updates' database
        """

        con = lite.connect('static/database/Updates.db')
        messageID = self.randomword()
        creator = trueUser
        created = time.time()
        if type == 1:
            link = "/homepage"
            body = "New Status Update"
        elif type == 2:
            link = "/portfolio"
            body = "New Photo Update"
        elif type ==3:
            link = "/event"
            body = "New Event Update"
        else:
            link = "/homepage"
            body = "If you are seeing this, something is not quite right with by server"

        with con:

            cur = con.cursor() 
            cur.execute("CREATE TABLE IF NOT EXISTS Updates(messageID TEXT, creator TEXT, created INT, link TEXT, type INT, body TEXT)")   
            cur.execute("INSERT INTO Updates VALUES(?, ?, ?, ?, ?, ?)", (messageID, creator, created, link, type, body))

            "Debugging check"
            cur.execute('select * from Updates')
            print "The Updates database now contains:"
            for row in cur:
                print row


        return          

I have another method which my friends can call in order to get a newsfeed of my latest updates. This method is:

@cherrypy
def getActivity(self, minutes=48*60):
“”” Return any updates since last time this user requested them. Optional argument returns the last updates in the given time period instead.
“””
# current_user = getAuthenticatedUser(): # if not current_user:
# return “Please Authenticate”
# updates = getUpdatesByUser(current_user)

ExampleUpdate = [ {
‘messageID’: “ccog001-1332889924-839”, ‘creator’: “ccog001”,
‘created’: 1332889924,
‘link’: “/updates?id=839”,
‘type’: 1,
‘body’: “Hello, is anybody out there?”
},{
‘messageID’: “ccog001-1332890482-840”, ‘creator’: “ccog001”,
‘created’: 1332890482,
‘link’: “/updates?id=840”, ‘type’: 1,
‘body’: “Seriously, is this thing on?” }
]


reply = json.dumps(updates)
return reply

My question is, how do i read individual rows of the database into a separate dictionary, and then combine all the dictionary together into the format of the variable Example Update, before encoding it all with json.dumps?

Or would it maybe be easier if I write the messageID, creator, created ... etc... into a dictionary first, before writing that dictionary into a database? So I end up with a database containing only one column of dictionaries? If so, what would be the code like?

I am quite new, so please be detailed in your answer, preferably with code and comments to help me understand.

Thank you very much for you time

Answer 1

The column names are stored in Cursor.description after executing a SELECT statement. According to the docs, each entry in this list is a 7-tuple where the first element is populated by the column's name.

You can extract the column names and form a dict as follows:

cur.execute('select * from Updates')

# extract column names
column_names = [d[0] for d in cur.description]

for row in cur:
  # build dict
  info = dict(zip(column_names, row))

  # dump it to a json string
  reply = json.dumps(info)

Here, zip takes the two lists column_names and row and stitches them together element-wise into a list of tuples. dict then turns this into a dictionary ready for json to dump.