Database Management with Python

• Study notes on data base management in Python

Database Management in Python

• In the current information age, data needs to be managed efficiently.
• Database Management Systems (DBMS) are software systems used to create, store, modify, retrieve, and manage data.
• DBMS systems range in scale from personal computers to large mainframes.
• Python is well-suited for interacting with databases.

Python Database API

• Python uses a specific API to interact with databases, allowing programming of different DBMS.
• The process for different DBMS remains consistent at the code level.
• Establish connection to a database.
• Create a cursor to communicate with the data.
• Manipulate data using SQL.
• Apply or revert SQL manipulations to the data.
• Close the database connection.

SQLite

• SQLite is a lightweight, fully functional, and autonomous SQL-based database management system.
• It requires minimal external library support without needing a server.
• It uses a local data storage.
• SQLite requires no configurations.
• It allows for executing SQL queries on SQLite tables and storing into it.
• SQLite is used by Google, Apple, and Microsoft.
• SQLite module sqlite3 is used to interact with databases in Python.

Connecting to a Database

• Connect to a database by creating a Connection object representing the database.
• Example: conn = sqlite3.connect('company.db')
• The database will be stored in the 'company.db' file.

Creating a Cursor

• Create a cursor to communicate with the database: -curs = conn.cursor()

Data Manipulation

• SQL commands can be executed once connected to the database and a cursor is created.
• To create a new table named "employee" woth columns "name" and "age":
  • curs.execute('create table employee(name, age)')
• Data can be inserted via curs.execute("insert into employee values ('Ali', 28)").
• Multiple values can be inserted at once using the executemany() method.
• Example: Execute multiple values -values = [('Brad',54), ('Ross', 34), ('Muhammad', 28), ('Bilal', 44)] -curs.executemany('insert into employee values(?,?)', values)
• Changes need to be validated using conn.commit().
• The connection should be closed after all operations and validation using conn.close().

Complete Script

• A script is provided that connects to a database, creates a table, inserts some values, commits the change then closes the connection.

 import sqlite3
 conn = sqlite3.connect('company.db')
 curs = conn.cursor()
 curs.execute('create table employee (name, age)')
 curs.execute("insert into employee values ('Ali', 28)")
 values = [('Brad',54), ('Ross', 34), ('Muhammad', 28), ('Bilal', 44)]
 curs.executemany('insert into employee values(?,?)', values)
 conn.commit()
 conn.close()

• Execution will create a file named “company.db.’

Examining the Database

• The DB Browser for SQLite may be used to view the company.db file.
• Via the user interface:
• Use the "Open Database" button to open the database.
• The structure of the database can then be explored.
• The employee table with fields name and age can be confirmed.
• The data that have been entered in the tables is accessed via the "Examine" button.
• After this has been completed, a database (company) is created, and an “employee” table has been added.

Basic SQL Queries

• First steps involve importing the module, connecting to the database, and disconnecting.

import sqlite3
connection = sqlite3.connect('ejemplo.db')
connection.close()

• Create a table before running query statements.

import sqlite3
conexion = sqlite3.connect('ejemplo.db')
cursor = conexion.cursor()
cursor.execute("CREATE TABLE IF NOT EXISTS usuarios " \
"(nombre VARCHAR(100), edad INTEGER, email VARCHAR(100))")
conexion.commit()
conexion.close()

Inserting with INSERT

import sqlite3
conexion = sqlite3.connect('ejemplo.db')
cursor = conexion.cursor()
cursor.execute("INSERT INTO usuarios VALUES " \
"('Hector', 27, '[email protected]')")
conexion.commit()
conexion.close()

Reading with SELECT

• The first record can now be selected

import sqlite3
conexion = sqlite3.connect('ejemplo.db')
cursor = conexion.cursor()
cursor.execute("SELECT * FROM usuarios")
print(cursor)
usuario = cursor.fetchone()
print(usuario)
conexion.close()

Multiple Insertion

• Multiple records are insterted with .executemany():

import sqlite3
conexion = sqlite3.connect('ejemplo.db')
cursor = conexion.cursor()
usuarios = [('Mario', 51, '[email protected]'),
('Mercedes', 38, '[email protected]'),
('Juan', 19, '[email protected]')]
cursor.executemany("INSERT INTO usuarios VALUES (?,?,?)", usuarios)
conexion.commit()
conexion.close()

Multiple Read

• Multiple records are retrieved via .fetchall():

import sqlite3
conexion = sqlite3.connect('ejemplo.db')
cursor = conexion.cursor()
cursor.execute("SELECT * FROM usuarios")
usuarios = cursor.fetchall()
for usuario in usuarios:
print(usuario)
conexion.close()

Basic SQL Keys

• Primary keys act as unique identifiers for records in a table, ensuring no duplication.

import sqlite3
conexion = sqlite3.connect('usuarios.db')
cursor = conexion.cursor()
cursor.execute("CREATE TABLE IF NOT EXISTS usuarios ( dni VARCHAR(9) PRIMARY KEY, nombre VARCHAR(100), edad INTEGER, email VARCHAR(100))")
usuarios = [('11111111A', 'Hector', 27, '[email protected]'), ('22222222B', 'Mario', 51, '[email protected]'), ('33333333C', 'Mercedes', 38, '[email protected]'), ('44444444D', 'Juan', 19, '[email protected]')]
cursor.executemany("INSERT INTO usuarios VALUES (?,?,?,?)", usuarios)
conexion.commit()
conexion.close()

• Attempting to insert a duplicated record results will cause an error.

import sqlite3
conexion = sqlite3.connect('usuarios.db')
cursor = conexion.cursor()
cursor.execute("INSERT INTO usuarios VALUES " \
"('11111111A', 'Fernando', 31, '[email protected]')")
conexion.commit()
conexion.close()

Autoincrementing Keys

• Autoincrementing fields automatically assign a unique number when a new data is created.
• In SQLite if a numeric field is selected as the primary key, it is automatically sets said field to auto-increment.

import sqlite3
conexion = sqlite3.connect('productos.db')
cursor = conexion.cursor()
cursor.execute("CREATE TABLE IF NOT EXISTS productos ( id INTEGER PRIMARY KEY AUTOINCREMENT, nombre VARCHAR(100) NOT NULL, marca VARCHAR(50) NOT NULL, precio FLOAT NOT NULL)")
conexion.close()

Records Insertion

Field Auto Increment

• Inserting records with auto-incrementing fields requires specifying null.

import sqlite3
conexion = sqlite3.connect('productos.db')
cursor = conexion.cursor()
productos = [('Teclado', 'Logitech', 19.95), ('Pantalla 19', 'LG', 89.95), ('Altavoces 2.1','LG', 24.95),]
cursor.executemany("INSERT INTO productos VALUES (null,?,?,?)", productos)
conexion. commit()
conexion.close()

Retrieving Records

• The identifiers are now unique.

import sqlite3
conexion = sqlite3.connect('productos.db')
cursor = conexion.cursor()
cursor.execute("SELECT * FROM productos")
productos = cursor.fetchall()
for producto in productos:
print(producto)
conexion.close()

Unique Keys

• Unique keys prevent repeated entries on other fields other the.
• Adapting the previous example.

import sqlite3
conexion = sqlite3.connect('usuarios_autoincremental.db')
cursor = conexion.cursor()
cursor.execute("CREATE TABLE IF NOT EXISTS usuarios ( id INTEGER PRIMARY KEY, dni VARCHAR(9) UNIQUE, nombre VARCHAR(100), edad INTEGER(3), email VARCHAR(100))")
usuarios = [('11111111A', 'Hector', 27, '[email protected]'), ('22222222B', 'Mario', 51, '[email protected]'), ('33333333C', 'Mercedes', 38, '[email protected]'), ('44444444D', 'Juan', 19, '[email protected]')]
cursor.executemany("INSERT INTO usuarios VALUES (null, ?,?,?,?)", usuarios)
conexion.commit()
conexion.close()

DNI Integrity error

• Integrity error comes from adding a user wit an existing key.

import sqlite3
conexion = sqlite3.connect('usuarios_autoincremental.db')
cursor = conexion.cursor()
cursor.execute("INSERT INTO usuarios VALUES " \
"( null, '11111111A', 'Fernando', 31, '[email protected]')")
conexion.commit()
conexion.close()

Identifiers from the previous code

• This has an advantage of an automatic identifier with each record.

import sqlite3
conexion = sqlite3.connect('usuarios_autoincremental.db')
cursor = conexion.cursor()
cursor.execute("SELECT * FROM usuarios")
usuarios = cursor.fetchall()
for usuario in usuarios:
    print(usuario)
conexion.close()