sqlite 将一个表拆分为另一个表，每组有x行

fhity93d 于 2023-08-06 发布在 SQLite

关注(0)|答案(1)|浏览(98)

时间和销售数据从tastytrade.com我试图把蜡烛数据与1000滴答每蜡烛。我可以使用dataframe，Pandas，NumPy，SQLite和Python。
对于SQLite，如何选择分成每组1,000条记录的记录？我尝试将表转换为 Dataframe ，然后转换为字符串以循环字符串的切片，但这无法将max或min应用于 Dataframe 的部分。我得到了7组7万人，他们只有一栏写着“假”。

from decimal import Decimal
from tastytrade.account import Account
from tastytrade.instruments import Equity
from tastytrade.instruments import Future
from tastytrade.order import NewOrder, OrderAction, OrderTimeInForce, OrderType, PriceEffect
import time
import Test_file
from sqlalchemy import create_engine
import pandas as pd
import requests
import sqlite3
import fut
from io import StringIO
import numpy as np
import torch

engine = create_engine('sqlite:///iterist.db', echo=False)
con = sqlite3.connect("iterist.db")
cur=con.cursor()

dinper = ['3']
n=['/ESU3']

a="""select symbol, high, time, volume
from tasty_candles limit 70000
"""
df = pd.read_sql(a, engine)
df = df[df['symbol']==symbols]
df['rows']=df.index
h=df['rows'].astype(str)
n=h.values.tolist()
sym_list = n[0:3000] # take a slice of the symbols from earlier
chunk_size = 2999 #
num_calls = int(len(n) / chunk_size) + 1
df_hold_list = [] #collect intermidiary calls
for i in range(0,num_calls):
    sym_string = ','.join(n[chunk_size*i:chunk_size*(i+1)])
    qf=sym_string.split()
    gt=type(qf)
    con1 = (df['rows'].isin(qf))
    df_hold_list.append(df)
dff = pd.concat(df_hold_list)
dff.to_sql(name='fut_candles', con=engine, if_exists='replace')

字符串

sqlite

来源：https://stackoverflow.com/questions/76787955/split-a-table-into-another-table-with-x-rows-in-each-group

1条答案

按热度按时间

jogvjijk1#

这将时间和销售数据转换为滴答大小的蜡烛块。需要做更多的数据库工作来找到每个蜡烛的最大值、最小值、打开值和关闭值。您可以使用SQL或dataframe，这取决于您的策略。

from decimal import Decimal
from tastytrade.account import Account
from tastytrade.instruments import Equity
from tastytrade.instruments import Future
from tastytrade.order import NewOrder, OrderAction,
OrderTimeInForce, OrderType, PriceEffect
import time
import Test_file
from sqlalchemy import create_engine
import pandas as pd
import requests
import sqlite3
import fut
from io import StringIO
import numpy as np
import torch

engine = create_engine('sqlite:///iterist.db', echo=False)
con = sqlite3.connect("iterist.db")
cur=con.cursor()
cur.execute('DROP TABLE IF EXISTS etimes')
cur.execute('DROP TABLE IF EXISTS qtimes')
cur.execute('DROP TABLE IF EXISTS ytimes')
cur.execute('''CREATE TABLE etimes (
    "index" BIGINT,
    "symbol" text,
    "start" text,
    "end" text);''')

fut_symbols=['/ESU3']

a="""select symbol, high, time, volume
from tasty_candles limit 70000
"""
df = pd.read_sql(a, engine)
df = df[df['symbol']=='/ESU3']
#df['rows']=df.index
std=df['time'].astype(str)
#print(std)
n=std.values.tolist()
sym_list = n[0:1000] # take a slice of the symbols from
                     earlier
chunk_size = 999 #
num_calls = int(len(n) / chunk_size) + 1
df_hold_list = [] #collect intermidiary calls
for i in range(0,num_calls):
    sym_string = ','.join(n[chunk_size*i:chunk_size*(i+1)])
    qf=sym_string.split(',')
    rt = pd.DataFrame(qf, columns= ['time'])
    start = (rt['time'].min(axis=0))
    end = (rt['time'].max(axis=0))
    etimes = pd.DataFrame({'start': [start], 'end': [end],
     'symbol': fut_symbols})
    print(etimes)
    etimes.to_sql(name='etimes', con=engine,
    if_exists='append')
ticks='''select high from tasty_candles
inner join etimes
where tasty_candles.time between etimes.start and
 etimes.end'''
candle=pd.read_sql(ticks, con=engine)
print(candle)

字符串

赞(0）回复(0）举报 2023-08-06

我来回答

sqlite 将一个表拆分为另一个表，每组有x行

1条答案

相关问题

热门标签

最新问答