本文實例講述了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
