How To Connect MySQL Database In Python?

How To Connect MySQL Database In Python?

by

MySQL and Python are popular technologies related to programming and database. Python applications can use the MySQL databases to store data and use them. In order to store and use data in the MySQL database, the Python application should connect to the MySQL database.

Install MySQL Connector Module For Python

By default, Python does not provide the MySQL Connector module. The MySQL connectors are used to connect MySQL databases and run queries. The MySQL connector is named as mysql-connector-python package. This can be installed by using the pip command like below.

$ pip install mysql-connector-python

Import mysql.connector Module

After the installation is completed successfully we should import the mysql.connector module in order to use connection-related methods. The installed module can be imported with the mysql.connector name below.

import mysql.connector

Connect MySQL Database with connect() Method

The mysql.connector provides the connect() method in order to connect MySQL database or server. The connect() accept multiple parameters in order to specify the host or IP address or the MySQL database, username, and password for authentication to the MySQL database server.

import mysql.connector

c = mysql.connector.connect(host="localhost",user="ismail",password="123secret")

Connect Remote MySQL Database

The MySQL database service can be provided by another host via the network. The IP address or hostname of the MySQL database server can be provided with the host parameter for the connect() method.

import mysql.connector

c = mysql.connector.connect(host="db.com",user="ismail",password="123secret")

Alternatively, we can specify the IP address of the MySQL database server via the host parameter.

import mysql.connector

c = mysql.connector.connect(host="1.2.3.4",user="ismail",password="123secret")

Specify Database Name

During the connection to the MySQL database, we can also provide the database name as a parameter to the connect() method. The database parameter is used to set the default database for the established connection.

import mysql.connector

c = mysql.connector.connect(host="1.2.3.4",user="ismail",password="123secret",database="app")

Provide Database Connection Settings As Dictionary

The dictionary data type is used to store data in a key:value pair. The MySQL connection configuration can be specified as a dictionary data type and provided to the connect() method. The keys should be defined with the same names of the connect() method parameters.

import mysql.connector

db_config = {
   'user':'ismail',
   'password':'123secret',
   'host':'1.2.3.4',
   'database':'app'
}


c = mysql.connector.connect(**db_config)

Run SQL Query On MySQL Database

After establishing the connection to the MySQL database server we can use this connection to run and execute SQL queries. In the following example, we run the “Select * From Users” query over the established MySQL connection. In order to run SQL query a cursor should be initiated with the cursor() method and the query is executed via this cursor execute() method.

import mysql.connector

db_config = {
   'user':'ismail',
   'password':'123secret',
   'host':'1.2.3.4',
   'database':'app'
}

db_connection = mysql.connector.connect(**db_config)

db_cursor = db_connection.cursor()

db_cursor.execute("Select * From Users")

Closing MySQL Database Connection

Every created MySQL database connection requires and allocates resources from the local system and remote system. So if the task is completed and there is no need for MySQL database connection the connection should be closed with the close() method.

db_connection.close()

Leave a Comment