portfolio-post

In [24]:
import sqlalchemy as db
import pymysql
import pandas as pd

engine = db.create_engine("mysql+pymysql://root:password@localhost/sakila?host=localhost?port=3306")  # connect to server

# PostgreSQL
#engine = create_engine('postgresql://usr:pass@localhost:5432/sqlalchemy')
In [25]:
sql_query = """
select * from actor
"""
query_return = pd.read_sql_query(sql_query, engine)
query_return.head()
Out[25]:
actor_id first_name last_name last_update
0 1 PENELOPE GUINESS 2006-02-15 04:34:33
1 2 NICK WAHLBERG 2006-02-15 04:34:33
2 3 ED CHASE 2006-02-15 04:34:33
3 4 JENNIFER DAVIS 2006-02-15 04:34:33
4 5 JOHNNY LOLLOBRIGIDA 2006-02-15 04:34:33
In [26]:
sql_query = """
select concat(first_name, " ", last_name) as Actor_Name
from actor

"""
query_return = pd.read_sql_query(sql_query, engine)
query_return.head()
Out[26]:
Actor_Name
0 PENELOPE GUINESS
1 NICK WAHLBERG
2 ED CHASE
3 JENNIFER DAVIS
4 JOHNNY LOLLOBRIGIDA
In [27]:
sql_query = """
select actor_id, first_name, last_name from actor
where first_name = "Joe"
"""
query_return = pd.read_sql_query(sql_query, engine)
query_return.head()
Out[27]:
actor_id first_name last_name
0 9 JOE SWANK
In [28]:
sql_query = """
select * from actor
where last_name like "%%GEN%%"
"""
query_return = pd.read_sql_query(sql_query, engine)
query_return.head()
Out[28]:
actor_id first_name last_name last_update
0 14 VIVIEN BERGEN 2006-02-15 04:34:33
1 41 JODIE DEGENERES 2006-02-15 04:34:33
2 107 GINA DEGENERES 2006-02-15 04:34:33
3 166 NICK DEGENERES 2006-02-15 04:34:33
In [29]:
sql_query = """
select last_name, first_name from actor
where last_name like "%%LI%%"
order by last_name, first_name

"""
query_return = pd.read_sql_query(sql_query, engine)
query_return
Out[29]:
last_name first_name
0 CHAPLIN GREG
1 JOLIE WOODY
2 OLIVIER AUDREY
3 OLIVIER CUBA
4 WILLIAMS GROUCHO
5 WILLIAMS MORGAN
6 WILLIAMS SEAN
7 WILLIS BEN
8 WILLIS GENE
9 WILLIS HUMPHREY
In [30]:
sql_query = """
select country_id, country from country
where country IN ("China", "Afghanistan", "Bangladesh");

"""
query_return = pd.read_sql_query(sql_query, engine)
query_return
Out[30]:
country_id country
0 1 Afghanistan
1 12 Bangladesh
2 23 China
In [31]:
sql_query = """
select * 
from actor
"""
query_return = pd.read_sql_query(sql_query, engine)
query_return.head()
Out[31]:
actor_id first_name last_name last_update
0 1 PENELOPE GUINESS 2006-02-15 04:34:33
1 2 NICK WAHLBERG 2006-02-15 04:34:33
2 3 ED CHASE 2006-02-15 04:34:33
3 4 JENNIFER DAVIS 2006-02-15 04:34:33
4 5 JOHNNY LOLLOBRIGIDA 2006-02-15 04:34:33
In [32]:
sql_query = """
select * 
from actor
"""
query_return = pd.read_sql_query(sql_query, engine)
query_return.head()
Out[32]:
actor_id first_name last_name last_update
0 1 PENELOPE GUINESS 2006-02-15 04:34:33
1 2 NICK WAHLBERG 2006-02-15 04:34:33
2 3 ED CHASE 2006-02-15 04:34:33
3 4 JENNIFER DAVIS 2006-02-15 04:34:33
4 5 JOHNNY LOLLOBRIGIDA 2006-02-15 04:34:33
In [33]:
sql_query = """
select last_name,
count(last_name) as Name_Count
from actor
group by last_name
order by last_name asc
"""
query_return = pd.read_sql_query(sql_query, engine)
query_return.head()
Out[33]:
last_name Name_Count
0 AKROYD 3
1 ALLEN 3
2 ASTAIRE 1
3 BACALL 1
4 BAILEY 2
In [34]:
sql_query = """
select last_name,
count(last_name) as Name_Count
from actor
group by last_name
Having Name_Count >= 2
order by last_name asc
"""
query_return = pd.read_sql_query(sql_query, engine)
query_return.head()
Out[34]:
last_name Name_Count
0 AKROYD 3
1 ALLEN 3
2 BAILEY 2
3 BENING 2
4 BERRY 3
In [36]:
sql_query = """
select * from actor

"""
query_return = pd.read_sql_query(sql_query, engine)
query_return.head()
Out[36]:
actor_id first_name last_name last_update
0 1 PENELOPE GUINESS 2006-02-15 04:34:33
1 2 NICK WAHLBERG 2006-02-15 04:34:33
2 3 ED CHASE 2006-02-15 04:34:33
3 4 JENNIFER DAVIS 2006-02-15 04:34:33
4 5 JOHNNY LOLLOBRIGIDA 2006-02-15 04:34:33
In [37]:
sql_query = """
select * from address

"""
query_return = pd.read_sql_query(sql_query, engine)
query_return.head()
Out[37]:
address_id address address2 district city_id postal_code phone location last_update
0 1 47 MySakila Drive None Alberta 300