|
What's SQLObject?SQLObject is a popular Object Relational Manager for providing an object interface to your database, with tables as classes, rows as instances, and columns as attributes.I think the explanation is simple and enough, so let's start!一.定义类1.连接标识符:scheme://[user[:password]@]host[:port]/database[?parameters]其中scheme是sqlite, mysql, postgres, firebird, interbase, maxdb, sapdb, mssql, sybase其中之一；示例：mysql://user:passwd@host/databasemysql://host/database?debug=1postgres://user@host/database?debug=&cache=postgres://host:5432/databasesqlite:///full/path/to/databasesqlite:/C|/full/path/to/databasesqlite:/:memory:可选的参数：debug (默认值为False), debugOutput (默认值为False), cache (默认值为True), autoCommit (默认值为True), debugThreading (默认值为False).如果你想传递一个True值到一个连接标识符中，那么只需传递一个非空字符串就可以了；空字符串则表示False值。2.建立一个连接
1 500)this.width=500'> db_filename = os.path.abspath( ' data.db ' )2 500)this.width=500'> if os.path.exists(db_filename):3 500)this.width=500'> os.unlink(db_filename)4 500)this.width=500'>connection_string = ' sqlite: ' + db_filename5 500)this.width=500'>connection = connectionForURI(connection_string)6 500)this.width=500'>sqlhub.processConnection = connection 3.生成一个简单的“地址薄”数据库，我们定义如下类：
1500)this.width=500'>class Person(SQLObject):2500)this.width=500'> firstName = StringCol()3500)this.width=500'> middleInitial = StringCol(length=1, default=None)4500)this.width=500'> lastName = StringCol()这个类对应的数据库Scheme如下：
500)this.width=500'>CREATE TABLE person (500)this.width=500'> id INT PRIMARY KEY AUTO_INCREMENT,500)this.width=500'> first_name TEXT,500)this.width=500'> middle_initial CHAR(1),500)this.width=500'> last_name TEXT500)this.width=500'>);4.创建数据库表很简单，就一句：
500)this.width=500'>Person.createTable()
5.更多示例中firstName列为StringCol类型，当然你也可以换成其它类型。具体可参照：http://www.sqlobject.org/SQLObject.html#column-types你或许已经注意到了在类中并没有定义id列，它是隐式的。在MySQL中，它被定义成INT PRIMARY KEY AUTO_INCREMENT，在Postgres中是SERIAL PRIMARY KEY，而在SQLite中则是INTEGER PRIMARY KEY。你必须把这些值当作是不可变的。当然，你也可以覆盖“id”这个名字。二.使用这个类1.有了类，下面你需要做的就是创建一个新的对象（即新的一行），类实例化如下：
500)this.width=500'>>>> Person(firstName="John", lastName="Doe")500)this.width=500'><Person 1 firstName='John' middleInitial=None lastName='Doe'>2.你可以用get()方法取出已经存在的某行：
500)this.width=500'>>>> Person.get(1)500)this.width=500'><Person 1 firstName='John' middleInitial=None lastName='Doe'>3.这是一个略长些的例子：
500)this.width=500'>>>> p = Person.get(1)500)this.width=500'>>>> p500)this.width=500'><Person 1 firstName='John' middleInitial=None lastName='Doe'>500)this.width=500'>>>> p.firstName500)this.width=500'>'John'500)this.width=500'>>>> p.middleInitial = 'Q'500)this.width=500'>>>> p.middleInitial500)this.width=500'>'Q'500)this.width=500'>>>> p2 = Person.get(1)500)this.width=500'>>>> p2500)this.width=500'><Person 1 firstName='John' middleInitial='Q' lastName='Doe'>500)this.width=500'>>>> p is p2500)this.width=500'>True
4.在这里，列被当作属性来访问。上述代码的“背后”又发生了什么呢？你可以在连接标识符中添加?debug=t，这样，在控制台中将打印出下面类似结果：
500)this.width=500'>>>> # This will make SQLObject print out the SQL it executes:500)this.width=500'>>>> Person._connection.debug = True500)this.width=500'>>>> p = Person(firstName='Bob', lastName='Hope')500)this.width=500'> 1/QueryIns: INSERT INTO person (last_name, middle_initial, first_name) VALUES ('Hope', 500)this.width=500'>500)this.width=500'>NULL, 'Bob')500)this.width=500'> 1/COMMIT : auto500)this.width=500'> 1/QueryOne: SELECT first_name, middle_initial, last_name FROM person WHERE id = 2500)this.width=500'> 1/COMMIT : auto500)this.width=500'>>>> p500)this.width=500'><Person 2 firstName='Bob' middleInitial=None lastName='Hope'>500)this.width=500'>>>> p.middleInitial = 'Q'500)this.width=500'> 1/Query : UPDATE person SET middle_initial = 'Q' WHERE id = 2500)this.width=500'> 1/COMMIT : auto500)this.width=500'>>>> p2 = Person.get(1)500)this.width=500'>>>> # Note: no database access, since we're just grabbing the same500)this.width=500'>>>> # instance we already had.上述代码，可以清晰地看出“后台”所做的事情。5.作为一个小小的优化，你可以将独立地指定每个属性值，换成一次指定多个值，使用set方法：
500)this.width=500'>p.set(firstName='Robert', lastName='Hope Jr.')（未完待续）参考资料：http://www.sqlobject.org/SQLObject.html |
