sqlite 不能按同一天选择行

yxyvkwin 于 2023-01-31 发布在 SQLite

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

我是一个新手，在python中使用sqlite时遇到了“select”问题。这是我的代码：

import sqlite3
import pandas as pd
conn = sqlite3.connect('stock_price_1min.db')
cursor = conn.cursor()
query = "SELECT date,high from A397030 Group By substr(date,1,8) "
cursor.execute(query)
row0 = cursor.fetchall()

结果返回：，它只显示每天的前几行。

\[(202207280901, 22200),
(202207290901, 21800),
(202208010901, 21400),
(202208020901, 20750),
...
(202301130901, 15900),
(2023011490901, 16150),
(202301150901, 15650)\]

检查表：表格：A397030

(date,high,low,volume)
(202207280901, 22200, 19500, 434040)
(202207280901, 22200, 19500, 434040)
(202207280903, 23400, 22200, 125861)
...
(202301151519, 22000, 21750, 45180)
(202301151520, 21850, 21450, 30786)
(202301151530, 21850, 21450, 24732)

当我运行fetchall（或fetchmany，fetchone）时，我如何或是否可能在同一天读取所有行，以便我可以在同一天读取所有行，如下所示：

query = certain query(where can read  all rows by same day)
cursor.execute(query)
rows = cursor.fetchxxx()
for row in rows:
print(row)

我希望：行将只在同一天打印出来

first loop of rows

[（第202207280901，22200号），...，（第202207281530，22200号）]

second loop of rows

[（第202207290901，22200号），...，（第202207291530，22200号）]

...

在循环行的末尾

[(202301150901,22200),...,(202301151530, 21450)]

这是我想要的
1.日期格式为YYYYMMDDHHMM，但按YYMMDD选择行，其中仅提供同一天
1.每次使用fetchall（fetchmany、fetchone或fetchsomething）时，基于1：

query = "SELECT date from table Sameday  "
cursor.execute(query)
for i in [1,2,3] :
rows = cursor.fetchsomething()
   for row in rows :
      print(row)

预期结果：

when i=1 # "2022-07-28" only
row = [202207280901,202207280902,..., 202207281430]
when i=2 # "2022-07-29" only
row = [202207290901,202207290902,..., 202207291430]
when i=3 # "2022-07-30" only
row = [202207300901,202207300902,..., 202207301430]

sqlite

来源：https://stackoverflow.com/questions/75259216/cant-select-rows-by-same-day

1条答案

按热度按时间

5tmbdcev1#

1.从表中提取所有行
1.创建字典以按日期存储行
1.迭代您的SQL集合，提取日期字段并在dic中创建一个键。
1.按给定键排序行
代码：

import sqlite3

#Connect to the database

conn = sqlite3.connect("Someday.db")
cursor = conn.cursor()

#Fetch all rows

query = "SELECT * FROM Someday"
cursor.execute(query)
rows = cursor.fetchall()

#Create dictionary

dates = {}
for row in rows:
    date = row[0][:6] #Extract the key in YYMMDD format
    if date not in dates:
        dates[date] = []
    dates[date].append(row)

#Loop through the dictionary and print each row

for date in dates:
    print(dates[date])

#Close the connection

conn.close()

赞(0）回复(0）举报 2023-01-31

我来回答

sqlite 不能按同一天选择行

1条答案

相关问题

热门标签

最新问答