This repository was archived by the owner on Feb 2, 2025. It is now read-only.
forked from mattmakai/fullstackpython.com
-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathdatabases.html
More file actions
233 lines (226 loc) · 14 KB
/
databases.html
File metadata and controls
233 lines (226 loc) · 14 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
<!DOCTYPE html>
<html lang="en"><head><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="description" content="Full Stack Python shows how an entire Python web application is built and deployed. Each section of the guide explains a different key concept, from the server through the Python WSGI web framework to the front end JavaScript.">
<meta name="author" content="Matt Makai">
<link rel="shortcut icon" href="theme/img/full-stack-python-logo-bw.png">
<title>Full Stack Python</title>
<!-- Bootstrap core CSS -->
<link href="theme/css/fsp.css" rel="stylesheet">
<!--[if lt IE 9]>
<script src="https://oss.maxcdn.com/libs/html5shiv/3.7.0/html5shiv.js"></script>
<script src="https://oss.maxcdn.com/libs/respond.js/1.3.0/respond.min.js"></script>
<![endif]-->
<style>
html,
body {
font-size: 18px;
color: #222;
background: #fefefe;
}
body {
padding-top: 30px;
}
.footer {
padding: 20px 0 30px 0;
}
a, a:hover {border-bottom: 1px dotted; color: #444;}
a:hover {text-decoration: none; color: #000;}
.logo-title {font-size: 56px; color: #403072; padding-top: 80px;
font-family: "News Cycle", "Arial Narrow Bold", sans-serif;
font-weight: bold; line-height: 30px; margin-left: 5px;}
.logo-title a, .logo-title a:hover {color: #000; text-decoration: none;
border-bottom: none;}
.logo-title a:hover {color: gray;}
.logo-image {vertical-align: top; border: none;}
a.list-group-item.active {background: #444; border: 1px solid #222;}
a.list-group-item.active:hover {background: #444; border: 1px solid #222;}
#sidebar {margin-top: 30px;}
@media (max-width: 600px) {
.logo-header-section {
margin: 20px 32px 0 0;
}
}
</style>
<script type="text/javascript">
var _gaq = _gaq || [];
_gaq.push(['_setAccount', 'UA-19910497-7']);
_gaq.push(['_trackPageview']);
(function() {
var ga = document.createElement('script'); ga.type = 'text/javascript'; ga.async = true;
ga.src = ('https:' == document.location.protocol ? 'https://ssl' : 'http://www') + '.google-analytics.com/ga.js';
var s = document.getElementsByTagName('script')[0]; s.parentNode.insertBefore(ga, s);
})();
</script>
</head>
<body>
<a href="https://github.com/makaimc/fullstackpython.github.com" class="github">
<img style="position: absolute; top: 0; right: 0; border: 0;" src="http://s3.amazonaws.com/github/ribbons/forkme_right_darkblue_121621.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/full-stack-python-logo-bw.png" height="42" width="42" class="logo-image" /></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. The
database storage abstraction most commonly used in Python web development is
sets of relational tables. Alternative storage abstractions are explained in
the <a class="reference external" href="../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><a class="reference external" href="http://www.postgresql.org/">PostgreSQL</a> and
<a class="reference external" href="http://www.mysql.com/">MySQL</a> are two of the most common open source
databases for storing Python web application data.</p>
<p><a class="reference external" 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 class="reference external" href="https://docs.djangoproject.com/en/dev/ref/databases/#database-is-locked-errors">run a production web application with SQLite</a>.</p>
<div class="section" id="id1">
<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>
<div class="section" id="postgresql-resources">
<h3>PostgreSQL resources</h3>
<p>This post on "<a class="reference external" href="http://killtheyak.com/use-postgresql-with-django-flask/">Use PostgreSQL with Django or Flask</a>"
is a great quickstart guide for either framework.</p>
<p><a class="reference external" href="http://postgresweekly.com/">PostgreSQL Weekly</a> is a weekly newsletter of
PostgreSQL content from around the web.</p>
<p><a class="reference external" href="https://www.braintreepayments.com/braintrust/scaling-postgresql-at-braintree-four-years-of-evolution">Scaling PostgreSQL at Braintree</a>. Fascinating
inside look at the evolution of the database's usage at Braintree.</p>
<p><a class="reference external" href="http://www.ibm.com/developerworks/library/os-postgresecurity/">Total security in a PostgreSQL database</a>.
There is no such thing as total security but this is a good article anyway.</p>
<p><a class="reference external" href="http://www.craigkerstiens.com/2012/10/01/understanding-postgres-performance/">Understanding PostgreSQL performance</a></p>
<p><a class="reference external" href="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>
</div>
</div>
<div class="section" id="id2">
<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
trafficked sites such as
<a class="reference external" href="https://blog.twitter.com/2012/mysql-twitter">Twitter</a>,
<a class="reference external" href="https://www.facebook.com/notes/facebook-engineering/mysql-and-database-engineering-mark-callaghan/10150599729938920">Facebook</a>
and <a class="reference external" href="http://www.mysql.com/customers/">many others major organizations</a>.
However, since the company focused on MySQL development,
<a class="reference external" href="http://en.wikipedia.org/wiki/MySQL_AB">MySQL AB</a>, was
purchased by Sun Microsystems (which was in turn purchased by Oracle), there
have been major defections away from the database by
<a class="reference external" href="http://www.zdnet.com/wikipedia-moving-from-mysql-to-mariadb-7000008912/">Wikipedia</a>
and <a class="reference external" href="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>
<div class="section" id="mysql-resources">
<h3>MySQL resources</h3>
<p><a class="reference external" href="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>
<p>This tutorial shows how to install <a class="reference external" href="http://www.cs.wcupa.edu/rkline/index/mysql-lin.html">MySQL on Ubuntu</a>.</p>
</div>
</div>
<div class="section" id="connecting-to-a-database-with-python">
<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 class="reference external" href="http://initd.org/psycopg/">psycopg2</a> for PostgreSQL</p>
<p><a class="reference external" href="https://pypi.python.org/pypi/MySQL-python/1.2.4">MySQLdb</a> for MySQL</p>
<p><a class="reference external" 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>
</div>
<div class="section" id="object-relational-mapping">
<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.</p>
<p>Django provides an ORM with its core functionality. Flask leaves using an
ORM up to an extension, such as
<a class="reference external" href="http://pythonhosted.org/Flask-SQLAlchemy/">Flask-SQLALchemy</a>.</p>
<p>Developers can also use ORMs without a web framework, such as when
creating a data analysis tool or a batch script without a user interface.
Currently, the most widely used stand-alone ORM written for Python is
<a class="reference external" href="http://www.sqlalchemy.org/">SQLAlchemy</a>.</p>
</div>
<div class="section" id="database-third-party-services">
<h2>Database third-party services</h2>
<p>Numerous companies run scalable database servers as a hosted service.
Depending on the provider, there can be several advantages to using a
hosted database third-party service:</p>
<ol class="arabic simple">
<li>automated backups and recovery</li>
<li>tightened security configurations</li>
<li>easy vertical scaling</li>
</ol>
<p><a class="reference external" 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 class="reference external" 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>
<div class="section" id="database-resources">
<h3>Database resources</h3>
<p><a class="reference external" href="http://db-engines.com/en/ranking">DB-Engines</a> ranks the most popular
database management systems.</p>
<p><a class="reference external" href="http://dbweekly.com/">DB Weekly</a> is a new (as of Feb 2014) weekly roundup
of general database articles and resources.</p>
</div>
</div>
<br/>
Next read the
<a href="/wsgi-servers.html">wsgi servers</a> section.
</div>
<div class="col-md-offset-1 col-md-3" id="sidebar">
<div class="list-group">
<a href="/introduction.html" class="list-group-item ">Introduction</a>
<a href="/servers.html" class="list-group-item ">Servers</a>
<a href="/operating-systems.html" class="list-group-item ">Operating Systems</a>
<a href="/web-servers.html" class="list-group-item ">Web Servers</a>
<a href="/platform-as-a-service.html" class="list-group-item ">Platform-as-a-service</a>
<a href="/databases.html" class="list-group-item active">Databases</a>
<a href="/wsgi-servers.html" class="list-group-item ">WSGI Servers</a>
<a href="/web-frameworks.html" class="list-group-item ">Web Frameworks</a>
<a href="/task-queues.html" class="list-group-item ">Task Queues</a>
<a href="/static-content.html" class="list-group-item ">Static Content</a>
<a href="/caching.html" class="list-group-item ">Caching</a>
<a href="/web-browsers.html" class="list-group-item ">Web Browsers</a>
<a href="/web-application-security.html" class="list-group-item ">Web Application Security</a>
<a href="/monitoring.html" class="list-group-item ">Monitoring</a>
<a href="/web-analytics.html" class="list-group-item ">Web Analytics</a>
<a href="/api-integration.html" class="list-group-item ">API Integration</a>
<a href="/source-control.html" class="list-group-item ">Source Control</a>
<a href="/configuration-management.html" class="list-group-item ">Configuration Management</a>
<a href="/dependency-management.html" class="list-group-item ">Application Dependencies</a>
<a href="/no-sql-datastore.html" class="list-group-item ">NoSQL Data Stores</a>
<a href="/about-author.html" class="list-group-item ">About</a>
<a href="/change-log.html" class="list-group-item ">Change Log</a>
</div>
</div></div>
<hr/>
<div class="footer pull-right">
<a href="http://www.mattmakai.com/" class="underline">Matt Makai</a> 2014
</div>
</div>
<script src="http://code.jquery.com/jquery-2.1.0.min.js"></script>
<script src="theme/js/bootstrap.min.js"></script>
</body>
</html>