一、sqlite3日期数据类型，默认用datetime解析（根据stackflow）
使用时注意三点：
1. 创建表时，字段 DT 的类型为 date
2. 插入数据时，DT字段直接为 str 类型
3. DT字段的str ，年月日必须为 xxxx-xx-xx 格式，如 2016-01-01，不能是 2016-1-1
import sqlite3
import datetime
'''sqlite3日期数据类型'''
con = sqlite3.connect(
":memory:")
c =
con.cursor()
# Create table
c.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 time
purchases = [(
'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)
# ==============================================================
# 方式一：显式使用 datetime 类型
t = datetime.datetime.strptime(
'2016-1-5',
'%Y-%m-%d')
dt =
(datetime.date(t.year, t.month, t.day), )
for row
in c.execute(
'SELECT * FROM marksix WHERE DT = ?', dt):
print(row)
# 方式二：直接使用 str 类型
dt = (
'2016-01-05', )
for row
in c.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-01-11'}):
for row
in c.execute(
'SELECT * FROM marksix WHERE dt BETWEEN ? AND ?;', (
'2016-01-03',
'2016-01-11')):
print(row)
二、另一种方式使用时间类型数据（根据官网文档）
import sqlite3
import datetime
# 适配器
def adapt_date(date):
return datetime.datetime.strftime(
'%Y/%m/%d', date)
#return date.strftime('%Y/%m/%d').encode('ascii')
#return date.strftime('%Y/%m/%d').encode()
# 转换器
def convert_date(string):
return datetime.datetime.strptime(string.decode(),
'%Y/%m/%d')
# 注册适配器
sqlite3.register_adapter(datetime.datetime, adapt_date)
# 注册转换器
sqlite3.register_converter(
"date", convert_date)
# 注意：detect_types=sqlite3.PARSE_DECLTYPES
con = sqlite3.connect(
":memory:", detect_types=
sqlite3.PARSE_DECLTYPES)
c =
con.cursor()
# Create table
c.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 time
purchases = [(
'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 changes
con.commit()
for row
in c.execute(
'SELECT * FROM marksix'):
print(row)
# ==============================================================
# 显示日期等于2016/1/3的记录
t = (
'2016/1/3',)
c.execute('SELECT * FROM marksix WHERE dt = ?', t)
print(c.fetchone())
# 貌似不支持between运算，不知原因！
for row
in c.execute(
'SELECT * FROM marksix WHERE dt BETWEEN ? AND ?;', (
'2016/1/3',
'2016/1/11')):
print(row)
三、还有一种日期时间类型：timestamp(时间戳)
import datetime
# Converting SQLite values to custom Python types
# Default adapters and converters for datetime and timestamp
con = sqlite3.connect(
":memory:", detect_types=sqlite3.PARSE_DECLTYPES|
sqlite3.PARSE_COLNAMES)
cur =
con.cursor()
cur.execute('''create table test(
d date,
ts timestamp
)''')
# 插入datetime类型
today =
datetime.date.today()
now =
datetime.datetime.now()
day1 = datetime.timedelta(days=1
)
data = [(today-day1, now-
day1),
(today, now),
(today+day1, now+
day1)]
cur.executemany("insert into test(d, ts) values (?, ?)", data)
# 插入 str 类型
data = [(
'2016-01-22',
'2016-01-22 08:45:50'),
('2016-02-22',
'2016-02-22 08:45:50'),
('2016-03-22',
'2016-03-22 08:45:50')]
cur.executemany("insert into test(d, ts) values (?, ?)", data)
for row
in cur.execute(
"select * from test"):
print(row)
#=================================================================
#begin = datetime.date(2016, 2, 22)
#end = datetime.date(2016, 2, 23)
begin =
'2016-02-22'
end =
'2016-02-23'
for row
in cur.execute(
"select * from test where d BETWEEN ? AND ?", (begin, end)):
print(row)
begin = datetime.datetime(2016, 2, 22, 8, 45, 50
, 0)
end = datetime.datetime(2016, 2, 23, 20, 10, 47, 12345
)
#begin = '2016-02-22 08:45:50'
#end = '2016-02-23 20:10:47'
for row
in cur.execute(
"select * from test where ts BETWEEN ? AND ?", (begin, end)):
print(row)
转载于:https://www.cnblogs.com/hhh5460/p/5205982.html
