My first CRUD in Python without ORM

Requirements

• A database (MySQL or MariaDB)
• Docker
• Python

On this occasion we will use docker to connect with a database, for this we need the image and use it in Docker.

On the site docker hub, search for mariadb, choose the first option, which is an official image, something similar to the image below will appear.

Open your terminal and run the following command to download the image.

$ docker pull mariadb

and then create a folder for the project.

$ mkdir my-project
$ cd my-project

On this occasion we will use docker compose for the database, we could raise the container with Mariadb in one command, but I don't particularly like it, I prefer to use docker compose.

$ touch docker-compose.yml
$ nvim docker-compose.yml

We will add the following lines

version: "3.9"
services:
  chanchito:
    image: mariadb:10.6
    ports:
      - "3306:3306"
    environment:
      - MYSQL_ROOT_PASSWORD=root
      - MYSQL_PASSWORD=root
      - MYSQL_USER=roberto

These are the parameters we need for a database.

• image name
• version
• doors
• variables (password, username)

For more information, you can follow the Mariadb/MySQL documentation, which are basically the same. At the end of the tutorial, I will put the links for you!

When finished, we save and exit the file. In your terminal, inside the project folder that we created is the file, we will run it to raise our db.

Alright, now we need to create our “Tables” and “Schema” in MariaDB.

We will use MySQL Workbench to create in a more intuitive way.

We will create a new connection by clicking on the + icon

This window will appear, simply fill in the values that we put in our docker-compose.yml , click on Test connection, if everything is ok, a “warning” message will appear like this, because we are using Mariadb and not MySQL . Click Continue Anyway!

This confirmation window will appear.

Ready, now, to create our “Schema” and Tables, to create our “Schema” we must type.

CREATE SCHEMA `chanchitohappy`;

We click on the radius or press ctrl + Enter. Soon after, you can click refresh, if the new schema created does not appear in the left corner.

Now you might ask yourself “but aren't we going to use python to create?” and the answer is yes, but… we don't normally use python to create tables, we use python or another language to interact with the database.

The database structure is essentially already created

Create a table

CREATE TABLE `chanchitohappy`.`vendas` (
    `idProduct` INT NOT NULL AUTO_INCREMENT,
    `name_product` VARCHAR(45)NULL,
    `price` INT NULL,
    PRIMARY KEY(`idProduct`));

Again, we run the query and our “sales” table will be created

We can confirm as follows.

Okay, now we can go to our favorite IDE, create an index.py file and run our code.

import mysql.connector

conection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='root',
    database='chanchitohappy'
)

cursor = conexao.cursor()


cursor.close()
conexao.close()

What we did was generate a connection to the database, for that we need the following parameters: host, user, password, database

After that, like every system, we must turn on and off both the cursor and the connection.

Now, every command we do will be between cursor = conexao.cursor() and cursor.close()

CREATE

product_choosed = 'coca cola'
price_choosed = 5

# CREATE
command = f'INSERT INTO vendas (name_product, price) VALUES ("{product_choosed}", {price_choosed})'
cursor.execute(command)
conexao.commit()

READ

command = f'SELECT * FROM vendas'
cursor.execute(command)
resultado = cursor.fetchall()
print(resultado)

UPDATE

# UPDATE
product_choosed = 'coca cola'
price_choosed = 3

command = f'UPDATE vendas SET price = {price_choosed} WHERE name_product = "{product_choosed}"'
cursor.execute(command)
conexao.commit()

DELETE

product_choosed = 'coca cola'
command = f'DELETE FROM vendas WHERE name_product = "{product_choosed}"'
cursor.execute(command)
conexao.commit()

FINAL RESULT

import mysql.connector

conexao = mysql.connector.connect(
    host='localhost',
    user='root',
    password='root',
    database='chanchitohappy',
)

cursor = conexao.cursor()

# CREATE
product_choosed = 'coca cola'
price_choosed = 5
command = f'INSERT INTO vendas (name_product, price) VALUES ("{product_choosed}", {price_choosed})'
cursor.execute(command)
conexao.commit()

# READ
command = f'SELECT * FROM vendas'
cursor.execute(command)
resultado = cursor.fetchall()
print(resultado)

# UPDATE
product_choosed = 'coca cola'
price_choosed = 3

command = f'UPDATE vendas SET price = {price_choosed} WHERE name_product = "{product_choosed}"'
cursor.execute(command)
conexao.commit()

# DELETE
product_choosed = 'coca cola'
command = f'DELETE FROM vendas WHERE name_product = "{product_choosed}"'
cursor.execute(command)
conexao.commit()

cursor.close()
conexao.close()

Remember for each action (execution), comment the others, because it will show error.

We could add error handling, in addition to putting each action in a function and calling them when needed, but this is the basis of CRUD in python, I hope you learned it as I did. A big hug, until next time!

Links

https://hub.docker.com/_/mariadb