~dkuhlman/python-training-materials/Materials

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
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
<?xml version="1.0" encoding="utf-8" ?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<meta name="generator" content="Docutils 0.12: http://docutils.sourceforge.net/" />
<title>Python Database API</title>
<style type="text/css">

/* css */

body {
  font: 90% 'Lucida Grande', Verdana, Geneva, Lucida, Arial, Helvetica, sans-serif;
  background: #ffffff;
  color: black;
  margin: 2em;
  padding: 2em;
}

a[href] {
  color: #436976;
  background-color: transparent;
}

a.toc-backref {
  text-decoration: none;
}

h1 a[href] {
  text-decoration: none;
  color: #fcb100;
  background-color: transparent;
}

a.strong {
  font-weight: bold;
}

img {
  margin: 0;
  border: 0;
}

p {
  margin: 0.5em 0 1em 0;
  line-height: 1.5em;
}
p a {
  text-decoration: underline;
}
p a:visited {
  color: purple;
  background-color: transparent;
}
p a:active {
  color: red;
  background-color: transparent;
}
a:hover {
  text-decoration: none;
}
p img {
  border: 0;
  margin: 0;
}

h1, h2, h3, h4, h5, h6 {
  color: #003a6b;
  background-color: transparent;
  font: 100% 'Lucida Grande', Verdana, Geneva, Lucida, Arial, Helvetica, sans-serif;
  margin: 0;
  padding-top: 0.5em;
}

h1 {
  font-size: 160%;
  margin-bottom: 0.5em;
  border-bottom: 1px solid #fcb100;
}
h2 {
  font-size: 140%;
  margin-bottom: 0.5em;
  border-bottom: 1px solid #aaa;
}
h3 {
  font-size: 130%;
  margin-bottom: 0.5em;
  text-decoration: underline;
}
h4 {
  font-size: 110%;
  font-weight: bold;
}
h5 {
  font-size: 100%;
  font-weight: bold;
}
h6 {
  font-size: 80%;
  font-weight: bold;
}

ul a, ol a {
  text-decoration: underline;
}

dt {
  font-weight: bold;
}
dt a {
  text-decoration: none;
}

dd {
  line-height: 1.5em;
  margin-bottom: 1em;
}

legend {
  background: #ffffff;
  padding: 0.5em;
}

form {
  margin: 0;
}


dl.form {
  margin: 0;
  padding: 1em;
}

dl.form dt {
  width: 30%;
  float: left;
  margin: 0;
  padding: 0 0.5em 0.5em 0;
  text-align: right;
}

input {
  font: 100% 'Lucida Grande', Verdana, Geneva, Lucida, Arial, Helvetica, sans-serif;
  color: black;
  background-color: white;
  vertical-align: middle;
}

abbr, acronym, .explain {
  color: black;
  background-color: transparent;
}

q, blockquote {
}

code, pre {
  font-family: monospace;
  font-size: 1.2em;
  display: block;
  padding: 10px;
  border: 1px solid #838183;
  background-color: #eee;
  color: #000;
  overflow: auto;
  margin: 0.5em 1em;
}

div.admonition, div.attention, div.caution, div.danger, div.error,
div.hint, div.important, div.note, div.tip, div.warning {
  margin: 2em ;
  border: medium outset ;
  padding: 1em }

div.admonition p.admonition-title, div.hint p.admonition-title,
div.important p.admonition-title, div.note p.admonition-title,
div.tip p.admonition-title {
  font-weight: bold ;
  font-family: sans-serif }

div.attention p.admonition-title, div.caution p.admonition-title,
div.danger p.admonition-title, div.error p.admonition-title,
div.warning p.admonition-title {
  color: red ;
  font-weight: bold ;
  font-family: sans-serif }

tt.docutils {
  background-color: #dddddd;
}

ul.auto-toc {
  list-style-type: none }

</style>
</head>
<body>
<div class="document" id="python-database-api">
<h1 class="title">Python Database API</h1>

<div class="section" id="querying-a-database">
<h1>Querying a database</h1>
<p>Use the <tt class="docutils literal">sqlite3</tt> module in the Python standard library to
complete the following exercises.</p>
<p>Write a script containing a function that displays information about a
table in a database: information about the table and all the rows in a
database.</p>
<p>Apply your script to the database file <tt class="docutils literal">data01.db</tt> in this
directory.</p>
<ul class="simple">
<li>It contains a table named &quot;plants&quot;.</li>
<li>This table has the following fields: &quot;p_name&quot;, &quot;p_descrip&quot;, and
&quot;p_rating&quot;.</li>
</ul>
<p>Here are some SQL queries that you may find helpful:</p>
<pre class="literal-block">
sql = 'select * from plants'
sql = 'select * from plants where p_name = banana'
sql = 'insert into plants values (
sql = 'insert into plants values (&quot;tangerine&quot;, &quot;sweet and juicy&quot;, &quot;9&quot;)
sql = 'delete from plants where p_name = &quot;peach&quot;'
sql = 'update plants set p_descrip = &quot;most amazing fruit&quot; where p_name = &quot;banana&quot;'
</pre>
<ol class="arabic">
<li><p class="first">Open/create a connection to the database.</p>
</li>
<li><p class="first">Execute a query on the database -- example:</p>
<pre class="literal-block">
select * from plants
</pre>
</li>
<li><p class="first">Display the descriptions of the columns returned by the query.</p>
</li>
<li><p class="first">Display the number of rows returned as a result of the query.</p>
</li>
<li><p class="first">Display the &quot;raw&quot; results, that is each of the rows/tuples returned
by the query.</p>
</li>
<li><p class="first">Display each field (that is, each column) as follows:</p>
<ul class="simple">
<li>Name -- left justified in 12 spaces</li>
<li>Description -- left justified in 30 spaces</li>
<li>Rating -- right justified in 4 spaces</li>
</ul>
</li>
</ol>
<p>What you will learn:</p>
<ul class="simple">
<li>How to install a Python module/package.</li>
<li>How to use the Python database API.</li>
<li>How to create a connection to a database.</li>
<li>How to execute a query on a database.</li>
<li>How to process the results of a query.</li>
</ul>
</div>
<hr class="docutils" />
<div class="section" id="delete-a-row-from-a-database">
<h1>Delete a row from a database</h1>
<p>Write a function that deletes a row from the plants table in the
sample database.</p>
<p>First check to see whether the row exists in the database.  Print an
error message if it does not.</p>
<p>Modify your script so that it processes the following command lines:</p>
<pre class="literal-block">
python db_actions.py show
python db_actions.py delete plant_name
</pre>
<p>What you will learn:</p>
<ul class="simple">
<li>How to delete a row from a table in a database.</li>
<li>How to check for the existence of a row (that matches a query) in a
table in a database.</li>
<li>How to process arguments from the command line.</li>
</ul>
</div>
<div class="section" id="add-a-row-to-a-database">
<h1>Add a row to a database</h1>
<p>Write a function that adds a row to the plants table in the sample
database.</p>
<p>Modify your script so that it processes the following additional
command line:</p>
<pre class="literal-block">
python db_actions.py add plant_name description rating
</pre>
<p>What you will learn:</p>
<ul class="simple">
<li>How to insert a row into a database.</li>
</ul>
</div>
<div class="section" id="create-a-description-row-dictionary">
<h1>Create a description-row dictionary</h1>
<p>Write a function that takes the query (result) description
(<tt class="docutils literal">cursor.description</tt>) and a row as arguments, and which creates
and returns a dictionary whose keys are the names of the columns
and whose values are the corresponding values from that row.</p>
</div>
<div class="section" id="additional-information">
<h1>Additional information</h1>
<ul class="simple">
<li><a class="reference external" href="http://wiki.python.org/moin/DatabaseProgramming">DatabaseProgramming -- Starting point for learning about using
databases from Python --
http://wiki.python.org/moin/DatabaseProgramming</a></li>
<li><a class="reference external" href="http://www.python.org/dev/peps/pep-0249/">Python Database API Specification v2.0 --
http://www.python.org/dev/peps/pep-0249/</a></li>
<li><a class="reference external" href="http://gadfly.sourceforge.net/">GadflyB5: SQL Relational Database in Python --
http://gadfly.sourceforge.net/</a></li>
<li><a class="reference external" href="http://wiki.python.org/moin/DatabaseInterfaces">DatabaseInterfaces --
http://wiki.python.org/moin/DatabaseInterfaces</a></li>
<li><a class="reference external" href="http://wiki.python.org/moin/DbApiModuleComparison">DbApiModuleComparison --
http://wiki.python.org/moin/DbApiModuleComparison</a></li>
<li><a class="reference external" href="http://wiki.python.org/moin/DbApiCheatSheet">DbApiCheatSheet
http://wiki.python.org/moin/DbApiCheatSheet</a></li>
</ul>
</div>
</div>
<div class="footer">
<hr class="footer" />
<a class="reference external" href="py_db_api.txt">View document source</a>.
Generated on: 2015-10-05 05:02 UTC.
Generated by <a class="reference external" href="http://docutils.sourceforge.net/">Docutils</a> from <a class="reference external" href="http://docutils.sourceforge.net/rst.html">reStructuredText</a> source.

</div>
</body>
</html>