My first CRUD in Python without ORM
Tags: beginners, python, docker, sql
Published: 2022-09-02 01:45:46

## 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](https://hub.docker.com/), search for mariadb, choose the first option, which is an official image, something similar to the image below will appear.

![docker hub mariadb](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wk7ewcm2ur8m9f5hxgha.png)

Open your terminal and run the following command to download the image.
```bash
$ docker pull mariadb
```

and then create a folder for the project.

```bash
$ 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.

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

We will add the following lines
```yaml
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.

![verified create](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/o4z21p7tb19uppfpxpp9.png)

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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/v5w39gh87arpycu6ylq0.png)

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


![add conection](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/rfd8gr9w0imtopoplzyr.png)

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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5z0izx2zhzqrsq2psila.png)

This confirmation window will appear.

![Successfully made](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/cbsm1gi2wet5ti5vmi4r.png)

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

```sql
CREATE SCHEMA `chanchitohappy`;
```

![first query](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ollxijy3f6ywfai499nq.png)

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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ynnlmfavc79txya0lcop.png)

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

```sql
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

![created](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/dvgoza24ftkyyltjcfaa.png)

We can confirm as follows.

![image](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/l12vardyjq7wahnjil0a.png)

Okay, now we can go to our favorite IDE, create an `index.py` file and run our code.
```python
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
```python
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
```python
command = f'SELECT * FROM vendas'
cursor.execute(command)
resultado = cursor.fetchall()
print(resultado)
```
## UPDATE
```python
# 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
```python
product_choosed = 'coca cola'
command = f'DELETE FROM vendas WHERE name_product = "{product_choosed}"'
cursor.execute(command)
conexao.commit()
```
## FINAL RESULT

```python
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