How to connect MySQL database in Python

1. MySQL Database Management System

MySQL is a relational database management system that is open-source and free. It was developed by Oracle and is written in C and C++. You can download and use MySQL for free from the website https://www.mysql.com/.

We can use MySQL integrated into XAMPP. You can read the article Installing the PHP Web Development Environment with XAMPP for instructions on installing XAMPP and running MySQL. In this article, we will use Python to create a connection to MySQL integrated into XAMPP.

2. Install the MySQL Connector module in Python

To connect to MySQL, use the MySQL Connector module in Python. You need to first install it using the command below to use this library:

pip install mysql-connector-python

To install Python libraries in Visual Studio Code, refer to the article Installing Python and dev environment with Visual Studio Code.

Once you have installed the MySQL Connector module, you can import it with the following command:

import mysql.connector

3. Create a Connection to MySQL in Python

3.1. Create a Connection to MySQL

The MySQL Connector module supports the connect() function to establish a connection with MySQL. Please note that you need to start MySQL in XAMPP before creating a connection with MySQL.

import mysql.connector

# create connection to MySQL
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  password=""
)

print(mydb)
# disconnect from MySQL
mydb.close()

Result of successful connection

<mysql.connector.connection_cext.CMySQLConnection object at 0x000001A01C79C4C0>

Result of connection failure

File "C:\Python\Python310\lib\site-packages\mysql\connector\connection_cext.py", line 241, in _open_connection
    raise errors.get_mysql_exception(msg=exc.msg, errno=exc.errno,
mysql.connector.errors.DatabaseError: 2003 (HY000): Can't connect to MySQL server on 'localhost:3306' (10061)

Note: If you’re unable to connect to MySQL, it could be because MySQL isn’t running or the host, user, password, or database information is incorrect.

The parameters of the connect() function is defined as follows:

• The host parameter requires the name or IP address of the computer where MySQL is running and to which you want to connect.
• The user parameter specifies the username to log into MySQL. The default username for MySQL in XAMPP is root.
• The password parameter specifies the password to log into MySQL. The default password for MySQL in XAMPP is empty.

To disconnect from MySQL, use the close() function to close the connection.

3.2. Connect to a specific database in MySQL

Once you have initiated MySQL in XAMPP, open your web browser and go to http://localhost/phpmyadmin/ to create a database in MySQL.

Here is the database gochocit that was just created.

To connect to the gochocit database in MySQL, pass the database parameter to the connect() function of the MySQL Connector module.

import mysql.connector

# create connection to MySQL
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  password="",
  database = "gochocit"
)

print(mydb)
# disconnect from MySQL
mydb.close()

In the upcoming articles, we will use Python to perform operations on the gochocit database that we have connected to.