本文实例讲述了Python SQLite3数据库日期与时间常见函数。分享给大家供大家参考，具体如下：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
|
import sqlite3#con = sqlite3.connect('example.db')con = sqlite3.connect(":memory:")c = con.cursor()# Create tablec.execute('''CREATE TABLE stocks (date text, trans text, symbol text, qty real, price real)''')# Insert a row of datac.execute("INSERT INTO stocks VALUES (?,?,?,?,?)", ('2006-03-27','BUY','RHAT',100,60.14))# Larger example that inserts many records at a timepurchases = [('2006-03-28', 'BUY', 'IBM', 1000, 45.00), ('2006-04-05', 'BUY', 'MSFT', 1000, 72.00), ('2006-04-06', 'SELL', 'IBM', 500, 53.00), ('2006-04-07', 'SELL', 'MSFT', 500, 74.00), ('2006-04-08', 'SELL', 'IBM', 500, 54.00), ('2006-04-09', 'SELL', 'MSFT', 500, 73.00), ('2006-04-10', 'SELL', 'MSFT', 500, 75.00), ('2006-04-12', 'SELL', 'IBM', 500, 55.00), ]c.executemany('INSERT INTO stocks VALUES (?,?,?,?,?)', purchases)# Save (commit) the changescon.commit()# Do this insteadt = ('RHAT',)c.execute('SELECT * FROM stocks WHERE symbol=?', t)#print(c.fetchone())#for row in c.execute('SELECT * FROM stocks ORDER BY price'):# print(row)#for row in c.execute('SELECT * FROM stocks LIMIT 5 OFFSET 0'):# print(row)for row in c.execute('SELECT * FROM stocks LIMIT 5 OFFSET 1'): print(row)#Select Top N * From# ====================================================================================# SQLite 日期 & 时间# ====================================================================================print('='*30)print('SQLite 日期 & 时间')print('='*30)# 计算当前日期c.execute("SELECT date('now')")print(c.fetchone())# 计算当前月份的最后一天：c.execute("SELECT date('now','start of month','+1 month','-1 day');")print(c.fetchone())# 计算给定 UNIX 时间戳 1092941466 的日期和时间：c.execute("SELECT datetime(1092941466, 'unixepoch');")print(c.fetchone())# 计算给定 UNIX 时间戳 1092941466 相对本地时区的日期和时间：c.execute("SELECT datetime(1092941466, 'unixepoch', 'localtime');")print(c.fetchone())# 计算当前的 UNIX 时间戳：c.execute("SELECT datetime(1092941466, 'unixepoch', 'localtime');")print(c.fetchone())# 计算美国"独立宣言"签署以来的天数：c.execute("SELECT julianday('now') - julianday('1776-07-04');")print(c.fetchone())# 计算从 2004 年某一特定时刻以来的秒数：c.execute("SELECT strftime('%s','now') - strftime('%s','2004-01-01 02:34:56');")print(c.fetchone())# 计算当年 10 月的第一个星期二的日期：c.execute("SELECT date('now','start of year','+9 months','weekday 2');")print(c.fetchone())# 计算从 UNIX 纪元算起的以秒为单位的时间（类似 strftime('%s','now') ，不同的是这里有包括小数部分）：c.execute("SELECT (julianday('now') - 2440587.5)*86400.0;")print(c.fetchone())# 在 UTC 与本地时间值之间进行转换，当格式化日期时，使用 utc 或 localtime 修饰符，如下所示：c.execute("SELECT time('12:00', 'localtime');")print(c.fetchone())#c.execute("SELECT time('12:00', 'utc');")print(c.fetchone())con.close()# ====================================================================================# SQLite 常用函数# ====================================================================================print('='*30)print('SQLite 常用函数')print('='*30)con = sqlite3.connect(":memory:")c = con.cursor()# Create tablec.execute('''CREATE TABLE COMPANY (ID integer, NAME text, AGE integer, ADDRESS text, SALARY real)''')# Larger example that inserts many records at a timepurchases = [(1,'Paul',32,'California',20000.0), (2,'Allen',25,'Texas',15000.0), (3,'Teddy',23,'Norway',20000.0), (4,'Mark',25,'Rich-Mond',65000.0), (5,'David',27,'Texas',85000.0), (6,'Kim',22,'South-Hall',45000.0), (7,'James',24,'Houston',10000.0)]c.executemany('INSERT INTO COMPANY VALUES (?,?,?,?,?)', purchases)# Save (commit) the changescon.commit()# 返回数据库表最后 n 行记录# 先计算一个数据库表中的行数c.execute("SELECT count(*) FROM COMPANY;")last = c.fetchone()[0]n = 5c.execute("SELECT * FROM COMPANY LIMIT ? OFFSET ?;", (n, last-n))for row in c: print(row)# 计算一个数据库表中的行数c.execute("SELECT count(*) FROM COMPANY;")print(c.fetchone())# 选择某列的最大值c.execute("SELECT max(salary) FROM COMPANY;")print(c.fetchone())# 选择某列的最小值c.execute("SELECT min(salary) FROM COMPANY;")print(c.fetchone())# 计算某列的平均值c.execute("SELECT avg(salary) FROM COMPANY;")print(c.fetchone())# 为一个数值列计算总和c.execute("SELECT sum(salary) FROM COMPANY;")print(c.fetchone())# 返回一个介于 -9223372036854775808 和 +9223372036854775807 之间的伪随机整数c.execute("SELECT random() AS Random;")print(c.fetchone())# 返回数值参数的绝对值c.execute("SELECT abs(5), abs(-15), abs(NULL), abs(0), abs('ABC');")print(c.fetchone())# 把字符串转换为大写字母c.execute("SELECT upper(name) FROM COMPANY;")print(c.fetchone())# 把字符串转换为小写字母c.execute("SELECT lower(name) FROM COMPANY;")print(c.fetchone())# 返回字符串的长度c.execute("SELECT name, length(name) FROM COMPANY;")print(c.fetchone())# 返回 SQLite 库的版本c.execute("SELECT sqlite_version() AS 'SQLite Version';")print(c.fetchone())#c.execute("SELECT CURRENT_TIMESTAMP;")print(c.fetchone()) |
希望本文所述对大家Python程序设计有所帮助。
原文链接：http://www.cnblogs.com/hhh5460/p/5206001.html
