How to Integrate a MySQL Database With a Python Application?

Database programming can be an important step for many developers, and most applications need to interact with data in some form or another. Python is a programming language used for data analysis, and MySQL is a commonly used database service.

Python is conquering the world as a programming language. For a developer, learning how to use this popular programming language could be vital. Follow these steps to get started with MySQL and Python.

The standard way for Python to access databases is through Python DB API. Within this API, there are modules available for some of the most common database management software, such as MySQL and Oracle. If your application is a website, be sure to use a vulnerability scanner to check if there are any security risks with this new integration.

Note for Microsoft Visual Studio 2022 users: The 2022 version does not currently support MySQL Server. Microsoft Visual Studio 2019 was used for this tutorial on a Windows 10 machine.

Step 1. Install MySQL Using the MySQL Installer

The MySQL installer can easily install MySQL products. This tutorial will focus on MySQL Server 8.0.28, MySQL Workbench 8.0 CE, MySQL for Visual Studio 1.2.10, MySQl Shell 8.0.28, and Python/Connector 8.0.28.

MySQL Router will not be referenced.

When MySQL Server is installed, select “Development Computer” unless the machine you are using is going to function primarily as a server. All other fields should be left at their default values.

Select strong password encryption to keep your data safe. MySQL can be hacked by cryptocurrency miners and other malicious actors. And with the price of Bitcoin, for example, continuing to rise, it’s essential to take every precaution. Use the legacy authentication method only if MySQL 8.0 is not supported.

Choose a root password.

You may add a user in the “Accounts and Roles” section of the installer by selecting “add user” and then creating a username and password. For this tutorial, “admin” is used as the username. Use a secure password — “adminpassword” was selected for this tutorial.

Additional users aren’t required to continue installation.

Default options were chosen for Windows Service. You may deselect the option “Start the MySQL Server at System Startup” if necessary.

Apply the configuration changes to the MySQL Server installer.

The applications pictured below should all have been installed.

MySQL Shell is a code editor for MySQL. There, you can run queries, perform updates, and conduct administrative operations.

Try MYSQL Shell by connecting to the database, using the below command to connect to root, and entering the password when prompted.

\connect root@localhost:3306

Step 2. Install Python

Check if Python is installed on your machine by opening the command prompt and entering the command below.

py --version

If Python is not found, download it from the Python website. Check the version once installed.

You may need to add a PATH system variable in Windows to use the command “py.” If an older version of Python is installed, the command used is “python.”

Anaconda will not be used in this tutorial, but it is an excellent free and open-source tool that provides powerful data science packages for Python and R. Installing Anaconda is recommended if you will be working with data science and machine learning.

If you’re new to using Python, try some exercises to start developing your knowledge.

Step 3. Launch Your MySQL Workbench

Set up a new connection in MySQL Workbench. Select the username chosen during the server installation.

Alternatively, you may create a connection with the root account and password.

Once the connection is established, select the connection named “tutorial.”

After making your selection, create a new schema by selecting the fourth icon on the top row.

Name the schema “tutorial” and leave all other options as their default values.

Select “Apply” to review the script that is being executed.

Now that the schema has been added, create a new table in the “tutorial” schema by selecting the fifth icon on the top row. Notice that, on the left pane, “schemas” is now active instead of the “Administration” tab.

The below column names, data types, and categories were selected. The column “id_item” is an INT. Select PK (primary key), NN (not null), and AI (auto increment).

The variable “item_name” is of datatype VARCHAR(45), the max length is specified as 45 characters, and the NN box is ticked.

Choose “item_inventory” as the next column name with a datatype of DECIMAL(10,1), meaning that 10 digits are stored but only one is displayed. No other options should be ticked.

Click apply and review the SQL script that is run with these changes.

In Workbench 8.0, you can access the table data by right-clicking the table name in the “Schema” pane and clicking “Select Rows - Limit 1000.”

Add some test data to the table to access later in Python.

The command prompt window can also be used to create databases and tables. However, the GUI is often easier to work with for beginners.

Use the below command to access MySQL from the command prompt window.

mysql -u admin -p

Enter the password when prompted.

You may need to add a PATH system variable in Windows 10 to use the command “mysql.” To set this up, go to “View advanced system settings” on the Control Panel. Select the “Advanced” tab and select “Environment variables.”

Select the row with the variable “PATH” and then select “Edit.”

Add a “New” path to “C:\Program Files\MySQL Server 8.0\bin.”

Use the below command to show all available databases.

show databases;

The newly created schema should now be available within the database.

Select the new database by using the below command.

use {SCHEMA name};

use tutorial;

Then, use the below command to see all tables in the selected database.

show tables;

Step 4. Install the MySQL Connector Package

In the Command Prompt window, use the below command to install the mysql-connector-python package.

pip install mysql-connector-python

If the command “pip” is not available, ensure that the path “C:\Users\{Your Username}\AppData\Local\Programs\Python\Python310\Scripts” is set as an environment variable.

If this is the incorrect path for your installation, search for the pip application in the AppData folder and copy the full path to that folder.

Step 5. Use a Code Editor to Integrate a MySQL Database on a Python Application

You’re ready to connect to the MySQL database in Python. Open Microsoft Visual Studio 2019 or your code editor of choice. Use the below Python code to print all the data in the created tutorial table:

import mysql.connector

from mysql.connector import Error

 

# try to connect to the created database

try:

    connection = mysql.connector.connect(host='localhost',

                                         database='tutorial',

                                         user='admin',

                                         password='adminpassword')

    if connection.is_connected():

        database_info = connection.get_server_info()

        print("Connected to MySQL Server version ", database_info)

        cursor = connection.cursor()

        cursor.execute("select database();")

        record = cursor.fetchone()

        print("Connected to database: ", record)

 

except Error as e:

    print("Error while connecting to MySQL", e)

 

# declare the cursor object

cursor = connection.cursor()

 

# define the query by selecting the table name

query = "SELECT * FROM tutorial_table"

 

# get records from the table by executing the query

cursor.execute(query)

result = cursor.fetchall()

 

# print column headers

num_fields = len(cursor.description)

field_names = [i[0] for i in cursor.description]

print(field_names)

 

# print values in each row

for row in result:

    print(row)

If the below text is displayed in the console, the Python application has successfully connected to MySQL:

Connected to MySQL Server version  8.0.28

Connected to database:  ('tutorial',)

['id_item', 'item_name', 'item_inventory']

(1, 'apples', Decimal('5.5'))

(2, 'bananas', Decimal('10.0'))

(3, 'spinach', Decimal('12.5'))

(4, 'carrots', Decimal('8.3'))

Press any key to continue . . .

Step 6. Alter Table Data in Python

Table data can be accessed using Python. The methods are similar to the MySQL scripting language. For example, you may use the below code snippet to add a column in Python:

     # alter the student table by adding one more column

    alter_statement = "ALTER TABLE tutorial_table ADD item_UOM VARCHAR(10);"

 

     # execute the SQL ALTER statement

    cursor.execute(alter_statement)

 

    # verify the new schema by issuing a DESCRIBE statement

    desc_statement  = "DESC tutorial_table"

 

    # Execute the SQL SELECT query

    cursor.execute(desc_statement)

 

    # Fetch the updated row

    columns = cursor.fetchall()

Here is the console output when the column is added:

Connected to MySQL Server version  8.0.28

Connected to database:  ('tutorial',)

['id_item', 'item_name', 'item_inventory', 'item_UOM']

(1, 'apples', Decimal('5.5'), None)

(2, 'bananas', Decimal('10.0'), None)

(3, 'spinach', Decimal('12.5'), None)

(4, 'carrots', Decimal('8.3'), None)

Similarly, values in the table can be edited using the UPDATE command.

Conclusion

Integrating a MySQL database with a Python application can take a bit of work your first time. If you’re already a savvy MySQL user, the commands in Python are the same, but there’s an added layer of abstraction, as a cursor object is used. This can take some getting used to, but it’s worth it to have access to powerful analytic tools with Python.