DEV Community

Cover image for My first CRUD in Python without ORM
Robertopaolo Ramirez
Robertopaolo Ramirez

Posted on • Updated on

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.

docker hub mariadb

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

$ docker pull mariadb
Enter fullscreen mode Exit fullscreen mode

and then create a folder for the project.

$ mkdir my-project
$ cd my-project
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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.

verified create

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

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

welcome mysql

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

add conection

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!

warm message

This confirmation window will appear.

Successfully made

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

CREATE SCHEMA `chanchitohappy`;
Enter fullscreen mode Exit fullscreen mode

first query

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.

on the schema

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`));
Enter fullscreen mode Exit fullscreen mode

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

created

We can confirm as follows.

image

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()
Enter fullscreen mode Exit fullscreen mode

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()
Enter fullscreen mode Exit fullscreen mode

READ

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

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()
Enter fullscreen mode Exit fullscreen mode

DELETE

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

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()
Enter fullscreen mode Exit fullscreen mode

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

Top comments (0)