<metaname="description" content="Full Stack Python explains each layer of the web application stack, from the server up through the rendering in a user's browser.">
<p>A database is an abstraction on top of an operating system's file system to
ease creating, reading, updating, and deleting persistent data. </p>
<h2>Why are databases necessary?</h2>
<p>At a high level web applications store data and present it to users in a
useful way. For example, Google stores data about roads and provides
directions to get from one location to another by driving through the
<ahref="https://www.google.com/maps/">Maps</a> application. Driving directions are
possible because the data is stored in a structured way. </p>
<p>Databases make structured storage reliable and fast. They also give you a
mental framework for how the data should be saved and retrieved instead of
having to figure out what to do with the data every time you build a new
application.</p>
<h2>Relational databases</h2>
<p>The database storage abstraction most commonly used in Python web development
is sets of relational tables. Alternative storage abstractions are explained
in the <ahref="../no-sql-datastore.html">NoSQL</a> section of this guide.</p>
<p>Relational databases store all data in a series of tables. Interconnections
between the tables are specified as <em>foreign keys</em>.</p>
<p>Databases storage implementations vary in complexity. SQLite, a database
included with Python, creates a single file for all data per database.
Other databases such as Oracle, PostgreSQL, and MySQL have more complicated
persistence schemes while offering additional advanced features that are
useful for web application data storage.</p>
<p><ahref="http://www.postgresql.org/">PostgreSQL</a> and
<ahref="http://www.mysql.com/">MySQL</a> are two of the most common open source
databases for storing Python web application data.</p>
<p><ahref="http://www.sqlite.org/">SQLite</a> is a database that is stored in a single
file on disk. SQLite is built into Python but is only built for access
by a single connection at a time. Therefore is highly recommended to not
<ahref="https://docs.djangoproject.com/en/dev/ref/databases/#database-is-locked-errors">run a production web application with SQLite</a>.</p>
<h2>PostgreSQL</h2>
<p>PostgreSQL is the recommended relational database for working with Python
web applications. PostgreSQL's feature set, active development and stability
contribute to its usage as the backend for millions of applications live
on the Web today.</p>
<h3>PostgreSQL resources</h3>
<ul>
<li>
<p>This post on
<ahref="http://killtheyak.com/use-postgresql-with-django-flask/">using PostgreSQL with Django or Flask</a>
is a great quickstart guide for either framework.</p>
</li>
<li>
<p><ahref="http://postgresweekly.com/">PostgreSQL Weekly</a> is a weekly newsletter of
PostgreSQL content from around the web.</p>
</li>
<li>
<p>Braintree wrote about their experiences <ahref="https://www.braintreepayments.com/braintrust/scaling-postgresql-at-braintree-four-years-of-evolution">scaling PostgreSQL</a>.
The post is an inside look at the evolution of Braintree's usage of the database.</p>
</li>
<li>
<p>This post estimates the <ahref="http://hans.io/blog/2014/02/19/postgresql_connection/index.html">costs of a PostgreSQL connection</a>.</p>
</li>
<li>
<p>There is no such thing as total security but this IBM article covers
<ahref="http://www.ibm.com/developerworks/library/os-postgresecurity/">hardening a PostgreSQL database</a>. </p>
</li>
<li>
<p>Craig Kerstien's wrote a detailed post about <ahref="http://www.craigkerstiens.com/2012/10/01/understanding-postgres-performance/">understanding PostgreSQL performance</a>.</p>
</li>
<li>
<p><ahref="http://instagram-engineering.tumblr.com/post/40781627982/handling-growth-with-postgres-5-tips-from-instagram">Handling growth with Postgres</a>
provides 5 specific tips from Instagram's engineering team on how to scale
the design of your PostgreSQL database.</p>
</li>
</ul>
<h2>MySQL</h2>
<p>MySQL is another viable open source database backend option for Python web
applications. MySQL has a slightly easier initial learning curve than
PostgreSQL. The database is deployed in production at some of the highest
and <ahref="http://readwrite.com/2013/09/14/google-waves-goodbye-to-mysql-in-favor-of-mariadb">Google</a>.
MySQL remains a viable database option but I always recommend new Python
developers learn PostgreSQL if they do not already know MySQL.</p>
<h3>MySQL resources</h3>
<ul>
<li>
<p><ahref="http://designm.ag/tutorials/28-beginners-tutorials-for-learning-about-mysql-databases/">28 Beginner's Tutorials for Learning about MySQL Databases</a>
is a curated collection on various introductory MySQL topics.</p>
</li>
<li>
<p>This tutorial shows how to install <ahref="http://www.cs.wcupa.edu/rkline/index/mysql-lin.html">MySQL on Ubuntu</a>.</p>
</li>
</ul>
<h2>Connecting to a database with Python</h2>
<p>To work with a relational database using Python, you need to use a code
library. The most common libraries for relational databases are:</p>
<ul>
<li>
<p><ahref="http://initd.org/psycopg/">psycopg2</a> for PostgreSQL</p>
</li>
<li>
<p><ahref="https://pypi.python.org/pypi/MySQL-python/1.2.4">MySQLdb</a> for MySQL</p>
</li>
<li>
<p><ahref="http://cx-oracle.sourceforge.net/">cx_Oracle</a> for Oracle</p>
</li>
</ul>
<p>SQLite support is built into Python 2.7+ and therefore a separate library
is not necessary. Simply "import sqlite3" to begin interfacing with the
single file-based database.</p>
<h2>Object-Relational Mapping</h2>
<p>Object-relational mappers (ORMs) allow developers to access data from a
backend by writing Python code instead of SQL queries. Each web
<h4>Interested in a complete Full Stack Python book with detailed tutorials and example code? Sign up here and you'll get an alert email if a book is created. No other emails will be sent other than sign up confirmation.</h4>
$("#mc-embedded-subscribe-form").unbind('submit');//remove the validator so we can get into beforeSubmit on the ajaxform, which then calls the validator