You are yet to become a backend developer, if you have never connected your python application to a MySQL database, create a table and perform various CRUD operations. MySQL database is one of the most popular used relational databases.
By the end of this post, you would have learned how to connect your Python application to a MySQL database and perform various CRUD operations.
Prerequisites
To connect your Python application to the MySQL database, you need to have the following basic items installed on your PC.
Installed MySQL.
Mysql-connector-python module
Connecting Python to MySQL
Prior to any MySQL database operation, you, first of all, need to connect your Python application to the MySQL server.
To establish the connection, you need to follow the following simple steps:
Step 1: First of all, import MySQL. Connector.
|
Import mysql.connector |
Step 2: Now, establish the database connection by entering the correct hostname, username and password. If all or one of these MySQL server credentials is wrong, you’ll definitely receive an error and there will be no connection.
|
db_connection = mysql.connector.connect( host="hostname", user="username", passwd="password") |
Step 3: Now, test the connection. If your connection is successfully installed, the following should be displayed on your screen.
|
<mysql.connector.connection.MySQLConnection object at 0x000002338A4C6B00> |
The following is an example of an example of a Python MySQL connection:
|
import mysql.connector db_connection = mysql.connector.connect( host="localhost", user="root", passwd="root" ) print(db_connection) |
How to create Database
After creating a Python MySQL connection, the second step is to create a MySQL database where you are going to store and retrieve data.
You can only create a database after a successful connection between Python and MySQL.
The syntax of creating a new database includes:
|
CREATE DATABASE "database_name" |
For example, you would like to create a database with a named student, you should run the following code
|
CREATE DATABASE ‘student’ |
How to Create a Table
The MySQL database is a relational database and stores its values in tables. After creating a database, now the next step is to create tables where we are going to insert our values as well as retrieve our data.
The syntax for creating a table includes:
|
CREATE TABLE student (id INT, name VARCHAR (255)) |
From the previous section, we have created a database Student now, we are going to create table marks.
|
CREATE TABLE marks (id INT, name VARCHAR (255)) |
Python CRUD operations with MySQL Database
Create
Creating in CRUD operation simply means inserting data into your MySQL database.
To insert data into the MySQL table, we are going to use the method connection.commit () in order to get the changes. Now, let’s insert sample marks our marks table:
|
query = "insert into marks (Student_name, marks) values (%s, %s)" value = [("Annah", "80"), (“Judy”, “70”), (“Ann”,”87”), (“Kimberly”, “54”)] connection. Commit () cursor.execute(query, value) |
Retrieve
In the CRUD operations, Retrieve means fetching the data in the database table and displaying it on the screen. The main syntax involved includes:
To retrieve data from your marks table we are going to retrieve all data in the table or we can use a specific condition or student name or marks to select specific data to be retrieved.
To retrieve all data we are going to use “*”. For example:
|
cursor = connection.cursor() cursor.execute("Select * from marks") cursor.fetchall() |
To retrieve particular data we are going to insert a condition: For instance, let's display the marks for the student with the name "Annah”.
|
cursor.execute("Select * from marks where student_name='Annah'") cursor.fetchall() |
Update
Update in Crud operations simply means, altering the data inserted in the database table. This operation allows the user to adjust the values he or she entered in the previous insert operation.
Example-To update our marks table, we need to use a specific condition in order to update a specific entry. For instance, lets update the row where the
|
import mysql.connector db_connection = mysql.connector.connect( host="localhost", user="root", passwd="root", database="student" ) db_cursor = db_connection.cursor() #Here we modify existing column id db_cursor.execute("ALTER TABLE marks MODIFY id INT PRIMARY KEY") |
Delete
Delete operation simply means dropping data from the table in the database. This operation allows the user to drop any selected row by user.
For example, to delete the student marks we can run the following code snippet:
|
delete_marks_query = "DELETE FROM stdntmrks WHERE studentname = 'Rajesh'" cursor.execute(delete_patients_query) my_db_con.commit() |
|
Article Contributed By :
|
|
|
|
|
586 Views |
|