Python sqlite table sql dump to create table with sample data to run date queries

SQLite

Place part 1, part 2 and part 3 in the same sequence and execute

part 1

import sqlite3
my_conn = sqlite3.connect('test2_db') # Change your database name here
print("Connected to database successfully")
r_set=my_conn.execute("CREATE TABLE IF NOT EXISTS dt_table ( \
  record_id varchar(4) NOT NULL, \
  date text NOT NULL, \
  `month-year` varchar(10) NOT NULL)")
my_conn.commit()

part 2

data=[('-15', '2021-08-13','Aug-2021' ),
('-14', '2021-08-12','Aug-2021' ),
('-13', '2021-08-11','Aug-2021' ),
('-12', '2021-08-10','Aug-2021' ),
('-11', '2021-08-09','Aug-2021' ),
('-10', '2021-08-08','Aug-2021' ),
('-9', '2021-08-07','Aug-2021' ),
('-8', '2021-08-06','Aug-2021' ),
('-7', '2021-08-05','Aug-2021' ),
('-6', '2021-08-04','Aug-2021' ),
('-5', '2021-08-03','Aug-2021' ),
('-4', '2021-08-02','Aug-2021' ),
('-3', '2021-08-01','Aug-2021' ),
('-2', '2021-07-31','Jul-2021' ),
('-1', '2021-07-30','Jul-2021' ),
('0', '2021-07-29','Jul-2021' ),
('1', '2021-07-28','Jul-2021' ),
('2', '2021-07-27','Jul-2021' ),
('3', '2021-07-26','Jul-2021' ),
('4', '2021-07-25','Jul-2021' ),
('5', '2021-07-24','Jul-2021' ),
('6', '2021-07-23','Jul-2021' ),
('7', '2021-07-22','Jul-2021' ),
('8', '2021-07-21','Jul-2021' ),
('9', '2021-07-20','Jul-2021' ),
('10', '2021-07-19','Jul-2021' ),
('11', '2021-07-18','Jul-2021' ),
('12', '2021-07-17','Jul-2021' ),
('13', '2021-07-16','Jul-2021' ),
('14', '2021-07-15','Jul-2021' ),
('15', '2021-07-14','Jul-2021' ),
('16', '2021-07-13','Jul-2021' ),
]

part 3

r_set=my_conn.executemany("INSERT INTO dt_table VALUES (?, ?,?)",data)
my_conn.commit()

Note that Part 2 changes every day ( dynamic ) based on the current date. We are using here previous 15 days and next 15 days as data for our table. This is required to run the queries based on today’s date.

Example : Collect last 5 days records. Collect records of present week , collect records starting from 1st of previous month.

Here is a sample ipynb file used to create the database with sample data. Note that the data tuple changes every day.
View & Download sample ipynb file (.html format)

Sqlite Date Query insert update select Order By

Numpy Pandas Python & MySQL Python- Tutorials

SQLite Sample table with Date column

Subscribe