python操作sqlite3

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
SQLite数据库是一款非常小巧的嵌入式开源数据库软件,也就是说
没有独立的维护进程,所有的维护都来自于程序本身。
在python中,使用sqlite3创建数据库的连接,当我们指定的数据库文件不存在的时候
连接对象会自动创建数据库文件;如果数据库文件已经存在,则连接对象不会再创建
数据库文件,而是直接打开该数据库文件。
连接对象可以是硬盘上面的数据库文件,也可以是建立在内存中的,在内存中的数据库
执行完任何操作后,都不需要提交事务的(commit)

创建在硬盘上面: conn = sqlite3.connect('c:\\test\\test.db')
创建在内存上面: conn = sqlite3.connect('"memory:')

下面我们一硬盘上面创建数据库文件为例来具体说明:
conn = sqlite3.connect('c:\\test\\hongten.db')
其中conn对象是数据库链接对象,而对于数据库链接对象来说,具有以下操作:

commit() --事务提交
rollback() --事务回滚
close() --关闭一个数据库链接
cursor() --创建一个游标

cu = conn.cursor()
这样我们就创建了一个游标对象:cu
在sqlite3中,所有sql语句的执行都要在游标对象的参与下完成
对于游标对象cu,具有以下具体操作:

execute() --执行一条sql语句
executemany() --执行多条sql语句
close() --游标关闭
fetchone() --从结果中取出一条记录
fetchmany() --从结果中取出多条记录
fetchall() --从结果中取出所有记录
scroll() --游标滚动

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
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Date : 2019-03-14 16:10:24
# @Author : Jeff.Sui (215687736@qq.com)
# @Link : http://example.org
# @Version : $Id$
import sqlite3

con = sqlite3.connect(":memory:")

c = con.cursor()

# Create table
c.execute('''CREATE TABLE stocks
(date text, trans text, symbol text, qty real, price real)''')

# Insert a row of data
c.execute("INSERT INTO stocks VALUES (?,?,?,?,?)", ('2006-03-27','BUY','RHAT',100,60.14))


# Larger example that inserts many records at a time
purchases = [('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 changes
con.commit()


# Do this instead
t = ('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 table
c.execute('''CREATE TABLE COMPANY
(ID integer, NAME text, AGE integer, ADDRESS text, SALARY real)''')

# Larger example that inserts many records at a time
purchases = [(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 changes
con.commit()


# 返回数据库表最后 n 行记录
# 先计算一个数据库表中的行数
c.execute("SELECT count(*) FROM COMPANY;")
last = c.fetchone()[0]
n = 5
c.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())
坚持原创技术分享,您的支持将鼓励我继续创作!