1.使用原生SQL语句远程链接MySQL
view.py中
---
import MySQL
def request_data(request):
if request.method == "GET": conn = MySQLdb.Connect( host ='my_ip', port = 3306, user = 'my_user', passwd = 'my_passwd', db = 'my_db', charset = 'utf8' ) cursor = conn.cursor() cursor.execute("select * from my_table limit 1")
values=cursor.fetchall()
cursor.close() conn.close() return render(request,"data.html",{'results':values})
print(values)
------------------
((1L, u'\u2320\u2131', u'13333333333333', datetime.datetime(2017, 10, 16, 0, 0)))
------------------
此时返回的values是tuple类型
2.将sql返回的value转化为dict类型
------------------------
def dictfetchall(cursor):
"将游标返回的结果保存到一个字典对象中"
desc = cursor.description
return [
dict(zip([col[0] for col in desc], row))
for row in cursor.fetchall()
]
修改一下代码
values = dictfetchall(cursor)
然后 print(values)
-----------------------------------------------------
[{'phone': u'133333333333', 'id': 1L, 'expected_date': datetime.datetime(2017, 10, 16, 0, 0), 'uname': u'\u5f20\u79d1'}]
------------------------------------------
已经变成dict类型了
在html中可以用template自带的方法引用
{% for item in results %}
<li>{{item.phone}}</li>
{%endfor%}
3.转化为json格式给js用
view.py中,改一行代码即可
用json.dumps()处理一下values
import json
return render(request,"data.html",{'results':json.dumps(values)})
print一下results的结果
----------------------------------------------------
[{"phone": "13333333333", "id": 1, "expected_date": "datetime.datetime(2017, 10, 16, 0, 0)", "uname": "\u5f20\u79d1"}]
--------------------------------------------------
可以看到单引号变成了双引号,python用的标识u、L消去了。
4.处理错误的datetime.datetime
import datetime
class MySQLEncoder(json.JSONEncoder):
def default(self,obj):
if isinstance(obj,datetime.datetime):
return obj.strftime('%Y-%m-%d %H:%M:%S')
elif isinstance(obj,date):
return obj.strftime('%Y-%m-%d')
else:
return json.JSONEncoder.default(self,obj)
定义好之后
再在刚才json.dumps()的地方改一下
return render(request,"data.html",{'results':json.dumps(values,cls=MySQLEncoder)})
print可以看到results现在已经显示正确的时间格式
[{"phone": "13333333333", "id": 1, "expected_date": "2017-10-16 00:00:00", "uname": "\u5f20\u79d1"}]
def default(self,obj):
if isinstance(obj,datetime.datetime):
return obj.strftime('%Y-%m-%d %H:%M:%S')
elif isinstance(obj,datetime.date):
return obj.strftime('%Y-%m-%d')
elif isinstance(obj, decimal.Decimal):
return str(obj)
else:
return json.JSONEncoder.default(self,obj)
def __init__(self,name):
self.val = name
return obj.val