python dbf query between two dates

Question

I am using dbf module. I need to search (query) all the products where invoice date between 2023-01-23 to 2023-01-25, include both dates.

I have dbf table ( product.dbf ) with below data

invno  invdt       hsn        mname          packg    
2010 2023-01-21 30086666     Medicine01     10'S  
2010 2023-01-23 30044444     Medicine02      10'S   
2010 2023-01-23 30043333     Medicine03      15'S   
2010 2023-01-23 30049999     Medicine04      15'S  
2011 2023-01-24 30049999     Medicine05      15'S  
2011 2023-01-24 30049999     Medicine06      15'S    
2011 2023-01-24 30049444     Medicine07      30'ML  
2011 2023-01-24 30049333     Medicine08      30'ML   
2011 2023-01-26 30049111     Medicine09      30'ML   
2011 2023-01-26 30049111     Medicine09      30'ML   
2012 2023-01-31 30042333     Medicine10      60'S    
2012 2023-01-31 30042234     Medicine11      15'S      

I am trying with below code, it is not working as expected. I am getting exception when exact date is not there in the table. example if 25-01-2023 is not there, then below exception will be displayed

dbf.NotFoundError: 'dbf.Index.index_search(x): x not in Index'

import dbf
import datetime

table = dbf.Table('product.dbf')
table.open(dbf.READ_ONLY)

index = table.create_index(lambda rec: rec.invdt)

# search between 2023-01-23 to 2023-01-25, include both dates.
fromdate = index.index_search(match=(datetime.datetime.strptime('23-01-2023','%d-%m-%Y').date()))
todate = index.index_search(match=(datetime.datetime.strptime('25-01-2023','%d-%m-%Y').date()))

for row in index[fromdate:todate]:
    print(row[0],row[1],row[2],row[3],row[4])

# Same way below code also throw exception for invoice range, 
# where from or to invoice number(here invoice 2015) is not found.

index = table.create_index(lambda rec: rec.invno)

# search between 2010 to 2015 include both invno.
frominv = index.index_search(match=(2010,))
toinv = index.index_search(match=(2015,))

    
table.close()

Answer 1

You're trying to compare 2023-01-25 to 25-01-2023?

It would be easier to do a comparison like this

import pandas as pd
from simpledbf import Dbf5

dbf = Dbf5('product.dbf')
table = dbf.to_dataframe()
desiredRange = table.loc[(table['invdt'] >= start_date) & (table['invdt'] <= end_date)]

Answer 2

If a missing value is acceptable (which it appears to be in your case), add nearest=True:

fromdate = index.index_search(match=(datetime...).date(), nearest=True)

fromdate will be an IndexLocation, which is a subtype of int (long in Python 2.7) and has a found attribute. found will be True or False, but since you are using fromdate and todate in ranges you won't need that.

---

Looking a bit closer, what you really want is to use index.query*:

start_date = dbf.Date('2023-01-23')
end_date = dbf.date('2023-01-25')
for row in index.query(lambda rec: start_date <= rec.invdt <= end_date):
    # do something

---

* this is not an SQL engine.