Here we will see how to connect to MySQL database server using Python in different ways. We need to install mysql-connector-python module to connect to MySQL database from Python programming language.

We will create the connection objects in different ways and using this connection object we will be able to connect to MySQL server.

Prerequisites

Python 3.8.3, MySQL 8.0.17

Install mysql-connector-python module using the command pip install mysql-connector-python from the command line tool.

Connect To MySQL

Now let’s create connection object to connect to MySQL server.

The connect() constructor creates a connection to the MySQL server and returns a MySQLConnection object.

The following code example connects to MySQL server:

import mysql.connector

conn = mysql.connector.connect(user='root', password='root', host='127.0.0.1', database='roytuts')

print (conn)

conn.close()

The above code will give you the following output which returns MySQLConnection object:

<mysql.connector.connection.MySQLConnection object at 0x00000201D5B42400>

We can also create connection objects using the MySQLConnection class:

from mysql.connector import (connection)

conn = connection.MySQLConnection(user='root', password='root', host='127.0.0.1', database='roytuts')

print (conn)

conn.close()

The above code also will return the similar MySQLConnection object.

If you want to handle connection errors, use the try statement and catch all errors using the errors.Error exception:

import mysql.connector
from mysql.connector import errorcode

try:
	conn = mysql.connector.connect(user='root', password='root', host='127.0.0.1', database='roytuts')

	print (conn)
except mysql.connector.Error as err:
	if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
		print("Something is wrong with user name or password")
	elif err.errno == errorcode.ER_BAD_DB_ERROR:
		print("Database does not exist")
	else:
		print(err)
else:
	conn.close()

We can also define connection arguments in a dictionary and using the ** operator is another option:

import mysql.connector

config = {
	'user': 'root',
	'password': 'root',
	'host': '127.0.0.1',
	'database': 'roytuts',
	'raise_on_warnings': True
}

conn = mysql.connector.connect(**config)

print (conn)

conn.close()

Python Connector offers two implementations: a pure Python interface and a C extension that uses the MySQL C client library. This can be configured at runtime using the use_pure connection argument. The default value of this argument is False in MySQL 8 and it means C extension is used. If the C extension is not available on the system then you need to set use_pure to True.

If Connector/Python C Extension is not available and making use_pure=False throws error: MySQL Connector/Python C Extension not available.

import mysql.connector

conn = mysql.connector.connect(user='root', password='root', host='127.0.0.1', database='roytuts', use_pure=True)

print (conn)

conn.close()

The above code will return MySQLConnection object.

Source Code

Download

Thanks for reading.