問題
我有一個帶有兩個表 bt 和資料的 sqlite3 db。兩個表都包含格式為 YYYY-MM-DD 的日期列。我已經閱讀了一張作為熊貓 df 的表格。
我需要遍歷 df 中的行并回傳該行日期后 14 天內的行數。
我試過的代碼
import sqlite3 as lite
import pandas as pd
#Function to Create DB Connection and Raise Error
def create_connection(db_file):
""" create a database connection to the SQLite database
specified by db_file
:param db_file: database file
:return: Connection object or None
"""
conn = None
try:
conn = lite.connect(db_file)
return conn
except lite.Error as e:
print(e)
return conn
#Creat Connection to DB and Create df
conn = create_connection('test.db')
df = pd.read_sql("Select * from data;", conn)
#Create Cursor
cur = conn.cursor()
#Iterate over rows in df and return count of rows within date range
for index ,row in df.iterrows():
cur.execute("SELECT count(*) FROM bt WHERE id='{}' AND datetime(btdate) BETWEEN datetime('{}') AND datetime('{}', '-15 day');".format(row['id'], row['date'], row['date']))
print(cur.fetchall())
電流輸出
上面的查詢回傳全 0，但是我知道表中有符合此條件的行。
[(0,)]
[(0,)]
[(0,)]
[(0,)]
[(0,)]
[(0,)]
[(0,)]
[(0,)]
[(0,)]
[(0,)]
[(0,)]
[(0,)]
[(0,)]
[(0,)]
[(0,)]
[(0,)]
[(0,)]
[(0,)]
[(0,)]
[(0,)]
[(0,)]
[(0,)]
[(0,)]
[(0,)]
期望的輸出 我期望的輸出是兩個日期之間的行數。
uj5u.com熱心網友回復：
您的代碼的問題是您已將運算子的起始值和結束值設定為BETWEEN相反的。
它應該是：
BETWEEN datetime('{}', '-15 day') AND datetime('{}')
但是，我也建議使用?占位符來傳遞引數。此外，如果日期已經是 format
，則無需使用datetime()or函式來獲取日期。date()YYYYY-MM-DD
用這個：
sql = "SELECT COUNT(*) FROM bt WHERE id = ? AND btdate BETWEEN date(?, '-15 day') AND ?;"
cur.execute(sql, (row['id'], row['date'], row['date']))
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/411050.html
標籤：