Sqlite3 Date Data type

One, Sqlite3 date data type, by default with datetime parsing (according to Stackflow）

Note three points when using:

1. When creating a table, the field DT is of type date

2. When inserting data, the DT field is directly in str type

3. The dt field of STR, date and Year must be xxxx-xx-xx format, such as 2016-01-01, cannot be 2016-1-1

ImportSqlite3Importdatetime" "sqlite3 Date Data type""
Con= Sqlite3.connect (": Memory:") C=con.cursor ()#Create TableC.execute (" "CREATE TABLE marksix (DT date, Period text, P1 int, P2 int, P3 int, P4 int, P5 int, P6 int, T7 int)" ")#Larger example that inserts many records at a timepurchases = [('2016-01-01','2016001', 2, 36, 23, 43, 12, 25, 29),             ('2016-01-03','2016002', 34, 35, 17, 49, 24, 30, 16),             ('2016-01-05','2016003', 1, 35, 12, 49, 49, 26, 34),             ('2016-01-08','2016004', 6, 35, 10, 40, 4, 23, 2),             ('2016-01-10','2016005', 14, 35, 27, 40, 4, 12, 45),             ('2016-01-12','2016006', 33, 10, 13, 21, 27, 22, 17),             ('2016-01-15','2016007', 20, 35, 17, 49, 5, 29, 28),]c.executemany ('INSERT into Marksix (dt,period,p1,p2,p3,p4,p5,p6,t7) VALUES (?,?,?,?,?,?,?,?,?)', purchases)
for row in c.execute ('SELECT * from Marksix'): print(row) 

# ==============================================================

#method One: Use datetime types explicitlyt = datetime.datetime.strptime ('2016-1-5','%y-%m-%d') DT=(Datetime.date (T.year, T.month, T.day),) forRowinchC.execute ('SELECT * from marksix WHERE DT =?', DT):Print(Row)#mode two: Directly using str typeDT = ('2016-01-05', ) forRowinchC.execute ('SELECT * from marksix WHERE DT =?', DT):Print(Row)

# for row in C.execute (' SELECT * from marksix WHERE dt between:begin and:end; ', {' Begin ': ' 2016-01-03 ', ' End ': ' 2016-0 1-11 '}): for in C.execute ('SELECT * from marksix WHERE dt between? and?; ', ('2016-01-03'2016-01-11')):     Print (ROW)

Two, another way to use the time type data (according to official documents）

ImportSqlite3Importdatetime#Adapterdefadapt_date (date):returnDatetime.datetime.strftime ('%y/%m/%d', date)#return Date.strftime ('%y/%m/%d '). Encode (' ASCII ')    #return Date.strftime ('%y/%m/%d '). Encode ()#Convertersdefconvert_date (String):returnDatetime.datetime.strptime (String.decode (),'%y/%m/%d')#Registering AdaptersSqlite3.register_adapter (Datetime.datetime, adapt_date)#Registering ConvertersSqlite3.register_converter ("Date", Convert_date)#Note: Detect_types=sqlite3. Parse_decltypescon = Sqlite3.connect (": Memory:", detect_types=Sqlite3. Parse_decltypes) C=con.cursor ()#Create TableC.execute (" "CREATE TABLE marksix (dt date, period text, p1 int, p2 int, p3 int, P4 int, p5 int, P6 int, T7 int)" ")#Larger example that inserts many records at a timepurchases = [('2016/1/1','2016001', 2, 36, 23, 43, 12, 25, 29),             ('2016/1/3','2016002', 34, 35, 17, 49, 24, 30, 16),             ('2016/1/5','2016003', 1, 35, 12, 49, 49, 26, 34),             ('2016/1/8','2016004', 6, 35, 10, 40, 4, 23, 2),             ('2016/1/10','2016005', 14, 35, 27, 40, 4, 12, 45),             ('2016/1/12','2016006', 33, 10, 13, 21, 27, 22, 17),             ('2016/1/15','2016007', 20, 35, 17, 49, 5, 29, 28),]c.executemany ('INSERT into Marksix VALUES (?,?,?,?,?,?,?,?,?)', purchases)#Save (Commit) The changesCon.commit () forRowinchC.execute ('SELECT * from Marksix'):    Print(Row)# ==============================================================#record showing date equal to 2016/1/3T = ('2016/1/3',) C.execute ('SELECT * from marksix WHERE dt =?', T)Print(C.fetchone ())#seemingly do not support between operation, do not know why!  forRowinchC.execute ('SELECT * from marksix WHERE dt between? and?;', ('2016/1/3','2016/1/11')):    Print(ROW)

Sqlite3 Date Data type