MySQL is an open source relational database implementation for storing and retrieving data.
MySQL or PostgreSQL?
MySQL is a viable open source database implementation for Python web applications. MySQL has a slightly easier initial learning curve than PostgreSQL. However, PostgreSQL’s design is often preferred by Python web developers, especially when data migrations are run as an application evolves.
Python Drivers for MySQL
Accessing MySQL from a Python application requires a database driver (also called a “connector”). While it is possible to write a driver as part of your application, in practice most developers use an existing open source driver.
There was a major issue with MySQL drivers since the introduction of Python 3. One of the most popular libraries called MySQLdb did not work in its existing form with Python 3 and there were no plans to update it. Therefore a fork of MySQLdb named mysqlclient added Python 3 compatibility.
The mysqlclient fork was good in that existing MySQLdb users could drop mysqlclient into existing projects that were upgrading to Python 3. However, the fork often causes confusion when searching for which Python driver to use with MySQL. Many developer simply decide to use PostgreSQL because there is better support for Python drivers in the PostgreSQL community.
With that driver support context in mind, it’s absolutely possible to build a Python 3 web application with MySQL as a backend. Here is a list of drivers along with whether it supports Python 2, 3 or both.
- mysqlclient is a fork of MySQLdb that supports Python 2 and 3.
- MySQL Connector is Oracle’s “official” (Oracle currently owns MySQL) Python connector. The driver supports Python 2 and 3, just make sure to check the version guide for what releases work with which Python versions.
- MySQLdb supports Python 2 and was frequently used by Python web applications before the mass migration to Python 3 began.
- PyMySQL is a pure Python (no C low-level code) implementation that attempts to be a drop-in replacement for MySQLdb. However, some MySQL APIs are not supported by the driver so whether or not your application can use this connector will depend on what you’re building.
What organizations use MySQL?
The database is deployed in production at some of the highest trafficked sites such as Twitter, Facebook and many others major organizations. However, since MySQL AB, the company that developed MySQL, was purchased by Sun Microsystems (which was in turn purchased by Oracle), there have been major defections away from the database by Wikipedia and Google. MySQL remains a viable database option but I always recommend new Python developers learn PostgreSQL if they do not already know MySQL.
Python-specific MySQL resources
- Python 3.4.0 with MySQL database and Python 3 and MySQL provide context for the commonly asked question about which database MySQL driver to use with Python 3.
- Terrible Choices: MySQL is a blog post about specific deficiencies in MySQL’s implementation that hinder its usage with Django’s ORM.
- Graph Data From MySQL Database in Python is an interesting study with code of how to pull data out of MySQL and graph the data with Plotly.
- MySQL Python tutorial uses the MySQLdb driver to connect to a MySQL server instance and shows some examples for inserting and querying data.
General MySQL resources
- How to Install and Use MySQL on Ubuntu 16.04 is a quick tutorial for getting up and running on Ubuntu Linux.
- 28 Beginner’s Tutorials for Learning about MySQL Databases is a curated collection on various introductory MySQL topics.
- This tutorial shows how to install MySQL on Ubuntu.
- A Basic MySQL Tutorial doesn’t have the most original title but it’s a good walkthrough of your first few steps in MySQL for creating users and working with tables.
- Pinterest open sourced many of their MySQL tools to manage instances of the database.
- Bye Bye MySQL & MongoDB, Guten Tag PostgreSQL goes into details for why the company Userlike migrated from their MySQL database setup to PostgreSQL.
- Growing up with MySQL is a story about how one company went through dramatic growth and had to keep up with it by quickly scaling their MySQL database.
- Tracker: Ingesting MySQL data at scale – (Part 1) is the first blog post in a series explaining Pinterest’s tool to load large volumes of data into MySQL from other internal sources such as Kafka and Redis.
- Monitoring MySQL metrics is the first of a three part series, with the other parts on collecting metrics and monitoring & collecting specifically with the DataDog tool. The series explains what metrics you should be collecting and monitoring in your production database along with the purpose for why those metrics are important.