fullstackpython.com/databases.html at gh-pages · fnscoder/fullstackpython.com

History

425 lines (423 loc) · 28.1 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

365

366

367

368

369

370

371

372

373

374

375

376

377

378

379

380

381

382

383

384

385

386

387

388

389

390

391

392

393

394

395

396

397

398

399

400

401

402

403

404

405

406

407

408

409

410

411

412

413

414

415

416

417

418

419

420

421

422

423

424

425

<!DOCTYPE html>

<head>

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

<!--[if lt IE 9]>

<![endif]-->

</head>

<body>

<h2 style="color: #fff; margin: 20px 40px 0 0;"><a href="https://www.gumroad.com/l/python-deployments" style="color: #fff">The Full Stack Python Guide to Deployments book</a> has been released!</h2>

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

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

</div>

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

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

While you're learning about relational databases you should also read up on

<a href="/object-relational-mappers-orms.html">object-relational mappers (ORMs)</a>

and check out the

<a href="/web-frameworks.html">web frameworks</a> page.

</div>

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

<li>

<p><a href="http://rob.conery.io/2015/02/09/inserting-using-new-record-postgres/">Inserting And Using A New Record In Postgres</a>

shows some SQL equivalents to what many developers just do in their ORM

of choice.</p>

</li>

<li>

<p><a href="http://patshaughnessy.net/2014/10/13/following-a-select-statement-through-postgres-internals">Following a Select Statement Through Postgres Internals</a>

provides a fascinating look into the internal workings of PostgreSQL

during a query.</p>

</li>

<li>

<p>This article explains how and why PostgreSQL can handle <a href="http://blog.lostpropertyhq.com/postgres-full-text-search-is-good-enough/">full text searching</a>

for many use cases.</p>

</li>

<li>

<p>If you're just getting started with PostgreSQL here are

<a href="https://eye.raze.mx/10-beginner-postgresql-tasks-you-should-know/">10 beginner tasks you should know how to execute</a>.</p>

</li>

<li>

<p>The title's a bit presumptuous but here's a useful list of

<a href="http://engineering.tilt.com/7-postgresql-data-migration-hacks/">7 PostgreSQL data migration hacks you should be using, but aren't</a>.</p>

</li>

<li>

<p>This guide to

<a href="http://russ.garrett.co.uk/2015/10/02/postgres-monitoring-cheatsheet/">PostgreSQL monitoring</a>

is handy for knowing what to measure and how to do it.</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>

<li>

<p><a href="http://blog.ionelmc.ro/2014/12/28/terrible-choices-mysql/">Terrible Choices: MySQL</a>

is a blog post about specific deficiencies in MySQL's implementation that

hinder its usage with Django's ORM.</p>

</li>

<li>

<p><a href="http://moderndata.plot.ly/graph-data-from-mysql-database-in-python/">Graph Data From MySQL Database in Python</a>

is an interesting study with code of how to pull data out of MySQL and graph

the data with Plotly.</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.5">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. 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>

<ul>

<li>

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

</li>

<li>

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

</li>

<li>

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

databases with extensive backup services.</p>

</li>

</ul>

<h2>General database resources</h2>

<ul>

<li>

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

</li>

<li>

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

</li>

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

<li>

<p><a href="http://blog.isotoma.com/2014/05/a-different-view/">A different view</a>

provides some perspective on the impedance mismatch between ORMs and

traditional SQL queries.</p>

</li>

<li>

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

</li>

<li>

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

</li>

</ul>

<h2>Databases learning checklist</h2>

<ol>

<li>

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

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

</li>

<li>

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

application's dependencies.</p>

</li>

<li>

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

</li>

<li>

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

</li>

<li>

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

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

</li>

<li>

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

from your web application.</p>

</li>

</ol>

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

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

</div>

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

</div>

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

</div>

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

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

</div>

</form>

</div>

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

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

</div>

<a href="/introduction.html" class="list-group-item smaller-item " style='font-family: "Helvetica Neue",sans-serif;'>Introduction</a>

<a href="/learning-programming.html" class="list-group-item smaller-item " style='font-family: "Helvetica Neue",sans-serif;'>Learning Programming</a>

<a href="/why-use-python.html" class="list-group-item smaller-item " style='font-family: "Helvetica Neue",sans-serif;'>Why Use Python?</a>

<a href="/python-2-or-3.html" class="list-group-item smaller-item " style='font-family: "Helvetica Neue",sans-serif;'>Python 2 or 3?</a>

<a href="/enterprise-python.html" class="list-group-item smaller-item " style='font-family: "Helvetica Neue",sans-serif;'>Enterprise Python</a>

<a href="/best-python-resources.html" class="list-group-item smaller-item " style='font-family: "Helvetica Neue",sans-serif;'>Best Python Resources</a>

<a href="/best-python-videos.html" class="list-group-item smaller-item " style='font-family: "Helvetica Neue",sans-serif;'>Best Python Videos</a>

<a href="/development-environments.html" class="list-group-item smaller-item " style='font-family: "Helvetica Neue",sans-serif;'>Development Environments</a>

<a href="/emacs.html" class="list-group-item smaller-item " style='font-family: "Helvetica Neue",sans-serif;'>Emacs</a>

<a href="/generators.html" class="list-group-item smaller-item " style='font-family: "Helvetica Neue",sans-serif;'>Generators</a>

<a href="/comprehensions.html" class="list-group-item smaller-item " style='font-family: "Helvetica Neue",sans-serif;'>Comprehensions</a>

<a href="/web-frameworks.html" class="list-group-item smaller-item " style='font-family: "Helvetica Neue",sans-serif;'>Web Frameworks</a>

<a href="/django.html" class="list-group-item smaller-item " style='font-family: "Helvetica Neue",sans-serif;'>Django</a>

<a href="/flask.html" class="list-group-item smaller-item " style='font-family: "Helvetica Neue",sans-serif;'>Flask</a>

<a href="/bottle.html" class="list-group-item smaller-item " style='font-family: "Helvetica Neue",sans-serif;'>Bottle</a>

<a href="/pyramid.html" class="list-group-item smaller-item " style='font-family: "Helvetica Neue",sans-serif;'>Pyramid</a>

<a href="/morepath.html" class="list-group-item smaller-item " style='font-family: "Helvetica Neue",sans-serif;'>Morepath</a>

<a href="/other-web-frameworks.html" class="list-group-item smaller-item " style='font-family: "Helvetica Neue",sans-serif;'>Other Web Frameworks</a>

<a href="/web-design.html" class="list-group-item smaller-item " style='font-family: "Helvetica Neue",sans-serif;'>Web Design</a>

<a href="/cascading-style-sheets.html" class="list-group-item smaller-item " style='font-family: "Helvetica Neue",sans-serif;'>Cascading Style Sheets</a>

<a href="/javascript.html" class="list-group-item smaller-item " style='font-family: "Helvetica Neue",sans-serif;'>JavaScript</a>

<a href="/websockets.html" class="list-group-item smaller-item " style='font-family: "Helvetica Neue",sans-serif;'>WebSockets</a>

<a href="/web-application-security.html" class="list-group-item smaller-item " style='font-family: "Helvetica Neue",sans-serif;'>Web Application Security</a>

<a href="/static-site-generator.html" class="list-group-item smaller-item " style='font-family: "Helvetica Neue",sans-serif;'>Static Site Generator</a>

<a href="/databases.html" class="list-group-item smaller-item active" style='font-family: "Helvetica Neue",sans-serif;'>Databases</a>

<a href="/no-sql-datastore.html" class="list-group-item smaller-item " style='font-family: "Helvetica Neue",sans-serif;'>NoSQL Data Stores</a>

<a href="/object-relational-mappers-orms.html" class="list-group-item smaller-item " style='font-family: "Helvetica Neue",sans-serif;'>Object-relational Mappers (ORMs)</a>

<a href="/application-programming-interfaces.html" class="list-group-item smaller-item " style='font-family: "Helvetica Neue",sans-serif;'>Application Programming Interfaces</a>

<a href="/api-integration.html" class="list-group-item smaller-item " style='font-family: "Helvetica Neue",sans-serif;'>API Integration</a>

<a href="/api-creation.html" class="list-group-item smaller-item " style='font-family: "Helvetica Neue",sans-serif;'>API Creation</a>

<a href="/deployment.html" class="list-group-item smaller-item " style='font-family: "Helvetica Neue",sans-serif;'>Deployment</a>

<a href="/servers.html" class="list-group-item smaller-item " style='font-family: "Helvetica Neue",sans-serif;'>Servers</a>

<a href="/platform-as-a-service.html" class="list-group-item smaller-item " style='font-family: "Helvetica Neue",sans-serif;'>Platform-as-a-service</a>

<a href="/operating-systems.html" class="list-group-item smaller-item " style='font-family: "Helvetica Neue",sans-serif;'>Operating Systems</a>

<a href="/web-servers.html" class="list-group-item smaller-item " style='font-family: "Helvetica Neue",sans-serif;'>Web Servers</a>

<a href="/wsgi-servers.html" class="list-group-item smaller-item " style='font-family: "Helvetica Neue",sans-serif;'>WSGI Servers</a>

<a href="/source-control.html" class="list-group-item smaller-item " style='font-family: "Helvetica Neue",sans-serif;'>Source Control</a>

<a href="/application-dependencies.html" class="list-group-item smaller-item " style='font-family: "Helvetica Neue",sans-serif;'>Application Dependencies</a>

<a href="/static-content.html" class="list-group-item smaller-item " style='font-family: "Helvetica Neue",sans-serif;'>Static Content</a>

<a href="/task-queues.html" class="list-group-item smaller-item " style='font-family: "Helvetica Neue",sans-serif;'>Task Queues</a>

<a href="/configuration-management.html" class="list-group-item smaller-item " style='font-family: "Helvetica Neue",sans-serif;'>Configuration Management</a>

<a href="/continuous-integration.html" class="list-group-item smaller-item " style='font-family: "Helvetica Neue",sans-serif;'>Continuous Integration</a>

<a href="/logging.html" class="list-group-item smaller-item " style='font-family: "Helvetica Neue",sans-serif;'>Logging</a>

<a href="/monitoring.html" class="list-group-item smaller-item " style='font-family: "Helvetica Neue",sans-serif;'>Monitoring</a>

<a href="/web-analytics.html" class="list-group-item smaller-item " style='font-family: "Helvetica Neue",sans-serif;'>Web Analytics</a>

<a href="/docker.html" class="list-group-item smaller-item " style='font-family: "Helvetica Neue",sans-serif;'>Docker</a>

<a href="/caching.html" class="list-group-item smaller-item " style='font-family: "Helvetica Neue",sans-serif;'>Caching</a>

<a href="/microservices.html" class="list-group-item smaller-item " style='font-family: "Helvetica Neue",sans-serif;'>Microservices</a>

<a href="/testing.html" class="list-group-item smaller-item " style='font-family: "Helvetica Neue",sans-serif;'>Testing</a>

<a href="/unit-testing.html" class="list-group-item smaller-item " style='font-family: "Helvetica Neue",sans-serif;'>Unit Testing</a>

<a href="/integration-testing.html" class="list-group-item smaller-item " style='font-family: "Helvetica Neue",sans-serif;'>Integration Testing</a>

<a href="/code-metrics.html" class="list-group-item smaller-item " style='font-family: "Helvetica Neue",sans-serif;'>Code Metrics</a>

<a href="/debugging.html" class="list-group-item smaller-item " style='font-family: "Helvetica Neue",sans-serif;'>Debugging</a>

<a href="/what-full-stack-means.html" class="list-group-item smaller-item " style='font-family: "Helvetica Neue",sans-serif;'>What "Full Stack" Means</a>

<a href="/change-log.html" class="list-group-item smaller-item " style='font-family: "Helvetica Neue",sans-serif;'>Change Log</a>

<a href="/future-directions.html" class="list-group-item smaller-item " style='font-family: "Helvetica Neue",sans-serif;'>Future Directions</a>

<a href="/about-author.html" class="list-group-item smaller-item " style='font-family: "Helvetica Neue",sans-serif;'>About the Author</a>

</div>

</p>

</div>

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

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

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

<hr/>

</div>

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

2015

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

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

</script>

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