<!doctype html> <html xmlns="http://www.w3.org/1999/xhtml" lang="en"> <head> <meta http-equiv="X-UA-Compatible" content="IE=Edge" /> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <title>SQLObject — SQLObject 3.7.0 documentation</title> <link rel="stylesheet" href="_static/bizstyle.css" type="text/css" /> <link rel="stylesheet" href="_static/pygments.css" type="text/css" /> <script type="text/javascript" src="_static/documentation_options.js"></script> <script type="text/javascript" src="_static/jquery.js"></script> <script type="text/javascript" src="_static/underscore.js"></script> <script type="text/javascript" src="_static/doctools.js"></script> <script type="text/javascript" src="_static/bizstyle.js"></script> <link rel="index" title="Index" href="genindex.html" /> <link rel="search" title="Search" href="search.html" /> <link rel="next" title="SQLObject FAQ" href="FAQ.html" /> <link rel="prev" title="SQLObject and Python 3" href="Python3.html" /> <meta name="viewport" content="width=device-width,initial-scale=1.0"> <!--[if lt IE 9]> <script type="text/javascript" src="_static/css3-mediaqueries.js"></script> <![endif]--> </head><body> <div class="related" role="navigation" aria-label="related navigation"> <h3>Navigation</h3> <ul> <li class="right" style="margin-right: 10px"> <a href="genindex.html" title="General Index" accesskey="I">index</a></li> <li class="right" > <a href="py-modindex.html" title="Python Module Index" >modules</a> |</li> <li class="right" > <a href="FAQ.html" title="SQLObject FAQ" accesskey="N">next</a> |</li> <li class="right" > <a href="Python3.html" title="SQLObject and Python 3" accesskey="P">previous</a> |</li> <li class="nav-item nav-item-0"><a href="index.html">SQLObject 3.7.0 documentation</a> »</li> </ul> </div> <div class="sphinxsidebar" role="navigation" aria-label="main navigation"> <div class="sphinxsidebarwrapper"> <h3><a href="index.html">Table Of Contents</a></h3> <ul> <li><a class="reference internal" href="#">SQLObject</a><ul> <li><a class="reference internal" href="#credits">Credits</a></li> <li><a class="reference internal" href="#license">License</a></li> <li><a class="reference internal" href="#introduction">Introduction</a></li> <li><a class="reference internal" href="#requirements">Requirements</a></li> <li><a class="reference internal" href="#compared-to-other-database-wrappers">Compared To Other Database Wrappers</a></li> <li><a class="reference internal" href="#using-sqlobject-an-introduction">Using SQLObject: An Introduction</a><ul> <li><a class="reference internal" href="#declaring-a-connection">Declaring a Connection</a></li> <li><a class="reference internal" href="#declaring-the-class">Declaring the Class</a></li> <li><a class="reference internal" href="#using-the-class">Using the Class</a></li> <li><a class="reference internal" href="#selecting-multiple-objects">Selecting Multiple Objects</a><ul> <li><a class="reference internal" href="#q-magic">q-magic</a></li> <li><a class="reference internal" href="#selectby-method">selectBy Method</a></li> </ul> </li> <li><a class="reference internal" href="#lazy-updates">Lazy Updates</a></li> <li><a class="reference internal" href="#one-to-many-relationships">One-to-Many Relationships</a></li> <li><a class="reference internal" href="#many-to-many-relationships">Many-to-Many Relationships</a></li> <li><a class="reference internal" href="#selecting-objects-using-relationships">Selecting Objects Using Relationships</a></li> <li><a class="reference internal" href="#class-sqlmeta">Class sqlmeta</a><ul> <li><a class="reference internal" href="#using-sqlmeta">Using sqlmeta</a></li> <li><a class="reference internal" href="#j-magic">j-magic</a></li> </ul> </li> <li><a class="reference internal" href="#sqlobject-class">SQLObject Class</a></li> <li><a class="reference internal" href="#customizing-the-objects">Customizing the Objects</a><ul> <li><a class="reference internal" href="#initializing-the-objects">Initializing the Objects</a></li> <li><a class="reference internal" href="#adding-magic-attributes-properties">Adding Magic Attributes (properties)</a></li> <li><a class="reference internal" href="#overriding-column-attributes">Overriding Column Attributes</a></li> <li><a class="reference internal" href="#undefined-attributes">Undefined attributes</a></li> </ul> </li> </ul> </li> <li><a class="reference internal" href="#reference">Reference</a><ul> <li><a class="reference internal" href="#col-class-specifying-columns">Col Class: Specifying Columns</a><ul> <li><a class="reference internal" href="#column-types">Column Types</a></li> </ul> </li> <li><a class="reference internal" href="#relationships-between-classes-tables">Relationships Between Classes/Tables</a><ul> <li><a class="reference internal" href="#foreignkey">ForeignKey</a></li> <li><a class="reference internal" href="#multiplejoin-and-sqlmultiplejoin-one-to-many">MultipleJoin and SQLMultipleJoin: One-to-Many</a></li> <li><a class="reference internal" href="#relatedjoin-and-sqlrelatedjoin-many-to-many">RelatedJoin and SQLRelatedJoin: Many-to-Many</a></li> <li><a class="reference internal" href="#singlejoin-one-to-one">SingleJoin: One-to-One</a></li> </ul> </li> <li><a class="reference internal" href="#connection-pooling">Connection pooling</a></li> <li><a class="reference internal" href="#transactions">Transactions</a></li> <li><a class="reference internal" href="#automatic-schema-generation">Automatic Schema Generation</a><ul> <li><a class="reference internal" href="#indexes">Indexes</a></li> <li><a class="reference internal" href="#creating-and-dropping-tables">Creating and Dropping Tables</a></li> </ul> </li> </ul> </li> <li><a class="reference internal" href="#dynamic-classes">Dynamic Classes</a><ul> <li><a class="reference internal" href="#automatic-class-generation">Automatic Class Generation</a></li> <li><a class="reference internal" href="#runtime-column-and-join-changes">Runtime Column and Join Changes</a></li> </ul> </li> <li><a class="reference internal" href="#legacy-database-schemas">Legacy Database Schemas</a><ul> <li><a class="reference internal" href="#sqlobject-requirements">SQLObject requirements</a><ul> <li><a class="reference internal" href="#workaround-for-primary-keys-made-up-of-multiple-columns">Workaround for primary keys made up of multiple columns</a></li> </ul> </li> <li><a class="reference internal" href="#changing-the-naming-style">Changing the Naming Style</a></li> <li><a class="reference internal" href="#irregular-naming">Irregular Naming</a></li> <li><a class="reference internal" href="#non-integer-keys">Non-Integer Keys</a></li> </ul> </li> <li><a class="reference internal" href="#dbconnection-database-connections">DBConnection: Database Connections</a><ul> <li><a class="reference internal" href="#id3">MySQL</a></li> <li><a class="reference internal" href="#postgres">Postgres</a></li> <li><a class="reference internal" href="#id4">SQLite</a></li> <li><a class="reference internal" href="#id5">Firebird</a></li> <li><a class="reference internal" href="#id6">Sybase</a></li> <li><a class="reference internal" href="#id7">MAX DB</a></li> <li><a class="reference internal" href="#ms-sql-server">MS SQL Server</a></li> </ul> </li> <li><a class="reference internal" href="#events-signals">Events (signals)</a></li> <li><a class="reference internal" href="#exported-symbols">Exported Symbols</a><ul> <li><a class="reference internal" href="#left-join-and-other-joins">LEFT JOIN and other JOINs</a></li> <li><a class="reference internal" href="#how-can-i-join-a-table-with-itself">How can I join a table with itself?</a></li> <li><a class="reference internal" href="#can-i-use-a-join-with-aliases">Can I use a JOIN() with aliases?</a></li> <li><a class="reference internal" href="#subqueries-subselects">Subqueries (subselects)</a></li> <li><a class="reference internal" href="#utilities">Utilities</a></li> <li><a class="reference internal" href="#sqlbuilder">SQLBuilder</a></li> </ul> </li> </ul> </li> </ul> <h4>Previous topic</h4> <p class="topless"><a href="Python3.html" title="previous chapter">SQLObject and Python 3</a></p> <h4>Next topic</h4> <p class="topless"><a href="FAQ.html" title="next chapter">SQLObject FAQ</a></p> <div role="note" aria-label="source link"> <h3>This Page</h3> <ul class="this-page-menu"> <li><a href="_sources/SQLObject.rst.txt" rel="nofollow">Show Source</a></li> </ul> </div> <div id="searchbox" style="display: none" role="search"> <h3>Quick search</h3> <div class="searchformwrapper"> <form class="search" action="search.html" method="get"> <input type="text" name="q" /> <input type="submit" value="Go" /> <input type="hidden" name="check_keywords" value="yes" /> <input type="hidden" name="area" value="default" /> </form> </div> </div> <script type="text/javascript">$('#searchbox').show(0);</script> </div> </div> <div class="document"> <div class="documentwrapper"> <div class="bodywrapper"> <div class="body" role="main"> <div class="section" id="sqlobject"> <h1><a class="toc-backref" href="#id9">SQLObject</a><a class="headerlink" href="#sqlobject" title="Permalink to this headline">¶</a></h1> <div class="contents topic" id="contents"> <p class="topic-title first">Contents:</p> <ul class="simple"> <li><a class="reference internal" href="#sqlobject" id="id9">SQLObject</a><ul> <li><a class="reference internal" href="#credits" id="id10">Credits</a></li> <li><a class="reference internal" href="#license" id="id11">License</a></li> <li><a class="reference internal" href="#introduction" id="id12">Introduction</a></li> <li><a class="reference internal" href="#requirements" id="id13">Requirements</a></li> <li><a class="reference internal" href="#compared-to-other-database-wrappers" id="id14">Compared To Other Database Wrappers</a></li> <li><a class="reference internal" href="#using-sqlobject-an-introduction" id="id15">Using SQLObject: An Introduction</a><ul> <li><a class="reference internal" href="#declaring-a-connection" id="id16">Declaring a Connection</a></li> <li><a class="reference internal" href="#declaring-the-class" id="id17">Declaring the Class</a></li> <li><a class="reference internal" href="#using-the-class" id="id18">Using the Class</a></li> <li><a class="reference internal" href="#selecting-multiple-objects" id="id19">Selecting Multiple Objects</a><ul> <li><a class="reference internal" href="#q-magic" id="id20">q-magic</a></li> <li><a class="reference internal" href="#selectby-method" id="id21">selectBy Method</a></li> </ul> </li> <li><a class="reference internal" href="#lazy-updates" id="id22">Lazy Updates</a></li> <li><a class="reference internal" href="#one-to-many-relationships" id="id23">One-to-Many Relationships</a></li> <li><a class="reference internal" href="#many-to-many-relationships" id="id24">Many-to-Many Relationships</a></li> <li><a class="reference internal" href="#selecting-objects-using-relationships" id="id25">Selecting Objects Using Relationships</a></li> <li><a class="reference internal" href="#class-sqlmeta" id="id26">Class sqlmeta</a><ul> <li><a class="reference internal" href="#using-sqlmeta" id="id27">Using sqlmeta</a></li> <li><a class="reference internal" href="#j-magic" id="id28">j-magic</a></li> </ul> </li> <li><a class="reference internal" href="#sqlobject-class" id="id29">SQLObject Class</a></li> <li><a class="reference internal" href="#customizing-the-objects" id="id30">Customizing the Objects</a><ul> <li><a class="reference internal" href="#initializing-the-objects" id="id31">Initializing the Objects</a></li> <li><a class="reference internal" href="#adding-magic-attributes-properties" id="id32">Adding Magic Attributes (properties)</a></li> <li><a class="reference internal" href="#overriding-column-attributes" id="id33">Overriding Column Attributes</a></li> <li><a class="reference internal" href="#undefined-attributes" id="id34">Undefined attributes</a></li> </ul> </li> </ul> </li> <li><a class="reference internal" href="#reference" id="id35">Reference</a><ul> <li><a class="reference internal" href="#col-class-specifying-columns" id="id36">Col Class: Specifying Columns</a><ul> <li><a class="reference internal" href="#column-types" id="id37">Column Types</a></li> </ul> </li> <li><a class="reference internal" href="#relationships-between-classes-tables" id="id38">Relationships Between Classes/Tables</a><ul> <li><a class="reference internal" href="#foreignkey" id="id39">ForeignKey</a></li> <li><a class="reference internal" href="#multiplejoin-and-sqlmultiplejoin-one-to-many" id="id40">MultipleJoin and SQLMultipleJoin: One-to-Many</a></li> <li><a class="reference internal" href="#relatedjoin-and-sqlrelatedjoin-many-to-many" id="id41">RelatedJoin and SQLRelatedJoin: Many-to-Many</a></li> <li><a class="reference internal" href="#singlejoin-one-to-one" id="id42">SingleJoin: One-to-One</a></li> </ul> </li> <li><a class="reference internal" href="#connection-pooling" id="id43">Connection pooling</a></li> <li><a class="reference internal" href="#transactions" id="id44">Transactions</a></li> <li><a class="reference internal" href="#automatic-schema-generation" id="id45">Automatic Schema Generation</a><ul> <li><a class="reference internal" href="#indexes" id="id46">Indexes</a></li> <li><a class="reference internal" href="#creating-and-dropping-tables" id="id47">Creating and Dropping Tables</a></li> </ul> </li> </ul> </li> <li><a class="reference internal" href="#dynamic-classes" id="id48">Dynamic Classes</a><ul> <li><a class="reference internal" href="#automatic-class-generation" id="id49">Automatic Class Generation</a></li> <li><a class="reference internal" href="#runtime-column-and-join-changes" id="id50">Runtime Column and Join Changes</a></li> </ul> </li> <li><a class="reference internal" href="#legacy-database-schemas" id="id51">Legacy Database Schemas</a><ul> <li><a class="reference internal" href="#sqlobject-requirements" id="id52">SQLObject requirements</a><ul> <li><a class="reference internal" href="#workaround-for-primary-keys-made-up-of-multiple-columns" id="id53">Workaround for primary keys made up of multiple columns</a></li> </ul> </li> <li><a class="reference internal" href="#changing-the-naming-style" id="id54">Changing the Naming Style</a></li> <li><a class="reference internal" href="#irregular-naming" id="id55">Irregular Naming</a></li> <li><a class="reference internal" href="#non-integer-keys" id="id56">Non-Integer Keys</a></li> </ul> </li> <li><a class="reference internal" href="#dbconnection-database-connections" id="id57">DBConnection: Database Connections</a><ul> <li><a class="reference internal" href="#id3" id="id58">MySQL</a></li> <li><a class="reference internal" href="#postgres" id="id59">Postgres</a></li> <li><a class="reference internal" href="#id4" id="id60">SQLite</a></li> <li><a class="reference internal" href="#id5" id="id61">Firebird</a></li> <li><a class="reference internal" href="#id6" id="id62">Sybase</a></li> <li><a class="reference internal" href="#id7" id="id63">MAX DB</a></li> <li><a class="reference internal" href="#ms-sql-server" id="id64">MS SQL Server</a></li> </ul> </li> <li><a class="reference internal" href="#events-signals" id="id65">Events (signals)</a></li> <li><a class="reference internal" href="#exported-symbols" id="id66">Exported Symbols</a><ul> <li><a class="reference internal" href="#left-join-and-other-joins" id="id67">LEFT JOIN and other JOINs</a></li> <li><a class="reference internal" href="#how-can-i-join-a-table-with-itself" id="id68">How can I join a table with itself?</a></li> <li><a class="reference internal" href="#can-i-use-a-join-with-aliases" id="id69">Can I use a JOIN() with aliases?</a></li> <li><a class="reference internal" href="#subqueries-subselects" id="id70">Subqueries (subselects)</a></li> <li><a class="reference internal" href="#utilities" id="id71">Utilities</a></li> <li><a class="reference internal" href="#sqlbuilder" id="id72">SQLBuilder</a></li> </ul> </li> </ul> </li> </ul> </div> <div class="section" id="credits"> <h2><a class="toc-backref" href="#id10">Credits</a><a class="headerlink" href="#credits" title="Permalink to this headline">¶</a></h2> <p>SQLObject is by Ian Bicking (<a class="reference external" href="mailto:ianb%40colorstudy.com">ianb<span>@</span>colorstudy<span>.</span>com</a>) and <a class="reference external" href="Authors.html">Contributors</a>. The website is <a class="reference external" href="http://sqlobject.org">sqlobject.org</a>.</p> </div> <div class="section" id="license"> <h2><a class="toc-backref" href="#id11">License</a><a class="headerlink" href="#license" title="Permalink to this headline">¶</a></h2> <p>The code is licensed under the <a class="reference external" href="https://www.gnu.org/copyleft/lesser.html">Lesser General Public License</a> (LGPL).</p> <p>This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public License for more details.</p> </div> <div class="section" id="introduction"> <h2><a class="toc-backref" href="#id12">Introduction</a><a class="headerlink" href="#introduction" title="Permalink to this headline">¶</a></h2> <p>SQLObject is an <em>object-relational mapper</em> for <a class="reference external" href="https://www.python.org/">Python</a> programming language. It allows you to translate RDBMS table rows into Python objects, and manipulate those objects to transparently manipulate the database.</p> <p>In using SQLObject, you will create a class definition that will describe how the object translates to the database table. SQLObject will produce the code to access the database, and update the database with your changes. The generated interface looks similar to any other interface, and callers need not be aware of the database backend.</p> <p>SQLObject also includes a novel feature to avoid generating, textually, your SQL queries. This also allows non-SQL databases to be used with the same query syntax.</p> </div> <div class="section" id="requirements"> <h2><a class="toc-backref" href="#id13">Requirements</a><a class="headerlink" href="#requirements" title="Permalink to this headline">¶</a></h2> <p>Currently SQLObject supports <a class="reference external" href="https://www.mysql.com/">MySQL</a> via <a class="reference external" href="https://sourceforge.net/projects/mysql-python/">MySQLdb</a> aka MySQL-python (called <a class="reference external" href="https://pypi.org/project/mysqlclient/">mysqlclient</a> for Python 3), <a class="reference external" href="https://pypi.org/project/mysql-connector/">MySQL Connector</a>, <a class="reference external" href="https://github.com/python-oursql/oursql">oursql</a>, <a class="reference external" href="https://github.com/PyMySQL/PyMySQL/">PyMySQL</a>, <a class="reference external" href="https://pypi.org/project/pyodbc/">PyODBC</a> and <a class="reference external" href="https://pypi.org/project/pypyodbc/">PyPyODBC</a>. For <a class="reference external" href="https://postgresql.org">PostgreSQL</a> <a class="reference external" href="http://initd.org/psycopg/">psycopg2</a> is recommended; <a class="reference external" href="http://www.pygresql.org/">PyGreSQL</a>, <a class="reference external" href="https://pypi.org/project/py-postgresql/">py-postgresql</a> and <a class="reference external" href="https://pypi.org/project/pg8000/">pg8000</a> are supported; <a class="reference external" href="https://sqlite.org/">SQLite</a> has a built-in driver or <a class="reference external" href="https://github.com/ghaering/pysqlite">PySQLite</a>. <a class="reference external" href="http://www.firebirdsql.org/en/python-driver/">Firebird</a> is supported via <a class="reference external" href="http://www.firebirdsql.org/en/devel-python-driver/">fdb</a> or <a class="reference external" href="http://kinterbasdb.sourceforge.net/">kinterbasdb</a>; <a class="reference external" href="https://pypi.org/project/firebirdsql/">pyfirebirdsql</a> is supported but has problems. <a class="reference external" href="http://maxdb.sap.com/">MAX DB</a> (also known as SAP DB) is supported via <a class="reference external" href="http://maxdb.sap.com/doc/7_8/50/01923f25b842438a408805774f6989/frameset.htm">sapdb</a>. Sybase via <a class="reference external" href="http://www.object-craft.com.au/projects/sybase/">Sybase</a>. <a class="reference external" href="http://www.microsoft.com/sql/">MSSQL Server</a> via <a class="reference external" href="http://www.pymssql.org/en/latest/index.html">pymssql</a> (+ <a class="reference external" href="http://www.freetds.org/">FreeTDS</a>) or <a class="reference external" href="http://adodbapi.sourceforge.net/">adodbapi</a> (Win32). <a class="reference external" href="https://pypi.org/project/pyodbc/">PyODBC</a> and <a class="reference external" href="https://pypi.org/project/pypyodbc/">PyPyODBC</a> are supported for MySQL, PostgreSQL and MSSQL but have problems (not all tests passed).</p> <p>Python 2.7 or 3.4+ is required.</p> </div> <div class="section" id="compared-to-other-database-wrappers"> <h2><a class="toc-backref" href="#id14">Compared To Other Database Wrappers</a><a class="headerlink" href="#compared-to-other-database-wrappers" title="Permalink to this headline">¶</a></h2> <p>There are several object-relational mappers (ORM) for Python. We honestly can’t comment deeply on the quality of those packages, but we’ll try to place SQLObject in perspective.</p> <p>Objects have built-in magic – setting attributes has side 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 or write-only later.</p> <p>SQLObject creates objects that feel similar to normal Python objects. An attribute attached to a column doesn’t look different than an attribute that’s attached to a file, or an attribute that is calculated. It is a specific goal that you be able to change the database without changing the interface, including changing the scope of the database, making it more or less prominent as a storage mechanism.</p> <p>This is in contrast to some ORMs that provide a dictionary-like interface to the database (for example, <a class="reference external" href="http://skunkweb.sourceforge.net/pydo.html">PyDO</a>). The dictionary interface distinguishes the row from a normal Python object. We also don’t care for the use of strings where an attribute seems more natural – columns are limited in number and predefined, just like attributes. (Note: newer version of PyDO apparently allow attribute access as well)</p> <p>SQLObject is, to my knowledge, unique in using metaclasses to facilitate this seamless integration. Some other ORMs use code generation to create an interface, expressing the schema in a CSV or XML file (for example, <a class="reference external" href="http://webware.sourceforge.net/Webware/MiddleKit/Docs/">MiddleKit</a>, part of <a class="reference external" href="http://webware.sourceforge.net/Webware/Docs/">Webware</a>). By using metaclasses you are able to comfortably define your schema in the Python source code. No code generation, no weird tools, no compilation step.</p> <p>SQLObject provides a strong database abstraction, allowing cross-database compatibility (so long as you don’t sidestep SQLObject).</p> <p>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 extensible.</p> <p>You can map between database names and Python attribute and class 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 do not have to inherit the database’s naming schemes.</p> </div> <div class="section" id="using-sqlobject-an-introduction"> <h2><a class="toc-backref" href="#id15">Using SQLObject: An Introduction</a><a class="headerlink" href="#using-sqlobject-an-introduction" title="Permalink to this headline">¶</a></h2> <p>Let’s start off quickly. We’ll generally just import everything from the <code class="docutils literal notranslate"><span class="pre">sqlobject</span></code> class:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="kn">from</span> <span class="nn">sqlobject</span> <span class="k">import</span> <span class="o">*</span> </pre></div> </div> <div class="section" id="declaring-a-connection"> <h3><a class="toc-backref" href="#id16">Declaring a Connection</a><a class="headerlink" href="#declaring-a-connection" title="Permalink to this headline">¶</a></h3> <p>The connection URI must follow the standard URI syntax:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span>scheme://[user[:password]@]host[:port]/database[?parameters] </pre></div> </div> <p>Scheme is one of <code class="docutils literal notranslate"><span class="pre">sqlite</span></code>, <code class="docutils literal notranslate"><span class="pre">mysql</span></code>, <code class="docutils literal notranslate"><span class="pre">postgres</span></code>, <code class="docutils literal notranslate"><span class="pre">firebird</span></code>, <code class="docutils literal notranslate"><span class="pre">interbase</span></code>, <code class="docutils literal notranslate"><span class="pre">maxdb</span></code>, <code class="docutils literal notranslate"><span class="pre">sapdb</span></code>, <code class="docutils literal notranslate"><span class="pre">mssql</span></code>, <code class="docutils literal notranslate"><span class="pre">sybase</span></code>.</p> <p>Examples:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span>mysql://user:password@host/database mysql://host/database?debug=1 postgres://user@host/database?debug=&cache= postgres:///full/path/to/socket/database postgres://host:5432/database sqlite:///full/path/to/database sqlite:/C:/full/path/to/database sqlite:/:memory: </pre></div> </div> <p>Parameters are: <code class="docutils literal notranslate"><span class="pre">debug</span></code> (default: False), <code class="docutils literal notranslate"><span class="pre">debugOutput</span></code> (default: False), <code class="docutils literal notranslate"><span class="pre">cache</span></code> (default: True), <code class="docutils literal notranslate"><span class="pre">autoCommit</span></code> (default: True), <code class="docutils literal notranslate"><span class="pre">debugThreading</span></code> (default: False), <code class="docutils literal notranslate"><span class="pre">logger</span></code> (default: None), <code class="docutils literal notranslate"><span class="pre">loglevel</span></code> (default: None), <code class="docutils literal notranslate"><span class="pre">schema</span></code> (default: None).</p> <p>If you want to pass True value in a connection URI - pass almost any non-empty string, especially <code class="docutils literal notranslate"><span class="pre">yes</span></code>, <code class="docutils literal notranslate"><span class="pre">true</span></code>, <code class="docutils literal notranslate"><span class="pre">on</span></code> or <code class="docutils literal notranslate"><span class="pre">1</span></code>; an empty string or <code class="docutils literal notranslate"><span class="pre">no</span></code>, <code class="docutils literal notranslate"><span class="pre">false</span></code>, <code class="docutils literal notranslate"><span class="pre">off</span></code> or <code class="docutils literal notranslate"><span class="pre">0</span></code> for False.</p> <p>There are also connection-specific parameters, they are listed in the appropriate sections.</p> <p>Lets first set up a connection:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="kn">import</span> <span class="nn">os</span> <span class="gp">>>> </span><span class="n">db_filename</span> <span class="o">=</span> <span class="n">os</span><span class="o">.</span><span class="n">path</span><span class="o">.</span><span class="n">abspath</span><span class="p">(</span><span class="s1">'data.db'</span><span class="p">)</span> <span class="gp">>>> </span><span class="n">connection_string</span> <span class="o">=</span> <span class="s1">'sqlite:'</span> <span class="o">+</span> <span class="n">db_filename</span> <span class="gp">>>> </span><span class="n">connection</span> <span class="o">=</span> <span class="n">connectionForURI</span><span class="p">(</span><span class="n">connection_string</span><span class="p">)</span> <span class="gp">>>> </span><span class="n">sqlhub</span><span class="o">.</span><span class="n">processConnection</span> <span class="o">=</span> <span class="n">connection</span> </pre></div> </div> <p>The <code class="docutils literal notranslate"><span class="pre">sqlhub.processConnection</span></code> assignment means that all classes will, by default, use this connection we’ve just set up.</p> </div> <div class="section" id="declaring-the-class"> <h3><a class="toc-backref" href="#id17">Declaring the Class</a><a class="headerlink" href="#declaring-the-class" title="Permalink to this headline">¶</a></h3> <p>We’ll develop a simple addressbook-like database. We could create the tables ourselves, and just have SQLObject access those tables, but let’s have SQLObject do that work. First, the class:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="k">class</span> <span class="nc">Person</span><span class="p">(</span><span class="n">SQLObject</span><span class="p">):</span> <span class="gp">...</span> <span class="gp">... </span> <span class="n">firstName</span> <span class="o">=</span> <span class="n">StringCol</span><span class="p">()</span> <span class="gp">... </span> <span class="n">middleInitial</span> <span class="o">=</span> <span class="n">StringCol</span><span class="p">(</span><span class="n">length</span><span class="o">=</span><span class="mi">1</span><span class="p">,</span> <span class="n">default</span><span class="o">=</span><span class="kc">None</span><span class="p">)</span> <span class="gp">... </span> <span class="n">lastName</span> <span class="o">=</span> <span class="n">StringCol</span><span class="p">()</span> </pre></div> </div> <p>Many basic table schemas won’t be any more complicated than that. <cite>firstName</cite>, <cite>middleInitial</cite>, and <cite>lastName</cite> are all columns in the database. The general schema implied by this class definition is:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">CREATE</span> <span class="n">TABLE</span> <span class="n">person</span> <span class="p">(</span> <span class="nb">id</span> <span class="n">INT</span> <span class="n">PRIMARY</span> <span class="n">KEY</span> <span class="n">AUTO_INCREMENT</span><span class="p">,</span> <span class="n">first_name</span> <span class="n">TEXT</span><span class="p">,</span> <span class="n">middle_initial</span> <span class="n">CHAR</span><span class="p">(</span><span class="mi">1</span><span class="p">),</span> <span class="n">last_name</span> <span class="n">TEXT</span> <span class="p">);</span> </pre></div> </div> <p>This is for SQLite or MySQL. The schema for other databases looks slightly different (especially the <code class="docutils literal notranslate"><span class="pre">id</span></code> column). You’ll notice the names were changed from mixedCase to underscore_separated – this is done by the <a class="reference internal" href="#changing-the-naming-style">style object</a>. There are a variety of ways to handle names that don’t fit conventions (see <a class="reference internal" href="#irregular-naming">Irregular Naming</a>).</p> <p>Now we’ll create the table in the database:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="n">Person</span><span class="o">.</span><span class="n">createTable</span><span class="p">()</span> <span class="go">[]</span> </pre></div> </div> <p>We can change the type of the various columns by using something other than <cite>StringCol</cite>, or using different arguments. More about this in <a class="reference internal" href="#column-types">Column Types</a>.</p> <p>You’ll note that the <code class="docutils literal notranslate"><span class="pre">id</span></code> column is not given in the class definition, it is implied. For MySQL databases it should be defined as <code class="docutils literal notranslate"><span class="pre">INT</span> <span class="pre">PRIMARY</span> <span class="pre">KEY</span> <span class="pre">AUTO_INCREMENT</span></code>, in Postgres <code class="docutils literal notranslate"><span class="pre">SERIAL</span> <span class="pre">PRIMARY</span> <span class="pre">KEY</span></code>, in SQLite as <code class="docutils literal notranslate"><span class="pre">INTEGER</span> <span class="pre">PRIMARY</span> <span class="pre">KEY</span> <span class="pre">AUTOINCREMENT</span></code>, and for other backends accordingly. You can’t use tables with SQLObject that don’t have a single primary key, and you must treat that key as immutable (otherwise you’ll confuse SQLObject terribly).</p> <p>You can <a class="reference internal" href="#class-sqlmeta">override the id name</a> in the database, but it is always called <code class="docutils literal notranslate"><span class="pre">.id</span></code> from Python.</p> </div> <div class="section" id="using-the-class"> <h3><a class="toc-backref" href="#id18">Using the Class</a><a class="headerlink" href="#using-the-class" title="Permalink to this headline">¶</a></h3> <p>Now that you have a class, how will you use it? We’ll be considering the class defined above.</p> <p>To create a new object (and row), use class instantiation, like:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="n">Person</span><span class="p">(</span><span class="n">firstName</span><span class="o">=</span><span class="s2">"John"</span><span class="p">,</span> <span class="n">lastName</span><span class="o">=</span><span class="s2">"Doe"</span><span class="p">)</span> <span class="go"><Person 1 firstName='John' middleInitial=None lastName='Doe'></span> </pre></div> </div> <div class="admonition note"> <p class="first admonition-title">Note</p> <p>In SQLObject NULL/None does <em>not</em> mean default. NULL is a funny thing; it mean very different things in different contexts and to different people. Sometimes it means “default”, sometimes “not applicable”, sometimes “unknown”. If you want a default, NULL or otherwise, you always have to be explicit in your class definition.</p> <p class="last">Also note that the SQLObject default isn’t the same as the database’s default (SQLObject never uses the database’s default).</p> </div> <p>If you had left out <code class="docutils literal notranslate"><span class="pre">firstName</span></code> or <code class="docutils literal notranslate"><span class="pre">lastName</span></code> you would have gotten an error, as no default was given for these columns (<code class="docutils literal notranslate"><span class="pre">middleInitial</span></code> has a default, so it will be set to <code class="docutils literal notranslate"><span class="pre">NULL</span></code>, the database equivalent of <code class="docutils literal notranslate"><span class="pre">None</span></code>).</p> <p>You can use the class method <cite>.get()</cite> to fetch instances that already exist:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="n">Person</span><span class="o">.</span><span class="n">get</span><span class="p">(</span><span class="mi">1</span><span class="p">)</span> <span class="go"><Person 1 firstName='John' middleInitial=None lastName='Doe'></span> </pre></div> </div> <p>When you create an object, it is immediately inserted into the database. SQLObject uses the database as immediate storage, unlike some other systems where you explicitly save objects into a database.</p> <p>Here’s a longer example of using the class:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="n">p</span> <span class="o">=</span> <span class="n">Person</span><span class="o">.</span><span class="n">get</span><span class="p">(</span><span class="mi">1</span><span class="p">)</span> <span class="gp">>>> </span><span class="n">p</span> <span class="go"><Person 1 firstName='John' middleInitial=None lastName='Doe'></span> <span class="gp">>>> </span><span class="n">p</span><span class="o">.</span><span class="n">firstName</span> <span class="go">'John'</span> <span class="gp">>>> </span><span class="n">p</span><span class="o">.</span><span class="n">middleInitial</span> <span class="o">=</span> <span class="s1">'Q'</span> <span class="gp">>>> </span><span class="n">p</span><span class="o">.</span><span class="n">middleInitial</span> <span class="go">'Q'</span> <span class="gp">>>> </span><span class="n">p2</span> <span class="o">=</span> <span class="n">Person</span><span class="o">.</span><span class="n">get</span><span class="p">(</span><span class="mi">1</span><span class="p">)</span> <span class="gp">>>> </span><span class="n">p2</span> <span class="go"><Person 1 firstName='John' middleInitial='Q' lastName='Doe'></span> <span class="gp">>>> </span><span class="n">p</span> <span class="ow">is</span> <span class="n">p2</span> <span class="go">True</span> </pre></div> </div> <p>Columns are accessed like attributes. (This uses the <code class="docutils literal notranslate"><span class="pre">property</span></code> feature of Python, so that retrieving and setting these attributes executes code). Also note that objects are unique – there is generally only one <code class="docutils literal notranslate"><span class="pre">Person</span></code> instance of a particular id in memory at any one time. If you ask for a person by a particular ID more than once, 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 isn’t true if you’re using <a class="reference internal" href="#transactions">transactions</a>, which are necessarily isolated.</p> <p>To get an idea of what’s happening behind the surface, we’ll give the same actions with the SQL that is sent, along with some commentary:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="c1"># This will make SQLObject print out the SQL it executes:</span> <span class="gp">>>> </span><span class="n">Person</span><span class="o">.</span><span class="n">_connection</span><span class="o">.</span><span class="n">debug</span> <span class="o">=</span> <span class="kc">True</span> <span class="gp">>>> </span><span class="n">p</span> <span class="o">=</span> <span class="n">Person</span><span class="p">(</span><span class="n">firstName</span><span class="o">=</span><span class="s1">'Bob'</span><span class="p">,</span> <span class="n">lastName</span><span class="o">=</span><span class="s1">'Hope'</span><span class="p">)</span> <span class="go"> 1/QueryIns: INSERT INTO person (first_name, middle_initial, last_name) VALUES ('Bob', NULL, 'Hope')</span> <span class="go"> 1/QueryR : INSERT INTO person (first_name, middle_initial, last_name) VALUES ('Bob', NULL, 'Hope')</span> <span class="go"> 1/COMMIT : auto</span> <span class="go"> 1/QueryOne: SELECT first_name, middle_initial, last_name FROM person WHERE ((person.id) = (2))</span> <span class="go"> 1/QueryR : SELECT first_name, middle_initial, last_name FROM person WHERE ((person.id) = (2))</span> <span class="go"> 1/COMMIT : auto</span> <span class="gp">>>> </span><span class="n">p</span> <span class="go"><Person 2 firstName='Bob' middleInitial=None lastName='Hope'></span> <span class="gp">>>> </span><span class="n">p</span><span class="o">.</span><span class="n">middleInitial</span> <span class="o">=</span> <span class="s1">'Q'</span> <span class="go"> 1/Query : UPDATE person SET middle_initial = ('Q') WHERE id = (2)</span> <span class="go"> 1/QueryR : UPDATE person SET middle_initial = ('Q') WHERE id = (2)</span> <span class="go"> 1/COMMIT : auto</span> <span class="gp">>>> </span><span class="n">p2</span> <span class="o">=</span> <span class="n">Person</span><span class="o">.</span><span class="n">get</span><span class="p">(</span><span class="mi">1</span><span class="p">)</span> <span class="gp">>>> </span><span class="c1"># Note: no database access, since we're just grabbing the same</span> <span class="gp">>>> </span><span class="c1"># instance we already had.</span> </pre></div> </div> <p>Hopefully you see that the SQL that gets sent is pretty clear and predictable. To view the SQL being sent, add <code class="docutils literal notranslate"><span class="pre">?debug=true</span></code> to your connection URI, or set the <code class="docutils literal notranslate"><span class="pre">debug</span></code> attribute on the connection, and all SQL will be printed to the console. This can be reassuring, and we would encourage you to try it.</p> <p>As a small optimization, instead of assigning each attribute individually, you can assign a number of them using the <code class="docutils literal notranslate"><span class="pre">set</span></code> method, like:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="n">p</span><span class="o">.</span><span class="n">set</span><span class="p">(</span><span class="n">firstName</span><span class="o">=</span><span class="s1">'Robert'</span><span class="p">,</span> <span class="n">lastName</span><span class="o">=</span><span class="s1">'Hope Jr.'</span><span class="p">)</span> </pre></div> </div> <p>This will send only one <code class="docutils literal notranslate"><span class="pre">UPDATE</span></code> statement. You can also use <cite>set</cite> with non-database properties (there’s no benefit, but it helps hide the difference between database and non-database attributes).</p> </div> <div class="section" id="selecting-multiple-objects"> <h3><a class="toc-backref" href="#id19">Selecting Multiple Objects</a><a class="headerlink" href="#selecting-multiple-objects" title="Permalink to this headline">¶</a></h3> <p>While the full power of all the kinds of joins you can do with a relational database are not revealed in SQLObject, a simple <code class="docutils literal notranslate"><span class="pre">SELECT</span></code> is available.</p> <p><code class="docutils literal notranslate"><span class="pre">select</span></code> is a class method, and you call it like (with the SQL that’s generated):</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="n">Person</span><span class="o">.</span><span class="n">_connection</span><span class="o">.</span><span class="n">debug</span> <span class="o">=</span> <span class="kc">True</span> <span class="gp">>>> </span><span class="n">peeps</span> <span class="o">=</span> <span class="n">Person</span><span class="o">.</span><span class="n">select</span><span class="p">(</span><span class="n">Person</span><span class="o">.</span><span class="n">q</span><span class="o">.</span><span class="n">firstName</span><span class="o">==</span><span class="s2">"John"</span><span class="p">)</span> <span class="gp">>>> </span><span class="nb">list</span><span class="p">(</span><span class="n">peeps</span><span class="p">)</span> <span class="go"> 1/Select : SELECT person.id, person.first_name, person.middle_initial, person.last_name FROM person WHERE ((person.first_name) = ('John'))</span> <span class="go"> 1/QueryR : SELECT person.id, person.first_name, person.middle_initial, person.last_name FROM person WHERE ((person.first_name) = ('John'))</span> <span class="go"> 1/COMMIT : auto</span> <span class="go">[<Person 1 firstName='John' middleInitial='Q' lastName='Doe'>]</span> </pre></div> </div> <p>This example returns everyone with the first name John.</p> <p>Queries can be more complex:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="n">peeps</span> <span class="o">=</span> <span class="n">Person</span><span class="o">.</span><span class="n">select</span><span class="p">(</span> <span class="gp">... </span> <span class="n">OR</span><span class="p">(</span><span class="n">Person</span><span class="o">.</span><span class="n">q</span><span class="o">.</span><span class="n">firstName</span> <span class="o">==</span> <span class="s2">"John"</span><span class="p">,</span> <span class="gp">... </span> <span class="n">LIKE</span><span class="p">(</span><span class="n">Person</span><span class="o">.</span><span class="n">q</span><span class="o">.</span><span class="n">lastName</span><span class="p">,</span> <span class="s2">"%Hope%"</span><span class="p">)))</span> <span class="gp">>>> </span><span class="nb">list</span><span class="p">(</span><span class="n">peeps</span><span class="p">)</span> <span class="go"> 1/Select : SELECT person.id, person.first_name, person.middle_initial, person.last_name FROM person WHERE (((person.first_name) = ('John')) OR (person.last_name LIKE ('%Hope%')))</span> <span class="go"> 1/QueryR : SELECT person.id, person.first_name, person.middle_initial, person.last_name FROM person WHERE (((person.first_name) = ('John')) OR (person.last_name LIKE ('%Hope%')))</span> <span class="go"> 1/COMMIT : auto</span> <span class="go">[<Person 1 firstName='John' middleInitial='Q' lastName='Doe'>, <Person 2 firstName='Robert' middleInitial='Q' lastName='Hope Jr.'>]</span> </pre></div> </div> <p>You’ll note that classes have an attribute <code class="docutils literal notranslate"><span class="pre">q</span></code>, which gives access to special objects for constructing query clauses. All attributes under <code class="docutils literal notranslate"><span class="pre">q</span></code> refer to column names and if you construct logical statements with these it’ll give you the SQL for that statement. You can also create your SQL more manually:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="n">Person</span><span class="o">.</span><span class="n">_connection</span><span class="o">.</span><span class="n">debug</span> <span class="o">=</span> <span class="kc">False</span> <span class="c1"># Need for doctests</span> <span class="gp">>>> </span><span class="n">peeps</span> <span class="o">=</span> <span class="n">Person</span><span class="o">.</span><span class="n">select</span><span class="p">(</span><span class="s2">"""person.first_name = 'John' AND</span> <span class="gp">... </span><span class="s2"> person.last_name LIKE 'D%'"""</span><span class="p">)</span> </pre></div> </div> <p>You should use <cite>MyClass.sqlrepr</cite> to quote any values you use if you create SQL manually (quoting is automatic if you use <code class="docutils literal notranslate"><span class="pre">q</span></code>).</p> <p id="orderby">You can use the keyword arguments <cite>orderBy</cite> to create <code class="docutils literal notranslate"><span class="pre">ORDER</span> <span class="pre">BY</span></code> in the select statements: <cite>orderBy</cite> takes a string, which should be the <em>database</em> name of the column, or a column in the form <code class="docutils literal notranslate"><span class="pre">Person.q.firstName</span></code>. You can use <code class="docutils literal notranslate"><span class="pre">"-colname"</span></code> or <code class="docutils literal notranslate"><span class="pre">DESC(Person.q.firstName</span></code>) to specify descending order (this is translated to DESC, so it works on non-numeric types as well), or call <code class="docutils literal notranslate"><span class="pre">MyClass.select().reversed()</span></code>. orderBy can also take a list of columns in the same format: <code class="docutils literal notranslate"><span class="pre">["-weight",</span> <span class="pre">"name"]</span></code>.</p> <p>You can use the <a class="reference internal" href="#class-sqlmeta">sqlmeta</a> class variable <cite>defaultOrder</cite> to give a default ordering for all selects. To get an unordered result when <cite>defaultOrder</cite> is used, use <code class="docutils literal notranslate"><span class="pre">orderBy=None</span></code>.</p> <p>Select results are generators, which are lazily evaluated. So the SQL is only executed when you iterate over the select results, or if you use <code class="docutils literal notranslate"><span class="pre">list()</span></code> 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. You can also do things like <code class="docutils literal notranslate"><span class="pre">.reversed()</span></code> without fetching and reversing the entire result – instead, SQLObject can change the SQL that is sent so you get equivalent results.</p> <p>You can also slice select results. This modifies the SQL query, so <code class="docutils literal notranslate"><span class="pre">peeps[:10]</span></code> will result in <code class="docutils literal notranslate"><span class="pre">LIMIT</span> <span class="pre">10</span></code> being added to the end of the SQL query. If the slice cannot be performed in the SQL (e.g., peeps[:-10]), then the select is executed, and the slice is performed on the list of results. This will generally only happen when you use negative indexes.</p> <p>In certain cases, you may get a select result with an object in it more than once, e.g., in some joins. If you don’t want this, you can add the keyword argument <code class="docutils literal notranslate"><span class="pre">MyClass.select(...,</span> <span class="pre">distinct=True)</span></code>, which results in a <code class="docutils literal notranslate"><span class="pre">SELECT</span> <span class="pre">DISTINCT</span></code> call.</p> <p>You can get the length of the result without fetching all the results by calling <code class="docutils literal notranslate"><span class="pre">count</span></code> on the result object, like <code class="docutils literal notranslate"><span class="pre">MyClass.select().count()</span></code>. A <code class="docutils literal notranslate"><span class="pre">COUNT(*)</span></code> query is used – the actual objects are not fetched from the database. Together with slicing, this makes batched queries easy to write:</p> <blockquote> <div>start = 20 size = 10 query = Table.select() results = query[start:start+size] total = query.count() print “Showing page %i of %i” % (start/size + 1, total/size + 1)</div></blockquote> <div class="admonition note"> <p class="first admonition-title">Note</p> <p>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.</p> <p class="last">In this case, caching would mean retrieving the <em>complete</em> results. You can use <code class="docutils literal notranslate"><span class="pre">list(MyClass.select(...))</span></code> 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.</p> </div> <p>For more information on the where clause in the queries, see the <a class="reference external" href="SQLBuilder.html">SQLBuilder documentation</a>.</p> <div class="section" id="q-magic"> <h4><a class="toc-backref" href="#id20">q-magic</a><a class="headerlink" href="#q-magic" title="Permalink to this headline">¶</a></h4> <p>Please note the use of the <cite>q</cite> attribute in examples above. <cite>q</cite> is an object that returns special objects to construct SQL expressions. Operations on objects returned by <cite>q-magic</cite> are not evaluated immediately but stored in a manner similar to symbolic algebra; the entire expression is evaluated by constructing a string that is sent then to the backend.</p> <p>For example, for the code:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="n">peeps</span> <span class="o">=</span> <span class="n">Person</span><span class="o">.</span><span class="n">select</span><span class="p">(</span><span class="n">Person</span><span class="o">.</span><span class="n">q</span><span class="o">.</span><span class="n">firstName</span><span class="o">==</span><span class="s2">"John"</span><span class="p">)</span> </pre></div> </div> <p>SQLObject doesn’t evaluate firstName but stores the expression:</p> <blockquote> <div>Person.q.firstName==”John”</div></blockquote> <p>Later SQLObject converts it to the string <code class="docutils literal notranslate"><span class="pre">first_name</span> <span class="pre">=</span> <span class="pre">'John'</span></code> and passes the string to the backend.</p> </div> <div class="section" id="selectby-method"> <h4><a class="toc-backref" href="#id21">selectBy Method</a><a class="headerlink" href="#selectby-method" title="Permalink to this headline">¶</a></h4> <p>An alternative to <code class="docutils literal notranslate"><span class="pre">.select</span></code> is <code class="docutils literal notranslate"><span class="pre">.selectBy</span></code>. It works like:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="n">peeps</span> <span class="o">=</span> <span class="n">Person</span><span class="o">.</span><span class="n">selectBy</span><span class="p">(</span><span class="n">firstName</span><span class="o">=</span><span class="s2">"John"</span><span class="p">,</span> <span class="n">lastName</span><span class="o">=</span><span class="s2">"Doe"</span><span class="p">)</span> </pre></div> </div> <p>Each keyword argument is a column, and all the keyword arguments are ANDed together. The return value is a <cite>SelectResults</cite>, so you can slice it, count it, order it, etc.</p> </div> </div> <div class="section" id="lazy-updates"> <h3><a class="toc-backref" href="#id22">Lazy Updates</a><a class="headerlink" href="#lazy-updates" title="Permalink to this headline">¶</a></h3> <p>By default SQLObject sends an <code class="docutils literal notranslate"><span class="pre">UPDATE</span></code> to the database for every attribute you set, or every time you call <code class="docutils literal notranslate"><span class="pre">.set()</span></code>. If you want to avoid this many updates, add <code class="docutils literal notranslate"><span class="pre">lazyUpdate</span> <span class="pre">=</span> <span class="pre">True</span></code> to your class <a class="reference internal" href="#class-sqlmeta">sqlmeta definition</a>.</p> <p>Then updates will only be written to the database when you call <code class="docutils literal notranslate"><span class="pre">inst.syncUpdate()</span></code> or <code class="docutils literal notranslate"><span class="pre">inst.sync()</span></code>: <code class="docutils literal notranslate"><span class="pre">.sync()</span></code> also refetches the data from the database, which <code class="docutils literal notranslate"><span class="pre">.syncUpdate()</span></code> does not do.</p> <p>When enabled instances will have a property <code class="docutils literal notranslate"><span class="pre">.sqlmeta.dirty</span></code>, which indicates if there are pending updates. Inserts are still done immediately; there’s no way to do lazy inserts at this time.</p> </div> <div class="section" id="one-to-many-relationships"> <h3><a class="toc-backref" href="#id23">One-to-Many Relationships</a><a class="headerlink" href="#one-to-many-relationships" title="Permalink to this headline">¶</a></h3> <p>An address book is nothing without addresses.</p> <p>First, let’s define the new address table. People can have multiple addresses, of course:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="k">class</span> <span class="nc">Address</span><span class="p">(</span><span class="n">SQLObject</span><span class="p">):</span> <span class="gp">...</span> <span class="gp">... </span> <span class="n">street</span> <span class="o">=</span> <span class="n">StringCol</span><span class="p">()</span> <span class="gp">... </span> <span class="n">city</span> <span class="o">=</span> <span class="n">StringCol</span><span class="p">()</span> <span class="gp">... </span> <span class="n">state</span> <span class="o">=</span> <span class="n">StringCol</span><span class="p">(</span><span class="n">length</span><span class="o">=</span><span class="mi">2</span><span class="p">)</span> <span class="gp">... </span> <span class="nb">zip</span> <span class="o">=</span> <span class="n">StringCol</span><span class="p">(</span><span class="n">length</span><span class="o">=</span><span class="mi">9</span><span class="p">)</span> <span class="gp">... </span> <span class="n">person</span> <span class="o">=</span> <span class="n">ForeignKey</span><span class="p">(</span><span class="s1">'Person'</span><span class="p">)</span> <span class="gp">>>> </span><span class="n">Address</span><span class="o">.</span><span class="n">createTable</span><span class="p">()</span> <span class="go">[]</span> </pre></div> </div> <p>Note the column <code class="docutils literal notranslate"><span class="pre">person</span> <span class="pre">=</span> <span class="pre">ForeignKey("Person")</span></code>. This is a reference to a <cite>Person</cite> object. We refer to other classes by name (with a string). In the database there will be a <code class="docutils literal notranslate"><span class="pre">person_id</span></code> column, type <code class="docutils literal notranslate"><span class="pre">INT</span></code>, which points to the <code class="docutils literal notranslate"><span class="pre">person</span></code> column.</p> <div class="admonition note"> <p class="first admonition-title">Note</p> <p>The reason SQLObject uses strings to refer to other classes is because the other class often does not yet exist. Classes in Python are <em>created</em>, not <em>declared</em>; so when a module is imported the commands are executed. <code class="docutils literal notranslate"><span class="pre">class</span></code> is just another command; one that creates a class and assigns it to the name you give.</p> <p class="last">If class <code class="docutils literal notranslate"><span class="pre">A</span></code> referred to class <code class="docutils literal notranslate"><span class="pre">B</span></code>, but class <code class="docutils literal notranslate"><span class="pre">B</span></code> was defined below <code class="docutils literal notranslate"><span class="pre">A</span></code> in the module, then when the <code class="docutils literal notranslate"><span class="pre">A</span></code> class was created (including creating all its column attributes) the <code class="docutils literal notranslate"><span class="pre">B</span></code> class simply wouldn’t exist. By referring to classes by name, we can wait until all the required classes exist before creating the links between classes.</p> </div> <p>We want an attribute that gives the addresses for a person. In a class definition we’d do:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="k">class</span> <span class="nc">Person</span><span class="p">(</span><span class="n">SQLObject</span><span class="p">):</span> <span class="o">...</span> <span class="n">addresses</span> <span class="o">=</span> <span class="n">MultipleJoin</span><span class="p">(</span><span class="s1">'Address'</span><span class="p">)</span> </pre></div> </div> <p>But we already have the class. We can add this to the class in-place:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="n">Person</span><span class="o">.</span><span class="n">sqlmeta</span><span class="o">.</span><span class="n">addJoin</span><span class="p">(</span><span class="n">MultipleJoin</span><span class="p">(</span><span class="s1">'Address'</span><span class="p">,</span> <span class="gp">... </span> <span class="n">joinMethodName</span><span class="o">=</span><span class="s1">'addresses'</span><span class="p">))</span> </pre></div> </div> <div class="admonition note"> <p class="first admonition-title">Note</p> <p class="last">In almost all cases you can modify SQLObject classes after they’ve been created. Having attributes that contain <code class="docutils literal notranslate"><span class="pre">*Col</span></code> objects in the class definition is equivalent to calling certain class methods (like <code class="docutils literal notranslate"><span class="pre">addColumn()</span></code>).</p> </div> <p>Now we can get the backreference with <code class="docutils literal notranslate"><span class="pre">aPerson.addresses</span></code>, which returns a list. An example:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="n">p</span><span class="o">.</span><span class="n">addresses</span> <span class="go">[]</span> <span class="gp">>>> </span><span class="n">Address</span><span class="p">(</span><span class="n">street</span><span class="o">=</span><span class="s1">'123 W Main St'</span><span class="p">,</span> <span class="n">city</span><span class="o">=</span><span class="s1">'Smallsville'</span><span class="p">,</span> <span class="gp">... </span> <span class="n">state</span><span class="o">=</span><span class="s1">'MN'</span><span class="p">,</span> <span class="nb">zip</span><span class="o">=</span><span class="s1">'55407'</span><span class="p">,</span> <span class="n">person</span><span class="o">=</span><span class="n">p</span><span class="p">)</span> <span class="go"><Address 1 ...></span> <span class="gp">>>> </span><span class="n">p</span><span class="o">.</span><span class="n">addresses</span> <span class="go">[<Address 1 ...>]</span> </pre></div> </div> <div class="admonition note"> <p class="first admonition-title">Note</p> <p class="last">MultipleJoin, as well as RelatedJoin, returns a list of results. It is often preferable to get a <a class="reference external" href="SelectResults.html">SelectResults</a> object instead, in which case you should use SQLMultipleJoin and SQLRelatedJoin. The declaration of these joins is unchanged from above, but the returned iterator has many additional useful methods.</p> </div> </div> <div class="section" id="many-to-many-relationships"> <h3><a class="toc-backref" href="#id24">Many-to-Many Relationships</a><a class="headerlink" href="#many-to-many-relationships" title="Permalink to this headline">¶</a></h3> <p>For this example we will have user and role objects. The two have a many-to-many relationship, which is represented with the <cite>RelatedJoin</cite>.</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="k">class</span> <span class="nc">User</span><span class="p">(</span><span class="n">SQLObject</span><span class="p">):</span> <span class="gp">...</span> <span class="gp">... </span> <span class="k">class</span> <span class="nc">sqlmeta</span><span class="p">:</span> <span class="gp">... </span> <span class="c1"># user is a reserved word in some databases, so we won't</span> <span class="gp">... </span> <span class="c1"># use that for the table name:</span> <span class="gp">... </span> <span class="n">table</span> <span class="o">=</span> <span class="s2">"user_table"</span> <span class="gp">...</span> <span class="gp">... </span> <span class="n">username</span> <span class="o">=</span> <span class="n">StringCol</span><span class="p">(</span><span class="n">alternateID</span><span class="o">=</span><span class="kc">True</span><span class="p">,</span> <span class="n">length</span><span class="o">=</span><span class="mi">20</span><span class="p">)</span> <span class="gp">... </span> <span class="c1"># We'd probably define more attributes, but we'll leave</span> <span class="gp">... </span> <span class="c1"># that exercise to the reader...</span> <span class="gp">...</span> <span class="gp">... </span> <span class="n">roles</span> <span class="o">=</span> <span class="n">RelatedJoin</span><span class="p">(</span><span class="s1">'Role'</span><span class="p">)</span> </pre></div> </div> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="k">class</span> <span class="nc">Role</span><span class="p">(</span><span class="n">SQLObject</span><span class="p">):</span> <span class="gp">...</span> <span class="gp">... </span> <span class="n">name</span> <span class="o">=</span> <span class="n">StringCol</span><span class="p">(</span><span class="n">alternateID</span><span class="o">=</span><span class="kc">True</span><span class="p">,</span> <span class="n">length</span><span class="o">=</span><span class="mi">20</span><span class="p">)</span> <span class="gp">...</span> <span class="gp">... </span> <span class="n">users</span> <span class="o">=</span> <span class="n">RelatedJoin</span><span class="p">(</span><span class="s1">'User'</span><span class="p">)</span> </pre></div> </div> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="n">User</span><span class="o">.</span><span class="n">createTable</span><span class="p">()</span> <span class="go">[]</span> <span class="gp">>>> </span><span class="n">Role</span><span class="o">.</span><span class="n">createTable</span><span class="p">()</span> <span class="go">[]</span> </pre></div> </div> <div class="admonition note"> <p class="first admonition-title">Note</p> <p class="last">The sqlmeta class is used to store different kinds of metadata (and override that metadata, like table). This is new in SQLObject 0.7. See the section <a class="reference internal" href="#class-sqlmeta">Class sqlmeta</a> for more information on how it works and what attributes have special meanings.</p> </div> <p>And usage:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="n">bob</span> <span class="o">=</span> <span class="n">User</span><span class="p">(</span><span class="n">username</span><span class="o">=</span><span class="s1">'bob'</span><span class="p">)</span> <span class="gp">>>> </span><span class="n">tim</span> <span class="o">=</span> <span class="n">User</span><span class="p">(</span><span class="n">username</span><span class="o">=</span><span class="s1">'tim'</span><span class="p">)</span> <span class="gp">>>> </span><span class="n">jay</span> <span class="o">=</span> <span class="n">User</span><span class="p">(</span><span class="n">username</span><span class="o">=</span><span class="s1">'jay'</span><span class="p">)</span> <span class="gp">>>> </span><span class="n">admin</span> <span class="o">=</span> <span class="n">Role</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s1">'admin'</span><span class="p">)</span> <span class="gp">>>> </span><span class="n">editor</span> <span class="o">=</span> <span class="n">Role</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s1">'editor'</span><span class="p">)</span> <span class="gp">>>> </span><span class="n">bob</span><span class="o">.</span><span class="n">addRole</span><span class="p">(</span><span class="n">admin</span><span class="p">)</span> <span class="gp">>>> </span><span class="n">bob</span><span class="o">.</span><span class="n">addRole</span><span class="p">(</span><span class="n">editor</span><span class="p">)</span> <span class="gp">>>> </span><span class="n">tim</span><span class="o">.</span><span class="n">addRole</span><span class="p">(</span><span class="n">editor</span><span class="p">)</span> <span class="gp">>>> </span><span class="n">bob</span><span class="o">.</span><span class="n">roles</span> <span class="go">[<Role 1 name='admin'>, <Role 2 name='editor'>]</span> <span class="gp">>>> </span><span class="n">tim</span><span class="o">.</span><span class="n">roles</span> <span class="go">[<Role 2 name='editor'>]</span> <span class="gp">>>> </span><span class="n">jay</span><span class="o">.</span><span class="n">roles</span> <span class="go">[]</span> <span class="gp">>>> </span><span class="n">admin</span><span class="o">.</span><span class="n">users</span> <span class="go">[<User 1 username='bob'>]</span> <span class="gp">>>> </span><span class="n">editor</span><span class="o">.</span><span class="n">users</span> <span class="go">[<User 1 username='bob'>, <User 2 username='tim'>]</span> </pre></div> </div> <p>In the process an intermediate table is created, <code class="docutils literal notranslate"><span class="pre">role_user</span></code>, which references both of the other classes. This table is never exposed as a class, and its rows do not have equivalent Python objects – this hides some of the nuisance of a many-to-many relationship.</p> <p>By the way, if you want to create an intermediate table of your own, maybe with additional columns, be aware that the standard SQLObject methods add/removesomething may not work as expected. Assuming that you are providing the join with the correct joinColumn and otherColumn arguments, be aware it’s not possible to insert extra data via such methods, nor will they set any default value.</p> <p>Let’s have an example: in the previous User/Role system, you’re creating a UserRole intermediate table, with the two columns containing the foreign keys for the MTM relationship, and an additional DateTimeCol defaulting to datetime.datetime.now : that column will stay empty when adding roles with the addRole method. If you want to get a list of rows from the intermediate table directly add a MultipleJoin to User or Role class.</p> <p>You may notice that the columns have the extra keyword argument <cite>alternateID</cite>. If you use <code class="docutils literal notranslate"><span class="pre">alternateID=True</span></code>, this means that the column uniquely identifies rows – like a username uniquely identifies a user. This identifier is in addition to the primary key (<code class="docutils literal notranslate"><span class="pre">id</span></code>), which is always present.</p> <div class="admonition note"> <p class="first admonition-title">Note</p> <p class="last">SQLObject has a strong requirement that the primary key be unique and <em>immutable</em>. 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, it is also <em>safe</em> to change it in the future.</p> </div> <p>A alternateID column creates a class method, like <code class="docutils literal notranslate"><span class="pre">byUsername</span></code> for a column named <code class="docutils literal notranslate"><span class="pre">username</span></code> (or you can use the <cite>alternateMethodName</cite> keyword argument to override this). Its use:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="n">User</span><span class="o">.</span><span class="n">byUsername</span><span class="p">(</span><span class="s1">'bob'</span><span class="p">)</span> <span class="go"><User 1 username='bob'></span> <span class="gp">>>> </span><span class="n">Role</span><span class="o">.</span><span class="n">byName</span><span class="p">(</span><span class="s1">'admin'</span><span class="p">)</span> <span class="go"><Role 1 name='admin'></span> </pre></div> </div> </div> <div class="section" id="selecting-objects-using-relationships"> <h3><a class="toc-backref" href="#id25">Selecting Objects Using Relationships</a><a class="headerlink" href="#selecting-objects-using-relationships" title="Permalink to this headline">¶</a></h3> <p>An select expression can refer to multiple classes, like:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="n">Person</span><span class="o">.</span><span class="n">_connection</span><span class="o">.</span><span class="n">debug</span> <span class="o">=</span> <span class="kc">False</span> <span class="c1"># Needed for doctests</span> <span class="gp">>>> </span><span class="n">peeps</span> <span class="o">=</span> <span class="n">Person</span><span class="o">.</span><span class="n">select</span><span class="p">(</span> <span class="gp">... </span> <span class="n">AND</span><span class="p">(</span><span class="n">Address</span><span class="o">.</span><span class="n">q</span><span class="o">.</span><span class="n">personID</span> <span class="o">==</span> <span class="n">Person</span><span class="o">.</span><span class="n">q</span><span class="o">.</span><span class="n">id</span><span class="p">,</span> <span class="gp">... </span> <span class="n">Address</span><span class="o">.</span><span class="n">q</span><span class="o">.</span><span class="n">zip</span><span class="o">.</span><span class="n">startswith</span><span class="p">(</span><span class="s1">'504'</span><span class="p">)))</span> <span class="gp">>>> </span><span class="nb">list</span><span class="p">(</span><span class="n">peeps</span><span class="p">)</span> <span class="go">[]</span> <span class="gp">>>> </span><span class="n">peeps</span> <span class="o">=</span> <span class="n">Person</span><span class="o">.</span><span class="n">select</span><span class="p">(</span> <span class="gp">... </span> <span class="n">AND</span><span class="p">(</span><span class="n">Address</span><span class="o">.</span><span class="n">q</span><span class="o">.</span><span class="n">personID</span> <span class="o">==</span> <span class="n">Person</span><span class="o">.</span><span class="n">q</span><span class="o">.</span><span class="n">id</span><span class="p">,</span> <span class="gp">... </span> <span class="n">Address</span><span class="o">.</span><span class="n">q</span><span class="o">.</span><span class="n">zip</span><span class="o">.</span><span class="n">startswith</span><span class="p">(</span><span class="s1">'554'</span><span class="p">)))</span> <span class="gp">>>> </span><span class="nb">list</span><span class="p">(</span><span class="n">peeps</span><span class="p">)</span> <span class="go">[<Person 2 firstName='Robert' middleInitial='Q' lastName='Hope Jr.'>]</span> </pre></div> </div> <p>It is also possible to use the <code class="docutils literal notranslate"><span class="pre">q</span></code> attribute when constructing complex queries, like:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="n">Person</span><span class="o">.</span><span class="n">_connection</span><span class="o">.</span><span class="n">debug</span> <span class="o">=</span> <span class="kc">False</span> <span class="c1"># Needed for doctests</span> <span class="gp">>>> </span><span class="n">peeps</span> <span class="o">=</span> <span class="n">Person</span><span class="o">.</span><span class="n">select</span><span class="p">(</span><span class="s2">"""address.person_id = person.id AND</span> <span class="gp">... </span><span class="s2"> address.zip LIKE '504%'"""</span><span class="p">,</span> <span class="gp">... </span> <span class="n">clauseTables</span><span class="o">=</span><span class="p">[</span><span class="s1">'address'</span><span class="p">])</span> </pre></div> </div> <p>Note that you have to use <code class="docutils literal notranslate"><span class="pre">clauseTables</span></code> if you use tables besides the one you are selecting from. If you use the <code class="docutils literal notranslate"><span class="pre">q</span></code> attributes SQLObject will automatically figure out what extra classes you might have used.</p> </div> <div class="section" id="class-sqlmeta"> <h3><a class="toc-backref" href="#id26">Class sqlmeta</a><a class="headerlink" href="#class-sqlmeta" title="Permalink to this headline">¶</a></h3> <p>This new class is available starting with SQLObject 0.7 and allows specifying metadata in a clearer way, without polluting the class namespace with more attributes.</p> <p>There are some special attributes that can be used inside this class that will change the behavior of the class that contains it. Those values are:</p> <dl class="docutils"> <dt><cite>table</cite>:</dt> <dd>The name of the table in the database. This is derived from <code class="docutils literal notranslate"><span class="pre">style</span></code> and the class name if no explicit name is given. If you don’t give a name and haven’t defined an alternative <code class="docutils literal notranslate"><span class="pre">style</span></code>, then the standard <cite>MixedCase</cite> to <cite>mixed_case</cite> translation is performed.</dd> <dt><cite>idName</cite>:</dt> <dd>The name of the primary key column in the database. This is derived from <code class="docutils literal notranslate"><span class="pre">style</span></code> if no explicit name is given. The default name is <code class="docutils literal notranslate"><span class="pre">id</span></code>.</dd> <dt><cite>idType</cite>:</dt> <dd>A function that coerces/normalizes IDs when setting IDs. This is <code class="docutils literal notranslate"><span class="pre">int</span></code> by default (all IDs are normalized to integers).</dd> <dt><cite>style</cite>:</dt> <dd>A style object – this object allows you to use other algorithms for translating between Python attribute and class names, and the database’s column and table names. See <a class="reference internal" href="#changing-the-naming-style">Changing the Naming Style</a> for more. It is an instance of the <cite>IStyle</cite> interface.</dd> <dt><cite>lazyUpdate</cite>:</dt> <dd>A boolean (default false). If true, then setting attributes on instances (or using <code class="docutils literal notranslate"><span class="pre">inst.set(.)</span></code> will not send <code class="docutils literal notranslate"><span class="pre">UPDATE</span></code> queries immediately (you must call <code class="docutils literal notranslate"><span class="pre">inst.syncUpdates()</span></code> or <code class="docutils literal notranslate"><span class="pre">inst.sync()</span></code> first).</dd> <dt><cite>defaultOrder</cite>:</dt> <dd>When selecting objects and not giving an explicit order, this attribute indicates the default ordering. It is like this value is passed to <code class="docutils literal notranslate"><span class="pre">.select()</span></code> and related methods; see those method’s documentation for details.</dd> <dt><cite>cacheValues</cite>:</dt> <dd><p class="first">A boolean (default true). If true, then the values in the row are cached as long as the instance is kept (and <code class="docutils literal notranslate"><span class="pre">inst.expire()</span></code> is not called).</p> <p class="last">If set to <cite>False</cite> then values for attributes from the database won’t be cached. So every time you access an attribute in the object the database will be queried for a value, i.e., a <code class="docutils literal notranslate"><span class="pre">SELECT</span></code> will be issued. If you want to handle concurrent access to the database from multiple processes then this is probably the way to do so.</p> </dd> <dt><cite>registry</cite>:</dt> <dd>Because SQLObject uses strings to relate classes, and these strings do not respect module names, name clashes will occur if you put different systems together. This string value serves as a namespace for classes.</dd> <dt><cite>fromDatabase</cite>:</dt> <dd>A boolean (default false). If true, then on class creation the database will be queried for the table’s columns, and any missing columns (possible all columns) will be added automatically. Please be warned that not all connections fully implement database introspection.</dd> <dt><cite>dbEncoding</cite>:</dt> <dd><a class="reference internal" href="#column-types">UnicodeCol</a> looks up <cite>sqlmeta.dbEncoding</cite> if <cite>column.dbEncoding</cite> is <code class="docutils literal notranslate"><span class="pre">None</span></code> (if <cite>sqlmeta.dbEncoding</cite> is <code class="docutils literal notranslate"><span class="pre">None</span></code> <a class="reference internal" href="#column-types">UnicodeCol</a> looks up <cite>connection.dbEncoding</cite> and if <cite>dbEncoding</cite> isn’t defined anywhere it defaults to <code class="docutils literal notranslate"><span class="pre">"utf-8"</span></code>). For Python 3 there must be one encoding for connection - do not define different columns with different encodings, it’s not implemented.</dd> </dl> <p>The following attributes provide introspection but should not be set directly - see <a class="reference internal" href="#runtime-column-and-join-changes">Runtime Column and Join Changes</a> for dynamically modifying these class elements.</p> <dl class="docutils"> <dt><cite>columns</cite>:</dt> <dd>A dictionary of <code class="docutils literal notranslate"><span class="pre">{columnName:</span> <span class="pre">anSOColInstance}</span></code>. You can get information on the columns via this read-only attribute.</dd> <dt><cite>columnList</cite>:</dt> <dd>A list of the values in <code class="docutils literal notranslate"><span class="pre">columns</span></code>. Sometimes a stable, ordered version of the columns is necessary; this is used for that.</dd> <dt><cite>columnDefinitions</cite>:</dt> <dd>A dictionary like <code class="docutils literal notranslate"><span class="pre">columns</span></code>, but contains the original column definitions (which are not class-specific, and have no logic).</dd> <dt><cite>joins</cite>:</dt> <dd>A list of all the Join objects for this class.</dd> <dt><cite>indexes</cite>:</dt> <dd>A list of all the indexes for this class.</dd> <dt><cite>createSQL</cite>:</dt> <dd>SQL queries run after table creation. createSQL can be a string with a single SQL command, a list of SQL commands, or a dictionary with keys that are dbNames and values that are either single SQL command string or a list of SQL commands. This is usually for ALTER TABLE commands.</dd> </dl> <p>There is also one instance attribute:</p> <dl class="docutils"> <dt><cite>expired</cite>:</dt> <dd>A boolean. If true, then the next time this object’s column attributes are accessed a query will be run.</dd> </dl> <p>While in previous versions of SQLObject those attributes were defined directly at the class that will map your database data to Python and all of them were prefixed with an underscore, now it is suggested that you change your code to this new style. The old way was removed in SQLObject 0.8.</p> <p>Please note: when using InheritedSQLObject, sqlmeta attributes don’t get inherited, e.g. you can’t access via the sqlmeta.columns dictionary the parent’s class column objects.</p> <div class="section" id="using-sqlmeta"> <h4><a class="toc-backref" href="#id27">Using sqlmeta</a><a class="headerlink" href="#using-sqlmeta" title="Permalink to this headline">¶</a></h4> <p>To use sqlmeta you should write code like this example:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="k">class</span> <span class="nc">MyClass</span><span class="p">(</span><span class="n">SQLObject</span><span class="p">):</span> <span class="k">class</span> <span class="nc">sqlmeta</span><span class="p">:</span> <span class="n">lazyUpdate</span> <span class="o">=</span> <span class="kc">True</span> <span class="n">cacheValues</span> <span class="o">=</span> <span class="kc">False</span> <span class="n">columnA</span> <span class="o">=</span> <span class="n">StringCol</span><span class="p">()</span> <span class="n">columnB</span> <span class="o">=</span> <span class="n">IntCol</span><span class="p">()</span> <span class="k">def</span> <span class="nf">_set_attr1</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">value</span><span class="p">):</span> <span class="c1"># do something with value</span> <span class="k">def</span> <span class="nf">_get_attr1</span><span class="p">(</span><span class="bp">self</span><span class="p">):</span> <span class="c1"># do something to retrieve value</span> </pre></div> </div> <p>The above definition is creating a table <code class="docutils literal notranslate"><span class="pre">my_class</span></code> (the name may be different if you change the <code class="docutils literal notranslate"><span class="pre">style</span></code> used) with two columns called columnA and columnB. There’s also a third field that can be accessed using <code class="docutils literal notranslate"><span class="pre">MyClass.attr1</span></code>. The sqlmeta class is changing the behavior of <code class="docutils literal notranslate"><span class="pre">MyClass</span></code> so that it will perform lazy updates (you’ll have to call the <code class="docutils literal notranslate"><span class="pre">.sync()</span></code> method to write the updates to the database) and it is also telling that <code class="docutils literal notranslate"><span class="pre">MyClass</span></code> won’t have any cache, so that every time you ask for some information it will be retrieved from the database.</p> </div> <div class="section" id="j-magic"> <h4><a class="toc-backref" href="#id28">j-magic</a><a class="headerlink" href="#j-magic" title="Permalink to this headline">¶</a></h4> <p>There is a magic attribute <cite>j</cite> similar to <a class="reference internal" href="#q-magic">q</a> with attributes for ForeignKey and SQLMultipleJoin/SQLRelatedJoin, providing a shorthand for the SQLBuilder join expressions to traverse the given relationship. For example, for a ForeignKey AClass.j.someB is equivalent to (AClass.q.someBID==BClass.q.id), as is BClass.j.someAs for the matching SQLMultipleJoin.</p> </div> </div> <div class="section" id="sqlobject-class"> <h3><a class="toc-backref" href="#id29">SQLObject Class</a><a class="headerlink" href="#sqlobject-class" title="Permalink to this headline">¶</a></h3> <p>There is one special attribute - <cite>_connection</cite>. It is the connection defined for the table.</p> <dl class="docutils"> <dt><cite>_connection</cite>:</dt> <dd><p class="first">The connection object to use, from <cite>DBConnection</cite>. You can also set the variable <cite>__connection__</cite> in the enclosing module and it will be picked up (be sure to define <cite>__connection__</cite> before your class). You can also pass a connection object in at instance creation time, as described in <a class="reference internal" href="#transactions">transactions</a>.</p> <p class="last">If you have defined <cite>sqlhub.processConnection</cite> then this attribute can be omitted from your class and the sqlhub will be used instead. If you have several classes using the same connection that might be an advantage, besides saving a lot of typing.</p> </dd> </dl> </div> <div class="section" id="customizing-the-objects"> <h3><a class="toc-backref" href="#id30">Customizing the Objects</a><a class="headerlink" href="#customizing-the-objects" title="Permalink to this headline">¶</a></h3> <p>While we haven’t done so in the examples, you can include your own methods in the class definition. Writing your own methods should be obvious enough (just do so like in any other class), but there are some other details to be aware of.</p> <div class="section" id="initializing-the-objects"> <h4><a class="toc-backref" href="#id31">Initializing the Objects</a><a class="headerlink" href="#initializing-the-objects" title="Permalink to this headline">¶</a></h4> <p>There are two ways SQLObject instances can come into existence: 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 role of <cite>__init__</cite> a little confusing.</p> <p>In general, you should not touch <cite>__init__</cite>. Instead use the <cite>_init</cite> method, which is called after an object is fetched or inserted. This method has the signature <code class="docutils literal notranslate"><span class="pre">_init(self,</span> <span class="pre">id,</span> <span class="pre">connection=None,</span> <span class="pre">selectResults=None)</span></code>, though you may just want to use <code class="docutils literal notranslate"><span class="pre">_init(self,</span> <span class="pre">*args,</span> <span class="pre">**kw)</span></code>. <strong>Note:</strong> don’t forget to call <code class="docutils literal notranslate"><span class="pre">SQLObject._init(self,</span> <span class="pre">*args,</span> <span class="pre">**kw)</span></code> if you override the method!</p> </div> <div class="section" id="adding-magic-attributes-properties"> <h4><a class="toc-backref" href="#id32">Adding Magic Attributes (properties)</a><a class="headerlink" href="#adding-magic-attributes-properties" title="Permalink to this headline">¶</a></h4> <p>You can use all the normal techniques for defining methods in this class, including <cite>classmethod</cite>, <cite>staticmethod</cite>, and <cite>property</cite>, but you can also use a shortcut. If you have a method that’s name starts with <code class="docutils literal notranslate"><span class="pre">_set_</span></code>, <code class="docutils literal notranslate"><span class="pre">_get_</span></code>, <code class="docutils literal notranslate"><span class="pre">_del_</span></code>, or <code class="docutils literal notranslate"><span class="pre">_doc_</span></code>, it will be used to create a property. So, for instance, say you have images stored under the ID of the person in the <code class="docutils literal notranslate"><span class="pre">/var/people/images</span></code> directory:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="k">class</span> <span class="nc">Person</span><span class="p">(</span><span class="n">SQLObject</span><span class="p">):</span> <span class="c1"># ...</span> <span class="k">def</span> <span class="nf">imageFilename</span><span class="p">(</span><span class="bp">self</span><span class="p">):</span> <span class="k">return</span> <span class="s1">'images/person-</span><span class="si">%s</span><span class="s1">.jpg'</span> <span class="o">%</span> <span class="bp">self</span><span class="o">.</span><span class="n">id</span> <span class="k">def</span> <span class="nf">_get_image</span><span class="p">(</span><span class="bp">self</span><span class="p">):</span> <span class="k">if</span> <span class="ow">not</span> <span class="n">os</span><span class="o">.</span><span class="n">path</span><span class="o">.</span><span class="n">exists</span><span class="p">(</span><span class="bp">self</span><span class="o">.</span><span class="n">imageFilename</span><span class="p">()):</span> <span class="k">return</span> <span class="kc">None</span> <span class="n">f</span> <span class="o">=</span> <span class="nb">open</span><span class="p">(</span><span class="bp">self</span><span class="o">.</span><span class="n">imageFilename</span><span class="p">())</span> <span class="n">v</span> <span class="o">=</span> <span class="n">f</span><span class="o">.</span><span class="n">read</span><span class="p">()</span> <span class="n">f</span><span class="o">.</span><span class="n">close</span><span class="p">()</span> <span class="k">return</span> <span class="n">v</span> <span class="k">def</span> <span class="nf">_set_image</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">value</span><span class="p">):</span> <span class="c1"># assume we get a string for the image</span> <span class="n">f</span> <span class="o">=</span> <span class="nb">open</span><span class="p">(</span><span class="bp">self</span><span class="o">.</span><span class="n">imageFilename</span><span class="p">(),</span> <span class="s1">'w'</span><span class="p">)</span> <span class="n">f</span><span class="o">.</span><span class="n">write</span><span class="p">(</span><span class="n">value</span><span class="p">)</span> <span class="n">f</span><span class="o">.</span><span class="n">close</span><span class="p">()</span> <span class="k">def</span> <span class="nf">_del_image</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">value</span><span class="p">):</span> <span class="c1"># We usually wouldn't include a method like this, but for</span> <span class="c1"># instructional purposes...</span> <span class="n">os</span><span class="o">.</span><span class="n">unlink</span><span class="p">(</span><span class="bp">self</span><span class="o">.</span><span class="n">imageFilename</span><span class="p">())</span> </pre></div> </div> <p>Later, you can use the <code class="docutils literal notranslate"><span class="pre">.image</span></code> property just like an attribute, and the changes will be reflected in the filesystem by calling these 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).</p> <p>You can also pass an <code class="docutils literal notranslate"><span class="pre">image</span></code> keyword argument to the constructor or the <cite>set</cite> method, like <code class="docutils literal notranslate"><span class="pre">Person(...,</span> <span class="pre">image=imageText)</span></code>.</p> <p>All of the methods (<code class="docutils literal notranslate"><span class="pre">_get_</span></code>, <code class="docutils literal notranslate"><span class="pre">_set_</span></code>, etc) are optional – you can use any one of them without using the others. So you could define just a <code class="docutils literal notranslate"><span class="pre">_get_attr</span></code> method so that <code class="docutils literal notranslate"><span class="pre">attr</span></code> was read-only.</p> </div> <div class="section" id="overriding-column-attributes"> <h4><a class="toc-backref" href="#id33">Overriding Column Attributes</a><a class="headerlink" href="#overriding-column-attributes" title="Permalink to this headline">¶</a></h4> <p>It’s a little more complicated if you want to override the behavior of an database column attribute. For instance, imagine there’s special code you want to run whenever someone’s name changes. In many systems you’d do some custom code, then call the superclass’s code. But the superclass (<code class="docutils literal notranslate"><span class="pre">SQLObject</span></code>) doesn’t know anything about the column in your subclass. It’s even worse with properties.</p> <p>SQLObject creates methods like <code class="docutils literal notranslate"><span class="pre">_set_lastName</span></code> for each of your columns, but again you can’t use this, since there’s no superclass to reference (and you can’t write <code class="docutils literal notranslate"><span class="pre">SQLObject._set_lastName(...)</span></code>, because the SQLObject class doesn’t know about your class’s columns). You want to override that <code class="docutils literal notranslate"><span class="pre">_set_lastName</span></code> method yourself.</p> <p>To deal with this, SQLObject creates two methods for each getter and setter, for example: <code class="docutils literal notranslate"><span class="pre">_set_lastName</span></code> and <code class="docutils literal notranslate"><span class="pre">_SO_set_lastName</span></code>. So to intercept all changes to <code class="docutils literal notranslate"><span class="pre">lastName</span></code>:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="k">class</span> <span class="nc">Person</span><span class="p">(</span><span class="n">SQLObject</span><span class="p">):</span> <span class="n">lastName</span> <span class="o">=</span> <span class="n">StringCol</span><span class="p">()</span> <span class="n">firstName</span> <span class="o">=</span> <span class="n">StringCol</span><span class="p">()</span> <span class="k">def</span> <span class="nf">_set_lastName</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">value</span><span class="p">):</span> <span class="bp">self</span><span class="o">.</span><span class="n">notifyLastNameChange</span><span class="p">(</span><span class="n">value</span><span class="p">)</span> <span class="bp">self</span><span class="o">.</span><span class="n">_SO_set_lastName</span><span class="p">(</span><span class="n">value</span><span class="p">)</span> </pre></div> </div> <p>Or perhaps you want to constrain a phone numbers to be actual digits, and of proper length, and make the formatting nice:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="kn">import</span> <span class="nn">re</span> <span class="k">class</span> <span class="nc">PhoneNumber</span><span class="p">(</span><span class="n">SQLObject</span><span class="p">):</span> <span class="n">phoneNumber</span> <span class="o">=</span> <span class="n">StringCol</span><span class="p">(</span><span class="n">length</span><span class="o">=</span><span class="mi">30</span><span class="p">)</span> <span class="n">_garbageCharactersRE</span> <span class="o">=</span> <span class="n">re</span><span class="o">.</span><span class="n">compile</span><span class="p">(</span><span class="sa">r</span><span class="s1">'[\-\.\(\) ]'</span><span class="p">)</span> <span class="n">_phoneNumberRE</span> <span class="o">=</span> <span class="n">re</span><span class="o">.</span><span class="n">compile</span><span class="p">(</span><span class="sa">r</span><span class="s1">'^[0-9]+$'</span><span class="p">)</span> <span class="k">def</span> <span class="nf">_set_phoneNumber</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">value</span><span class="p">):</span> <span class="n">value</span> <span class="o">=</span> <span class="bp">self</span><span class="o">.</span><span class="n">_garbageCharactersRE</span><span class="o">.</span><span class="n">sub</span><span class="p">(</span><span class="s1">''</span><span class="p">,</span> <span class="n">value</span><span class="p">)</span> <span class="k">if</span> <span class="ow">not</span> <span class="nb">len</span><span class="p">(</span><span class="n">value</span><span class="p">)</span> <span class="o">>=</span> <span class="mi">10</span><span class="p">:</span> <span class="k">raise</span> <span class="ne">ValueError</span><span class="p">(</span> <span class="s1">'Phone numbers must be at least 10 digits long'</span><span class="p">)</span> <span class="k">if</span> <span class="ow">not</span> <span class="bp">self</span><span class="o">.</span><span class="n">_phoneNumberRE</span><span class="o">.</span><span class="n">match</span><span class="p">(</span><span class="n">value</span><span class="p">):</span> <span class="k">raise</span> <span class="ne">ValueError</span><span class="p">,</span> <span class="s1">'Phone numbers can contain only digits'</span> <span class="bp">self</span><span class="o">.</span><span class="n">_SO_set_phoneNumber</span><span class="p">(</span><span class="n">value</span><span class="p">)</span> <span class="k">def</span> <span class="nf">_get_phoneNumber</span><span class="p">(</span><span class="bp">self</span><span class="p">):</span> <span class="n">value</span> <span class="o">=</span> <span class="bp">self</span><span class="o">.</span><span class="n">_SO_get_phoneNumber</span><span class="p">()</span> <span class="n">number</span> <span class="o">=</span> <span class="s1">'(</span><span class="si">%s</span><span class="s1">) </span><span class="si">%s</span><span class="s1">-</span><span class="si">%s</span><span class="s1">'</span> <span class="o">%</span> <span class="p">(</span><span class="n">value</span><span class="p">[</span><span class="mi">0</span><span class="p">:</span><span class="mi">3</span><span class="p">],</span> <span class="n">value</span><span class="p">[</span><span class="mi">3</span><span class="p">:</span><span class="mi">6</span><span class="p">],</span> <span class="n">value</span><span class="p">[</span><span class="mi">6</span><span class="p">:</span><span class="mi">10</span><span class="p">])</span> <span class="k">if</span> <span class="nb">len</span><span class="p">(</span><span class="n">value</span><span class="p">)</span> <span class="o">></span> <span class="mi">10</span><span class="p">:</span> <span class="n">number</span> <span class="o">+=</span> <span class="s1">' ext.</span><span class="si">%s</span><span class="s1">'</span> <span class="o">%</span> <span class="n">value</span><span class="p">[</span><span class="mi">10</span><span class="p">:]</span> <span class="k">return</span> <span class="n">number</span> </pre></div> </div> <div class="admonition note"> <p class="first admonition-title">Note</p> <p>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.</p> <p class="last">Also note while these conversions will take place when getting and setting the column, in queries the conversions will not take place. So if you convert the value from a “Pythonic” representation to a “SQLish” representation, your queries (when using <code class="docutils literal notranslate"><span class="pre">.select()</span></code> and <code class="docutils literal notranslate"><span class="pre">.selectBy()</span></code>) will have to be in terms of the SQL/Database representation (as those commands generate SQL that is run on the database).</p> </div> </div> <div class="section" id="undefined-attributes"> <h4><a class="toc-backref" href="#id34">Undefined attributes</a><a class="headerlink" href="#undefined-attributes" title="Permalink to this headline">¶</a></h4> <p>There’s one more thing worth telling, because you may something get strange results when making a typo. SQLObject won’t ever complain or raise any error when setting a previously undefined attribute; it will simply set it, without making any change to the database, i.e: it will work as any other attribute you set on any Python class, it will ‘forget’ it is a SQLObject class.</p> <p>This may sometimes be a problem: if you have got a ‘name’ attribute and you you write <code class="docutils literal notranslate"><span class="pre">a.namme="Victor"</span></code> once, when setting it, you’ll get no error, no warning, nothing at all, and you may get crazy at understanding why you don’t get that value set in your DB.</p> </div> </div> </div> <div class="section" id="reference"> <h2><a class="toc-backref" href="#id35">Reference</a><a class="headerlink" href="#reference" title="Permalink to this headline">¶</a></h2> <p>The instructions above should tell you enough to get you started, and be useful for many situations. Now we’ll show how to specify the class more completely.</p> <div class="section" id="col-class-specifying-columns"> <h3><a class="toc-backref" href="#id36">Col Class: Specifying Columns</a><a class="headerlink" href="#col-class-specifying-columns" title="Permalink to this headline">¶</a></h3> <p>The list of columns is a list of <cite>Col</cite> objects. These objects don’t have functionality in themselves, but give you a way to specify the column.</p> <dl class="docutils"> <dt><cite>dbName</cite>:</dt> <dd>This is the name of the column in the database. If you don’t give a name, your Pythonic name will be converted from mixed-case to underscore-separated.</dd> <dt><cite>default</cite>:</dt> <dd>The default value for this column. Used when creating a new row. If you give a callable object or function, the function will be called, and the return value will be used. So you can give <code class="docutils literal notranslate"><span class="pre">DateTimeCol.now</span></code> to make the default value be the current time. Or you can use <code class="docutils literal notranslate"><span class="pre">sqlbuilder.func.NOW()</span></code> to have the database use the <code class="docutils literal notranslate"><span class="pre">NOW()</span></code> function internally. If you don’t give a default there will be an exception if this column isn’t specified in the call to <cite>new</cite>.</dd> <dt><cite>defaultSQL</cite>:</dt> <dd><code class="docutils literal notranslate"><span class="pre">DEFAULT</span></code> SQL attribute.</dd> <dt><cite>alternateID</cite>:</dt> <dd><p class="first">This boolean (default False) indicates if the column can be used as an ID for the field (for instance, a username), though it is not a primary key. If so a class method will be added, like <code class="docutils literal notranslate"><span class="pre">byUsername</span></code> which will return that object. Use <cite>alternateMethodName</cite> if you don’t like the <code class="docutils literal notranslate"><span class="pre">by*</span></code> name (e.g. <code class="docutils literal notranslate"><span class="pre">alternateMethodName="username"</span></code>).</p> <p class="last">The column should be declared <code class="docutils literal notranslate"><span class="pre">UNIQUE</span></code> in your table schema.</p> </dd> <dt><cite>unique</cite>:</dt> <dd>If true, when SQLObject creates a table it will declare this column to be <code class="docutils literal notranslate"><span class="pre">UNIQUE</span></code>.</dd> <dt><cite>notNone</cite>:</dt> <dd>If true, None/<code class="docutils literal notranslate"><span class="pre">NULL</span></code> is not allowed for this column. Useful if you are using SQLObject to create your tables.</dd> <dt><cite>sqlType</cite>:</dt> <dd>The SQL type for this column (like <code class="docutils literal notranslate"><span class="pre">INT</span></code>, <code class="docutils literal notranslate"><span class="pre">BOOLEAN</span></code>, etc). You can use classes (defined below) for this, but if those don’t work it’s sometimes easiest just to use <cite>sqlType</cite>. Only necessary if SQLObject is creating your tables.</dd> <dt><cite>validator</cite>:</dt> <dd><a class="reference external" href="http://formencode.org/">formencode</a>-like <a class="reference external" href="http://www.formencode.org/en/latest/Validator.html">validator</a>. Making long story short, this is an object that provides <code class="docutils literal notranslate"><span class="pre">to_python()</span></code> and <code class="docutils literal notranslate"><span class="pre">from_python()</span></code> to validate <em>and</em> convert (adapt or cast) the values when they are read/written from/to the database. You should see <a class="reference external" href="http://formencode.org/">formencode</a> <a class="reference external" href="http://www.formencode.org/en/latest/Validator.html">validator</a> documentation for more details. This validator is appended to the end of the list of the list of column validators. If the column has a list of validators their <code class="docutils literal notranslate"><span class="pre">from_python()</span></code> methods are ran from the beginnig of the list to the end; <code class="docutils literal notranslate"><span class="pre">to_python()</span></code> in the reverse order. That said, <code class="docutils literal notranslate"><span class="pre">from_python()</span></code> method of this validator is called last, after all validators in the list; <code class="docutils literal notranslate"><span class="pre">to_python()</span></code> is called first.</dd> <dt><cite>validator2</cite>:</dt> <dd>Another validator. It is inserted in the beginning of the list of the list of validators, i.e. its <code class="docutils literal notranslate"><span class="pre">from_python()</span></code> method is called first; <code class="docutils literal notranslate"><span class="pre">to_python()</span></code> last.</dd> </dl> <div class="section" id="column-types"> <h4><a class="toc-backref" href="#id37">Column Types</a><a class="headerlink" href="#column-types" title="Permalink to this headline">¶</a></h4> <p>The <cite>ForeignKey</cite> class should be used instead of <cite>Col</cite> when the column is a reference to another table/class. It is generally used like <code class="docutils literal notranslate"><span class="pre">ForeignKey('Role')</span></code>, in this instance to create a reference to a table <cite>Role</cite>. This is largely equivalent to <code class="docutils literal notranslate"><span class="pre">Col(foreignKey='Role',</span> <span class="pre">sqlType='INT')</span></code>. Two attributes will generally be created, <code class="docutils literal notranslate"><span class="pre">role</span></code>, which returns a <cite>Role</cite> instance, and <code class="docutils literal notranslate"><span class="pre">roleID</span></code>, which returns an integer ID for the related role.</p> <p>There are some other subclasses of <cite>Col</cite>. These are used to indicate different types of columns, when SQLObject creates your tables.</p> <dl class="docutils"> <dt><cite>BLOBCol</cite>:</dt> <dd>A column for binary data. Presently works only with MySQL, PostgreSQL and SQLite backends.</dd> <dt><cite>BoolCol</cite>:</dt> <dd>Will create a <code class="docutils literal notranslate"><span class="pre">BOOLEAN</span></code> column in Postgres, or <code class="docutils literal notranslate"><span class="pre">INT</span></code> in other databases. It will also convert values to <code class="docutils literal notranslate"><span class="pre">"t"/"f"</span></code> or <code class="docutils literal notranslate"><span class="pre">0/1</span></code> according to the database backend.</dd> <dt><cite>CurrencyCol</cite>:</dt> <dd>Equivalent to <code class="docutils literal notranslate"><span class="pre">DecimalCol(size=10,</span> <span class="pre">precision=2)</span></code>. WARNING: as DecimalCol MAY NOT return precise numbers, this column may share the same behavior. Please read the DecimalCol warning.</dd> <dt><cite>DateTimeCol</cite>:</dt> <dd>A date and time (usually returned as an datetime or mxDateTime object).</dd> <dt><cite>DateCol</cite>:</dt> <dd>A date (usually returned as an datetime or mxDateTime object).</dd> <dt><cite>TimeCol</cite>:</dt> <dd>A time (usually returned as an datetime or mxDateTime object).</dd> <dt><cite>TimestampCol</cite>:</dt> <dd>Supports MySQL TIMESTAMP type.</dd> <dt><cite>DecimalCol</cite>:</dt> <dd>Base-10, precise number. Uses the keyword arguments <cite>size</cite> for number of digits stored, and <cite>precision</cite> for the number of digits after the decimal point. WARNING: it may happen that DecimalCol values, although correctly stored in the DB, are returned as floats instead of decimals. For example, due to the <a class="reference external" href="http://sqlite.org/datatype3.html#affinity">type affinity</a> SQLite stores decimals as integers or floats (NUMERIC storage class). You should test with your database adapter, and you should try importing the Decimal type and your DB adapter before importing SQLObject.</dd> </dl> <dl class="docutils"> <dt><cite>DecimalStringCol</cite>:</dt> <dd>Similar to <cite>DecimalCol</cite> but stores data as strings to work around problems in some drivers and type affinity problem in SQLite. As it stores data as strings the column cannot be used in SQL expressions (column1 + column2) and probably will has problems with ORDER BY.</dd> <dt><cite>EnumCol</cite>:</dt> <dd><p class="first">One of several string values – give the possible strings as a list, with the <cite>enumValues</cite> keyword argument. MySQL has a native <code class="docutils literal notranslate"><span class="pre">ENUM</span></code> type, but will work with other databases too (storage just won’t be as efficient).</p> <p class="last">For PostgreSQL, EnumCol’s are implemented using check constraints. Due to the way PostgreSQL handles check constraints involving NULL, specifying None as a member of an EnumCol will effectively mean that, at the SQL level, the check constraint will be ignored (see <a class="reference external" href="http://archives.postgresql.org/pgsql-sql/2004-12/msg00065.php">http://archives.postgresql.org/pgsql-sql/2004-12/msg00065.php</a> for more details).</p> </dd> <dt><cite>SetCol</cite>:</dt> <dd>Supports MySQL SET type.</dd> <dt><cite>FloatCol</cite>:</dt> <dd>Floats.</dd> <dt><cite>ForeignKey</cite>:</dt> <dd>A key to another table/class. Use like <code class="docutils literal notranslate"><span class="pre">user</span> <span class="pre">=</span> <span class="pre">ForeignKey('User')</span></code>. It can check for referential integrity using the keyword argument <cite>cascade</cite>, please see <a class="reference internal" href="#foreignkey">ForeignKey</a> for details.</dd> <dt><cite>IntCol</cite>:</dt> <dd>Integers.</dd> <dt><cite>JsonbCol</cite>:</dt> <dd>A column for jsonb objects. Only supported on Postgres. Any Python object that can be serialized with json.dumps can be stored.</dd> <dt><cite>JSONCol</cite>:</dt> <dd>A universal json column that converts simple Python objects (None, bool, int, float, long, dict, list, str/unicode to/from JSON using json.dumps/loads. A subclass of StringCol.</dd> <dt><cite>PickleCol</cite>:</dt> <dd>An extension of BLOBCol; this column can store/retrieve any Python object; it actually (un)pickles the object from/to string and stores/retrieves the string. One can get and set the value of the column but cannot search (use it in WHERE).</dd> <dt><cite>StringCol</cite>:</dt> <dd><p class="first">A string (character) column. Extra keywords:</p> <dl class="last docutils"> <dt><cite>length</cite>:</dt> <dd>If given, the type will be something like <code class="docutils literal notranslate"><span class="pre">VARCHAR(length)</span></code>. If not given, then <code class="docutils literal notranslate"><span class="pre">TEXT</span></code> is assumed (i.e., lengthless).</dd> <dt><cite>varchar</cite>:</dt> <dd>A boolean; if you have a length, differentiates between <code class="docutils literal notranslate"><span class="pre">CHAR</span></code> and <code class="docutils literal notranslate"><span class="pre">VARCHAR</span></code>, default True, i.e., use <code class="docutils literal notranslate"><span class="pre">VARCHAR</span></code>.</dd> </dl> </dd> <dt><cite>UnicodeCol</cite>:</dt> <dd><p class="first">A subclass of <cite>StringCol</cite>. Also accepts a <cite>dbEncoding</cite> keyword argument, it defaults to <code class="docutils literal notranslate"><span class="pre">None</span></code> which means to lookup <cite>dbEncoding</cite> in <a class="reference internal" href="#class-sqlmeta">sqlmeta</a> and connection, and if <cite>dbEncoding</cite> isn’t defined anywhere it defaults to <code class="docutils literal notranslate"><span class="pre">"utf-8"</span></code>. Values coming in and out from the database will be encoded and decoded. <strong>Note</strong>: there are some limitations on using UnicodeCol in queries:</p> <ul class="simple"> <li>only simple q-magic fields are supported; no expressions;</li> <li>only == and != operators are supported;</li> </ul> <p>The following code works:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">MyTable</span><span class="o">.</span><span class="n">select</span><span class="p">(</span><span class="sa">u</span><span class="s1">'value'</span> <span class="o">==</span> <span class="n">MyTable</span><span class="o">.</span><span class="n">q</span><span class="o">.</span><span class="n">name</span><span class="p">)</span> <span class="n">MyTable</span><span class="o">.</span><span class="n">select</span><span class="p">(</span><span class="n">MyTable</span><span class="o">.</span><span class="n">q</span><span class="o">.</span><span class="n">name</span> <span class="o">!=</span> <span class="sa">u</span><span class="s1">'value'</span><span class="p">)</span> <span class="n">MyTable</span><span class="o">.</span><span class="n">select</span><span class="p">(</span><span class="n">OR</span><span class="p">(</span><span class="n">MyTable</span><span class="o">.</span><span class="n">q</span><span class="o">.</span><span class="n">col1</span> <span class="o">==</span> <span class="sa">u</span><span class="s1">'value1'</span><span class="p">,</span> <span class="n">MyTable</span><span class="o">.</span><span class="n">q</span><span class="o">.</span><span class="n">col2</span> <span class="o">!=</span> <span class="sa">u</span><span class="s1">'value2'</span><span class="p">))</span> <span class="n">MyTable</span><span class="o">.</span><span class="n">selectBy</span><span class="p">(</span><span class="n">name</span> <span class="o">=</span> <span class="sa">u</span><span class="s1">'value'</span><span class="p">)</span> <span class="n">MyTable</span><span class="o">.</span><span class="n">selectBy</span><span class="p">(</span><span class="n">col1</span><span class="o">=</span><span class="sa">u</span><span class="s1">'value1'</span><span class="p">,</span> <span class="n">col2</span><span class="o">=</span><span class="sa">u</span><span class="s1">'value2'</span><span class="p">)</span> <span class="n">MyTable</span><span class="o">.</span><span class="n">byCol1</span><span class="p">(</span><span class="sa">u</span><span class="s1">'value1'</span><span class="p">)</span> <span class="c1"># if col1 is an alternateID</span> </pre></div> </div> <p>The following does not work:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">MyTable</span><span class="o">.</span><span class="n">select</span><span class="p">((</span><span class="n">MyTable</span><span class="o">.</span><span class="n">q</span><span class="o">.</span><span class="n">name</span> <span class="o">+</span> <span class="n">MyTable</span><span class="o">.</span><span class="n">q</span><span class="o">.</span><span class="n">surname</span><span class="p">)</span> <span class="o">==</span> <span class="sa">u</span><span class="s1">'value'</span><span class="p">)</span> </pre></div> </div> <p>In that case you must apply the encoding yourself:</p> <div class="last highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">MyTable</span><span class="o">.</span><span class="n">select</span><span class="p">((</span><span class="n">MyTable</span><span class="o">.</span><span class="n">q</span><span class="o">.</span><span class="n">name</span> <span class="o">+</span> <span class="n">MyTable</span><span class="o">.</span><span class="n">q</span><span class="o">.</span><span class="n">surname</span><span class="p">)</span> <span class="o">==</span> <span class="sa">u</span><span class="s1">'value'</span><span class="o">.</span><span class="n">encode</span><span class="p">(</span><span class="n">dbEncoding</span><span class="p">))</span> </pre></div> </div> </dd> <dt><cite>UuidCol</cite>:</dt> <dd>A column for UUID. On Postgres uses ‘UUID’ data type, on all other backends uses VARCHAR(36).</dd> </dl> </div> </div> <div class="section" id="relationships-between-classes-tables"> <h3><a class="toc-backref" href="#id38">Relationships Between Classes/Tables</a><a class="headerlink" href="#relationships-between-classes-tables" title="Permalink to this headline">¶</a></h3> <div class="section" id="foreignkey"> <h4><a class="toc-backref" href="#id39">ForeignKey</a><a class="headerlink" href="#foreignkey" title="Permalink to this headline">¶</a></h4> <p>You can use the <cite>ForeignKey</cite> to handle foreign references in a table, but for back references and many-to-many relationships you’ll use joins.</p> <p><cite>ForeignKey</cite> allows you to specify referential integrity using the keyword <cite>cascade</cite>, which can have these values:</p> <dl class="docutils"> <dt><cite>None</cite>:</dt> <dd>No action is taken on related deleted columns (this is the default). Following the Person/Address example, if you delete the object <cite>Person</cite> with id 1 (John Doe), the <cite>Address</cite> with id 1 (123 W Main St) will be kept untouched (with <code class="docutils literal notranslate"><span class="pre">personID=1</span></code>).</dd> <dt><cite>False</cite>:</dt> <dd>Deletion of an object that has other objects related to it using a <cite>ForeignKey</cite> will fail (sets <code class="docutils literal notranslate"><span class="pre">ON</span> <span class="pre">DELETE</span> <span class="pre">RESTRICT</span></code>). Following the Person/Address example, if you delete the object <cite>Person</cite> with id 1 (John Doe) a <cite>SQLObjectIntegrityError</cite> exception will be raised, because the <cite>Address</cite> with id 1 (123 W Main St) has a reference (<code class="docutils literal notranslate"><span class="pre">personID=1</span></code>) to it.</dd> <dt><cite>True</cite>:</dt> <dd>Deletion of an object that has other objects related to it using a <cite>ForeignKey</cite> will delete all the related objects too (sets <code class="docutils literal notranslate"><span class="pre">ON</span> <span class="pre">DELETE</span> <span class="pre">CASCADE</span></code>). Following the Person/Address example, if you delete the object <cite>Person</cite> with id 1 (John Doe), the <cite>Address</cite> with id 1 (123 W Main St) will be deleted too.</dd> <dt><cite>‘null’</cite>:</dt> <dd>Deletion of an object that has other objects related to it using a <cite>ForeignKey</cite> will set the <cite>ForeignKey</cite> column to <cite>NULL</cite>/<cite>None</cite> (sets <code class="docutils literal notranslate"><span class="pre">ON</span> <span class="pre">DELETE</span> <span class="pre">SET</span> <span class="pre">NULL</span></code>). Following the Person/Address example, if you delete the object <cite>Person</cite> with id 1 (John Doe), the <cite>Address</cite> with id 1 (123 W Main St) will be kept but the reference to person will be set to <cite>NULL</cite>/<cite>None</cite> (<code class="docutils literal notranslate"><span class="pre">personID=None</span></code>).</dd> </dl> </div> <div class="section" id="multiplejoin-and-sqlmultiplejoin-one-to-many"> <h4><a class="toc-backref" href="#id40">MultipleJoin and SQLMultipleJoin: One-to-Many</a><a class="headerlink" href="#multiplejoin-and-sqlmultiplejoin-one-to-many" title="Permalink to this headline">¶</a></h4> <p>See <a class="reference internal" href="#one-to-many-relationships">One-to-Many Relationships</a> for an example of one-to-many relationships.</p> <p>MultipleJoin returns a list of results, while SQLMultipleJoin returns a SelectResults object.</p> <p>Several keyword arguments are allowed to the <cite>MultipleJoin</cite> constructor:</p> <dl class="docutils" id="multiple-join-keywords"> <dt><cite>joinColumn</cite>:</dt> <dd>The column name of the key that points to this table. So, if you have a table <code class="docutils literal notranslate"><span class="pre">Product</span></code>, and another table has a column <code class="docutils literal notranslate"><span class="pre">ProductNo</span></code> that points to this table, then you’d use <code class="docutils literal notranslate"><span class="pre">joinColumn="ProductNo"</span></code>. WARNING: the argument you pass must conform to the column name in the database, not to the column in the class. So, if you have a SQLObject containing the <code class="docutils literal notranslate"><span class="pre">ProductNo</span></code> column, this will probably be translated into <code class="docutils literal notranslate"><span class="pre">product_no_id</span></code> in the DB (<code class="docutils literal notranslate"><span class="pre">product_no</span></code> is the normal uppercase- to-lowercase + underscores SQLO Translation, the added _id is just because the column referring to the table is probably a ForeignKey, and SQLO translates foreign keys that way). You should pass that parameter.</dd> <dt><cite>orderBy</cite>:</dt> <dd>Like the <a class="reference internal" href="#orderby">orderBy</a> argument to <cite>select()</cite>, you can specify the order that the joined objects should be returned in. <cite>defaultOrder</cite> will be used if not specified; <code class="docutils literal notranslate"><span class="pre">None</span></code> forces unordered results.</dd> <dt><cite>joinMethodName</cite>:</dt> <dd>When adding joins dynamically (using the class method <a class="reference internal" href="#addjoin">addJoin</a>), you can give the name of the accessor for the join. It can also be created automatically, and is normally implied (i.e., <code class="docutils literal notranslate"><span class="pre">addresses</span> <span class="pre">=</span> <span class="pre">MultipleJoin(...)</span></code> implies <code class="docutils literal notranslate"><span class="pre">joinMethodName="addresses"</span></code>).</dd> </dl> </div> <div class="section" id="relatedjoin-and-sqlrelatedjoin-many-to-many"> <h4><a class="toc-backref" href="#id41">RelatedJoin and SQLRelatedJoin: Many-to-Many</a><a class="headerlink" href="#relatedjoin-and-sqlrelatedjoin-many-to-many" title="Permalink to this headline">¶</a></h4> <p>See <a class="reference internal" href="#many-to-many-relationships">Many-to-Many Relationships</a> for examples of using many-to-many joins.</p> <p>RelatedJoin returns a list of results, while SQLRelatedJoin returns a SelectResults object.</p> <p><cite>RelatedJoin</cite> has all the keyword arguments of <a class="reference internal" href="#multiple-join-keywords">MultipleJoin</a>, plus:</p> <dl class="docutils"> <dt><cite>otherColumn</cite>:</dt> <dd>Similar to <cite>joinColumn</cite>, but referring to the joined class. Same warning about column name.</dd> <dt><cite>intermediateTable</cite>:</dt> <dd>The name of the intermediate table which references both classes. WARNING: you should pass the database table name, not the SQLO class representing.</dd> <dt><cite>addRemoveName</cite>:</dt> <dd>In the <a class="reference internal" href="#many-to-many-relationships">user/role example</a>, the methods <cite>addRole(role)</cite> and <cite>removeRole(role)</cite> are created. The <code class="docutils literal notranslate"><span class="pre">Role</span></code> portion of these method names can be changed by giving a string value here.</dd> <dt><cite>createRelatedTable</cite>:</dt> <dd>default: <code class="docutils literal notranslate"><span class="pre">True</span></code>. If <code class="docutils literal notranslate"><span class="pre">False</span></code>, then the related table won’t be automatically created; instead you must manually create it (e.g., with explicit SQLObject classes for the joins). New in 0.7.1.</dd> </dl> <div class="admonition note"> <p class="first admonition-title">Note</p> <p class="last">Let’s suppose you have SQLObject-inherited classes Alpha and Beta, and an AlphasAndBetas used for the many-to-many relationship. AlphasAndBetas contains the alphaIndex Foreign Key column referring to Alpha, and the betaIndex FK column referring to Beta. if you want a ‘betas’ RelatedJoin in Alpha, you should add it to Alpha passing ‘Beta’ (class name!) as the first parameter, then passing ‘alpha_index_id’ as joinColumn, ‘beta_index_id’ as otherColumn, and ‘alphas_and_betas’ as intermediateTable.</p> </div> <p>An example schema that requires the use of <cite>joinColumn</cite>, <cite>otherColumn</cite>, and <cite>intermediateTable</cite>:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">CREATE</span> <span class="n">TABLE</span> <span class="n">person</span> <span class="p">(</span> <span class="nb">id</span> <span class="n">SERIAL</span><span class="p">,</span> <span class="n">username</span> <span class="n">VARCHAR</span><span class="p">(</span><span class="mi">100</span><span class="p">)</span> <span class="n">NOT</span> <span class="n">NULL</span> <span class="n">UNIQUE</span> <span class="p">);</span> <span class="n">CREATE</span> <span class="n">TABLE</span> <span class="n">role</span> <span class="p">(</span> <span class="nb">id</span> <span class="n">SERIAL</span><span class="p">,</span> <span class="n">name</span> <span class="n">VARCHAR</span><span class="p">(</span><span class="mi">50</span><span class="p">)</span> <span class="n">NOT</span> <span class="n">NULL</span> <span class="n">UNIQUE</span> <span class="p">);</span> <span class="n">CREATE</span> <span class="n">TABLE</span> <span class="n">assigned_roles</span> <span class="p">(</span> <span class="n">person</span> <span class="n">INT</span> <span class="n">NOT</span> <span class="n">NULL</span><span class="p">,</span> <span class="n">role</span> <span class="n">INT</span> <span class="n">NOT</span> <span class="n">NULL</span> <span class="p">);</span> </pre></div> </div> <p>Then the usage in a class:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="k">class</span> <span class="nc">Person</span><span class="p">(</span><span class="n">SQLObject</span><span class="p">):</span> <span class="n">username</span> <span class="o">=</span> <span class="n">StringCol</span><span class="p">(</span><span class="n">length</span><span class="o">=</span><span class="mi">100</span><span class="p">,</span> <span class="n">alternateID</span><span class="o">=</span><span class="kc">True</span><span class="p">)</span> <span class="n">roles</span> <span class="o">=</span> <span class="n">RelatedJoin</span><span class="p">(</span><span class="s1">'Role'</span><span class="p">,</span> <span class="n">joinColumn</span><span class="o">=</span><span class="s1">'person'</span><span class="p">,</span> <span class="n">otherColumn</span><span class="o">=</span><span class="s1">'role'</span><span class="p">,</span> <span class="n">intermediateTable</span><span class="o">=</span><span class="s1">'assigned_roles'</span><span class="p">)</span> <span class="k">class</span> <span class="nc">Role</span><span class="p">(</span><span class="n">SQLObject</span><span class="p">):</span> <span class="n">name</span> <span class="o">=</span> <span class="n">StringCol</span><span class="p">(</span><span class="n">length</span><span class="o">=</span><span class="mi">50</span><span class="p">,</span> <span class="n">alternateID</span><span class="o">=</span><span class="kc">True</span><span class="p">)</span> <span class="n">roles</span> <span class="o">=</span> <span class="n">RelatedJoin</span><span class="p">(</span><span class="s1">'Person'</span><span class="p">,</span> <span class="n">joinColumn</span><span class="o">=</span><span class="s1">'role'</span><span class="p">,</span> <span class="n">otherColumn</span><span class="o">=</span><span class="s1">'person'</span><span class="p">,</span> <span class="n">intermediateTable</span><span class="o">=</span><span class="s1">'assigned_roles'</span><span class="p">)</span> </pre></div> </div> </div> <div class="section" id="singlejoin-one-to-one"> <h4><a class="toc-backref" href="#id42">SingleJoin: One-to-One</a><a class="headerlink" href="#singlejoin-one-to-one" title="Permalink to this headline">¶</a></h4> <p>Similar to <cite>MultipleJoin</cite>, but returns just one object, not a list.</p> </div> </div> <div class="section" id="connection-pooling"> <h3><a class="toc-backref" href="#id43">Connection pooling</a><a class="headerlink" href="#connection-pooling" title="Permalink to this headline">¶</a></h3> <p>Connection object acquires a new low-level DB API connection from the pool and stores it; the low-level connection is removed from the pool; “releasing” means “return it to the pool”. For single-threaded programs there is one connection in the pool.</p> <p>If the pool is empty a new low-level connection opened; if one has disabled pooling (by setting conn._pool = None) the connection will be closed instead of returning to the pool.</p> </div> <div class="section" id="transactions"> <h3><a class="toc-backref" href="#id44">Transactions</a><a class="headerlink" href="#transactions" title="Permalink to this headline">¶</a></h3> <p>Transaction support in SQLObject is left to the database. Transactions can be used like:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">conn</span> <span class="o">=</span> <span class="n">DBConnection</span><span class="o">.</span><span class="n">PostgresConnection</span><span class="p">(</span><span class="s1">'yada'</span><span class="p">)</span> <span class="n">trans</span> <span class="o">=</span> <span class="n">conn</span><span class="o">.</span><span class="n">transaction</span><span class="p">()</span> <span class="n">p</span> <span class="o">=</span> <span class="n">Person</span><span class="o">.</span><span class="n">get</span><span class="p">(</span><span class="mi">1</span><span class="p">,</span> <span class="n">trans</span><span class="p">)</span> <span class="n">p</span><span class="o">.</span><span class="n">firstName</span> <span class="o">=</span> <span class="s1">'Bob'</span> <span class="n">trans</span><span class="o">.</span><span class="n">commit</span><span class="p">()</span> <span class="n">p</span><span class="o">.</span><span class="n">firstName</span> <span class="o">=</span> <span class="s1">'Billy'</span> <span class="n">trans</span><span class="o">.</span><span class="n">rollback</span><span class="p">()</span> </pre></div> </div> <p>The <code class="docutils literal notranslate"><span class="pre">trans</span></code> object here is essentially a wrapper around a single database connection, and <cite>commit</cite> and <cite>rollback</cite> just pass that message to the low-level connection.</p> <p>One can call as much <code class="docutils literal notranslate"><span class="pre">.commit()</span></code>’s, but after a <code class="docutils literal notranslate"><span class="pre">.rollback()</span></code> one has to call <code class="docutils literal notranslate"><span class="pre">.begin()</span></code>. The last <code class="docutils literal notranslate"><span class="pre">.commit()</span></code> should be called as <code class="docutils literal notranslate"><span class="pre">.commit(close=True)</span></code> to release low-level connection back to the connection pool.</p> <p>You can use SELECT FOR UPDATE in those databases that support it:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">Person</span><span class="o">.</span><span class="n">select</span><span class="p">(</span><span class="n">Person</span><span class="o">.</span><span class="n">q</span><span class="o">.</span><span class="n">name</span><span class="o">==</span><span class="s2">"value"</span><span class="p">,</span> <span class="n">forUpdate</span><span class="o">=</span><span class="kc">True</span><span class="p">,</span> <span class="n">connection</span><span class="o">=</span><span class="n">trans</span><span class="p">)</span> </pre></div> </div> <p>Method <code class="docutils literal notranslate"><span class="pre">sqlhub.doInTransaction</span></code> can be used to run a piece of code in a transaction. The method accepts a callable and positional and keywords arguments. It begins a transaction using its <code class="docutils literal notranslate"><span class="pre">processConnection</span></code> or <code class="docutils literal notranslate"><span class="pre">threadConnection</span></code>, calls the callable, commits the transaction and closes the underlying connection; it returns whatever the callable returned. If an error occurs during call to the callable it rolls the transaction back and reraise the exception.</p> </div> <div class="section" id="automatic-schema-generation"> <h3><a class="toc-backref" href="#id45">Automatic Schema Generation</a><a class="headerlink" href="#automatic-schema-generation" title="Permalink to this headline">¶</a></h3> <p>All the connections support creating and dropping tables based on the class definition. First you have to prepare your class definition, which means including type information in your columns.</p> <div class="section" id="indexes"> <h4><a class="toc-backref" href="#id46">Indexes</a><a class="headerlink" href="#indexes" title="Permalink to this headline">¶</a></h4> <p>You can also define indexes for your tables, which is only meaningful when creating your tables through SQLObject (SQLObject relies on the database to implement the indexes). You do this again with attribute assignment, like:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">firstLastIndex</span> <span class="o">=</span> <span class="n">DatabaseIndex</span><span class="p">(</span><span class="s1">'firstName'</span><span class="p">,</span> <span class="s1">'lastName'</span><span class="p">)</span> </pre></div> </div> <p>This creates an index on two columns, useful if you are selecting a particular name. Of course, you can give a single column, and you can give the column object (<code class="docutils literal notranslate"><span class="pre">firstName</span></code>) instead of the string name. Note that if you use <code class="docutils literal notranslate"><span class="pre">unique</span></code> or <code class="docutils literal notranslate"><span class="pre">alternateID</span></code> (which implies <code class="docutils literal notranslate"><span class="pre">unique</span></code>) the database may make an index for you, and primary keys are always indexed.</p> <p>If you give the keyword argument <code class="docutils literal notranslate"><span class="pre">unique</span></code> to <cite>DatabaseIndex</cite> you’ll create a unique index – the combination of columns must be unique.</p> <p>You can also use dictionaries in place of the column names, to add extra options. E.g.:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">lastNameIndex</span> <span class="o">=</span> <span class="n">DatabaseIndex</span><span class="p">({</span><span class="s1">'expression'</span><span class="p">:</span> <span class="s1">'lower(last_name)'</span><span class="p">})</span> </pre></div> </div> <p>In that case, the index will be on the lower-case version of the column. It seems that only PostgreSQL supports this. You can also do:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">lastNameIndex</span> <span class="o">=</span> <span class="n">DatabaseIndex</span><span class="p">({</span><span class="s1">'column'</span><span class="p">:</span> <span class="n">lastName</span><span class="p">,</span> <span class="s1">'length'</span><span class="p">:</span> <span class="mi">10</span><span class="p">})</span> </pre></div> </div> <p>Which asks the database to only pay attention to the first ten characters. Only MySQL supports this, but it is ignored in other databases.</p> </div> <div class="section" id="creating-and-dropping-tables"> <h4><a class="toc-backref" href="#id47">Creating and Dropping Tables</a><a class="headerlink" href="#creating-and-dropping-tables" title="Permalink to this headline">¶</a></h4> <p>To create a table call <cite>createTable</cite>. It takes two arguments:</p> <dl class="docutils"> <dt><cite>ifNotExists</cite>:</dt> <dd>If the table already exists, then don’t try to create it. Default False.</dd> <dt><cite>createJoinTables</cite>:</dt> <dd>If you used <a class="reference internal" href="#many-to-many-relationships">Many-to-Many relationships</a>, then the intermediate tables will be created (but only for one of the two involved classes). Default True.</dd> </dl> <p><cite>dropTable</cite> takes arguments <cite>ifExists</cite> and <cite>dropJoinTables</cite>, self-explanatory.</p> </div> </div> </div> <div class="section" id="dynamic-classes"> <h2><a class="toc-backref" href="#id48">Dynamic Classes</a><a class="headerlink" href="#dynamic-classes" title="Permalink to this headline">¶</a></h2> <p>SQLObject classes can be manipulated dynamically. This leaves open the possibility of constructing SQLObject classes from an XML file, from database introspection, or from a graphical interface.</p> <div class="section" id="automatic-class-generation"> <h3><a class="toc-backref" href="#id49">Automatic Class Generation</a><a class="headerlink" href="#automatic-class-generation" title="Permalink to this headline">¶</a></h3> <p>SQLObject can read the table description from the database, and fill in the class columns (as would normally be described in the <cite>_columns</cite> attribute). Do this like:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="k">class</span> <span class="nc">Person</span><span class="p">(</span><span class="n">SQLObject</span><span class="p">):</span> <span class="k">class</span> <span class="nc">sqlmeta</span><span class="p">:</span> <span class="n">fromDatabase</span> <span class="o">=</span> <span class="kc">True</span> </pre></div> </div> <p>You can still specify columns (in <cite>_columns</cite>), and only missing columns will be added.</p> </div> <div class="section" id="runtime-column-and-join-changes"> <h3><a class="toc-backref" href="#id50">Runtime Column and Join Changes</a><a class="headerlink" href="#runtime-column-and-join-changes" title="Permalink to this headline">¶</a></h3> <p>You can add and remove columns to your class at runtime. Such changes will effect all instances, since changes are made in place to the class. There are two methods of the <a class="reference internal" href="#class-sqlmeta">class sqlmeta object</a>, <cite>addColumn</cite> and <cite>delColumn</cite>, both of which take a <cite>Col</cite> object (or subclass) as an argument. There’s also an option argument <cite>changeSchema</cite> which, if True, will add or drop the column from the database (typically with an <code class="docutils literal notranslate"><span class="pre">ALTER</span></code> command).</p> <p>When adding columns, you must pass the name as part of the column constructor, like <code class="docutils literal notranslate"><span class="pre">StringCol("username",</span> <span class="pre">length=20)</span></code>. When removing columns, you can either use the Col object (as found in <cite>sqlmeta.columns</cite>, or which you used in <cite>addColumn</cite>), or you can use the column name (like <code class="docutils literal notranslate"><span class="pre">MyClass.delColumn("username")</span></code>).</p> <p id="addjoin">You can also add <a class="reference internal" href="#relationships-between-classes-tables">Joins</a>, like <code class="docutils literal notranslate"><span class="pre">MyClass.addJoin(MultipleJoin("MyOtherClass"))</span></code>, and remove joins with <cite>delJoin</cite>. <cite>delJoin</cite> does not take strings, you have to get the join object out of the <cite>sqlmeta.joins</cite> attribute.</p> </div> </div> <div class="section" id="legacy-database-schemas"> <h2><a class="toc-backref" href="#id51">Legacy Database Schemas</a><a class="headerlink" href="#legacy-database-schemas" title="Permalink to this headline">¶</a></h2> <p>Often you will have a database that already exists, and does not use the naming conventions that SQLObject expects, or does not use any naming convention at all.</p> <div class="section" id="sqlobject-requirements"> <h3><a class="toc-backref" href="#id52">SQLObject requirements</a><a class="headerlink" href="#sqlobject-requirements" title="Permalink to this headline">¶</a></h3> <p>While SQLObject tries not to make too many requirements on your schema, some assumptions are made. Some of these may be relaxed in the future.</p> <p>All tables that you want to turn into a class need to have an integer primary key. That key should be defined like:</p> <dl class="docutils"> <dt>MySQL:</dt> <dd><code class="docutils literal notranslate"><span class="pre">INT</span> <span class="pre">PRIMARY</span> <span class="pre">KEY</span> <span class="pre">AUTO_INCREMENT</span></code></dd> <dt>Postgres:</dt> <dd><code class="docutils literal notranslate"><span class="pre">SERIAL</span> <span class="pre">PRIMARY</span> <span class="pre">KEY</span></code></dd> <dt>SQLite:</dt> <dd><code class="docutils literal notranslate"><span class="pre">INTEGER</span> <span class="pre">PRIMARY</span> <span class="pre">KEY</span> <span class="pre">AUTOINCREMENT</span></code></dd> </dl> <p>SQLObject does not support primary keys made up of multiple columns (that probably won’t change). It does not generally support tables with primary keys with business meaning – i.e., primary keys are assumed to be immutable (that won’t change).</p> <p>At the moment foreign key column names must end in <code class="docutils literal notranslate"><span class="pre">"ID"</span></code> (case-insensitive). This restriction will probably be removed in the next release.</p> <div class="section" id="workaround-for-primary-keys-made-up-of-multiple-columns"> <h4><a class="toc-backref" href="#id53">Workaround for primary keys made up of multiple columns</a><a class="headerlink" href="#workaround-for-primary-keys-made-up-of-multiple-columns" title="Permalink to this headline">¶</a></h4> <p>If the database table/view has ONE NUMERIC Primary Key then sqlmeta - idName should be used to map the table column name to SQLObject id column.</p> <p>If the Primary Key consists only of number columns it is possible to create a virtual column <code class="docutils literal notranslate"><span class="pre">id</span></code> this way:</p> <p>Example for Postgresql:</p> <blockquote> <div>select ‘1’||lpad(PK1,max_length_of_PK1,‘0’)||lpad(PK2,max_length_of_PK2,‘0’)||…||lpad(PKn,max_length_of_PKn,‘0’) as “id”, column_PK1, column_PK2, .., column_PKn, column… from table;</div></blockquote> <p>Note:</p> <ul class="simple"> <li>The arbitrary ‘1’ at the beginning of the string to allow for leading zeros of the first PK.</li> <li>The application designer has to determine the maximum length of each Primary Key.</li> </ul> <p>This statement can be saved as a view or the column can be added to the database table, where it can be kept up to date with a database trigger.</p> <p>Obviously the “view” method does generally not allow insert, updates or deletes. For Postgresql you may want to consult the chapter “RULES” for manipulating underlying tables.</p> <p>For an alphanumeric Primary Key column a similar method is possible:</p> <p>Every character of the lpaded PK has to be transfered using ascii(character) which returns a 3digit number which can be concatenated as shown above.</p> <p>Caveats:</p> <ul class="simple"> <li>this way the <code class="docutils literal notranslate"><span class="pre">id</span></code> may become a very large integer number which may cause troubles elsewhere.</li> <li>no performance loss takes place if the where clauses specifies the PK columns.</li> </ul> <p>Example: CD-Album * Album: PK=ean * Tracks: PK=ean,disc_nr,track_nr</p> <p>The database view to show the tracks starts:</p> <blockquote> <div>SELECT ean||lpad(“disc_nr”,2,‘0’)||lpad(“track_nr”,2,‘0’) as id, … Note: no leading ‘1’ and no padding necessary for ean numbers</div></blockquote> <p>Tracks.select(Tracks.q.ean==id) … where id is the ean of the Album.</p> </div> </div> <div class="section" id="changing-the-naming-style"> <h3><a class="toc-backref" href="#id54">Changing the Naming Style</a><a class="headerlink" href="#changing-the-naming-style" title="Permalink to this headline">¶</a></h3> <p>By default names in SQLObject are expected to be mixed case in Python (like <code class="docutils literal notranslate"><span class="pre">mixedCase</span></code>), and underscore-separated in SQL (like <code class="docutils literal notranslate"><span class="pre">mixed_case</span></code>). This applies to table and column names. The primary key is assumed to be simply <code class="docutils literal notranslate"><span class="pre">id</span></code>.</p> <p>Other styles exist. A typical one is mixed case column names, and a primary key that includes the table name, like <code class="docutils literal notranslate"><span class="pre">ProductID</span></code>. You can use a different <cite>Style</cite> object to indicate a different naming convention. For instance:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="k">class</span> <span class="nc">Person</span><span class="p">(</span><span class="n">SQLObject</span><span class="p">):</span> <span class="k">class</span> <span class="nc">sqlmeta</span><span class="p">:</span> <span class="n">style</span> <span class="o">=</span> <span class="n">MixedCaseStyle</span><span class="p">(</span><span class="n">longID</span><span class="o">=</span><span class="kc">True</span><span class="p">)</span> <span class="n">firstName</span> <span class="o">=</span> <span class="n">StringCol</span><span class="p">()</span> <span class="n">lastName</span> <span class="o">=</span> <span class="n">StringCol</span><span class="p">()</span> </pre></div> </div> <p>If you use <code class="docutils literal notranslate"><span class="pre">Person.createTable()</span></code>, you’ll get:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">CREATE</span> <span class="n">TABLE</span> <span class="n">Person</span> <span class="p">(</span> <span class="n">PersonID</span> <span class="n">INT</span> <span class="n">PRIMARY</span> <span class="n">KEY</span><span class="p">,</span> <span class="n">FirstName</span> <span class="n">Text</span><span class="p">,</span> <span class="n">LastName</span> <span class="n">Text</span> <span class="p">)</span> </pre></div> </div> <p>The <cite>MixedCaseStyle</cite> object handles the initial capitalization of words, but otherwise leaves them be. By using <code class="docutils literal notranslate"><span class="pre">longID=True</span></code>, we indicate that the primary key should look like a normal reference (<code class="docutils literal notranslate"><span class="pre">PersonID</span></code> for <cite>MixedCaseStyle</cite>, or <code class="docutils literal notranslate"><span class="pre">person_id</span></code> for the default style).</p> <p>If you wish to change the style globally, assign the style to the connection, like:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">__connection__</span><span class="o">.</span><span class="n">style</span> <span class="o">=</span> <span class="n">MixedCaseStyle</span><span class="p">(</span><span class="n">longID</span><span class="o">=</span><span class="kc">True</span><span class="p">)</span> </pre></div> </div> </div> <div class="section" id="irregular-naming"> <h3><a class="toc-backref" href="#id55">Irregular Naming</a><a class="headerlink" href="#irregular-naming" title="Permalink to this headline">¶</a></h3> <p>This is now covered in the <a class="reference internal" href="#class-sqlmeta">Class sqlmeta</a> section.</p> </div> <div class="section" id="non-integer-keys"> <h3><a class="toc-backref" href="#id56">Non-Integer Keys</a><a class="headerlink" href="#non-integer-keys" title="Permalink to this headline">¶</a></h3> <p>While not strictly a legacy database issue, this fits into the category of “irregularities”. If you use non-integer keys, all primary key management is up to you. You must create the table yourself (SQLObject can create tables with int or str IDs), and when you create instances you must pass a <code class="docutils literal notranslate"><span class="pre">id</span></code> keyword argument into constructor (like <code class="docutils literal notranslate"><span class="pre">Person(id='555-55-5555',</span> <span class="pre">...)</span></code>).</p> </div> </div> <div class="section" id="dbconnection-database-connections"> <h2><a class="toc-backref" href="#id57">DBConnection: Database Connections</a><a class="headerlink" href="#dbconnection-database-connections" title="Permalink to this headline">¶</a></h2> <p>The <cite>DBConnection</cite> module currently has six external classes, <cite>MySQLConnection</cite>, <cite>PostgresConnection</cite>, <cite>SQLiteConnection</cite>, <cite>SybaseConnection</cite>, <cite>MaxdbConnection</cite>, <cite>MSSQLConnection</cite>.</p> <p>You can pass the keyword argument <cite>debug</cite> to any connector. If set to true, then any SQL sent to the database will also be printed to the console.</p> <p>You can additionally pass <cite>logger</cite> keyword argument which should be a name of the logger to use. If specified and <cite>debug</cite> is <code class="docutils literal notranslate"><span class="pre">True</span></code>, SQLObject will write debug print statements via that logger instead of printing directly to console. The argument <cite>loglevel</cite> allows to choose the logging level - it can be <code class="docutils literal notranslate"><span class="pre">debug</span></code>, <code class="docutils literal notranslate"><span class="pre">info</span></code>, <code class="docutils literal notranslate"><span class="pre">warning</span></code>, <code class="docutils literal notranslate"><span class="pre">error</span></code>, <code class="docutils literal notranslate"><span class="pre">critical</span></code> or <code class="docutils literal notranslate"><span class="pre">exception</span></code>. In case <cite>logger</cite> is absent or empty SQLObject uses <code class="docutils literal notranslate"><span class="pre">print</span></code>’s instead of logging; <cite>loglevel</cite> can be <code class="docutils literal notranslate"><span class="pre">stdout</span></code> or <code class="docutils literal notranslate"><span class="pre">stderr</span></code> in this case; default is <code class="docutils literal notranslate"><span class="pre">stdout</span></code>.</p> <p>To configure logging one can do something like that:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="kn">import</span> <span class="nn">logging</span> <span class="n">logging</span><span class="o">.</span><span class="n">basicConfig</span><span class="p">(</span> <span class="n">filename</span><span class="o">=</span><span class="s1">'test.log'</span><span class="p">,</span> <span class="nb">format</span><span class="o">=</span><span class="s1">'[</span><span class="si">%(asctime)s</span><span class="s1">] </span><span class="si">%(name)s</span><span class="s1"> </span><span class="si">%(levelname)s</span><span class="s1">: </span><span class="si">%(message)s</span><span class="s1">'</span><span class="p">,</span> <span class="n">level</span><span class="o">=</span><span class="n">logging</span><span class="o">.</span><span class="n">DEBUG</span><span class="p">,</span> <span class="p">)</span> <span class="n">log</span> <span class="o">=</span> <span class="n">logging</span><span class="o">.</span><span class="n">getLogger</span><span class="p">(</span><span class="s2">"TEST"</span><span class="p">)</span> <span class="n">log</span><span class="o">.</span><span class="n">info</span><span class="p">(</span><span class="s2">"Log started"</span><span class="p">)</span> <span class="n">__connection__</span> <span class="o">=</span> <span class="s2">"sqlite:/:memory:?debug=1&logger=TEST&loglevel=debug"</span> </pre></div> </div> <p>The code redirects SQLObject debug messages to <cite>test.log</cite> file.</p> <div class="section" id="id3"> <h3><a class="toc-backref" href="#id58">MySQL</a><a class="headerlink" href="#id3" title="Permalink to this headline">¶</a></h3> <p><cite>MySQLConnection</cite> takes the keyword arguments <cite>host</cite>, <cite>port</cite>, <cite>db</cite>, <cite>user</cite>, and <cite>password</cite>, just like <cite>MySQLdb.connect</cite> does.</p> <p>MySQLConnection supports all the features, though MySQL only supports <a class="reference internal" href="#transactions">transactions</a> when using the InnoDB backend; SQLObject can explicitly define the backend using <code class="docutils literal notranslate"><span class="pre">sqlmeta.createSQL</span></code>.</p> <p>Supported drivers are <code class="docutils literal notranslate"><span class="pre">mysqldb</span></code>, <code class="docutils literal notranslate"><span class="pre">connector</span></code>, <code class="docutils literal notranslate"><span class="pre">oursql</span></code> and <code class="docutils literal notranslate"><span class="pre">pymysql</span></code>, <code class="docutils literal notranslate"><span class="pre">pyodbc</span></code>, <code class="docutils literal notranslate"><span class="pre">pypyodbc</span></code> or <code class="docutils literal notranslate"><span class="pre">odbc</span></code> (try <code class="docutils literal notranslate"><span class="pre">pyodbc</span></code> and <code class="docutils literal notranslate"><span class="pre">pypyodbc</span></code>); defualt is <code class="docutils literal notranslate"><span class="pre">mysqldb</span></code>.</p> <p>Keyword argument <code class="docutils literal notranslate"><span class="pre">conv</span></code> allows to pass a list of custom converters. Example:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="kn">import</span> <span class="nn">time</span> <span class="kn">import</span> <span class="nn">sqlobject</span> <span class="kn">import</span> <span class="nn">MySQLdb.converters</span> <span class="k">def</span> <span class="nf">_mysql_timestamp_converter</span><span class="p">(</span><span class="n">raw</span><span class="p">):</span> <span class="sd">"""Convert a MySQL TIMESTAMP to a floating point number representing</span> <span class="sd"> the seconds since the Un*x Epoch. It uses custom code the input seems</span> <span class="sd"> to be the new (MySQL 4.1+) timestamp format, otherwise code from the</span> <span class="sd"> MySQLdb module is used."""</span> <span class="k">if</span> <span class="n">raw</span><span class="p">[</span><span class="mi">4</span><span class="p">]</span> <span class="o">==</span> <span class="s1">'-'</span><span class="p">:</span> <span class="k">return</span> <span class="n">time</span><span class="o">.</span><span class="n">mktime</span><span class="p">(</span><span class="n">time</span><span class="o">.</span><span class="n">strptime</span><span class="p">(</span><span class="n">raw</span><span class="p">,</span> <span class="s1">'%Y-%m-</span><span class="si">%d</span><span class="s1"> %H:%M:%S'</span><span class="p">))</span> <span class="k">else</span><span class="p">:</span> <span class="k">return</span> <span class="n">MySQLdb</span><span class="o">.</span><span class="n">converters</span><span class="o">.</span><span class="n">mysql_timestamp_converter</span><span class="p">(</span><span class="n">raw</span><span class="p">)</span> <span class="n">conversions</span> <span class="o">=</span> <span class="n">MySQLdb</span><span class="o">.</span><span class="n">converters</span><span class="o">.</span><span class="n">conversions</span><span class="o">.</span><span class="n">copy</span><span class="p">()</span> <span class="n">conversions</span><span class="p">[</span><span class="n">MySQLdb</span><span class="o">.</span><span class="n">constants</span><span class="o">.</span><span class="n">FIELD_TYPE</span><span class="o">.</span><span class="n">TIMESTAMP</span><span class="p">]</span> <span class="o">=</span> <span class="n">_mysql_timestamp_converter</span> <span class="n">MySQLConnection</span> <span class="o">=</span> <span class="n">sqlobject</span><span class="o">.</span><span class="n">mysql</span><span class="o">.</span><span class="n">builder</span><span class="p">()</span> <span class="n">connection</span> <span class="o">=</span> <span class="n">MySQLConnection</span><span class="p">(</span><span class="n">user</span><span class="o">=</span><span class="s1">'foo'</span><span class="p">,</span> <span class="n">db</span><span class="o">=</span><span class="s1">'somedb'</span><span class="p">,</span> <span class="n">conv</span><span class="o">=</span><span class="n">conversions</span><span class="p">)</span> </pre></div> </div> <p>Connection-specific parameters are: <code class="docutils literal notranslate"><span class="pre">unix_socket</span></code>, <code class="docutils literal notranslate"><span class="pre">init_command</span></code>, <code class="docutils literal notranslate"><span class="pre">read_default_file</span></code>, <code class="docutils literal notranslate"><span class="pre">read_default_group</span></code>, <code class="docutils literal notranslate"><span class="pre">conv</span></code>, <code class="docutils literal notranslate"><span class="pre">connect_timeout</span></code>, <code class="docutils literal notranslate"><span class="pre">compress</span></code>, <code class="docutils literal notranslate"><span class="pre">named_pipe</span></code>, <code class="docutils literal notranslate"><span class="pre">use_unicode</span></code>, <code class="docutils literal notranslate"><span class="pre">client_flag</span></code>, <code class="docutils literal notranslate"><span class="pre">local_infile</span></code>, <code class="docutils literal notranslate"><span class="pre">ssl_key</span></code>, <code class="docutils literal notranslate"><span class="pre">ssl_cert</span></code>, <code class="docutils literal notranslate"><span class="pre">ssl_ca</span></code>, <code class="docutils literal notranslate"><span class="pre">ssl_capath</span></code>, <code class="docutils literal notranslate"><span class="pre">charset</span></code>.</p> </div> <div class="section" id="postgres"> <h3><a class="toc-backref" href="#id59">Postgres</a><a class="headerlink" href="#postgres" title="Permalink to this headline">¶</a></h3> <p><cite>PostgresConnection</cite> takes a single connection string, like <code class="docutils literal notranslate"><span class="pre">"dbname=something</span> <span class="pre">user=some_user"</span></code>, just like <cite>psycopg.connect</cite>. You can also use the same keyword arguments as for <cite>MySQLConnection</cite>, and a dsn string will be constructed.</p> <p>PostgresConnection supports transactions and all other features.</p> <p>The user can choose a DB API driver for PostgreSQL by using a <code class="docutils literal notranslate"><span class="pre">driver</span></code> parameter in DB URI or PostgresConnection that can be a comma-separated list of driver names. Possible drivers are: <code class="docutils literal notranslate"><span class="pre">psycopg2</span></code>, <code class="docutils literal notranslate"><span class="pre">psycopg</span></code> (alias for <code class="docutils literal notranslate"><span class="pre">psycopg2</span></code>), <code class="docutils literal notranslate"><span class="pre">pygresql</span></code>, <code class="docutils literal notranslate"><span class="pre">pypostgresql</span></code>, <code class="docutils literal notranslate"><span class="pre">pg8000</span></code>, <code class="docutils literal notranslate"><span class="pre">pyodbc</span></code>, <code class="docutils literal notranslate"><span class="pre">pypyodbc</span></code> or <code class="docutils literal notranslate"><span class="pre">odbc</span></code> (try <code class="docutils literal notranslate"><span class="pre">pyodbc</span></code> and <code class="docutils literal notranslate"><span class="pre">pypyodbc</span></code>). Default is <code class="docutils literal notranslate"><span class="pre">psycopg</span></code>.</p> <p>Connection-specific parameters are: <code class="docutils literal notranslate"><span class="pre">sslmode</span></code>, <code class="docutils literal notranslate"><span class="pre">unicodeCols</span></code>, <code class="docutils literal notranslate"><span class="pre">schema</span></code>, <code class="docutils literal notranslate"><span class="pre">charset</span></code>.</p> </div> <div class="section" id="id4"> <h3><a class="toc-backref" href="#id60">SQLite</a><a class="headerlink" href="#id4" title="Permalink to this headline">¶</a></h3> <p><cite>SQLiteConnection</cite> takes the a single string, which is the path to the database file.</p> <p>SQLite puts all data into one file, with a journal file that is opened in the same directory during operation (the file is deleted when the program quits). SQLite does not restrict the types you can put in a column – strings can go in integer columns, dates in integers, etc.</p> <p>SQLite may have concurrency issues, depending on your usage in a multi-threaded environment.</p> <p>The user can choose a DB API driver for SQLite by using a <code class="docutils literal notranslate"><span class="pre">driver</span></code> parameter in DB URI or SQLiteConnection that can be a comma-separated list of driver names. Possible drivers are: <code class="docutils literal notranslate"><span class="pre">pysqlite2</span></code> (alias <code class="docutils literal notranslate"><span class="pre">sqlite2</span></code>), <code class="docutils literal notranslate"><span class="pre">sqlite3</span></code>, <code class="docutils literal notranslate"><span class="pre">sqlite</span></code> (alias <code class="docutils literal notranslate"><span class="pre">sqlite1</span></code>). Default is to test pysqlite2, sqlite3 and sqlite in that order.</p> <p>Connection-specific parameters are: <code class="docutils literal notranslate"><span class="pre">encoding</span></code>, <code class="docutils literal notranslate"><span class="pre">mode</span></code>, <code class="docutils literal notranslate"><span class="pre">timeout</span></code>, <code class="docutils literal notranslate"><span class="pre">check_same_thread</span></code>, <code class="docutils literal notranslate"><span class="pre">use_table_info</span></code>.</p> </div> <div class="section" id="id5"> <h3><a class="toc-backref" href="#id61">Firebird</a><a class="headerlink" href="#id5" title="Permalink to this headline">¶</a></h3> <p><cite>FirebirdConnection</cite> takes the arguments <cite>host</cite>, <cite>db</cite>, <cite>user</cite> (default <code class="docutils literal notranslate"><span class="pre">"sysdba"</span></code>), <cite>password</cite> (default <code class="docutils literal notranslate"><span class="pre">"masterkey"</span></code>).</p> <p>Firebird supports all the features. Support is still young, so there may be some issues, especially with concurrent access, and especially using lazy selects. Try <code class="docutils literal notranslate"><span class="pre">list(MyClass.select())</span></code> to avoid concurrent cursors if you have problems (using <code class="docutils literal notranslate"><span class="pre">list()</span></code> will pre-fetch all the results of a select).</p> <p>Firebird support <code class="docutils literal notranslate"><span class="pre">fdb</span></code>, <code class="docutils literal notranslate"><span class="pre">kinterbasdb</span></code> or <code class="docutils literal notranslate"><span class="pre">firebirdsql</span></code> drivers. Default are <code class="docutils literal notranslate"><span class="pre">fdb</span></code> and <code class="docutils literal notranslate"><span class="pre">kinterbasdb</span></code>.</p> <p>There could be a problem if one tries to connect to a server running on w32 from a program running on Unix; the problem is how to specify the database so that SQLObject correctly parses it. Vertical bar is replaces by a semicolon only on a w32. On Unix a vertical bar is a pretty normal character and must not be processed.</p> <p>The most correct way to fix the problem is to connect to the DB using a database name, not a file name. In the Firebird a DBA can set an alias instead of database name in the aliases.conf file</p> <p>Example from <a class="reference external" href="http://www.firebirdmanual.com/firebird/en/firebird-manual/2">Firebird 2.0 Administrators Manual</a>:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="c1"># fbdb1 is on a Windows server:</span> <span class="n">fbdb1</span> <span class="o">=</span> <span class="n">c</span><span class="p">:</span>\<span class="n">Firebird</span>\<span class="n">sample</span>\<span class="n">Employee</span><span class="o">.</span><span class="n">fdb</span> </pre></div> </div> <p>Now a program can connect to firebird://host:port/fbdb1.</p> <p>One can edit aliases.conf whilst the server is running. There is no need to stop and restart the server in order for new aliases.conf entries to be recognised.</p> <p>If you are using indexes and get an error like <em>key size exceeds implementation restriction for index</em>, see <a class="reference external" href="http://mujweb.cz/iprenosil/interbase/ip_ib_indexcalculator.htm">this page</a> to understand the restrictions on your indexing.</p> <p>Connection-specific parameters are: <code class="docutils literal notranslate"><span class="pre">dialect</span></code>, <code class="docutils literal notranslate"><span class="pre">role</span></code>, <code class="docutils literal notranslate"><span class="pre">charset</span></code>.</p> </div> <div class="section" id="id6"> <h3><a class="toc-backref" href="#id62">Sybase</a><a class="headerlink" href="#id6" title="Permalink to this headline">¶</a></h3> <p><cite>SybaseConnection</cite> takes the arguments <cite>host</cite>, <cite>db</cite>, <cite>user</cite>, and <cite>password</cite>. It also takes the extra boolean argument <cite>locking</cite> (default True), which is passed through when performing a connection. You may use a False value for <cite>locking</cite> if you are not using multiple threads, for a slight performance boost.</p> <p>It uses the <a class="reference external" href="http://www.object-craft.com.au/projects/sybase/">Sybase</a> module.</p> <p>Connection-specific parameters are: <code class="docutils literal notranslate"><span class="pre">locking</span></code>, <code class="docutils literal notranslate"><span class="pre">autoCommit</span></code>.</p> </div> <div class="section" id="id7"> <h3><a class="toc-backref" href="#id63">MAX DB</a><a class="headerlink" href="#id7" title="Permalink to this headline">¶</a></h3> <p>MAX DB, also known as SAP DB, is available from a partnership of SAP and MySQL. It takes the typical arguments: <cite>host</cite>, <cite>database</cite>, <cite>user</cite>, <cite>password</cite>. It also takes the arguments <cite>sqlmode</cite> (default <code class="docutils literal notranslate"><span class="pre">"internal"</span></code>), <cite>isolation</cite>, and <cite>timeout</cite>, which are passed through when creating the connection to the database.</p> <p>It uses the <a class="reference external" href="http://maxdb.sap.com/doc/7_8/50/01923f25b842438a408805774f6989/frameset.htm">sapdb</a> module.</p> <p>Connection-specific parameters are: <code class="docutils literal notranslate"><span class="pre">autoCommit</span></code>, <code class="docutils literal notranslate"><span class="pre">sqlmode</span></code>, <code class="docutils literal notranslate"><span class="pre">isolation</span></code>, <code class="docutils literal notranslate"><span class="pre">timeout</span></code>.</p> </div> <div class="section" id="ms-sql-server"> <h3><a class="toc-backref" href="#id64">MS SQL Server</a><a class="headerlink" href="#ms-sql-server" title="Permalink to this headline">¶</a></h3> <p>The <cite>MSSQLConnection</cite> objects wants to use new style connection strings in the format of</p> <p>mssql://user:pass@host:port/db</p> <p>This will then be mapped to either the correct driver format. If running SQL Server on a “named” port, make sure to specify the port number in the URI.</p> <p>The two drivers currently supported are <a class="reference external" href="http://adodbapi.sourceforge.net/">adodbapi</a> and <a class="reference external" href="http://www.pymssql.org/en/latest/index.html">pymssql</a>.</p> <p>The user can choose a DB API driver for MSSQL by using a <code class="docutils literal notranslate"><span class="pre">driver</span></code> parameter in DB URI or MSSQLConnection that can be a comma-separated list of driver names. Possible drivers are: <code class="docutils literal notranslate"><span class="pre">adodb</span></code> (alias <code class="docutils literal notranslate"><span class="pre">adodbapi</span></code>) and <code class="docutils literal notranslate"><span class="pre">pymssql</span></code>. Default is to test <code class="docutils literal notranslate"><span class="pre">adodbapi</span></code> and <code class="docutils literal notranslate"><span class="pre">pymssql</span></code> in that order.</p> <p>Connection-specific parameters are: <code class="docutils literal notranslate"><span class="pre">autoCommit</span></code>, <code class="docutils literal notranslate"><span class="pre">timeout</span></code>.</p> </div> </div> <div class="section" id="events-signals"> <h2><a class="toc-backref" href="#id65">Events (signals)</a><a class="headerlink" href="#events-signals" title="Permalink to this headline">¶</a></h2> <p>Signals are a mechanism to be notified when data or schema changes happen through SQLObject. This may be useful for doing custom data validation, logging changes, setting default attributes, etc. Some of what signals can do is also possible by overriding methods, but signals may provide a cleaner way, especially across classes not related by inheritance.</p> <p>Example:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="kn">from</span> <span class="nn">sqlobject.events</span> <span class="k">import</span> <span class="n">listen</span><span class="p">,</span> <span class="n">RowUpdateSignal</span><span class="p">,</span> <span class="n">RowCreatedSignal</span> <span class="kn">from</span> <span class="nn">model</span> <span class="k">import</span> <span class="n">Users</span> <span class="k">def</span> <span class="nf">update_listener</span><span class="p">(</span><span class="n">instance</span><span class="p">,</span> <span class="n">kwargs</span><span class="p">):</span> <span class="sd">"""keep "last_updated" field current"""</span> <span class="kn">import</span> <span class="nn">datetime</span> <span class="c1"># BAD method 1, causes infinite recursion?</span> <span class="c1"># instance should be read-only</span> <span class="n">instance</span><span class="o">.</span><span class="n">last_updated</span> <span class="o">=</span> <span class="n">datetime</span><span class="o">.</span><span class="n">datetime</span><span class="o">.</span><span class="n">now</span><span class="p">()</span> <span class="c1"># GOOD method 2</span> <span class="n">kwargs</span><span class="p">[</span><span class="s1">'last_updated'</span><span class="p">]</span> <span class="o">=</span> <span class="n">datetime</span><span class="o">.</span><span class="n">datetime</span><span class="o">.</span><span class="n">now</span><span class="p">()</span> <span class="k">def</span> <span class="nf">created_listener</span><span class="p">(</span><span class="n">instance</span><span class="p">,</span> <span class="n">kwargs</span><span class="p">,</span> <span class="n">post_funcs</span><span class="p">):</span> <span class="sd">""""email me when new users added"""</span> <span class="c1"># email() implementation left as an exercise for the reader</span> <span class="n">msg</span> <span class="o">=</span> <span class="s2">"</span><span class="si">%s</span><span class="s2"> just was just added to the database!"</span> <span class="o">%</span> <span class="n">kwargs</span><span class="p">[</span><span class="s1">'name'</span><span class="p">]</span> <span class="n">email</span><span class="p">(</span><span class="n">msg</span><span class="p">)</span> <span class="n">listen</span><span class="p">(</span><span class="n">update_listener</span><span class="p">,</span> <span class="n">Users</span><span class="p">,</span> <span class="n">RowUpdateSignal</span><span class="p">)</span> <span class="n">listen</span><span class="p">(</span><span class="n">created_listener</span><span class="p">,</span> <span class="n">Users</span><span class="p">,</span> <span class="n">RowCreatedSignal</span><span class="p">)</span> </pre></div> </div> </div> <div class="section" id="exported-symbols"> <h2><a class="toc-backref" href="#id66">Exported Symbols</a><a class="headerlink" href="#exported-symbols" title="Permalink to this headline">¶</a></h2> <p>You can use <code class="docutils literal notranslate"><span class="pre">from</span> <span class="pre">sqlobject</span> <span class="pre">import</span> <span class="pre">*</span></code>, though you don’t have to. It exports a minimal number of symbols. The symbols exported:</p> <p>From <cite>sqlobject.main</cite>:</p> <ul class="simple"> <li><cite>NoDefault</cite></li> <li><cite>SQLObject</cite></li> <li><cite>getID</cite></li> <li><cite>getObject</cite></li> </ul> <p>From <cite>sqlobject.col</cite>: * <cite>Col</cite> * <cite>StringCol</cite> * <cite>IntCol</cite> * <cite>FloatCol</cite> * <cite>KeyCol</cite> * <cite>ForeignKey</cite> * <cite>EnumCol</cite> * <cite>SetCol</cite> * <cite>DateTimeCol</cite> * <cite>DateCol</cite> * <cite>TimeCol</cite> * <cite>TimestampCol</cite> * <cite>DecimalCol</cite> * <cite>CurrencyCol</cite></p> <p>From <cite>sqlobject.joins</cite>: * <cite>MultipleJoin</cite> * <cite>RelatedJoin</cite></p> <p>From <cite>sqlobject.styles</cite>: * <cite>Style</cite> * <cite>MixedCaseUnderscoreStyle</cite> * <cite>DefaultStyle</cite> * <cite>MixedCaseStyle</cite></p> <p>From <cite>sqlobject.sqlbuilder</cite>:</p> <ul class="simple"> <li><cite>AND</cite></li> <li><cite>OR</cite></li> <li><cite>NOT</cite></li> <li><cite>IN</cite></li> <li><cite>LIKE</cite></li> <li><cite>DESC</cite></li> <li><cite>CONTAINSSTRING</cite></li> <li><cite>const</cite></li> <li><cite>func</cite></li> </ul> <div class="section" id="left-join-and-other-joins"> <h3><a class="toc-backref" href="#id67">LEFT JOIN and other JOINs</a><a class="headerlink" href="#left-join-and-other-joins" title="Permalink to this headline">¶</a></h3> <p>First look in the <a class="reference external" href="FAQ.html#how-can-i-do-a-left-join">FAQ</a>, question “How can I do a LEFT JOIN?”</p> <p>Still here? Well. To perform a JOIN use one of the JOIN helpers from <a class="reference external" href="SQLBuilder.html">SQLBuilder</a>. Pass an instance of the helper to .select() method. For example:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="kn">from</span> <span class="nn">sqlobject.sqlbuilder</span> <span class="k">import</span> <span class="n">LEFTJOINOn</span> <span class="n">MyTable</span><span class="o">.</span><span class="n">select</span><span class="p">(</span> <span class="n">join</span><span class="o">=</span><span class="n">LEFTJOINOn</span><span class="p">(</span><span class="n">Table1</span><span class="p">,</span> <span class="n">Table2</span><span class="p">,</span> <span class="n">Table1</span><span class="o">.</span><span class="n">q</span><span class="o">.</span><span class="n">name</span> <span class="o">==</span> <span class="n">Table2</span><span class="o">.</span><span class="n">q</span><span class="o">.</span><span class="n">value</span><span class="p">))</span> </pre></div> </div> <p>will generate the query:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">SELECT</span> <span class="n">my_table</span><span class="o">.*</span> <span class="n">FROM</span> <span class="n">my_table</span><span class="p">,</span> <span class="n">table1</span> <span class="n">LEFT</span> <span class="n">JOIN</span> <span class="n">table2</span> <span class="n">ON</span> <span class="n">table1</span><span class="o">.</span><span class="n">name</span> <span class="o">=</span> <span class="n">table2</span><span class="o">.</span><span class="n">value</span><span class="p">;</span> </pre></div> </div> <p>If you want to join with the primary table - leave the first table None:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">MyTable</span><span class="o">.</span><span class="n">select</span><span class="p">(</span> <span class="n">join</span><span class="o">=</span><span class="n">LEFTJOINOn</span><span class="p">(</span><span class="kc">None</span><span class="p">,</span> <span class="n">Table1</span><span class="p">,</span> <span class="n">MyTable</span><span class="o">.</span><span class="n">q</span><span class="o">.</span><span class="n">name</span> <span class="o">==</span> <span class="n">Table1</span><span class="o">.</span><span class="n">q</span><span class="o">.</span><span class="n">value</span><span class="p">))</span> </pre></div> </div> <p>will generate the query:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">SELECT</span> <span class="n">my_table</span><span class="o">.*</span> <span class="n">FROM</span> <span class="n">my_table</span> <span class="n">LEFT</span> <span class="n">JOIN</span> <span class="n">table2</span> <span class="n">ON</span> <span class="n">my_table</span><span class="o">.</span><span class="n">name</span> <span class="o">=</span> <span class="n">table1</span><span class="o">.</span><span class="n">value</span><span class="p">;</span> </pre></div> </div> <p>The join argument for .select() can be a JOIN() or a sequence (list/tuple) of JOIN()s.</p> <p>Available joins are JOIN, INNERJOIN, CROSSJOIN, STRAIGHTJOIN, LEFTJOIN, LEFTOUTERJOIN, NATURALJOIN, NATURALLEFTJOIN, NATURALLEFTOUTERJOIN, RIGHTJOIN, RIGHTOUTERJOIN, NATURALRIGHTJOIN, NATURALRIGHTOUTERJOIN, FULLJOIN, FULLOUTERJOIN, NATURALFULLJOIN, NATURALFULLOUTERJOIN, INNERJOINOn, LEFTJOINOn, LEFTOUTERJOINOn, RIGHTJOINOn, RIGHTOUTERJOINOn, FULLJOINOn, FULLOUTERJOINOn, INNERJOINUsing, LEFTJOINUsing, LEFTOUTERJOINUsing, RIGHTJOINUsing, RIGHTOUTERJOINUsing, FULLJOINUsing, FULLOUTERJOINUsing.</p> </div> <div class="section" id="how-can-i-join-a-table-with-itself"> <h3><a class="toc-backref" href="#id68">How can I join a table with itself?</a><a class="headerlink" href="#how-can-i-join-a-table-with-itself" title="Permalink to this headline">¶</a></h3> <p>Use Alias from <a class="reference external" href="SQLBuilder.html">SQLBuilder</a>. Example:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="kn">from</span> <span class="nn">sqlobject.sqlbuilder</span> <span class="k">import</span> <span class="n">Alias</span> <span class="n">alias</span> <span class="o">=</span> <span class="n">Alias</span><span class="p">(</span><span class="n">MyTable</span><span class="p">,</span> <span class="s2">"my_table_alias"</span><span class="p">)</span> <span class="n">MyTable</span><span class="o">.</span><span class="n">select</span><span class="p">(</span><span class="n">MyTable</span><span class="o">.</span><span class="n">q</span><span class="o">.</span><span class="n">name</span> <span class="o">==</span> <span class="n">alias</span><span class="o">.</span><span class="n">q</span><span class="o">.</span><span class="n">value</span><span class="p">)</span> </pre></div> </div> <p>will generate the query:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">SELECT</span> <span class="n">my_table</span><span class="o">.*</span> <span class="n">FROM</span> <span class="n">my_table</span><span class="p">,</span> <span class="n">my_table</span> <span class="n">AS</span> <span class="n">my_table_alias</span> <span class="n">WHERE</span> <span class="n">my_table</span><span class="o">.</span><span class="n">name</span> <span class="o">=</span> <span class="n">my_table_alias</span><span class="o">.</span><span class="n">value</span><span class="p">;</span> </pre></div> </div> </div> <div class="section" id="can-i-use-a-join-with-aliases"> <h3><a class="toc-backref" href="#id69">Can I use a JOIN() with aliases?</a><a class="headerlink" href="#can-i-use-a-join-with-aliases" title="Permalink to this headline">¶</a></h3> <p>Sure! That’s a situation the JOINs and aliases were primary developed for. Code:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="kn">from</span> <span class="nn">sqlobject.sqlbuilder</span> <span class="k">import</span> <span class="n">LEFTJOINOn</span><span class="p">,</span> <span class="n">Alias</span> <span class="n">alias</span> <span class="o">=</span> <span class="n">Alias</span><span class="p">(</span><span class="n">OtherTable</span><span class="p">,</span> <span class="s2">"other_table_alias"</span><span class="p">)</span> <span class="n">MyTable</span><span class="o">.</span><span class="n">select</span><span class="p">(</span><span class="n">MyTable</span><span class="o">.</span><span class="n">q</span><span class="o">.</span><span class="n">name</span> <span class="o">==</span> <span class="n">OtherTable</span><span class="o">.</span><span class="n">q</span><span class="o">.</span><span class="n">value</span><span class="p">,</span> <span class="n">join</span><span class="o">=</span><span class="n">LEFTJOINOn</span><span class="p">(</span><span class="n">MyTable</span><span class="p">,</span> <span class="n">alias</span><span class="p">,</span> <span class="n">MyTable</span><span class="o">.</span><span class="n">col1</span> <span class="o">==</span> <span class="n">alias</span><span class="o">.</span><span class="n">q</span><span class="o">.</span><span class="n">col2</span><span class="p">))</span> </pre></div> </div> <p>will result in the query:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">SELECT</span> <span class="n">my_table</span><span class="o">.*</span> <span class="n">FROM</span> <span class="n">other_table</span><span class="p">,</span> <span class="n">my_table</span> <span class="n">LEFT</span> <span class="n">JOIN</span> <span class="n">other_table</span> <span class="n">AS</span> <span class="n">other_table_alias</span> <span class="n">WHERE</span> <span class="n">my_table</span><span class="o">.</span><span class="n">name</span> <span class="o">==</span> <span class="n">other_table</span><span class="o">.</span><span class="n">value</span> <span class="n">AND</span> <span class="n">my_table</span><span class="o">.</span><span class="n">col1</span> <span class="o">=</span> <span class="n">other_table_alias</span><span class="o">.</span><span class="n">col2</span><span class="o">.</span> </pre></div> </div> </div> <div class="section" id="subqueries-subselects"> <h3><a class="toc-backref" href="#id70">Subqueries (subselects)</a><a class="headerlink" href="#subqueries-subselects" title="Permalink to this headline">¶</a></h3> <p>You can run queries with subqueries (subselects) on those DBMS that can do subqueries (MySQL supports subqueries from version 4.1).</p> <p>Use corresponding classes and functions from <a class="reference external" href="SQLBuilder.html">SQLBuilder</a>:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="kn">from</span> <span class="nn">sqlobject.sqlbuilder</span> <span class="k">import</span> <span class="n">EXISTS</span><span class="p">,</span> <span class="n">Select</span> <span class="n">select</span> <span class="o">=</span> <span class="n">Test1</span><span class="o">.</span><span class="n">select</span><span class="p">(</span><span class="n">EXISTS</span><span class="p">(</span><span class="n">Select</span><span class="p">(</span><span class="n">Test2</span><span class="o">.</span><span class="n">q</span><span class="o">.</span><span class="n">col2</span><span class="p">,</span> <span class="n">where</span><span class="o">=</span><span class="p">(</span><span class="n">Outer</span><span class="p">(</span><span class="n">Test1</span><span class="p">)</span><span class="o">.</span><span class="n">q</span><span class="o">.</span><span class="n">col1</span> <span class="o">==</span> <span class="n">Test2</span><span class="o">.</span><span class="n">q</span><span class="o">.</span><span class="n">col2</span><span class="p">))))</span> </pre></div> </div> <p>generates the query:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">SELECT</span> <span class="n">test1</span><span class="o">.</span><span class="n">id</span><span class="p">,</span> <span class="n">test1</span><span class="o">.</span><span class="n">col1</span> <span class="n">FROM</span> <span class="n">test1</span> <span class="n">WHERE</span> <span class="n">EXISTS</span> <span class="p">(</span><span class="n">SELECT</span> <span class="n">test2</span><span class="o">.</span><span class="n">col2</span> <span class="n">FROM</span> <span class="n">test2</span> <span class="n">WHERE</span> <span class="p">(</span><span class="n">test1</span><span class="o">.</span><span class="n">col1</span> <span class="o">=</span> <span class="n">test2</span><span class="o">.</span><span class="n">col2</span><span class="p">))</span> </pre></div> </div> <p>Note the usage of Outer - it is a helper to allow referring to a table in the outer query.</p> <p>Select() is used instead of .select() because you need to control what columns the inner query returns.</p> <p>Available queries are <code class="docutils literal notranslate"><span class="pre">IN()</span></code>, <code class="docutils literal notranslate"><span class="pre">NOTIN()</span></code>, <code class="docutils literal notranslate"><span class="pre">EXISTS()</span></code>, <code class="docutils literal notranslate"><span class="pre">NOTEXISTS()</span></code>, <code class="docutils literal notranslate"><span class="pre">SOME()</span></code>, <code class="docutils literal notranslate"><span class="pre">ANY()</span></code> and <code class="docutils literal notranslate"><span class="pre">ALL()</span></code>. The last 3 are used with comparison operators, like this: <code class="docutils literal notranslate"><span class="pre">somevalue</span> <span class="pre">=</span> <span class="pre">ANY(Select(...))</span></code>.</p> </div> <div class="section" id="utilities"> <h3><a class="toc-backref" href="#id71">Utilities</a><a class="headerlink" href="#utilities" title="Permalink to this headline">¶</a></h3> <p>Some useful utility functions are included with SQLObject. For more information see their module docstrings.</p> <ul class="simple"> <li><a class="reference external" href="module-sqlobject.util.csvexport.html">sqlobject.util.csvexport</a></li> </ul> </div> <div class="section" id="sqlbuilder"> <h3><a class="toc-backref" href="#id72">SQLBuilder</a><a class="headerlink" href="#sqlbuilder" title="Permalink to this headline">¶</a></h3> <p>For more information on SQLBuilder, read the <a class="reference external" href="SQLBuilder.html">SQLBuilder Documentation</a>.</p> <a class="reference external image-reference" href="https://sourceforge.net/projects/sqlobject"><img alt="Get SQLObject at SourceForge.net. Fast, secure and Free Open Source software downloads" class="noborder align-center" src="https://sourceforge.net/sflogo.php?group_id=74338&type=10" style="width: 80px; height: 15px;" /></a> </div> </div> </div> </div> </div> </div> <div class="clearer"></div> </div> <div class="related" role="navigation" aria-label="related navigation"> <h3>Navigation</h3> <ul> <li class="right" style="margin-right: 10px"> <a href="genindex.html" title="General Index" >index</a></li> <li class="right" > <a href="py-modindex.html" title="Python Module Index" >modules</a> |</li> <li class="right" > <a href="FAQ.html" title="SQLObject FAQ" >next</a> |</li> <li class="right" > <a href="Python3.html" title="SQLObject and Python 3" >previous</a> |</li> <li class="nav-item nav-item-0"><a href="index.html">SQLObject 3.7.0 documentation</a> »</li> </ul> </div> <div class="footer" role="contentinfo"> © Copyright 2004-2018, Ian Bicking and contributors. Created using <a href="http://sphinx-doc.org/">Sphinx</a> 1.7.4. </div> </body> </html>