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. Show
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:
To get the most out of this tutorial, you should have a working knowledge of Python concepts like
Comparing MySQL to Other SQL DatabasesSQL 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:
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 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. 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/PythonNow, 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 ServerThe 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 SetupWhile you must specify credentials for the root account during setup, you can modify these settings later on. Although you only need the MySQL server for this tutorial, you can also set up other helpful tools like MySQL Workbench using these installers. If you don’t want to install MySQL directly in your operating system, then deploying MySQL on Linux with Docker is a convenient alternative. Installing MySQL Connector/PythonA database driver is a piece of software that allows an application to connect and interact with a database system. Programming languages like Python need a special driver before they can speak to a database from a specific vendor. These drivers are typically obtained as third-party modules. The Python Database API (DB-API) defines the standard interface with which all Python database drivers must comply. These details are documented in PEP 249. All Python database drivers, such as sqlite3 for SQLite, psycopg for PostgreSQL, and MySQL Connector/Python for MySQL, follow these implementation rules. Many popular programming languages have their own database API. For example, Java has the Java Database Connectivity (JDBC) API. If you need to connect a Java application to a MySQL database, then you need to use the MySQL JDBC connector, which follows the JDBC API. Similarly, in Python you need to install a Python MySQL connector to interact with a MySQL database. Many packages follow the DB-API standards, but the most popular among them is MySQL Connector/Python. You can get it with
To test if the installation was successful, type the following command on your Python terminal: >>>
If the above code executes with no errors, then Make sure that you’re installing the correct Establishing a Connection With MySQL ServerMySQL is a server-based database management system. One server might contain multiple databases. To interact with a database, you must first establish a connection with the server. The general workflow of a Python program that interacts with a MySQL-based database is as follows:
This is a generic workflow that might vary depending on the individual application. But whatever the application might be, the first step is to connect your database with your application. Establishing a ConnectionThe first step in interacting with a MySQL server is to establish a connection. To
do this, you need
The code above uses the entered login credentials to establish a connection with your MySQL server. In return, you get a There are several important things to notice in the code above:
You’ve now established a connection between your program and your MySQL server, but you still need to either create a new database or connect to an existing database inside the server. Creating a New DatabaseIn the last section, you established a connection with your MySQL server. To create a new database, you need to execute a SQL statement:
The above statement will create a new database with the name To execute a SQL query in Python, you’ll need to use a cursor, which abstracts away the access to database records. MySQL Connector/Python provides you with the
The above code gives you an instance of the A query that needs to be executed is sent to
After executing of the code above, you’ll have a new database called The You might receive an error here if a database with the same name already exists in your server. To confirm this, you can display the name of all databases in your server. Using the same >>>
The above code prints the names of all the databases currently in your MySQL server. The You created a new database in this section by executing the
Connecting to an Existing DatabaseIn the last section, you created a new database called You can do this using the same
The above code is very similar to the connection script that you used earlier. The only change here is an additional Creating, Altering, and Dropping a TableIn this section, you’ll learn how to perform some basic DDL queries like Defining the Database SchemaYou can start by creating a database schema for an online movie rating system. The database will consist of three tables:
A real-world movie rating system, like IMDb, would need to store a bunch of other attributes, like emails, movie cast lists, and so on. If you want, you can add more tables and attributes to this database. But these three tables will suffice for the purpose of this tutorial. The image below depicts the database schema: Schema Diagram for an Online Movie Rating SystemThe tables in this database are related to each other. Creating Tables Using the CREATE TABLE StatementNow, to create a new table in MySQL, you need to use the
If you’ve looked at SQL statements before, then most of the above query might make sense. But there are some differences in the MySQL syntax that you should be aware of. For example, MySQL has a wide variety of data types for your perusal, including To create a new table, you need to pass this query to
Now you have the Also, notice the
As you did with the
If required, you could add more information about a reviewer, such as their email ID or demographic information. But Finally, you can create the
The implementation of foreign key relationships in MySQL is slightly different and limited as compared to the standard SQL. In MySQL, both the parent and the child in the foreign key constraint must use the same storage engine. A storage engine is the underlying software component that a database management system uses for performing SQL operations. In MySQL, storage engines come in two different flavors:
InnoDB is the default and most popular storage engine. It helps maintain data integrity by supporting foreign key constraints. This means that any CRUD operation on a foreign key is checked to ensure that it doesn’t lead to inconsistencies across different tables. Also, note that the You may choose to reuse the same cursor for multiple executions. In that case, all executions would become one atomic transaction rather than multiple separate transactions. For example, you can
execute all
The above code will first execute all three Showing a Table Schema Using the DESCRIBE StatementNow, that you’ve created all three tables, you can look at their schema using the following SQL statement: To get some results back from the >>>
Once you execute the above code, you should receive a table containing information
about all the columns in Modifying a Table Schema Using the ALTER StatementIn the
>>>
As shown in the output, the Deleting Tables Using the DROP StatementTo delete a table, you need to execute the To
delete the
If you execute the above code, you will have successfully deleted the Inserting Records in TablesIn the last section, you created three tables in your database: The first method, Using .execute()The
first approach uses the same For reference, the
You don’t need to add data for
The Using .executemany()The previous approach is more suitable when the number of records is fairly small and you can write these records directly into the code. But this is rarely true. You’ll often have this data stored in some other file, or the data will be generated by a different script and will need to be added to the MySQL database. This is where
The following example inserts records for the
In the script above, you pass both the query and the list of records as arguments to The
code uses You can similarly use
All three tables are now populated with data. You now have a fully functional online movie rating database. The next step is to understand how to interact with this database. Reading Records From the DatabaseUntil now, you’ve been building your database. Now it’s time to perform some queries on it and find some interesting properties from this
dataset. In this section, you’ll learn how to read records from database tables using the Reading Records Using the SELECT StatementTo retrieve records, you need to send a Try writing a MySQL query to select all records from the >>>
The In the query above, you use the In MySQL, the
The first argument specifies an offset of You can also query for selected columns: >>>
Now, the code outputs values only from the two specified columns: Filtering Results Using the WHERE ClauseYou can filter table records by specific
criteria using the
You can also use >>>
MySQL offers a
plethora of string formatting operations like >>>
If you
don’t want to use the
Try retrieving the titles of the five highest-grossing movies concatenated with their
release years again, but this time use >>>
The output with It’s necessary to clean all unread results before executing any other statements on the same connection. Otherwise,
an Handling Multiple Tables Using the JOIN StatementIf you found the queries in the last section to be quite straightforward, don’t worry. You can make your Let’s look at some slightly more complex >>>
As shown above, Night of the
Living Dead and The Godfather are tied as the highest-rated movies in your To find the name of the reviewer who gave the most ratings, write the following query: >>>
Updating and Deleting Records From the DatabaseIn this section, you’ll be updating and deleting records
from the database. Both of these operations can be performed on either a single record or multiple records in the table. You’ll select the rows that need to be modified using the UPDATE CommandOne of the reviewers in your database,
The code passes the update query to Suppose you need to provide an option that allows reviewers to modify ratings. A reviewer will provide three values, Assuming that
The above queries first update the rating and then display it. You can create a complete Python script that establises a connection with the database and allows the reviewer to modify a rating:
Save this code to a file named If If no result set is fetched on an operation, then While this code should solve your purpose, the For example, if a user sends
The
Again, the output shows that the specified The hacker sneaked in an update query while entering the >>>
The above code displays the There’s a quick fix to prevent such attacks. Don’t add the query values provided by the user directly to your query string. Instead, update the
Notice that the
DELETE CommandDeleting records
works very similarly to updating records. You use the It’s recommended that you first run a >>>
The above code snippet outputs the
With this query, you remove all ratings given by the reviewer with Other Ways to Connect Python and MySQLIn this tutorial, you saw MySQL Connector/Python, which is the officially recommended means of interacting with a MySQL database from a Python application. There are two other popular connectors:
These connectors act as interfaces between your program and a MySQL database, and you send your SQL queries through them. But many developers prefer using an object-oriented paradigm rather than SQL queries to manipulate data. Object-relational mapping (ORM) is a technique that allows you to query and manipulate data from a database directly using an object-oriented language. An ORM library encapsulates the code needed to manipulate data, which eliminates the need to use even a tiny bit of SQL. Here are the most popular Python ORMs for SQL-based databases:
You might find one of these approaches to be more suitable for your application. If you’re not sure which one to use, then it’s best to go with the officially recommended MySQL Connector/Python that you saw in action in this tutorial. ConclusionIn this tutorial, you saw how to use MySQL Connector/Python to integrate a MySQL database with your Python application. You also saw some unique features of a MySQL database that differentiate it from other SQL databases. Along the way, you learned some programming best practices that are worth considering when it comes to establishing a connection, creating tables, and inserting and updating records in a database application. You also developed a sample MySQL database for an online movie rating system and interacted with it directly from your Python application. In this tutorial, you learned how to:
If you’re interested, Python also has connectors for other DBMSs like MongoDB and PostgreSQL. For more information, check out Python Database Tutorials. How do you connect to a database in Python?To create a connection between the MySQL database and Python, the connect() method of mysql. connector module is used. We pass the database details like HostName, username, and the password in the method call, and then the method returns the connection object.
How do I connect to a MySQL database?To Connect to a MySQL Database. Click Services tab.. Expand the Drivers node from the Database Explorer. ... . Enter User Name and Password. ... . Click OK to accept the credentials. ... . Click OK to accept the default schema.. Right-click the MySQL Database URL in the Services window (Ctrl-5).. How do I view MySQL database in python?Steps to show all tables present in a database and server using MySQL in python. import MySQL connector.. establish connection with the connector using connect(). create the cursor object using cursor() method.. create a query using the appropriate mysql statements.. execute the SQL query using execute() method.. How do I connect Python to MySQL on Mac?Show activity on this post. Install homebrew. Open a terminal and run: brew install mysql-connector-c.. XCode and the command line tools (as suggested by @7stud, @kjti). Install homebrew.. brew install mysql-connector-c.. pip install mysql-python.. |