本文實(shí)例講述了Python SQLite3數(shù)據(jù)庫日期與時(shí)間常見函數(shù)。分享給大家供大家參考，具體如下：
|
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 日期 & 時(shí)間# ====================================================================================print('='*30)print('SQLite 日期 & 時(shí)間')print('='*30)# 計(jì)算當(dāng)前日期c.execute("SELECT date('now')")print(c.fetchone())# 計(jì)算當(dāng)前月份的最后一天：c.execute("SELECT date('now','start of month','+1 month','-1 day');")print(c.fetchone())# 計(jì)算給定 UNIX 時(shí)間戳 1092941466 的日期和時(shí)間：c.execute("SELECT datetime(1092941466, 'unixepoch');")print(c.fetchone())# 計(jì)算給定 UNIX 時(shí)間戳 1092941466 相對本地時(shí)區(qū)的日期和時(shí)間：c.execute("SELECT datetime(1092941466, 'unixepoch', 'localtime');")print(c.fetchone())# 計(jì)算當(dāng)前的 UNIX 時(shí)間戳：c.execute("SELECT datetime(1092941466, 'unixepoch', 'localtime');")print(c.fetchone())# 計(jì)算美國"獨(dú)立宣言"簽署以來的天數(shù)：c.execute("SELECT julianday('now') - julianday('1776-07-04');")print(c.fetchone())# 計(jì)算從 2004 年某一特定時(shí)刻以來的秒數(shù)：c.execute("SELECT strftime('%s','now') - strftime('%s','2004-01-01 02:34:56');")print(c.fetchone())# 計(jì)算當(dāng)年 10 月的第一個(gè)星期二的日期：c.execute("SELECT date('now','start of year','+9 months','weekday 2');")print(c.fetchone())# 計(jì)算從 UNIX 紀(jì)元算起的以秒為單位的時(shí)間（類似 strftime('%s','now') ，不同的是這里有包括小數(shù)部分）：c.execute("SELECT (julianday('now') - 2440587.5)*86400.0;")print(c.fetchone())# 在 UTC 與本地時(shí)間值之間進(jìn)行轉(zhuǎn)換，當(dāng)格式化日期時(shí)，使用 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 常用函數(shù)# ====================================================================================print('='*30)print('SQLite 常用函數(shù)')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()# 返回?cái)?shù)據(jù)庫表最后 n 行記錄# 先計(jì)算一個(gè)數(shù)據(jù)庫表中的行數(shù)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)# 計(jì)算一個(gè)數(shù)據(jù)庫表中的行數(shù)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())# 計(jì)算某列的平均值c.execute("SELECT avg(salary) FROM COMPANY;")print(c.fetchone())# 為一個(gè)數(shù)值列計(jì)算總和c.execute("SELECT sum(salary) FROM COMPANY;")print(c.fetchone())# 返回一個(gè)介于 -9223372036854775808 和 +9223372036854775807 之間的偽隨機(jī)整數(shù)c.execute("SELECT random() AS Random;")print(c.fetchone())# 返回?cái)?shù)值參數(shù)的絕對值c.execute("SELECT abs(5), abs(-15), abs(NULL), abs(0), abs('ABC');")print(c.fetchone())# 把字符串轉(zhuǎn)換為大寫字母c.execute("SELECT upper(name) FROM COMPANY;")print(c.fetchone())# 把字符串轉(zhuǎn)換為小寫字母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程序設(shè)計(jì)有所幫助。
原文鏈接：http://www.cnblogs.com/hhh5460/p/5206001.html
