Python MySQL Database Connection

In this section, we will discuss the steps to connect the python application to the mysql database.

These are the following steps to connect a python application to our database.

1. import mysql.connector module
2. Create the connection object.
3. Create the cursor object
4. Execute the query
5. Close the Connection

Creating Database connection

To create a connection between the MySQL database and the python application, the connect() method of mysql.connector module is used

Pass the database details like HostName, username, and the database password in the method call. The method returns the connection object. The syntax to use the connect() is given below.

Connection-Object= mysql.connector.connect(host = <host-name> , user = <username> , password = <password> )

MySQL Database connection

import mysql.connector  
#Create the connection object   
myconn = mysql.connector.connect(host = "localhost", user = "root",password = "gkindex")  
#printing the connection object   
print(myconn)

Here, we must notice that we can specify the database name in the connect() method if we want to connect to a specific database.

MySQL Connection Object

import mysql.connector  
  
#Create the connection object   
myconn = mysql.connector.connect(host = "localhost", user = "root",password = "gkindex", database = "py")  
  
#printing the connection object   
print(myconn)

MySQL DB cursor object

We can create the cursor object by calling the 'cursor' function of the connection object. The cursor object is an important aspect of executing queries to the databases.

The syntax to create the cursor object is given below.

my_cur>  = conn.cursor()

Python Database cursor object

import mysql.connector  
myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "gkindex", database = "py")  
  
#printing the connection object   
print(myconn)   
  
#creating the cursor object  
cur = myconn.cursor()  
print(cur)

Creating new databases in MySQL

Here, we will create the new database PythonDB.

Getting the list of existing databases

We can get the list of all the databases by using the following MySQL query.

show databases;

import mysql.connector  
  
#Create the connection object   
myconn = mysql.connector.connect(host = "localhost", user = "root",password = "gkindex")  
  
#creating the cursor object  
cur = myconn.cursor()  
  
try:  
    cur.execute("show databases")  
except:  
    myconn.rollback()  
for x in cur:  
    print(x)  
myconn.close()
						

Creating the new database

The new database can be created by using the following SQL query.

create database <database-name> 

import mysql.connector  
  
myconn = mysql.connector.connect(host = "localhost", user = "root",password = "gkindex")   
cur = myconn.cursor()  
  
try:  
    #creating a new database  
    cur.execute("create database PythonDB")  
  
    #getting the list of all the databases which will now include the new database PythonDB  
    dbs = cur.execute("show databases")  
except:  
    myconn.rollback()  
for x in cur:  
        print(x)  
myconn.close()