Learn about the MySQL database engine in this video. Discover why you might use this database over others for your Python application.
- [Instructor] Another relational database engine is MySQL and it's one of the most popular relational database management systems used with Python. Similar to SQLite, it uses a relational model with tables to describe relationships between its data. But instead of being contained directly on the file system, your database lives on a server. MySQL follows a client-server relationship. This means your database lives on a server. And any time you want to make changes to your database, you send a request to the server. The request contains your SQL statement or instructions for the modification or selection. Once received the server accepts that request, performs the action and sends a response back to you the client with the results. Most of this is abstracting to us as users of the database. All we have to do is install the correct driver and configure the connection correctly which we'll do throughout this chapter. Although we only have one client pictured here, your database can have multiple clients or multiple machines that can each access and modify your database on the server. It's important to remember that MySQL is not SQL. MySQL is a relational database management system which helps you create and manage your database. Whereas SQL is a query language used to interact directly with the database. Many RDBMS is use SQL, but one RDBMS would not use another RDBMS. MySQL was designed with speed and ease of use in mind, which is why it's one of the most popular relational databases, but with speed as a priority, it comes with certain functional limitations. It is not fully SQL compliant as it does not support certain SQL operations such as full join clauses. MySQL is also a dual-licensed software with a free and open source community edition as well as several paid additions. Many features in plugins are only available in the proprietary additions. However, MySQL has good replication support which makes it a great choice for distributed database setups. It's also known for powering many websites and applications across the internet because it is easy to install, run, and scale. Similar to other databases, MySQL has compatible graphical user interfaces or GUIs we can use to interact with the database. However, we can also interact with MySQL databases in custom Python bill applications as well. Similar to how Python offers a module to interact with SQLite, Python also offers a module called mysql-connector for interacting with MySQL. We can also use an ORM or object relational mapping tool, such as SQLAlchemy within a Python application to interact with the MySQL database. We'll be looking at how to use both in this chapter, but again, in the real world you would choose one method to access your database within a single Python application, either an ORM or the specific Python module, and you'd stick with it. Most of the time you'll create your database using a MySQL GUI or with MySQL in the command-line, and then interact with your database using your Python application. Your Python applications and users of your Python app will be querying, inserting, updating, or deleting data from your database but not necessarily creating new databases and new tables. However, similar to SQLite, it is possible to use Python to create databases and tables in MySQL as well.
- What is a database?
- Relational vs. nonrelational databases
- Creating a SQLite database
- Editing records in SQLite
- Creating a MySQL database
- Encapsulating database operations
- Creating a PostgreSQL database
- Interacting with databases using SQLAlchemy
- Creating a stored procedure
- Developing full-stack apps with Python and Flask
- Developing analysis apps with pandas and SQLAlchemy