[Sqlalchemy-commits] [1293] sqlalchemy/branches/schema/doc/build: dev
Brought to you by:
zzzeek
Menu
▾
▴
[Sqlalchemy-commits] [1293] sqlalchemy/branches/schema/doc/build: dev
|
From: <co...@sq...> - 2006-04-19 02:26:33
|
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head><style type="text/css"><!-- #msg dl { border: 1px #006 solid; background: #369; padding: 6px; color: #fff; } #msg dt { float: left; width: 6em; font-weight: bold; } #msg dt:after { content:':';} #msg dl, #msg dt, #msg ul, #msg li { font-family: verdana,arial,helvetica,sans-serif; font-size: 10pt; } #msg dl a { font-weight: bold} #msg dl a:link { color:#fc3; } #msg dl a:active { color:#ff0; } #msg dl a:visited { color:#cc6; } h3 { font-family: verdana,arial,helvetica,sans-serif; font-size: 10pt; font-weight: bold; } #msg pre { overflow: auto; background: #ffc; border: 1px #fc0 solid; padding: 6px; } #msg ul, pre { overflow: auto; } #patch { width: 100%; } #patch h4 {font-family: verdana,arial,helvetica,sans-serif;font-size:10pt;padding:8px;background:#369;color:#fff;margin:0;} #patch .propset h4, #patch .binary h4 {margin:0;} #patch pre {padding:0;line-height:1.2em;margin:0;} #patch .diff {width:100%;background:#eee;padding: 0 0 10px 0;overflow:auto;} #patch .propset .diff, #patch .binary .diff {padding:10px 0;} #patch span {display:block;padding:0 10px;} #patch .modfile, #patch .addfile, #patch .delfile, #patch .propset, #patch .binary, #patch .copfile {border:1px solid #ccc;margin:10px 0;} #patch ins {background:#dfd;text-decoration:none;display:block;padding:0 10px;} #patch del {background:#fdd;text-decoration:none;display:block;padding:0 10px;} #patch .lines, .info {color:#888;background:#fff;} --></style> <title>[1293] sqlalchemy/branches/schema/doc/build: dev</title> </head> <body> <div id="msg"> <dl> <dt>Revision</dt> <dd>1293</dd> <dt>Author</dt> <dd>zzzeek</dd> <dt>Date</dt> <dd>2006-04-18 21:26:23 -0500 (Tue, 18 Apr 2006)</dd> </dl> <h3>Log Message</h3> <pre>dev</pre> <h3>Modified Paths</h3> <ul> <li><a href="#sqlalchemybranchesschemadocbuildcontenttutorialtxt">sqlalchemy/branches/schema/doc/build/content/tutorial.txt</a></li> <li><a href="#sqlalchemybranchesschemadocbuildtestdocspy">sqlalchemy/branches/schema/doc/build/testdocs.py</a></li> </ul> </div> <div id="patch"> <h3>Diff</h3> <a id="sqlalchemybranchesschemadocbuildcontenttutorialtxt"></a> <div class="modfile"><h4>Modified: sqlalchemy/branches/schema/doc/build/content/tutorial.txt (1292 => 1293)</h4> <pre class="diff"><span> <span class="info">--- sqlalchemy/branches/schema/doc/build/content/tutorial.txt 2006-04-19 02:00:16 UTC (rev 1292) +++ sqlalchemy/branches/schema/doc/build/content/tutorial.txt 2006-04-19 02:26:23 UTC (rev 1293) </span><span class="lines">@@ -1,148 +1,165 @@ </span><del>-Tutorial -======== -This tutorial provides a relatively simple walking tour through the basic concepts of SQLAlchemy. You may wish to skip it and dive into the [main manual][manual] which is more reference-oriented. - -[manual]: rel:howtoread - -Installation ------------- - -### Installing SQLAlchemy {@name=sqlalchemy} - -Installing SQLAlchemy from scratch is most easily achieved with [setuptools][]. ([setuptools installation][install setuptools]). Just run this from the command-line: - - $ easy_install SQLAlchemy - -This command will download the latest version of SQLAlchemy from the [Python Cheese Shop][cheese] and install it to your system. - -[setuptools]: http://peak.telecommunity.com/DevCenter/setuptools -[install setuptools]: http://peak.telecommunity.com/DevCenter/EasyInstall#installation-instructions -[cheese]: http://cheeseshop.python.org/pypi - -### Installing a Database API {@name=dbms} - -SQLAlchemy is designed to operate with a [DBAPI][DBAPI] implementation built for a particular database, and includes support for the most popular databases. If you have one of the [supported DBAPI implementations][supported dbms], you can proceed to the following section. Otherwise [SQLite][] is an easy-to-use database to get started with, which works with plain files or in-memory databases. - -[DBAPI]: http://www.python.org/doc/peps/pep-0249/ - -To work with SQLite, you'll need: - - * [pysqlite][] - Python interface for SQLite - * SQLite library - -Note that the SQLite library download is not required with Windows, as the Windows Pysqlite library already includes it linked in. Pysqlite and SQLite can also be installed on Linux or FreeBSD via pre-made [packages][pysqlite packages] or [from sources][pysqlite]. - -[supported dbms]: rel:dbengine_establishing -[sqlite]: http://sqlite.org/ -[pysqlite]: http://pysqlite.org/ -[pysqlite packages]: http://initd.org/tracker/pysqlite/wiki/PysqlitePackages - -Getting Started {@name=gettingstarted} --------------------------- - -### Connecting to the Database - -The first thing needed is a handle to the desired database, represented by a `SQLEngine` object. This object handles the business of managing connections and dealing with the specifics of a particular database. Below, we will make a SQLite connection to a file-based database called "tutorial.db". - - >>> from sqlalchemy import * - >>> db = create_engine('sqlite://filename=tutorial.db') - -For full information on creating database engines, including those for SQLite and others, see [dbengine](rel:dbengine). - -### Creating a Table {@name=table} - -A core philosophy of SQLAlchemy is that tables and domain classes are different beasts. For this reason, SQLAlchemy provides constructs that represent tables by themselves (known as *table metadata*). So we will begin by constructing table metadata objects and performing SQL operations with them directly, keeping in mind that there is also an Object Relational Mapper (ORM) which does the same thing except via domain models. Let's construct an object that represents a table: - - >>> users = Table('users', db, - ... Column('user_id', Integer, primary_key = True), - ... Column('user_name', String(40)), - ... Column('password', String(80)) - ... ) - -As you might have guessed, we have just defined a table named `users` which has three columns: `user_id` (which is a primary key column), `user_name` and `password`. Currently it is just an object that may not correspond to an existing table in your database. To actually create the table, we use the `create()` method. To make it interesting we will have SQLAlchemy to echo the SQL statements it sends to the database: - - >>> db.echo = True - >>> users.create() # doctest:+ELLIPSIS,+NORMALIZE_WHITESPACE - CREATE TABLE users( - user_id INTEGER NOT NULL PRIMARY KEY, - user_name VARCHAR(40), - password VARCHAR(80) - ) - ... - >>> db.echo = False # you can skip this if you want to keep logging SQL statements - -Alternatively, the `users` table might already exist (such as, if you're running examples from this tutorial for the second time), in which case you can just skip the `create()` method call. You can even skip defining the individual columns in the `users` table and ask SQLAlchemy to load its definition from the database: - - >>> users = Table('users', db, autoload = True) - >>> list(users.columns)[0].name - 'user_id' - -Documentation on table metadata is available in [metadata](rel:metadata). - -### Inserting Rows - -Inserting is achieved via the `insert()` method, which defines a *clause object* representing an INSERT statement: - - >>> i = users.insert() - >>> i # doctest:+ELLIPSIS - <sqlalchemy.sql.Insert object at 0x...> - >>> print i - INSERT INTO users (user_id, user_name, password) VALUES (?, ?, ?) - -The `execute()` method of the clause object executes the statement at the database level: - - >>> for name in ['Tom', 'Dick', 'Harry']: # doctest:+ELLIPSIS - ... i.execute(user_name = name) - <sqlalchemy.engine.ResultProxy instance at 0x...> - ... - >>> i.execute(user_name = 'Mary', password = 'secure') # doctest:+ELLIPSIS - <sqlalchemy.engine.ResultProxy instance at 0x...> - -When constructing clause objects, SQLAlchemy will bind all literal values into bind parameters, according to the paramstyle of the underlying DBAPI. This allows for better performance, as the database may cache a compiled representation of the statement and reuse it for new executions, substituting the new values. Also, when using bound values, you need not worry about [SQL injection][] attacks. - -[SQL injection]: http://en.wikipedia.org/wiki/SQL_injection - -Documentation on inserting: [sql_insert](rel:sql_insert). - -### Constructing Queries - -Let's check that the data we have put into `users` table is actually there. The procedure is analogous to the insert example above, except you now call the `select()` method off the `users` table: - - >>> s = users.select() - >>> print s - SELECT users.user_id, users.user_name, users.password - FROM users - >>> r = s.execute() - -This time, we won't ignore the return value of `execute()`: - - >>> r # doctest:+ELLIPSIS - <sqlalchemy.engine.ResultProxy instance at 0x...> - >>> r.keys - ['user_id', 'user_name', 'password'] - >>> row = r.fetchone() - >>> row['user_name'] - u'Tom' - >>> r.fetchall() - [(2, u'Dick', None), (3, u'Harry', None), (4, u'Mary', u'secure')] - -Documentation on selecting: [sql_select](rel:sql_select). - -### Related Table - -Main documentation: [sql](rel:sql). - -### Fancier Querying {@name=fancyquery} - -Main documentation: [sql](rel:sql). - -### Data Mapping {@name=mapping} - -Main documentation: [datamapping](rel:datamapping), [adv_datamapping](rel:adv_datamapping). - -### Transactions - -Main documentation: [unitofwork](rel:unitofwork), [dbengine_transactions](rel:dbengine_transactions). - -Conclusion ----------- </del><ins>+Tutorial +======== +This tutorial provides a relatively simple walking tour through the basic concepts of SQLAlchemy. You may wish to skip it and dive into the [main manual][manual] which is more reference-oriented. + +[manual]: rel:howtoread + +Installation +------------ + +### Installing SQLAlchemy {@name=sqlalchemy} + +Installing SQLAlchemy from scratch is most easily achieved with [setuptools][]. ([setuptools installation][install setuptools]). Just run this from the command-line: + + $ easy_install SQLAlchemy + +This command will download the latest version of SQLAlchemy from the [Python Cheese Shop][cheese] and install it to your system. + +[setuptools]: http://peak.telecommunity.com/DevCenter/setuptools +[install setuptools]: http://peak.telecommunity.com/DevCenter/EasyInstall#installation-instructions +[cheese]: http://cheeseshop.python.org/pypi + +### Installing a Database API {@name=dbms} + +SQLAlchemy is designed to operate with a [DBAPI][DBAPI] implementation built for a particular database, and includes support for the most popular databases. If you have one of the [supported DBAPI implementations][supported dbms], you can proceed to the following section. Otherwise [SQLite][] is an easy-to-use database to get started with, which works with plain files or in-memory databases. + +[DBAPI]: http://www.python.org/doc/peps/pep-0249/ + +To work with SQLite, you'll need: + + * [pysqlite][] - Python interface for SQLite + * SQLite library + +Note that the SQLite library download is not required with Windows, as the Windows Pysqlite library already includes it linked in. Pysqlite and SQLite can also be installed on Linux or FreeBSD via pre-made [packages][pysqlite packages] or [from sources][pysqlite]. + +[supported dbms]: rel:dbengine_establishing +[sqlite]: http://sqlite.org/ +[pysqlite]: http://pysqlite.org/ +[pysqlite packages]: http://initd.org/tracker/pysqlite/wiki/PysqlitePackages + +Getting Started {@name=gettingstarted} +-------------------------- + +### Imports + +SQLAlchemy provides the entire namespace of everything you'll need under the module name `sqlalchemy`. For the purposes of this tutorial, we will import its full list of symbols into our own local namespace. We also will be using a *mod* that provides access to thread-managed connection and session objects, which will greatly simplifies our code. A *mod* is a module that augments the core functionality of SQLAlchemy with additional functionality, and only needs to be imported once within an application. + + >>> from sqlalchemy import * + >>> import sqlalchemy.mods.threadlocal + +### Connecting to the Database + +After our imports, the next thing we need is a handle to the desired database, represented by an `Engine` object. This object handles the business of managing connections and dealing with the specifics of a particular database. Below, we will make a SQLite connection to a file-based database called "tutorial.db". + + >>> db = create_engine('sqlite:///tutorial.db') + + +For full information on creating database engines, including those for SQLite and others, see [dbengine](rel:dbengine). + +### Creating a Table {@name=table} + +A core philosophy of SQLAlchemy is that tables and domain classes are different beasts. For this reason, SQLAlchemy provides constructs that represent tables by themselves (known as *table metadata*). So we will begin by constructing table metadata objects and performing SQL operations with them directly, keeping in mind that there is also an Object Relational Mapper (ORM) which does the same thing except via domain models. + +Firstly, your Tables have to belong to a collection called `MetaData`. We will create a handy form of `MetaData` that automatically connects to our `Engine` (connecting a schema object to an Engine is called *binding*): + + >>> metadata = BoundMetaData(db) + +An equivalent operation is to create the `BoundMetaData` object directly with an Engine URL, which calls the `create_engine` call for us: + + >>> metadata = BoundMetaData('sqlite:///tutorial.db') + +With `metadata` as our established home for tables, lets make a Table for it: + + >>> users = Table('users', metadata, + ... Column('user_id', Integer, primary_key=True), + ... Column('user_name', String(40)), + ... Column('password', String(80)) + ... ) + +As you might have guessed, we have just defined a table named `users` which has three columns: `user_id` (which is a primary key column), `user_name` and `password`. Currently it is just an object that may not correspond to an existing table in your database. To actually create the table, we use the `create()` method. To make it interesting, we will have SQLAlchemy to echo the SQL statements it sends to the database, by setting the `echo` flag on the `Engine` associated with our `BoundMetaData`: + + >>> metadata.engine.echo = True + >>> users.create() # doctest:+ELLIPSIS,+NORMALIZE_WHITESPACE + CREATE TABLE users( + user_id INTEGER NOT NULL PRIMARY KEY, + user_name VARCHAR(40), + password VARCHAR(80) + ) + ... + >>> metadata.engine.echo = False # you can skip this if you want to keep logging SQL statements + +Alternatively, the `users` table might already exist (such as, if you're running examples from this tutorial for the second time), in which case you can just skip the `create()` method call. You can even skip defining the individual columns in the `users` table and ask SQLAlchemy to load its definition from the database: + + >>> users = Table('users', db, autoload = True) + >>> list(users.columns)[0].name + 'user_id' + +Documentation on table metadata is available in [metadata](rel:metadata). + +### Inserting Rows + +Inserting is achieved via the `insert()` method, which defines a *clause object* representing an INSERT statement: + + >>> i = users.insert() + >>> i # doctest:+ELLIPSIS + <sqlalchemy.sql.Insert object at 0x...> + >>> print i + INSERT INTO users (user_id, user_name, password) VALUES (?, ?, ?) + +The `execute()` method of the clause object executes the statement at the database level: + + >>> for name in ['Tom', 'Dick', 'Harry']: # doctest:+ELLIPSIS + ... i.execute(user_name = name) + <sqlalchemy.engine.ResultProxy instance at 0x...> + ... + >>> i.execute(user_name = 'Mary', password = 'secure') # doctest:+ELLIPSIS + <sqlalchemy.engine.ResultProxy instance at 0x...> + +When constructing clause objects, SQLAlchemy will bind all literal values into bind parameters, according to the paramstyle of the underlying DBAPI. This allows for better performance, as the database may cache a compiled representation of the statement and reuse it for new executions, substituting the new values. Also, when using bound values, you need not worry about [SQL injection][] attacks. + +[SQL injection]: http://en.wikipedia.org/wiki/SQL_injection + +Documentation on inserting: [sql_insert](rel:sql_insert). + +### Constructing Queries + +Let's check that the data we have put into `users` table is actually there. The procedure is analogous to the insert example above, except you now call the `select()` method off the `users` table: + + >>> s = users.select() + >>> print s + SELECT users.user_id, users.user_name, users.password + FROM users + >>> r = s.execute() + +This time, we won't ignore the return value of `execute()`: + + >>> r # doctest:+ELLIPSIS + <sqlalchemy.engine.ResultProxy instance at 0x...> + >>> r.keys + ['user_id', 'user_name', 'password'] + >>> row = r.fetchone() + >>> row['user_name'] + u'Tom' + >>> r.fetchall() + [(2, u'Dick', None), (3, u'Harry', None), (4, u'Mary', u'secure')] + +Documentation on selecting: [sql_select](rel:sql_select). + +### Related Table + +Main documentation: [sql](rel:sql). + +### Fancier Querying {@name=fancyquery} + +Main documentation: [sql](rel:sql). + +### Data Mapping {@name=mapping} + +Main documentation: [datamapping](rel:datamapping), [adv_datamapping](rel:adv_datamapping). + +### Transactions + +Main documentation: [unitofwork](rel:unitofwork), [dbengine_transactions](rel:dbengine_transactions). + +Conclusion +---------- </ins></span></pre></div> <a id="sqlalchemybranchesschemadocbuildtestdocspy"></a> <div class="modfile"><h4>Modified: sqlalchemy/branches/schema/doc/build/testdocs.py (1292 => 1293)</h4> <pre class="diff"><span> <span class="info">--- sqlalchemy/branches/schema/doc/build/testdocs.py 2006-04-19 02:00:16 UTC (rev 1292) +++ sqlalchemy/branches/schema/doc/build/testdocs.py 2006-04-19 02:26:23 UTC (rev 1293) </span><span class="lines">@@ -35,9 +35,9 @@ </span><span class="cx"> return runner.failures, runner.tries </span><span class="cx"> </span><span class="cx"> def replace_file(s, oldfile, newfile): </span><del>- engine = r"(^\s*>>>\s*[a-zA-Z_]\w*\s*=\s*create_engine\('sqlite',\s*\{'filename':\s*')" + oldfile+ "('\}\)$)" </del><ins>+ engine = r"sqlite:///" + oldfile </ins><span class="cx"> engine = re.compile(engine, re.MULTILINE) </span><del>- s, n = re.subn(engine, r'\1' + newfile + r'\2', s, 1) </del><ins>+ s, n = re.subn(engine, "sqlite:///" + newfile, s, 1) </ins><span class="cx"> if not n: </span><span class="cx"> raise ValueError("Couldn't find suitable create_engine call to replace '%s' in it" % oldfile) </span><span class="cx"> return s </span></span></pre> </div> </div> </body> </html> |
