-
Notifications
You must be signed in to change notification settings - Fork 2
Expand file tree
/
Copy pathDatabase.html
More file actions
134 lines (113 loc) · 20.7 KB
/
Copy pathDatabase.html
File metadata and controls
134 lines (113 loc) · 20.7 KB
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
<html>
<head>
<title>NodeBox | Database</title>
<meta http-equiv="content-type" content="text/html; charset=utf-8">
<meta http-equiv="imagetoolbar" content="no" />
<meta name="description" content="" />
<meta name="keywords" content="NodeBox, Database" />
<link rel="canonical" href="https://www.nodebox.net/code/Database" />
<link type="text/css" rel="stylesheet" media="screen" href="/media/css/nbar.css">
<script type="text/javascript" src="/code/js/pop.js"></script>
<script type="text/javascript" src="/code/js/confirm.js"></script>
<link href="/code/css/default.css" rel="stylesheet" type="text/css" />
<link href="/code/css/print.css" rel="stylesheet" type="text/css" media="print" />
</head>
<body id="body">
<div class="nbar">
<ol>
<li><a class="node" href="/code/Home">NodeBox 1<span class="nbar-arrow"></span></a>
<ol class="nbar-dropdown">
<li><a href="/">Homepage</a></li>
<li><a href="/node/">NodeBox 3<small>Node-based app for generative design and data visualization</small></a></li>
<li><a href="/opengl/">NodeBox OpenGL<small>Hardware-accelerated cross-platform graphics library</small></a></li>
<li><a href="/code/Home">NodeBox 1<small>Generate 2D visuals using Python code (Mac OS X only)</small></a></li>
</ol>
</li>
<li><a class="gallery" href="/gallery/">Gallery</a></li>
<li><a class="documentation" href="/code/Tutorial">Documentation</a></li>
<li><a class="forum" href="http://support.nodebox.net/discussions">Forum</a></li>
<li><a class="blog" href="/blog/">Blog</a></li>
</ol>
</div>
<div id="all">
<div id="header_and_navigation">
<div id="header">
<a href="/code/Home"><img id="header_image" src="/code/g/header-small.jpg" width="800" /></a></div>
<div id="title">
<a href="/code/Home"><h1>NodeBox</h1>
<strong><em>Create visual output with Python programming code</em></strong>
</a></div>
<div id="contextual">
<div id="languages">
</div>
<div id="search">
<form id="sf" method="get">
<input type="text" id="q" name="q" value="" />
<a href="javascript:document.getElementById('sf').submit();">GO</a>
</form>
</div>
</div>
<div id="navigation">
<div id="navigation_public">
<a href="/code/Home">Home</a>
<a href="/code/Download">Download</a>
<a href="/code/Reference">Reference</a>
<a href="/code/Tutorial">Tutorial</a>
<a href="/code/Library">Library</a>
<a href="/code/Gallery">Gallery</a>
<a href="/code/About">About</a>
</div>
</div>
</div>
<div id="content">
<div id="nodebox-link">
<a href="/code/Home"><img src="/code/g/transparent.gif" /></a>
</div>
<div class="inside">
<h3>Database</h3>
<h2>Description</h2> <p>The NodeBox Database library offers a very easy way to store data and browse through it. The commands are simple and straightforward (no knowledge of SQL is needed) but the database-capabilities limited. The library builds upon <a href="http://pysqlite.org">PySQLite</a>, and the data itself is stored in a portable SQLite database. This means the database is just another file in your project folder that you can copy and redistribute along with your NodeBox-scripts.</p> <p>If you need a viewer application for the SQLite database you can download the free <a href="http://sqlitebrowser.sourceforge.net/">SQLite Database Browser</a> and bundle it with your project. There's also a <a href="https://addons.mozilla.org/en-US/firefox/addon/5817" target="_self">Firefox extension</a>.<br /> </p> <h2>Download</h2> <table border="0"><tbody><tr><td><img src="/code/data/media/download.gif" alt="download" /></td><td><a href="https://nodebox.s3.amazonaws.com/code/data/media/database-ppc.zip" target="_self">PowerPC</a> | <a href="https://nodebox.s3.amazonaws.com/code/data/media/database-intel.zip" target="_self">Intel</a> (140KB)<br /> <i>Last updated for NodeBox 1.9.2<br /></i><i>Author: Tom De Smedt</i><br /></td></tr></tbody></table> <h2>Documentation</h2><ul><li><a href="Database.html#load" target="_self">How to get the library up and running</a></li><li><a href="Database.html#createdatabase" target="_self">Creating a database</a></li><li><a href="Database.html#createtable" target="_self">Creating tables and indexes</a></li><li><a href="Database.html#retrievetable" target="_self">Retrieving a table from the database</a> </li><li><a href="Database.html#addedit" target="_self">Adding and editing records of data in a table</a></li><li><a href="Database.html#find" target="_self">Retrieving data from a table</a></li><li><a href="Database.html#batch" target="_self">Batch transactions</a><br /></li><li><a href="Database.html#xml" target="_self">Exporting the database to XML</a></li><li><a href="Database.html#sql" target="_self">Executing raw SQL queries </a><br /></li></ul><p> </p><hr /><h2><a name="load" title="load"></a>How to get the library up and running</h2><p>Put the downloaded <i>database</i> folder in <i>~/Library/Application Support/NodeBox/</i> or in the same folder as your script so NodeBox can find the library. Then import the library in your script:<br /></p><pre class="python">database = <span style="color: #530035;">ximport</span><span style="">(</span><span style="color: #ff0080;">"database"</span><span style="">)</span></pre></pre><br /><br /> <hr /><h2><a name="createdatabase" title="createdatabase"></a>Creating a database</h2> <pre class="python">db = database.<span style="">create</span><span style="">(</span>name, overwrite=<span style="color: #530035;">True</span><span style="">)</span></pre></pre><pre class="python">db = database.<span style="">connect</span><span style="">(</span>name<span style="">)</span></pre></pre> <p>The <i>create()</i> command starts a new database file with the given <i>name</i> in the current folder (a <i>.db</i> file extension is added automatically). The <i>connect()</i> command opens an existing database file. Both commands return a <i>Database</i> object which you can use in your script to create tables or retrieve data.</p> <p> </p><hr /><h2><a name="createtable" title="createtable"></a>Creating tables and indexes</h2><p>A database is organised in tables that hold different records of data. For example, a <i>persons</i> table could store <i>name</i>, <i>address</i>, <i>age</i> and <i>email</i> information for each person in the table.</p><pre class="python">db.<span style="">create_table</span><span style="">(</span>name, fields=<span style="">[</span><span style="">]</span>, key=<span style="color: #ff0080;">"id"</span><span style="">)</span></pre></pre><pre class="python">db.<span style="">create_index</span><span style="">(</span>table, field, unique=<span style="color: #530035;">False</span>, ascending=<span style="color: #530035;">True</span><span style="">)</span></pre></pre><p>Once you have a <i>Database</i> object returned from <i>create()</i> or <i>open()</i> you can use its <i>create_table()</i> method to start new tables. You need to supply a <i>name</i> for the table and list of <i>fields</i> in the table. The optional <i>key</i> parameter defines the name of an id-field. An id stores a unique number for each record of data.</p><p>Aside from the id-field you can create your own indexes on a table with the <i>create_index()</i> method. Indexes in a database makes things go faster: if we are going to perform a lot of search queries on the <i>age</i> field in the <i>persons</i> table it's a good idea to index that field. When you define the <i>unique</i> parameter as <i>True</i>, the indexed field must be a <b>unique value for each record</b> in the table.</p> <pre class="python">book = database.<span style="">create</span><span style="">(</span><span style="color: #ff0080;">"book"</span><span style="">)</span>
book.<span style="">create_table</span><span style="">(</span><span style="color: #ff0080;">"pages"</span>, <span style="">[</span><span style="color: #ff0080;">"title"</span>, <span style="color: #ff0080;">"text"</span>, <span style="color: #ff0080;">"image"</span>, <span style="color: #ff0080;">"pagenumber"</span><span style="">]</span><span style="">)</span>
book.<span style="">create_index</span><span style="">(</span><span style="color: #ff0080;">"pages"</span>, <span style="color: #ff0080;">"title"</span><span style="">)</span></pre></pre><p>Find out what tables are in the database with a for-loop:</p><pre class="python"><span style="color: #530035;">for</span> table <span style="color: #530035;">in</span> book:
<span style="color: #530035;">print</span> table.<span style="">name</span>
>>> pages</pre></pre> <p> </p> <hr /><h2><a name="retrievetable" title="retrievetable"></a>Retrieving a table from the database </h2><p>Once the database has one or more tables you can retrieve these as <i>Table</i> objects. <i>Table</i> objects have various methods to add, edit and retrieve data. </p><pre class="python">table = db.<span style="">table_name</span></pre></pre><pre class="python">table = db<span style="">[</span>table_name<span style="">]</span></pre></pre><p>You can retrieve a <i>Table</i> object by accessing the database as a list or by directly typing the name of the table after the dot:</p><pre class="python">book = database.<span style="">create</span><span style="">(</span><span style="color: #ff0080;">"book"</span><span style="">)</span>
book.<span style="">create_table</span><span style="">(</span><span style="color: #ff0080;">"pages"</span>, <span style="">[</span><span style="color: #ff0080;">"title"</span>, <span style="color: #ff0080;">"text"</span>, <span style="color: #ff0080;">"image"</span>, <span style="color: #ff0080;">"pagenumber"</span><span style="">]</span><span style="">)</span>
pages = book<span style="">[</span><span style="color: #ff0080;">"pages"</span><span style="">]</span>
pages = book.<span style="">pages</span> <span style="color: grey;"># this is even easier</span></pre></pre> <p> </p> <hr /><h2><a name="addedit" title="addedit"></a>Adding and editing records of data in a table</h2><pre class="python">table.<span style="">append</span><span style="">(</span>fields<span style="">)</span></pre></pre><pre class="python">table.<span style="">edit</span><span style="">(</span><span style="">id</span>, fields<span style="">)</span></pre></pre><pre class="python">table.<span style="">remove</span><span style="">(</span><span style="">id</span>, <span style="">operator</span>=<span style="color: #ff0080;">"="</span>, key=<span style="color: #530035;">None</span><span style="">)</span></pre></pre><p>The <i>append()</i> method adds a new record with the given <i>fields</i> of data to the table. The <i>fields</i> can be a dictionary or a number of parameters specifying the names of the fields and the data in each field. You don't need to supply a value for the id-field - this is a number that is automatically incremented. </p><pre class="python">book = database.<span style="">create</span><span style="">(</span><span style="color: #ff0080;">"book"</span><span style="">)</span>
book.<span style="">create_table</span><span style="">(</span><span style="color: #ff0080;">"pages"</span>, <span style="">[</span><span style="color: #ff0080;">"title"</span>, <span style="color: #ff0080;">"text"</span>, <span style="color: #ff0080;">"image"</span>, <span style="color: #ff0080;">"pagenumber"</span><span style="">]</span><span style="">)</span>
book.<span style="">pages</span>.<span style="">append</span><span style="">(</span><span style="">{</span>
<span style="color: #ff0080;">"title"</span>: <span style="color: #ff0080;">"Chapter One"</span>,
<span style="color: #ff0080;">"text"</span>: <span style="color: #ff0080;">"A short intro on databases in NodeBox"</span>,
<span style="color: #ff0080;">"pagenumber"</span>: <span style="">2</span>
<span style="">}</span><span style="">)</span></pre></pre> <p>Here we used a dictionary to supply the field data, but named parameters are even easier:</p><pre class="python">book.<span style="">pages</span>.<span style="">append</span><span style="">(</span>title=<span style="color: #ff0080;">"Chapter Two"</span>, <span style="color: #530035;">text</span>=<span style="color: #ff0080;">""</span>, pagenumber=<span style="">3</span><span style="">)</span></pre></pre> <p>Editing works in a similar way. You can supply fields to update as a dictionary or as named parameters:<br /></p><pre class="python">book.<span style="">pages</span>.<span style="">edit</span><span style="">(</span><span style="">1</span>, pagenumber=<span style="">1</span><span style="">)</span></pre></pre><p>The <i>remove()</i> method deletes the record with the given <i>id</i> from the table. The <i>operator</i> parameter allows you to specify a different comparison than =, >= or < for example. The <i>key</i> parameter allows you to specify a different field to search in.</p><pre class="python"><span style="color: grey;"># Delete the record with id 2: </span>
book.<span style="">pages</span>.<span style="">remove</span><span style="">(</span><span style="">2</span><span style="">)</span>
<span style="color: grey;"># Delete all pages beyond the first:</span>
book.<span style="">pages</span>.<span style="">delete</span><span style="">(</span><span style="">2</span>, comparison=<span style="color: #ff0080;">">="</span>, key=<span style="color: #ff0080;">"pagenumber"</span><span style="">)</span></pre></pre><p>Find out what fields are in the table with the <i>fields()</i> method:</p><pre class="python"><span style="color: #530035;">print</span> book.<span style="">pages</span>.<span style="">fields</span><span style="">(</span><span style="">)</span>
>>> <span style="">[</span><span style="color: #ff0080;">"id"</span>, <span style="color: #ff0080;">"title"</span>, <span style="color: #ff0080;">"text"</span>, <span style="color: #ff0080;">"image"</span>, <span style="color: #ff0080;">"pagenumber"</span><span style="">]</span></pre></pre> <hr /><h2><a name="find" title="find"></a>Retrieving data from a table</h2><pre class="python">table.<span style="">find</span><span style="">(</span>q, <span style="">operator</span>=<span style="color: #ff0080;">"="</span>, fields=<span style="color: #ff0080;">"*"</span>, key=<span style="color: #530035;">None</span><span style="">)</span></pre></pre><pre class="python">table.<span style="">all</span><span style="">(</span><span style="">)</span></pre></pre><p>The <i>find()</i> method returns records from the table that match a given query. By default, it returns the record whose id is equal to <i>q</i>. The <i>operator</i> parameter allows you to specify a different comparison. The <i>key</i> parameter allows you to specify a different field to search in.</p><p>By default, all fields from the record are returned, or you can define a list of field names in the <i>fields</i> parameter. <br /></p><p>The <i>q</i> parameter can contain an asterisk (*) at the start or at the end as a <b>wildcard</b> character. </p><pre class="python">book = db.<span style="">connect</span><span style="">(</span><span style="color: #ff0080;">"book"</span><span style="">)</span>
r = book.<span style="">pages</span>.<span style="">find</span><span style="">(</span><span style="color: #ff0080;">"Chapter *"</span> key=<span style="color: #ff0080;">"title"</span><span style="">)</span>
<span style="color: #530035;">for</span> record <span style="color: #530035;">in</span> r:
<span style="color: #530035;">print</span> record
>>> <span style="">(</span><span style="">1</span>, u<span style="color: #ff0080;">'Chapter One'</span>, u<span style="color: #ff0080;">'A short intro on databases in NodeBox'</span>, <span style="color: #530035;">None</span>, <span style="">2</span><span style="">)</span>
>>> <span style="">(</span><span style="">2</span>, u<span style="color: #ff0080;">'Chapter Two'</span>, u<span style="color: #ff0080;">''</span>, <span style="color: #530035;">None</span>, <span style="">3</span><span style="">)</span>
<span style="color: grey;"># As you see the returned value is a list of tuple-values, </span>
<span style="color: grey;"># which is easy to unpack:</span>
<span style="color: #530035;">for</span> <span style="">id</span>, title, <span style="color: #530035;">text</span>, <span style="color: #530035;">image</span>, pagenumber <span style="color: #530035;">in</span> r:
<span style="color: #530035;">print</span> title
>>> Chapter One
>>> Chapter Two</pre></pre> <p>You can also query fields directly. For example, this does exactly the same as above:</p><pre class="python">r = book.<span style="">pages</span>.<span style="">title</span><span style="">(</span><span style="color: #ff0080;">"Chapter *"</span><span style="">)</span></pre></pre><p>To get all the records in the table you can use the <i>all()</i> method.</p><p> </p><hr /><h2><a name="batch" title="batch"></a>Batch transactions</h2><p>It's faster to write records to disk in batches of, say, 10 000, than writing each record to the database file individually. Automated scripts that process a lot of records can make use of the <i>commit()</i> method for a significant speed increase:</p><pre class="python">db.<span style="">commit</span><span style="">(</span>each=<span style="">0</span><span style="">)</span></pre></pre><p>When called without parameters, commits any pending transactions. A transaction is a call to a table's <i>append()</i>, <i>edit()</i> or <i>remove()</i> method, or the database's <i>sql()</i> method. </p><p>When commit() is called with the <i>each</i> parameter, sets the batch size. By default, transactions are committed instantly, but <b>if you plan to add a lot of records at the same time it's better to commit them in batch</b>. </p><p>Do not forget to <b>close the database</b> when you are done with it. This commits any pending transaction that have not yet been written to disk.</p><pre class="python">db.<span style="">close</span><span style="">(</span><span style="">)</span></pre></pre> <hr /><h2><a name="xml" title="xml"></a>Exporting the database to XML</h2><pre class="python">db.<span style="">dump</span><span style="">(</span><span style="">)</span></pre></pre><p>The <i>dump()</i> method generates an <a href="http://en.wikipedia.org/wiki/Xml" target="_self">XML</a>-file with all the database's contents. XML is a standardized format to hold data. It's pretty human readable and supported by many applications.</p><pre class="python">book = database.<span style="">connect</span><span style="">(</span><span style="color: #ff0080;">"book"</span><span style="">)</span>
book.<span style="">dump</span><span style="">(</span><span style="">)</span>
<span style="">xml</span> = <span style="color: #530035;">open</span><span style="">(</span><span style="color: #ff0080;">"book.xml"</span><span style="">)</span>.<span style="">read</span><span style="">(</span><span style="">)</span>
<span style="color: #530035;">print</span> <span style="">xml</span></pre></pre> <hr /><h2><a name="sql" title="sql"></a>Executing raw SQL queries</h2><pre class="python">db.<span style="">sql</span><span style="">(</span>query<span style="">)</span></pre></pre><p>Executes an <a href="http://en.wikipedia.org/wiki/Sql" target="_self">SQL</a>-statement on the database. For example, a SELECT-query returns the list of records found. </p><pre class="python">book = db.<span style="">connect</span><span style="">(</span><span style="color: #ff0080;">"book"</span><span style="">)</span>
book.<span style="">sql</span><span style="">(</span><span style="color: #ff0080;">"select * from pages"</span><span style="">)</span></pre></pre>
</div>
</div>
<div id="footer">
Last modified: Jan 10 2008 | © 2004-2012 <a href="http://www.emrg.be/" class="noexternal">Experimental Media Research Group</a>
</div>
<img src="/code/g/footer.jpg" style="border:0" class="footer" width="800" height="334" />
</div>
<script>
try{e=document.getElementsByTagName("span");for(i=0;i<e.length;i++){if(e[i].className=="header_image"){src=e[i].getElementsByTagName("img")[0].src;document.getElementById("header_image").src=src;break;}}}catch(e){}
</script>
</body>
</html>