PostgreSQL w Pythonie - połączenie z bazą i zapytania (psycopg2)

import psycopg2
conn = psycopg2.connect(host='localhost', dbname='sklep',
                        user='postgres', password='...')
cur = conn.cursor()
cur.execute('SELECT * FROM klienci')
print(cur.fetchall())

Sposób łączenia z serwerem bazodanowym w Pythonie jest zależny od tego z jakim rodzajem bazy danych się łączymy. Po nawiązaniu połączenia, sposób korzystania z bazy (pobieranie, zmiana, kasowanie i ładowanie danych) odbywa się tak samo dla każdego rodzaju bazy relacyjnej. Bazy obiektowe rządzą się swoimi prawami, ale nie będziemy ich tu omawiać.

Łączenie z serwerem PostgreSQL

Do łączenia z serwerem PostgreSQL proponuję bibliotekę "psycopg2". Łączenie z bazą PostgreSQL odbywa się z jej pomocą w ten sposób:

import psycopg2
polaczenie=psycopg2.connect(host="jsystems.pl",database="demo",user="demo",password="demo",port=5432)

Nie trzeba tu chyba za wiele tłumaczyć. Trzeba podać host, nazwę bazy danych, użytkownika, hasło i port. W zamian dostajemy utworzony obiekt połączenia, dzięki któremu będziemy mogli na tej bazie wykonywać zapytania.

Pobieranie danych z użyciem SELECT

Niezależnie od tego, do jakiej bazy się łączymy (Oracle, PostgreSQL, MySQL, SQL Server etc), po nawiązaniu połączenia sposób obsługi bazy jest taki sam. Na potrzeby przykładów w tym rozdziale wykorzystam stworzone połączenie do bazy PostgreSQL. W bazie do której się łączę znajduje się tabela "owoce". Ma ona dwie kolumny: "numer" i "nazwa". Poniżej kompletny kod łączący się z tą bazą, wykonujący zapytanie i zwracający wynik na konsoli.

import psycopg2
polaczenie=psycopg2.connect(host="jsystems.pl",database="demo",user="demo",password="demo",port=5432)
kursor = polaczenie.cursor()
sql="select * from owoce"
kursor.execute(sql)
for w in kursor:
    print(w)
kursor.close()

Ponieważ łączenie z bazą zostało omówione już wcześniej, skupmy się teraz na samym pobieraniu danych i ich przetwarzaniu. Interesuje nas linijka od deklaracji zmiennej "kursor". Kursor to specjalny obiekt pozwalający na wykonywanie operacji na bazie danych. Uzyskamy go wywołując funkcję "cursor()" na rzecz obiektu połączenia. Sam kursor posiada funkcję "execute". Podajemy do niej przez parametr treść zapytania SQL które chcemy wykonać. Nie ma znaczenia czy będzie to zapytanie "SELECT" czy jakiś DDL albo DML. Jeśli zapytanie zwróci jakieś dane, będziemy mogli wykorzystać pętlę i je pobrać z użyciem kursora (co widać w przedostatnich 2 liniach). Kursor dobrze jest na koniec zamknąć by niepotrzebnie nie blokować zasobów. Wynik zapytania zostanie nam zwrócony w postaci krotek. Oto wynik działania poniższego kodu:

(1, 'Pomarańcza')
(2, 'Jabłko')
(3, 'Cytryna')
(4, 'Owoc żywota Twojego je ZUS')

Skoro są to krotki, to możemy postępować z nimi jak z każdym innym zestawem krotek. Na ten przykład odczytajmy tylko 2 kolumnę (o indeksie 1 ;) ) :

import psycopg2
polaczenie=psycopg2.connect(host="jsystems.pl",database="demo",user="demo",password="demo",port=5432)
kursor = polaczenie.cursor()
sql="select * from owoce"
kursor.execute(sql)
for w in kursor:
    print(w[1])
kursor.close()

Tym razem dane wydrukowane na konsolę wyglądają tak:

Pomarańcza
Jabłko
Cytryna
Owoc żywota Twojego je ZUS

Ważna informacja dla osób równie zeschizowanych na punkcie wydajności co i ja (czyli mam nadzieję również Ciebie szanowny czytelniku ;) ): Dane odczytywane są z bazy w momencie wywołania funkcji "execute". Fetch nie następuje w pętli kursorowej (do czego mogli przywyknąć użytkownicy takich języków jak np. PL/SQL), a od razu w momencie wywołania wykonania zapytania.

Wstawianie, zmiana i kasowanie danych, oraz operacje DDL

Poniższy kod wstawia jeden wiersz do tabeli owoce. W zasadzie różnica w poniższym kodzie w stosunku do tego z instrukcją "SELECT" tkwi jedynie w treści zapytania, oraz wykorzystaniu nowej instrukcji "commit" (przedostatnia linia). Służy ona do zatwierdzenia transakcji. Jeśli tego nie zrobisz, zmiana będzie widoczna tylko dla Twojej sesji, a kiedy ją zakończysz bez zatwierdzenia transakcji - dane nigdy nie zostaną utrwalone.

import psycopg2
polaczenie=psycopg2.connect(host="jsystems.pl",database="demo",user="demo",password="demo",port=5432)
kursor = polaczenie.cursor()
sql="insert into owoce(nazwa) values ('Granat')"
kursor.execute(sql)
polaczenie.commit()
kursor.close()

W dokładnie ten sam sposób możesz wykonać aktualizację danych, kasowanie, czy dowolną operację DDL. Należałoby jedynie zmienić treść zapytania. Pytanie które na przykład mi przychodzi do głowy przy okazji takich insertów jak ten powyższy "a pod jakim ID wylądował dodany owoc i jak ten ID odczytać?". To dobre pytanie i miło mi że o to pytasz ;). Kolumna ID w tabeli "owoce" jest typu "serial" - czyli wartości w tej kolumnie są generowanie po stronie bazy danych. Nie dodałem wartości która ma trafić do tej kolumny z poziomu Pythona, a jednak chciałbym wiedzieć pod jakim identyfikatorem wylądował "Granat". Czasami trzeba tę wartość odebrać np. ze względu na klucze obce gdy chcemy do tego wiersza dowiązać jakieś zależne wiersze w innych tabelach. Przykład: faktura i produkty zawarte na fakturze - musi istnieć relacja, a wiersze z produktami muszą wskazywać na wiersz faktury do której się odnoszą. Poniżej przykład jak się dobrać do takiego generowanego ID, albo jakiejkolwiek innej wartości generowanej po stronie bazy danych (np. przez trigger, czy pobieranej z sekwencji). Pomijam już elementy wielokrotnie powtarzane - nawiązywanie połączenia.

kursor = polaczenie.cursor()
sql="insert into owoce(nazwa) values ('Arbuz') returning numer"
kursor.execute(sql)
id=str(kursor.fetchone()[0])
print('id='+id)
polaczenie.commit()
kursor.close()

Zmieniłem nieco zapytanie. Aby można było odebrać taką dynamicznie generowaną wartość należy użyć klauzuli returning wskazującej nazwę kolumny z której taka dynamiczna wartość ma zostać odczytana. Samą wartość odczytujemy już za pomocą funkcji fetchone() uruchamianej na rzecz kursora.

Ten kurs daje Ci solidne podstawy Pythona. Jeśli chcesz pójść dalej i pod okiem trenera dojść od pierwszej linijki kodu do własnego modelu uczenia maszynowego, sprawdź nasze pięciodniowe szkolenie Python od podstaw, przez analizę danych, do machine learning. Pierwsze trzy dni to Python w praktyce: typy i kolekcje, instrukcje warunkowe i pętle, funkcje, moduły i pakiety, pliki tekstowe, usługi sieciowe oraz praca z bazą PostgreSQL. Czwarty dzień to analiza danych w pandas (wczytywanie CSV, TSV i XLS, filtrowanie, agregacje, statystyki) i wizualizacja w Matplotlib, Seaborn oraz Plotly. Piąty dzień to uczenie maszynowe w scikit-learn: regresja liniowa i logistyczna, drzewa decyzyjne, las losowy, XGBoost, walidacja predykcji, dobór hiperparametrów i przeciwdziałanie przeuczeniu. Szkolenie ma terminy gwarantowane, czyli odbywa się niezależnie od wielkości grupy.

Szkolenie Python od podstaw, przez analizę danych, do machine learning

To szkolenie może być dofinansowane dla Ciebie z KFS lub BUR.

★★★★★Średnia ocena naszych szkoleń w Google: 5/5