In Python, establishing a database connection serves as a communication channel between a Python application and a database management system. Through this connection, Python is enabled to carry out various tasks, such as executing SQL queries, retrieving results, and conducting database transactions. In this section, we will outline the procedures necessary for linking a Python application to a database.
There are the following steps to connect a python application to our database.
- Import mysql.connector module
- Create the connection object.
- Create the cursor object
- Execute the query
Creating the connection
To establish a link between the MySQL database and the Python application, the connect function from the mysql.connector module is utilized.
Provide the database information such as HostName, username, and the password for the database when invoking the method. This method will return the connection object.
Syntax for Creating the Database Connection
The syntax to use the connect is given below.
Connection-Object= mysql.connector.connect(host = <host-name> , user = <username> , passwd = <password> )
Python Example for Creating the Database Connection
Let us examine the subsequent example to illustrate the process of establishing a connection to the database.
import mysql.connector
#Create the connection object
myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "google")
#printing the connection object
print(myconn)
Output:
<mysql.connector.connection.MySQLConnection object at 0x7fb142edd780>
It is important to highlight that within the connect method, we have the option to indicate the database name if our intention is to establish a connection to a particular database.
Example
import mysql.connector
#Create the connection object
myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "google", database = "mydb")
#printing the connection object
print(myconn)
Output:
<mysql.connector.connection.MySQLConnection object at 0x7ff64aa3d7b8>
Creating a cursor object
The cursor object serves as an abstraction outlined in the Python DB-API 2.0. It enables the creation of multiple distinct working environments utilizing a single connection to the database. To instantiate the cursor object, we invoke the 'cursor' method of the connection object. This cursor object plays a crucial role in executing queries against the databases.
Syntax for Creating Cursor Object
The following syntax is utilized to instantiate the cursor object.
<my_cur> = conn.cursor()
Python Example for Creating Curson Object
Let us examine the subsequent example to illustrate the process of generating the cursor object.
import mysql.connector
#Create the connection object
myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "google", database = "mydb")
#printing the connection object
print(myconn)
#creating the cursor object
cur = myconn.cursor()
print(cur)
Output:
<mysql.connector.connection.MySQLConnection object at 0x7faa17a15748>
MySQLCursor: (Nothing executed yet)