wprowadzenie

Ten samouczek obejmie korzystanie z SQLite w połączeniu z interfejsem Pythona sqlite3. SQLite jest pojedynczą relacyjną bazą danych plików dostarczaną z większością standardowych instalacji Pythona. SQLite jest często wybieraną technologią dla małych aplikacji, szczególnie tych systemów wbudowanych i urządzeń, takich jak Telefony i Tablety, inteligentne urządzenia i instrumenty. Nierzadko jednak słyszy się, że jest on używany w małych i średnich aplikacjach internetowych i desktopowych.,

Tworzenie bazy danych i nawiązywanie połączenia

Tworzenie nowej bazy danych SQLite jest tak proste, jak tworzenie połączenia przy użyciu modułu sqlite3 w standardowej bibliotece Pythona. Aby nawiązać połączenie, wystarczy przekazać ścieżkę pliku do metody connect(...) w module sqlite3, a jeśli baza danych reprezentowana przez plik nie istnieje, zostanie utworzona na tej ścieżce.,

import sqlite3con = sqlite3.connect('/path/to/file/db.sqlite3')

przekonasz się, że w codziennym programowaniu baz danych będziesz stale tworzyć połączenia z bazą danych, dlatego warto zawinąć tę prostą instrukcję połączenia w uogólnioną funkcję wielokrotnego użytku.

tworzenie tabel

aby utworzyć tabele bazy danych, musisz mieć pojęcie o strukturze danych, które chcesz przechowywać. Istnieje wiele rozważań projektowych, które wchodzą w definiowanie tabel relacyjnej bazy danych, o których napisano całe książki., Nie będę wdawał się w szczegóły tej praktyki i pozostawię to czytelnikowi do dalszego zbadania.

jednak, aby pomóc w naszej dyskusji na temat programowania baz danych SQLite w Pythonie, będę pracował na założeniu, że baza danych musi być utworzona dla fikcyjnego sklepu z książkami, który ma poniższe dane już zebrane na temat sprzedaży książek.,

customer date product price
Alan Turing 2/22/1944 Introduction to Combinatorics 7.99
Donald Knuth 7/3/1967 A Guide to Writing Short Stories 17.99
Donald Knuth 7/3/1967 Data Structures and Algorithms 11.99
Edgar Codd 1/12/1969 Advanced Set Theory 16.,99

po sprawdzeniu tych danych jest oczywiste, że zawierają one informacje o klientach, produktach i zamówieniach. Powszechnym wzorcem w projektowaniu baz danych dla systemów transakcyjnych tego typu jest podział zleceń na dwie dodatkowe tabele, zlecenia i pozycje liniowe (czasami określane jako szczegóły zlecenia) w celu osiągnięcia większej normalizacji.

w interpreterze Pythona, w tym samym katalogu co db_utils.,zdefiniowany wcześniej moduł py, wprowadź SQL do tworzenia tabel klientów i produktów następująco:

powyższy kod tworzy obiekt połączenia, a następnie używa go do tworzenia instancji obiektu kursora. Obiekt cursor służy do wykonywania poleceń SQL w bazie danych SQLite.

za pomocą utworzonego kursora napisałem SQL, aby utworzyć tabelę klientów, nadając jej klucz podstawowy wraz z polem tekstowym imienia i nazwiska i przypisując go do zmiennej o nazwie customers_sql., Następnie wywołujęexecute(...) metodę obiektu kursora przekazującą zmiennącustomers_sql. Następnie w podobny sposób tworzę tabelę produktów.

możesz odpytywać tabelęsqlite_master, wbudowaną tabelę metadanych SQLite, aby sprawdzić, czy powyższe polecenia powiodły się.

aby zobaczyć wszystkie tabele w aktualnie podłączonej bazie danych, Wyślij zapytanie namekolumna sqlite_mastertabela, w której type jest równa „tabeli”.,

aby zapoznać się ze schematem tabel, należy sprawdzić kolumnę sqltej samej tabeli, w której typejest nadal „tabelą”, a name jest równe „klientom” i/lub „produktom”.

następną tabelą do zdefiniowania będzie tabela zamówień, która kojarzy klientów z zamówieniami za pomocą klucza zagranicznego i daty ich zakupu. Ponieważ SQLite nie obsługuje rzeczywistego typu danych data / czas (lub klasy danych, aby były zgodne z językiem SQLite), wszystkie daty będą reprezentowane jako wartości tekstowe.,

ostateczną tabelą do zdefiniowania będzie tabela pozycji liniowych, która zawiera szczegółowe zestawienie produktów w każdym zamówieniu.

wczytywanie danych

w tej sekcji zademonstruję jak wstawić nasze przykładowe dane do utworzonych właśnie tabel. Naturalnym miejscem startu byłoby najpierw wypełnić tabelę produktów, ponieważ bez produktów nie możemy mieć sprzedaży, a tym samym nie mielibyśmy kluczy zagranicznych, aby odnosić się do pozycji linii i zamówień., Patrząc na przykładowe dane widzę, że są cztery produkty:

obieg pracy do wykonywania poleceń INSERT jest po prostu:

  1. Połącz się z bazą danych
  2. Utwórz obiekt kursora
  3. napisz sparametryzowane polecenie insert SQL i zapisz jako zmienną
  4. wywołaj metodę execute na obiekcie kursora przekazując mu zmienną sql i wartości, jako krotkę, które mają być wstawione do tabeli

biorąc pod uwagę ten ogólny outline napiszmy jeszcze trochę kodu.,

powyższy kod prawdopodobnie wydaje się dość oczywisty, ale pozwól, że omówię go trochę, ponieważ dzieje się tu kilka ważnych rzeczy. Instrukcja insert jest zgodna ze standardową składnią SQL z wyjątkiem bitu?. ? są w rzeczywistości symbolami zastępczymi w tzw. „parametryzowanym zapytaniu”.

parametryzowane zapytania są ważną cechą wszystkich interfejsów baz danych do nowoczesnych języków programowania wysokiego poziomu, takich jak moduł sqlite3 w Pythonie., Ten rodzaj zapytań służy poprawie wydajności zapytań, które są powtarzane kilka razy. Być może co ważniejsze, dezaktywują również wejścia, które zajmują miejsce?, które są przekazywane podczas wywołania metody execute obiektu kursora, aby zapobiec nikczemnym wejściom prowadzącym do iniekcji SQL. Poniżej znajduje się komiks z popularnego xkcd.com blog opisujący zagrożenia związane z SQL injection.

aby wypełnić pozostałe tabele, będziemy postępować według nieco innego wzoru, aby nieco zmienić rzeczy., Przepływ pracy dla każdego zamówienia, identyfikowany przez kombinację imienia i nazwiska klienta oraz daty zakupu, będzie następujący:

  1. Wstaw nowego klienta do tabeli klientów i odzyskaj jego podstawowy identyfikator klucza
  2. Utwórz wpis zamówienia na podstawie identyfikatora klienta i daty zakupu, a następnie odzyskaj jego podstawowy identyfikator klucza
  3. dla każdego produktu w zamówieniu określ jego podstawowy identyfikator klucza i Utwórz wpis pozycji linii powiązanej z zamówieniem i produktem

aby uprościć sobie sprawę, pozwól nam wykonać kilka prostych czynności.szybkie spojrzenie na wszystkie nasze produkty., Na razie nie martw się zbytnio mechaniką instrukcji SELECT SQL, ponieważ wkrótce poświęcimy jej sekcję.

pierwsze zamówienie zostało złożone 22 lutego 1944 roku przez Alana Turinga, który kupił Introduction to Combinatorics za 7,99 USD.

zacznij od utworzenia nowego rekordu klienta dla Pana Turinga, a następnie określ jego główny identyfikator klucza, uzyskując dostęp do polalastrowid obiektu kursora.

możemy teraz utworzyć wpis zamówienia, pobrać nową wartość ID zamówienia i powiązać ją z wpisem pozycji liniowej wraz z zamówionym przez Pana Turinga produktem.,

Pozostałe rekordy są ładowane dokładnie tak samo, z wyjątkiem zamówienia złożonego Donaldowi Knuthowi, który otrzyma dwa wpisy pozycji liniowych. Jednak powtarzalność takiego zadania polega na konieczności zawijania tych funkcjonalności w funkcje wielokrotnego użytku. W db_utils.py moduł Dodaj następujący kod:

Awh, teraz możemy pracować z pewną wydajnością!

musiszexit() swojego interpretera Pythona i przeładować go, aby twoje nowe funkcje stały się dostępne w interpreterze.,

czuję się zmuszony dać jedną dodatkową Radę jako student rzemiosła programistycznego. Kiedy znajdziesz się robi wiele manipulacji bazy danych (wstawia w tym przypadku) w celu osiągnięcia tego, co jest rzeczywiście jednym zbiorczym zadaniem (tj. tworzenie zamówienia) najlepiej jest zawijać podzadania (tworzenie klienta, zamówienie, następnie pozycje linii) w jednej transakcji bazy danych, dzięki czemu można albo zatwierdzić na sukces lub rollback, jeśli wystąpi błąd po drodze.,

wyglądałoby to mniej więcej tak:

chcę zakończyć tę sekcję szybką demonstracją, jak zaktualizować istniejący rekord w bazie danych. Zaktualizujmy cenę przewodnika do pisania opowiadań do 10,99(w sprzedaży).

>>> update_sql = "UPDATE products SET price = ? WHERE id = ?">>> cur.execute(update_sql, (10.99, 2))

odpytywanie bazy danych

zazwyczaj najczęstszą akcją wykonywaną w bazie danych jest pobieranie niektórych danych przechowywanych w niej za pomocą instrukcji SELECT. W tej sekcji zademonstruję jak używać interfejsu SQLITE3 do wykonywania prostych zapytań SELECT.,

aby wykonać podstawowe zapytanie wielowarstwowe tabeli klientów, przekazujemy polecenie SELECT do metodyexecute(...) obiektu kursora. Następnie można iterować wyniki zapytania przez wywołanie metody fetchall() tego samego obiektu kursora.

powiedzmy, że zamiast tego chcesz pobrać jeden rekord z bazy danych. Można to zrobić pisząc bardziej szczegółowe zapytanie, np. dla Id Donalda Knutha z 2, a następnie wywołując fetchone() metodę obiektu kursora.,

>>> cur.execute("SELECT id, first_name, last_name FROM customers WHERE id = 2")>>> result = cur.fetchone()>>> print(result)(2, 'Donald', 'Knuth')

zobacz jak poszczególne wiersze każdego wyniku mają postać krotki? Cóż, podczas gdy krotki są bardzo przydatną strukturą danych Pythonicznych w niektórych przypadkach programowania, Wiele osób uważa je za nieco utrudniające, jeśli chodzi o zadanie pobierania danych. Tak się składa, że istnieje sposób na przedstawienie danych w sposób, który może być bardziej elastyczny dla niektórych. Wystarczy ustawić row_factory metodę obiektu connection na coś bardziej odpowiedniego, na przykład sqlite3.Row., Daje to możliwość dostępu do poszczególnych elementów wiersza według pozycji lub wartości słowa kluczowego.

podsumowanie

w tym artykule przedstawiłem krótką demonstrację najważniejszych cech i funkcjonalności interfejsu Pythona sqlite3 do lekkiej bazy danych SQLite z pojedynczym plikiem, która jest wstępnie dołączona do większości instalacji Pythona., Starałem się również udzielić kilku rad dotyczących najlepszych praktyk, jeśli chodzi o programowanie baz danych, ale ostrzegam nowo przybyłych, że zawiłości programowania baz danych są generalnie jednym z najbardziej podatnych na luki w zabezpieczeniach na poziomie przedsiębiorstwa i przed takim przedsięwzięciem niezbędna jest dalsza wiedza.