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
Thanks for reading.