SQL in Python
✕Database and SQL
- Relational Database: Organized collection of tables.
- Table: Collection of related data organized in rows(
record) and columns(field). - Analogy:
Table=>Excel Sheet,Database=>Excel Workbook. - Better performance, data integrity and security as compared to storing in files.
- RDBMS: Software that manages relational databases.
- Common RDBMS: MySQL, PostgreSQL, SQLite, Oracle, SQL Server (MS-SQL).
- SQL => Structured Query Language
- SQL: Code for searching, adding, modifying and removing data stored in table.
- DBeaver: Free tool that is used to Query various databases. Download from here and install.
Creating Database
- We will be using SQLite database.
- SQLite is a lightweight, file-based database that is easy to set up and use.
- It is a good choice for small to medium-sized applications and for learning SQL.
- Create a file
database.dbin your project folder. This will be our SQLite database file. - Click on
socketlike icon just below File. (Shortcut:ctrl + shift + N). - Click SQLite and click Next.
- Cick on
Open...and selectdatabase.dbfile created in previous step. - Click
Test Connection. Note: For First Time Driver will be downloaded. - Once connection is successful, click Finish to connect to database.
- Right click ondatabase name => SQL Editor => New SQL Script
- This opens SQL editor where we can write SQL queries.
Connecting Database in DBeaver
Sample Table for Mart
- Sample table
salesandemployeesfor a retail store:
| id | txn_date | customer_name | product_name | quantity | price |
|---|---|---|---|---|---|
| 1 | 2023-01-01 | Alice | Laptop | 1 | 999.99 |
| 2 | 2023-01-02 | Bob | Smartphone | 2 | 499.99 |
| 3 | 2023-01-03 | Charlie | Headphones | 3 | 199.99 |
Sample Sales Table for Retail Store
| id | name | salary | department |
|---|---|---|---|
| 1 | John Doe | 50000.00 | Sales |
| 2 | Jane Smith | 60000.00 | Marketing |
| 3 | Bob Johnson | 55000.00 | IT |
Sample Employees Table for Organization
Creating Tables
CREATE TABLE table_name ( column1 datatype, column2 datatype, ... );- Common DataTypes:
INTEGER,REAL,TEXT,BLOB. CREATE TABLE employees ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, salary REAL );
Syntax:
Example:
Inserting Data
- Data is added in table with
INSERT INTOstatement. - Syntax:
INSERT INTO table_name (column1, column2, ...) VALUES (value_1_1, value_1_2, ...), (value_2_1, value_2_2, ...), ... (value_n_1, value_n_2, ...); INSERT INTO employees (name, salary) VALUES ("Alice", 70000), ("Charlie", 60000);- Validate:
SELECT * FROM employees;
Example:
Updating and Deleting Data
- Used to modify existing records in a table.
- Syntax:
UPDATE table_name SET col_1 = val_1, col_2 = val_2, ... , column_n = val_n WHERE condition; - Example:
UPDATE employees SET salary = 75000 WHERE name = "Alice"; - Used to remove records from a table.
- Syntax:
DELETE FROM table_name WHERE condition; - Example:
DELETE FROM employees WHERE name = "Charlie";
UPDATE
DELETE
Retrieving Data with SELECT
- Data is retrieved from table with
SELECTstatement. - Syntax:
SELECT column1, column2, ... FROM table_name WHERE condition ORDER BY column ASC|DESC LIMIT number; SELECT * FROM employees;SELECT name, salary FROM employees;SELECT * FROM employees LIMIT 10;SELECT name, salary FROM employees ORDER BY salary DESC LIMIT 5;
Example:
Filter Conditions with WHERE Clause
- WHERE clause is used to filter records based on specified conditions.
- Operators:
=,!=,<,>,<=,>=,LIKE,IN,BETWEEN. SELECT * FROM employees WHERE salary > 60000;SELECT * FROM employees WHERE department != "HR";SELECT * FROM employees WHERE salary >= 50000 AND salary < 80000;SELECT * FROM employees WHERE salary BETWEEN 50000 AND 80000;SELECT * FROM employees WHERE name = "Alice" OR name = "Bob";SELECT * FROM employees WHERE name IN ("Alice", "Bob");SELECT * FROM employees WHERE name LIKE "A%";SELECT * FROM employees WHERE name LIKE "%e";- NOTE: Functions like
LOWER,UPPER,LENGTH,ROUND,REPLACE,ABS,TRIM,CONCAT,IFNULLetc. are available in SQL.
Example:
Read SQL table in Python
- We can use
sqlite3library to connect and query SQLite database in Python. - In-built library, no need to install.
import sqlite3 db_path = r"path_to_your_db.db" conn = sqlite3.connect(db_path) # conn.row_factory = sqlite3.Row (for dict) cursor = conn.cursor() cursor.execute("SELECT * FROM employees;") results = cursor.fetchall() print(results) cursor.close() conn.close()
Example:
Write SQL table from Python
import sqlite3 con=sqlite3.connect(r"path_to_your_db.db") cursor=con.cursor() # Create table cursor.execute("CREATE TABLE IF NOT EXISTS products (name TEXT, price REAL);") # Insert data data_to_add = [("Laptop", 999.99), ("Smartphone", 499.99), ("Headphones", 199.99)] cursor.executemany("INSERT INTO products (name, price) VALUES (?, ?);", data_to_add) con.commit() cursor.close() con.close()
Example: