Python uses SQLAlchemy to operate sqlite

sqllite
- 1. Introduction to SQLite
- 2. Install SQLite on Windows
- 3. Create a database using SQLite
- - 3.1 Create database from command line
  - 3.2 navicat connects to the database
- 4.SQLite data types
- - storage class
  - SQLite Affinity type
  - Boolean data type
  - Date and Time data types
- 5. Commonly used sql syntax
- - **CREATE TABLE**
  - **INSERT INTO**
  - **Query data (SELECT)**
  - **Update data (UPDATE)**
  - **DELETE**
  - **SQLite Glob clause**
  - **SQLite Limit clause**
  - **SQLite Order By clause**
  - **SQLite Group By clause**
  - **SQLite Having clause**
  - **SQLite Distinct keyword**
  - Aggregation functions (SUM, AVG, COUNT, MAX, MIN)
  - **Join Query (JOIN)**
  - **CREATE VIEW**
  - **CREATE INDEX**
- 6.Python uses SQLAlchemy to operate sqlite
- - 6.1 Install SQLAlchemy
  - 6.2 Create implementation script

sqllite

1. Introduction to SQLite

SQLite (Structured Query Language – Lite) is a lightweight embedded relational database management system (RDBMS). Here is some introduction to SQLite:

Lightweight: SQLite is a lightweight database engine, less than 400KiB when fully configured, and less than 250KiB when optional features are omitted.
Embedded Database: SQLite is an embedded database, which means it can be embedded into an application without the need for a separate database server. This makes it ideal for embedding into mobile apps, desktop apps, and other small projects.
Zero configuration: SQLite requires no server process or configuration files. You just create a database file and can use it directly in your application.
Full ACID Compatible: SQLite transactions are fully ACID compliant, allowing safe access from multiple processes or threads.
Supports standard SQL syntax: SQLite supports most standard SQL syntax, so you can use common SQL queries and operation statements.
Transaction support: SQLite provides support for transactions, which is very important to ensure the consistency and reliability of the database.
Cross-platform: SQLite can run on multiple operating systems, including Windows, Linux, macOS, etc.
Serverless Architecture: Unlike traditional client-server database management systems, SQLite does not have a separate server process. The database engine is embedded directly into the application.
Self-Contained: A SQLite database is a single, independent file that contains the entire database structure and data. This makes database transfer and backup relatively simple.

2. Install SQLite on Windows

Enter the official website to download: SQLite Download Page

Unzip it to the specified folder. After unzipping, it is as follows
Configure environment variables: Add E:\install\sqllite3.44.0 to the PATH environment variable, and finally use the sqlite3 command at the command prompt. The following results will be displayed.

3. Create a database using SQLite

3.1 Create database using command line

# Syntax: .open [path + database name]
.open E:\install\sqllite3.44.0\db\zhouquantest.db

3.2 navicat connects to the database

4.SQLite data type

Storage class

value is a NULL value.

Storage class	Field description
NULL
INTEGER	The value is a signed integer stored in 1, 2, 3, 4, 6 or 8 bytes depending on the size of the value .
REAL	The value is a floating-point value stored as an 8-byte IEEE floating-point number.
TEXT	The value is a text string stored using the database encoding (UTF-8, UTF-16BE, or UTF-16LE).
BLOB	A value is a blob of data stored entirely based on its input.

SQLite Affinity type

SQLite supports the concept of type affinity on columns. Any column can still store any type of data, but a column’s preferred storage class is its affinity. In a SQLite3 database, each table’s columns are assigned one of the following types of affinity:

Affinity	Description
TEXT	This column uses the storage class NULL, TEXT or BLOB to store all data.
NUMERIC	This column can contain values using all five storage classes.
INTEGER	Same as column with NUMERIC affinity, in CAST expression With exceptions.
REAL	Similar to columns with NUMERIC affinity, except that it Will force conversion of integer values to floating point representation.
NONE	Columns with affinity NONE will not give priority to which storage class is used , nor does it attempt to coerce data from one storage class to another.

Boolean data type

SQLite does not have a separate Boolean storage class. Instead, Boolean values are stored as integers 0 (false) and 1 (true).

Date and Time data types

SQLite does not have a separate storage class for storing dates and/or times, but SQLite can store dates and times as TEXT, REAL, or INTEGER values.

Storage class	Date format
TEXT	A date in the format of “YYYY-MM-DD HH:MM:SS.SSS”.
REAL	Start at noon GMT on November 24, 4714 BC The number of days to start.
INTEGER	Start from 1970-01-01 00:00:00 UTC seconds.

You can store dates and times in any of the above formats, and you can freely convert between formats using built-in date and time functions.

5. Commonly used SQL syntax

CREATE TABLE

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    age INTEGER,
    department TEXT
);

INSERT INTO

INSERT INTO employees (name, age, department)
VALUES ('Cai Xukun', 30, 'dev');

INSERT INTO employees (name, age, department)
VALUES ('Chicken Brother', 28, 'net');

Query data (SELECT)

Query all columns:
```
SELECT * FROM employees;
```
Query specific columns:
```
SELECT name, age FROM employees;
```

Use conditional query:

SELECT * FROM employees WHERE department = 'net';

Use wildcard query:

SELECT * FROM employees WHERE name LIKE 'Cai%';

UPDATE

UPDATE employees
SET age = 31
WHERE name = 'Cai Xukun';

DELETE

DELETE FROM employees
WHERE name = 'Chicken Brother';

SQLite Glob Clause

The Glob clause is used to perform string comparison based on pattern matching.

SELECT * FROM employees
WHERE name GLOB 'Cai*';

SQLite Limit Clause

The LIMIT clause is used to limit the number of rows in query results.

SELECT * FROM employees
LIMIT 5;

SQLite Order By clause

The ORDER BY clause is used to sort query results.

SELECT * FROM employees
ORDER BY age DESC;

SQLite Group By clause

The GROUP BY clause is used to group query results.

SELECT department, AVG(age) as avg_age
FROM employees
GROUP BY department;

SQLite Having clause

The HAVING clause is used to filter groups in the GROUP BY clause.

SELECT department, AVG(age) as avg_age
FROM employees
GROUP BY department
HAVING AVG(age) > 30;

SQLite Distinct Keyword

The DISTINCT keyword is used to return unique values and remove duplicates.

SELECT DISTINCT department FROM employees;

Aggregation functions (SUM, AVG, COUNT, MAX, MIN)

Calculate age:

SELECT AVG(age),MAX(age),MIN(age) FROM employees;

Calculate department headcount:

SELECT department, COUNT(*) as num_employees
FROM employees
GROUP BY department;

Joint Query (JOIN)

-- Create department table
CREATE TABLE departments (
    id INTEGER PRIMARY KEY,
    short_name TEXT,
    name TEXT
);

--Insert data
INSERT INTO departments (short_name,name)
VALUES ('dev','Development Department'), ('net','Network Department');

-- Related query
SELECT employees.name, employees.age, departments.name as department
FROM employees
JOIN departments ON employees.department = departments.short_name;

CREATE VIEW

CREATE VIEW view_employee_summary AS
SELECT department, AVG(age) as avg_age, COUNT(*) as num_employees
FROM employees
GROUP BY department;

CREATE INDEX

CREATE INDEX idx_department ON employees (department);

6.Python uses SQLAlchemy to operate sqlite

Create a python project

6.1 Install SQLAlchemy

SQLAlchemy is a powerful SQL toolkit and object-relational mapping (ORM) library that allows easier interaction with databases in Python

Install using the following command:

pip install SQLAlchemy

6.2 Create implementation script

from sqlalchemy import create_engine, Column, Integer, String, Sequence
from sqlalchemy.orm import declarative_base, sessionmaker

#Create a SQLite database engine
# sqlite:/// represents the SQLite database connection protocol
# students.db is the name of the SQLite database file
# echo=True prints SQL statements to the console
engine = create_engine('sqlite:///students.db', echo=True)

#Create a base class for declaring class definitions
Base = declarative_base()


#Define Student class
class Student(Base):
    __tablename__ = 'students'

    id = Column(Integer, Sequence('student_id_seq'), primary_key=True)
    name = Column(String(50), nullable=False)
    age = Column(Integer)
    grade = Column(String(10))


# Create "students" table
Base.metadata.create_all(engine)

#Create a Session class for database interaction
Session = sessionmaker(bind=engine)
session = Session()

# Add
student1 = Student(id=1, name='Xiao Ming', age=20, grade='A')
student2 = Student(id=2, name='Xiaohong', age=22, grade='B')
student3 = Student(id=3, name='小菜', age=21, grade='B')
student4 = Student(id=4, name='Xiaokun', age=23, grade='A')

# If you need to add new ones, uncomment the following two lines.
# session.add_all([student1, student2, student3, student4])
# session.commit()

# Query and print
print("All students:")
students = session.query(Student).all()
for students in students:
    print(f"ID: {<!-- -->student.id}, Name: {<!-- -->student.name}, Age: {<!-- -->student.age}, Grade: {<!-- -->student.grade}")

# Query specified students
specific_student = session.query(Student).filter_by(name='Xiao Ming').first()
if specific_student:
    print(f"\\
Specific student: ID: {<!-- -->specific_student.id}, Name: {<!-- -->specific_student.name},"
          f"Age: {<!-- -->specific_student.age}, Grade: {<!-- -->specific_student.grade}")
else:
    print("\\
Specific student not found")

# Update student’s grades
update_student = session.query(Student).filter_by(name='小红').first()
if update_student:
    update_student.grade = 'A'
    session.commit()
    print("\\
The results have been updated")
else:
    print("\\
Student not found")

# Delete students
delete_student = session.query(Student).filter_by(name='Xiao Ming').first()
if delete_student:
    session.delete(delete_student)
    session.commit()
    print("\\
Student has been deleted")
else:
    print("\\
Student not found")

# Close Session
session.close()