python sql

QUERY = '''
select name, birthdate from animals where species = 'gorilla';
'''

+———+————+
| name | birthdate |
+=========+============+
| Max | 2001-04-23 |
| Dave | 1988-09-29 |
| Becky | 1979-07-04 |
| Liz | 1998-06-12 |
| George | 2011-01-09 |
| George | 1998-05-18 |
| Wendell | 1982-09-24 |
| Bjorn | 2000-03-07 |
| Kristen | 1990-04-25 |
+———+————+

select name, birthdate from animals where species = ‘gorilla’ and name = ‘Max’

否定

QUERY = '''
select name, birthdate from animals where species != 'gorilla' and name != 'Max';
'''

between文

QUERY = '''
select name from animals where species = 'llama' AND birthdate between '1995-01-01' AND '1998-12-31';
'''

query

QUERY = "select max(name) from animals;"

QUERY = "select * from animals limit 10;"

QUERY = "select * from animals where species = 'orangutan' order by birthdate;"

QUERY = "select name from animals where species = 'orangutan' order by birthdate desc;"

QUERY = "select name, birthdate from animals order by name limit 10 offset 20;"

QUERY = "select species, min(birthdate) from animals group by species;"

QUERY = '''
 select name, count(*) as num from animals
 group by name
 order by num desc
 limit 5;
 '''

Limit count Offset skip
limit is how many rows to return
offset is how far into the results to start

Order by columns DESC
columns is which columns to sort by, separated with commas
DESC is sort in reverse order(descending)

Group by columns
columns is which columns to use as groupings when aggregating

QUERY = "select count(*) as num, species from animals group by species order by num desc;"

insert

INSERT_QUERY = '''
insert into animals (name, species, birthdate) values ('yoshi', 'opposum', '2016-12-13');
'''

what’s going on?

this function mean a*b, z is x time multiply.

def naive(a, b):
    x = a; y = b
    z = 0
    while x > 0:
        z = z + y
        x = x - 1
    return z

print(naive(4, 5))
def russian(a, b):
    x = a; y = b
    z = 0
    while x > 0:
        if x % 2 == 1: z = z + y
        y = y << 1
        x = x >> 1
    return z

print(russian(5, 5))

make new instance

your-name

import media

toy_story = media.Movie("Toy Story",
                        "A story of a boy and toys that come to life",
                        "http://upload.wikimedia.org/wikipedia/en/1/13/Toy_Story.jpg",
                        "https://www.youtube.com/watch?v=vwyZH85NQC4")
# print(toy_story.storyline)

avatar = media.Movie("Avatar",
                     "A marine on an alien planet",
                     "http://upload.wikimedia.org/wikipedia/id/b/b0/Avatar-Teaser-Poster.jpg",
                     "http://www.youtube.com/watch?v=-9ceBgWV8io")

print(avatar.storyline)
# avatar.show_trailer()

kiminonawa = media.Movie("Kimino na wa",
                         "Your Name is a 2016 Japanese anime fantasy film written and directed by Makoto Shinkai",
                         "https://myanimelist.cdn-dena.com/images/anime/7/79999.webp",
                         "https://www.youtube.com/watch?v=k4xGqY5IDBE")
kiminonawa.show_trailer()

media.py

import webbrowser

class Movie():
    def __init__(self, movie_title, movie_storyline, poster_image, trailer_youtube):
        self.title = movie_title
        self.storyline = movie_storyline
        self.poster_image_url = poster_image
        self.trailer_youtube_url = trailer_youtube

    def show_trailer(self):
        webbrowser.open(self.trailer_youtube_url)

%e7%84%a1%e9%a1%8c

import fresh_tomatoes
import media

brother = media.Movie("brother",
                        "Takeshi Kitano plays Yamamoto, a lone yakuza officer. Defeated in a war with a rival family, his boss killed, he heads to Los Angeles, California.",
                        "https://upload.wikimedia.org/wikipedia/en/a/a1/BrotherKitano.jpg",
                        "https://www.youtube.com/watch?v=AgjDYR6-DTU")
# print(toy_story.storyline)
outrage = media.Movie("Outrage",
                     "The film begins with a sumptuous banquet at the opulent estate of the Grand Yakuza leader Sekiuchi (Soichiro Kitamura), boss of the Sanno-kai, a huge organized crime syndicate controlling the entire Kanto region, and he has invited the many Yakuza leaders under his control.",
                     "https://upload.wikimedia.org/wikipedia/en/f/f4/Outrage-2010-poster.png",
                     "https://www.youtube.com/watch?v=EXLuvZY8bMU")

#print(avatar.storyline)
# avatar.show_trailer()

#kiminonawa = media.Movie("Kimino na wa",
#                         "Your Name is a 2016 Japanese anime fantasy film written and directed by Makoto Shinkai",
#                         "https://myanimelist.cdn-dena.com/images/anime/7/79999.webp",
#                         "https://www.youtube.com/watch?v=k4xGqY5IDBE")
#kiminonawa.show_trailer()

kids = media.Movie("Kids Return",
                             "The movie is about two high school dropouts, Masaru (Ken Kaneko) and Shinji (Masanobu Ando)",
                             "https://upload.wikimedia.org/wikipedia/en/a/a7/KidsReturnPoster.jpg",
                             "https://www.youtube.com/watch?v=MvY6JCFCrQk")

ratatouille = media.Movie("Ratatouille",
                             "storyline",
                             "http://upload.wikimedia.org/wikipedia/en/5/50/RatatouillePoster.jpg",
                             "https://www.youtube.com/watch?v=3YG4h5GbTqU")

midnight_in_paris = media.Movie("Midnight in Paris",
                             "storyline",
                             "http://upload.wikimedia.org/wikipedia/en/9/9f/Midnight_in_Paris_Poster.jpg",
                             "https://www.youtube.com/watch?v=JABOZpoBYQE")

hunger_games = media.Movie("Hunger Games",
                             "storyline",
                             "http://upload.wikimedia.org/wikipedia/en/4/42/HungerGamesPoster.jpg",
                             "https://www.youtube.com/watch?v=PbA63a7H0bo")

movies = [brother, outrage, kids, ratatouille, midnight_in_paris, hunger_games]
fresh_tomatoes.open_movies_page(movies)

documentation

>>> import turtle
>>> turtle.Turtle.__doc__
'RawTurtle auto-creating (scrolled) canvas.\n\n    When a Turtle object is created or a function derived from some\n    Turtle method is called a TurtleScreen object is automatically created.\n 

inheritance

class Parent():
    def __init__(self, last_name, eye_color):
        print("parent constructor called")
        self.last_name = last_name
        self.ey_color = eye_color

class Child(Parent):
    def __init__(self, last_name, eye_color, number_of_toys):
        print("child constructor called")
        Parent.__init__(self, last_name, eye_color)
        self.number_of_toys = number_of_toys
        

#billy_cyrus = Parent("Cyrus","blue")
#print(billy_cyrus.last_name)

myley_crus = Child("cryus", "blue", 5)
print(myley_crus.last_name)
print(myley_crus.number_of_toys)

curse word search

http://www.wdylike.appspot.com/ is a site judge it contain curse word in it.

import urllib

def read_text():
   quotes = open(r"C:\Users\hoge\Desktop\controller\movie_quote.txt")
   contents_of_file = quotes.read()
   # print(contents_of_file)
   quotes.close()
   check_profanity(contents_of_file)

def check_profanity(text_to_check):
    connection = urllib.urlopen("http://www.wdylike.appspot.com/?q="+text_to_check)
    output = connection.read()
    # print(output)
    connection.close()
    if "true" in output:
        print("Profanity Alert!")
    elif "false" in output:
        print("This document has no curse words")
    else:
        print("Could not scan the document properly.")
    
read_text()

when you write python, google style guide would help coding.
https://google.github.io/styleguide/pyguide.html

draw star

star

import turtle

def draw_star(some_turtle):
    for i in range(1, 6):
        some_turtle.forward(100)
        some_turtle.right(144)

def draw_art():
    window = turtle.Screen()
    window.bgcolor("red")
    
    brad = turtle.Turtle()
    brad.shape("arrow")
    brad.color("blue")    
    brad.speed(5)
    for i in range(1, 11):
        draw_star(brad)
        brad.right(36)

    # angie = turtle.Turtle()
    # angie.shape("arrow")
    # angie.color("blue")
    # angie.circle(100)

    # triangle = turtle.Turtle()
    # angie.shape("arrow")
    # angie.color("blue")
    
    # for x in range(0, 3):
    #    brad.forward(100)
    #    brad.right(240)

    window.exitonclick()

draw_art()

open file and read the content from the file.
python library: https://docs.python.org/2/library/functions.html#open

below code “quotes” indicate the object another word instance.

def read_text():
   quotes = open(r"C:\Users\hoge\Desktop\controller\movie_quote.txt")
   contents_of_file = quotes.read()
   print(contents_of_file)
   quotes.close()
   
read_text()

using python build-in function

def multi_func(a, b):
    return a*b

items = [1,3, 5, 7]
def reduce_value():
    value = reduce(multi_func, items)
    print(value)

reduce_value()

105
>>>

python time alert

time alert program which call you tube video.

import time
import webbrowser

total_breaks = 3
break_count = 0

print("this program start on"+time.ctime())
while(break_count < total_breaks):
    time.sleep(2*60*60)
    webbrowser.open("https://www.youtube.com/watch?v=PDSkFeMVNFs")
    break_count = break_count + 1

get a file from computer and rename file name.In this case, remove the number of file name.

import os
def rename_files():
    #(1)get file names from a folder
    file_list = os.listdir(r"C:\Users\prank")
    #print(file_list)
    save_path = os.getcwd()
    print(save_path)
    os.chdir(r"C:\Users\prank")
    #(2)for each file, rename filename
    for file_name in file_list:
       os.rename(file_name, file_name.translate(None, "0123456789"))
    os.chdir(save_path)

rename_files()

crawling page rank

def crawl_web(seed): # returns index, graph of outlinks
    tocrawl = [seed]
    crawled = []
    graph = {}  # <url>:[list of pages it links to]
    index = {} 
    while tocrawl: 
        page = tocrawl.pop()
        if page not in crawled:
            content = get_page(page)
            add_page_to_index(index, page, content)
            outlinks = get_all_links(content)
            graph[page] = outlinks
            union(tocrawl, outlinks)
            crawled.append(page)
    return index, graph
def ancestors(genealogy, person):
  if person in genealogy:
    parents = genealogy[person]
    result = parents
    for parent in parents:
      result = result + ancestors(genealogy, parent)
    return result
    
  return []

Blaise Pascal Triangle

def make_next_row(row):
  result = []
  prev = 0
  for e in row:
    result.append(e + prev)
    prev = e
  result.append(prev)
  return result

def triangle(n):
  result = []
  current = [1]
  for unused in range(0, n):
    result.append(current)
    current = make_next_row(current)
    
  return result

fibonacci

-base cases
fibonacci(0)=0
fibonacci(1)=1

-recursive case
fibonacci(n) = fibonacci(n-1)+fibonacci(n-2)
n > 1

A procedure, fibonacci, that takes a natural number as its input, and returns the value of that fibonacci number.

def fibonacci(n):
    if n == 0:
      return 0
    if n == 1:
      return n
    return fibonacci(n-1) + fibonacci(n-2)

fibonacci has interesting feature below
fibo(x) = fibo(x-n-1)

More faster fibonacci procedure using for loop.

def fibonacci(n):
    current = 0
    after = 1
    for i in range(0, n):
      current, after = after, current + after
    return current

mass_of_earth = 5.9722 * 10**24
mass_of_rabbit = 2

n = 1
while fibonacci(n) * mass_of_rabbit < mass_of_earth:
  n = n + 1
print n, fibonacci(n)

- Ranking web pages as popularity
popularity(p) = # of people who are friends with p
popularity(p) = Σ popularity(f), f E friends of p

def popularity(p):
score = 0
for f in friends(p):
score = score + popularity(f)
return score

Defining Procedures Recursively

factorial(n) = n*(n-1)*(n-2)*…1

factorial(n)=1
factorial(n)=n*factorial(n-1)

A procedure, factorial, that takes a natural number as its input, and returns the number of ways to arrange the input number of items.

def factorial(n):
 if n == 0:
   return 1
 else:
   return n * factorial(n-1)

Palindromes:match the first and last string, such as level.
A procedure is_palindrome, that takes as input a string, and returns a Boolean indicating if the input string is a palindrome.

def is_palindrome(s):
  if s == "":
    return True
  else:
    if s[1] == s[-1]:
        return is_palindrome(s[1:-1])
    else:
        return false

Another way, using for loop, to write palindrome procedure.

def iter_palindrome(s):
  for i in range(0, len(s)/2):
    if s[i] != s[-(i + 1)]:
      return False
  return True

Dictionary

String: sequence of characters, immutable
ex. ‘hello’, s[i] is i th character in S
List: list of elements, mutable
ex. [‘alpha’, 23], p[i] is i th element of p
Dictionary: set of pairs, mutable
ex. {‘hydrogen’: 1, ‘helium’: 23, d[k], k is whatever the key value
ex. d[k] = v, is similar to the update.

Here is a example to use dictionary

elements = {'hydrogen':1, 'helium':2, 'carbon':6 }
elements['lithimu'] = 3
elements['nitrogen'] = 8
elements['nitrogen'] = 7
print elements['nitrogen']

population = {'Shanghai':17.8, 'Istanbul':13.3, 'Karachi':13.0, 'Mumbai':12.5 }

another example

elements = {}
elements['H'] = {'name': 'Hydrogen', 'number': 1, 'weight': 1.00794}
elements['He'] = {'name': 'Helium', 'number': 2, 'weight': 4.002602, 'noble gas': True}
print elements['He']['name']