PythonでSQL

sql_to_python.py

# %%
# Doctest
import doctest

from matplotlib.style import context

def add(a, b):
    """Return the sum of a and b.

    >>> add(2, 2)
    4
    """
    sum = a
    return sum

#doctest.testmod(verbose=True)

# %%
# Unittest
import unittest

class TestFunction(unittest.TestCase):
    def test_add(self):
        self.assertEquals(add(2,2), 2)

def add(a, b):
    """Return the sum of a and b."""
    sum = a
    return sum

#unittest.main(argv=[''], verbosity=2, exit=False)

# %%
# Map
# https://www.learnpython.org/en/Map,_Filter,_Reduce
my_pets = ['alfred', 'tabitha', 'william', 'arla']

uppered_pets = list(map(str.upper, my_pets))

print(uppered_pets)

circle_areas = [3.56773, 5.57668, 4.00914, 56.24241, 9.01344, 32.00013]

result = list(map(round, circle_areas, range(1, 7)))

print(result)

circle_areas = [3.56773, 5.57668, 4.00914, 56.24241, 9.01344, 32.00013]

result = list(map(round, circle_areas, range(1, 3)))

print(result)

my_strings = ['a', 'b', 'c', 'd', 'e']
my_numbers = [1, 2, 3, 4, 5]

results = list(zip(my_strings, my_numbers))

print(results)

my_strings = ['a', 'b', 'c', 'd', 'e']
my_numbers = [1, 2, 3, 4, 5]

results = list(map(lambda x, y: (x, y), my_strings, my_numbers))

print(results)

# %%
# Filter
scores = [66, 90, 68, 59, 76, 60, 88, 74, 81, 65]

def is_A_student(score):
    return score > 75

over_75 = list(filter(is_A_student, scores))

print(over_75)

dromes = ("demigod", "rewire", "madam", "freer", "anutforajaroftuna", "kiosk")

palindromes = list(filter(lambda word: word == word[::-1], dromes))

print(palindromes)

# %%
# Reduce
from functools import reduce

numbers = [3, 4, 6, 9, 34, 12]

def custom_sum(first, second):
    return first + second

result = reduce(custom_sum, numbers)
print(result)

numbers = [3, 4, 6, 9, 34, 12]

def custom_sum(first, second):
    return first + second

result = reduce(custom_sum, numbers, 10)
print(result)

# %%
# SQL入門編1
# https://www.canit.jp/intro/sql/sql1-1.html
import unittest
import pandas

students = [
  {
    "id": 1,
    "name": "佐藤",
    "age": 12,
    "gender": "男",
    "test_score": 89,
    "birthday": "2000-05-09",
  },
  {
    "id": 2,
    "name": "鈴木",
    "age": 9,
    "gender": "男",
    "test_score": 64,
    "birthday": "1997-04-09",
  },
  {
    "id": 3,
    "name": "高橋",
    "age": 8,
    "gender": "女",
    "test_score": 90,
    "birthday": "1996-03-11",
  },
  {
    "id": 4,
    "name": "田中",
    "age": 10,
    "gender": "男",
    "test_score": 45,
    "birthday": "1998-04-05",
  },
  {
    "id": 5,
    "name": "伊藤",
    "age": 11,
    "gender": "女",
    "test_score": 25,
    "birthday": "1999-01-13",
  },
  {
    "id": 6,
    "name": "渡辺",
    "age": 8,
    "gender": "女",
    "test_score": 50,
    "birthday": "1996-02-14",
  },
  {
    "id": 7,
    "name": "山本",
    "age": 11,
    "gender": "男",
    "test_score": 55,
    "birthday": "1999-03-30",
  },
  {
    "id": 8,
    "name": "中村",
    "age": 12,
    "gender": "男",
    "test_score": 100,
    "birthday": "2000-06-01",
  },
  {
    "id": 9,
    "name": "小林",
    "age": 10,
    "gender": "女",
    "test_score": 99,
    "birthday": "1998-07-28",
  },
  {
    "id": 10,
    "name": "加藤",
    "age": 10,
    "gender": "女",
    "test_score": 14,
    "birthday": "1998-08-25",
  },
]
class TestSelect(unittest.TestCase):
    def test_select_from(self):
        result = select_from(students)
        print(pandas.DataFrame(result))
        self.assertEqual(len(result),10)

    def test_select_id_from(self):
        result = select_id_from(students)
        print(pandas.DataFrame(result, columns=['id']))
        self.assertEqual(len(result),10)

    def test_select_id_test_score_from(self):
        result = select_id_test_score_from(students)
        print(pandas.DataFrame(result, columns=['id', 'test_score']))
        self.assertEqual(len(result),10)

def select_from(students):
    """SELECT * FROM Student"""
    return list(map(lambda student: student, students))

def select_id_from(students):
    """SELECT id FROM Student"""
    return list(map(lambda student: student["id"], students))

def select_id_test_score_from(students):
    """SELECT id,test_score FROM Student"""
    return list(map(lambda student: (student["id"], student["test_score"]), students))

unittest.main(argv=[''], verbosity=2, exit=False)

# %%
# SQL入門編2
# https://www.canit.jp/intro/sql/sql1-2.html

class TestWhere(unittest.TestCase):
    def test_select_from_by_age(self):
        result = select_from_by_age(students, 12)
        print(pandas.DataFrame(result))
        self.assertEqual(len(result),2)

    def test_select_from_by_gender(self):
        result = select_from_by_gender(students, "女")
        print(pandas.DataFrame(result))
        self.assertEqual(len(result),5)

    def test_select_from_by_grater_age(self):
        result = select_from_by_grater_age(students, 10)
        print(pandas.DataFrame(result))
        self.assertEqual(len(result),4)

    def test_select_from_by_less_or_equal_test_score(self):
        result = select_from_by_less_or_equal_test_score(students, 50)
        print(pandas.DataFrame(result))
        self.assertEqual(len(result),4)

def select_from_by_age(students, age):
    """SELECT * FROM Student WHERE age=12"""
    return list(filter(lambda student: student["age"] == age, students))

def select_from_by_gender(students, gender):
    """SELECT * FROM Student WHERE gender='女'"""
    return list(filter(lambda student: student["gender"] == gender, students))

def select_from_by_grater_age(students, age):
    """SELECT * FROM Student WHERE age>10"""
    return list(filter(lambda student: student["age"] > age, students))

def select_from_by_less_or_equal_test_score(students, test_score):
    """SELECT * FROM Student WHERE test_score<=50"""
    return list(filter(lambda student: student["test_score"] <= test_score, students))

unittest.main(argv=[''], verbosity=2, exit=False)

# %%
# SQL入門編3
# https://www.canit.jp/intro/sql/sql1-3.html
import re

class TestLike(unittest.TestCase):
  def test_select_from_by_birthday_like(self):
    result = select_from_by_birthday_like(students, "2000")
    print(pandas.DataFrame(result))
    self.assertEqual(len(result),2)

  def test_select_from_by_birthday_like_start(self):
    result = select_from_by_birthday_like_start(students, "1998-")
    print(pandas.DataFrame(result))
    self.assertEqual(len(result),3)

  def test_select_from_by_birthday_like_end(self):
    result = select_from_by_name_like_end(students, "藤")
    print(pandas.DataFrame(result))
    self.assertEqual(len(result),3)

  def test_select_from_by_test_score_is_null(self):
    result = select_from_by_test_score_is_null(students)
    print(pandas.DataFrame(result))
    self.assertEqual(len(result),0)

  def test_select_from_by_test_score_is_not_null(self):
    result = select_from_by_test_score_is_not_null(students)
    print(pandas.DataFrame(result))
    self.assertEqual(len(result),10)

def select_from_by_birthday_like(data, birthday):
    """SELECT * FROM Student WHERE birthday LIKE '%2000-%'"""
    return [i for i in data if re.search("\\s*" + birthday + "\\s*", i["birthday"])]

def select_from_by_birthday_like_start(students, birthday):
    """SELECT * FROM Student WHERE birthday LIKE '1998-%'"""
    return list(filter(lambda student: student["birthday"].startswith(birthday), students))

def select_from_by_name_like_end(students, name):
    """SELECT * FROM Student WHERE name LIKE '%藤'"""
    return list(filter(lambda student: student["name"].endswith(name), students))

def select_from_by_test_score_is_null(students):
    """SELECT * FROM Student WHERE test_score IS NULL"""
    return list(filter(lambda student: student["test_score"] is None, students))

def select_from_by_test_score_is_not_null(students):
    """SELECT * FROM Student WHERE test_score IS NOT NULL"""
    return list(filter(lambda student: student["test_score"] is not None, students))

unittest.main(argv=[''], verbosity=2, exit=False)