0
2
盘古平台下
python数据库使用教程
本次教程分成三个部分：
首先，第一部分介绍目前盘古股平台上已经提供的一系列封装好的接口。这些接口可以让使用者通过传递参数的方式直接调用，本文中会以案例的形式进行简要的介绍。
其次，盘古平台除了通过上述封装好的数据接口来查询数据之外，还支持使用者直接编写SQL语句，通过查询具体的数据表，来查询个性化信息。在这部分我们会教会使用者如何使用python语言，通过数据表的增删改查以及表的连接和分组来实现数据的访问。
最后，我们会在本文的最后介绍使用Pyspark SQL操作大数据集的实际应用，并通过实际案例来介绍具体使用。
数据库使用教程
一、盘古平台访问数据接口
from DataApi import BondAPI##导入BondAPIbond_api = BondAPI()##实例化BondAPI1、获取债券基础资料
bondBasicInfo = bond_api.getBondBasicInfo(['125328'])##获取债券代码为125328的基础资料display(bondBasicInfo.head(5))##head函数用于展示前5条数据
2、获取计息期次表
interestSchedule = bond_api.getInterestSchedule(['125328'])##取债券125328的计息其次信息display(interestSchedule.head(5))##head函数用于展示前5条数据
3、获取选择权表
bondOption = bond_api.getBondOption(['125328'])##获取债券125328的选择权信息
display(bondOption.head(5))##head函数用于展示前5条数据4、获取工作日期
display(bond_api.getWorkDate("2020-01-02", "2020-01-10"))##获取2020年1月2日到2020年1月10之间的工作日期列表
['2020-01-02',
'2020-01-03',
'2020-01-06',
'2020-01-07',
'2020-01-08',
'2020-01-09',
'2020-01-10']5、获取指定行业的债券列表
sectorBonds = bond_api.getSectorBonds('2020-01-02', sector_dl='UE', sector_xl='5443')##获取2020年1月2日，行业分类大类代码为“UE”，或者小类代码为“5443”
display(sectorBonds.head(5))##head函数用于展示前5条数据
6、获取曲线收益率插值计算要素
curveFormula = bond_api.getCurveFormula('2020-01-02', '中短', 1)##获取2020年1月2日，曲线名称中包含“中短”字样，且曲线分类为“1”的曲线信息
display(curveFormula.head(5))##head函数用于展示前5条数据
数据库使用教程
二、盘古平台访问数据表
1、数据库的增、删、改、查操作1、数据库查询操作：在ADS库的债券基本资料表中，查询某一只债券的债券资料。
import DBLab as db
sql = "SELECT * FROM htis.adb_xxfw_wbzq where zqdm='138666'"
db.execute(sql,display=False)
2、数据库插入操作：在ADS库的债券基本资料表中，插入某一只债券的债券资料。
import DBLab as db
sql = "insert into adb_xxfw_wbzq(zqdm,zqxh,zqmc,zqjc,zqlxdm,zqpzdm) VALUES('0000001'，''，'债券名称'，'债券简称','01','10')"#举例插入债券代码为0000001的债券
db.execute(sql,display=False)
##具备数据库插入权限时，可执行。仅支持查询语句
3、数据库删除记录的操作:在ADS库的债券基本资料表中，删除某一只债券的债券资料。import DBLab as db
sql = "delete from adb_xxfw_wbzq WHERE zqdm = 138666"
db.execute(sql,display=False)
##具备数据库删除权限时，可执行。
仅支持查询语句
4、数据库更新操作：在ADS库的债券基本资料表中，修改某一只债券的债券资料，修改138666这只债券的类型代码为02。
import DBLab as db
sql = "update adb_xxfw_wbzq set zqlxdm = 02 WHERE zqdm = 138666"
db.execute(sql,display=False)
##具备数据库更新权限时，可执行。仅支持查询语句
2、数据库的连接操作1、inner join：查询某一债券的债项评级情况，返回结果是“adb_xxfw_wbzq表”和“adb_xxfw_wbxzxpj表”的交集。如果所查询的债券没有债项评级，则查询结果集中无相应的记录。
import DBLab as db
sql = "SELECT a.zqmc,b.* FROM htis.adb_xxfw_wbzq a INNER JOIN htis.adb_xxfw_wbxzxpj b ON a.zqdm=b.zqdm where a.zqdm = '138666'"
db.execute(sql,display=False)
2、left join：查询某一债券的债项评级情况，是“adb_xxfw_wbzq表”和“adb_xxfw_wbxzxpj表”的左连接。返回结果是“adb_xxfw_wbzq表”(左表)的完全集，也就是满足条件的所有债券的集合。如果所查询的债券没有债项评级，则查询结果集中相应记录的评级字段以null值代替。
import DBLab as db
sql = "SELECT a.zqmc,b.* FROM htis.adb_xxfw_wbzq a left JOIN htis.adb_xxfw_wbxzxpj b ON a.zqdm=b.zqdm where a.zqdm ='138666'"
db.execute(sql,display=False)
3、right join：右连接和左连接相对应。查询某一债券的债项评级情况，是“adb_xxfw_wbxzxpj表”和“adb_xxfw_wbzq表”的右连接。返回结果是“adb_xxfw_wbzq表”(右表)的完全集，也就是满足条件的所有债券的集合。如果所查询的债券没有债项评级，则查询结果集中相应记录的评级字段以null值代替。
import DBLab as db
sql = "SELECT a.zqmc,b.* FROM htis.adb_xxfw_wbxzxpj b right JOIN htis.adb_xxfw_wbzq a ON a.zqdm=b.zqdm where a.zqdm = '138666'"
db.execute(sql,display=False)
4、full join：返回结果是“adb_xxfw_wbzq表”和“adb_xxfw_wbxzxpj表”的并集，对于没有匹配的记录，则以null值代替。
import DBLab as db
sql = "SELECT a.zqmc,b.* FROM htis.adb_xxfw_wbzq a FULL OUTER JOIN htis.adb_xxfw_wbxzxpj b ON a.zqdm=b.zqdm where a.zqdm = '138666'"
db.execute(sql,display=False)
GROUP BY语句可结合一些聚合函数来使用,GROUP BY语句用于结合聚合函数，根据一个或多个列对结果集进行分组。同时在SQL中增加HAVING子句原因是，WHERE 关键字无法与聚合函数一起使用。而HAVING 子句可以让我们筛选分组后的各组数据。下面结合几个例子进行说明。
1、GROUP BY简单应用：统计所有发生在2019年2月1日和2019年3月1日之间的，按照流通场所划分的，且流通场所为“10”、“30”、“40”的，所有已发布债券的总体标准差。
import DBLab as db
sql = "SELECT P.ZQDM, P.CSBH, STDDEV_POP(P.GJJJ) AS GJJJ_BZC \
FROM BOND_EVL_PRICE P \
WHERE P.FBBS = '1' \
AND P.CSBH IN ('10', '30', '40') \
AND P.WORK_DATE BETWEEN to_date('20190201','yyyymmdd') AND to_date('20190301','yyyymmdd') \
AND P.ZQDM in ('150556','150565','1520014','170010') \
GROUP BY P.ZQDM, P.CSBH"
db.execute(sql,display=False)
2、GROUP BY进行多表的连接查询：统计所有发生在2019年2月1日和2019年3月1日之间的，按照流通场所划分的，且流通场所为“10”、“30”、“40”的，所有已发布债券的,且有债项评级的债券的总体标准差。
import DBLab as db
sql = "SELECT P.ZQDM, P.CSBH, STDDEV_POP(P.GJJJ) AS GJJJ_BZC \
FROM BOND_EVL_PRICE P INNER JOIN htis.adb_xxfw_wbxzxpj b ON P.zqdm=b.zqdm \
WHERE P.FBBS = '1' \
AND P.CSBH IN ('10', '30', '40') \
AND P.WORK_DATE BETWEEN to_date('20190201','yyyymmdd') AND to_date('20190301','yyyymmdd') \
AND P.ZQDM in ('150556','150565','1520014','170010') \
GROUP BY P.ZQDM, P.CSBH"
db.execute(sql,display=False)
3、HAVING子句的简单应用：统计所有发生在2019年2月1日和2019年3月1日之间的，按照流通场所划分的，且流通场所为“10”、“30”、“40”的，所有已发布债券的总体标准差，并展示其中总体标准差大于0.01的记录。
import DBLab as db
sql = "SELECT P.ZQDM, P.CSBH, STDDEV_POP(P.GJJJ) AS GJJJ_BZC \
FROM BOND_EVL_PRICE P \
WHERE P.FBBS = '1' \
AND P.CSBH IN ('10', '30', '40') \
AND P.WORK_DATE BETWEEN to_date('20190201','yyyymmdd') AND to_date('20190301','yyyymmdd') \
AND P.ZQDM in ('150556','150565','1520014','170010') \
GROUP BY P.ZQDM, P.CSBH HAVING STDDEV_POP(P.GJJJ) > 0.2"
db.execute(sql,display=False)
4、HAVING子句和where子句联合使用：统计所有发生在2019年2月1日和2019年3月1日之间的，按照流通场所划分的，且流通场所为“10”、“30”、“40”的，所有已发布债券的,且实际代偿期大于1年的债券的总体标准差。
import DBLab as db
sql = "SELECT P.ZQDM, P.CSBH, STDDEV_POP(P.GJJJ) AS GJJJ_BZC \
FROM BOND_EVL_PRICE P \
WHERE P.FBBS = '1' \
AND P.CSBH IN ('10', '30', '40') \
AND P.WORK_DATE BETWEEN to_date('20190201','yyyymmdd') AND to_date('20190301','yyyymmdd') \
AND P.ZQDM in ('150556','150565','1520014','170010') \
AND P.SJDCQ > 4\
GROUP BY P.ZQDM, P.CSBH"
db.execute(sql,display=False)
1、count()函数：查询2019年3月1日当天，处于发布状态的债券的总条数。
import DBLab as db
sql = "SELECT COUNT(*) num FROM BOND_EVL_PRICE where WORK_DATE = to_date('20190301','yyyymmdd') and fbbs = 1"
db.execute(sql,display=False)
2、SUM()函数：计算债券138666在2019年2月1日到3月1日的净价离差的总和。
import DBLab as db
sql = "SELECT sum(t.PREJJLC) sum FROM BOND_EVL_PRICE t where \
t.WORK_DATE BETWEEN to_date('20190501','yyyymmdd') AND to_date('20200529','yyyymmdd') \
and t.zqdm = '138666'"
db.execute(sql,display=False)
3、AVG()函数：查询债券138666在2019年2月1日至2019年3月1日之间，债券估价净价的平均值。
import DBLab as db
sql = "SELECT avg(gjjj) avg FROM BOND_EVL_PRICE where \
WORK_DATE BETWEEN to_date('20200501','yyyymmdd') AND to_date('20200529','yyyymmdd') \
and zqdm = '138666'"
db.execute(sql,display=False)
4、MAX()函数：查询债券138666在2019年2月1日至2019年3月1日之间，债券估价净价的最大值。
import DBLab as db
sql = "SELECT max(gjjj) avg FROM BOND_EVL_PRICE where \
WORK_DATE BETWEEN to_date('20200501','yyyymmdd') AND to_date('20200529','yyyymmdd')\
and zqdm = '138666'"
db.execute(sql,display=False)
5、MIN()函数：查询债券138666在2019年2月1日至2019年3月1日之间，债券估价净价的最小值。
import DBLab as db
sql = "SELECT min(gjjj) avg FROM BOND_EVL_PRICE where \
WORK_DATE BETWEEN to_date('20200501','yyyymmdd') AND to_date('20200529','yyyymmdd') \
and zqdm = '138666'"
db.execute(sql,display=False)
数据库使用教程
三、使用pyspark SQL处理大数据
Spark为结构化数据处理引入了一个称为Spark SQL的编程模块。它提供了一个称为DataFrame的编程抽象，并且可以充当分布式SQL查询引擎。Spark SQL无缝 地将SQL查询与Spark程序整合，将结构化数据作为Spark中的分布式数据集(RDD)进行查询，在Python中集成了API，这使得其可以轻松地运行SQL查询以及复杂的分 析算法。DataFrame与关系数据库中的表类似，但具有更丰富的优化,它是一种适用于结构和半结构化数据的数据抽象,是命名列和行形式的分布式数据集合。下面将结合实际案例来简要介绍如何使用Spark SQL处理大数据集。
1、引入Spark SQL包，为数据分析做准备。
from pyspark.sql import SparkSession
from pyspark.sql import Row
from pyspark.sql.types import StructType, StructField, LongType, StringType2、创建SparkSession对象。SparkSession是在Spark 2.0中引入的，它替换了旧的SQLContext和HiveContext，作为通往Spark SQL的入口。
spark=SparkSession.builder.appName('app_name').getOrCreate()3、实际案例一：定义函数Read_Json()，使用Spark SQL解析Json数据。
def Read_Json():
# 构造用于测试的Json数据，使用parallelize来创建并行集合，可以在分布式集群上并行计算。
stringJSONRDD = spark.sparkContext.parallelize((
"""{"id": "123","name": "Katie","age": 19,"eyeColor": "brown"}""",
"""{"id": "234","name": "Michael","age": 22,"eyeColor": "green"}""",
"""{"id": "345","name": "Simone","age": 23,"eyeColor": "blue"}""")
)
# 创建DataFrame
DATA_JSON = spark.read.json(stringJSONRDD)
# DataFrame 注册为临时表 DATA_JSON
DATA_JSON.createOrReplaceTempView("DATA_JSON")
# DataFrame API 查看数据
DATA_JSON.show()
# 使用SQL查询
data=spark.sql("select * from DATA_JSON").collect() # sql函数返回的 DataFrame对象
for i in data: # 对于data中的每行是 Row类型,数据内容像键值对。
print(i['eyeColor'])
DATA_JSON.printSchema() # 查看模型树4、实际案例二：定义函数Specify_Schema()，按照特定Schema生成DataFrame，然后使用Spark SQL处理数据。
def Specify_Schema():
#使用parallelize来创建并行集合，可以在分布式集群上并行计算。
stringCSVRDD = spark.sparkContext.parallelize([(123, 'Katie', 19, 'brown'), (234, 'Michael', 22, 'green'), (345, 'Simone', 23, 'blue')])
#定义schema的结构。
schema = StructType([
StructField("id", LongType(), True),
StructField("name", StringType(), True),
StructField("age", LongType(), True),
StructField("eyeColor", StringType(), True)
])
DATA = spark.createDataFrame(stringCSVRDD, schema) # 创建 DataFrame，并指定schema
DATA.createOrReplaceTempView("DATA") # 构建临时表DATA
spark.sql("SELECT id , age , eyecolor FROM DATA").show() # 选择对应的列
spark.sql("select count(*) cnt from DATA").show() # 使用聚合函数
spark.sql("select id, age from DATA where age = 22").show() # 使用where子句
spark.sql("select name, eyeColor from DATA where eyeColor like 'b%' ").show() # 使用like子句
# --------------- 和spark sql 同样效果的 DataFrame API ------------------
DATA.show()
DATA.count()
DATA.select("id","age").filter("age=22").show()
DATA.select("name", "eyeColor").filter("eyeColor like 'b%'").show()5、实际案例三：使用Spark SQL进行大数据集的多表操作。
def Multi_Table_Query():
#构建网站信息表，从文件websites.csv文件中
websitesFilePath = "websites.csv"
websites = spark.read.csv(websitesFilePath, header='true', inferSchema='true', sep='\t')#读取CSV文件
websites.createOrReplaceTempView("websites") # 构建临时表websites
#构建网站访问记录表，从文件Access_log.csv文件中获取
AccesslogFilePath = "Access_log.csv"
Access_log = spark.read.csv(AccesslogFilePath, header='true')#读取CSV文件
Access_log.createOrReplaceTempView("Access_log")# 构建临时表Access_log
Access_log.cache()
spark.sql("SELECT b.id,a.site_id,SUM(a.count) AS nums FROM access_log a \
INNER JOIN Websites b ON b.id=a.site_id \
GROUP BY a.site_id order by SUM(a.count) desc").show()
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
