Inleiding

Deze tutorial zal betrekking hebben op het gebruik van SQLite in combinatie met Python ‘ s sqlite3-interface. SQLite is een enkel bestand relationele database gebundeld met de meeste standaard Python installeert. SQLite is vaak de technologie bij uitstek voor kleine toepassingen, met name die van embedded systemen en apparaten zoals telefoons en tablets, slimme apparaten en instrumenten. Het is echter niet ongewoon om te horen dat het wordt gebruikt voor kleine tot middelgrote web-en desktoptoepassingen.,

een Database aanmaken en een verbinding maken

een nieuwe SQLite-database aanmaken is net zo eenvoudig als een verbinding maken met de sqlite3-module in de Python-standaardbibliotheek. Om een verbinding tot stand te brengen hoef je alleen maar een bestandspad door te geven aan de connect(...) methode in de sqlite3 module, en als de database die door het bestand wordt weergegeven niet bestaat, zal er een worden aangemaakt op dat pad.,

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

u zult merken dat u bij het dagelijks programmeren van databases voortdurend verbindingen met uw database zult maken, dus het is een goed idee om dit eenvoudige verbindingsstatement in een herbruikbare gegeneraliseerde functie te wikkelen.

tabellen aanmaken

om databasetabellen aan te maken moet u een idee hebben van de structuur van de gegevens die u wilt opslaan. Er zijn veel ontwerp overwegingen die gaan in het definiëren van de tabellen van een relationele database, waar hele boeken over zijn geschreven., Ik zal niet ingaan op de details van deze praktijk en zal in plaats daarvan laat het aan de lezer om verder te onderzoeken.

echter, om te helpen in onze discussie over SQLite database programmeren met Python Ik zal werken vanuit de veronderstelling dat een database moet worden gemaakt voor een fictieve boek winkel die de onderstaande gegevens al verzameld over de verkoop van boeken.,

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

bij inspectie van deze gegevens is het duidelijk dat het informatie bevat over klanten, producten en bestellingen. Een gemeenschappelijk patroon in database ontwerp voor transactionele systemen van dit type zijn om de orders te splitsen in twee extra tabellen, orders en line items (soms aangeduid als order details) om een grotere normalisatie te bereiken.

In een Python-interpreter, in dezelfde map als de db_utils.,py module eerder gedefinieerd, voer de SQL voor het maken van de klanten en producten tabellen volgt:

de bovenstaande code maakt een verbinding object vervolgens gebruikt om een cursor object instantiate. Het cursor object wordt gebruikt om SQL statements uit te voeren op de SQLite database.

met de cursor aangemaakt ik schreef vervolgens de SQL om de klantentabel aan te maken, waarbij ik deze een primaire sleutel gaf samen met een tekstveld voor de voor-en achternaam en deze toewijst aan een variabele genaamd customers_sql., Ik noem dan deexecute(...) methode van het cursor object dat het geeft decustomers_sql variabele. Ik maak dan een productentabel op een vergelijkbare manier.

u kunt de tabel sqlite_master opvragen, een ingebouwde SQLite-metadatatabel, om te controleren of de bovenstaande commando ‘ s succesvol waren.

om alle tabellen in de momenteel verbonden database query te zien, wordt de name kolom van de sqlite_master tabel weergegeven waarbij de type gelijk is aan “tabel”.,

om een blik te krijgen op het schema van de tabellen query de sql kolom van dezelfde tabel waar de type is nog steeds “tabel” en de name is gelijk aan “klanten” en/of “producten”.

de volgende tabel die moet worden gedefinieerd, is de tabel met orders die klanten aan orders koppelt via een buitenlandse sleutel en de datum van aankoop. Aangezien SQLite geen werkelijke datum/tijd gegevenstype ondersteunt (of gegevensklasse om consistent te zijn met de SQLite-volkstaal), worden alle datums weergegeven als tekstwaarden.,

De finaletabel die moet worden gedefinieerd, is de tabel met posten die een gedetailleerde boekhouding van de producten in elke volgorde geeft.

het laden van de gegevens

In deze sectie zal ik demonstreren hoe we onze voorbeeldgegevens kunnen invoegen in de zojuist aangemaakte tabellen. Een natuurlijke startplaats zou zijn om de productentabel eerst te bevolken omdat zonder producten wij geen verkoop kunnen hebben en dus niet de buitenlandse sleutels zouden hebben om op de lijnpunten en orden betrekking te hebben., Als ik de voorbeeldgegevens bekijk, zie ik dat er vier producten zijn:

de workflow voor het uitvoeren van INSERT statements is eenvoudig:

1. verbinding maken met de database
2. Maak een cursor-object
3. Schrijf een geparametreerd SQL-statement invoegen en sla op als een variabele
4. aanroep de methode uitvoeren op het cursor-object waarbij het de SQL-variabele en de waarden, als een tupel, worden ingevoegd in de tabel

gegeven deze algemene schets laat ons wat meer code schrijven.,

de bovenstaande code lijkt waarschijnlijk vrij voor de hand liggend, maar laat me het een beetje bespreken omdat er hier een aantal belangrijke dingen aan de hand zijn. De insert instructie volgt de standaard SQL syntaxis behalve de ? bit. De ?’s zijn in feite plaatshouders in wat bekend staat als een”geparametreerde query”.

geparametreerde queries zijn een belangrijk kenmerk van in wezen alle database-interfaces naar moderne programmeertalen op hoog niveau, zoals de sqlite3 module in Python., Dit type query dient om de efficiëntie van query ‘ s die meerdere keren worden herhaald te verbeteren. Misschien nog belangrijker, ze ontsmetten ook ingangen die de plaats innemen van de? plaatshouders die worden doorgegeven tijdens de aanroep naar de methode uitvoeren van het cursor-object om te voorkomen dat snode ingangen leiden tot SQL-injectie. Het volgende is een strip uit de populaire xkcd.com blog beschrijft de gevaren van SQL injectie.

om de resterende tabellen te vullen gaan we een iets ander patroon volgen om dingen een beetje te veranderen., De workflow voor elke bestelling, geïdentificeerd door een combinatie van klant voor-en achternaam en de aankoopdatum, zal zijn:

1. plaats de nieuwe klant in de klantentabel en haal de primaire sleutel-id op
2. Creëer een orderingang op basis van de klant-id en de aankoopdatum haal dan de primaire sleutel-id op
3. voor elk product in de bestelling bepaal de primaire sleutel-id en maak een regelitemingang die de bestelling en het product koppelt

om het voor onszelf eenvoudiger te maken een snelle blik op al onze producten., Maak je voorlopig niet te veel zorgen over de mechanica van het Select SQL statement, want we zullen er binnenkort een sectie aan wijden.de eerste order werd geplaatst op 22 februari 1944 door Alan Turing die Introduction to Combinatorics kocht voor $7,99.

begin met het maken van een nieuw klantrecord voor Mr.Turing en bepaal vervolgens zijn primaire sleutel-id door toegang te krijgen tot het lastrowid veld van het cursor-object.

We kunnen nu een orderingang maken, de waarde van de nieuwe order id verzamelen en deze koppelen aan een regelitemingang samen met het product dat Mr.Turing heeft besteld.,

de overige records worden exact hetzelfde geladen, behalve voor de bestelling die wordt gemaakt aan Donald Knuth, die twee regelitems zal ontvangen. Het repetitieve karakter van een dergelijke taak roept echter de noodzaak uit om deze functionaliteiten in herbruikbare functies te verpakken. In de db_utils.py module voeg de volgende code toe:

Awh, nu kunnen we met enige efficiëntie werken!

u moet exit() uw Python-interpreter herladen om uw nieuwe functies toegankelijk te maken in de interpreter.,

Ik voel me verplicht om een extra advies te geven als student van software vakmanschap. Wanneer je merkt dat je het doen van meerdere database manipulaties (INSERTs in dit geval) om te bereiken wat eigenlijk een cumulatieve taak (dat wil zeggen, het creëren van een bestelling) is het het beste om de subtaken (het creëren van klant, bestelling, dan line items) in een enkele database transactie, zodat u kunt committen op succes of terugdraaien als er een fout optreedt langs de weg.,

Dit zou er ongeveer zo uitzien:

Ik wil deze sectie afsluiten met een snelle demonstratie van het bijwerken van een bestaand record in de database. Laten we een update van De Gids Voor het schrijven van korte verhalen ‘ prijs tot 10,99 (gaan op de verkoop).

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

opvragen van de Database

over het algemeen is de meest voorkomende actie die op een database wordt uitgevoerd, het ophalen van enkele gegevens die erin zijn opgeslagen via een SELECT statement. Voor deze sectie zal ik demonstreren hoe de sqlite3 interface te gebruiken om eenvoudige select queries uit te voeren.,

om een basis MultiRow query van de klantentabel uit te voeren, geeft u een SELECT statement door aan de execute(...) methode van het cursor-object. Hierna kunt u de resultaten van de query herhalen door de fetchall() methode van hetzelfde cursor object aan te roepen.

laten we zeggen dat u in plaats daarvan één record uit de database wilt ophalen. U kunt dit doen door een specifiekere query te schrijven, bijvoorbeeld voor Donald Knuth ‘ s id van 2, en dat te volgen door fetchone() methode van het cursor object aan te roepen.,

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

zie hoe de afzonderlijke rij van elk resultaat in de vorm van een tupel is? Nou, terwijl tuples zijn een zeer nuttige Pythonic data structuur voor sommige programmering use cases veel mensen vinden ze een beetje hinderend als het gaat om de taak van het ophalen van gegevens. Het is gewoon zo dat er een manier is om de gegevens weer te geven op een manier die misschien flexibeler is voor sommigen. U hoeft alleen de methode row_factory van het verbindingsobject in te stellen op iets geschikter, zoals sqlite3.Row., Dit geeft u de mogelijkheid om toegang te krijgen tot de afzonderlijke items van een rij op positie of trefwoord waarde.

conclusie

In dit artikel gaf ik een korte demonstratie van wat ik voel als de belangrijkste functies en functionaliteiten van de sqlite3 Python interface naar de lichtgewicht single file SQLite database die vooraf wordt geleverd met de meeste Python installaties., Ik heb ook geprobeerd om een paar stukjes van adviezen te geven over best practices als het gaat om database programmeren, maar ik waarschuw de nieuwkomer dat de fijne kneepjes van database programmeren is over het algemeen een van de meest vatbaar voor veiligheidslekken op het niveau van de onderneming en verdere kennis is nodig voordat een dergelijke onderneming.