-
Notifications
You must be signed in to change notification settings - Fork 0
/
sqlite3-in-python.html
295 lines (226 loc) · 18.8 KB
/
sqlite3-in-python.html
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
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
<!DOCTYPE html>
<html lang="zh">
<head>
<meta charset="utf-8" />
<meta http-equiv="X-UA-Compatible" content="IE=edge" />
<meta name="HandheldFriendly" content="True" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<meta name="robots" content="index, follow" />
<link href="https://fonts.googleapis.com/css2?family=Source+Code+Pro:ital,wght@0,400;0,700;1,400&family=Source+Sans+Pro:ital,wght@0,300;0,400;0,700;1,400&display=swap" rel="stylesheet">
<link rel="stylesheet" type="text/css" href="https://blog.tonychow.me/theme/stylesheet/style.min.css">
<link id="pygments-light-theme" rel="stylesheet" type="text/css"
href="https://blog.tonychow.me/theme/pygments/colorful.min.css">
<link rel="stylesheet" type="text/css" href="https://blog.tonychow.me/theme/font-awesome/css/fontawesome.css">
<link rel="stylesheet" type="text/css" href="https://blog.tonychow.me/theme/font-awesome/css/brands.css">
<link rel="stylesheet" type="text/css" href="https://blog.tonychow.me/theme/font-awesome/css/solid.css">
<link href="https://blog.tonychow.me/feeds/all.atom.xml" type="application/atom+xml" rel="alternate" title="Tonychow's Blog Atom">
<!-- Chrome, Firefox OS and Opera -->
<meta name="theme-color" content="#333333">
<!-- Windows Phone -->
<meta name="msapplication-navbutton-color" content="#333333">
<!-- iOS Safari -->
<meta name="apple-mobile-web-app-capable" content="yes">
<meta name="apple-mobile-web-app-status-bar-style" content="black-translucent">
<!-- Microsoft EDGE -->
<meta name="msapplication-TileColor" content="#333333">
<meta name="author" content="tonychow" />
<meta name="description" content="" />
<meta name="keywords" content="python, sqlite3">
<meta property="og:site_name" content="Tonychow's Blog"/>
<meta property="og:title" content="Python 中 sqlite3 模块使用小记"/>
<meta property="og:description" content=""/>
<meta property="og:locale" content="en_US"/>
<meta property="og:url" content="https://blog.tonychow.me/sqlite3-in-python.html"/>
<meta property="og:type" content="article"/>
<meta property="article:published_time" content="2013-05-12 00:00:00+08:00"/>
<meta property="article:modified_time" content=""/>
<meta property="article:author" content="https://blog.tonychow.me/author/tonychow.html">
<meta property="article:section" content="python"/>
<meta property="article:tag" content="python"/>
<meta property="article:tag" content="sqlite3"/>
<meta property="og:image" content="/images/avatar.jpg">
<title>Tonychow's Blog – Python 中 sqlite3 模块使用小记</title>
</head>
<body class="light-theme">
<aside>
<div>
<a href="https://blog.tonychow.me/">
<img src="/images/avatar.jpg" alt="" title="">
</a>
<h1>
<a href="https://blog.tonychow.me/"></a>
</h1>
<p>Go/Python backend developer</p>
<ul class="social">
<li>
<a class="sc-github" href="https://github.com/chow1937" target="_blank">
<i class="fab fa-github"></i>
</a>
</li>
</ul>
</div>
</aside>
<main>
<nav>
<a href="https://blog.tonychow.me/">Home</a>
<a href="/archives.html">Archives</a>
<a href="/categories.html">Categories</a>
<a href="/tags.html">Tags</a>
<a href="https://blog.tonychow.me/feeds/all.atom.xml">Atom</a>
</nav>
<article class="single">
<header>
<h1 id="sqlite3-in-python">Python 中 sqlite3 模块使用小记</h1>
<p>
Posted on 日 12 五月 2013 in <a href="https://blog.tonychow.me/category/python.html">python</a>
• 2 min read
</p>
</header>
<div>
<h3>前记</h3>
<p>Python 的标准库中包含了对 sqlite 这个轻巧的数据库的支持模块，也就是 sqlite3 模块。sqlite 数据库的好处我就不多说了，小型而强大，适合很多小型或者中型的数据库应用。最近在使用 sqlite3 模块遇到一些问题，解决了，顺便就记下来。</p>
<h3>问题</h3>
<p>sqlite3 模块的使用很简单，如下这段测试代码，创建一个 person 数据表然后进行一次数据库查询操作。</p>
<div class="highlight"><pre><span></span><code><span class="ch">#!/usr/bin/env pypthon</span>
<span class="c1">#_*_ coding: utf-8 _*_</span>
<span class="kn">import</span> <span class="nn">sqlite3</span>
<span class="n">SCHEMA</span> <span class="o">=</span> <span class="s2">"""</span>
<span class="s2"> CREATE TABLE person (</span>
<span class="s2"> p_id int,</span>
<span class="s2"> p_name text</span>
<span class="s2"> )</span>
<span class="s2"> """</span>
<span class="k">def</span> <span class="nf">init</span><span class="p">():</span>
<span class="n">data</span> <span class="o">=</span> <span class="p">[(</span><span class="mi">1</span><span class="p">,</span> <span class="s1">'tony'</span><span class="p">),</span> <span class="p">(</span><span class="mi">1</span><span class="p">,</span> <span class="s1">'jack'</span><span class="p">)]</span>
<span class="n">conn</span> <span class="o">=</span> <span class="n">sqlite3</span><span class="o">.</span><span class="n">connect</span><span class="p">(</span><span class="s1">':memory:'</span><span class="p">)</span>
<span class="n">c</span> <span class="o">=</span> <span class="n">conn</span><span class="o">.</span><span class="n">cursor</span><span class="p">()</span>
<span class="k">try</span><span class="p">:</span>
<span class="n">c</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">SCHEMA</span><span class="p">)</span>
<span class="k">for</span> <span class="n">person</span> <span class="ow">in</span> <span class="n">data</span><span class="p">:</span>
<span class="n">c</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s1">'insert into person values(?, ?)'</span><span class="p">,</span> <span class="n">person</span><span class="p">)</span>
<span class="n">conn</span><span class="o">.</span><span class="n">commit</span><span class="p">()</span>
<span class="k">except</span> <span class="n">sqlite3</span><span class="o">.</span><span class="n">Error</span> <span class="k">as</span> <span class="n">e</span><span class="p">:</span>
<span class="nb">print</span> <span class="s1">'error!'</span><span class="p">,</span> <span class="n">e</span><span class="o">.</span><span class="n">args</span><span class="p">[</span><span class="mi">0</span><span class="p">]</span>
<span class="k">return</span> <span class="n">conn</span>
<span class="k">if</span> <span class="vm">__name__</span> <span class="o">==</span><span class="s1">'__main__'</span><span class="p">:</span>
<span class="n">conn</span> <span class="o">=</span> <span class="n">init</span><span class="p">()</span>
<span class="n">c</span> <span class="o">=</span> <span class="n">conn</span><span class="o">.</span><span class="n">cursor</span><span class="p">()</span>
<span class="c1">#Do a query.</span>
<span class="n">c</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s1">'select * from person where p_name = ?'</span><span class="p">,</span> <span class="s1">'tony'</span><span class="p">)</span>
<span class="n">person</span> <span class="o">=</span> <span class="n">c</span><span class="o">.</span><span class="n">fetchone</span><span class="p">()</span>
<span class="nb">print</span> <span class="n">person</span>
</code></pre></div>
<p>运行这段代码，抛出了个异常，如下提示：</p>
<div class="highlight"><pre><span></span><code>Traceback <span class="o">(</span>most recent call last<span class="o">)</span>:
File <span class="s2">"sqlite3_test.py"</span>, line <span class="m">32</span>, <span class="k">in</span> <module>
c.execute<span class="o">(</span><span class="s1">'select * from person where p_name = ?'</span>, <span class="s1">'tony'</span><span class="o">)</span>
sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses <span class="m">1</span>, and there are <span class="m">4</span> supplied.
</code></pre></div>
<p>很莫名奇妙是不？明明我提供的占位符?绑定只有一个字符串参数，可是却说我提供了四个。再看仔细点，说提供了四个，正好字符串 'tony' 是四个字符。</p>
<h3>解决</h3>
<p>翻了翻文档，发现也给出了一个占位符查询的例子如下：</p>
<div class="highlight"><pre><span></span><code><span class="n">t</span> <span class="o">=</span> <span class="p">(</span><span class="err">’</span><span class="n">RHAT</span><span class="err">’</span><span class="p">,)</span>
<span class="n">c</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="err">’</span><span class="n">SELECT</span> <span class="o">*</span> <span class="n">FROM</span> <span class="n">stocks</span> <span class="n">WHERE</span> <span class="n">symbol</span><span class="o">=</span><span class="err">?’</span><span class="p">,</span> <span class="n">t</span><span class="p">)</span>
</code></pre></div>
<p>所以将字符参数放到元组中就可以了，修改如下：</p>
<div class="highlight"><pre><span></span><code><span class="n">c</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s1">'select * from person where p_name = ?'</span><span class="p">,</span> <span class="p">(</span><span class="s1">'tony'</span><span class="p">))</span>
</code></pre></div>
<p>结果依旧是抛出了同样的异常。再仔细看下，漏了个','，于是加上：</p>
<div class="highlight"><pre><span></span><code><span class="n">c</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s1">'select * from person where p_name = ?'</span><span class="p">,</span> <span class="p">(</span><span class="s1">'tony'</span><span class="p">,))</span>
</code></pre></div>
<p>这次终于得到最终的结果了,其中的字符为 unicode 类型：</p>
<div class="highlight"><pre><span></span><code><span class="p">(</span><span class="mi">1</span><span class="p">,</span> <span class="sa">u</span><span class="s1">'tony'</span><span class="p">)</span>
</code></pre></div>
<h3>原因</h3>
<p>但是为什么？ Python 中的 sqlite3 模块提供了对 sqlite 数据操作的 API，执行查询的函数是在 sqlite3 模块源码中定义的，很明显想要知道为啥，最好的方式是去看 sqlite3 模块的源码。我手上的 Python 源码是 Python-2.7.4，在源码 Python-2.7.4/Modules/_sqlite/cursor.c 的函数 PyObject<em> _pysqlite_query_execute(pysqlite_Cursor</em> self, int multiple, PyObject* args) 中 497-529 行：</p>
<div class="highlight"><pre><span></span><code><span class="p">...</span>
<span class="cm">/* execute() */</span>
<span class="k">if</span> <span class="p">(</span><span class="o">!</span><span class="n">PyArg_ParseTuple</span><span class="p">(</span><span class="n">args</span><span class="p">,</span> <span class="s">"O|O"</span><span class="p">,</span> <span class="o">&</span><span class="n">operation</span><span class="p">,</span> <span class="o">&</span><span class="n">second_argument</span><span class="p">))</span> <span class="p">{</span>
<span class="k">goto</span> <span class="n">error</span><span class="p">;</span>
<span class="p">}</span>
<span class="k">if</span> <span class="p">(</span><span class="o">!</span><span class="n">PyString_Check</span><span class="p">(</span><span class="n">operation</span><span class="p">)</span> <span class="o">&&</span> <span class="o">!</span><span class="n">PyUnicode_Check</span><span class="p">(</span><span class="n">operation</span><span class="p">))</span> <span class="p">{</span>
<span class="n">PyErr_SetString</span><span class="p">(</span><span class="n">PyExc_ValueError</span><span class="p">,</span> <span class="s">"operation parameter must be str or unicode"</span><span class="p">);</span>
<span class="k">goto</span> <span class="n">error</span><span class="p">;</span>
<span class="p">}</span>
<span class="n">parameters_list</span> <span class="o">=</span> <span class="n">PyList_New</span><span class="p">(</span><span class="mi">0</span><span class="p">);</span>
<span class="k">if</span> <span class="p">(</span><span class="o">!</span><span class="n">parameters_list</span><span class="p">)</span> <span class="p">{</span>
<span class="k">goto</span> <span class="n">error</span><span class="p">;</span>
<span class="p">}</span>
<span class="k">if</span> <span class="p">(</span><span class="n">second_argument</span> <span class="o">==</span> <span class="nb">NULL</span><span class="p">)</span> <span class="p">{</span>
<span class="n">second_argument</span> <span class="o">=</span> <span class="n">PyTuple_New</span><span class="p">(</span><span class="mi">0</span><span class="p">);</span>
<span class="k">if</span> <span class="p">(</span><span class="o">!</span><span class="n">second_argument</span><span class="p">)</span> <span class="p">{</span>
<span class="k">goto</span> <span class="n">error</span><span class="p">;</span>
<span class="p">}</span>
<span class="p">}</span> <span class="k">else</span> <span class="p">{</span>
<span class="n">Py_INCREF</span><span class="p">(</span><span class="n">second_argument</span><span class="p">);</span>
<span class="p">}</span>
<span class="k">if</span> <span class="p">(</span><span class="n">PyList_Append</span><span class="p">(</span><span class="n">parameters_list</span><span class="p">,</span> <span class="n">second_argument</span><span class="p">)</span> <span class="o">!=</span> <span class="mi">0</span><span class="p">)</span> <span class="p">{</span>
<span class="n">Py_DECREF</span><span class="p">(</span><span class="n">second_argument</span><span class="p">);</span>
<span class="k">goto</span> <span class="n">error</span><span class="p">;</span>
<span class="p">}</span>
<span class="n">Py_DECREF</span><span class="p">(</span><span class="n">second_argument</span><span class="p">);</span>
<span class="n">parameters_iter</span> <span class="o">=</span> <span class="n">PyObject_GetIter</span><span class="p">(</span><span class="n">parameters_list</span><span class="p">);</span>
<span class="k">if</span> <span class="p">(</span><span class="o">!</span><span class="n">parameters_iter</span><span class="p">)</span> <span class="p">{</span>
<span class="k">goto</span> <span class="n">error</span><span class="p">;</span>
<span class="p">}</span>
<span class="p">...</span>
</code></pre></div>
<p>从这段源码中可以看到这段代码将参数 args 解析成为 Python 的一个元组作为 parameters_list ，然后最这个得到的元组进行 iter 操作，不断地读取这个元组的元素作为参数，而 Python 中对一个字符串进行 parse tuple 会怎样？我觉得 PyArg_ParseTuple 这个函数的操作和以下代码会是类似的：</p>
<div class="highlight"><pre><span></span><code><span class="o">...</span>
<span class="o">>>></span> <span class="nb">tuple</span><span class="p">(</span><span class="s1">'test'</span><span class="p">)</span>
<span class="p">(</span><span class="s1">'t'</span><span class="p">,</span> <span class="s1">'e'</span><span class="p">,</span> <span class="s1">'s'</span><span class="p">,</span> <span class="s1">'t'</span><span class="p">)</span>
<span class="o">...</span>
</code></pre></div>
<p>所以现在我们可以看到我们的答案了，sqlite3 模块中，cursor 对象的 execute 方法会接受两个参数，第二个参数会被 PyArg_ParseTuple 函数转化成为 Python中 的 tuple。而对一个字符进行 tuple parse 导致的结果是将这个字符串的每个字符作为 tuple 的一个元素，所以上面抛出错误的时候提示的提供了四个所以错误也可以理解了。那为什么'('tony')'同样是错误呢？如下：</p>
<div class="highlight"><pre><span></span><code><span class="o">>>></span> <span class="nb">type</span><span class="p">((</span><span class="s1">'tony'</span><span class="p">))</span>
<span class="o"><</span><span class="nb">type</span> <span class="s1">'str'</span><span class="o">></span>
<span class="o">>>></span> <span class="nb">type</span><span class="p">((</span><span class="s1">'tony'</span><span class="p">,))</span>
<span class="o"><</span><span class="nb">type</span> <span class="s1">'tuple'</span><span class="o">></span>
</code></pre></div>
<p>很明显，('tony')是一个 str 也就是一个字符串，相当于是 'tony'，而 ('tony',) 才是一个单元素的 tuple 。同样，因为：</p>
<div class="highlight"><pre><span></span><code><span class="o">>>></span> <span class="nb">tuple</span><span class="p">([</span><span class="s1">'tony'</span><span class="p">])</span>
<span class="p">(</span><span class="s1">'tony'</span><span class="p">,)</span>
</code></pre></div>
<p>所以如果那一行查询执行改为：</p>
<div class="highlight"><pre><span></span><code><span class="n">c</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s1">'select * from person where p_name = ?'</span><span class="p">,</span> <span class="p">[</span><span class="s1">'tony'</span><span class="p">])</span>
</code></pre></div>
<p>同样也是可以执行成功的。</p>
</div>
<div class="tag-cloud">
<p>
<a href="https://blog.tonychow.me/tag/python.html">python</a>
<a href="https://blog.tonychow.me/tag/sqlite3.html">sqlite3</a>
</p>
</div>
</article>
<footer>
<p>
© 2017 - This work is licensed under a <a rel="license" href="http://creativecommons.org/licenses/by-sa/4.0/deed.en_US" target="_blank">Creative Commons Attribution-ShareAlike</a>
</p>
<p>
Built with <a href="http://getpelican.com" target="_blank">Pelican</a> using <a href="http://bit.ly/flex-pelican" target="_blank">Flex</a> theme
</p><p>
<a rel="license"
href="http://creativecommons.org/licenses/by-sa/4.0/"
target="_blank">
<img alt="Creative Commons License"
title="Creative Commons License"
style="border-width:0"
src="https://i.creativecommons.org/l/by-sa/4.0/80x15.png"
width="80"
height="15"/>
</a>
</p> </footer>
</main>
<script type="application/ld+json">
{
"@context" : "http://schema.org",
"@type" : "Blog",
"name": " Tonychow's Blog ",
"url" : "https://blog.tonychow.me",
"image": "/images/avatar.jpg",
"description": "tonychow's Thoughts and Writings"
}
</script>
</body>
</html>