<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Performing raw SQL queries — Django v1.1 documentation</title>
<link rel="stylesheet" href="../../_static/default.css" type="text/css" />
<link rel="stylesheet" href="../../_static/pygments.css" type="text/css" />
<script type="text/javascript">
var DOCUMENTATION_OPTIONS = {
URL_ROOT: '../../',
VERSION: '1.1',
COLLAPSE_MODINDEX: false,
FILE_SUFFIX: '.html',
HAS_SOURCE: true
};
</script>
<script type="text/javascript" src="../../_static/jquery.js"></script>
<script type="text/javascript" src="../../_static/doctools.js"></script>
<link rel="top" title="Django v1.1 documentation" href="../../index.html" />
<link rel="up" title="Models and databases" href="index.html" />
<link rel="next" title="Managing database transactions" href="transactions.html" />
<link rel="prev" title="Managers" href="managers.html" />
<script type="text/javascript" src="../../templatebuiltins.js"></script>
<script type="text/javascript">
(function($) {
if (!django_template_builtins) {
// templatebuiltins.js missing, do nothing.
return;
}
$(document).ready(function() {
// Hyperlink Django template tags and filters
var base = "../../ref/templates/builtins.html";
if (base == "#") {
// Special case for builtins.html itself
base = "";
}
// Tags are keywords, class '.k'
$("div.highlight\\-html\\+django span.k").each(function(i, elem) {
var tagname = $(elem).text();
if ($.inArray(tagname, django_template_builtins.ttags) != -1) {
var fragment = tagname.replace(/_/, '-');
$(elem).html("<a href='" + base + "#" + fragment + "'>" + tagname + "</a>");
}
});
// Filters are functions, class '.nf'
$("div.highlight\\-html\\+django span.nf").each(function(i, elem) {
var filtername = $(elem).text();
if ($.inArray(filtername, django_template_builtins.tfilters) != -1) {
var fragment = filtername.replace(/_/, '-');
$(elem).html("<a href='" + base + "#" + fragment + "'>" + filtername + "</a>");
}
});
});
})(jQuery);
</script>
</head>
<body>
<div class="document">
<div id="custom-doc" class="yui-t6">
<div id="hd">
<h1><a href="../../index.html">Django v1.1 documentation</a></h1>
<div id="global-nav">
<a title="Home page" href="../../index.html">Home</a> |
<a title="Table of contents" href="../../contents.html">Table of contents</a> |
<a title="Global index" href="../../genindex.html">Index</a> |
<a title="Module index" href="../../modindex.html">Modules</a>
</div>
<div class="nav">
« <a href="managers.html" title="Managers">previous</a>
|
<a href="../index.html" title="Using Django" accesskey="U">up</a>
|
<a href="transactions.html" title="Managing database transactions">next</a> »</div>
</div>
<div id="bd">
<div id="yui-main">
<div class="yui-b">
<div class="yui-g" id="topics-db-sql">
<div class="section" id="s-performing-raw-sql-queries">
<span id="s-topics-db-sql"></span><span id="performing-raw-sql-queries"></span><span id="topics-db-sql"></span><h1>Performing raw SQL queries<a class="headerlink" href="#performing-raw-sql-queries" title="Permalink to this headline">¶</a></h1>
<p>Feel free to write custom SQL statements in custom model methods and
module-level methods. The object <tt class="docutils literal"><span class="pre">django.db.connection</span></tt> represents the
current database connection, and <tt class="docutils literal"><span class="pre">django.db.transaction</span></tt> represents the
current database transaction. To use the database connection, call
<tt class="docutils literal"><span class="pre">connection.cursor()</span></tt> to get a cursor object. Then, call
<tt class="docutils literal"><span class="pre">cursor.execute(sql,</span> <span class="pre">[params])</span></tt> to execute the SQL and <tt class="docutils literal"><span class="pre">cursor.fetchone()</span></tt>
or <tt class="docutils literal"><span class="pre">cursor.fetchall()</span></tt> to return the resulting rows. After performing a data
changing operation, you should then call
<tt class="docutils literal"><span class="pre">transaction.commit_unless_managed()</span></tt> to ensure your changes are committed
to the database. If your query is purely a data retrieval operation, no commit
is required. For example:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="k">def</span> <span class="nf">my_custom_sql</span><span class="p">(</span><span class="bp">self</span><span class="p">):</span>
<span class="kn">from</span> <span class="nn">django.db</span> <span class="kn">import</span> <span class="n">connection</span><span class="p">,</span> <span class="n">transaction</span>
<span class="n">cursor</span> <span class="o">=</span> <span class="n">connection</span><span class="o">.</span><span class="n">cursor</span><span class="p">()</span>
<span class="c"># Data modifying operation - commit required</span>
<span class="n">cursor</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s">"UPDATE bar SET foo = 1 WHERE baz = </span><span class="si">%s</span><span class="s">"</span><span class="p">,</span> <span class="p">[</span><span class="bp">self</span><span class="o">.</span><span class="n">baz</span><span class="p">])</span>
<span class="n">transaction</span><span class="o">.</span><span class="n">commit_unless_managed</span><span class="p">()</span>
<span class="c"># Data retrieval operation - no commit required</span>
<span class="n">cursor</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s">"SELECT foo FROM bar WHERE baz = </span><span class="si">%s</span><span class="s">"</span><span class="p">,</span> <span class="p">[</span><span class="bp">self</span><span class="o">.</span><span class="n">baz</span><span class="p">])</span>
<span class="n">row</span> <span class="o">=</span> <span class="n">cursor</span><span class="o">.</span><span class="n">fetchone</span><span class="p">()</span>
<span class="k">return</span> <span class="n">row</span>
</pre></div>
</div>
<div class="section" id="s-transactions-and-raw-sql">
<span id="s-id1"></span><span id="transactions-and-raw-sql"></span><span id="id1"></span><h2>Transactions and raw SQL<a class="headerlink" href="#transactions-and-raw-sql" title="Permalink to this headline">¶</a></h2>
<p>If you are using transaction decorators (such as <tt class="docutils literal"><span class="pre">commit_on_success</span></tt>) to
wrap your views and provide transaction control, you don't have to make a
manual call to <tt class="docutils literal"><span class="pre">transaction.commit_unless_managed()</span></tt> -- you can manually
commit if you want to, but you aren't required to, since the decorator will
commit for you. However, if you don't manually commit your changes, you will
need to manually mark the transaction as dirty, using
<tt class="docutils literal"><span class="pre">transaction.set_dirty()</span></tt>:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="nd">@commit_on_success</span>
<span class="k">def</span> <span class="nf">my_custom_sql_view</span><span class="p">(</span><span class="n">request</span><span class="p">,</span> <span class="n">value</span><span class="p">):</span>
<span class="kn">from</span> <span class="nn">django.db</span> <span class="kn">import</span> <span class="n">connection</span><span class="p">,</span> <span class="n">transaction</span>
<span class="n">cursor</span> <span class="o">=</span> <span class="n">connection</span><span class="o">.</span><span class="n">cursor</span><span class="p">()</span>
<span class="c"># Data modifying operation</span>
<span class="n">cursor</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s">"UPDATE bar SET foo = 1 WHERE baz = </span><span class="si">%s</span><span class="s">"</span><span class="p">,</span> <span class="p">[</span><span class="n">value</span><span class="p">])</span>
<span class="c"># Since we modified data, mark the transaction as dirty</span>
<span class="n">transaction</span><span class="o">.</span><span class="n">set_dirty</span><span class="p">()</span>
<span class="c"># Data retrieval operation. This doesn't dirty the transaction,</span>
<span class="c"># so no call to set_dirty() is required.</span>
<span class="n">cursor</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s">"SELECT foo FROM bar WHERE baz = </span><span class="si">%s</span><span class="s">"</span><span class="p">,</span> <span class="p">[</span><span class="n">value</span><span class="p">])</span>
<span class="n">row</span> <span class="o">=</span> <span class="n">cursor</span><span class="o">.</span><span class="n">fetchone</span><span class="p">()</span>
<span class="k">return</span> <span class="n">render_to_response</span><span class="p">(</span><span class="s">'template.html'</span><span class="p">,</span> <span class="p">{</span><span class="s">'row'</span><span class="p">:</span> <span class="n">row</span><span class="p">})</span>
</pre></div>
</div>
<p>The call to <tt class="docutils literal"><span class="pre">set_dirty()</span></tt> is made automatically when you use the Django ORM
to make data modifying database calls. However, when you use raw SQL, Django
has no way of knowing if your SQL modifies data or not. The manual call to
<tt class="docutils literal"><span class="pre">set_dirty()</span></tt> ensures that Django knows that there are modifications that
must be committed.</p>
</div>
<div class="section" id="s-connections-and-cursors">
<span id="connections-and-cursors"></span><h2>Connections and cursors<a class="headerlink" href="#connections-and-cursors" title="Permalink to this headline">¶</a></h2>
<p><tt class="docutils literal"><span class="pre">connection</span></tt> and <tt class="docutils literal"><span class="pre">cursor</span></tt> mostly implement the standard <a class="reference external" href="http://www.python.org/dev/peps/pep-0249/">Python DB-API</a>
(except when it comes to <a class="reference external" href="transactions.html#topics-db-transactions"><em>transaction handling</em></a>).
If you're not familiar with the Python DB-API, note that the SQL statement in
<tt class="docutils literal"><span class="pre">cursor.execute()</span></tt> uses placeholders, <tt class="docutils literal"><span class="pre">"%s"</span></tt>, rather than adding parameters
directly within the SQL. If you use this technique, the underlying database
library will automatically add quotes and escaping to your parameter(s) as
necessary. (Also note that Django expects the <tt class="docutils literal"><span class="pre">"%s"</span></tt> placeholder, <em>not</em> the
<tt class="docutils literal"><span class="pre">"?"</span></tt> placeholder, which is used by the SQLite Python bindings. This is for
the sake of consistency and sanity.)</p>
</div>
<div class="section" id="s-an-easier-option">
<span id="an-easier-option"></span><h2>An easier option?<a class="headerlink" href="#an-easier-option" title="Permalink to this headline">¶</a></h2>
<p>A final note: If all you want to do is a custom <tt class="docutils literal"><span class="pre">WHERE</span></tt> clause, you can just
use the <tt class="docutils literal"><span class="pre">where</span></tt>, <tt class="docutils literal"><span class="pre">tables</span></tt> and <tt class="docutils literal"><span class="pre">params</span></tt> arguments to the
<a class="reference external" href="../../ref/models/querysets.html#queryset-extra"><em>extra clause</em></a> in the standard queryset API.</p>
</div>
</div>
</div>
</div>
</div>
<div class="yui-b" id="sidebar">
<div class="sphinxsidebar">
<div class="sphinxsidebarwrapper">
<h3><a href="../../contents.html">Table Of Contents</a></h3>
<ul>
<li><a class="reference external" href="#">Performing raw SQL queries</a><ul>
<li><a class="reference external" href="#transactions-and-raw-sql">Transactions and raw SQL</a></li>
<li><a class="reference external" href="#connections-and-cursors">Connections and cursors</a></li>
<li><a class="reference external" href="#an-easier-option">An easier option?</a></li>
</ul>
</li>
</ul>
<h3>Browse</h3>
<ul>
<li>Prev: <a href="managers.html">Managers</a></li>
<li>Next: <a href="transactions.html">Managing database transactions</a></li>
</ul>
<h3>You are here:</h3>
<ul>
<li>
<a href="../../index.html">Django v1.1 documentation</a>
<ul><li><a href="../index.html">Using Django</a>
<ul><li><a href="index.html">Models and databases</a>
<ul><li>Performing raw SQL queries</li></ul>
</li></ul></li></ul>
</li>
</ul>
<h3>This Page</h3>
<ul class="this-page-menu">
<li><a href="../../_sources/topics/db/sql.txt"
rel="nofollow">Show Source</a></li>
</ul>
<div id="searchbox" style="display: none">
<h3>Quick search</h3>
<form class="search" action="../../search.html" method="get">
<input type="text" name="q" size="18" />
<input type="submit" value="Go" />
<input type="hidden" name="check_keywords" value="yes" />
<input type="hidden" name="area" value="default" />
</form>
<p class="searchtip" style="font-size: 90%">
Enter search terms or a module, class or function name.
</p>
</div>
<script type="text/javascript">$('#searchbox').show(0);</script>
</div>
</div>
<h3>Last update:</h3>
<p class="topless">Feb 18, 2011</p>
</div>
</div>
<div id="ft">
<div class="nav">
« <a href="managers.html" title="Managers">previous</a>
|
<a href="../index.html" title="Using Django" accesskey="U">up</a>
|
<a href="transactions.html" title="Managing database transactions">next</a> »</div>
</div>
</div>
<div class="clearer"></div>
</div>
</body>
</html>
distrib
>
Mandriva
>
current
>
i586
>
media
>
main-updates
>
by-pkgid
>
57efe471f3561e70a829edf1b0e9f507
>
files
>
2373
