Learn Python – How to connect Database in Python- Basic and advance

The database is a well-organized collection of structured information or statistics saved in a pc system. In database, the records is organized in the tabular form, and we can get admission to that records or facts via querying.

Python can be used to connect the Database. MySQL is one of the most popular Databases. In this tutorial, we will examine to establish a connection to MySQL via Python. Let’s apprehend the following steps to work with the MySQL using Python.

Install MySQL Driver
Create a connection Object
Create a cursor Object
Execute the Query

Install MySQL Driver

First, we want a MySQL driver in our system. Install the MySQL software and configure the settings. We will use the MySQL connector driver, which is established the use of the pip command. Open a command prompt and kind the following command.

python -m pip install mysql-connector-python  

Press the enter button. It will down load the MySQL driver.

Verify the Driver

Let’s check whether or not we have mounted it true or not. It can be performed with the aid of importing the mysql.connector.

import mysql.connector  

If this line is achieved except error, it capacity MySQL connector has established properly. We are equipped to use it.

Create a Connection Object

The mysql.connector affords the connect() method used to create a connection between the MySQL database and the Python application. The syntax is given below.

Syntax:

Conn_obj= mysql.connector.connect(host = <hostname>, user = <username>, passwd = <password>)    

The connect() function accepts the following arguments.

Hostname – It represents the server name or IP address on which MySQL is running.

Username – It represents the name of the user that we use to work with the MySQL server. By default, the username for the MySQL database is root.

Password – The password is provided at the time of installing the MySQL database. We don’t need to pass a password if we are using the root.

Database – It specifies the database name which we want to connect. This argument is used when we have multiple databases.

Consider the following example.

Example –

import mysql.connector  
  
# Creating a the connection object     
conn_obj = mysql.connector.connect(host="localhost", user="root", passwd="admin123")  
  
# printing the connection object     
print(conn_obj)    

Output:

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

Create a Cursor Object

The connection object is indispensable to create due to the fact it presents the more than one working environments the same connection to the database. The cursor() feature is used to create the cursor object. It is import for executing the SQL queries. The syntax is given below.

Syntax:

Con_obj = conn.cursor()  

Let’s understand the following example.

Example –

import mysql.connector  
  
# Creating the connection object     
conn_obj = mysql.connector.connect(host="localhost", user="root", passwd="admin123", database="mydatabase")  
  
# printing the connection object     
print(conn_obj)  
  
# creating the cursor object    
cur_obj = conn_obj.cursor()  
  
print(cur_obj)    

Output:

 
MySQLCursor: (Nothing executed yet)

Executes the Query

In the following example, we will create a database by means of executing the query. Let’s understand the following example.

Example –

import mysql.connector  
  
# Creating the connection object     
conn_obj = mysql.connector.connect(host="localhost", user="root", passwd="admin123")  
  
# creating the cursor object    
cur_obj = conn_obj.cursor()  
  
try:  
    # creating a new database using query   
    cur_obj.execute("create database New_PythonDB")  
  
    # getting the list of all the databases which will now include the new database New_PythonDB    
    dbms = cur_obj.execute("show databases")  
  
except:  
    conn_obj.rollback() # it is used if the operation is failed then it will not reflect in your database  
  
for x in cur_obj:  
    print(x)  
  
conn_obj.close()   

Output:

'information_schema',)
('javatpoint',)
('javatpoint1',)
(New_Pythondb)
('mydb',)
('mydb1',)
('mysql',)
('performance_schema',)
('testDB',)

In the above tutorial, we have discussed how we can establish a connection to MySQL through Python. You can study whole Python with MySQL from (https://www.javatpoint.com/python-mysql-database-connection).