TechVidvan

Integrating Database Access through Python

One of the most well-liked database management systems (DBMSs) on the market right now is MySQL. In this year’s DB-Engines Ranking, it came in second place behind only the Oracle DBMS. Programming languages like Python offer capabilities for storing and accessing different data sources since the majority of software applications require some sort of interaction with data.

You will be able to successfully link a MySQL database with a Python program by using the methods covered in this article. For a movie rating system, you’ll create a simple MySQL database and discover how to use it straight from Python scripts.

To make the most of this lecture, you should be comfortable utilizing Python concepts like loops, functions, and exception handling. Additionally, you ought to be familiar with relational like drop SELECT, DROP, CREATE, and JOIN.

Linking Python with MySQL

The mysql.connector modules connect() method is used to establish a connection between Python and the MySQL database. After obtaining the hostname, username, and password for the database in the method call, the method returns the connection object.

Python and the database can be connected relatively easily. It shows how a connection request is sent to the MySQL connector in Python, approved by the database, and then a cursor is run with the return data.

To connect SQL and Python, the following procedures must be followed:

Step 1: Free MySQL database download and installation

Step 2: Open the command prompt after the MySQL database has been installed.

Step 3: Navigate to PIP using your command prompt. How to install PIP may be seen here.

Step 4: Execute the following commands to download and install “MySQL Connector.” In this case, the mysql.connector statement will assist you in interacting with the MySQL database.

Step 5: Test the connector

Go to your IDE and run the following code to see if the installation of “MySQL Connector” was successful or if you have already done so:

Syntax:

import mysql.connector

Step 6: Establish a Connection

Now run the code provided below in your IDE to link with SQL.

Syntax:

# Importing module
import mysql.connector
 
mydb = mysql.connector.connect(
    host = "localhost1",
    username = "Enter_username",
    password = "Enter_password"
)
print(mydba)

Creating a Connection with the MySQL Server

The database management system MySQL runs on servers. There could be several databases on one server. You need to connect to the server to interact with a database. When Python software communicates with a MySQL-based database, the usual approach is as follows:

  • Link up with the MySQL server.
  • Build a fresh database.
  • A newly built or existing database should be accessed.
  • Run a SQL query to get information.
  • If any modifications are made to a table, notify the database.
  • Connecting to the MySQL server should be terminated.

Example:

from getpass import getpass
from mysql.connector import connect, Error

Try:
    with connect(
        host="host1",
        userdata=input("Username: "),
        password=getpass("Password: "),
    ) as connected:
        print(connected)
except Error as err:
    print(err)

The code above connects to your MySQL server using the login credentials you entered. A MySQLConnection object, kept in the connection variable, is what you receive in return.

There are a few significant details to note in the code above:

Dealing with potential exceptions that may be thrown while connecting to the MySQL server is a best practice. This is why you employ a try-except block to identify and print any potential exceptions.

After using the database, you should always terminate the connection. Leaving idle connections open can result in several unpleasant errors and performance problems. The code in the above sentence makes use of a context manager called with, which abstracts away the connection cleanup procedure.

Establishing a New Database

You connected to your MySQL server in the previous section. You must run a SQL statement to create a new database:

Syntax:

CREATE DATABASE Notes_db;

You must employ a cursor, which hides database record access, to carry out a SQL query in Python. The MySQLCursor class, which is offered by MySQL Connector/, creates objects that can run Python MySQL queries. “Cursor” is another name for an instance of the MySQLCursor class.

Linking to a Current Database:

An association between Python and the MySQL database is made using the connect() method of the mysql.connector module. The hostname, username, and password for the database are passed to the function along with other database-related data, and the method then returns the connection object.

Using the same connect() procedure that you did earlier and by including a database parameter, you can accomplish this:

from getpass import getpass
from mysql.connector import connect, Error

Syntax:

Try:
    with connect(
        host="localhost1",
        username=input("TechVidvan Username is: "),
        pass=getpass("TechVidvan Password is : "),
        database="online_book_rate",
    ) as connect:
        print(connect)
except Error as err:
    print(err)

Database Operations[CRUD]:

A programmer can conduct a wide range of operations utilising databases and SQL if they are well-versed in MySQL and database programming.

Below, I’ve shown how to use CRUD.

Create: It is a SQL command that creates a record in a table, or you could say it makes a table.

Read: This function is used to retrieve pertinent data from the database.

Update: This specific SQL statement is used to update the table or the records in the table.

Delete: This command is used to delete tables, as its name implies.

Conclusion:

With TechVidvan we learned that this language is a powerful, all-purpose, and widely used programming language. In essence, programmers may communicate their notions in fewer lines of code thanks to the design’s emphasis on code readability. It can also be used with SQL.