def executmany(sql, args=None):
with getPTConnection() as db:
try:
cur = db.cursor
result = cur.executemany(sql, args)
db.conn.commit()
return result
except Exception, e:
print e
db.conn.rollback()
def query(sql, args=None):
with getPTConnection() as db:
try:
cur = db.cursor
cur.execute(sql, args)
return db.cursor.fetchall()
except Exception, e:
db.conn.rollback()
raise Exception(e)
def insert(sql):
with getPTConnection() as db:
try:
log.info(sql)
db.cursor.execute(sql)
db.conn.commit()
return 1 # 插入成功
except Exception as e:
log.error(e)
log.error(traceback.format_exc())
return 0 # 插入失败
def insert1(id, name, pid, type, pname):
lock.acquire()
with getPTConnection() as db:
# SQL 查询语句;
try:
db.cursor.execute(
'''INSERT INTO city_code (id,name,pid,type,pname) VALUES ("%s","%s","%s","%s","%s")'''
% (int(id), name, int(pid), int(type), pname))
db.conn.commit()
except Exception, e:
logging.warning("Error: insert error{%s}" % [e])
finally:
def TestMySQL():
#申请资源
with getPTConnection() as db:
# SQL 查询语句;
sql = "SELECT tra_date FROM pricetable_zb where code='600000'"
try:
# 获取所有记录列表
db.cursor.execute(sql)
results = db.cursor.fetchall()
for row in results:
print(row[0])
# 打印结果
except:
print("Error: unable to fecth data")
def query(sql):
results = {}
with getPTConnection() as db:
try:
log.info(sql)
db.cursor.execute(sql)
results = db.cursor.fetchall()
return results
# for spard_session in results:
# print spard_session
# print sess
except Exception as e:
log.error(e)
log.error(traceback.format_exc())
return 0
def TestMySQL():
#申请资源
with getPTConnection() as db:
# SQL 查询语句;
sql = 'select * from users'
try:
# 获取所有记录列表
db.cursor.execute(sql)
results = db.cursor.fetchall()
for row in results:
userId = row[0]
name = row[1].decode()
nickname = row[2].decode()
hobby = row[6].decode()
# 打印结果
print("userId=%d \t 姓名=%s \t 昵称=%s \t 兴趣=%s" %\
( userId, name, nickname, hobby ) )
except:
print("Error: unable to fecth data")
def TestMySQL():
#申请资源
with getPTConnection() as db:
# SQL 查询语句;
tables = input('输入你要提供的表名：')
sql = 'select * from ' + tables
try:
# 获取所有记录列表
db.cursor.execute(sql)
results = db.cursor.fetchall()
dataset = []
temp = []
for row in results:
for column in range(len(row)):
temp.append(row[column].decode())
dataset.append(temp)
temp = []
return dataset
except:
print("出错:无法获取数据,请检查数据库")
def cal_one_stock_sp_price(code):
# 计算一只股票的所有的SP价格 思路 先读取所有非空的chip和close组合。然后整体计算汇成list，最后再executemany
code_table = {
'6': "pricetable_zb",
'0': "pricetable_zxb",
'3': "pricetable_cyb"
}
table_name = code_table[code[0]]
# sql_get_all_records = "select code, tra_date, chip, close from %s where code=%s"%(table_name, code) # 找出所有的chip和close不为空的记录
# sql_get_all_records = "select code, tra_date, chip, close from %s where code=%s and tra_date<'20160101'"%(table_name, code) # 找出所有的chip和close不为空的记录
# sql_get_all_records = "select code, tra_date, chip, close from %s where code=%s and length(chip)>4 and length(close)>=4 "%(table_name, code) # 找出所有的chip和close不为空的记录
# conn = pymysql.connect(host='127.0.0.1', user='******', passwd='passw0rd', db="pv_table", port=3306, charset='utf8')
#
# #conn = pymysql.connect(host='172.16.20.103', user='******', passwd='9JEhCpbeu3YXxyNpFDVQ', port=3308,
# # db='pv_table', charset='utf8')
#
# cur = conn.cursor()
# pdb.set_trace()
# # 获取所有记录，一次性算完之后再写回到Mysql
# cur.execute(sql_get_all_records)
# records_tuple = cur.fetchall()
# records=[]
#
## pdb.set_trace()
# for item in records_tuple[0:1]:
# tmp_pv_table = eval(item[2])
# close = item[3] # code, tra_date, chip, close
# pv_table = {}
#
# for key, value in tmp_pv_table.items():
# pv_table[float(key)] = value
#
# sp_price_dict = extreme(pv_table, close) # 需要获得前复权价格 002668 NoneType has no attribute 'item'
#
# records.append((sp_price_dict["P"], sp_price_dict["S"], item[0], item[1]))
## 所有记录形成list
#
# try:
# if table_name == "pricetable_zb":
# #db.cursor.executemany("insert into pricetable_zb (code, tra_date, close) values(%s,%s,%f)", records)
# cur.executemany("update pricetable_zb set pre_p=%s, sup_p=%s where code=%s and tra_date=%s", records) #%(records[0][2], records[0][0], str(records[0][1]).replace('-','')))
#
# if table_name == "pricetable_zxb":
# #db.cursor.executemany("insert into pricetable_zxb (code, tra_date, close) values(%s,%s,%f)", records)
# cur.executemany("update pricetable_zxb set pre_p=%s, sup_p=%s where code=%s and tra_date=%s", records) #%(records[0][2], records[0][0], str(records[0][1]).replace('-','')))
# if table_name == "pricetable_cyb":
# #db.cursor.executemany("insert into pricetable_cyb (code, tra_date, close) values(%s,%s,%f)", records)
# cur.executemany("update pricetable_cyb set pre_p=%s, sup_p=%s where code=%s and tra_date=%s", records) #%(records[0][2], records[0][0], str(records[0][1]).replace('-','')))
# conn.commit()
# print(code, " over")
# except Exception as e:
# print("Exception: ", str(e))
# conn.rollback()
#
with getPTConnection() as db:
# sql_get_all_records = "select code, tra_date, chip, close from %s where code=%s and length(chip)>4 and length(close)>=4 "%(table_name, code) # 找出所有的chip和close不为空的记录
sql_get_all_records = "select code, tra_date, chip, close from %s where code=%s" % (
table_name, code) # 找出所有的chip和close不为空的记录
#conn = pymysql.connect(host='127.0.0.1', user='******', passwd='', db="pv_table", port=3306, charset='utf8')
# 获取所有记录，一次性算完之后再写回到Mysql
db.cursor.execute(sql_get_all_records)
records_tuple = db.cursor.fetchall()
records = []
# pdb.set_trace()
for item in records_tuple:
tmp_pv_table = eval(item[2])
close = item[3] # code, tra_date, chip, close
pv_table = {}
for key, value in tmp_pv_table.items():
pv_table[float(key)] = value
sp_price_dict = extreme(
pv_table,
close) # 需要获得前复权价格 002668 NoneType has no attribute 'item'
records.append((float(sp_price_dict["P"]),
float(sp_price_dict["S"]), item[0], item[1]))
try:
if table_name == "pricetable_zb":
#db.cursor.executemany("insert into pricetable_zb (code, tra_date, close) values(%s,%s,%f)", records)
db.cursor.executemany(
"update pricetable_zb set pre_p=%s, sup_p=%s where code=%s and tra_date=%s",
records
) #%(records[0][2], records[0][0], str(records[0][1]).replace('-','')))
if table_name == "pricetable_zxb":
#db.cursor.executemany("insert into pricetable_zxb (code, tra_date, close) values(%s,%s,%f)", records)
db.cursor.executemany(
"update pricetable_zxb set pre_p=%s, sup_p=%s where code=%s and tra_date=%s",
records
) #%(records[0][2], records[0][0], str(records[0][1]).replace('-','')))
if table_name == "pricetable_cyb":
#db.cursor.executemany("insert into pricetable_cyb (code, tra_date, close) values(%s,%s,%f)", records)
db.cursor.executemany(
"update pricetable_cyb set pre_p=%s, sup_p=%s where code=%s and tra_date=%s",
records
) #%(records[0][2], records[0][0], str(records[0][1]).replace('-','')))
db.conn.commit()
print(code, " over")
except Exception as e:
print("Exception: ", str(e))
db.conn.rollback()
# with getPTConnection() as db:
for item in files_name[160:300]: # 一个pricetable是一个循环，一次计算完一个pricetable
print(item)
date = item[0:8] # 20160104
sum_df = pd.read_csv("/data/yue_ming_pricetable/pricetable/" + item)
# 修改为一天只处理一个连接
#conn = pymysql.connect(host='172.16.20.103', user='******', passwd='9JEhCpbeu3YXxyNpFDVQ', port=3308,
# db='pv_table', charset='utf8')
# conn=pymysql.connect(host='127.0.0.1', user='******', passwd='', db="pv_table", port=3306, charset='utf8')
# cur = conn.cursor()
# sql_get_all_tables = "select table_name from information_schema.TABLES where TABLE_SCHEMA='pv_table'"
# cur.execute(sql_get_all_tables)
# row_list_tables = cur.fetchall()
with getPTConnection() as db:
new_write_oneday_pricetable(sum_df, date, db)
#from DB_connetion_pool import getPTConnection, PTConnectionPool;
#
#def TestMySQL():
# #申请资源
# with getPTConnection() as db:
# # SQL 查询语句;
# sql = "SELECT tra_date FROM pricetable_zb where code='600000'";
# try:
# # 获取所有记录列表
# db.cursor.execute(sql)
# results = db.cursor.fetchall();