fullstackpython.github.com/databases.html at gh-pages · xidianwlc/fullstackpython.github.com

History

364 lines (359 loc) · 18.3 KB

Raw

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

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

324

325

326

327

328

329

330

331

332

333

334

335

336

337

338

339

340

341

342

343

344

345

346

347

348

349

350

351

352

353

354

355

356

357

358

359

360

361

362

363

364

<!DOCTYPE html>

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

<!--[if lt IE 9]>

<![endif]-->

<style>

html,

body {

font-size: 18px;

color: #222;

background: #fefefe;

}

body {

padding-top: 10px;

}

.footer {

padding: 20px 0 30px 0;

}

a, a:hover {border-bottom: 1px dotted; color: #444;}

a:hover {text-decoration: none; color: #000;}

.logo-title a, .logo-title a:hover {font-size: 72px; font-weight: normal;

letter-spacing: .04em;

vertical-align: middle;

margin-left: 5px;

color: #000; text-decoration: none;

border-bottom: none;

font-family: "Helvetica Neue",

sans-serif;

line-height: .9em;}

.logo-title a:hover {color: gray;}

.logo-image {vertical-align: middle; border: none;}

.logo-header-section {margin: 15px 0 15px 0;}

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

.select-next {

min-height: 300px;

}

.choose-btn {

font-size: 1.1em;

margin: 10px 0 0 0;

}

.choose-next {

border: 1px solid black;

background-color: #444;

margin-left: 25px;

color: #ddd;

}

.choose-next a {

color: #eee;

}

.btn-full {width: 100%; box-shadow: 1px 2px 1px #222;}

p.under-btn {text-align: left; margin-top: 20px;}

h3.panel-head {margin: 5px 0 0 0; font-size: 26px; color: #fff;}

.smaller-item {font-size: .8em; padding: 5px 0 5px 10px;}

@media (max-width: 1200px) {

h3.panel-head {font-size: 22px;}

}

@media (max-width: 992px) {

.choose-next {

margin-left: 0px;

}

.select-next {

min-height: 100px;

}

.smaller-item {font-size: 1em; padding: 15px 0 15px 10px;}

}

@media (max-width: 600px) {

.logo-header-section {

margin: 20px 32px 0 0;

}

</style>

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>

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

</div>

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

<h3>PostgreSQL resources</h3>

<ul>

<li>

<p>This post on

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

<a href="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 <a href="http://www.craigkerstiens.com/2012/10/01/understanding-postgres-performance/">understanding PostgreSQL performance</a>.</p>

</li>

<li>

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

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

trafficked sites such as

<a href="https://blog.twitter.com/2012/mysql-twitter">Twitter</a>,

<a href="https://www.facebook.com/notes/facebook-engineering/mysql-and-database-engineering-mark-callaghan/10150599729938920">Facebook</a>

and <a href="http://www.mysql.com/customers/">many others major organizations</a>.

However, since the company focused on MySQL development,

<a 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 href="http://www.zdnet.com/wikipedia-moving-from-mysql-to-mariadb-7000008912/">Wikipedia</a>

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

<h3>MySQL resources</h3>

<ul>

<li>

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

</li>

<li>

<p>This tutorial shows how to install <a href="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><a href="http://initd.org/psycopg/">psycopg2</a> for PostgreSQL</p>

</li>

<li>

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

</li>

<li>

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

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 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 href="http://www.sqlalchemy.org/">SQLAlchemy</a>.</p>

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

<li>automated backups and recovery</li>

<li>tightened security configurations</li>

<li>easy vertical scaling</li>

</ol>

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

<h2>Database resources</h2>

<ul>

<li>

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

database management systems.</p>

</li>

<li>

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

articles and resources.</p>

</li>

</ul>

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

</a>

What about non-relational data stores hipsters tell me to use?

</p>

</div>

How do I set up a WSGI server to run the Python code?

</p>

</div>

My app is running, but it looks awful. I need to style the

interface.

</p>

</div>

How do I create a better browser experience with JavaScript?

</p>

</div>

</div> </div>

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

<a href="/">Full Stack Python</a> is an open source book explaining

each layer of the Python web application stack along with the

best Python resources from around the web.

<hr/>

<a href="http://twitter.com/mattmakai">Matt Makai</a> designed and

wrote this site with assistance from community pull

requests. If you're on GitHub you can

<a href="https://github.com/makaimc">follow him</a>

to see what daily changes are made or

<a href="https://github.com/makaimc/fullstackpython.github.com">star the repository</a> for future reference.

</div>

<h3 class="panel-head">Table of Contents</h3>

</div>

<a href="/introduction.html" class="list-group-item smaller-item ">Introduction</a>

<a href="/bottle.html" class="list-group-item smaller-item ">Bottle</a>

<a href="/deployment.html" class="list-group-item smaller-item ">Deployment</a>

<a href="/other-web-frameworks.html" class="list-group-item smaller-item ">Other Web Frameworks</a>

<a href="/servers.html" class="list-group-item smaller-item ">Servers</a>

<a href="/web-frameworks.html" class="list-group-item smaller-item ">Web Frameworks</a>

<a href="/django.html" class="list-group-item smaller-item ">Django</a>

<a href="/operating-systems.html" class="list-group-item smaller-item ">Operating Systems</a>

<a href="/flask.html" class="list-group-item smaller-item ">Flask</a>

<a href="/web-servers.html" class="list-group-item smaller-item ">Web Servers</a>

<a href="/platform-as-a-service.html" class="list-group-item smaller-item ">Platform-as-a-service</a>

<a href="/databases.html" class="list-group-item smaller-item active">Databases</a>

<a href="/wsgi-servers.html" class="list-group-item smaller-item ">WSGI Servers</a>

<a href="/application-dependencies.html" class="list-group-item smaller-item ">Application Dependencies</a>

<a href="/cascading-style-sheets.html" class="list-group-item smaller-item ">Cascading Style Sheets</a>

<a href="/javascript.html" class="list-group-item smaller-item ">JavaScript</a>

<a href="/static-content.html" class="list-group-item smaller-item ">Static Content</a>

<a href="/source-control.html" class="list-group-item smaller-item ">Source Control</a>

<a href="/caching.html" class="list-group-item smaller-item ">Caching</a>

<a href="/task-queues.html" class="list-group-item smaller-item ">Task Queues</a>

<a href="/api-integration.html" class="list-group-item smaller-item ">API Integration</a>

<a href="/no-sql-datastore.html" class="list-group-item smaller-item ">NoSQL Data Stores</a>

<a href="/web-application-security.html" class="list-group-item smaller-item ">Web Security</a>

<a href="/logging.html" class="list-group-item smaller-item ">Logging</a>

<a href="/monitoring.html" class="list-group-item smaller-item ">Monitoring</a>

<a href="/configuration-management.html" class="list-group-item smaller-item ">Configuration Management</a>

<a href="/web-analytics.html" class="list-group-item smaller-item ">Web Analytics</a>

<a href="/best-python-resources.html" class="list-group-item smaller-item ">Best Python Resources</a>

<a href="/about-author.html" class="list-group-item smaller-item ">About the Author</a>

<a href="/change-log.html" class="list-group-item smaller-item ">Change Log</a>

<a href="/future-directions.html" class="list-group-item smaller-item ">Future Directions</a>

</div>

</div></div>

<hr/>

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

2014

</div>

</body>

</html>

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

FilesExpand file tree

databases.html

Latest commit

History

databases.html

File metadata and controls