Python Database Connection
In this section, we will discuss the steps to connect the python application to the database.
These 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
Close the Connection
Creating the 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> )
Python Database Connection Example
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.
Python Database Connection Object Example
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)
Python Database 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 Example
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()