Python MySQL - Database Connection

To connect with MySQL, (one way is to) open the MySQL command prompt in your system as shown below −

It asks for password here; you need to type the password you have set to the default user (root) at the time of installation.

Then a connection is estabpshed with MySQL displaying the following message −

Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 4
Server version: 5.7.12-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affipates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affipates. Other names may be trademarks of their respective
owners.

Type  help;  or  h  for help. Type  c  to clear the current input statement.

You can disconnect from the MySQL database any time using the exit command at mysql> prompt.

mysql> exit
Bye

Estabpshing connection with MySQL using python

Before estabpshing connection to MySQL database using python, assume −

That we have created a database with name mydb.

We have created a table EMPLOYEE with columns FIRST_NAME, LAST_NAME, AGE, SEX and INCOME.

The credentials we are using to connect with MySQL are username: root, password: password.

You can estabpsh a connection using the connect() constructor. This accepts username, password, host and, name of the database you need to connect with (optional) and, returns an object of the MySQLConnection class.

Example

Following is the example of connecting with MySQL database "mydb".

import mysql.connector

#estabpshing the connection
conn = mysql.connector.connect(user= root , password= password , host= 127.0.0.1 , database= mydb )

#Creating a cursor object using the cursor() method
cursor = conn.cursor()

#Executing an MYSQL function using the execute() method
cursor.execute("SELECT DATABASE()")

# Fetch a single row using fetchone() method.
data = cursor.fetchone()
print("Connection estabpshed to: ",data)

#Closing the connection
conn.close()

Output

On executing, this script produces the following output −

D:Python_MySQL>python EstabpshCon.py
Connection estabpshed to: ( mydb ,)

You can also estabpsh connection to MySQL by passing credentials (user name, password, hostname, and database name) to connection.MySQLConnection() as shown below −

from mysql.connector import (connection)

#estabpshing the connection
conn = connection.MySQLConnection(user= root , password= password , host= 127.0.0.1 , database= mydb )

#Closing the connection
conn.close()