Using MySQL with Python: A Step-By-Step Tutorial

In this tutorial, we will explore how to use MySQL databases with Python programming. We will use the mysql-connector-python library for this purpose.

Step 1: Install MySQL Connector

To start, you’ll need to install the MySQL connector for Python. Run the following command:

pip install mysql-connector-python

Step 2: Import MySQL Connector

Once installed, import the library into your Python script:

import mysql.connector

Step 3: Establish a Database Connection

You can connect to a MySQL database like so:

mydb = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password",
    database="your_database"
)

Step 4: Create a Cursor Object

Create a cursor object using the cursor() method:

my_cursor = mydb.cursor()

Step 5: Execute SQL Commands

Use the cursor object to execute SQL commands:

my_cursor.execute("CREATE TABLE students (name VARCHAR(255), age INT)")

Step 6: Fetch and Use Results

After executing a SELECT statement, fetch the results:

my_cursor.execute("SELECT * FROM students")
my_result = my_cursor.fetchall()

for x in my_result:
  print(x)

Step 7: Close the Connection

Finally, close the database connection:

mydb.close()

Conclusion

That’s it! Now you know how to use MySQL with Python using mysql-connector-python.

Step 0: Install MySQL Server

Before you can connect to a MySQL database through Python, you need to make sure MySQL Server is installed on your machine. You can download it from the official MySQL website.

Follow the installation steps for your operating system. Once installed, you’ll be able to start, stop, and manage your MySQL databases using the MySQL Server software.

Alternative: Use a Remote MySQL Server

If you prefer not to install MySQL Server locally, you can also use a remote MySQL database. In this case, you’ll need to know the host, username, and password for the remote connection.

Installing MySQL Server on Windows 11

Step 0.1: Download the MySQL Installer

Go to the MySQL Installer download page and download the installer. You can choose either the web-community version, which will download components during installation, or the full version that includes everything in the package.

Step 0.2: Run the Installer

Run the downloaded installer file to start the installation process. Accept the license terms and choose the setup type. For most users, the “Developer Default” option is suitable for a full-featured installation.

Step 0.3: Component Selection

Select the components you want to install. At a minimum, you’ll need the MySQL Server and MySQL Workbench. Click ‘Next’ to proceed.

Step 0.4: Configuration

The installer will now guide you through the configuration process. You’ll set your MySQL root password here and make other optional configurations. Make sure to remember the root password as you’ll need it to connect to the MySQL server later.

Step 0.5: Installation

Click ‘Execute’ to install the selected components. Once the installation is complete, click ‘Finish’ to close the installer.

Step 0.6: Test the Installation

To test whether MySQL Server is installed properly, open the Command Prompt and type the following command:

mysql -u root -p

You’ll be prompted for the root password. If you’re able to log in successfully, the installation was successful.

Do I Need to Run the Connection Code Every Time?

Once you’ve connected to the MySQL database in your Python script using the mysql.connector.connect() function, the connection remains active for the duration of your script or session. This means that you can execute multiple SQL commands without having to reconnect.

However, if you start a new Python script or session, you’ll need to re-establish the connection to the MySQL database. Essentially, the connection line:

mydb = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password",
    database="your_database"
)

needs to be executed every time you start a new Python environment that requires database access.

Setting MySQL Server Path in Environment Variables (Optional)

Why Set the Path?

Setting the MySQL Server path in your Windows environment variables allows you to run MySQL commands from any Command Prompt window without having to navigate to the MySQL Server installation directory.

Step-by-Step Guide for Windows 11

1. Locate the installation directory of MySQL Server. The default location is often C:\Program Files\MySQL\MySQL Server x.y\bin, where x.y is your installed version number.
2. Copy the full path to the bin directory.
3. Right-click on the Start button and select System.
4. Click on Advanced system settings on the right side.
5. Click on the Environment Variables button at the bottom.
6. In the System Variables section, find the Path variable and click Edit.
7. Click New and paste the path to the MySQL bin directory.
8. Click OK to close all windows.

Once you’ve set the MySQL path in the environment variables, you should be able to run MySQL commands from any Command Prompt window. If it doesn’t work immediately, you may need to restart your Command Prompt or even your computer for the changes to take effect.

Finding Your MySQL Server Username

Default Username

The default username for a fresh MySQL Server installation is usually root. During the installation process, you are often asked to set a password for this root user. Keep both the username and password safe, as you’ll need them to connect to the MySQL database.

Additional Usernames

If you’ve set up additional user accounts in MySQL, you can use those usernames to connect to your databases. Usernames and their permissions can be configured through MySQL Workbench, command-line interface, or through SQL commands.

How to Check Existing Usernames

You can list all MySQL user accounts by logging into MySQL and running the following SQL query:

SELECT user FROM mysql.user;

This will display a list of all usernames that can connect to the MySQL Server.

Forgot Username or Password?

If you have forgotten your MySQL username or password, you may need to reset it through the MySQL Server configuration or consult your database administrator for assistance.

Troubleshooting: SyntaxError When Running MySQL Commands

Symptoms

If you’re seeing an error like SyntaxError: Unexpected identifier 'root', it likely means you are trying to run the MySQL command in an inappropriate environment.

The Cause

This error usually occurs when you try to run MySQL commands within a JavaScript environment like Node.js or a browser console. MySQL commands are meant to be run in a Command Prompt or terminal window.

The Solution

Open a new Command Prompt or terminal window and try running the MySQL command again:

mysql -u root -p

This command will prompt you for the root password you set during the MySQL Server installation. Enter the password when prompted to log into MySQL.

Troubleshooting: ‘mysql’ is not recognized in PowerShell

Symptoms

You may see an error like this when trying to run the mysql command in PowerShell:

mysql : The term 'mysql' is not recognized as the name of a cmdlet, function, script file, or operable program.

The Cause

This error occurs because PowerShell does not load commands from the current directory by default. This is a security feature to prevent the accidental execution of scripts and executables that you might not intend to run.

The Solution

To resolve this issue, you can use .\mysql instead of just mysql to explicitly tell PowerShell to run the mysql command from the current directory:

.\mysql -u root -p

This will prompt you for the root password, and upon successful authentication, you’ll be taken to the MySQL command-line interface.

Troubleshooting: Databases Not Displaying with show databases

Symptoms

When you run the command show databases, you see the following prompt and no databases are displayed:

mysql> show databases
    ->

The Cause

This usually happens when the SQL statement is not terminated with a semicolon. MySQL is waiting for the statement to be terminated to execute it.

The Solution

Terminate the SQL statement with a semicolon like so:

mysql> show databases;

This will execute the command, and you should see a list of all databases you have permission to view.

Troubleshooting: Syntax Error in show databases Command

Symptoms

If you see an error like this:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'datbases' at line 1

The Cause

The error occurs because of a typo in the SQL command. The correct command for listing all databases is show databases;, not show datbases;.

The Solution

Run the command with the correct syntax:

mysql> show databases;

This will display a list of all databases that you have the permission to view.