def doall(sql):
"""Executes SQL statement and closes connection.
Args:
sql (TYPE): Description
"""
connection, cursor = connect()
cursor.execute(sql)
commitandclose(connection, cursor)
def countrecords(table):
"""Prints count of records in table.
Args:
table (string): Table name.
"""
connection, cursor = connect()
cursor.execute("SELECT COUNT(*) FROM {table}".format(table=table))
for results in cursor:
print(results)
cursor.close()
connection.close()
def executesql(sql):
"""Executes SQL statement then returns connection and cursor.
Args:
sql (sql): SQL query.
Returns:
(connection, cursor): Connection and cursor objects.
"""
connection, cursor = connect()
cursor.execute(sql)
return (connection, cursor)
def insertbulk(sql, table):
"""Efficient, bulk insert of data.
Args:
sql (list): List of values to be inserted.
"""
# TODO: Count number of inputs
connection, cursor = connect()
cursor.prepare(
"INSERT INTO {table} VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17, :18, :19, :20)"
.format(table=table))
cursor.executemany(None, sql)
commitandclose(connection)
def snapshotlist(table):
"""Returns list of unique snapshots timestamps.
Returns:
list: List of snapshot timestamps.
"""
connection, cursor = connect()
cursor.execute("SELECT UNIQUE TSTAMP FROM {table}".format(table=table))
results = cursor.fetchall()
cursor.close()
connection.close()
snapshots = []
for tstamp in results:
snapshots.append(tstamp[0])
return snapshots
def priceinformation(item, table):
"""Returns price information on item.
Args:
item (int / string): Description
Returns:
list: Result of select.
"""
connection, cursor = connect()
cursor.execute("SELECT * FROM {table} WHERE ITEM = {item}".format(
table=table, item=str(item)))
results = cursor.fetchall()
cursor.close()
connection.close()
return results
def snapshotexists(timestamp, table):
"""Returns true/false if timestamp snapshot exists.
Args:
timestamp (int / string): Unix timestamp in milliseconds.
Returns:
bool: True if timestamp exists otherwise False.
"""
connection, cursor = connect()
cursor.execute(
"SELECT UNIQUE TSTAMP FROM {table} WHERE TSTAMP = {timestamp}".format(
table=table, timestamp=str(timestamp)))
result = cursor.fetchone()
cursor.close()
connection.close()
if result is None:
return False
else:
return True
# -*- coding: utf-8 -*-
import get_stock_data as gsd
import format_stock_data as fsd
import oracle_connect as oc
import multiprocessing as mp
from multiprocessing import Process
from time import ctime, sleep
conn = oc.connect()
def task(code, start):
print("股票", code, "开始")
try:
rawData = gsd.get_data(code=code, start=start)
except:
# 获取数据异常则直接结束，不建表
print("FETCH STOCK %d ERROR" % (code))
return
data = fsd.format_data(rawData)
# print(ctime())
if not oc.is_table_exist(conn, code): # 如果表不存在，先创建表
oc.create_table(conn, code) # 如果表不存在，先建表
try:
insert_data = oc.insert_data(conn, code, data)
except:
oc.truncate_table(conn, code)
print("股票", code, "插入异常")
# print(ctime())
print("股票", code, "结束")