Jak stworzyć bazę danych SQLite oraz jak korzystać z niej w Pythonie?

1. Stworzenie bazy danych SQLite w terminalu: Aby stworzyć bazę danych SQLite, potrzebujesz uruchomić polecenie sqlite3 w terminalu. Wykonaj następujące kroki: Otwórz Terminal i wpisz:

   sqlite3 mydatabase.db

Gdzie mydatabase.db to nazwa pliku, w którym chcesz przechowywać bazę danych. Jeśli ten plik nie istnieje, SQLite stworzy go dla Ciebie.

Po wprowadzeniu tego polecenia, powinieneś zobaczyć znak zachęty sqlite> oznaczający, że jesteś w trybie interaktywnej konsoli SQLite.

2. Stworzenie tabeli: W trybie interaktywnej konsoli SQLite możesz stworzyć tabelę za pomocą polecenia CREATE TABLE. Przykład tworzenia tabeli “users” z kolumnami “id” i “name”:

   CREATE TABLE users (
       id INTEGER PRIMARY KEY,
       name TEXT
   );

3. Zamknięcie konsoli SQLite: Aby zakończyć pracę w konsoli SQLite, wprowadź .exit lub naciśnij klawisze Ctrl + D.
4. Korzystanie z bazy danych w Pythonie: Po utworzeniu bazy danych SQLite, możesz korzystać z niej w swoim kodzie Pythona. Oto przykład, jak otworzyć połączenie do tej bazy w kodzie i wykonać zapytanie:

   import sqlite3

   # Otwarcie połączenia do bazy danych
   connection = sqlite3.connect('mydatabase.db')
   cursor = connection.cursor()

   # Przykładowe zapytanie
   cursor.execute("SELECT * FROM users")
   rows = cursor.fetchall()

   # Wyświetlenie wyników zapytania
   for row in rows:
       print(row)

   # Zamknięcie połączenia
   connection.close()

Upewnij się, że Twój skrypt Pythona znajduje się w tym samym katalogu, w którym znajduje się plik bazy danych “mydatabase.db”. Jeśli chcesz użyć innego katalogu, podaj pełną ścieżkę do pliku przy otwieraniu połączenia.

Bardziej szczegółowy opis jak używać w Flasku biblioteki SQLite3:

1. Importuj bibliotekę SQLite3:

import sqlite3

2. Utwórz połączenie z bazą danych:

conn = sqlite3.connect('baza.db')

3. Utwórz kursor:

c = conn.cursor()

4. Wykonaj zapytanie SQL:

c.execute("CREATE TABLE czujnik (temp REAL, hum REAL, bat REAL)")

5. Zatwierdź zmiany w bazie danych:

conn.commit()

6. Zamknij połączenie z bazą danych:

conn.close()

Oto kilka najważniejszych zapytań SQL, których można użyć przy obsłudze bazy SQLite3 w Flasku:

1. Tworzenie tabeli:

c.execute("CREATE TABLE nazwa_tabeli (kolumna1 typ_danych1, kolumna2 typ_danych2, ...)")

2. Dodawanie danych do tabeli:

c.execute("INSERT INTO nazwa_tabeli (kolumna1, kolumna2, ...) VALUES (wartosc1, wartosc2, ...)")

3. Wyświetlanie wszystkich rekordów z tabeli:

c.execute("SELECT * FROM nazwa_tabeli")

4. Wyświetlanie wybranych kolumn z tabeli:

c.execute("SELECT kolumna1, kolumna2 FROM nazwa_tabeli")

5. Aktualizowanie danych w tabeli:

c.execute("UPDATE nazwa_tabeli SET kolumna1 = nowa_wartosc WHERE warunek")

6. Usuwanie danych z tabeli:

c.execute("DELETE FROM nazwa_tabeli WHERE warunek")

INDEKSOWANIE w SQLite3

Oto funkcja która kreuje bazę i tworzy tabelę oraz dwa indeksy:

DATABASE = 'mydatabase.db'

def create_table():
    conn = sqlite3.connect(DATABASE)
    cursor = conn.cursor()
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS sensor_data (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            device_name TEXT NOT NULL,
            data TEXT NOT NULL,
            timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
        )
    ''')
    cursor.execute('''
        CREATE INDEX IF NOT EXISTS idx_device_name ON sensor_data (device_name);
    ''')
    cursor.execute('''
        CREATE INDEX IF NOT EXISTS idx_timestamp ON sensor_data (timestamp);
    ''')
    conn.commit()
    conn.close()

Funkcja create_table() jest przeznaczona do tworzenia tabeli w bazie danych SQLite. Oto jej działanie krok po kroku:

1. Otwieranie połączenia z bazą danych:
   Funkcja rozpoczyna od otwarcia połączenia z bazą danych. Wartość DATABASE to prawdopodobnie zmienna zawierająca nazwę pliku bazy danych lub ścieżkę do tego pliku. Polecenie sqlite3.connect(DATABASE) tworzy połączenie z bazą danych.
2. Tworzenie kursora:
   Kursor to obiekt, który służy do wykonywania poleceń SQL w bazie danych. Tworzymy kursor za pomocą cursor = conn.cursor().
3. Tworzenie tabeli sensor_data:
   Funkcja wykonuje polecenie CREATE TABLE IF NOT EXISTS, które tworzy tabelę sensor_data, jeśli taka tabela jeszcze nie istnieje. Tabela ma następujące kolumny:

• id – Unikalny identyfikator (klucz główny), który jest automatycznie generowany (AUTOINCREMENT).
• device_name – Tekstowa kolumna przechowująca nazwę urządzenia.
• data – Tekstowa kolumna przechowująca dane z urządzenia.
• timestamp – Data i czas, kiedy dane zostały dodane. Ma domyślną wartość CURRENT_TIMESTAMP, co oznacza, że automatycznie zostanie wypełniona datą i czasem w momencie dodawania rekordu.

1. Tworzenie indeksów:
   Funkcja następnie tworzy dwa indeksy na tabeli sensor_data, aby przyspieszyć zapytania wyszukujące. Indeksy są tworzone przy użyciu polecenia CREATE INDEX IF NOT EXISTS.

• idx_device_name – Indeks na kolumnie device_name, co pozwala na szybkie wyszukiwanie danych na podstawie nazwy urządzenia.
• idx_timestamp – Indeks na kolumnie timestamp, co umożliwia szybkie sortowanie i wyszukiwanie danych według czasu.

1. Commit i zamknięcie połączenia:
   Po wykonaniu poleceń SQL, funkcja wywołuje conn.commit(), aby zatwierdzić wprowadzone zmiany w bazie danych, a następnie zamyka połączenie z bazą danych za pomocą conn.close().

Używanie indeksów

Aby używać indeksów w swojej bazie danych SQLite, musisz najpierw utworzyć indeksy na odpowiednich kolumnach. Wcześniejsza odpowiedź zawierała przykład tworzenia indeksów w funkcji create_table(). Teraz przedstawimy bardziej szczegółowe informacje na ten temat:

1. Tworzenie indeksów:
   Indeksy są tworzone przy użyciu polecenia CREATE INDEX. Możesz dodać indeks do odpowiednich kolumn w istniejącej tabeli. Oto ogólna składnia:

   CREATE INDEX nazwa_indeksu ON nazwa_tabeli (kolumna1, kolumna2, ...);

Na przykład, aby dodać indeks na kolumnie “device_name” w tabeli “sensor_data”, wpisz:

   CREATE INDEX idx_device_name ON sensor_data (device_name);

Indeksy mogą być tworzone zarówno podczas tworzenia tabeli, jak i po jej utworzeniu.

2. Usuwanie indeksów:
   Jeśli z jakiegoś powodu chcesz usunąć indeks, możesz użyć polecenia DROP INDEX. Oto jak to zrobić:

   DROP INDEX nazwa_indeksu;

Na przykład, aby usunąć indeks “idx_device_name”, wpisz:

   DROP INDEX idx_device_name;

3. Używanie indeksów w zapytaniach:
   Indeksy są używane automatycznie przez bazę danych w zapytaniach, które mogą skorzystać z indeksu, aby przyspieszyć operacje wyszukiwania, sortowania, łączenia i inne. Nie musisz niczego specjalnego robić w zapytaniach, aby korzystać z indeksów, o ile twoje zapytania są zgodne z indeksowanymi kolumnami. Jednak ważne jest, aby wybierać właściwe kolumny do indeksowania i zachować umiar, aby nie tworzyć nadmiernie dużo indeksów, które mogą wpłynąć na wydajność zapisu i ogólnej wydajności bazy danych.
4. Analiza wydajności:
   Możesz użyć narzędzi analitycznych, takich jak polecenie EXPLAIN QUERY PLAN, aby zrozumieć, jakie indeksy są używane przez bazy danych w konkretnych zapytaniach i jakie operacje są wykonywane.

Podsumowując, używanie indeksów w bazie danych SQLite polega na tworzeniu indeksów na odpowiednich kolumnach tabeli. Baza danych automatycznie wykorzystuje indeksy w operacjach zapytań, które są zgodne z indeksowanymi kolumnami, aby poprawić wydajność operacji.