Python + MySQL: How to Connect Using MySQL Connector

Here is the article, where we will explain how to connect MySQL databases with Python programs. This task can be easily done with the help of the Connector module, which uses only the Python standard library, and there is no need for any specific dependencies.

Establishing a Connection to the Database

MySQL Connector can be used for the connection of cloud-based as well as local databases in the most intuitive and simple way you can ever imagine. In mysql.connector library, there is a method which we will be using called connect().

Here is the standard syntax for the connect() function:  

conn_obj = mysql.connector.connect(

     host="localhost", # Change to your hostname or IP

     user="your_username", # Replace with your MySQL username

     password="your_password", # Replace with your MySQL password

     database="your_database" # Optional: use only if you're connecting to a specific DB

)

Let’s explain some of the next arguments that you should change in the code above:

• ​localhost: should be changed to IP or server name where the database is running.
• ​your_username: by default, it is usually root.
• ​your_password: in case you are using root, there is no necessity to use a password.

Establishing connection to a local database

Here is an example of how a connection can be made to a local MySQL database:

# Connecting to the MySQL server

conn = mysql.connector.connect(

     user='username',

     host='localhost',

     password='your_password',   # Add password if required

     database='database_name'

)

if conn.is_connected():

     print("Successfully connected to the database.")

     print("Connection object:", conn)

Also, it is possible to use MySQLConnection() instead of the above-mentioned variant. In such a scenario, the code will be as follows:

# Connecting to the MySQL server

conn = connection.MySQLConnection(

     user='username',

     host='localhost',

     password='your_password',   # Add password if required

     database='database_name'

)

if conn.is_connected():

     print("Successfully connected to the database.")

     print("Connection object:", conn)

One more possible variant is the usage of the ‘**’ operator, as in here:

db_config = {

'user': 'root',

'host': 'localhost',

'password': 'your_password',  # Add password if needed

'database': 'College'

}

try:

# Connecting to the server

conn = connection.MySQLConnection(**db_config)

if conn.is_connected():

     print("Connected to the MySQL database.")

     print("Connection object:", conn)

Establishing connection to online database

Connection to the online database is almost identical to the local connections. Here, you should just change the parameter so that the online server will be mentioned here.

dataBase = mysql.connector.connect(

     host="your-cloud-database-host",   # e.g., "db.example.com"

     user="your-username",

     passwd="your-password",

     database="your-database-name"

)

if dataBase.is_connected():

     print("Successfully connected to the cloud database.")

By just changing the value, it is possible to easily switch between cloud and local database, and there is no need to write new code all the time.