def CreateTable():
db = pymysql_connect()
cursor = db.cursor()
cursor.execute('DROP TABLE IF EXISTS industry_estimation_detail')
estimation = """CREATE TABLE IF NOT EXISTS `industry_estimation_detail` (
`code` varchar(25) DEFAULT NULL,
`name` varchar(25) DEFAULT NULL,
`industry` varchar(25) DEFAULT NULL,
`area` varchar(25) DEFAULT NULL,
`pe` varchar(25) DEFAULT NULL,
`outstanding` varchar(25) DEFAULT NULL,
`totals` varchar(25) DEFAULT NULL,
`totalAssets` varchar(25) DEFAULT NULL,
`liquidAssets` varchar(25) DEFAULT NULL,
`fixedAssets` varchar(25) DEFAULT NULL,
`reserved` varchar(25) DEFAULT NULL,
`reservedPerShare` varchar(25) DEFAULT NULL,
`esp` varchar(25) DEFAULT NULL,
`bvps` varchar(25) DEFAULT NULL,
`pb` varchar(25) DEFAULT NULL,
`timeToMarket` varchar(25) DEFAULT NULL,
`undp` varchar(25) DEFAULT NULL,
`perundp` varchar(25) DEFAULT NULL,
`rev` varchar(25) DEFAULT NULL,
`profit` varchar(25) DEFAULT NULL,
`gpr` varchar(25) DEFAULT NULL,
`npr` varchar(25) DEFAULT NULL,
`holders` varchar(25) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8"""
cursor.execute(estimation)
db.commit()
cursor.close()
db.close()
def create_k_table():
db = pymysql_connect()
cursor = db.cursor()
sql1 = """CREATE TABLE IF NOT EXISTS `anack_d_k_data` (
`date` varchar(255) DEFAULT NULL,
`open` varchar(255) DEFAULT NULL,
`close` varchar(255) DEFAULT NULL,
`high` varchar(255) DEFAULT NULL,
`low` varchar(255) DEFAULT NULL,
`volume` varchar(255) DEFAULT NULL,
`code` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8"""
sql2 = """CREATE TABLE IF NOT EXISTS `anack_m_k_data` (
`date` varchar(255) DEFAULT NULL,
`open` varchar(255) DEFAULT NULL,
`close` varchar(255) DEFAULT NULL,
`high` varchar(255) DEFAULT NULL,
`low` varchar(255) DEFAULT NULL,
`volume` varchar(255) DEFAULT NULL,
`code` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8"""
cursor.execute(sql1)
cursor.execute(sql2)
db.commit()
cursor.close()
db.close()
def create_classify_table():
db = pymysql_connect()
cursor = db.cursor()
cursor.execute('DROP TABLE IF EXISTS anack_macro_data')
macro = """CREATE TABLE IF NOT EXISTS `anack_macro_data` (
`month` varchar(255) DEFAULT NULL,
`cpi` varchar(16) DEFAULT NULL,
`ppi` varchar(16) DEFAULT NULL,
`m2` varchar(16) DEFAULT NULL,
`m1` varchar(16) DEFAULT NULL,
`m0` varchar(16) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8"""
cursor.execute(macro)
db.commit()
cursor.close()
db.close()
def create_classify_table():
db = pymysql_connect()
cursor = db.cursor()
cursor.execute('DROP TABLE IF EXISTS anack_classify')
classify = """CREATE TABLE IF NOT EXISTS `anack_classify` (
`code` varchar(255) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
`industry` varchar(255) DEFAULT NULL,
`area` varchar(255) DEFAULT NULL,
`sz50` varchar(255) DEFAULT NULL,
`hs300_weight` FLOAT(10) DEFAULT NULL,
`zz500_weight` FLOAT(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8"""
cursor.execute(classify)
db.commit()
cursor.close()
db.close()
def CreateTable_industry_avg():
db = pymysql_connect()
cursor = db.cursor()
cursor.execute('DROP TABLE IF EXISTS industry_estimation_avg')
estimation = """CREATE TABLE IF NOT EXISTS `industry_estimation_avg` as
select industry,avg(pe) as avg_pe,avg(outstanding) as avg_outstanding,
avg(totals) as avg_totals ,avg(totalAssets) as avg_totalAssets,
avg(liquidAssets) as avg_liquidAssets,avg(fixedAssets) as avg_fixedAssets,
avg(reserved) as avg_reserved,avg(reservedPerShare) as avg_reservedPerShare,
avg(esp) as avg_esp,avg(bvps) as avg_bvps,avg(pb) as avg_pb,
avg(timeToMarket) as avg_timeToMarket,avg(undp) as avg_undp,
avg(perundp) as avg_perundp,avg(rev) as avg_rev ,avg(profit) as avg_profit
,avg(gpr) as avg_gpr ,avg(npr) as avg_npr ,avg(holders) as avg_holders
from industry_estimation_detail where pe < 100 and pb < 10 and rev <1000 and rev > -1000 and profit < 1000 and profit > -1000 and gpr < 1000 and gpr > -1000 and npr < 1000 and npr > -1000
group by industry
"""
cursor.execute(estimation)
db.commit()
cursor.close()
db.close()
def CreateTable():
db = pymysql_connect()
cursor = db.cursor()
cursor.execute('DROP TABLE IF EXISTS industry_estimation')
estimation = """CREATE TABLE IF NOT EXISTS `industry_estimation` (
`行业` varchar(25) DEFAULT NULL,
`年度` varchar(25) DEFAULT NULL,
`企业数量` int(25) DEFAULT NULL,
`总市值` float(25) DEFAULT NULL,
`平均市值` float(25) DEFAULT NULL,
`平均市盈率` float(25) DEFAULT NULL,
`平均市净率` float(25) DEFAULT NULL,
`收入增长率` float(25) DEFAULT NULL,
`利润增长率` float(25) DEFAULT NULL,
`毛利率` float(25) DEFAULT NULL,
`净利润率` float(25) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8"""
cursor.execute(estimation)
db.commit()
cursor.close()
db.close()
#import pymysql
#from sqlalchemy import create_engine
import tushare as ts
from SQL.sql import pymysql_connect
from SQL.sql import df_to_mysql
#from SQL.glo import get_value
#import requests
## 加上字符集参数，防止中文乱码
#确定输出表头信息：
#基础：总市值、平均市值、市盈率、市净率、收入增长、净利增长、毛利率、净利率
#扩展：资产负债率、市净率、市现率、市销率（需要根据财务报表获取）
#自定义：
dbconn = pymysql_connect()
clm = [
'行业', '年度', '企业数量', '总市值', '平均市值', '平均市盈率', '平均市净率', '收入增长率', '利润增长率',
'毛利率', '净利润率'
]
headers = [
'name', 'industry', 'totalAssets', 'pe', 'pb', 'rev', 'profit', 'gpr',
'npr'
]
#sql语句示例
#select 字段 from 表名 where 条件;
#eg:select * from student where sex='男' and age>20; //查询性别是男，并且年龄大于20岁的人。