Geek Answers Handbook

How to read data from a SQLite database into a dictionary before encoding in JSON?

I am starting in python and using SQLite. So please be patient with me. I'm not quite sure how much information I should provide, so I decided to put as much code as I think. As they say; God saves man, who save himself.

Basically, I have a python script running on a cherrypy server for my peer-to-peer social networking web application. I have a method that logs three types of updates that I make in my profile; New message, 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 made these fields into table columns inside a database created using SQLite, here is the method I used for this:

    @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

, , . :

@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

: , Example Update, json.dumps?

, , , messageID, , ... .. , ? , , ? , ?

, , , , , .

+5
1

Cursor.description SELECT. docs, 7-, .

dict :

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)

zip column_names row . dict , json, .

+4

More articles:


All Articles