Python MySQL操作实战教程(1):查询并数据JSON格式

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!