Ejemplo n.º 1
0
    def get_bars(self, pcontract, dt_start, dt_end, window_size):
        cursor = self.db.cursor()
        id_start, u = datautil.encode2id(pcontract.period, dt_start)
        id_end, u = datautil.encode2id(pcontract.period, dt_end)
        table = '_'.join(
            [pcontract.contract.exchange, pcontract.contract.code])
        #sql = "SELECT COUNT(*) FROM {tb} \
        #WHERE {start}<=id AND id<={end}".format(tb=table, start=id_start, end=id_end)
        #max_length = cursor.execute(sql).fetchone()[0]
        #
        sql = "SELECT datetime, open, close, high, low, volume FROM {tb} \
                WHERE {start}<=id AND id<={end}".format(tb=table,
                                                        start=id_start,
                                                        end=id_end)

        data = pd.read_sql_query(sql, self.db, index_col='datetime')
        if not series.g_rolling:
            data = pd.read_sql_query(sql, self.db, index_col='datetime')
            ## @todo
            return SqliteSourceWrapper(pcontract, data, None, len(data))
        else:
            cursor.execute(sql)
            data = pd.DataFrame({
                'open': [],
                'close': [],
                'high': [],
                'low': [],
                'volume': []
            })
            data.index = []
            return SqliteSourceWrapper(pcontract, data, cursor, window_size)
Ejemplo n.º 2
0
 def load_bars(self, pcontract, dt_start, dt_end, window_size):
     cursor = self.db.cursor()
     id_start, u = datautil.encode2id(pcontract.period, dt_start)
     id_end, u = datautil.encode2id(pcontract.period, dt_end)
     table = string.replace(str(pcontract.contract), '.', '_')
     #sql = "SELECT COUNT(*) FROM {tb} \
             #WHERE {start}<=id AND id<={end}".format(tb=table, start=id_start, end=id_end)
     #max_length = cursor.execute(sql).fetchone()[0]
     #
     sql = "SELECT datetime, open, close, high, low, volume FROM {tb} \
             WHERE {start}<=id AND id<={end}".format(tb=table, start=id_start, end=id_end)
             
     data = pd.read_sql_query(sql, self.db, index_col='datetime')
     if not series.g_rolling:
         data = pd.read_sql_query(sql, self.db, index_col='datetime')
         ## @todo
         return SqliteSourceWrapper(pcontract, data, None, len(data))
     else:
         cursor.execute(sql)
         data = pd.DataFrame({
             'open': [],
             'close': [],
             'high': [],
             'low': [],
             'volume': []
             })
         data.index = []
         return SqliteSourceWrapper(pcontract, data, cursor, window_size)
Ejemplo n.º 3
0
 def get_bars(self, pcontract, dt_start, dt_end):
     id_start, u = datautil.encode2id(pcontract.period, dt_start)
     id_end, u = datautil.encode2id(pcontract.period, dt_end)
     table = '_'.join([pcontract.contract.exchange,
                       pcontract.contract.code])
     sql = "SELECT datetime, open, close, high, low, volume FROM {tb} \
             WHERE {start}<=id AND id<={end}".format(
                 tb=table, start=id_start, end=id_end)
     data = pd.read_sql_query(sql, self._db, index_col='datetime')
     return SourceWrapper(pcontract, data, len(data))
Ejemplo n.º 4
0
 def load_bars(self, pcontract, dt_start, dt_end):
     id_start, u = datautil.encode2id(pcontract.period, dt_start)
     id_end, u = datautil.encode2id(pcontract.period, dt_end)
     table = string.replace(str(pcontract.contract), '.', '_')
     #rows = self.cursor.execute("SELECT * FROM %s" % table) 
     ## @todo 使用参数dt_start, dt_end
     sql = "SELECT utime, open, close, high, low, volume FROM {tb} \
             WHERE {start}<=id AND id<={end}".format(tb=table, start=id_start, end=id_end)
     data =  pd.read_sql_query(sql, self.db, index_col='utime')
     return data
Ejemplo n.º 5
0
 def get_bars(self, pcontract, dt_start, dt_end):
     id_start, u = datautil.encode2id(pcontract.period, dt_start)
     id_end, u = datautil.encode2id(pcontract.period, dt_end)
     table = '_'.join([pcontract.contract.exchange, pcontract.contract.code])
     #sql = "SELECT COUNT(*) FROM {tb} \
             #WHERE {start}<=id AND id<={end}".format(tb=table, start=id_start, end=id_end)
     #max_length = cursor.execute(sql).fetchone()[0]
     #
     sql = "SELECT datetime, open, close, high, low, volume FROM {tb} \
             WHERE {start}<=id AND id<={end}".format(tb=table, start=id_start, end=id_end)
     data = pd.read_sql_query(sql, self.db, index_col='datetime')
     return SqliteSourceWrapper(pcontract, data, len(data))
Ejemplo n.º 6
0
 def load_bars(self, pcontract, dt_start, dt_end):
     id_start, u = datautil.encode2id(pcontract.period, dt_start)
     id_end, u = datautil.encode2id(pcontract.period, dt_end)
     table = string.replace(str(pcontract.contract), '.', '_')
     #rows = self.cursor.execute("SELECT * FROM %s" % table)
     ## @todo 使用参数dt_start, dt_end
     sql = "SELECT utime, open, close, high, low, volume FROM {tb} \
             WHERE {start}<=id AND id<={end}".format(tb=table,
                                                     start=id_start,
                                                     end=id_end)
     data = pd.read_sql_query(sql, self.db, index_col='utime')
     return data
Ejemplo n.º 7
0
 def get_bars(self, pcontract, dt_start, dt_end):
     id_start, u = datautil.encode2id(pcontract.period, dt_start)
     id_end, u = datautil.encode2id(pcontract.period, dt_end)
     table = '_'.join([pcontract.contract.exchange,
                       pcontract.contract.code])
     # sql = "SELECT COUNT(*) FROM {tb} \
             # WHERE {start}<=id AND id<={end}".format(tb=table, start=id_start, end=id_end)
     # max_length = cursor.execute(sql).fetchone()[0]
     #
     sql = "SELECT datetime, open, close, high, low, volume FROM {tb} \
             WHERE {start}<=id AND id<={end}".format(
                 tb=table, start=id_start, end=id_end)
     data = pd.read_sql_query(sql, self.db, index_col='datetime')
     return SqliteSourceWrapper(pcontract, data, len(data))
Ejemplo n.º 8
0
 def get_bars(self, pcontract, dt_start, dt_end):
     id_start, _ = datautil.encode2id(pcontract.period, dt_start)
     id_end, _ = datautil.encode2id(pcontract.period, dt_end)
     colname = self.__get_collection_name(pcontract.period,
                                          pcontract.contract.exchange,
                                          pcontract.contract.code)
     cursor = self.db[colname].find({
         'id': {
             '$gt': id_start,
             '$lt': id_end
         }
     }).sort('id', pymongo.ASCENDING)
     data = pd.DataFrame(list(cursor)).set_index('datetime')
     return MongoSourceWrapper(pcontract, data, len(data))
Ejemplo n.º 9
0
 def get_bars(self, pcontract, dt_start, dt_end):
     id_start, _ = datautil.encode2id(pcontract.period, dt_start)
     id_end, _ = datautil.encode2id(pcontract.period, dt_end)
     colname = self.__get_collection_name(
         pcontract.period,
         pcontract.contract.exchange,
         pcontract.contract.code)
     cursor = self.db[colname].find({
         'id': {
             '$gt': id_start,
             '$lt': id_end
         }
     }).sort('id', pymongo.ASCENDING)
     data = pd.DataFrame(list(cursor)).set_index('datetime')
     return MongoSourceWrapper(pcontract, data, len(data))
Ejemplo n.º 10
0
 def import_bars(self, tbdata, pcontract):
     """ 导入交易数据
     
     Args:
         tbdata (dict): {'datetime', 'open', 'close', 'high', 'low', 'volume'}
         pcontract (PContract): 周期合约
     """
     strpcon = str(pcontract).upper()
     data = []
     ids, utimes = [], []
     strdt = strpcon.split('-')[1].upper()
     tbname = strpcon.split('-')[0].split('.')
     tbname = "_".join([tbname[1], tbname[0]])
     for dt in tbdata['datetime']:
         id,  utime = datautil.encode2id(strdt, dt)
         ids.append(id)
         utimes.append(utime)
     data = zip(ids, utimes, tbdata['open'], tbdata['close'], tbdata['high'],
                tbdata['low'], tbdata['volume'])
     try:
         self.cursor.execute('''CREATE TABLE {tb}
                      (id int primary key,
                       datetime timestamp,
                       open real,
                       close real,
                       high real,
                       low real,
                       volume int)'''.format(tb = tbname))
         self.db.commit()
     except sqlite3.OperationalError:
         pass
     finally:
         sql = "INSERT INTO %s VALUES (?,?,?,?,?,?,?)" % tbname
         self.cursor.executemany(sql, data)
         self.db.commit()
Ejemplo n.º 11
0
 def import_bars(self, tbdata, pcontract):
     """ 导入交易数据
     
     Args:
         tbdata (dict): {'datetime', 'open', 'close', 'high', 'low', 'volume'}
         pcontract (PContract): 周期合约
     """
     strpcon = str(pcontract).upper()
     data = []
     ids, utimes = [], []
     strdt = strpcon.split('-')[1].upper()
     tbname = strpcon.split('-')[0].split('.')
     tbname = "_".join([tbname[1], tbname[0]])
     for dt in tbdata['datetime']:
         id, utime = datautil.encode2id(strdt, dt)
         ids.append(id)
         utimes.append(utime)
     data = zip(ids, utimes, tbdata['open'], tbdata['close'],
                tbdata['high'], tbdata['low'], tbdata['volume'])
     try:
         self.cursor.execute('''CREATE TABLE {tb}
                      (id int primary key,
                       datetime timestamp,
                       open real,
                       close real,
                       high real,
                       low real,
                       volume int)'''.format(tb=tbname))
         self.db.commit()
     except sqlite3.OperationalError:
         pass
     finally:
         sql = "INSERT INTO %s VALUES (?,?,?,?,?,?,?)" % tbname
         self.cursor.executemany(sql, data)
         self.db.commit()
Ejemplo n.º 12
0
 def _df2sqlite(self, df, tbname):
     self.cursor.execute('''CREATE TABLE {tb}
                  (id int primary key,
                   datetime timestamp,
                   open real,
                   close real,
                   high real,
                   low real,
                   volume int)'''.format(tb = tbname))
     data = []
     for index, row in df.iterrows():
         id, datetime = datautil.encode2id('1.Minute', index)
         data.append((id, datetime, row['open'], row['close'], row['high'], row['low'], row['vol']))
     sql = "INSERT INTO %s VALUES (?,?,?,?,?,?,?)" % tbname
     self.cursor.executemany(sql, data)
     self.db.commit()
Ejemplo n.º 13
0
 def _df2sqlite(self, df, tbname):
     self.cursor.execute('''CREATE TABLE {tb}
                  (id int primary key,
                   datetime timestamp,
                   open real,
                   close real,
                   high real,
                   low real,
                   volume int)'''.format(tb = tbname))
     data = []
     for index, row in df.iterrows():
         id, datetime = datautil.encode2id('1.Minute', index)
         data.append((id, datetime, row['open'], row['close'], row['high'], row['low'], row['vol']))
     sql = "INSERT INTO %s VALUES (?,?,?,?,?,?,?)" % tbname
     self.cursor.executemany(sql, data)
     self.db.commit()
Ejemplo n.º 14
0
 def import_bars(self, tbdata, pcontract):
     strpcon = str(pcontract).upper()
     code_exchange, strdt = strpcon.split('-')
     code, exchange = code_exchange.split('.')
     colname = self.__get_collection_name(strdt, exchange, code)
     ts = map(lambda dt: datautil.encode2id(strdt, dt), tbdata['datetime'])
     ids, utimes = zip(*ts)
     data = map(lambda (_id, _datetime,
                        _open, _close,
                        _high, _low,
                        _volume): {
         'id': _id, 'datetime': _datetime,
         'open': _open, 'close': _close,
         'high': _high, 'low': _low,
         'volume': _volume
     }, zip(ids, tbdata['datetime'], tbdata['open'], tbdata['close'],
            tbdata['high'], tbdata['low'], tbdata['volume']))
     self.db[colname].insert_many(data)
Ejemplo n.º 15
0
 def _df2sqlite(self, df, tbname):
     self.cursor.execute(
         """CREATE TABLE {tb}
                  (id int primary key,
                   datetime timestamp,
                   open real,
                   close real,
                   high real,
                   low real,
                   volume int)""".format(
             tb=tbname
         )
     )
     data = []
     for index, row in df.iterrows():
         id, datetime = datautil.encode2id("1.Minute", index)
         data.append((id, datetime, row["open"], row["close"], row["high"], row["low"], row["vol"]))
     sql = "INSERT INTO %s VALUES (?,?,?,?,?,?,?)" % tbname
     self.cursor.executemany(sql, data)
     self.db.commit()
Ejemplo n.º 16
0
 def import_bars(self, tbdata, strpcon):
     """ 导入交易数据
     
     Args:
         tbdata (dict): {'datetime', 'open', 'close', 'high', 'low', 'volume'}
         strpcon (str): 周期合约字符串如, 'AA.SHFE-1.Minute'生成表格: 'SHFE_AA' 
     """
     data = []
     ids, utimes = [], []
     strdt = strpcon.split("-")[1].upper()
     tbname = strpcon.split("-")[0].split(".")
     tbname = "_".join([tbname[1], tbname[0]])
     for dt in tbdata["datetime"]:
         id, utime = datautil.encode2id(strdt, dt)
         ids.append(id)
         utimes.append(utime)
     data = zip(ids, utimes, tbdata["open"], tbdata["close"], tbdata["high"], tbdata["low"], tbdata["volume"])
     try:
         self.cursor.execute(
             """CREATE TABLE {tb}
                      (id int primary key,
                       datetime timestamp,
                       open real,
                       close real,
                       high real,
                       low real,
                       volume int)""".format(
                 tb=tbname
             )
         )
         self.db.commit()
     except sqlite3.OperationalError:
         pass
     finally:
         sql = "INSERT INTO %s VALUES (?,?,?,?,?,?,?)" % tbname
         self.cursor.executemany(sql, data)
         self.db.commit()