MySQL数据库结构
CREATE TABLE IF NOT EXISTS `po_sales` ( `sid` int(11) NOT NULL, `s_name` varchar(32) DEFAULT NULL, `s_amount` decimal(10,1) DEFAULT NULL, `s_time` int(10) DEFAULT NULL ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8; INSERT INTO `po_sales` (`sid`, `s_name`, `s_amount`, `s_time`) VALUES (1, '2016年度销售', '500.0', 1457929020), (2, '2017年度销售', '600.0', 1489465020), (3, '2018年度销售', '1025.0', 1521001020), (4, '2019年度销售', '1242.0', 1552537020), (5, '2020年度销售', '1448.0', 1584159420), (6, '2021年度销售', '1348.0', 1615695420);
Python读取MySQL并输出元祖
import pymysql import time # 获取pymysql相关属性 # print(dir(pymysql)) # for name in dir(pymysql): # print(name) # 打开数据库连接 conn = pymysql.connect(host='188.131.*.*', user='safety', passwd='w7is4JLEyjiRHrA8', db='safety') # 获取游标 db = conn.cursor() # 查询数据表 sql = "select sid,s_name,s_amount,FROM_UNIXTIME(s_time,'%Y-%m-%d %H:%i:%s') from po_sales" db.execute(sql) # 获取全部数据 results = db.fetchall() # 输出数据 for rs in results: print(rs) # 关闭数据库 conn.close()
Python读取MySQL并输出JSON
# 获取游标 db = conn.cursor() # 查询数据表 sql = "select sid,s_name,s_amount,FROM_UNIXTIME(s_time,'%Y-%m-%d %H:%i:%s') from po_sales" db.execute(sql) # 获取全部数据 results = db.fetchall() # 定义字典和序列 rows = [] data = {} # 输出数据 for rs in results: row = {} row["sid"] = rs[0] row["s_name"] = rs[1] row["s_amount"] = rs[2].to_eng_string() # 浮点数转为字符串 row["s_time"] = rs[3] rows.append(row) data['code'] = 0 data['msg'] = 'OK' data['data'] = rows # 关闭数据库 conn.close() # 输出标准的JSON字符串 jsonStr = json.dumps(data, ensure_ascii=False) print(jsonStr)
lockdatav Done!
