{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "# 800100715151 Astronomide Veritabanları #\n", "\n", "## Ders - 05 Yapılandırılmış Sorgu Dili: SQL'e Giriş ##" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Doç. Dr. Özgür Baştürk
\n", "Ankara Üniversitesi, Astronomi ve Uzay Bilimleri Bölümü
\n", "obasturk at ankara.edu.tr
\n", "http://ozgur.astrotux.org" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# Bu derste neler öğreneceksiniz? #\n", "## Yapılandırılmş Sorgu Dili SQL ##\n", "\n", "* [Python ve SQL](#Python-ve-SQL)\n", " * [Giriş ve Gerekli Paketlerin Kurulumu](#Giriş-ve-Gerekli-Paketlerin-Kurulumu)\n", " * [Modüllerin Yüklenmesi ve Veritabanı Sunucusuna Bağlanma](#Modüllerin-Yüklenmesi-ve-Veritabanı-Sunucusuna-Bağlanma) \n", "* [SQL ile Tablo Oluşturma](#SQL-ile-Tablo-Oluşturma)\n", " * [NASA Kepler Ötegezegen Veritabanı Uygulaması](#NASA-Kepler-Ötegezegen-Veritabanı-Uygulaması)\n", "* [Veritabanıyla Bağlantının Kurulması](#Veritabanıyla-Bağlantının-Kurulması) \n", "* [Veritabanı tablolarına verinin alınması](#Veritabanı-Tablolarına-Verinin-Alınması)\n", "* [SQL Sorgularıyla Satır Seçme](#SQL-Sorgularıyla-Satır-Seçme)\n", " * [SQL Sorgularıyla Bazı Satır ve Sütunları Seçme](#SQL-Sorgularıyla-Bazı-Satır-ve-Sütunları-Seçme)\n", " * [Bir Koşulu Sağlayan Satırları Elde Etme](#Bir-Koşulu-Sağlayan-Satırları-Elde-Etme)\n", " * [Örnek 1: Büyük Yıldızlar](#Örnek-1:-Büyük-Yıldızlar)\n", "* [Şartlı Sorgular](#Şartlı-Sorgular)\n", " * [Koşulların Bağlanması](#Koşulların-Bağlanması)\n", " * [Örnek 2: Sıcak Yıldızlar](#Örnek-2:-Sıcak-Yıldızlar)\n", "* [Bir Veritabanının Yapısının İncelenmesi](#Bir-Veritabanının-Yapısının-İncelenmesi)\n", " * [SQL'de NULL Değeri](#SQL'de-NULL-Değeri)\n", "* [Örnek 3: Onaylanmış Küçük Gezegenler](#Örnek-3:-Onaylanmış-Küçük-Gezegenler)\n", "* [SQL'de Veri İstatistikleri ve Birleştirme Fonksiyonları](#SQL'de-Veri-İstatistikleri-ve-Birleştirme-Fonksiyonları)\n", " * [COUNT Fonksiyonu](#COUNT-Fonksiyonu)\n", " * [Diğer Birleştirme ve İstatistik Fonksiyonları](#Diğer-Birleştirme-ve-İstatistik-Fonksiyonları)\n", "* [Sorgu Sonuçlarını Sıralama ve Limitleme](#Sorgu-Sonuçlarını-Sıralama-ve-Limitleme)\n", "* [Örnek 4: Gezegen İstatistikleri](#Örnek-4:-Gezegen-İstatistikleri)\n", "* [Veriyi Gruplama](#Veriyi-Gruplama)\n", " * [Gruplandırılmış Veride Şartlı Sorgulama](#Gruplandırılmış-Veride-Şartlı-Sorgulama)\n", " * [Örnek 5: Çoklu Gezegen Sistemleri](#Örnek-5:-Çoklu-Gezegen-Sistemleri)\n", "* [Kaynaklar](#Kaynaklar)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# Python ve SQL\n", "\n", "## Giriş ve Gerekli Paketlerin Kurulumu\n", "\n", "[Yapılandırılmış Sorgu Dili - SQL](https://tr.wikipedia.org/wiki/SQL) ilişkisel (ing. relational) bir veritabanı yönetim sisteminde (RDBMS) tutulan verileri yönetmek için tasarlanmış, veritabanı yönetimi ve sorgulanmasına özgü olarak geliştirilmiş bir dildir. Öncüllerinden farklı olarak tek bir komutla birçok kayda erişme imkanı sağlaması ile öne çıkmış ve o zamandan beri de veritabanı alanında bir standart olarak kullanılagelmiştir. Kendisi bir programlama dili olmayıp, veritabanlarını sınırlı bir programlama kapasitesi sağlayan komutlar üzerinden yönetme ve sorgulama olanağı sağladığı için genellikle ve yanlışlıkla bir programlama dili sanılır.\n", "\n", "Bu derste temel `SQL` komutlarını `python` dilinin olanaklarıyla birleştirmek üzere yapılandırılmış sorgu diline dayalı, açık kaynak kodlu bir veritabanı yönetim sistemi olan [PostgreSQL](https://www.postgresql.org/) kullanılacaktır. Bu nedenle ilgili kodların çalışması için makinenizde bir `PostgreSQL` sunucusunun kurulu olması ya da bağlanabileceğiniz bir `PostgreSQL` sunucusunun varlığına ihtiyaç duyar. Kurulum için [bkz.](https://www.postgresql.org/docs/). Ubuntu gibi bir Linux tabanlı işletim sisteminde `PostgreSQL` kurulumu için `apt` uygulaması aşağıdaki şekilde kullanılabilir.\n", "\n", "```\n", "sudo apt install postgresql postgresql-contrib\n", "```\n", "\n", "İstendiği takdirde [MySQL](https://www.mysql.com/) gibi yine SQL'e dayalı bir başka veritabanı yönetim sistemi de tercih edilebilir.\n", "\n", "Ayrıca PostgreSQL-Python etkileşimi için [pyscopg2](https://www.psycopg.org/) modülü kullanılacaktır. Ancak öneri `psycopg2-binary` kurulması yönündedir; zira 2.8 sürümü sonrası `psycopg` modülünün isminin değişmesi planlanmıştır. Her iki modül de `pip` ya da başka bir \"installer\" ile kolaylıkla kurulabilir. Bu amaçla yazılmış [başka modüller](https://towardsdatascience.com/how-to-run-sql-queries-from-a-jupyter-notebook-aaa18e59e7bc) de bulunmaktadır. Ayrıca [sqlalchemy](https://www.sqlalchemy.org/) araçlarından da faydalanmanız gerekebilecektir. Python-SQL etkileşimi için `sqlalchemy` modülünün kurulu olmasında da fayda bulunmaktadır. \n", "\n", "Bu derste yazılan kodların çalışması için\n", "\n", "* Python > 3\n", "* PostgreSQL > 9 \n", "* psycopg2-binary\n", "* sqlalchemy\n", "\n", "sürümlerine ihtiyaç duyulur. Ayrıca bir şifresini bildiğiniz bir de `PostgreSQL` kullanıcısı gerekir. \n", "\n", "Sisteminize `PostgreSQL` veritabanı yönetim sistemini kurduktan sonra aşağıdaki basit komutlarla `myuser` isminde bir kullanıcı tanımlamanız, `mydatabase` isminde bir veritabanı oluşturmanız durumunda bu Jupyter defterindeki bütün uygulamaları çalıştıramnız mümkün olacaktır. Bu amaçla terminal ekranından `PostgreSQL` sunucunuza bağlanmanız ve gerekli komutları sırayla vermeniz yeterli olacaktır.\n", "\n", "```\n", "sudo -u postgres psql\n", "postgres=# create database mydatabase;\n", "postgres=# create user myuser with encrypted password 'PostGRE:sql';\n", "postgres=# grant all privileges on database mydatabase to myuser;\n", "```\n", "\n", "Bu derste bu kullanıcı adının `myuser` olduğu varsayılmıştır. Bu kullanıcıyla sunucuya bağlanıp `mydatabase` isimli veritabanına aşağıdaki şekilde de erişilebilir.\n", "\n", "```\n", "psql -U myuser -W\n", "...\n", "# \\c mydatabase;\n", "\\d;\n", "```" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "[Başa Dön](#Bu-derste-neler-öğreneceksiniz?)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Modüllerin Yüklenmesi ve Veritabanı Sunucusuna Bağlanma\n", "\n", "Öncelikle ders boyunca kullanılacak modüllerin yüklenmesi gerekmektedir." ] }, { "cell_type": "code", "execution_count": 1, "metadata": {}, "outputs": [], "source": [ "import os\n", "import sys\n", "# import the connect library for psycopg2\n", "import psycopg2\n", "import pandas as pd\n", "import numpy as np\n", "from sqlalchemy import create_engine\n", "import matplotlib.pyplot as plt" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Ardından veritabanı sunucusuna `pyscopg2` fonksiyonları ile bağlanmak için gerekli fonksiyon `connect` aşağıda verilmiştir. Bu fonksiyonun argümanı `conn_params_dic` sözlük yapısında bağlantı ile ilgili kullanıcı adı (`user`), şifre (`password`), sunucu (`host`) ve veritabanı adı (`database`) anahtarlarının karışılığı olan değerlerini içermelidir. `psycopg2.connect` fonksiyonu bu parametreleri kullanarak veritabanı sunucusuna bağlanır. `OperationalError` bu bağlantının gerçekleşmemesi durumunda bir hata mesajı üretir ve bağlantı `None` türünde döndürülür. Aksi takdirde bağlantının gerçekleştiğine dair bir mesaj kullanıcıya bildirilir ve bağlantı nesnesi döndürülür. " ] }, { "cell_type": "code", "execution_count": 2, "metadata": {}, "outputs": [], "source": [ "# Define a connect function for PostgreSQL database server\n", "def connect(conn_params_dic):\n", " conn = None\n", " try:\n", " print('PostgreSQL sunucusuna baglaniliyor ...........')\n", " conn = psycopg2.connect(**conn_params_dic)\n", " print(\"Baglanti basarili..................\")\n", " \n", " except OperationalError as err:\n", " # passing exception to function\n", " show_psycopg2_exception(err) \n", " # set the connection to 'None' in case of error\n", " conn = None\n", " return conn" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Bağlantı hatası durumunda hatanın türü ve hatayla ilgili bilgiler kullanıcıya verilmelidir ki kullanıcı buna göre bir çözüm arasın. Bu amaçla aşağıda `sys.exc_info()` fonksiyonuna dayalı olarak yazılan hata yönetimi fonksiyonu `show_psycopg2_exception` fonksiyonu verilmiştir. Bu fonksiyonun argümanı `psycopg2` tarafından başarısız bağlantı denemelerinde üretilen `error` nesnesidir." ] }, { "cell_type": "code", "execution_count": 3, "metadata": {}, "outputs": [], "source": [ "# Define function to catch exception\n", "# Define a function that handles and parses psycopg2 exceptions\n", "def show_psycopg2_exception(err):\n", " # get details about the exception\n", " err_type, err_obj, traceback = sys.exc_info() \n", " # get the line number when exception occured\n", " line_n = traceback.tb_lineno \n", " # print the connect() error\n", " print (\"\\npsycopg2 ERROR:\", err, \"on line number:\", line_n)\n", " print (\"psycopg2 traceback:\", traceback, \"-- type:\", err_type) \n", " # psycopg2 extensions.Diagnostics object attribute\n", " print (\"\\nextensions.Diagnostics:\", err.diag) \n", " # print the pgcode and pgerror exceptions\n", " print (\"pgerror:\", err.pgerror)\n", " print (\"pgcode:\", err.pgcode, \"\\n\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "[Başa Dön](#Bu-derste-neler-öğreneceksiniz?)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# SQL ile Tablo Oluşturma\n", "\n", "SQL'de tablo oluşturmak için `CREATE TABLE table_name` komutu kullanılır. Bu isimle aynı veritabanında kayıtlı bir tablo varsa öncelikle onu silmekte fayda olacaktır. Bu amaç için `DROP TABLE IF EXISTS table_name` komutu kullanılır. Her iki SQL komutunu çalıştırmak için `psycopg2.connection.cursor` nesnesinin `execute` fonksiyonu kullanılmalıdır. Bu fonksiyona çalıştırılmak istenen SQL komutu SQL'de çalışacağı şekilde metin yapısında geçirilmelidir. Bu amaçla ilgili komuta `python` nesnelerinden girdi alınıyorsa bu girdiler yer tutucularla metin içine yerleştirilerek `execute` fonksiyonuna gönderilmelidir.\n", "\n", "## NASA Kepler Ötegezegen Veritabanı Uygulaması\n", "\n", "Bir veritabanı, organize edilmiş (genellikle yapılandırılmış) bir veri topluluğudur.\n", "\n", "Bu derste örnek olarak NASA ötegezegen arşivinden alınan Kepler keşfi bir grup yıldız ve bu yıldızların etrafında keşfedilen gezegenler hakkında bilgileri içeren sırasıyla `Star` ve `Planet` adlarında iki tablodan oluşan bir veritabanı oluşturulmak istenmektedir.\n", "\n", "Aşağıdaki şekilde, bu tabloların organizasyonu görülmektedir. \n", "\n", "
\n", " \n", "
\n", "\n", "Aşağıda bu organizasyona uygun olarak `Star` tablosunu: \n", "\n", "* `kepler_id` boş bırakılamayacak (NOT NULL), tamsayıdan oluşan (INTEGER) nesnelerle tanımlanan Kepler katalog ID'leri,\n", "* `koi_name` 20 karakter uzunluğunda metin veri saklayabilecek (VARCHAR(20)) Kepler kataloğu nesne isimlerini,\n", "* `t_eff` tamsayı olarak tanımlanacak yıldız etkin sıcaklığını,\n", "* `radius` en fazla 5 ondalık basamak içeren kayan noktalı sayı formatında yıldız yarıçapını\n", "\n", "içerecek şekilde `mydatabase` veritabanına ekleyen fonksiyon `create_table_Star` yer almaktadır. `kepler_name` bu tablonun ana anahtar sütunudur (PRIMARY KEY). Bu nedenle her bir yıldızın `kepler_id` adı tek (ing. unique) olmalı ve boş bırakılmamalıdır. Bu yapısıyla PRIMARY KEY her satırı tanımlayan sütun görevini görmektedir." ] }, { "cell_type": "code", "execution_count": 4, "metadata": {}, "outputs": [], "source": [ "# Define function to create table\n", "def create_table_Star(cursor):\n", " try:\n", " # Dropping table iris if exists\n", " cursor.execute(\"DROP TABLE IF EXISTS Star CASCADE;\")\n", " sql = '''CREATE TABLE Star (\n", " kepler_id INTEGER NOT NULL,\n", " koi_name VARCHAR(20) NOT NULL,\n", " t_eff INTEGER,\n", " radius FLOAT(5),\n", " PRIMARY KEY (kepler_id)\n", " );'''\n", " # Creating a table\n", " cursor.execute(sql);\n", " print(\"Star tablosu basariyla olusturulmustur...............\") \n", " except psycopg2.OperationalError as err:\n", " # pass exception to function\n", " show_psycopg2_exception(err)\n", " # set the connection to 'None' in case of error\n", " conn = None" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Gezegen tablosu `Planet` da aynı yapıda bir fonksiyonla\n", "\n", "* `kepler_id` boş bırakılamayacak (NOT NULL), tamsayıdan oluşan (INTEGER) nesnelerle tanımlanan Kepler katalog ID'lerini,\n", "* `koi_name` 20 karakter uzunluğunda metin veri saklayabilecek (VARCHAR(20)) ve yine boş bırakılamayacak (NOT NULL) Kepler kataloğu nesene isimlerini,\n", "* `kepler_name` Kepler keşfi her bir gezegenin 20 karakter uzunluğunda metin veri saklayabilecek (VARCHAR(20)) ve yine boş bırakılamayacak (NOT NULL) Kepler kataloğundaki isimlerini,\n", "* `status` gezegenin onaylanmış (CONFIRMED) olup olmadığı bilgisini,\n", "* `period` gezegenin kayan noktalı sayı formatında (FLOAT) yörünge dönemini,\n", "* `radius` gezegenin kayan noktalı sayı formatında yarıçapını,\n", "* `t_eq` gezegenin tamsayı formatında eşdeğer sıcaklığını,\n", "\n", "içerecek şekilde oluşturulmaktadır. Bu fonksiyonun da PRIMARY KEY sütunu `kepler_id` olarak belirlenmiş ve istenen tablonun oluşturulamaması durumunda oluşacak hata mesajının nasıl yönetileceği belilrlenmiştir.\n" ] }, { "cell_type": "code", "execution_count": 5, "metadata": {}, "outputs": [], "source": [ "def create_table_Planet(cursor):\n", " try:\n", " # Dropping table iris if exists\n", " cursor.execute(\"DROP TABLE IF EXISTS Planet CASCADE;\")\n", " sql = '''CREATE TABLE Planet (\n", " kepler_id INTEGER NOT NULL,\n", " koi_name VARCHAR(20),\n", " kepler_name VARCHAR(20),\n", " status VARCHAR(20) NOT NULL,\n", " period FLOAT,\n", " radius FLOAT,\n", " t_eq INTEGER,\n", " PRIMARY KEY (koi_name)\n", " )'''\n", " # Creating a table\n", " cursor.execute(sql);\n", " print(\"Planet tablosu basariyla olusturulmustur...............\") \n", " except psycopg2.OperationalError as err:\n", " # pass exception to function\n", " show_psycopg2_exception(err)\n", " # set the connection to 'None' in case of error\n", " conn = None" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "[Başa Dön](#Bu-derste-neler-öğreneceksiniz?)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# Veritabanıyla Bağlantının Kurulması\n", "\n", "Öncelikle bağlantı için gerekli bilgilerin bir sözlük yapısında tanımlanarak `connect` fonksiyonuna gönderilmesiyle veritabanıyla bağlantının kurulması sonrası; sırasıyla `Star` ve `Planet` tabloları aşağıdaki şekilde oluşturulmaktadır." ] }, { "cell_type": "code", "execution_count": 6, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "PostgreSQL sunucusuna baglaniliyor ...........\n", "Baglanti basarili..................\n", "Star tablosu basariyla olusturulmustur...............\n", "Planet tablosu basariyla olusturulmustur...............\n" ] } ], "source": [ "conn_params_dic={ 'user':'myuser', 'password':'PostGRE:sql','host':'localhost','dbname':'mydatabase'}\n", "conn = connect(conn_params_dic)\n", "# We set autocommit=True so every command we execute will produce results immediately.\n", "conn.autocommit = True\n", "cursor = conn.cursor()\n", "create_table_Star(cursor)\n", "create_table_Planet(cursor)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "[Başa Dön](#Bu-derste-neler-öğreneceksiniz?)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# Veritabanı Tablolarına Verinin Alınması\n", "\n", "Veritabanındaki tablolara dışarıdan veri almak için öncelikle veriyi içeren dosya `data/stars.csv` açılmalıdır. Daha sonra bu dosya satır satır okunur ve yapısına bağlı olarak uygun bir şekilde bileşenlerine (sütunlarına) ayrılarak `INSERT INTO column_name` komutuyla tablonun ilgili sütunlarına alınır." ] }, { "cell_type": "code", "execution_count": 7, "metadata": { "scrolled": true }, "outputs": [], "source": [ "fn = open(\"veri/stars.csv\")\n", "\n", "for line in fn:\n", " f1 = line.split(\",\")\n", " cursor.execute(\n", " \"INSERT INTO Star (kepler_id, koi_name, t_eff, radius) VALUES (%s, %s, %s, %s);\", \n", " (f1[0], f1[1], f1[2], f1[3])\n", " ) " ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "`Planet` tablosu da aynı şekilde ilgili dosyadan gelen verilerle aşağıdaki şekilde doldurulabilir." ] }, { "cell_type": "code", "execution_count": 8, "metadata": {}, "outputs": [], "source": [ "fn = open(\"veri/planets.csv\")\n", "\n", "for line in fn:\n", " f1 = line.split(\",\")\n", " cursor.execute('''\n", " INSERT INTO Planet (kepler_id, koi_name, kepler_name, status, period, radius, t_eq) \n", " VALUES (%s, %s, %s, %s, %s, %s, %s)\n", " ''',(f1[0], f1[1], f1[2], f1[3], f1[4], f1[5], f1[6])\n", " ) " ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "[Başa Dön](#Bu-derste-neler-öğreneceksiniz?)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# SQL Sorgularıyla Satır Seçme\n", "\n", "Oluşturalan veritabanını sorgulamak üzere bir veya daha fazla tabloyu alan ve istenen verilerle yeni bir tablo döndüren bazı örnekler `psycopg2` fonksiyonu `cursor.execute` ile çalıştırarak aşağıda verilmektedir. " ] }, { "cell_type": "code", "execution_count": 9, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "(2713049, 'K00794.01', 5996, 0.956)\n", "(3114167, 'K00795.01', 5666, 0.677)\n", "(3115833, 'K00797.01', 5995, 0.847)\n", "(3246984, 'K00799.01', 5735, 0.973)\n", "(3342970, 'K00800.01', 6167, 1.064)\n", "(3351888, 'K00801.01', 5717, 1.057)\n", "(3453214, 'K00802.01', 5733, 0.77)\n", "(3641726, 'K00804.01', 5349, 0.82)\n", "(3832474, 'K00806.01', 5485, 0.867)\n", "(3935914, 'K00809.01', 5934, 0.893)\n", "(3940418, 'K00810.01', 5170, 0.807)\n", "(4049131, 'K00811.01', 4905, 0.761)\n", "(4139816, 'K00812.02', 3887, 0.48)\n", "(4275191, 'K00813.01', 5557, 0.781)\n", "(4476123, 'K00814.01', 5413, 0.751)\n", "(4932348, '', 5575, 0.932)\n", "(5358241, 'K00829.01', 6079, 0.945)\n", "(5358624, 'K00830.01', 5071, 0.788)\n", "(5456651, 'K00835.01', 4980, 0.734)\n", "(6862328, 'K00865.01', 5796, 0.871)\n", "(6721123, '', 6227, 1.958)\n", "(6922244, 'K00010.01', 6225, 1.451)\n", "(8395660, 'K00116.01', 5881, 1.029)\n", "(9579641, 'K00115.01', 6391, 1.332)\n", "(10187017, 'K00082.01', 4812, 0.755)\n", "(10480982, 'K00744.01', 6117, 0.947)\n", "(10526549, 'K00746.01', 4856, 0.696)\n", "(10583066, 'K00747.01', 4536, 0.693)\n", "(10601284, 'K00749.01', 5559, 0.806)\n", "(10662202, 'K00750.01', 4722, 0.527)\n", "(10666592, 'K00002.01', 6350, 1.991)\n", "(10682541, 'K00751.01', 5339, 0.847)\n", "(10797460, 'K00752.01', 5850, 1.04)\n", "(10811496, 'K00753.01', 5853, 0.868)\n", "(10419211, '', 5795, 0.848)\n", "(10848459, 'K00754.01', 5795, 0.803)\n", "(10854555, 'K00755.01', 6031, 1.046)\n", "(10583180, '', 4889, 0.819)\n", "(10872983, 'K00756.01', 6046, 0.972)\n", "(10875245, 'K00117.02', 5851, 1.411)\n", "(10910878, 'K00757.01', 5126, 0.742)\n", "(10984090, 'K00112.02', 5803, 1.073)\n", "(10987985, 'K00758.01', 5015, 0.826)\n", "(11018648, 'K00759.01', 5588, 0.796)\n", "(11138155, 'K00760.01', 6117, 1.025)\n", "(11152159, '', 5550, 0.91)\n", "(11153539, 'K00762.01', 6075, 0.969)\n", "(11304958, 'K00764.01', 5468, 1.046)\n", "(11391957, 'K00765.01', 5592, 0.782)\n", "(11403044, 'K00766.01', 6174, 1.103)\n", "(11414511, 'K00767.01', 5653, 0.965)\n", "(11460018, 'K00769.01', 5641, 0.831)\n", "(11446443, 'K00001.01', 5820, 0.964)\n", "(11465813, 'K00771.01', 5520, 0.983)\n", "(11493732, 'K00772.01', 6144, 1.091)\n", "(11507101, 'K00773.01', 5957, 0.971)\n", "(11754553, 'K00775.01', 3898, 0.54)\n", "(11812062, 'K00776.01', 5492, 0.812)\n", "(11818800, 'K00777.01', 5446, 0.781)\n", "(11853255, 'K00778.01', 3741, 0.45)\n", "(11904151, 'K00072.01', 5627, 1.056)\n", "(11918099, 'K00780.01', 4989, 0.727)\n", "(11923270, 'K00781.01', 3672, 0.49)\n", "(11960862, 'K00782.01', 5992, 0.989)\n", "(12020329, 'K00783.01', 5485, 0.867)\n", "(12066335, 'K00784.01', 3767, 0.48)\n", "(12070811, 'K00785.01', 5557, 0.752)\n", "(12110942, 'K00786.01', 5880, 0.917)\n", "(12366084, 'K00787.01', 5841, 0.931)\n", "(12404086, 'K00788.01', 5127, 0.775)\n", "(12470844, 'K00790.01', 5354, 0.788)\n", "(12644822, 'K00791.01', 5795, 0.919)\n" ] } ], "source": [ "query = \"SELECT * FROM Star;\"\n", "cursor.execute(query)\n", "sonuc = cursor.fetchall()\n", "for kayit in sonuc:\n", " print(kayit)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Görüldüğü gibi bu sorgu `Star` tablosundaki tüm satırları döndürmektedir. `SELECT` komutu sorguyu başlatmakta `*` (wildcard) karakteri tüm satırların (\"attributes\") döndürülmesini sağlamakta; `FROM` ifadesi ise sorgunun hangi tablo üzerinde uygulanabileceğini belirtmektedir. `psycopg2` için zorunlu olmamakla birlikte SQL sorguları `;` karakteri ile sonlandırılır. \n", "\n", "`psycopg2` fonksiyonlarından `conn.cursor.execute()` sorguyu uygularken `cursor.fetchall()` sorgunun ürettiği tüm satırları (ing. attributes) alır. Alternatif olarak sadece bir satır alınmak isteniyor ya da sorgunun sadece bir satır ürettiğinden emin olunmuş ise `cursor.fetchone()` fonksiyonu da kullanılabilir. İstenen sayıda satır almak için `cursor.fetchmany()` fonksiyonuna satır sayısı argüman olarak geçirilebilir. Tüm bu fonksiyonlar birer liste üretir. Bu liste bir `for` döngüsü ile taranarak yukarıdaki gibi her satıra (attribute) karşılık gelen demet değişken (tuple) yukarıdaki gibi ayrı ayrı ekrana yazdırılabileceği gibi herhangi bir amaçla ayrılarak da kullanılabilir.\n", "\n", "\n", "### Uyarı: SQL sorguları büyük / küçük harf duyarlı değldir." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "[Başa Dön](#Bu-derste-neler-öğreneceksiniz?)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## SQL Sorgularıyla Bazı Satır ve Sütunları Seçme\n", "\n", "Aşağıdaki sorguyla `Star` tablosundan bu kez tüm sütunlar yerine sadece `koi_name` ve `radius` sütunları seçilmektedir." ] }, { "cell_type": "code", "execution_count": 10, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "koi_name\t R_*\n", "('K00794.01', 0.956)\n", "('K00795.01', 0.677)\n", "('K00797.01', 0.847)\n", "('K00799.01', 0.973)\n", "('K00800.01', 1.064)\n", "('K00801.01', 1.057)\n", "('K00802.01', 0.77)\n", "('K00804.01', 0.82)\n", "('K00806.01', 0.867)\n", "('K00809.01', 0.893)\n", "('K00810.01', 0.807)\n", "('K00811.01', 0.761)\n", "('K00812.02', 0.48)\n", "('K00813.01', 0.781)\n", "('K00814.01', 0.751)\n", "('', 0.932)\n", "('K00829.01', 0.945)\n", "('K00830.01', 0.788)\n", "('K00835.01', 0.734)\n", "('K00865.01', 0.871)\n", "('', 1.958)\n", "('K00010.01', 1.451)\n", "('K00116.01', 1.029)\n", "('K00115.01', 1.332)\n", "('K00082.01', 0.755)\n", "('K00744.01', 0.947)\n", "('K00746.01', 0.696)\n", "('K00747.01', 0.693)\n", "('K00749.01', 0.806)\n", "('K00750.01', 0.527)\n", "('K00002.01', 1.991)\n", "('K00751.01', 0.847)\n", "('K00752.01', 1.04)\n", "('K00753.01', 0.868)\n", "('', 0.848)\n", "('K00754.01', 0.803)\n", "('K00755.01', 1.046)\n", "('', 0.819)\n", "('K00756.01', 0.972)\n", "('K00117.02', 1.411)\n", "('K00757.01', 0.742)\n", "('K00112.02', 1.073)\n", "('K00758.01', 0.826)\n", "('K00759.01', 0.796)\n", "('K00760.01', 1.025)\n", "('', 0.91)\n", "('K00762.01', 0.969)\n", "('K00764.01', 1.046)\n", "('K00765.01', 0.782)\n", "('K00766.01', 1.103)\n", "('K00767.01', 0.965)\n", "('K00769.01', 0.831)\n", "('K00001.01', 0.964)\n", "('K00771.01', 0.983)\n", "('K00772.01', 1.091)\n", "('K00773.01', 0.971)\n", "('K00775.01', 0.54)\n", "('K00776.01', 0.812)\n", "('K00777.01', 0.781)\n", "('K00778.01', 0.45)\n", "('K00072.01', 1.056)\n", "('K00780.01', 0.727)\n", "('K00781.01', 0.49)\n", "('K00782.01', 0.989)\n", "('K00783.01', 0.867)\n", "('K00784.01', 0.48)\n", "('K00785.01', 0.752)\n", "('K00786.01', 0.917)\n", "('K00787.01', 0.931)\n", "('K00788.01', 0.775)\n", "('K00790.01', 0.788)\n", "('K00791.01', 0.919)\n" ] } ], "source": [ "query = \"SELECT koi_name, radius FROM Star;\"\n", "cursor.execute(query)\n", "sonuc = cursor.fetchall()\n", "print(\"koi_name\\t R_*\")\n", "for kayit in sonuc:\n", " print(kayit)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "[Başa Dön](#Bu-derste-neler-öğreneceksiniz?)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Bir Koşulu Sağlayan Satırları Elde Etme\n", "\n", "Bazı durumlarda bir tablodan sadece bir koşula uyan bazı sütunlar istenebilir. İsteen sütun adları `SELECT` ifadesinden sonra verilir ve `WHERE` ifadesi ile hangi koşula uyan satırların istendiği bir \"boolean\" ifadeyle veritabanı sunucusuna gönderilir." ] }, { "cell_type": "code", "execution_count": 11, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "koi_name\t R_*\n", "('K00794.01', 0.956)\n", "('K00795.01', 0.677)\n", "('K00797.01', 0.847)\n", "('K00799.01', 0.973)\n", "('K00800.01', 1.064)\n", "('K00801.01', 1.057)\n", "('K00802.01', 0.77)\n", "('K00804.01', 0.82)\n", "('K00806.01', 0.867)\n", "('K00809.01', 0.893)\n", "('K00810.01', 0.807)\n", "('K00811.01', 0.761)\n", "('K00812.02', 0.48)\n", "('K00813.01', 0.781)\n", "('K00814.01', 0.751)\n", "('', 0.932)\n", "('K00829.01', 0.945)\n", "('K00830.01', 0.788)\n", "('K00835.01', 0.734)\n", "('K00865.01', 0.871)\n", "('', 1.958)\n", "('K00010.01', 1.451)\n", "('K00116.01', 1.029)\n", "('K00115.01', 1.332)\n", "('K00082.01', 0.755)\n", "('K00744.01', 0.947)\n", "('K00746.01', 0.696)\n", "('K00747.01', 0.693)\n", "('K00749.01', 0.806)\n", "('K00750.01', 0.527)\n", "('K00002.01', 1.991)\n", "('K00751.01', 0.847)\n", "('K00752.01', 1.04)\n", "('K00753.01', 0.868)\n", "('', 0.848)\n", "('K00754.01', 0.803)\n", "('K00755.01', 1.046)\n", "('', 0.819)\n", "('K00756.01', 0.972)\n", "('K00117.02', 1.411)\n", "('K00757.01', 0.742)\n", "('K00112.02', 1.073)\n", "('K00758.01', 0.826)\n", "('K00759.01', 0.796)\n", "('K00760.01', 1.025)\n", "('', 0.91)\n", "('K00762.01', 0.969)\n", "('K00764.01', 1.046)\n", "('K00765.01', 0.782)\n", "('K00766.01', 1.103)\n", "('K00767.01', 0.965)\n", "('K00769.01', 0.831)\n", "('K00001.01', 0.964)\n", "('K00771.01', 0.983)\n", "('K00772.01', 1.091)\n", "('K00773.01', 0.971)\n", "('K00775.01', 0.54)\n", "('K00776.01', 0.812)\n", "('K00777.01', 0.781)\n", "('K00778.01', 0.45)\n", "('K00072.01', 1.056)\n", "('K00780.01', 0.727)\n", "('K00781.01', 0.49)\n", "('K00782.01', 0.989)\n", "('K00783.01', 0.867)\n", "('K00784.01', 0.48)\n", "('K00785.01', 0.752)\n", "('K00786.01', 0.917)\n", "('K00787.01', 0.931)\n", "('K00788.01', 0.775)\n", "('K00790.01', 0.788)\n", "('K00791.01', 0.919)\n" ] } ], "source": [ "query = \"SELECT koi_name, radius FROM Star WHERE radius < 2;\"\n", "cursor.execute(query)\n", "sonuc = cursor.fetchall()\n", "print(\"koi_name\\t R_*\")\n", "for kayit in sonuc:\n", " print(kayit)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Görüldüğü gibi bu sorgu tüm satırlar arasından sadece koşulu sağlayan (Güneş yarıçapı biriminde 2 Güneş yarıçapından küçük) yıldızları getirmiştir." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "[Başa Dön](#Bu-derste-neler-öğreneceksiniz?)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Örnek 1: Büyük Yıldızlar\n", "\n", "`Star` tablosunda Güneş'ten büyük yıldızların yarıçapını ve sıcaklığını bulmak için bir SQL sorgusu yazınız. Tabloda yıldızların yarıçapı Güneş yarıçapı cinsinden verilmiştir." ] }, { "cell_type": "code", "execution_count": 12, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "[(1.064, 6167), (1.057, 5717), (1.958, 6227), (1.451, 6225), (1.029, 5881), (1.332, 6391), (1.991, 6350), (1.04, 5850), (1.046, 6031), (1.411, 5851), (1.073, 5803), (1.025, 6117), (1.046, 5468), (1.103, 6174), (1.091, 6144), (1.056, 5627)]\n" ] } ], "source": [ "cursor.execute(\"SELECT {:s} FROM {:s} WHERE {:s}\".\n", " format(\"radius,t_eff\",\"Star\",\"radius > 1\"))\n", "print(cursor.fetchall())" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "[Başa Dön](#Bu-derste-neler-öğreneceksiniz?)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# Şartlı Sorgular\n", "\n", "SQL'de karşılaştırmalar için $><$, $>$, $\\le$, $\\ge$ gibi Boolean operatörlerin yanı sıra `AND`, `OR`, `NOT` gibi koşulları birleştirme işlevi taşıyan operatörler de kullanılır.\n", "\n", "`SELECT` ifadesiyle basit bir örnek aşağıdaki şekilde verilebilir." ] }, { "cell_type": "code", "execution_count": 13, "metadata": { "scrolled": true }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "(False,)\n", "(True,)\n", "(False,)\n" ] } ], "source": [ "cursor.execute(\"SELECT 2 > 3;\")\n", "print(cursor.fetchone())\n", "cursor.execute(\"SELECT NOT 2 > 3;\")\n", "print(cursor.fetchone())\n", "cursor.execute(\"SELECT 2 = 3;\")\n", "print(cursor.fetchone())" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Sayısal karşılaştırma operatörleri, tırnak işaretleri kullanılarak verilen metin değişkenlerle de çalışır:" ] }, { "cell_type": "code", "execution_count": 14, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "(True,)\n" ] } ], "source": [ "cursor.execute(\"SELECT 'abc' < 'abcd';\")\n", "print(cursor.fetchone())" ] }, { "cell_type": "code", "execution_count": 15, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "(False,)\n" ] } ], "source": [ "cursor.execute(\"SELECT 'abc' = 'ABC';\")\n", "print(cursor.fetchone())" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "SQL büyük/küçük harfe duyarlı olmasa da metin değişkenler öyle değildir. Bir öznitelikte büyük mü yoksa küçük harflerin mi kullanıldığından emin olunamayan durumlarda, özniteliği dönüştürmek için `UPPER` veya `LOWER` işlevi kullanılabilir ve ardından karşılaştırma gerçekleştirilebilir:" ] }, { "cell_type": "code", "execution_count": 16, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "(True,)\n" ] } ], "source": [ "cursor.execute(\"SELECT UPPER('aBc') = 'ABC';\")\n", "print(cursor.fetchone())" ] }, { "cell_type": "code", "execution_count": 17, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "(True,)\n" ] } ], "source": [ "cursor.execute(\"SELECT LOWER('aBc') = 'abc';\")\n", "print(cursor.fetchone())" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "[Başa Dön](#Bu-derste-neler-öğreneceksiniz?)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Koşulların Bağlanması\n", "\n", "Bir `WHERE` ifadesi içindeki koşullar da birbirlerine `AND` ve `OR` ile bağlanabilir." ] }, { "cell_type": "code", "execution_count": 18, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "(True,)\n" ] } ], "source": [ "cursor.execute(\"SELECT 0 < 1 AND 1 < 2;\")\n", "print(cursor.fetchone())" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Bu özellik veri üzerinde daha komplike sorgular kurgulamak için kullanılır." ] }, { "cell_type": "code", "execution_count": 19, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "koi_name\t R_*\n", "('K00800.01', 1.064)\n", "('K00801.01', 1.057)\n", "('', 1.958)\n", "('K00010.01', 1.451)\n", "('K00116.01', 1.029)\n", "('K00115.01', 1.332)\n", "('K00002.01', 1.991)\n", "('K00752.01', 1.04)\n", "('K00755.01', 1.046)\n", "('K00117.02', 1.411)\n", "('K00112.02', 1.073)\n", "('K00760.01', 1.025)\n", "('K00764.01', 1.046)\n", "('K00766.01', 1.103)\n", "('K00772.01', 1.091)\n", "('K00072.01', 1.056)\n" ] } ], "source": [ "cursor.execute('''\n", "SELECT koi_name, radius FROM Star\n", "WHERE radius >= 1 AND radius <= 2;\n", "''')\n", "sonuc = cursor.fetchall()\n", "print(\"koi_name\\t R_*\")\n", "for kayit in sonuc:\n", " print(kayit)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Bu tür aralık sorguları SQL'de yaygın olarak kullanıldığı için SQL bu amaçla `BETWEEN` ifadesini sağlamıştır. Bu ifade kullanılarak okunması daha kolay sorgular yazılabilir:" ] }, { "cell_type": "code", "execution_count": 20, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "koi_name, R_*, T_eff\n", "('K00800.01', 1.064, 6167)\n", "('K00801.01', 1.057, 5717)\n", "('', 1.958, 6227)\n", "('K00010.01', 1.451, 6225)\n", "('K00116.01', 1.029, 5881)\n", "('K00115.01', 1.332, 6391)\n", "('K00002.01', 1.991, 6350)\n", "('K00752.01', 1.04, 5850)\n", "('K00755.01', 1.046, 6031)\n", "('K00117.02', 1.411, 5851)\n", "('K00112.02', 1.073, 5803)\n", "('K00760.01', 1.025, 6117)\n", "('K00764.01', 1.046, 5468)\n", "('K00766.01', 1.103, 6174)\n", "('K00772.01', 1.091, 6144)\n", "('K00072.01', 1.056, 5627)\n" ] } ], "source": [ "cursor.execute('''\n", "SELECT koi_name, radius, t_eff FROM Star\n", "WHERE radius BETWEEN 1 AND 2;\n", "''')\n", "sonuc = cursor.fetchall()\n", "print(\"koi_name, R_*, T_eff\")\n", "for kayit in sonuc:\n", " print(kayit)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "`BETWEEN` sorguları aranan aralığın ($1 \\le R_{\\star} \\le 2$) her iki ucunu da dahil ederek sonuç üretirler." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "[Başa Dön](#Bu-derste-neler-öğreneceksiniz?)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Örnek 2: Sıcak Yıldızlar\n", "\n", "`Star` tablosunda etkin sıcaklıkları 5000 ile 6000 K arasında olan tüm yıldızların `kepler_id` ve `t_eff` sütunundaki bilgileri getiren bir sorgu yazınız." ] }, { "cell_type": "code", "execution_count": 21, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Kepler ID, T_eff\n", "('K00800.01', 1.064, 6167)\n", "('K00801.01', 1.057, 5717)\n", "('', 1.958, 6227)\n", "('K00010.01', 1.451, 6225)\n", "('K00116.01', 1.029, 5881)\n", "('K00115.01', 1.332, 6391)\n", "('K00002.01', 1.991, 6350)\n", "('K00752.01', 1.04, 5850)\n", "('K00755.01', 1.046, 6031)\n", "('K00117.02', 1.411, 5851)\n", "('K00112.02', 1.073, 5803)\n", "('K00760.01', 1.025, 6117)\n", "('K00764.01', 1.046, 5468)\n", "('K00766.01', 1.103, 6174)\n", "('K00772.01', 1.091, 6144)\n", "('K00072.01', 1.056, 5627)\n" ] } ], "source": [ "# Solution\n", "cursor.execute(\"SELECT {:s} FROM {:s} WHERE {:s} BETWEEN {:d} AND {:d};\".\n", " format(\"kepler_id, t_eff\",\"Star\",\"t_eff\", 5000, 6000))\n", "print(\"Kepler ID, T_eff\")\n", "for kayit in sonuc:\n", " print(kayit)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "[Başa Dön](#Bu-derste-neler-öğreneceksiniz?)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# Bir Veritabanının Yapısının İncelenmesi\n", "\n", "Bir tabloyu sorgulamak için tablonun yapısının, sütunların adlarının ve ne tür bilgi içerdiklerinin bilinmesi gerekir. Bu amaçla SQL `\\d` (description) komutundan faydalanır. Örneğin PostgreSQL prompt'una (`psql`) aşağıdaki ifade yazılarak `Planet` tablosunun yapısı öğrenilebilir.\n", "\n", "```\n", "\\d Planet;\n", "```\n", "\n", "`psql` \\d komutu gibi kısayollar sağlasa da bunlar SQL'in parçası değildirler.. `psycopg2` ile sorgularda SQL'de bu amaçla kullanılan `information_schema` özniteliğinden aşağıdaki şekilde faydalanılır." ] }, { "cell_type": "code", "execution_count": 22, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "('period', 'double precision', 'YES')\n", "('radius', 'double precision', 'YES')\n", "('t_eq', 'integer', 'YES')\n", "('kepler_id', 'integer', 'NO')\n", "('status', 'character varying', 'NO')\n", "('koi_name', 'character varying', 'NO')\n", "('kepler_name', 'character varying', 'YES')\n" ] } ], "source": [ "cursor.execute('''\n", "SELECT column_name, data_type, is_nullable\n", "FROM information_schema.columns\n", "WHERE table_name = %s;\n", "''',(\"planet\",))\n", "for column in cursor.fetchall():\n", " print(column)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "`information_schema` (ya da `\\d`) her sütunun adını, ne tür veri sakladığını ve NULL veri içerip içeremeyeceği gibi bilgiler döndürür. Elde edilebilecek diğer bilgiler için [bkz.](https://docs.microsoft.com/en-us/sql/relational-databases/system-information-schema-views/columns-transact-sql?view=sql-server-ver15)\n", "\n", "Bazı sütunların NULL türünde veri içeremeyeceği görülmektedir. Bu konu önemli olduğundan kısaca bakmakta fayda vardır." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "[Başa Dön](#Bu-derste-neler-öğreneceksiniz?)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## SQL'de NULL Değeri\n", "\n", "Daha önceki derslerden bilindiği gibi NULLözel bir veri türüdür. Örnek olarak verilen `Planet` tablosunda tüm ötegezegnlerin `kepler_name` sütunları dolu değildir. Zira tüm gezegenler diğer yöntemlerle onaylanarak CONFIRMED statüsü almamış olabilir. Bu sütunlar NULL değeri alabilir. Ancak tüm çalışılan cisimlerin tekil birer (ing. unique) `kepler_id` numaraları bulunduğundan değerleri NULL olamaz.\n", "\n", "NULL ile boşluk ' ' aynı anlama gelmez:" ] }, { "cell_type": "code", "execution_count": 23, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "(None,)\n" ] } ], "source": [ "cursor.execute(\"SELECT '' = NULL;\")\n", "print(cursor.fetchone())" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Bu karşılaştırma SQL tarafından yapılamadığından `False` ya da `True` yerine `None` değeri döndürmüştür. Çünkü SQL tarafından NULL değeri bilinmemektedir. Ancak `IS` karşılaştırması yapılarak istenen amaca ulaşılabilir." ] }, { "cell_type": "code", "execution_count": 24, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "(True,)\n" ] } ], "source": [ "cursor.execute(\"SELECT NULL IS NULL;\")\n", "print(cursor.fetchone())" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Bununla, $=$ operatörüne sahip ilk sorgunun aslında bir $NULL$ değeri döndürdüğünü de görebilirsiniz:" ] }, { "cell_type": "code", "execution_count": 25, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "(True,)\n" ] } ], "source": [ "cursor.execute(\"SELECT ('' = NULL) IS NULL; \")\n", "print(cursor.fetchone())" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "[Başa Dön](#Bu-derste-neler-öğreneceksiniz?)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# Örnek 3: Onaylanmış Küçük Gezegenler\n", "\n", "`Planet` tablosundaki onaylanmış gezegenleri (`kepler_name` sütunu NULL olmayan ya da `status` sütunu CONFIRMED olan gezegenler) listeleyen bir SQL sorgusu yazınız. Sorgunuzun sonucunu sadece 1 ile 3 Yer yarıçapı gezegenler arasındakileri alacak şekilde sınırlandırınız. Tabloda gezegen yarıçapları Yer yarıçapı cinsinden verilmiştir." ] }, { "cell_type": "code", "execution_count": 26, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Kepler Name, R_p\n", "('Kepler-10 b', 1.45)\n", "('Kepler-466 c', 1.24)\n", "('Kepler-105 c', 1.88)\n", "('Kepler-106 c', 2.35)\n", "('Kepler-106 e', 2.58)\n", "('Kepler-107 c', 1.84)\n", "('Kepler-660 b', 2.52)\n", "('Kepler-226 c', 2.7)\n", "('Kepler-226 b', 1.59)\n", "('Kepler-226 d', 1.19)\n", "('Kepler-662 b', 1.54)\n", "('Kepler-663 b', 2.7)\n", "('Kepler-664 b', 2.71)\n", "('Kepler-228 b', 1.56)\n", "('Kepler-229 b', 2.41)\n", "('Kepler-665 b', 2.86)\n", "('Kepler-230 c', 2.13)\n", "('Kepler-666 b', 2.21)\n", "('Kepler-668 b', 2.54)\n", "('Kepler-671 b', 2.33)\n", "('Kepler-672 b', 2.7)\n", "('Kepler-52 c', 1.81)\n", "('Kepler-52 b', 2.33)\n", "('Kepler-52 d', 1.8)\n", "('Kepler-674 b', 1.32)\n", "('Kepler-675 b', 2.38)\n", "('Kepler-231 c', 1.73)\n", "('Kepler-231 b', 1.61)\n", "('Kepler-679 b', 2.69)\n", "('Kepler-680 b', 1.96)\n", "('Kepler-233 b', 2.71)\n", "('Kepler-233 c', 2.72)\n", "('Kepler-683 b', 1.97)\n", "('Kepler-684 b', 2.66)\n", "('Kepler-30 b', 1.91)\n", "('Kepler-235 b', 2.18)\n", "('Kepler-235 d', 1.99)\n", "('Kepler-235 e', 1.94)\n", "('Kepler-235 c', 1.22)\n", "('Kepler-689 b', 2.45)\n", "('Kepler-53 b', 2.9)\n", "('Kepler-53 d', 2.44)\n", "('Kepler-239 b', 2.36)\n", "('Kepler-239 c', 2.19)\n" ] } ], "source": [ "query = '''\n", "SELECT {} FROM {} WHERE {} = 'CONFIRMED' \n", "AND {} IS NOT NULL \n", "AND {} BETWEEN {} AND {}\n", "'''.format(\"kepler_name,radius\",\"Planet\",\"status\",\"kepler_name\",\"radius\",1,3)\n", "print(\"Kepler Name, R_p\")\n", "cursor.execute(query)\n", "for column in cursor.fetchall():\n", " print(column)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "[Başa Dön](#Bu-derste-neler-öğreneceksiniz?)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# SQL'de Veri İstatistikleri ve Birleştirme Fonksiyonları\n", "\n", "## COUNT Fonksiyonu\n", "\n", "Veritabanlarının boyutu büyüdükçe, istenen tüm verilerin tam bir tablosunu yazdırmak artık öğretici ve hatta mümkün olmayabilir. Bu durumlarda sonuçları sınırlandırmak için sorguyu daha spesifik hale getirme yoluna gidilebilir. Ancak hangi sonuçların beklendiği önceden bilinmiyorsa, verilerin boyutunu ve istatistiklerini karakterize etmek ve listelenen verileri sınırlandırmak için daha iyi bir yola ihtiyaç duyulur. \n", "\n", "İstenen verileri tablo formatında döndürmek yerine, yalnızca sonuç tablosundaki satır sayısını döndürmek için `COUNT` fonksiyonu kullanılabilir. Örneğin `Planet` tablosundaki tüm gezegenleri saymak için bir örnek aşağıda verilmiştir." ] }, { "cell_type": "code", "execution_count": 27, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "(96,)\n" ] } ], "source": [ "cursor.execute(\"SELECT COUNT(*) FROM Planet;\")\n", "print(cursor.fetchone()) " ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "`COUNT` daha önce görülen diğer tüm SQL ifadeleriyle birleştirilebilir. Örneğin yalnızca onaylanmış gezegenlerin sayısını almak için aşağıdaki gibi bir sorgu yazılabilir." ] }, { "cell_type": "code", "execution_count": 28, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "[(96,)]\n" ] } ], "source": [ "query = '''\n", "SELECT COUNT(*) FROM {}\n", "WHERE {} IS NOT NULL;\n", "'''.format('Planet','kepler_name')\n", "cursor.execute(query)\n", "print(cursor.fetchall())" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "[Başa Dön](#Bu-derste-neler-öğreneceksiniz?)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Diğer Birleştirme ve İstatistik Fonksiyonları\n", "\n", "`COUNT` SQL'in veri birleştirme (ing. aggregate) fonksiyonlarından yalnızca biridir. Bu tür fonksiyonlar yalnızca bir sonuç döndürdüğünden `psycopg2` 'de `cursor.fetchone()` fonksiyonuyla içerikleri bir demet değişkene (tuple) alınabilir. SQL \n", "\n", "SQL'in sunduğu diğer birleştirme ve istatistik fonksiyonlarında en çok kullanılan bazıları aşağıda listelenmiştir. Bu fonksiyonların hangi veriler veya hangi veri aralığına uygulanacağını bilmek, bir sorguyu daha spesifik hale getirmeye yardımcı olabilir. \n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
FonksiyonÇıktı
COUNTSorgu sonucu döndürülecek satır sayısı
MINBir sütundaki minimum değer\n", "
MAXBir sütundaki maksimum değer
SUMBir sütundaki değerlerin toplamı
AVGBir sütundaki değerlerin ortalaması
STDDEVBir sütundaki değerlerin standart sapması
\n", " \n", "Bu fonksiyonların kullanımına ilişkin bazı örnekler aşağıda sunulmuştur." ] }, { "cell_type": "code", "execution_count": 29, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "(0.49, 3791.05, 85.74760416666668)\n" ] } ], "source": [ "query = '''\n", "SELECT MIN({}), MAX({}), AVG({})\n", "FROM {};\n", "'''.format('radius','radius','radius','Planet')\n", "cursor.execute(query)\n", "print(cursor.fetchone())" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "`SELECT` ifadesinde `SUM` ve bölme (/) operatörlerini birlikte kullanarak `AVG` fonksiyonunun sonucuyla karşılaştırmak için aşağıdaki gibi bir sorgu yazılabilir." ] }, { "cell_type": "code", "execution_count": 30, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "(5503, Decimal('5503.3472222222222222'))\n" ] } ], "source": [ "query = '''\n", "SELECT SUM({})/COUNT(*), AVG({})\n", "FROM {};\n", "'''.format('t_eff','t_eff','Star')\n", "cursor.execute(query)\n", "print(cursor.fetchone())" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Bu iki sonucun birbirinden farklı olmasının nedeni `t_eff` özniteliğinin bir tamsayı olması ve SQL 'in \"/\" operatörüyle bu özniteliği içeren bölme işlemlerini tamsayı bölmesi şeklinde yapmasıdır. SQL'de tam sayı bölmesi aşağıdaki şekilde uygulanır. SQL'de \"/\" operatörüyle uygulanan bölme işlemi bu yapısyla Python 2.x versiyonlarındakine benzer şekilde çalışır." ] }, { "cell_type": "code", "execution_count": 31, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "(0, Decimal('0.50000000000000000000'))\n" ] } ], "source": [ "query = \"SELECT 1/2, 1/2.0\"\n", "cursor.execute(query)\n", "print(cursor.fetchone())" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "[Başa Dön](#Bu-derste-neler-öğreneceksiniz?)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# Sorgu Sonuçlarını Sıralama ve Limitleme\n", "\n", "Bir sorgu sonucunda elde edilen tablodaki sonuçlar yararlı bir şekilde sıralanmış olmayabilir; zira veritabanındaki girildikleri sırayla getirilirler.\n", "\n", "`ORDER BY` fonksiyonu istenen bir sütuna dayalı olarak istenen sırada sonuçların sıralanmasını sağlar. Örneğin `Planet` tablosundaki bir sorgunun gezegen yarıçapına (`radius`) azalan sırada (descending -- `DESC`) sıralanması için aşağıdaki sorgu yazılabilir." ] }, { "cell_type": "code", "execution_count": 32, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "KOI Numarasi, Yaricap (Yer Yaricapi)\n", "('K00759.01', 3791.05)\n", "('K00753.01', 3462.25)\n", "('K00799.01', 447.32)\n", "('K00772.01', 64.23)\n", "('K00744.01', 51.4)\n", "('K00754.01', 34.04)\n", "('K00002.01', 16.39)\n", "('K00010.01', 14.83)\n", "('K00771.01', 14.41)\n", "('K00806.02', 12.88)\n", "('K00001.01', 12.85)\n", "('K00767.01', 12.82)\n", "('K00802.01', 12.0)\n", "('K00760.01', 11.88)\n", "('K00830.01', 11.87)\n", "('K00809.01', 11.77)\n", "('K00801.01', 9.74)\n", "('K00806.01', 9.36)\n", "('K00797.01', 8.18)\n", "('K00777.01', 8.02)\n", "('K00813.01', 7.92)\n", "('K00791.01', 7.66)\n", "('K00865.01', 7.58)\n", "('K00776.01', 6.27)\n", "('K00764.01', 5.73)\n", "('K00782.01', 5.38)\n", "('K00780.02', 5.32)\n", "('K00757.01', 5.27)\n", "('K00783.01', 4.91)\n", "('K00766.01', 4.46)\n", "('K00756.01', 4.02)\n", "('K00787.02', 3.74)\n", "('K00800.01', 3.62)\n", "('K00757.02', 3.62)\n", "('K00811.01', 3.62)\n", "('K00829.03', 3.57)\n", "('K00800.02', 3.51)\n", "('K00115.01', 3.28)\n", "('K00788.01', 3.16)\n", "('K00747.01', 3.14)\n", "('K00752.02', 3.1)\n", "('K00752.01', 3.1)\n", "('K00781.01', 3.07)\n", "('K00787.01', 3.07)\n", "('K00756.02', 3.02)\n", "('K00829.01', 2.9)\n", "('K00758.01', 2.86)\n", "('K00810.01', 2.76)\n", "('K00804.01', 2.72)\n", "('K00790.02', 2.72)\n", "('K00790.01', 2.71)\n", "('K00755.01', 2.71)\n", "('K00773.01', 2.7)\n", "('K00749.01', 2.7)\n", "('K00751.01', 2.7)\n", "('K00785.01', 2.69)\n", "('K00795.01', 2.66)\n", "('K00116.02', 2.58)\n", "('K00765.01', 2.54)\n", "('K00746.01', 2.52)\n", "('K00814.01', 2.45)\n", "('K00829.02', 2.44)\n", "('K00757.03', 2.41)\n", "('K00780.01', 2.38)\n", "('K00835.01', 2.36)\n", "('K00116.01', 2.35)\n", "('K00775.02', 2.33)\n", "('K00769.01', 2.33)\n", "('K00762.01', 2.21)\n", "('K00835.02', 2.19)\n", "('K00812.01', 2.18)\n", "('K00759.02', 2.13)\n", "('K00812.02', 1.99)\n", "('K00794.01', 1.97)\n", "('K00786.01', 1.96)\n", "('K00812.03', 1.94)\n", "('K00806.03', 1.91)\n", "('K00115.02', 1.88)\n", "('K00117.02', 1.84)\n", "('K00775.01', 1.81)\n", "('K00775.03', 1.8)\n", "('K00784.01', 1.73)\n", "('K00784.02', 1.61)\n", "('K00749.02', 1.59)\n", "('K00756.03', 1.56)\n", "('K00750.01', 1.54)\n", "('K00072.01', 1.45)\n", "('K00778.01', 1.32)\n", "('K00112.02', 1.24)\n", "('K00812.04', 1.22)\n", "('K00749.03', 1.19)\n", "('K00116.04', 0.99)\n", "('K00116.03', 0.85)\n", "('K00115.03', 0.65)\n", "('K00082.04', 0.58)\n", "('K00082.05', 0.49)\n" ] } ], "source": [ "query = '''\n", "SELECT {} FROM {} \n", "ORDER BY {} DESC;\n", "'''.format(\"koi_name, radius\", \"Planet\", \"radius\")\n", "print(\"KOI Numarasi, Yaricap (Yer Yaricapi)\")\n", "cursor.execute(query)\n", "for record in cursor.fetchall():\n", " print(record)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Görüldüğü gibi tablo çok uzundur. Bu tablonun sadece bir kısmını görmek için `LIMIT` fonksiyonu kullanılabilir. Örneğin en küçük 5 gezegeni listelemek için azalan sıra (`DESC`) yerine artan sırada (`ASC`) sıralama istenerek sorgu sonuçları aşağıdaki şekilde limitlenebilir." ] }, { "cell_type": "code", "execution_count": 33, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "KOI Numarasi, Yaricap (Yer Yaricapi)\n", "('K00082.05', 0.49)\n", "('K00082.04', 0.58)\n", "('K00115.03', 0.65)\n", "('K00116.03', 0.85)\n", "('K00116.04', 0.99)\n" ] } ], "source": [ "query = '''\n", "SELECT {} FROM {} \n", "ORDER BY {} ASC\n", "LIMIT 5;\n", "'''.format(\"koi_name, radius\", \"Planet\", \"radius\")\n", "print(\"KOI Numarasi, Yaricap (Yer Yaricapi)\")\n", "cursor.execute(query)\n", "for record in cursor.fetchall():\n", " print(record)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Bu tür bir sınırlandırma büyük tablolarla karşılaşıldığında performansı önemli ölçüde etkileyecektir." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "[Başa Dön](#Bu-derste-neler-öğreneceksiniz?)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# Örnek 4: Gezegen İstatistikleri\n", "\n", "Onaylanmış gezegenler için \n", "\n", "* minimum yarıçap;\n", "* maksimum yarıçap;\n", "* ortalama yarıçap ve\n", "* yarıçapın standart sapma değerlerini \n", "\n", "veren bir SQL sorgusu yazınız." ] }, { "cell_type": "code", "execution_count": 34, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Minimum, Maksimum, Ortalama, St.Sapma\n", "(0.49, 3791.05, 85.74760416666668, 521.7809005819341)\n" ] } ], "source": [ "query = '''\n", "SELECT MIN(radius),MAX(radius),AVG(radius),STDDEV(radius) \n", "FROM Planet WHERE status IS NOT NULL\n", "'''\n", "print(\"Minimum, Maksimum, Ortalama, St.Sapma\")\n", "cursor.execute(query)\n", "for record in cursor.fetchall():\n", " print(record)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "[Başa Dön](#Bu-derste-neler-öğreneceksiniz?)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# Veriyi Gruplama\n", "\n", "`Planet` tablosunda aynı boyuta sahip (eşit yarıçaplı) birden fazla gezegen olup olmadığı öğrenilmek isteniyor olsun. Bu amaçla sonuçları aynı yarıçapın birden çok kez geçtiği gezegenleri görmek üzere sıralanmış basit bir sorgu yazılabilir." ] }, { "cell_type": "code", "execution_count": 35, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Kepler ID, Yaricap\n", "(10187017, 0.49)\n", "(10187017, 0.58)\n", "(9579641, 0.65)\n", "(8395660, 0.85)\n", "(8395660, 0.99)\n", "(10601284, 1.19)\n", "(4139816, 1.22)\n", "(10984090, 1.24)\n", "(11853255, 1.32)\n", "(11904151, 1.45)\n", "(10662202, 1.54)\n", "(10872983, 1.56)\n", "(10601284, 1.59)\n", "(12066335, 1.61)\n", "(12066335, 1.73)\n", "(11754553, 1.8)\n", "(11754553, 1.81)\n", "(10875245, 1.84)\n", "(9579641, 1.88)\n", "(3832474, 1.91)\n", "(4139816, 1.94)\n", "(12110942, 1.96)\n", "(2713049, 1.97)\n", "(4139816, 1.99)\n", "(11018648, 2.13)\n", "(4139816, 2.18)\n", "(5456651, 2.19)\n", "(11153539, 2.21)\n", "(11460018, 2.33)\n", "(11754553, 2.33)\n", "(8395660, 2.35)\n", "(5456651, 2.36)\n", "(11918099, 2.38)\n", "(10910878, 2.41)\n", "(5358241, 2.44)\n", "(4476123, 2.45)\n", "(10526549, 2.52)\n", "(11391957, 2.54)\n", "(8395660, 2.58)\n", "(3114167, 2.66)\n", "(12070811, 2.69)\n", "(10682541, 2.7)\n", "(10601284, 2.7)\n", "(11507101, 2.7)\n", "(10854555, 2.71)\n", "(12470844, 2.71)\n", "(12470844, 2.72)\n", "(3641726, 2.72)\n", "(3940418, 2.76)\n", "(10987985, 2.86)\n", "(5358241, 2.9)\n", "(10872983, 3.02)\n", "(12366084, 3.07)\n", "(11923270, 3.07)\n", "(10797460, 3.1)\n", "(10797460, 3.1)\n", "(10583066, 3.14)\n", "(12404086, 3.16)\n", "(9579641, 3.28)\n", "(3342970, 3.51)\n", "(5358241, 3.57)\n", "(3342970, 3.62)\n", "(10910878, 3.62)\n", "(4049131, 3.62)\n", "(12366084, 3.74)\n", "(10872983, 4.02)\n", "(11403044, 4.46)\n", "(12020329, 4.91)\n", "(10910878, 5.27)\n", "(11918099, 5.32)\n", "(11960862, 5.38)\n", "(11304958, 5.73)\n", "(11812062, 6.27)\n", "(6862328, 7.58)\n", "(12644822, 7.66)\n", "(4275191, 7.92)\n", "(11818800, 8.02)\n", "(3115833, 8.18)\n", "(3832474, 9.36)\n", "(3351888, 9.74)\n", "(3935914, 11.77)\n", "(5358624, 11.87)\n", "(11138155, 11.88)\n", "(3453214, 12.0)\n", "(11414511, 12.82)\n", "(11446443, 12.85)\n", "(3832474, 12.88)\n", "(11465813, 14.41)\n", "(6922244, 14.83)\n", "(10666592, 16.39)\n", "(10848459, 34.04)\n", "(10480982, 51.4)\n", "(11493732, 64.23)\n", "(3246984, 447.32)\n", "(10811496, 3462.25)\n", "(11018648, 3791.05)\n" ] } ], "source": [ "query = '''\n", "SELECT {} FROM {}\n", "ORDER BY {} ASC;\n", "'''.format('kepler_id, radius','Planet','radius')\n", "cursor.execute(query)\n", "print(\"Kepler ID, Yaricap\")\n", "for record in cursor.fetchall():\n", " print(record)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Ancak bu tür bir tabloda birden fazla kez geçen yarıçap değeri bulmak zor olduğu gibi tablonun daha büyük olması durumunda olanaksız hale de gelebilir. Böyle durumlarda `GROUP BY` fonksiyonundan yararlanarak birbiriyle aynı bilgiler üzerindne gruplandırmalar yapılarak sayma (`COUNT`) gibi birleştirme fonksiyonlarından faydalanılabilir. Bir önceki sorguda karşılaşılan problem aşağıdaki gibi bir sorguyla çözülebilir." ] }, { "cell_type": "code", "execution_count": 36, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Yaricap, Sayi\n", "(2.52, 1)\n", "(2.76, 1)\n", "(5.38, 1)\n", "(2.41, 1)\n", "(2.36, 1)\n", "(12.85, 1)\n", "(1.32, 1)\n", "(3.14, 1)\n", "(6.27, 1)\n", "(2.71, 2)\n", "(3.57, 1)\n", "(7.92, 1)\n", "(2.44, 1)\n", "(8.02, 1)\n", "(2.9, 1)\n", "(14.83, 1)\n", "(7.58, 1)\n", "(8.18, 1)\n", "(5.27, 1)\n", "(2.58, 1)\n", "(1.81, 1)\n", "(2.45, 1)\n", "(3.74, 1)\n", "(12.0, 1)\n", "(2.66, 1)\n", "(4.02, 1)\n", "(1.88, 1)\n", "(14.41, 1)\n", "(16.39, 1)\n", "(3.16, 1)\n", "(4.91, 1)\n", "(11.88, 1)\n", "(4.46, 1)\n", "(2.86, 1)\n", "(3.02, 1)\n", "(2.19, 1)\n", "(3.28, 1)\n", "(1.56, 1)\n", "(2.54, 1)\n", "(0.49, 1)\n", "(1.8, 1)\n", "(2.35, 1)\n", "(3462.25, 1)\n", "(447.32, 1)\n", "(1.96, 1)\n", "(51.4, 1)\n", "(12.82, 1)\n", "(1.99, 1)\n", "(0.65, 1)\n", "(3.1, 2)\n", "(1.24, 1)\n", "(2.38, 1)\n", "(0.85, 1)\n", "(1.91, 1)\n", "(1.54, 1)\n", "(2.7, 3)\n", "(2.33, 2)\n", "(11.87, 1)\n", "(7.66, 1)\n", "(2.13, 1)\n", "(12.88, 1)\n", "(2.18, 1)\n", "(2.72, 2)\n", "(3.62, 3)\n", "(2.21, 1)\n", "(1.73, 1)\n", "(1.22, 1)\n", "(1.61, 1)\n", "(1.59, 1)\n", "(3.51, 1)\n", "(64.23, 1)\n", "(9.74, 1)\n", "(9.36, 1)\n", "(5.73, 1)\n", "(34.04, 1)\n", "(11.77, 1)\n", "(0.99, 1)\n", "(1.45, 1)\n", "(0.58, 1)\n", "(2.69, 1)\n", "(1.94, 1)\n", "(5.32, 1)\n", "(1.97, 1)\n", "(3791.05, 1)\n", "(1.84, 1)\n", "(3.07, 2)\n", "(1.19, 1)\n" ] } ], "source": [ "query = '''\n", "SELECT {}, COUNT({}) \n", "FROM {} \n", "GROUP BY {};\n", "'''.format('radius','koi_name','Planet','radius')\n", "print(\"Yaricap, Sayi\")\n", "cursor.execute(query)\n", "for record in cursor.fetchall():\n", " print(record)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Bu sorgu bir öncekine göre daha faydalı, ancak hala çok uzundur. Bu sorgunun sonuçlarını daha da sınırlandırmakta fayda vardır." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "[Başa Dön](#Bu-derste-neler-öğreneceksiniz?)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Gruplandırılmış Veride Şartlı Sorgulama\n", "\n", "Bu sorgunun sonucundaki tabloyu gezegen sayısının birden büyük olduğu sonuçlarla sınırlamak istendiğinde, `WHERE` ifadesi kullanılarak bir koşul eklenebilir. Aşağıdaki sorgu bu amaç için ilk bakışta ideal gözükebilir:\n", "\n", "```\n", "SELECT radius, COUNT(koi_name) \n", "FROM Planet \n", "WHERE COUNT(koi_name) > 1\n", "GROUP BY radius;\n", "```\n", "\n", "Ancak `WHERE` ifadesi `GROUP BY` fonksiyonundan önce geçtiği için gruplandırılmış tablounun `COUNT` fonsiyonuna erişimi kalmamıştır.\n", "\n", "`COUNT` gibi birleştirme fonksiyonlarına `GROUP BY` ifadeleriyle gruplanan tabloların erişimi için`WHERE` yerine `HAVING` ifadesi kullanılır." ] }, { "cell_type": "code", "execution_count": 37, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "(2.71, 2)\n", "(3.1, 2)\n", "(2.7, 3)\n", "(2.33, 2)\n", "(2.72, 2)\n", "(3.62, 3)\n", "(3.07, 2)\n" ] } ], "source": [ "query = '''\n", "SELECT {}, COUNT({}) \n", "FROM {} \n", "GROUP BY {}\n", "HAVING COUNT({}) > {};\n", "'''.format('radius','koi_name','Planet','radius','koi_name',1)\n", "cursor.execute(query)\n", "for record in cursor.fetchall():\n", " print(record)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "[Başa Dön](#Bu-derste-neler-öğreneceksiniz?)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Örnek 5: Çoklu Gezegen Sistemleri\n", "\n", "`Planet` tablosunda yer alan çok gezegenli sistemlerdeki gezegen sayısını verecek bir sorgu yazınız. Aynı barınak yıldızın etrafında dolanan gezegenler aynı kepler_id değerine, ancak farklı koi_name değerlerine sahip olacaktır.\n", "\n", "Sorgunuz, her satırda yıldızın `kepler_id` değerini ve o yıldızın etrafında dönen gezegenlerin sayısını (yani bu `kepler_id` değerini paylaşan gezegenlerin sayısını) içeren bir tablo döndürmelidir.\n", "\n", "Sonuçlarınız sadece çoklu gezegen sistemleri (birden fazla gezegen içeren) sıralanmış olmalıı ve artan gezegen sayısına göre sıralanmalıdır." ] }, { "cell_type": "code", "execution_count": 38, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Kepler ID, Gezegen Sayisi\n", "(8395660, 4)\n", "(4139816, 4)\n", "(3832474, 3)\n", "(5358241, 3)\n", "(10910878, 3)\n", "(9579641, 3)\n", "(10872983, 3)\n", "(10601284, 3)\n", "(11754553, 3)\n", "(12066335, 2)\n", "(11918099, 2)\n", "(10797460, 2)\n", "(12366084, 2)\n", "(3342970, 2)\n", "(11018648, 2)\n", "(10187017, 2)\n", "(5456651, 2)\n", "(12470844, 2)\n" ] } ], "source": [ "query = '''\n", "SELECT {}, COUNT({}) FROM {}\n", "GROUP BY {}\n", "HAVING COUNT({}) > {}\n", "ORDER BY COUNT({}) DESC;\n", "'''.format('kepler_id','koi_name','Planet','kepler_id','koi_name',1,'koi_name')\n", "print(\"Kepler ID, Gezegen Sayisi\")\n", "cursor.execute(query)\n", "for record in cursor.fetchall():\n", " print(record)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "[Başa Dön](#Bu-derste-neler-öğreneceksiniz?)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# Kaynaklar\n", "\n", "1. [PostgreSQL Dokümantasyonu](https://www.postgresql.org/docs/)\n", "2. [psycopg2 Dokümantasyonu](https://www.psycopg.org/docs/)\n", "3. [sqlalchemy Dokümantasyonu](https://docs.sqlalchemy.org/en/14/)\n", "4. [Learning SQL, 3rd Edition, Alan Beaulieu, O'Reilly, 2020](https://www.oreilly.com/library/view/learning-sql-3rd/9781492057604/)\n", "5. [Data Driven Astronomy Course by University of Sydney in coursera.org](https://www.coursera.org/learn/data-driven-astronomy)" ] } ], "metadata": { "kernelspec": { "display_name": "Python 3 (ipykernel)", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.10.6" } }, "nbformat": 4, "nbformat_minor": 5 }