Crud Python - Sqlite

Comenzando

Creamos un nuevo archivo main.py:

• BASH
• CMD

• 1
  touch main.py
  

• 1
  type null > main.py
  

1
2
3
4
5
def init():
    pass

if __name__ == "__main__":
    init()

La instrucción if __name__ == "__main__": comprueba si el script se está ejecutando como programa principal. Si es así, llama a la función init() que de momento solo tiene la declaración pass esto es más que nada para promover la modularidad y la reutilización. Permite que el script sirva como programa independiente y como módulo importable.

Abrir una nueva conexión a SQLITE

import sqlite3

def init():
    conexion = sqlite3.connect("cars.db")

if __name__ == "__main__":
    init()

Ahora como se puede observar en el código anterior, importamos el módulo de sqlite3 que viene integrado con Python y dentro de la función init() que arranca junto a la ejecución del programa almacenamos en la variable conexion una nueva conexión a un archivo llamado cars.db.

Crear una tabla en la base de datos

Para facilitarnos la existencia, vamos a modularizar el código, quiere decir que vamos subdividir el programa en más archivos.

Creamos un nuevo archivo db.py:

• BASH
• CMD

• 1
  touch db.py
  

• 1
  type null > db.py
  

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
import sqlite3
from sqlite3 import Error
import os

CURDIR = os.path.dirname(os.path.abspath(__file__))
FILENAME = "schema.sql"
FILE = os.path.join(CURDIR, "db", FILENAME)

def open_db():
    try:
        con = sqlite3.connect('cars.db')
        return con
    except Error as e:
        print('Error: ', e)

def run_query(sql, params='', multiple=False):

    with open_db() as con:
        cursor = con.cursor()
        try:
            if multiple:
                return cursor.executemany(sql, params)
            else:
                return cursor.execute(sql, params)
        except Error as e:
            print('Error: ', e)

def create_schema():
    with open(FILE, 'r') as sql_file:
        sql_script = sql_file.read()
        schema_created = run_query(sql_script)
        if schema_created.rowcount == -1:
            print("Database created successfully")

if __name__ == "__main__":
    create_schema()

Como vemos en primer lugar tenemos que importar el módulo, luego tenemos que definir algunas funciones como:

open_db()

Se encargará de crear o abrir la base de datos envultos en un bloque try/except para manejar posibles errores.

run_query

Esta función va a utilizar la conexión que retorna open_db y con ella podemos realizar consultas a la base de datos.

create_schema()

La función va a construir el esquema de la base de datos que tenemos que crearlo luego en un archivo schema.sql.

• main
• db module
• sql

• main.py

  1
  2
  3
  4
  5
  6
  7
  import db
  
  def init():
      db.create_schema()
  
  if __name__ == "__main__":
      init()
  

• db.py

  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16
  17
  18
  19
  20
  21
  22
  23
  24
  25
  26
  27
  28
  29
  30
  31
  32
  33
  34
  35
  36
  37
  38
  import sqlite3
  from sqlite3 import Error
  import os
  
  CURDIR = os.path.dirname(os.path.abspath(__file__))
  FILENAME = "schema.sql"
  FILE = os.path.join(CURDIR, "db", FILENAME)
  
  def open_db():
      try:
          con = sqlite3.connect('cars.db')
          return con
      except Error as e:
          print('Error: ', e)
  
  def run_query(sql, params='', multiple=False):
  
      with open_db() as con:
          cursor = con.cursor()
          try:
              if multiple:
                  return cursor.executemany(sql, params)
              else:
                  return cursor.execute(sql, params)
          except Error as e:
              print('Error: ', e)
  
  def create_schema():
      with open(FILE, 'r') as sql_file:
          sql_script = sql_file.read()
          schema_created = run_query(sql_script)
          if schema_created.rowcount == -1:
              print("Database created successfully")
  
  if __name__ == "__main__":
      create_schema()
  
  

• db/schema.sql

  1
  2
  3
  4
  CREATE TABLE IF NOT EXISTS cars(
      brand VARCHAR(50) NOT NULL,
      model VARCHAR(12) NOT NULL
  );
  

Insertar Datos

Para comenzar con las operaciones del CRUD, creamos un nuevo archivo crud.py:

• BASH
• CMD

• 1
  touch crud.py
  

• 1
  type null > crud.py
  

Y en el archivo vamos a crear una función que pueda utilizar el módulo db.py para llamar a la función run_query y así insertar algunos datos:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
import db

def insert_data():
  insert_query = "INSERT INTO cars (brand, model) VALUES(?, ?)"
  cars_data = [
      ('Chevrolet', 'Chevrolet Camaro'),
      ('Chevrolet', 'Chevrolet Captiva'),
      ('Fiat', 'Fiat 125 Mirafiori'),
      ('Fiat', 'Fiat 125 Centurion'),
      ('Honda', 'Honda CR-V'),
      ('Honda', 'Honda CR-X del Sol'),
      ('Honda', 'Honda CR-Z')
  ]

  result = db.run_query(insert_query, cars_data, True)
  print("Record inserted successfully into table", result.rowcount)

Ahora, desde el módulo principal main.py importamos al módulo crud.py y ejecutamos la función insert_data:

• main
• crud module
• db module
• resultado

• main.py

  import db
  import crud
  
  def init():
      # db.create_schema()
      crud.insert_data()
  
  if __name__ == "__main__":
      init()

• crud.py

  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16
  17
  18
  import db
  
  def insert_data():
    insert_query = "INSERT INTO cars (brand, model) VALUES(?, ?)"
    cars_data = [
        ('Chevrolet', 'Chevrolet Camaro'),
        ('Chevrolet', 'Chevrolet Captiva'),
        ('Fiat', 'Fiat 125 Mirafiori'),
        ('Fiat', 'Fiat 125 Centurion'),
        ('Honda', 'Honda CR-V'),
        ('Honda', 'Honda CR-X del Sol'),
        ('Honda', 'Honda CR-Z')
    ]
  
    result = db.run_query(insert_query, cars_data, True)
    print("Record inserted successfully into table", result.rowcount)
  
  

• db.py

  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16
  17
  18
  19
  20
  21
  22
  23
  24
  25
  26
  27
  28
  29
  30
  31
  32
  33
  34
  35
  36
  37
  38
  import sqlite3
  from sqlite3 import Error
  import os
  
  CURDIR = os.path.dirname(os.path.abspath(__file__))
  FILENAME = "schema.sql"
  FILE = os.path.join(CURDIR, "db", FILENAME)
  
  def open_db():
      try:
          con = sqlite3.connect('cars.db')
          return con
      except Error as e:
          print('Error: ', e)
  
  def run_query(sql, params='', multiple=False):
  
      with open_db() as con:
          cursor = con.cursor()
          try:
              if multiple:
                  return cursor.executemany(sql, params)
              else:
                  return cursor.execute(sql, params)
          except Error as e:
              print('Error: ', e)
  
  def create_schema():
      with open(FILE, 'r') as sql_file:
          sql_script = sql_file.read()
          schema_created = run_query(sql_script)
          if schema_created.rowcount == -1:
              print("Database created successfully")
  
  if __name__ == "__main__":
      create_schema()
  
  

• 1
  Record inserted successfully into table 7
  

Consultado los datos de la tabla

Recuperamos los datos insertados anteriormente:

• main
• crud module
• db module
• resultado

• main.py

  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  import db
  import crud
  
  def init():
      # db.create_schema()
      # crud.insert_data()
      crud.get_data()
  
  if __name__ == "__main__":
      init()
  

• crud.py

  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16
  17
  18
  19
  20
  21
  22
  23
  24
  import db
  from prettytable import from_db_cursor # pip install prettytable
  
  def get_data():
    select_query = "SELECT * FROM cars"
    result = db.run_query(select_query)
    mytable = from_db_cursor(result)
    mytable.align = "l"
    print(mytable)
  
  def insert_data():
    insert_query = "INSERT INTO cars (brand, model) VALUES(?, ?)"
    cars_data = [
        ('Chevrolet', 'Chevrolet Camaro'),
        ('Chevrolet', 'Chevrolet Captiva'),
        ('Fiat', 'Fiat 125 Mirafiori'),
        ('Fiat', 'Fiat 125 Centurion'),
        ('Honda', 'Honda CR-V'),
        ('Honda', 'Honda CR-X del Sol'),
        ('Honda', 'Honda CR-Z')
    ]
  
    result = db.run_query(insert_query, cars_data, True)
    print("Record inserted successfully into table", result.rowcount)
  

• db.py

  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16
  17
  18
  19
  20
  21
  22
  23
  24
  25
  26
  27
  28
  29
  30
  31
  import sqlite3
  from sqlite3 import Error
  
  def open_db():
      try:
          con = sqlite3.connect('cars.db')
          return con
  
      except Error as e:
          print('Error: ', e)
  
  def run_query(sql, params='', multiple=False):
      with open_db() as con:
          cursor = con.cursor()
          try:
              if multiple:
                  return cursor.executemany(sql, params)
              else:
                  return cursor.execute(sql, params)
          except Error as e:
              print('Error: ', e)
  
  def create_schema():
      with open('db/schema.sql', 'r') as sql_file:
          sql_script = sql_file.read()
          schema_created = run_query(sql_script)
          if schema_created.rowcount == -1:
              print("Database created successfully")
  
  if __name__ == "__main__":
      create_schema()
  

• 1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  +-------+-----------+--------------------+
  | rowid | brand     | model              |
  +-------+-----------+--------------------+
  | 1     | Chevrolet | Chevrolet Camaro   |
  | 2     | Chevrolet | Chevrolet Captiva  |
  | 3     | Fiat      | Fiat 125 Mirafiori |
  | 4     | Fiat      | Fiat 125 Centurion |
  | 5     | Honda     | Honda CR-V         |
  | 6     | Honda     | Honda CR-X del Sol |
  | 7     | Honda     | Honda CR-Z         |
  +-------+-----------+--------------------+
  

prettytable es una librería de Python que da formato de tabla a los datos por consola.

Actualización de datos

Podemos actualizar los modelos de autos:

• main
• crud module
• db module
• resultado

• main.py

  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  import db
  import crud
  
  def init():
      # db.create_schema()
      # crud.insert_data()
      crud.update_data("CR-V", "HR-V")
      crud.get_data()
  
  if __name__ == "__main__":
      init()
  

• crud.py

  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  import db
  from prettytable import from_db_cursor # pip install prettytable
  
  def update_data(model, newModel):
    update_query = "UPDATE cars SET model = ? WHERE model = ?"
    db.run_query(update_query, (newModel, model))
  
  def get_data():
    select_query = "SELECT * FROM cars"
    result = db.run_query(select_query)
    mytable = from_db_cursor(result)
    mytable.align = "l"
    print(mytable)
  

• db.py

  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16
  17
  18
  19
  20
  21
  22
  23
  24
  25
  26
  27
  28
  29
  30
  31
  import sqlite3
  from sqlite3 import Error
  
  def open_db():
      try:
          con = sqlite3.connect('cars.db')
          return con
  
      except Error as e:
          print('Error: ', e)
  
  def run_query(sql, params='', multiple=False):
      with open_db() as con:
          cursor = con.cursor()
          try:
              if multiple:
                  return cursor.executemany(sql, params)
              else:
                  return cursor.execute(sql, params)
          except Error as e:
              print('Error: ', e)
  
  def create_schema():
      with open('db/schema.sql', 'r') as sql_file:
          sql_script = sql_file.read()
          schema_created = run_query(sql_script)
          if schema_created.rowcount == -1:
              print("Database created successfully")
  
  if __name__ == "__main__":
      create_schema()
  

• 1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  +-------+-----------+---------------+
  | rowid | brand     | model         |
  +-------+-----------+---------------+
  | 1     | Chevrolet | Camaro        |
  | 2     | Chevrolet | Captiva       |
  | 3     | Fiat      | Mirafiori     |
  | 4     | Fiat      | 125 Centurion |
  | 5     | Honda     | HR-V          |
  | 6     | Honda     | CR-X del Sol  |
  | 7     | Honda     | CR-Z          |
  +-------+-----------+---------------+
  

Eliminar datos

Eliminar datos desde la tabla:

• main
• crud module
• db module
• resultado

• main.py

  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  import db
  import crud
  
  def init():
      # db.create_schema()
      # crud.insert_data()
      # crud.update_data("CR-V", "HR-V")
      crud.delete_data("Camaro")
      crud.get_data()
  
  if __name__ == "__main__":
      init()
  

• crud.py

  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  import db
  from prettytable import from_db_cursor # pip install prettytable
  
  def get_data():
    select_query = "SELECT * FROM cars"
    result = db.run_query(select_query)
    mytable = from_db_cursor(result)
    mytable.align = "l"
    print(mytable)
  
  def delete_data(model):
    delete_query = "DELETE FROM cars WHERE model = ?"
    db.run_query(delete_query, (model,))
  

• db.py

  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16
  17
  18
  19
  20
  21
  22
  23
  24
  25
  26
  27
  28
  29
  30
  31
  import sqlite3
  from sqlite3 import Error
  
  def open_db():
      try:
          con = sqlite3.connect('cars.db')
          return con
  
      except Error as e:
          print('Error: ', e)
  
  def run_query(sql, params='', multiple=False):
      with open_db() as con:
          cursor = con.cursor()
          try:
              if multiple:
                  return cursor.executemany(sql, params)
              else:
                  return cursor.execute(sql, params)
          except Error as e:
              print('Error: ', e)
  
  def create_schema():
      with open('db/schema.sql', 'r') as sql_file:
          sql_script = sql_file.read()
          schema_created = run_query(sql_script)
          if schema_created.rowcount == -1:
              print("Database created successfully")
  
  if __name__ == "__main__":
      create_schema()
  

• 1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  +-------+-----------+---------------+
  | rowid | brand     | model         |
  +-------+-----------+---------------+
  | 2     | Chevrolet | Captiva       |
  | 3     | Fiat      | Mirafiori     |
  | 4     | Fiat      | 125 Centurion |
  | 5     | Honda     | HR-V          |
  | 6     | Honda     | CR-X del Sol  |
  | 7     | Honda     | CR-Z          |
  +-------+-----------+---------------+