<html lang="en">

<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">

<meta charset="utf-8">

<meta http-equiv="X-UA-Compatible" content="IE=edge">

<meta name="viewport" content="width=device-width, initial-scale=1.0">

<meta name="author" content="Matt Makai">

<meta name="description" content="Relational databases serve the critical role of persisting data in many Python applications.">

<link rel="shortcut icon" href="//static.fullstackpython.com/fsp-fav.png">

<title>Databases - Full Stack Python</title>

<!--[if lt IE 9]>

<link href="theme/css/f.min.css" rel="stylesheet">

<div style="padding: 0 0 20px 0; margin: 0 0 20px 0; background-color: #22B24C;">

<div class="container">

<p class="banner"><a href="https://www.gumroad.com/l/python-deployments" style="color: #fff">Learning Python web development? Check out the just-updated Guide to Deployments book</a>!</p>

</div>

</div> <a href="https://github.com/makaimc/fullstackpython.com"><img style="position: absolute; top: 0; right: 0; border: 0;" src="/theme/img/fork.png" alt="Fork me on GitHub"></a>

<div class="container">

<div class="row">

<div class="col-md-12">

<div class="logo-header-section">

<a href="/" style="text-decoration: none; border: none;"><img src="theme/img/logo.png" height="52" width="52" class="logo-image" style="padding-top: 1px;" alt="Full Stack Python logo"></a>

<span class="logo-title"><a href="/">Full Stack Python</a></span>

</div>

</div>

</div><div class="row">

<div class="col-md-8">

<h1>Databases</h1>

<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

<a href="https://www.google.com/maps/">Maps</a> application. Driving directions are

possible because the data is stored in a structured format. </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>

<div class="well see-also">

Databases are a concept with many implementations,

including <a href="/postgresql.html">PostgreSQL</a>,

<a href="/mysql.html">MySQL</a> and

<a href="/sqlite.html">SQLite</a>. Non-relational databases

called <a href="/no-sql-datastore.html">NoSQL data stores</a> also exist.

Learn more in the <a href="/data.html">data</a> chapter or view the

<a href="/table-of-contents.html">table of contents</a> for all topics.

<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

on the <a href="/no-sql-datastore.html">NoSQL</a> page.</p>

<p>Relational databases store data in a series of tables. Interconnections

between the tables are specified as <em>foreign keys</em>. A foreign key is a

unique reference from one row in a relational table to another row in

a table, which can be the same table but is most commonly a different table.</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 <a href="/postgresql.html">PostgreSQL</a>,

<a href="/mysql.html">MySQL</a>, Oracle and Microsoft SQL Server have more

complicated persistence schemes while offering additional advanced features

that are useful for web application data storage. These advanced

features include but are not limited to:</p>

<li>data replication between a master database and one or more read-only slave

instances</li>

<li>advanced column types that can efficiently store semi-structured data

such as JavaScript Object Notation (JSON)</li>

<li>sharding, which allows horizontal scaling of multiple databases that

each serve as read-write instances at the cost of latency in data

consistency</li>

<li>monitoring, statistics and other useful runtime information for

database schemas and tables</li>

<p>Typically web applications start with a single database instance such

as PostgreSQL with a straightforward schema. Over time the database

schema evolves to a more complex structure using schema migrations and

advanced features such as replication, sharding and monitoring become

more useful as database utilization increases based on the application

users' needs.</p>

<h2>Most common databases for Python web apps</h2>

<p><a href="http://www.postgresql.org/">PostgreSQL</a> and

<a href="http://www.mysql.com/">MySQL</a> are two of the most common open source

databases for storing Python web applications' data.</p>

<p><a href="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

<a href="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>

<p>Learn more about using PostgreSQL with Python on the

<a href="/postgresql.html">PostgreSQL page</a>.</p>

<h2>MySQL</h2>

<p>MySQL is another viable open source database implementation for Python

applications. MySQL has a slightly easier initial learning curve than

PostgreSQL but is not as feature rich.</p>

<p>Find out about Python applications with a MySQL backed on the dedicated

<a href="/mysql.html">MySQL page</a>.</p>

<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>

<p><a href="http://initd.org/psycopg/">psycopg2</a> for PostgreSQL</p>

<p><a href="https://pypi.python.org/pypi/MySQL-python/1.2.5">MySQLdb</a> for MySQL</p>

<p><a href="http://cx-oracle.sourceforge.net/">cx_Oracle</a> for Oracle</p>

<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

application framework handles integrating ORMs differently. There's

<a href="/object-relational-mappers-orms.html">an entire page on object-relational mapping</a>

(ORMs) that you should read to get a handle on this subject.</p>

<h2>Database third-party services</h2>

<p>Numerous companies run scalable database servers as a hosted service.

Hosted databases can often provide automated backups and recovery,

tightened security configurations and easy vertical scaling, depending on the

provider.</p>

<p><a href="http://aws.amazon.com/rds/">Amazon Relational Database Service (RDS)</a>

provides pre-configured MySQL and PostgreSQL instances. The instances can

be scaled to larger or smaller configurations based on storage and performance

needs.</p>

<p><a href="https://developers.google.com/cloud-sql/">Google Cloud SQL</a> is a service

with managed, backed up, replicated, and auto-patched MySQL instances. Cloud

SQL integrates with Google App Engine but can be used independently as well.</p>

<p><a href="http://www.gobitcan.com/">BitCan</a> provides both MySQL and MongoDB hosted

databases with extensive backup services.</p>

<p><a href="https://www.elephantsql.com/">ElephantSQL</a> is a software-as-a-service company

that hosts PostgreSQL databases and handles the server configuration, backups

and data connections on top of Amazon Web Services instances.</p>

<h2>General database resources</h2>

<p><a href="http://coding-geek.com/how-databases-work/">How does a relational database work?</a>

is a detailed longform post on the sorting, searching, merging and other

operations we often take for granted when using an established relational

database such as PostgreSQL.</p>

<p><a href="https://medium.com/@jeeyoungk/why-i-love-databases-1d4cc433685f">Why I Love Databases</a>

is a great read on the CAP Theorem, distributed systems and other topics

that are at the core of database theory and implementation. Well worth

the time to read.</p>

<p><a href="http://www.craigkerstiens.com/2016/01/08/writing-better-sql/">Writing better SQL</a>

is a short code styling guide to make your queries easier to read.</p>

<p><a href="http://db-engines.com/en/ranking">DB-Engines</a> ranks the most popular

database management systems.</p>

<p><a href="http://dbweekly.com/">DB Weekly</a> is a weekly roundup of general database

articles and resources.</p>

<p><a href="https://julien.danjou.info/blog/2014/db-integration-testing-strategies-python">Databases integration testing strategies</a>

covers a difficult topic that comes up on every real world project.</p>

<p><a href="http://techspot.zzzeek.org/2015/02/15/asynchronous-python-and-databases/">Asynchronous Python and Databases</a>

is an in-depth article covering why many Python database drivers cannot

be used without modification due to the differences in blocking versus

asychronous event models. Definitely worth a read if you are using

<a href="/websockets.html">WebSockets</a> via Tornado or gevent.</p>

<p><a href="http://www.pg-versus-ms.com/">PostgreSQL vs. MS SQL Server</a> is one

perspective on the differences between the two database servers from a

data analyst.</p>

<h2>Databases learning checklist</h2>

<p>Install PostgreSQL on your server. Assuming you went with Ubuntu run

<code>sudo apt-get install postgresql</code>.</p>

<p>Make sure the <a href="http://initd.org/psycopg/">psycopg2</a> library is in your

application's dependencies.</p>

<p>Configure your web application to connect to the PostgreSQL instance.</p>

<p>Create models in your ORM, either with Django's

<a href="https://docs.djangoproject.com/en/dev/topics/db/">built-in ORM</a> or

<a href="http://www.sqlalchemy.org/">SQLAlchemy with Flask</a>. </p>

<p>Build your database tables or sync the ORM models with the PostgreSQL

instance, if you're using an ORM.</p>

<p>Start creating, reading, updating and deleting data in the database

from your web application.</p>

<h3>What's next to get your app running?</h3>

<div class="row">

<div class="col-md-4">

<div class="well select-next">

<a href="/no-sql-datastore.html" class="btn btn-success btn-full"><svg width="34" height="30" viewBox="0 0 1792 1792" xmlns="http://www.w3.org/2000/svg"><path d="M1151 960h316q-1-3-2.5-8t-2.5-8l-212-496h-708l-212 496q-1 2-2.5 8t-2.5 8h316l95 192h320zm513 30v482q0 26-19 45t-45 19h-1408q-26 0-45-19t-19-45v-482q0-62 25-123l238-552q10-25 36.5-42t52.5-17h832q26 0 52.5 17t36.5 42l238 552q25 61 25 123z" fill="#fff"/></svg></a>

<p class="under-btn">What're these NoSQL data stores hipster developers keep talking about?</p> </div>

</div>

<div class="col-md-4">

<div class="well select-next">

<a href="/cascading-style-sheets.html" class="btn btn-success btn-full"><svg width="28" height="30" viewBox="0 0 1792 1792" xmlns="http://www.w3.org/2000/svg"><path d="M275 128h1505l-266 1333-804 267-698-267 71-356h297l-29 147 422 161 486-161 68-339h-1208l58-297h1209l38-191h-1208z" fill="#fff"/></svg></a>

<p class="under-btn">My app runs but looks awful. How do I style the user interface?</p> </div>

</div>

<div class="col-md-4">

<div class="well select-next">

<a href="/javascript.html" class="btn btn-success btn-full"><svg width="28" height="30" viewBox="0 0 1792 1792" xmlns="http://www.w3.org/2000/svg"><path d="M1152 896q0-106-75-181t-181-75-181 75-75 181 75 181 181 75 181-75 75-181zm512-109v222q0 12-8 23t-20 13l-185 28q-19 54-39 91 35 50 107 138 10 12 10 25t-9 23q-27 37-99 108t-94 71q-12 0-26-9l-138-108q-44 23-91 38-16 136-29 186-7 28-36 28h-222q-14 0-24.5-8.5t-11.5-21.5l-28-184q-49-16-90-37l-141 107q-10 9-25 9-14 0-25-11-126-114-165-168-7-10-7-23 0-12 8-23 15-21 51-66.5t54-70.5q-27-50-41-99l-183-27q-13-2-21-12.5t-8-23.5v-222q0-12 8-23t19-13l186-28q14-46 39-92-40-57-107-138-10-12-10-24 0-10 9-23 26-36 98.5-107.5t94.5-71.5q13 0 26 10l138 107q44-23 91-38 16-136 29-186 7-28 36-28h222q14 0 24.5 8.5t11.5 21.5l28 184q49 16 90 37l142-107q9-9 24-9 13 0 25 10 129 119 165 170 7 8 7 22 0 12-8 23-15 21-51 66.5t-54 70.5q26 50 41 98l183 28q13 2 21 12.5t8 23.5z" fill="#fff"/></svg></a>

<p class="under-btn">How do I use JavaScript with my Python web application?</p> </div>

</div>

</div><div id="mc_embed_signup">

<form action="//mattmakai.us2.list-manage.com/subscribe/post?u=b7e774f0c4f05dcebbfee183d&amp;id=b22335388d" method="post" id="mc-embedded-subscribe-form" name="mc-embedded-subscribe-form" class="validate" target="_blank" novalidate>

<div id="mc_embed_signup_scroll">

<h4>Sign up here to receive an email with major updates to this site and Python tutorials delivered to your inbox once a month.</h4>

<div class="row">

<div class="col-md-9">

<input type="email" value="" name="EMAIL" class="email form-control" id="mce-EMAIL" placeholder="email address" required>

<div style="position: absolute; left: -5000px;"><input type="text" name="b_b7e774f0c4f05dcebbfee183d_b22335388d" tabindex="-1" value=""></div>

</div>

<div class="col-md-3">

<div class="clear"><input type="submit" value="Subscribe" name="subscribe" id="mc-embedded-subscribe" class="btn btn-success" style="font-family: 'Helvetica Neue';"></div>

</div>

</div>

</div>

</div>

<div class="col-md-offset-1 col-md-3" id="sidebar">

<div class="panel panel-success">

<div class="panel-body">

<a href="http://www.deploypython.com/"><img src="//static.fullstackpython.com/fsp-deployment-guide.png" alt="The Full Stack Python Guide to Deployments" width="100%"></a>

<p style="font-size: .8em; margin-top: 10px;">Searching for a complete, step-by-step deployment walkthrough? Learn more about <a href="http://www.deploypython.com/">The Full Stack Python Guide to Deployments book</a>.

</p>

</div>

</div><div class="panel panel-success">

<div class="panel-heading">

<h3 class="panel-head">Email Updates</h3>

</div>

<div class="panel-body">

<div id="mc_embed_signup">

<form action="//mattmakai.us2.list-manage.com/subscribe/post?u=b7e774f0c4f05dcebbfee183d&amp;id=b22335388d" method="post" id="mc-embedded-subscribe-form" name="mc-embedded-subscribe-form" class="validate" target="_blank" novalidate>

<div id="mc_embed_signup_scroll">

<h5>Sign up to get a monthly email with Python tutorials and major updates to this site.</h5>

<input type="email" value="" name="EMAIL" class="email form-control" id="mce-EMAIL" placeholder="email address" required>

<div style="position: absolute; left: -5000px;"><input type="text" name="b_b7e774f0c4f05dcebbfee183d_b22335388d" tabindex="-1" value=""></div>

<input type="submit" value="Subscribe" name="subscribe" id="mc-embedded-subscribe" class="btn btn-success" style="font-family: 'Helvetica Neue'; margin-top: 5px;">

</div>

</form>

</div>

</div>

</div> <div class="panel panel-success">

<div class="panel-heading">

<h3 class="panel-head"><a href="/table-of-contents.html" style="color: #fff;">Chapters</a></h3>

</div>

<div class="list-group">

<a href="/introduction.html" class="list-group-item smaller-item " style='font-family: "Helvetica Neue",sans-serif;'>1. Introduction</a><a href="/development-environments.html" class="list-group-item smaller-item " style='font-family: "Helvetica Neue",sans-serif;'>2. Development Environments</a><a href="/python-programming-language.html" class="list-group-item smaller-item " style='font-family: "Helvetica Neue",sans-serif;'>3. Core Language</a><a href="/web-development.html" class="list-group-item smaller-item " style='font-family: "Helvetica Neue",sans-serif;'>4. Web Development</a><a href="/web-frameworks.html" class="list-group-item smaller-item " style='font-family: "Helvetica Neue",sans-serif;'>5. Web Frameworks</a><a href="/data.html" class="list-group-item smaller-item " style='font-family: "Helvetica Neue",sans-serif;'>6. Data</a> <a href="/databases.html" class="list-group-item smaller-item active" style='font-family: "Helvetica Neue",sans-serif;'>&raquo; Relational Databases</a>

<a href="/application-programming-interfaces.html" class="list-group-item smaller-item " style='font-family: "Helvetica Neue",sans-serif;'>7. Web APIs</a><a href="/deployment.html" class="list-group-item smaller-item " style='font-family: "Helvetica Neue",sans-serif;'>8. Deployment</a><a href="/testing.html" class="list-group-item smaller-item " style='font-family: "Helvetica Neue",sans-serif;'>9. Testing</a><a href="/change-log.html" class="list-group-item smaller-item " style='font-family: "Helvetica Neue",sans-serif;'>13. Change Log</a><a href="/table-of-contents.html" class="list-group-item smaller-item" style='font-family: "Helvetica Neue",san-serif;background-color:#22B24C; color: #fff;'>...or <span style="border-bottom: 1px dotted;">view the full table of contents</span>.</a> </div>

</div>

<div class="panel panel-success">

<div class="panel-heading"><h3 class="panel-head">Databases</h3></div>

<div class="panel-body">

Major updates are tweeted via

<a href="https://twitter.com/fullstackpython">@fullstackpython</a>.

<hr/>

Need more detailed tutorials than you see here?

<a href="http://www.deploypython.com/">Learn more about The Full Stack Python Guide to Deployments book.</a>

</div>

</div>

<hr/>

</div>

<div class="container">

<div class="footer pull-right">

<a href="http://www.mattmakai.com/" class="underline">Matt Makai</a>

2016

</div>

</div>

(function(i,s,o,g,r,a,m){i['GoogleAnalyticsObject']=r;i[r]=i[r]||function(){

(i[r].q=i[r].q||[]).push(arguments)},i[r].l=1*new Date();a=s.createElement(o),

m=s.getElementsByTagName(o)[0];a.async=1;a.src=g;m.parentNode.insertBefore(a,m)

})(window,document,'script','//www.google-analytics.com/analytics.js','ga');

ga('create', 'UA-19910497-7', 'auto');

ga('send', 'pageview');

<script type='text/javascript'>

var trackOutboundLink = function(url) { ga('send', 'event', 'outbound', 'click', url, {'hitCallback': function () { document.location = url; } }); }