Crud Operations In Python

CRUD stands for Create, Read, Update, and Delete. These operations are essential when working with databases. In this example, we will use Python and MySQL to perform CRUD operations.

Installing Requirements

First, we need to install the MySQL connector for Python using pip. You can install it by running the following command in your terminal:

pip install mysql-connector-python

Once you have installed the connector, you can use it in your Python script to connect to your MySQL database and perform CRUD operations.

Connecting to the MySQL Database

To connect to the MySQL database, you will need to provide the host, user, password, and database name. You can do this using the mysql.connector.connect() function. Here's an example:

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  password="yourpassword",
  database="mydatabase"
)

In this example, we connect to the MySQL database running on localhost using the yourusername and yourpassword credentials. We also specify the mydatabase database as the default database.

Creating a Table

To create a table, we can use the CREATE TABLE statement. Here's an example:

mycursor = mydb.cursor()
mycursor.execute("CREATE TABLE customers (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), address VARCHAR(255))")

In this example, we create a table called customers with three columns: id, name, and address. The id column is an auto-incrementing primary key.

Inserting Data

To insert data into the table, we can use the INSERT INTO statement. Here's an example:

sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = ("John", "Highway 21")
mycursor.execute(sql, val)
mydb.commit()

print(mycursor.rowcount, "record inserted.")

In this example, we insert a new record into the customers table with a name of "John" and an address of "Highway 21". We use the %s placeholder to prevent SQL injection attacks. The actual values are passed as a tuple to the execute() method.

After executing the INSERT INTO statement, we need to commit the changes using the commit() method. We also print the number of records inserted using the rowcount attribute.

Reading Data

To read data from the table, we can use the SELECT statement. Here's an example:

mycursor.execute("SELECT * FROM customers")
myresult = mycursor.fetchall()

for x in myresult:
  print(x)

In this example, we select all the records from the customers table using the SELECT * statement. We then fetch all the results using the fetchall() method.

We can then iterate through the results using a for loop and print each record.

Updating Data

To update data in the table, we can use the UPDATE statement. Here's an example:

sql = "UPDATE customers SET address = %s WHERE name = %s"
val = ("Canyon 123", "John")

mycursor.execute(sql, val)
mydb.commit()

print(mycursor.rowcount, "record(s) affected")

In this example, we update the address column of the record with a name of "John" to "Canyon 123". We use the %s placeholder to prevent SQL injection attacks. The actual values are passed as a tuple to the execute().

Delete Data

To delete data from a MySQL database using Python, we can use the DELETE statement. Here's an example:

sql = "DELETE FROM customers WHERE name = %s"
val = ("John", )

mycursor.execute(sql, val)
mydb.commit()

print(mycursor.rowcount, "record(s) deleted")

In this example, we delete the record(s) with a name of "John" from the customers table using the DELETE statement. We use the %s placeholder to prevent SQL injection attacks. The actual values are passed as a tuple to the execute() method.

After executing the DELETE statement, we need to commit the changes using the commit() method. We also print the number of records deleted using the rowcount attribute.

Note that the rowcount attribute will return the number of records deleted, not the number of affected rows. If there were no matching records to delete, the rowcount attribute will return 0.