CODEWITHSUNDEEP
pythonstringlistdictionary
user1492810
user1492810

Reputation: 87

convert python sql list into dictionary

How to convert 

cursor.execute("SELECT strftime('%m.%d.%Y %H:%M:%S', timestamp, 'localtime'), temp FROM data WHERE timestamp>datetime('now','-1 hours')")
# fetch all or one we'll go for all.
results = cursor.fetchall()
for row in results[:-1]:
row=results[-1]
rowstr="['{0}',{1}]\n".format(str(row[0]),str(row[1]))
temp_chart_table+=rowstr

result

['01.15.2015 21:38:52',21.812]

into dictionary output in form of:

[{timestamp:'01.15.2015 21:38:52',temp:21.812}]

Edit

This is fetchone sample I currenyly use and it works fine:

def get_avg():

    conn=sqlite3.connect(dbname)
    curs=conn.cursor()
    curs.execute("SELECT ROUND(avg(temp), 2.2) FROM data WHERE timestamp>datetime('now','-1 hour') AND timestamp<=datetime('now')")
    rowavg=curs.fetchone()
    #print rowavg
    #rowstrmin=format(str(rowavg[0]))
    #return rowstrmin
    **d = [{"avg":rowavg[0]}]**
    return d

    conn.close()

#print get_avg()
schema = {"avg": ("number", "avg")}
data = get_avg()
# Loading it into gviz_api.DataTable
data_table = gviz_api.DataTable(schema)
data_table.LoadData(data)
json = data_table.ToJSon()
#print results

#print "Content-type: application/json\n\n"
print "Content-type: application/json"
print
print json

Then I make jQuery call and pass it into javascript and found help for that in here ajax json query directly to python generated html gets undefined

Upvotes: 1

Views: 5463

Answers (5)

vk1011
vk1011

Reputation: 7179

Use MySQLdb's cursor library.

import MySQLdb
import MySQLdb.cursors

conn = MySQLdb.connect(host=db_host, user=db_user, passwd=db_passwd, db=db_schema, port=db_port, cursorclass=MySQLdb.cursors.DictCursor)

cursor = conn.cursor()

cursor.execute("SELECT timestamp, localtime, temp FROM data WHERE timestamp>datetime('now','-1 hours')")
# fetch all or one we'll go for all.
results = cursor.fetchall()

Then you have access to the results as a dictionary:

>>> results['timestamp']
14146587
>>> results['localtime']
20:08:07
>>> results['temp']
temp_variable_whatever

Upvotes: 0

garnertb
garnertb

Reputation: 9584

In most of the python database adapters you can use a DictCursor to retrieve records using an interface similar to the Python dictionaries instead of the tuples.

Using psycopg2:

>>> dict_cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
>>> dict_cur.execute("INSERT INTO test (num, data) VALUES(%s, %s)",
...                  (100, "abc'def"))
>>> dict_cur.execute("SELECT * FROM test")
>>> rec = dict_cur.fetchone()
>>> rec['id']
1
>>> rec['num']
100
>>> rec['data']
"abc'def"

Using MySQLdb:

>>> import MySQLdb 
>>> import MySQLdb.cursors 
>>> myDb = MySQLdb.connect(user='andy47', passwd='password', db='db_name', cursorclass=MySQLdb.cursors.DictCursor) 
>>> myCurs = myDb.cursor() 
>>> myCurs.execute("SELECT columna, columnb FROM tablea") 
>>> firstRow = myCurs.fetchone() 
{'columna':'first value', 'columnb':'second value'}

Upvotes: 1

Brent Washburne
Brent Washburne

Reputation: 13148

Try this instead:

cursor.execute("SELECT strftime('%m.%d.%Y %H:%M:%S', timestamp, 'localtime'), temp FROM data WHERE timestamp>datetime('now','-1 hours')")
# fetch all or one we'll go for all.
results = cursor.fetchall()
temp_chart_table = []
for row in results:
    temp_chart_table.append({'timestamp': row[0], 'temp': row[1]})

Upvotes: 1

Bhargav Rao
Bhargav Rao

Reputation: 52071

As I can see you are using format to write in the form of a string.

Note from the docs

it is not possible to use { and } as fill char while using the str.format() method

To make it look like a dictionary you can do

"[{timestamp:'%s',temp:%s}]\n"%(str(row[0]),str(row[1]))

But if you want to make it a dictionary then you will have to do

row_dic = [{'timestamp':row[0],'temp':row[1]}]

Upvotes: 2

L3viathan
L3viathan

Reputation: 27283

def stuffToDict(stuff):
    return {"timestamp":stuff[0],"temp":stuff[1]}

That would be a dictionary. The sample output you showed is a list of dictionaries, which can be achieved by putting square brackets around the dictionary. I don't know why you'd want that, though. Also, because of the missing quotes, it wasn't legal python syntax.

Upvotes: 0

Related Questions