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
|
<html>
<head>
<title>PyDbLite</title>
</head>
<body>
<style type="text/css">
body, td {
color: #000000;
background-color: #ffffff;
font-family: sans-serif;
font-size: 13;
}
pre {
font-family: arial }
li { padding-bottom:10;
}
.python {
color:330099;
font-family: "Courier New";
}
.console {
color:white;
background-color:black;
font-family: "Courier New";
padding : 3;
}
td.navigation
{ background-color: #99ccff;
font-weight: bold;
font-family: avantgarde, sans-serif;
font-size: 110%;
width: 90%}
td.lnk { background-color: #99ccff;
font-size: 70%;
}
ol { margin-left : 20px;
}
</style>
<table width="100%" cellspacing="0"><tr><td class="navigation" align="center">PyDbLite</td>
<td class="lnk" align="right"></td>
</tr></table>
<p>PyDbLite is a pure-Python in-memory database engine, using Python list
comprehensions as query language, instead of SQL
<p>It consists of one small module, <code>PyDbLite.py</code>. The package also provides two modules,
<code>SQLite.py</code> and <code>MySQL.py</code>. They use SQLite and MySQL backends with the
same Pythonic syntax as the pure-Python PyDbLite engine
<p>To install the package, just <a href="http://sourceforge.net/project/platformdownload.php?group_id=210258">download
it</a> and install it by running <span class="console">>python setup.py install</span>
<p><h2>Pure-Python engine</h2>
<ul>
<li> import class <CODE>Base</CODE> from module PyDbLite : <span class="python">from PyDbLite import Base</span>
<li> create a database instance, passing it a path in the file system : <span class="python">db = Base('dummy')</span>
<li>for a new database, define the field names : <span class="python">db.create('name','age','size')</span>
<br>You don't have to define the field types. PyDbLite will accept any value that
can be serialized by the <CODE>cPickle</CODE> module : strings, Unicode strings, integers,
floats, dates and datetimes (instances of the <CODE>date</CODE> and <CODE>datetime</CODE> classes in the <CODE>datetime</CODE> module), user-defined classes, etc
<li> if the base exists, open it : <span class="python">db.open()</span>
<li> you can pass a parameter "mode" to the <CODE>create()</CODE> method, to specify what
you want to do if the base already exists in the file system
<ul>
<li>mode = "open" : <span class="python">db.create('name','age','size',mode="open")</span>
opens the database and ignores the field definition
<li> mode = "override" : <span class="python">db.create('name','age','size',mode="override")</span>
erases the existing base and creates a new one with the field definition
<li> if mode is not specified and the base already exists, an <CODE>IOError</CODE> is raised
</ul>
<li> insert a new record
<ul>
<li> by keywords : <span class="python">db.insert(name='homer',age=23,size=1.84)</span>
<br>If some fields are missing, they are initialized with the value <CODE>None</CODE>
<li> by positional arguments : <span class="python">db.insert('homer',23,1.84)</span>
<br>The arguments must be provided in the same order as in the <CODE>create()</CODE> method
</ul>
<li>save the changes on disk : <span class="python">db.commit()</span>
<br>If you don't commit the changes, the insertion, deletion and update operations
will not be saved on disk. To return to the previous version, just <span class="python">
open()</span> it again (this is equivalent to rollback in transactional
databases)
<li> besides the fields passed to the <CODE>create()</CODE> method, an internal field called <CODE>__id__</CODE> is added. It is a integer which is guaranteed to be unique and unchanged for each record in the base, so that it can be used as the record identifier
<li> another internal field called <CODE>__version__</CODE> is also managed by the database engine. It is a integer which is set to 0 when the record is created, then incremented by 1 each time the record is updated. This is used to detect concurrency control, for instance in a web application where 2 users select the same record and want to update it at the same time
<li>the selection of records uses Python list comprehension syntax :
<br><span class="python">recs = [ r for r in db if 30 > r['age'] >= 18 and r['size'] < 2 ]</span>
<br>returns the records in the base where the age is between 18 and 30, and size is below 2 meters. The record is a dictionary, where the key is the field name and value is the field value
<li> Python generator expression syntax can also be used :
<br><span class="python">for r in (r for r in db if r['name'] in ('homer','marge') ):<br>
do_something_with(r)</span>
<br>iterates on the records where the name is one of 'homer' or 'marge'
<li> to iterate on all the records :
<br><span class="python">for r in db:<br>
do_something_with(r)</span>
<li> a record can be accessed by its identifier : <span class="python">record = db[rec_id]</span>
returns the record such that record['__id__'] == rec_id
<li> finally, a shortcut can be used for simple selections :
<span class="python">db(key1=val1,key2=val2)</span> returns the list of records where the keys take the given value. It is equivalent to <span class="python">[ r for r in db if r["key1"]==val1 and r["key2"]==val2]</span>, but much more concise
<li>to speed up selections, an index can be created on a field : <span class="python">db.create_index('age')</span>
<br>When an index is created, the database instance has an attribute (here <CODE>_age</CODE> : note the heading underscore, to avoid name conflicts with internal names). This attribute is a dictionary-like object, where keys are the values taken by the field, and values are the records whose field values are egal to the key :
<br><span class="python">records = db._age[23]</span> returns the list of records with age == 23
<br>If no record has this value, lookup by this value returns an empty list
<br>The index supports iteration on the field values, and the <CODE>keys()</CODE> method
returns all existing values for the field
<li>number of records in the base : <span class="python">len(db)</span>
<li>to delete a record : <span class="python">db.delete(record)</span> or, if you know the record identifier : <span class="python">del db[rec_id]</span>
<li>to delete a list of records : <span class="python">db.delete(list_of_records)</span>
<br><CODE>list_of_records</CODE> can be any iterable (list, tuple, set, etc) yielding records
<li>to update a record : <span class="python">db.update(record,age=24)</span>
<li>to add a new field to an existing base and specify a default value : <span class="python">db.add_field('new_field'[,default=v])</span>. If no default is provided, the field value is <CODE>None</CODE>
<li>to drop an existing field : <span class="python">db.drop_field('name')</span>
<li>to get the list of fields : <span class="python">db.fields</span>
</ul>
<a name="sqlite"><p><h2>SQLite adapter</h2>
<p>The only difference with the pure-Python module is the syntax to identify a <code>Base</code> and the need to specify field types on base creation
<ul>
<li>import the class <code>Base</code> :
<span class="python">from PyDbLite.SQLite import Base</span>
<li>connect to the SQLite database :
<span class="python">connection = sqlite.connect("test")</span>
<li>to create a <code>Base</code> instance (a table in the SQLite database) you pass the connection as argument : <span class="python">db = Base('dummy',connection)</code>
<li>to create the base you must specify an SQLite field type : NULL, INTEGER, REAL, TEXT
or BLOB : <span class="python">db.create(('name','TEXT'),('age',"INTEGER'),('size','REAL'))</span>
<p>For convenience, you can also use the types DATE and DATETIME (or TIMESTAMP), the package will
transparently manage the conversions between the <code>datetime.date</code> and
<code>datetime.datetime</code> and the TEXT type
</ul>
<p>For record insertion, selection, update and deletion, the syntax is the same as above. The only difference is that you can't use the <code>drop_field()</code> method, since dropping fields is not supported by SQLite
<p>The <code>Base</code> instance has an attribute <code>cursor</code>, so you can also execute
SQL expressions by <span class="python">db.cursor.execute(some_sql)</span> and get the result
by <span class="python">results = db.cursor.fetchall()</span>
<p><a name="mysql"><h2>MySQL adapter</h2>
<p>The only difference with the pure-Python module is the syntax to identify a <code>Base</code> and the need to specify field types on base creation
<ul>
<li>import the class <code>Base</code> :
<span class="python">from PyDbLite.MySQL import Base</span>
<li>connect to the SQLite database :
<p><span class="python">import MySQLdb<br>
connection = MySQLdb.connect("localhost","root","admin")<br>
connection.cursor().execute("USE test")
</span>
<li>pass the connection as argument to Base creation : <span class="python">db = Base('dummy',connection)</code>
<li>to create a new base (a table in the MySQL database),specify a valid MySQL field type :
<span class="python">db.create(('name','INTEGER'),('age',"INTEGER'),('size','REAL'))</span>
</ul>
<p>For record insertion, selection, update and deletion, adding or dropping fields,
the syntax is the same as above
<p>The <code>Base</code> instance has an attribute <code>cursor</code>, so you can also execute
SQL expressions by <span class="python">db.cursor.execute(some_sql)</span> and get the result
by <span class="python">results = db.cursor.fetchall()</span>
</body>
</html>
|
