[SQL-CVS] r6 - in trunk/SQLObject: docs examples
SQLObject is a Python ORM.
Brought to you by:
ianbicking,
phd
Menu
▾
▴
[SQL-CVS] r6 - in trunk/SQLObject: docs examples
|
From: <sub...@co...> - 2004-02-07 23:29:15
|
Author: ianb Date: Sat Feb 7 14:21:53 2004 New Revision: 6 Modified: trunk/SQLObject/docs/SQLObject.txt trunk/SQLObject/examples/ (props changed) trunk/SQLObject/examples/codebits.py trunk/SQLObject/examples/config.py trunk/SQLObject/examples/examplestripper.py trunk/SQLObject/examples/leftjoin.py trunk/SQLObject/examples/people.py trunk/SQLObject/examples/personaddress.py trunk/SQLObject/examples/setup.py trunk/SQLObject/examples/simpleperson.py trunk/SQLObject/examples/styles.py trunk/SQLObject/examples/userrole.py Log: Fixed examples and documentation for .new/.get change, and the new sqlobject (vs. SQLObject) package Modified: trunk/SQLObject/docs/SQLObject.txt ============================================================================== --- trunk/SQLObject/docs/SQLObject.txt (original) +++ trunk/SQLObject/docs/SQLObject.txt Sat Feb 7 14:21:53 2004 @@ -65,7 +65,7 @@ effects (it changes the database), and defining classes has side effects (through the use of metaclasses). Attributes are generally exposed, not marked private, knowing that they can be made dynamic -later. +or write-only later. SQLObject creates objects that feel similar to normal Python objects (with the semantics of new-style classes). An attribute attached to a @@ -96,10 +96,10 @@ .. _Webware: http://webware.sourceforge.net SQLObject provides a strong database abstraction, allowing -cross-database compatibility (so long as you don't specifically go -around SQLObject). This compatibility extends not just to several -databases, but also to currently one non-SQL, non-relational backend -(based on the `dbm` module). +cross-database compatibility (so long as you don't sidestep +SQLObject). This compatibility extends not just to several databases, +but also to currently one non-SQL, non-relational backend (based on +the `dbm` module). SQLObject has joins, one-to-many, and many-to-many, something which many ORMs do not have. The join system is also intended to be @@ -109,7 +109,7 @@ names; often these two won't match, or the database style would be inappropriate for a Python attribute. This way your database schema does not have to be designed with SQLObject in mind, and the resulting -classes +classes do not have to inherit the database's naming schemes. Future ====== @@ -130,6 +130,10 @@ * More kinds of joins, and more powerful join results (closer to how `select` works). +See also the `Plan for 0.6`__. + +.. __: Plan06.html + Using SQLObject: An Introduction ================================ @@ -164,6 +168,11 @@ use (you can also set a module-level variable `__connection__` which would automatically be picked up if you don't specify `_connection`). +.. warning:: + The `__connection__` magic variable can be a little fragile -- it + has to be defined before the class is defined. This means it + *must* be assigned above the ``class ...:`` line. + `firstName`, `middleInitial`, and `lastName` are all columns in the database. The general schema implied by this class definition is: @@ -191,7 +200,7 @@ you want to create the tables yourself), you can just use the vague `Col` class. SQLObject doesn't do much type checking, allowing the database and the adapter to handle most of the type conversion. -Databases usually do their own type coercion anyway. +Databases generally do their own type coercion on inputs. You'll note that the ``id`` column is not given in the class definition, it is implied. For MySQL databases it should be defined @@ -209,16 +218,23 @@ Now that you have a class, how will you use it? We'll be considering the class defined above. -You can use the standard constructor to fetch instances that *already -exist*. So if you wanted to fetch the Person by id 10, you'd call -``Person(10)``. - -To create a new object (and row), use the `.new()` class method. In -this case you might call ``Person.new(firstName="John", -lastName="Doe")``. If you had left out ``firstName`` or ``lastName`` -you would have gotten an error, as no default was given for these -columns (``middleInitial`` has a default, so it will be set to -``NULL``, the SQL equivalent of ``None``). +You can use the class method `.get()` to fetch instances that +already exist. So if you wanted to fetch the Person by id 10, you'd +call ``Person.get(10)``. + +.. warning:: + This is a change from SQLObject 0.5 -- before the standard + constructor fetched rows from the database, and the `.new()` + method created new rows. Now SQLObject is more like Python, where + the class constructor creates a new object/row, and the `.get()` + method fetches a row. + +To create a new object (and row), use class instantiation. In this +case you might call ``Person.new(firstName="John", lastName="Doe")``. +If you had left out ``firstName`` or ``lastName`` you would have +gotten an error, as no default was given for these columns +(``middleInitial`` has a default, so it will be set to ``NULL``, the +SQL equivalent of ``None``). When you create an object, it is immediately inserted into the database. SQLObject generally uses the database as immediate storage. @@ -236,7 +252,7 @@ person by a particular ID, you'll get back the same instance. This way you can be sure of a certain amount of consistency if you have multiple threads accessing the same data (though of course across -processes there can be no sharing of an instance). This changes if +processes there can be no sharing of an instance). This isn't true if you're using transactions_. To get an idea of what's happening behind the surface, I'll give the @@ -259,15 +275,15 @@ :file: ../examples/snippets/simpleaddress-person1-use-set.html This will send only one ``UPDATE`` statement. You can also use `set` -with non-database properties (there's no benefit, but the distinction -between database columns and other attributes thus remains somewhat -hidden). +with non-database properties (there's no benefit, but it helps hide +the difference between database and non-database attributes). + One-to-Many Relationships ------------------------- -Well, a real address book should have people, but also addresses. -These examples are in ``personaddress.py`` +A real address book should have people, but also addresses. These +examples are in ``personaddress.py`` First, let's define the new address table. People can have multiple addresses, of course: @@ -275,7 +291,7 @@ .. raw:: html :file: ../examples/snippets/address-address.html -Note the column ``person = ForeignKey('Person')``. This is a +Note the column ``person = ForeignKey("Person")``. This is a reference to a `Person` object. We refer to other classes by name (with a string) to avoid circular dependencies. In the database there will be a ``person_id`` column, type ``INT``, which points to @@ -286,10 +302,10 @@ .. raw:: html :file: ../examples/snippets/address-person.html -We get the backreference with ``addresses = -MultipleJoin('Address')``. When we access a person's `addresses` -attribute, we will get back a (dynamic) list of all the `Address` -objects associated with that person. An example: +We get the backreference with ``addresses = MultipleJoin('Address')``. +When we access a person's `addresses` attribute, we will get back a +list of all the `Address` objects associated with that person. An +example: .. raw:: html :file: ../examples/snippets/address-use1.html @@ -320,6 +336,17 @@ identifier is in addition to the primary key (``id``), which is always present. +.. note:: + SQLObject has a strong requirement that the primary key be unique + and *immutable*. You cannot change the primary key through + SQLObject, and if you change it through another mechanism you can + cause inconsistency in any running SQLObject program (and in your + data). For this reason meaningless integer IDs are encouraged -- + something like a username that could change in the future may + uniquely identify a row, but it may be changed in the future. So + long as it is not used to reference the row internally, it is also + *safe* to change it in the future. + A alternateID column creates a class method, like ``byUsername`` for a column named ``username`` (or you can use the `alternateMethodName` keyword argument to override this). Its use: @@ -356,8 +383,8 @@ .. raw:: html :file: ../examples/snippets/person-select3.html -You may wish to use `MyClass.sqlrepr` to quote any values you use -if you use this technique (quoting is automatic if you use ``q``). +You may wish to use `MyClass.sqlrepr` to quote any values you use if +you create SQL manually (quoting is automatic if you use ``q``). Tables given in `clauseTables` will be added to the ``FROM`` portion (again, they are automatically picked up when using ``q``). The table you're selecting is always assumed to be included, of course. @@ -367,9 +394,8 @@ You can use the keyword arguments `orderBy` to create ``ORDER BY`` in the select statements: `orderBy` takes a string, which should be the *database* name of the column, or a column in the form -``Person.q.firstName``; `groupBy` is similar. Both accept lists or -tuples of arguments. You can use ``"-colname"`` to specify descending -order, or call ``MyClass.select().reversed()``. +``Person.q.firstName``. You can use ``"-colname"`` to specify +descending order, or call ``MyClass.select().reversed()``. You can use the special class variable `_defaultOrder` to give a default ordering for all selects. To get an unordered result when @@ -380,7 +406,9 @@ use ``list()`` to force the result to be executed. When you iterate over the select results, rows are fetched one at a time. This way you can iterate over large results without keeping the entire result set -in memory. +in memory. You can also do things like ``.reversed()`` without +fetching and reversing the entire result -- instead, SQLObject can +change the SQL that is sent so you get equivalent results. You can also slice select results. The results are used in the SQL query, so ``peeps[:10]`` will result in ``LIMIT 10`` being added to @@ -398,6 +426,29 @@ .. raw:: html :file: ../examples/snippets/slicing-batch.html +.. note:: + + There are several factors when considering the efficiency of this + kind of batching, and it depends very much how the batching is + being used. Consider a web application where you are showing an + average of 100 results, 10 at a time, and the results are ordered + by the date they were added to the database. While slicing will + keep the database from returning all the results (and so save some + communication time), the database will still have to scan through + the entire result set to sort the items (so it knows which the + first ten are), and depending on your query may need to scan + through the entire table (depending on your use of indexes). + Indexes are probably the most important way to improve importance + in a case like this, and you may find caching to be more effective + than slicing. + + In this case, caching would mean retrieving the *complete* results. + You can use ``list(MyClass.select(...))`` to do this. You can save + these results for some limited period of time, as the user looks + through the results page by page. This means the first page in a + search result will be slightly more expensive, but all later pages + will be very cheap. + For more information on the where clause in the queries, see the `SQLBuilder documentation`_. @@ -414,29 +465,36 @@ Initializing the Objects ~~~~~~~~~~~~~~~~~~~~~~~~ -With new-style classes, `__init__` is called everytime the class is -called. That means it's called when an object is just fetched from -the cache. That's useless in most cases, so instead we use a `_init` -method, which is only called once in an object's life (with one -argument -- the object's ID). +There are two ways SQLObject instances can come into existance: they +can be fetched from the database, or they can be inserted into the +database. In both cases a new Python object is created. This makes +the place of `__init__` a little confusing. + +In general, you should not touch `__init__`. Instead use the `_init` +method, which is called after an object is fetched or inserted. This +method has the signature ``_init(self, id, connection=None, +selectResults=None)``, though you may just want to use ``_init(self, +*args, **kw)``. Adding Magic Attributes (properties) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ -You can use all the normal techniques for defining this new-style -class, including `classmethod`, `staticmethod`, and `property`, but -you can use a shortcut. If you have a method that's name starts with -``_set_``, ``_get_``, ``_del_``, or ``_doc_``, it will be used to -create a property. So, for instance, say you have images stored under -the ID of the person in the ``/var/people/images`` directory: +You can use all the normal techniques for defining methods in this +new-style class, including `classmethod`, `staticmethod`, and +`property`, but you can also use a shortcut. If you have a method +that's name starts with ``_set_``, ``_get_``, ``_del_``, or ``_doc_``, +it will be used to create a property. So, for instance, say you have +images stored under the ID of the person in the ``/var/people/images`` +directory: .. raw:: html :file: ../examples/snippets/person_magicmethod.html Later, you can use the ``.image`` property just like an attribute, and the changes will be reflected in the filesystem by calling these -methods. I use this particular technique frequently for information -that is better to keep in files as opposed to the database. +methods. This is a good technique for information that is better to +keep in files as opposed to the database (such as large, opaque data +like images). You can also pass an ``image`` keyword argument to the `new` class method or the `set` method, like ``Person.new(..., image=imageText)``. @@ -455,12 +513,13 @@ code you want to run whenever someone's name changes -- you could make a subclass, and then use ``Person.__setattr__(self, 'lastName', value)`` to actually do the deed, but that's obviously very awkward -- -you have to create subclasses without an real inheritance -relationship, and the whole thing feels architecturally fragile. -SQLObject creates methods like ``_set_lastName`` for each of your -columns, but again you can't use this, since there's no superclass to -reference (and you can't write ``SQLObject._set_lastName(...)``). You -want to override that ``_set_lastName`` method yourself. +you have to create subclasses without a real inheritance relationship, +and the whole thing feels architecturally fragile. SQLObject creates +methods like ``_set_lastName`` for each of your columns, but again you +can't use this, since there's no superclass to reference (and you +can't write ``SQLObject._set_lastName(...)``, because the SQLObject +class doesn't know about your class's columns). You want to override +that ``_set_lastName`` method yourself. To deal with this, SQLObject creates two methods for each getter and setter, for example: ``_set_lastName`` and ``_SO_set_lastName``. So @@ -475,12 +534,15 @@ .. raw:: html :file: ../examples/snippets/phonenumber_magicoverride.html -Of course, the user may be surprised if the value they set the -attribute to is not the same value they get back -- in this case we -removed some of the characters before putting it in the database, and -then formatted the number into a nice string on the way out. This is -one disadvantage of making actual work look like simple attribute -access. +.. note:: + + You should be a little cautious when modifying data that gets set + in an attribute. Generally someone using your class will expect + that the value they set the attribute to will be the same value + they get back. In this example we removed some of the characters + before putting it in the database, and reformatted it on the way + out. One advantage of methods (as opposed to attribute access) is + that the programmer is more likely to expect this disconnect. Reference ========= Modified: trunk/SQLObject/examples/codebits.py ============================================================================== --- trunk/SQLObject/examples/codebits.py (original) +++ trunk/SQLObject/examples/codebits.py Sat Feb 7 14:21:53 2004 @@ -67,7 +67,7 @@ ## Snippet "transactions1" conn = DBConnection.PostgresConnection('yada') trans = conn.transaction() -p = Person(1, trans) +p = Person.get(1, trans) p.firstName = 'Bob' trans.commit() p.firstName = 'Billy' Modified: trunk/SQLObject/examples/config.py ============================================================================== --- trunk/SQLObject/examples/config.py (original) +++ trunk/SQLObject/examples/config.py Sat Feb 7 14:21:53 2004 @@ -1,4 +1,4 @@ -from SQLObject import * +from sqlobject import * """ This contains basic configuration for all the examples. Since they all require a connection, you can configure that just in this file. @@ -6,10 +6,11 @@ ## Use one of these to define your connection: """ +## Snippet "connections" conn = MySQLConnection(user='test', db='testdb') conn = PostgresConnection('user=test dbname=testdb') conn = SQLiteConnect('database.db') conn = DBMConnection('database/') +## end snippet """ -conn = DBMConnection('database/') conn = MySQLConnection(user='test', db='test') Modified: trunk/SQLObject/examples/examplestripper.py ============================================================================== --- trunk/SQLObject/examples/examplestripper.py (original) +++ trunk/SQLObject/examples/examplestripper.py Sat Feb 7 14:21:53 2004 @@ -10,6 +10,10 @@ ## End Snippet Then a file snippets/snippetname.html is created. + +This isn't an example, but it's a tool for merging the examples and +the documentation. This requires the presence of the source-highlight +program: http://www.gnu.org/software/src-highlite/source-highlight.html """ import re, os, sys Modified: trunk/SQLObject/examples/leftjoin.py ============================================================================== --- trunk/SQLObject/examples/leftjoin.py (original) +++ trunk/SQLObject/examples/leftjoin.py Sat Feb 7 14:21:53 2004 @@ -1,4 +1,4 @@ -from SQLObject import * +from sqlobject import * ## Use one of these to define your connection: """ @@ -34,9 +34,9 @@ for insert in data: firstName, lastName = insert[0].split(' ', 1) - customer = Customer.new(firstName=firstName, lastName=lastName) + customer = Customer(firstName=firstName, lastName=lastName) for number in insert[1:]: - contact = Contact.new(customer=customer, phoneNumber=number) + contact = Contact(customer=customer, phoneNumber=number) ## Snippet "leftjoin-simple" for customer in Customer.select(): Modified: trunk/SQLObject/examples/people.py ============================================================================== --- trunk/SQLObject/examples/people.py (original) +++ trunk/SQLObject/examples/people.py Sat Feb 7 14:21:53 2004 @@ -1,31 +1,9 @@ #!/usr/bin/env python -from SQLObject import * +from sqlobject import * import os -############################################################ -## Configuration parameters: -############################################################ - -user = os.environ.get('SQLOBJECT_USER', 'sqlobject_test') -passwd = os.environ.get('SQLOBJECT_PASSWORD', '') -database = os.environ.get('SQLOBJECT_DATABASE', 'sqlobject_test') -debug = 1 - -############################################################ -## Setup connections: -############################################################ - -print 'Accessing with user %s and password %s' % (user, passwd) - -if 1: - # do MySQL test - __connection__ = MySQLConnection('localhost', database, - user, passwd, debug=debug) -else: - # do Postgres test - __connection__ = PostgresConnection('dbname=%s user=%s' % - (database, user), debug=debug) - +from setup import * +__connection__ = conn ############################################################ ## Define classes: @@ -93,7 +71,7 @@ if 'create' in args: for table in tableClasses: - table.createTable(ifExists=True) + table.createTable(ifNotExists=True) if 'clear' in args: for table in tableClasses: @@ -105,7 +83,7 @@ ############################################################ test1 = """ ->>> p = Person.new(firstName="John", lastName="Doe", username="johnd") +>>> p = Person(firstName="John", lastName="Doe", username="johnd") >>> print p <Person 1 firstName='John' middleInitial=None lastName='Doe'> >>> print p.firstName @@ -113,7 +91,7 @@ >>> p.middleInitial = 'Q' >>> print p.middleInitial Q ->>> p2 = Person(p.id) +>>> p2 = Person.get(p.id) >>> print p2 <Person 1 firstName='John' middleInitial='Q' lastName='Doe'> >>> print p is p2 @@ -124,7 +102,7 @@ """ test2 = """ ->>> r = Role.new(name="editor") +>>> r = Role(name="editor") >>> p = list(Person.select('all'))[-1] >>> p.addRole(r) >>> print p.roles @@ -134,7 +112,7 @@ >>> r.removePerson(p) >>> print p.roles [] ->>> phone = PhoneNumber.new(person=p, phoneNumber='773-555-1023', phoneType='home') +>>> phone = PhoneNumber(person=p, phoneNumber='773-555-1023', phoneType='home') >>> print p.phoneNumbers """ Modified: trunk/SQLObject/examples/personaddress.py ============================================================================== --- trunk/SQLObject/examples/personaddress.py (original) +++ trunk/SQLObject/examples/personaddress.py Sat Feb 7 14:21:53 2004 @@ -1,4 +1,4 @@ -from SQLObject import * +from sqlobject import * from config import conn __connection__ = conn @@ -32,21 +32,21 @@ reset() ## Snippet "address-use1" -p = Person.new(firstName='John', lastName='Doe') +p = Person(firstName='John', lastName='Doe') print p.addresses #>> [] -a1 = Address.new(street='123', city='Smallsville', +a1 = Address(street='123', city='Smallsville', state='IL', zip='50484', person=p) print [a.street for a in p.addresses] #>> ['123'] ## end snippet # We'll add some more data to make the results more interesting: -add1 = Person.new(firstName='Jane', lastName='Doe') -add2 = Person.new(firstName='Tom', lastName='Brown') -Address.new(street='5839', city='Eckersville', +add1 = Person(firstName='Jane', lastName='Doe') +add2 = Person(firstName='Tom', lastName='Brown') +Address(street='5839', city='Eckersville', state='IL', zip='50482', person=add1) -Address.new(street='4', city='Whinging', +Address(street='4', city='Whinging', state='AZ', zip='49378', person=add2) ## Snippet "person-select1" Modified: trunk/SQLObject/examples/setup.py ============================================================================== --- trunk/SQLObject/examples/setup.py (original) +++ trunk/SQLObject/examples/setup.py Sat Feb 7 14:21:53 2004 @@ -1,6 +1,6 @@ import sys from config import conn -import SQLObject +import sqlobject main = sys.modules['__main__'] @@ -12,7 +12,7 @@ for name in dir(main): value = getattr(main, name) if isinstance(value, type) \ - and issubclass(value, SQLObject.SQLObject)\ - and value is not SQLObject.SQLObject: + and issubclass(value, sqlobject.SQLObject)\ + and value is not sqlobject.SQLObject: value.dropTable(ifExists=True) value.createTable() Modified: trunk/SQLObject/examples/simpleperson.py ============================================================================== --- trunk/SQLObject/examples/simpleperson.py (original) +++ trunk/SQLObject/examples/simpleperson.py Sat Feb 7 14:21:53 2004 @@ -1,16 +1,7 @@ -from SQLObject import * +from sqlobject import * -## Use one of these to define your connection: -""" -## Snippet "connections" -conn = MySQLConnection(user='test', db='testdb') -conn = PostgresConnection('user=test dbname=testdb') -conn = SQLiteConnect('database.db') -conn = DBMConnection('database/') -## End snippet -""" -conn = DBMConnection('database/') -conn = MySQLConnection(user='test', db='test') +from setup import * +__connection__ = conn ## Snippet "simpleaddress-person1" class Person(SQLObject): @@ -47,7 +38,7 @@ ## End snippet ## Snippet "simpleaddress-person1-use" -p = Person.new(firstName="John", lastName="Doe") +p = Person(firstName="John", lastName="Doe") print p #>> <Person 1 firstName='John' middleInitial=None lastName='Doe'> print p.firstName @@ -55,7 +46,7 @@ p.middleInitial = 'Q' print p.middleInitial #>> 'Q' -p2 = Person(1) +p2 = Person.get(1) print p2 #>> <Person 1 firstName='John' middleInitial='Q' lastName='Doe'> print p is p2 @@ -67,7 +58,7 @@ conn.debug = 1 ## Snippet "simpleaddress-person1-use-debug" -p = Person.new(firstName="John", lastName="Doe") +p = Person(firstName="John", lastName="Doe") #>> QueryIns: # INSERT INTO person (last_name, middle_initial, first_name) # VALUES ('Doe', NULL, 'John') @@ -91,7 +82,7 @@ # WHERE id = 1 print p.middleInitial #>> 'Q' -p2 = Person(1) +p2 = Person.get(1) #-- Again, no database access, since we're just grabbing the same #-- instance we already had. print p2 Modified: trunk/SQLObject/examples/styles.py ============================================================================== --- trunk/SQLObject/examples/styles.py (original) +++ trunk/SQLObject/examples/styles.py Sat Feb 7 14:21:53 2004 @@ -1,4 +1,4 @@ -from SQLObject import * +from sqlobject import * from config import conn __connection__ = conn Modified: trunk/SQLObject/examples/userrole.py ============================================================================== --- trunk/SQLObject/examples/userrole.py (original) +++ trunk/SQLObject/examples/userrole.py Sat Feb 7 14:21:53 2004 @@ -1,4 +1,4 @@ -from SQLObject import * +from sqlobject import * import setup __connection__ = setup.conn @@ -34,12 +34,12 @@ setup.reset() ## Snippet "userrole-use" -bob = User.new(username='bob') -tim = User.new(username='tim') -jay = User.new(username='jay') +bob = User(username='bob') +tim = User(username='tim') +jay = User(username='jay') -admin = Role.new(name='admin') -editor = Role.new(name='editor') +admin = Role(name='admin') +editor = Role(name='editor') bob.addRole(admin) bob.addRole(editor) |
