Real Python: Python and MySQL Database: A Practical Introduction

MySQL is one of the most popular database management systems (DBMSs) on the market today. It ranked second only to the Oracle DBMS in this year’s DB-Engines Ranking. As most software applications need to interact with data in some form, programming languages like Python provide tools for storing and accessing these data sources.

Using the techniques discussed in this tutorial, you’ll be able to efficiently integrate a MySQL database with a Python application. You’ll develop a small MySQL database for a movie rating system and learn how to query it directly from your Python code.

By the end of this tutorial, you’ll be able to:

• Identify unique features of MySQL
• Connect your application to a MySQL database
• Query the database to fetch required data
• Handle exceptions that occur while accessing the database
• Use best practices while building database applications

To get the most out of this tutorial, you should have a working knowledge of Python concepts like for loops, functions, exception handling, and installing Python packages using pip. You should also have a basic understanding of relational database management systems and SQL queries like SELECT, DROP, CREATE, and JOIN.

Free Download: Get a sample chapter from Python Tricks: The Book that shows you Python’s best practices with simple examples you can apply instantly to write more beautiful + Pythonic code.

Comparing MySQL to Other SQL Databases

SQL stands for Structured Query Language and is a widely used programming language for managing relational databases. You may have heard of the different flavors of SQL-based DBMSs. The most popular ones include MySQL, PostgreSQL, SQLite, and SQL Server. All of these databases are compliant with the SQL standards but with varying degrees of compliance.

Being open source since its inception in 1995, MySQL quickly became a market leader among SQL solutions. MySQL is also a part of the Oracle ecosystem. While its core functionality is completely free, there are some paid add-ons as well. Currently, MySQL is used by all major tech firms, including Google, LinkedIn, Uber, Netflix, Twitter, and others.

Apart from a large open source community for support, there are many other reasons for MySQL’s success:

1. Ease of installation: MySQL was designed to be user-friendly. It’s quite straightforward to set up a MySQL database, and several widely available third-party tools, like phpMyAdmin, further streamline the setup process. MySQL is available for all major operating systems, including Windows, macOS, Linux, and Solaris.

2. Speed: MySQL holds a reputation for being an exceedingly fast database solution. It has a relatively smaller footprint and is extremely scalable in the long run.

3. User privileges and security: MySQL comes with a script that allows you to set the password security level, assign admin passwords, and add and remove user account privileges. This script uncomplicates the admin process for a web hosting user management portal. Other DBMSs, like PostgreSQL, use config files that are more complicated to use.

While MySQL is famous for its speed and ease of use, you can get more advanced features with PostgreSQL. Also, MySQL isn’t fully SQL compliant and has certain functional limitations, like no support for FULL JOIN clauses.

You might also face some issues with concurrent reading and writing in MySQL. If your software has many users writing data to it at once, then PostgreSQL might be a more suitable choice.

Note: For a more in-depth comparison of MySQL and PostgreSQL in a real-world context, check out Why Uber Engineering Switched from Postgres to MySQL.

SQL Server is also a very popular DBMS and is known for its reliability, efficiency, and security. It’s preferred by companies, especially in the banking domain, who regularly deal with large traffic workloads. It’s a commercial solution and is one of the systems that are most compatible with Windows services.

In 2010, when Oracle acquired Sun Microsystems and MySQL, many were worried about MySQL’s future. At the time, Oracle was MySQL’s biggest competitor. Developers feared that this was a hostile takeover from Oracle with the aim of destroying MySQL.

Several developers led by Michael Widenius, the original author of MySQL, created a fork of the MySQL code base and laid the foundation of MariaDB. The aim was to secure access to MySQL and keep it free forever.

To date, MariaDB remains fully GPL licensed, keeping it completely in the public domain. Some features of MySQL, on the other hand, are available only with paid licenses. Also, MariaDB provides several extremely useful features that aren’t supported by MySQL server, like distributed SQL and columnar storage. You can find more differences between MySQL and MariaDB listed on MariaDB’s website.

MySQL uses a very similar syntax to the Standard SQL. There are, however, some notable differences mentioned in the official documentation.

Installing MySQL Server and MySQL Connector/Python

Now, to start working through this tutorial, you need to set up two things: a MySQL server and a MySQL connector. MySQL server will provide all the services required for handling your database. Once the server is up and running, you can connect your Python application with it using MySQL Connector/Python.

Installing MySQL Server

The official documentation details the recommended way to download and install MySQL server. You’ll find instructions for all popular operating systems, including Windows, macOS, Solaris, Linux, and many more.

For Windows, the best way is to download MySQL Installer and let it take care of the entire process. The installation manager also helps you configure the security settings of the MySQL server. On the Accounts and Roles page, you need to enter a password for the root (admin) account and also optionally add other users with varying privileges:

MySQL Installer Account Setup

Read the full article at https://realpython.com/python-mysql/ »

---

[ Improve Your Python With 🐍 Python Tricks 💌 – Get a short & sweet Python Trick delivered to your inbox every couple of days. >> Click here to learn more and see examples ]

from Planet Python
via read more